MySQL: Grouping with condition "persons" table:

id | name
-----------
1  | alice
2  | ben
3  | claire

"items" table:

id | name        | person_id | is_active
-----------------------------------------
1  | apple       | 1         | NO
2  | banana      | 2         | YES
3  | carrot      | 2         | NO
4  | dragonfruit | 3         | NO
5  | eggplant    | 3         | YES

So by running the below query:

SELECT * FROM persons p
LEFT JOIN items i ON p.id = i.person_id

I get:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
1  | alice  | 1  | apple       | 1         | NO
2  | ben    | 2  | banana      | 2         | YES
2  | ben    | 3  | carrot      | 2         | NO
3  | claire | 4  | dragonfruit | 3         | NO
3  | claire | 5  | eggplant    | 3         | YES

Rules in application-level as follows:

Each person must have at least 1 item.
Each person can have 0 or 1 active items only.

I want to filter the above result to get:

Get 1 record only for each person.
Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person.
Order result by person id DESC.

So I'm trying to get something like this:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
3  | claire | 5  | eggplant    | 3         | YES
2  | ben    | 2  | banana      | 2         | YES
1  | alice  | 1  | apple       | 1         | NO

EDIT:

I need to get the persons records (even ones without an active item). From the items table I need only the active item's name. If the person doesn't have an active item, it's fine if I can get it as empty string or null. So something like this will also be ok:

id | name   | item_name
-----------------------------------------------------
3  | claire | eggplant
2  | ben    | banana
1  | alice  |

Thanks for the clear specifications of the requirements, and the sample input and output.

"Each person must have at least 1 item." -- probably no extra code.

"Each person can have 0 or 1 active items only." -- GROUP BY person_id, then do the test in HAVING SUM(is_active = 'YES') <= 1.

"Get 1 record only for each person" -- The GROUP BY does that.

"Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person." -- This gets tricky.  If there were only one person, something like this should work: ORDER BY is_active = 'YES' DESC  LIMIT 1.  But that does not work with grouping.  More in a minute.

"Order result by person id DESC" -- This happens at the end.

Now it gets tricky because you need a "groupwise max".  Let's try to get the item.id values rather than just the one id desired.  Then work on getting the details for the 'first' id.  And let's wait until the end to get the person.name; we can easily ignore it until then.

SELECT  id,
        GROUP_CONCAT(id ORDER BY is_active = 'YES' DESC) AS ids
    FROM  items
    GROUP BY  id
    HAVING  SUM(is_active = 'YES') <= 1
    ORDER BY id DESC

See if that gives you the correct set of rows (1 per person, ordered correctly) and the first id of ids is the desired item.  Assuming all that works, let's finish by getting the other desired columns.  (I will eliminate the redundant column in your sample output, and toss the ids.)

SELECT  p.id,
        p.name,
        IF(i.is_active = 'YES', i.name, '') AS item_name
    FROM (
            the-above-select
         ) AS x
    JOIN items AS i  ON i.id = SUBSTRING_INDEX(x.ids, ',', 1)
    JOIN persons AS p ON p.id = x.id
    ORDER BY p.id DESC

(There is, of course, a chance that I goofed.  But that could come close.)


Necessary to have separate index for lookups against one of a multi-column PK? Sorry if this is a FAQ, but I'm having problems turning up exactly what I'm asking:

I have a table (mysql) with a primary key which is composed of multiple columns only because this is necessary to make the PK unique (won't be using an autoincrement column).

However, most lookups will be against just the first column in the primary key.

So should I have a separate index only containing that single column, or is the multi-column PK sufficient (at least as long as the query column is listed first in the index definition)?

In short, it is sufficient. As far as the searched field is the first in a composite index (PK, unique, or regular index), no need for other separate index.

However, you mentioned that you

  (won't be using an autoincrement column)

Is there a reason for that? Composite PK might have performance hit, specially if you are using InnoDB engine, and if you have many updates on the table (insert/update/delete)


How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack? I was doing a PowerShell training, that said use the PowerShell help to run a "Networking troubleshooting pack"

I found, ran and read

Get-Help Get-TroubleshootingPack -full
Get-Help Invoke-TroubleshootingPack -full
Get-Help about_Remote_Troubleshooting -full

Thinking I had it mostly figured out I ran

PS C:\> Get-TroubleshootingPack -Path 'C:\Windows\diagnostics\system\Networking' | Invoke-TroubleshootingPack

And that gives me

Starting network diagnostics...

Instance ID
Not to be specified outside of MSDT application.
:

So I enter the Instance

MSSQLSERVER

And that gets me this error

Invoke-TroubleshootingPack : An error occurred when running a PowerShell script. (Exception from HRESULT:
0x803C0101)
At line:1 char:76
+ Get-TroubleshootingPack -Path "C:\Windows\diagnostics\system\Networking" | Invok ...
+                                                                            ~~~~~
    + CategoryInfo          : InvalidArgument: (Microsoft.Windows.Diagnosis.DiagPack:DiagPack) [Invoke-Trou
   bleshootingPack], COMException
    + FullyQualifiedErrorId : PackageDiagnoseFailed,Microsoft.Windows.Diagnosis.Commands.InvokeTroubleshoot
   ingPack

I try several options for the Instance ID: Server Name, IP, 0, 1. They all give the same error

Then I go looking for a way to identify the Instance ID, I find the two questions below but they don't give me anything to get around the error.

SQL Server - difference between instance name and instance id
SQL Server Instance ID

I search some more and found This Blog which is giving step by step to do everything I already figured out.  It shows the place to enter the Instance ID. But gives no clue what the value should be.  More searching just, leads to more frustration.

You don't need to enter a 'Instance ID' for PowerShell Invoke-TroubleshootingPack

Just hit enter, when prompted for the Instance ID, without a value.

After I figured it out, I went back and looked at the training directions and found the following hiding in the middle of the paragraph.

  you’ll be asked for an “Instance ID.” Hit Enter

I learned a lot on this journey, this was frustrating. Posting so others will find the answer sooner than I did.


AWS RDS databases & SSIS I have 2 AWS RDS databases (A & B) and need to create a datawarehouse with star scheme and slowly changing dimensions within AWS.

From a MS background it makes sense to create a SSIS package to run as a SQL job, which can collate the A & B databases and create dimensions and facts.

AWS RDS does not have SSIS support and I would therefore need to run an EC2 instance with SQL Server installed which then ran the SSIS package.

My questions is - is there any better way, assuming a heavy MS stack and AWS?

You can use several ETL tools for this, it could be on premies, or cloud based, final decision always depends from cost, corporate standards and many other moments

good starting point as most close to Your request:

Informatica Cloud -
https://marketplace.informatica.com/mpresources/docs/Implementing_DWH_on_AWS_in_Hybrid_Env.pdf
Talend Cloud - https://www.talend.com/products/integration-cloud
Community version of Talend as well could be installed on any VPS and
connect to cloud databases

Any tools and recommendations always depends from personal experience and of course on the market You can find more commercial and open source tools


how to update column on same row with trigger without recursion? SQL Server 2012.

I need to update column [LastUpdated] with the current date and time whenever a record changes in my table.  Currently I have:

CREATE TRIGGER Trig_LastUpdated ON Contact AFTER UPDATE
AS
SET NOCOUNT ON

UPDATE ct
SET LastUpdated = GETDATE()
FROM Contact ct
INNER JOIN Inserted i
    ON ct.IDContact = i.IDContact

But this is recursive and I don't want that, causes deadlocks and other weirdness.  I cannot turn off recursive triggers globally.  I see that INSTEAD OF triggers are non-recursive, but if I do that do I have to check every other column in the Inserted to see if it got updated or will SQL Server handle that for me?  What's the best way to do this?

Given that you cannot disable recursive triggers, the next best options are:

Have the trigger detect how many levels deep it is using TRIGGER_NESTLEVEL function. Use this at the beginning of the trigger to simply exit if it is not the 1st trigger execution in the stack. Something along the lines of:

IF (TRIGGER_NESTLEVEL() > 1)
BEGIN
  -- Uncomment the following PRINT line for debugging
  -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;

This will require a little bit of testing to see how it is affected by the initial insert being done by another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.
Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a VARBINARY(128) value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting the context_info column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.

You could place the following at the beginning of the trigger:

IF (CONTEXT_INFO() = 0x01)
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;
ELSE
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  SET CONTEXT_INFO 0x01;
END;

This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.

Either of those methods is more reliable than using IF NOT UPDATE(LastUpdated) since the UPDATE(column_name) function can only tell you if the column was in the SET clause or not. It cannot tell you if the value has changed, or if it changed to the "current" GETDATE() value that you are expecting / wanting. Meaning, all of the following statements bypass the desired effect of the trigger (i.e. making sure that the LastUpdated column has the actual date & time of the modification):

UPDATE ct
SET    ct.LastUpdated = ct.LastUpdated
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = '1900-04-01`
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = 1
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = GETDATE() + 90
FROM   Contact ct
WHERE  ...

The safest method is probably using TRIGGER_NESTLEVEL() (option 1) and passing in the parameters for checking just this particular trigger, so that being called due to an INSERT from another trigger does not adversely affect it:

TRIGGER_NESTLEVEL( @@PROCID , 'AFTER' , 'DML' )


Why does dropping foreign keys take long? I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:

ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2

What surprises me is that the script takes long time: on average, 20 seconds for each DROP FK.
Now, I understand that creating a FK may be a big deal, because server has to go and check that the FK constraint is not infringed from the beginning, but dropping? What does a server do when dropping FKs that takes so long?
This is both for my own curiosity, and to understand if there is a way to make things faster. Being able to remove FK (not just disable them) would allow me to be much faster during a migration, and therefore minimize downtime.

Dropping a constraint requires a Sch-M (Schema Modification) lock that will block others to query the table during the modification. You are probably waiting to get that lock and has to wait until all currently running queries against that table are finished.
A running query has a Sch-S (Schema Stability) lock on the table and that lock is incompatible with a Sch-M lock.
From Lock Modes, Schema Locks

The Database Engine uses schema modification (Sch-M) locks during a
table data definition language (DDL) operation, such as adding a
column or dropping a table. During the time that it is held, the Sch-M
lock prevents concurrent access to the table. This means the Sch-M
lock blocks all outside operations until the lock is released.
Some data manipulation language (DML) operations, such as table truncation,
use Sch-M locks to prevent access to affected tables by concurrent
operations.
The Database Engine uses schema stability (Sch-S) locks
when compiling and executing queries. Sch-S locks do not block any
transactional locks, including exclusive (X) locks. Therefore, other
transactions, including those with X locks on a table, continue to run
while a query is being compiled. However, concurrent DDL operations,
and concurrent DML operations that acquire Sch-M locks, cannot be
performed on the table.


On duplicate key do nothing I am inserting into the following table using LuaSQL with PtokaX API.

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is, when a user(represented by nick) tries to insert same request again, the UNIQUE index is checked and the script returns a false. This causes my script to fail and I have to restart the script.

  Is there something I can do in the INSERT ... ON DUPLICATE KEY command so that it does nothing or at-least does NOT return an error in case of DUPLICATE KEY?

Otherwise I'd have to go for updating my dated field with the new DATETIME value.

Three ways. Either IGNORE duplicate errors
(but don't use that, see note in the end):
INSERT IGNORE
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:
INSERT
  ...
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:
INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.
I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.
*Note: INSERT IGNORE will ignore all insert related errors, even not null constraint violations, so it is best to avoid it.


Is there a way to execute SQL statement based on SQL Server version? I would like to create computed index on a table if SQL Server is 2008 or newer and a simple index if SQL Server is 2005 or older:

-- check for sql server version
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10
        CREATE unique nonclustered index ix1_table
            ON table (column1, column2)
            WHERE column1 is not null and column2 is not null
        ELSE
            CREATE nonclustered index ix1_table
                ON table (column1, column2)

The problem is that the whole statement is evaluated and on SQL Server 2005 this throws an error:

  Incorrect syntax near the keyword 'WHERE'.

Is it possible to somehow create different index based on SQL Server version?

You can use dynamic SQL

I mean first check the version

Then build your SQL statement using a string variable, for example nvarchar(max)

Then execute it by sp_executeSQL

I think following script can work for this task

-- check for sql server version
declare @sql nvarchar(max)
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10

set @sql = N'CREATE unique nonclustered index ix1_table ON [table] (column1, column2)
        WHERE column1 is not null and column2 is not null'

        ELSE

set @sql = N'CREATE nonclustered index ix1_table ON [table] (column1, column2)'

exec sp_executeSQL @sql


Merge Replicate and Mirrors I am preparing some scripts to setup merge replication in our production environment.
After completing the export from UAT, I've come across something that doesn't appear like it would work in the production environment?

exec sp_addmergesubscription
    @publication = N'<snip/>',
    @subscriber = N'<Server Name>',
    @subscriber_db = N'<snip/>',
    @subscription_type = N'pull',
    @subscriber_type = N'local',
    @subscription_priority = 0,
    @sync_type = N'Automatic'

The problem I have with this is that the subscriber name is hardcoded to the physical server name of the subscriber DB.  In UAT that's fine because the subscriber is a single server, in production that's a problem because there are three production subscribers with mirror partners.

What name should I be using for the subscriber?  If I leave it as NULL as the documentation indicates I can, what are the implications of that?

The only answer here, unfortunately is that mirroring is only supported for the pulbisher and only if the distributor is on a different server from with the principal or mirror. I would recommend a dedicated server for the distributor, but even using the subscriber server would work.

Database mirroring is not supported for distributors or for subscribers, and the work-around for mirroring a subscriber as detailed in the whitepaper by SQLCat only works for transactional replication. You could still use the manual sync option if you want to try to do something similar, but you'd have to get the data in perfect sync yourself which is very difficult to do unless you stop traffic to the publisher.


Trying to restore users/passwords for a MySQL server I had a system failure, but I managed to save the /var/lib/mysql library on my linux system.

I have since rebuilt a new Linux (Ubuntu) system (LAMP). I copied the old /var/lib/mysql/mysql folder to the new system as /var/lib/mysql/old_mysql, and I can see the contents using phpmyadmin.

I also copied all the folders from the old /var/lib/mysql to the new /var/lib/mysql.

If I do the following:

Export the contents of the old_mysql/user table, and import it into the new mysql/user table

Export the contents of the old_mysql/db table, and import it into the new mysql/db table

Will that restore the users and their passwords for all the databases?

You don't need to export or import anything.  As long as the versions of MySQL on both machines are identical you can simply

Shutdown MySQL process service mysql stop
Overwrite the installed /var/lib/mysql with the old /var/lib/mysql.

A. cp -Rf /var/lib/old_mysql /var/lib/mysql

B. As a precaution you could move the /var/lib/mysql on the new server
mv /var/lib/mysql /var/lib/mysql_default
chown mysql:mysql -R /var/lib/mysql
Overwrite the installed my.cnf with the old /etc/mysql/my.cnf file

A. As a precaution you can mv the my.cnf on the new installation
mv /etc/mysql/my.cnf /etc/mysql/my.cnf_default

B. Or even better, you could put a custom .cnf file in /etc/mysql/conf.d
Start MySQL process service mysql start

Your users and permissions will be restored automatically

You can verify version info with mysql -V

Good luck


SQL Server vs SOLR (Or any document db) I have my "customer" data in a normalized sql server database.

Getting out the customer data in my app is taking too long.  This is because I have to go to 10+ tables to get all the data I need.

My company has an installation of SOLR that I thought about storing a Json object that contains all the data I need for a single "customer" already put together.

I think that this would give me some significant speed improvements.

However, it got me to wondering what the difference would be to me putting this data in a single table with a varchar(max) column that has the Json in it.  I could index my 10ish searchable columns on the same table as the json.

I know that document databases are very popular.  So I imagine there has to be benefits over just rolling my own using denormalized data in sql server.  Can someone tell me what they are?

Many NoSQL products have sharding built-in.  The DBMS itself looks after storing a particular key range on a certain server and keeping redundant copies for high availability.  Client connections are routed within the DBMS rather than in the application.  Multi-server scale out becomes easy, at the cost of CAP compromises.


Whats is the right way to check for new messages? I have some messages in my mails table (MySQL).

I would like to send request via ajax every 5 seconds and look for new messages.

when i load the page first time, I use query like this

SELECT * FROM mails WHERE user_id = 1 LIMIT 100;

I'm displaying the messages in frontend as html table.

If any new messages received, how to only fetch new messages via AJAX?

I'm talking about the same way how gmail inbox works. When someone send you a mail, new messages is prepended in your browser mail list.

Thanks

SELECT * FROM mails
    WHERE user_id = 1
      AND sequence > $left_off
    ORDER BY sequence
    LIMIT 100;

You need a column sequence that is monotonically increasing.  And AJAX needs to keep track of "where it left off".


How to escape special characters in MySQL When I do select * .. | mysql ... > /tmp/file from a table with text, there are some problematic characters that prevent me from loading it to a different db using copy (postgres) or load into (mysql).
Character like tab, new line are translated automatically to \n and \t, but some of the problematic characters are escape ^[, CR ^M, ^U,^Z,^F,^H and maybe other that I haven't seen before.
Usually I would just replace it like echo "select * .." | mysql .. | sed 's/\r/\\r/g', but there are too many unknown chars there. So instead of replacing them after the select I would like a function that retrieve the text already escaped (I guess remove them is also fine).
How should I do it?

To escape all special char except NULL, new line and tab:
select * .. | mysql ... | sed 's/[\x04-\x08\x0B-\x1F\x7F]/ /g' > /tmp/file
useful link:
https://stackoverflow.com/questions/6534556/how-to-remove-and-all-of-the-escape-sequences-in-a-file-using-linux-shell-sc


Dropping a schedule whose name contain '.' I was attempting to create a schedule called MYSCHEMA.MYSCHED in my Oracle database using SQL Developer 4.0 Early Access, and there appears to be a bug resulting in a schedule named MYSCHEMA. to be created.

How could I remove this schedule? This does not work:

EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('EDP_MANAGER.');

(results in 00931. 00000 -  "missing identifier")

While this:

EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('EDP_MANAGER\.');

results in "invalid character" instead.

Double quoting works:

EXECUTE SYS.dbms_scheduler.drop_schedule('"MYSCHEMA."');


6th normal form, recomposition query, efficient implementation I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the language tutorial D. I understand how to convert all this logic into SQL Server. But at the end of the he shows how well this can be implemented in current database management systems, and then I see this part I need to implement:

Recomposition query: can be done but likely to perform horribly.
Might be preferable to store PERS_INFO as a single table under the
covers, so that the tables resulting from decomposition can be
implemented as mappings to that. But current technology doesn’t give
clean separation of physical storage from  logical design. Perhaps
something for the next generation of software engineers to grapple
with?

It suggest storing PERS_INFO as a single table under the covers, but what does that really mean? How would I implement that in SQL Server?

Might be preferable to store PERS_INFO as a single table under the
  covers, so that the tables resulting from decomposition can be
  implemented as mappings to that. But current technology doesn’t give
  clean separation of physical storage from logical design.

He's talking about the dbms storing it as a single table, not you storing it as a single table.

The relational model itself says nothing about physical storage. It only requires that "the database be perceived by the user as tables. . . . At the physical level, in fact, the system is free to store the data any way it likes . . ." (An Introduction to Database Systems, 7th ed., CJ Date, p 61)

So Darwen is saying that if the database designer declares a set of 6NF tables, along with their

candidate keys,
distributed keys,
foreign keys,
foreign distributed keys,
and so on,

then the dbms is free to

implement that set of 6NF tables as a single table in a SQL tablespace,
hide that single-table implementation from the user, and
expose to the users a set of updatable views that are indistinguishable from the 6NF tables the database designer declared.

Having said all that, you might be able to work around the lack of dbms support by creating a single table on top of the covers (so to speak), and create a set of updatable views that are indistinguishable from your 6NF design. Revoke permissions on the single table, and require all users to use only the updatable views. What you can't do is have the dbms take care of all those details  based solely on a series of logically correct, 6NF CREATE TABLE statements.


You can have multiple aggregates within a PIVOT but you must alias them.

Doing this will add the alias of the aggregate as a SUFFIX for the resulting column name.  (Your requirement is to have them as a PREFIX)

The FOR x in ( ) entries can also have a column name.  These will be the PREFIX for the final column.

I've modified the requirement, but here is the result.

with data (ID,TYPE,LINE_1_ADDR,LINE_2_ADDR,LINE_3_ADDR
          ,CITY_NAM, STATE , COUNTRY , EFFEC_DT  , ROW_UPDATE_DT
          ,ADDR_ROLE_CD )
as (
SELECT
  10114753088
 ,'SBS CUSTOMER'
 ,'2095 CAPP HARLIN RD'
 ,'Apt #225'
 ,''
 ,'SIMPSONVILLE'
 ,'MN'
 ,'USA'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '20-Mar-15','rr-mon-dd' )
 ,'BILL'
FROM dual
UNION ALL
SELECT
  10114753088
 ,'SBS CUSTOMER'
 ,'304 S MAGNOLIA ST'
 ,''
 ,'P.NO:516'
 ,'Toronto'
 ,'ON'
 ,'CAN'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '21-Mar-15','rr-mon-dd' )
 ,'PHYSICAL'
FROM dual)
select *
from data
pivot (
   min(LINE_1_ADDR) as "LINE_1_ADDR"
  ,min(LINE_2_ADDR) AS "LINE_2_ADDR"
  ,min(LINE_3_ADDR) as "LINE_3_ADDR"
  ,min(CITY_NAM) as "CITY_NAM"
  ,min(STATE) as "STATE"
  ,min(COUNTRY) as "COUNTRY"
  ,min(EFFEC_DT) as "EFFEC_DT"
  ,min(ROW_UPDATE_DT) as "ROW_UPDATE_DT"
  for addr_role_cd in ( 'BILL' as "BILLING", 'PHYSICAL' as "PHYS" )
)


Companies and addresses MySQL I've got a database with 2 tables.

companies
addresses

These are my tables.
Adresses

adress_id
adress_adress
adress_adressnumber
adress_zipcode
adress_city
adress_postbus
adress_country
adress_building
adress_unit

Companies

company_id
company_name
company_adress_id
company_postadress_id

A company can have a address but maby different postal address. I want to join these addresses in 1 query but when i join them only 1 address is shown because i think they're overlapping.
What can i do make this work? I'm really struggling.

Thanks in advance.

SELECT * FROM companies as c JOIN adresses as a ON c.company_adress_id = a.adress_id JOIN adresses as postaddress ON c.company_postadress_id = postaddress.adress_id

-- ----------------------------
            -- Table structure for adresses
            -- ----------------------------
            DROP TABLE IF EXISTS `adresses`;
            CREATE TABLE `adresses` (
            `adress_id` int(11) NOT NULL AUTO_INCREMENT,
            `adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
            `adress_adressnumber` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
            `adress_zipcode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
            `adress_city` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
            `adress_postbus` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
            `adress_country` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
            `adress_building` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
            `adress_unit` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
            `company_id` int(11) DEFAULT NULL,
            `addresss_role` tinyint(4) DEFAULT NULL COMMENT 'Role 1 for company may be and 2 for comapny postal, feel free',
            PRIMARY KEY (`adress_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

            -- ----------------------------
            -- Records of adresses
            -- ----------------------------
            INSERT INTO `adresses` VALUES ('1', '1324234', '', '', '', null, '', null, null, '1', '2');
            INSERT INTO `adresses` VALUES ('2', '67856785678', '', '', '', null, '', null, null, '1', '1');

            -- ----------------------------
            -- Table structure for companies
            -- ----------------------------
            DROP TABLE IF EXISTS `companies`;
            CREATE TABLE `companies` (
            `company_id` int(11) NOT NULL AUTO_INCREMENT,
            `company_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
            `company_adress_id` int(11) NOT NULL,
            `company_postadress_id` int(11) NOT NULL,
            PRIMARY KEY (`company_id`),
            KEY `company_adress_id` (`company_adress_id`,`company_postadress_id`),
            KEY `company_adress_id_2` (`company_adress_id`,`company_postadress_id`),
            KEY `company_postadress_id` (`company_postadress_id`),
            CONSTRAINT `address` FOREIGN KEY (`company_adress_id`) REFERENCES `adresses` (`adress_id`) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT `postal_address` FOREIGN KEY (`company_postadress_id`) REFERENCES `adresses` (`adress_id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

            -- ----------------------------
            -- Records of companies
            -- ----------------------------
            INSERT INTO `companies` VALUES ('1', 'sdfgsdf', '1', '2');


How can I determine the date of a snapshot in SQL Server 2008r2? I am working on implementing an ETL process to load data into a warehouse.

Part of this process is pulling data from our production server (Server P) to a staging server where the actual load into the warehouse (and the warehouse itself) is located (Server S).  We are using snapshot replication for this.

For QC and some other purposes, I need a way to determine the date that the snapshot was taken on Server P, from Server S.  Linking servers for this query I don't think is an option.

I'm hoping that there is a record in a metadata table somewhere to reflect this, or some other command I can run to query the snapshot history.

MSSnapshot_History has snapshot information for replication.

You can join to MSSnapshot_Agents to narrow it down to your specific agent, publication, and job.

These tables are found in the [distribution] database on your distribution instance.

Here's a basic query to get the last start time of your snapshot agents.

select a.name, max(start_time) laststart
    from MSsnapshot_agents a
        join MSsnapshot_history h
            on a.id = h.agent_id
            where h.runstatus = 2 -- this will get you the last successful execution
        group by a.name


After log shipping, I activate failover with a RESTORE. What happens when the next log shipping job runs? I'm using log shipping for the first time. I've got it working between my primary and secondary server, on the secondary server the database is Restoring... which I understand basically means it is waiting to be activated.

I will run RESTORE DATABASE MyDatabase WITH RECOVERY to activate it, but then what happens next time the job runs to do log shipping? Will it fail? Should I disable the job manually? Will it overwrite my database and set it back to Restoring... ?

Thanks

I will run RESTORE DATABASE MyDatabase WITH RECOVERY to activate it, but then what happens next time the job runs to do log shipping? Will it fail? Should I disable the job manually? Will it overwrite my database and set it back to Restoring... ?

Yes they will run. Logshipping is simple copy and movement of logs so when you bring secondary database online it just brings the database online it does not affects any job running. So you should disable all backup,copy,restore and alerts jobs.
When next time on primary, backup job wil run it will succeed even copy job will succeed on secondary but restore job would fail on secondary as database is online and logs cannot be restored more over log chain would be broken.


Create a hierarchy chain (reverse path) for each node in a tree Given a typical closure table, how would I write a query to get a list of ids back to the root?  Is there a way to do that for all the [unique] ids?  I'm using Microsoft SQL Server.

Given:

parent  child   depth
1       1       0
2       2       0
3       3       0
1       2       1
2       3       1
1       3       2

I'd like:

id    path
1     1
2     1,2
3     1,2,3

Does that make sense?

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg

CREATE TABLE #heredity
(
    parent  INT NOT NULL,
    child   INT NOT NULL,
    depth   INT NOT NULL,

CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child )
);
GO

INSERT INTO #heredity ( parent, child, depth )
VALUES
    ( 1, 1, 0 ),
    ( 2, 2, 0 ),
    ( 3, 3, 0 ),
    ( 1, 2, 1 ),
    ( 2, 3, 1 ),
    ( 1, 3, 2 );
GO

SELECT
    child,
    STUFF(
    (
    SELECT ',' + CAST( parent AS VARCHAR(20) )
    FROM #heredity p
    WHERE c.parent = p.child
    ORDER BY parent DESC
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(100)'),
    1, 1,'') AS [path]
FROM #heredity c
WHERE depth = 0;

My results:


Remove punctuation marks, special chars and space and compare cols I have a table with new and old long description columns and I want to compare them by removing space, punctuation marks, and special characters for differences.

Create table DESCR_COMP
(
HCPCS Char(5) not null,
SEQNUM Char(4) not null,
NEW_LONG_DESC char(1000) Not NUll,
OLD_LONG_DESC char(1000) Not NUll,
SHORT_DESC char(250) Not nUll,
ADD_DT DATE,
ACT_EFF_DT DATE,
TERM_DT DATE,
CONSTRAINT PK_HCPCS primary key clustered
(
HCPCS ASC
)
)

Here are the values. total number of rows is 644 but below is the first few records

insert into Z64338_DESCR_COMP select 'A0120', '0010', 'Non-emergency transportation: mini-bus, mountain area transports, or other transportation systems', 'Non-emergency transportation: mini-bus, mountain area transports, or other  transportation systems', 'Noner transport mini-bus', '19820101', '20030101', ''
insert into Z64338_DESCR_COMP select 'A0130', '0010', 'Non-emergency transportation: wheelchair van', 'Non-emergency transportation: wheel-chair van', 'Noner transport wheelch van', '19820101', '', ''
insert into Z64338_DESCR_COMP select 'A0170', '0010', 'Transportation ancillary: parking fees, tolls, other', 'Transportation ancillary:  parking fees, tolls, other', 'Transport parking fees/tolls', '19820101', '20030101', ''
insert into Z64338_DESCR_COMP select 'A0180', '0010', 'Non-emergency transportation: ancillary: lodging-recipient', 'Non-emergency transportation: ancillary:  lodging-recipient', 'Noner transport lodgng recip', '19820101', '', ''
insert into Z64338_DESCR_COMP select 'A0190', '0010', 'Non-emergency transportation: ancillary: meals-recipient', 'Non-emergency transportation: ancillary:  meals-recipient', 'Noner transport meals recip', '19840101', '', ''
insert into Z64338_DESCR_COMP select 'A0200', '0010', 'Non-emergency transportation: ancillary: lodging escort', 'Non-emergency transportation: ancillary:  lodging escort', 'Noner transport lodgng escrt', '19820101', '', ''

I am first trying to see if I can query the columns with the values replaced and then use it in an update statement and have the column value stored in those columns and then compare. I start with the 'NEW_' column but the query is running for 20+ mins before I had to cancel it.

When I use Maxrecursion 1000, I get ",......has been exhausted before statement completion" so using infinite recursion and I don't know if that is the issue.

I dont know to use RegEX with SQLCLR so i havent gone that route. Are there more efficient ways doing it? Any way to fix my code?

DECLARE @Pattern varchar(50) = '%[^A-Za-z0-9]%';

WITH FixBadChars AS (SELECT NEW_LONG_DESC, cast(NEW_LONG_DESC as Varchar(max)) as FixedString, 1 AS MyCounter, HCPCS
                FROM [Z64338_DESCR_COMP]
                UNION ALL
                SELECT NEW_LONG_DESC, Replace(FixedString, PatIndex(@Pattern, FixedString COLLATE Latin1_General_BIN2), '') AS FixedString,
                    MyCounter + 1, HCPCS
                FROM FixBadChars
                WHERE FixedString COLLATE Latin1_General_BIN2 LIKE @Pattern)
SELECT NEW_LONG_DESC, FixedString, MyCounter, HCPCS
FROM FixBadChars
WHERE MyCounter =
        (SELECT MAX(MyCounter)
        FROM FixBadChars Fixed
        WHERE Fixed.HCPCS = FixBadChars.HCPCS)
OPTION (MAXRECURSION 0);

UPDATE FixBadChars
SET NEW_LONG_DESC = FixedString
WHERE MyCounter =
        (SELECT MAX(MyCounter)
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 0);

Since you only have 644 rows, you could probably get away (performance-wise) with creating a user defined function.

Create Function [dbo].[RemoveSpecialCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^A-Za-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Then, assuming your sample data (I purposely changed a value in the last row.)

DROP TABLE IF EXISTS DESCR_COMP;
go
CREATE TABLE DESCR_COMP (
    HCPCS CHAR(5) NOT NULL
    ,SEQNUM CHAR(4) NOT NULL
    ,NEW_LONG_DESC CHAR(1000) NOT NULL
    ,OLD_LONG_DESC CHAR(1000) NOT NULL
    ,SHORT_DESC CHAR(250) NOT NULL
    ,ADD_DT DATE
    ,ACT_EFF_DT DATE
    ,TERM_DT DATE
    ,CONSTRAINT PK_HCPCS PRIMARY KEY CLUSTERED (HCPCS ASC)
    )
insert into DESCR_COMP select 'A0120', '0010', 'Non-emergency transportation: mini-bus, mountain area transports, or other transportation systems', 'Non-emergency transportation: mini-bus, mountain area transports, or other  transportation systems', 'Noner transport mini-bus', '19820101', '20030101', ''
insert into DESCR_COMP select 'A0130', '0010', 'Non-emergency transportation: wheelchair van', 'Non-emergency transportation: wheel-chair van', 'Noner transport wheelch van', '19820101', '', ''
insert into DESCR_COMP select 'A0170', '0010', 'Transportation ancillary: parking fees, tolls, other', 'Transportation ancillary:  parking fees, tolls, other', 'Transport parking fees/tolls', '19820101', '20030101', ''
insert into DESCR_COMP select 'A0180', '0010', 'Non-emergency transportation: ancillary: lodging-recipient', 'Non-emergency transportation: ancillary:  lodging-recipient', 'Noner transport lodgng recip', '19820101', '', ''
insert into DESCR_COMP select 'A0190', '0010', 'Non-emergency transportation: ancillary: meals-recipient', 'Non-emergency transportation: ancillary:  meals-recipient', 'Noner transport meals recip', '19840101', '', ''
insert into DESCR_COMP select 'A0200', '0010', 'Non-emergency transportation: ancillary: lodging escort', 'Non-emergency transportation: ancillar:  lodging escort', 'Noner transport lodgng escrt', '19820101', '', ''
go

SELECT *
FROM DESCR_COMP
WHERE dbo.RemoveSpecialCharacters(OLD_LONG_DESC) <> dbo.RemoveSpecialCharacters(NEW_LONG_DESC)

Results:

| HCPCS | SEQNUM | NEW_LONG_DESC                                           | OLD_LONG_DESC                                           | SHORT_DESC                   | ADD_DT     | ACT_EFF_DT | TERM_DT    |
|-------|--------|---------------------------------------------------------|---------------------------------------------------------|------------------------------|------------|------------|------------|
| A0200 | 0010   | Non-emergency transportation: ancillary: lodging escort | Non-emergency transportation: ancillar:  lodging escort | Noner transport lodgng escrt | 1982-01-01 | 1900-01-01 | 1900-01-01 |


SQL Server replication error "profile name is not valid" Today I started getting the below error message on one of the distribution agent.  The publication has one push and on pull subscription.  The error appeared on the push subscription only.  The publication server has 5 other publication each having pull and push subscription to the same servers and all of them works fine.

I tried changing the distributor agent profile and restarted the distribution job but didn't work.

Message
2016-12-05 00:41:45.429 Copyright (c) 2014 Microsoft Corporation
2016-12-05 00:41:45.429 Microsoft SQL Server Replication Agent: distrib
2016-12-05 00:41:45.429
2016-12-05 00:41:45.429 The timestamps prepended to the output lines are expressed in terms of UTC time.
2016-12-05 00:41:45.429 User-specified agent parameter values:
            -Subscriber SubscriberServer
            -SubscriberDB DBName
            -Publisher Publisher1
            -Distributor DistributerServer
            -DistributorSecurityMode 1
            -Publication DBName_HA_Pub
            -PublisherDB DBName
            -Continuous
            -XJOBID 0x0D2A9EB2B6FBDF4CAE11F0A700401787
            -XJOBNAME Publisher_instance_-DBName-DBName_HA_Pub-SubscriberServer-48
            -XSTEPID 2
            -XSUBSYSTEM Distribution
            -XSERVER DistributerServer
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006888
            -XParentProcessHandle 00000000000083E8
2016-12-05 00:41:45.429 Startup Delay: 668 (msecs)
2016-12-05 00:41:46.101 Connecting to Distributor 'DistributerServer'
2016-12-05 00:41:46.179 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 2
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2016-12-05 00:41:46.179 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.226 Initializing
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.304 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:41:51.351 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:51.351 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:51.367 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:01.390 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:01.390 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:01.406 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:16.423 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:16.423 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:16.454 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:36.474 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:36.474 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:36.505 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.524 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:43:01.524 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:43:01.540 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.556 Agent message code 14607. profile name is not valid
2016-12-05 00:43:01.571 Category:COMMAND
Source:  Failed Command
Number:
Message: if @@trancount > 0 rollback tran
2016-12-05 00:43:01.571 Category:NULL
Source:  Microsoft SQL Server Native DBName 11.0
Number:  14607
Message: profile name is not valid

It seems that you initialized your transactional replication using database backup. With database backup, there is no schema difference between the publisher and subscriber.

There might be trigger exists in your publisher and restored in your subscriber, trying to send email using invalid profile name from your subscriber.


Creating SQL Assembly error Method parent has circular class type parameter constraints I'm trying to add an assembly to SQL server 2012 but I'm getting the following errors. I've tested the dll and it works fine from C# so don't think it's a code issue.  Any suggestions?

Msg 6218, Level 16, State 2, Line 6
CREATE ASSEMBLY for assembly 'HMDACalculateULICheckDigit' failed because assembly 'HMDACalculateULICheckDigit' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : CalculateULICheckDigitCLR::.ctor][mdToken=0x6000002][offset 0x00000000] Method parent has circular class type parameter constraints.
[ : CalculateULICheckDigitCLR::CalculateULICheckDigitHMDA][mdToken=0x6000001][offset 0x00000000] Method parent has circular class type parameter constraints.

The C# itself is pretty simple.

public class CalculateULICheckDigitCLR
{
public static int CalculateULICheckDigitHMDA(string val)
{
    int result;

    result =  (int)(BigInteger.Parse(val) % 97);
    return result;
}
}

Example of value I'm passing in 548322619182814292318016211461767828260500

The issue probably has something to do with the BigInteger class. It is found in System.Numerics which is not in the list of Supported .NET Framework Libraries. Which means that you probably loaded that manually as UNSAFE (or have not loaded it yet?). Which then means that your assembly, HMDACalculateULICheckDigit, needs to be marked as UNSAFE.

This assessment is further supported by the fact that you got a "failed verification" error message, since verification is a process that happens at CREATE ASSEMBLY time and checks for obvious indicators of requiring UNSAFE.


How to differentiate between the result sets retrieved for multiple OR conditions? I've a huge dataset which I'm quering with multiple OR conditions. Though I'm getting all the results, but I want to differentiate between the results that are retrieved for a certain where condition. Is there any way to do that? Here is an example -

Select * from table where col1 like '%text1%' or col1 like '%text2%'

This query is retrieving entire matching dataset, but how can I differentiate between the results that are matching text1 or text2? Thanks in advance.

Typically I recommend materialising your resultset when this scenario has come up. Since queries of this nature are usually pretty broad and analytical (read: non-performant), this approach has the benefit of

reducing editing requirements (do you want to add the line to both the select and where clauses for each evaluation?)
snapshotting the underlying data for debugging
capturing "missed" rows from the underlying data that you thought you should have captured

Avoid:

Select
    if(col1 like '%text1%',TRUE,FALSE) as a,
    if(col1 like '%text2%',TRUE,FALSE) as b,
    col1
from table
where col1 like '%text1%'
   or col1 like '%text2%'

Prefer:

create temporary table foo as (
    Select
        if(col1 like '%text1%',TRUE,FALSE) as a,
        if(col1 like '%text2%',TRUE,FALSE) as b,
        col1
    from table
);

select *
from foo
where a
   or b;

Play with a demo of this approach on dbfiddle


Converting Tables to Ansi Padding On I am currently working with a SQL Server 2008 R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables have Columns with Ansi Padding on AND columns with Ansi Padding off.

I have now been tasked with "converting" those tables so that every column uses Ansi_Padding ON.

I would like to know what is the easiest way to do this?

I am supposed to write a script which "converts" one table but in the end I am going to need a script which changes all the tables.

This can be quite a tall order. My original thinking was that you could simply add a new version of each OFF column, copy the data over, drop the old column, and rename the new column. However there are a boatload of complications that will make this tedious or downright impossible:

constraints (including foreign keys) which will need to be disabled and/or dropped/re-created
computed columns
indexed views and other peripheral objects pointing at the affected table with SCHEMABINDING
the blocking this type of operation will cause on large tables
the mere complexity of generating an ALTER TABLE ADD script for each column, given that in 2008 R2 it is very cumbersome to derive the right data type from the metadata (it's easier in 2012, but still no picnic)

Honestly, I think the easiest thing to do would be to generate a script of the entire database:

right-click the database > tasks > generate scripts
if you get the intro screen, check the checkbox and click next
choose all objects and click next
on the "set scripting options" tab, click "Advanced"

Change "Types of data to script" to "schema and data"

on the same tab, change the output to "New query window"

In the resulting script, before running it, change all the references to the original database name to a new database name (including the file paths), and change all the ANSI_PADDING settings in the script to ON (using find/replace). Once the new database is created, you can rename the old database and rename the new one to the old name. Ultimately you can drop the old database once you've confirmed that everything is working correctly.

Warning: do not try this in SQL Server 2014 CTP2 - In the process of answering this question, I lost some of my own work, due to this bug I just filed.


How to deny connections to localhost and force to use FQDN instead I have a server running Apache and MySQL. Connections to MySQL work fine when using localhost in the connection settings.

Now I'd like to force our developers to use a FQDN instead of localhost. This is because we're planning to move the MySQL servers from the application servers to a MySQL cluster. Then we can transparantly move things without the developers having to modify connection settings.

So I created a CNAME DNS record mysql.company.com pointing to the server that is running MySQL. Later on we can point this to the MySQL cluster.

I opened port 3306 on the server, allowed the MySQL users to connect from any host (%) and added the bind-address = mysql.company.com setting to the my.cnf (I also tried with the actual IP of the server, didn't work either). Also omitting the bind-address setting didn't work.

From the server mysql --host=mysql.company.com --user=root --password works fine.

When testing with a simple PHP script (see below) that's running on the same server, I always get a Permission denied error for the FQDN as server name. Using localhost as the server name works fine.

<?php

$servername = "mysql.company.com";
//$servername = "localhost";
$username = "tester";
$password = "my_awesome_pass";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

What am I doing wrong to achieve what I need? And how can I disable MySQL connections to localhost (maybe with the firewall?).

As always, a few minutes after posting a question, I found the answer myself. The issue was SELinux. It was set on enforced; permissive made it work instantly. Now I'll have to figure out a way to make it work with the enforced mode.

The following appeared in the audit.log:

  type=AVC msg=audit(1429781752.260:6738): avc:  denied  { name_connect } for  pid=40354 comm="httpd" dest=3306 scontext=system_u:system_r:httpd_t:s0 tcontext=system_u:object_r:mysqld_port_t:s0 tclass=tcp_socket


select duplicated rows by modified_date - PGSQL 9.2 Query:

SELECT j.id INTO junk.ja_jobs
FROM (
  SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id
  FROM ja_jobs
  WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
  GROUP BY time_job, recurrenceid, clientid, creatortype, deleted
  HAVING count(*) > 1
  ) jd
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted)
WHERE j.id <> jd.id;

The query above gets all duplicated rows by time_job,recurrenceid and put the them into the temp table. Only the oldest ID stays in the ja_jobs table.

ja_jobs table has created_date and modified_date column, as you can see below:

Question:

Find duplicate recurring jobs with identical recurrencid and time_job
and creator_type = 'legacyrec'
If created_userid = modified_userid AND the difference between
modified_date and created_date is smaller than 10 seconds (since
created_date is not necessarily smaller for strange reasons), we
treat them as not modified
If there are matches in step 2, we keep all of them and delete the rest, and if there is no match, we keep the oldest one and delete the rest

How can I do that? How would the select statement be?

Here is what I did:

CREATE TABLE junk.ja_jobs_WM (
        recurrenceid integer,
        time_job int8,
        time_job_stamp timestamp,
        dupcount integer,
        most_recent_date timestamp
);

INSERT INTO junk.ja_jobs_WM
    SELECT
                recurrenceid,
                time_job,
                TO_TIMESTAMP(time_job),
                COUNT(id) as dupcount,
                MAX(modified_date) AS most_recent_date
        FROM
                ja_jobs
        WHERE
                (NOT (recurrenceid IS NULL))
        AND
                deleted = false
        AND
                (clientid = 15288)
        AND
                creatortype = 'legacyrec'
        AND
                (time_job >= EXTRACT('epoch' FROM statement_timestamp()))
        GROUP BY
                recurrenceid,
                time_job
        HAVING
                COUNT(id) > 1;

This question is now closed.


Same query, different result (depending on user's permissions) A question about SQL in general:

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Imagine user1 has more permissions than user2.

With other words: Is it possible that user1 receives 100 rows, and user2 only 80?

My question is about the SQL standard.

But feel free to speak about implementations of specific DB systems in comments.

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Yes, absolutely.

Row-Level Access Control allows the Database Administrator to put "contextual" filters on a table so that different users really do see different data.

For example, a Users table might be protected such that users in the Personnel department can see everyone but anyone in any other department can only see people in the same department.


SQL Server 2016 | MS Exam According to Microsoft computation here:

  The maximum compute capacity usage by one instance is limited to the
  lesser of 4 sockets or 24 cores.

To me a socket is the recepticle where a CPU is seated. In which case a socket and a core count aren't always equivalent. I know some motherboards can have multiple sockets but nowadays with the high core count on server CPUs this isn't the case normally.

Can anyone explain what this means?

(doing my Microsoft 70-762 developing SQL databases exam)

Note that this only applies to Standard Edition.

Assume you have four sockets in the motherboard. If each of those sockets is populated with a CPU with more than six cores, SQL Server Standard Edition will not use the additional cores, but take care. It does something really ugly:

CPU 1: 8 cores (all used) - running total 8
CPU 2: 8 cores (all used) - running total 16
CPU 3: 8 cores (all used) - running total 24
CPU 4: 8 cores (none used) - you will still have to license these and the load will be unbalanced.

Another scenario, with two sockets:

CPU 1: 16 cores (all used) - running total 16
CPU 2: 16 cores (8 used) - running total 24

In this second scenario the cores are also unbalanced (meaning it won't spread the load evenly across NUMA nodes), and you'll still have to pay for the 8 remaining cores even though you can't use them.

If you have no say in the purchasing of hardware, you can mitigate this balancing problem by running Standard Edition in a virtual machine, and assigning the right number of vCPUs accordingly.


How can I force a minimum isolation level in a procedure? How can I force a minimum isolation level in a procedure in mssql?

I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will downgrade the isolation level if the caller/executor uses e.g. "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;" How can I force a minimum isolation level without downgrading a higher isolation level?

What happens if I commit the "nested" transaction in my procdure? Will the locks acquired by the nested transaction remain until the outer transaction (of the caller) is committed?

There are a couple of options for determining the transaction isolation level in SQL Server depending on whether you want to include read committed snapshot as separate from read committed.  Once you know what isolation level your session is using, you can implement whatever logic you want.  Something like

IF EXISTS( SELECT 1
             FROM sys.dm_exec_sessions
            WHERE session_id = @@SPID
              AND transaction_isolation_level < 3 )
BEGIN
  set transaction isolation level repeatable read;
END;


MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time? I want to create a temporary table with some select-data.
My (strange) problem is that I have to do the same query more than one time.
CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;

CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;

Unfortunately, MySQL executes the second query although the temp-table already exists.
QUESTIONS

Is there a way to tell MySQL that the creation of the temporary table and the SELECT query are just executed if the table does not exist?
Maybe with an if-condition?
Or can I check to some information schema if a temp-table exists?
Thanks for some help!

Best regards,
Timo

Here is the nature of table creation with CREATE TEMPORARY TABLE

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

My guess would be you must have done the following sequence of events:

You ran CREATE TEMPORARY TABLE
Terminated DB Connection (which makes the temporary table drop immediately)
You had to run CREATE TEMPORARY TABLE again because he table did not exist

I have seen this problem victimize many developers, especially when it comes to MySQL Replication. For example, if you run STOP SLAVE;, the SQL Thread will close. Any tables create with CREATE TEMPORARY TABLE will be dropped. When you run START SLAVE;, MySQL Replication breaks instantly because the table needed for INSERTs, UPDATEs, or DELETEs no longer exist. I have had to perform mysqlbinlog dumps on relay logs to locate the original CREATE TEMPORARY TABLE statements, run them as CREATE TABLE in order for the table to remain accessible to other DB Connections, including the SQL Thread when START SLAVE; is reissued.
As for your Questions

Is there a way to tell MySQL that the creation of the temporary table and the SELECT query are just executed if the table does not exist?
Maybe with an if-condition?
Or can I check to some information schema if a temp-table exists?

You cannot check the information_schema for temp table existence. In the OS, there is no manifested .frm in datadir. You could find it in the folder defined in tmpdir.
Here is an example: I will create a temporary table called test.junk and try to locate the whereabouts of the table. (Note: I am using MySQL for Windows)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create temporary table junk (a int) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table junk\G
*************************** 1. row ***************************
       Table: junk
Create Table: CREATE TEMPORARY TABLE `junk` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select count(1) from information_schema.tables
-> where table_schema='test' and table_name='junk';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.08 sec)

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | C:\MySQL_5.5.12\data\ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| tmpdir        | C:\Users\redwards\AppData\Local\Temp |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

The table is not visible in INFORMATION_SCHEMA.TABLES. Let's locate the table in the OS
C:\MySQL_5.5.12\data\test>cd
C:\MySQL_5.5.12\data\test

C:\MySQL_5.5.12\data\test>dir junk.*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data\test

File Not Found

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.frm
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM             8,554 #sql7ac_3_0.frm
               1 File(s)          8,554 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.MY*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM                 0 #sql7ac_3_0.MYD
10/19/2012  11:04 AM             1,024 #sql7ac_3_0.MYI
               2 File(s)          1,024 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>

There is the temp table, in the tmpdir folder. When I closed the mysql client and run dir C:\Users\redwards\AppData\Local\Temp\*.frm, the table vanished.
RECOMMENDATION
You can do one of two things

Create the temp table, load it with data, keep the same DB Connection around, and then access the data with the same ProcessID.
Do CREATE TEMPORARY TABLE IF NOT EXISTS (This is really a band-aid to improper design but may circumvent the problem).

UPDATE 2012-12-18 13:06 EDT
Further Observation #1
If you stayed with the same DB Connection and still have this problem, here something else not previously mentioned: You gave the temp table an index. That should be a UNIQUE INDEX or PRIMARY KEY.
Further Observation #2
You could shift the operation from a CREATE TABLE ... SELECT to CREATE TABLE ... INSERT IGNORE
Further Observation #3
If you want to use temp tables, please specify MyISAM. InnoDB has a tendency to leave pigeon holes in the data dictionary with ibdata1 in the event of a crash or upon DB Connection termination.
With these three(3) observations in mind, here is my recommended change
CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

Give it a Try !!!


How to model schedule and keep good constraints? I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will be scheduled for the same store two days in a row, but I have to ensure that they are scheduled for no more than 2 days in a given period (or "cycle").

A spreadsheet representation of the data would look like this.

Cycle Team    Store   Date
1     1       1   1-Dec
1     1       1   2-Dec
1     1       2   3-Dec
1     1       2   4-Dec
1     1       3   8-Dec
1     1       3   9-Dec
1     1       4   10-Dec
1     1       4   11-Dec
1     2       10  1-Dec
1     2       10  2-Dec
1     2       11  3-Dec
1     2       11  4-Dec
1     2       12  8-Dec
1     2       12  9-Dec
1     2       13  10-Dec
1     2       13  11-Dec

I already have a Team/Store cross reference that works pretty well, but getting down to this level of granularity is new. It would be easy if I could just put a composite primary key on Team/Store/Date, but I obviously can't do that, or I would only be able to store one of the two scheduled days.

I've considered using a date table with a one to many relationship from Team/Store to date, but that doesn't seem to fix the uniqueness issue and adds what feel like silly overhead.

Does anyone have any advice on how to model this?

The tag doesn't seem appropriate, as this is really an abstract design question that I'm asking, but I am using SQL Server 2008.

I think this can be done with an additional column (say Day) that will be allowed to hold only 2 possible values (1 and 2).

Assuming a team cannot be in 2 or more stores in the same date, then you could have a UNIQUE constraint on (Team, Date) and another on (Cycle, Team, Store, Day).

The constraints would be:

UNIQUE CONSTRAINT uq_1
  (Team, Date),
UNIQUE CONSTRAINT uq_2
  (Cycle, Team, Store, Day),
CHECK CONSTRAINT ck_max_2_days_per_team_store_cycle
  (Day in (1,2))

and the data:

Cycle Team Store Date   Day
1     1     1    1-Dec   1
1     1     1    2-Dec   2
1     1     2    3-Dec   1
1     1     2    4-Dec   2
1     1     3    8-Dec   1
1     1     3    9-Dec   2
1     1     4   10-Dec   1
1     1     4   11-Dec   2
1     2    10    1-Dec   1
1     2    10    2-Dec   2
1     2    11    3-Dec   1
1     2    11    4-Dec   2
1     2    12    8-Dec   1
1     2    12    9-Dec   2
1     2    13   10-Dec   1
1     2    13   11-Dec   2


Oracle Database Remote Connection I've been searching online for a solution to my problem without any luck. I'm trying to connect to a remote server (~ 700 km of distance), but since I'm new in this Database administration world I'm afraid of messing this up. So I've been practicing with a VirtualBox machine (Windows 7) on my Ubuntu real Machine for almost a week and nothing seems to work.

This are the steps that I'm following to achieve the connection:

ON SERVER SIDE:

Start database: conn sys as sysdba.
Start LISTENER: lsrnctl start on cmd.
listener.ora:

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
      (PROGRAM = extproc)
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ON CLIENT SIDE:

tnsnames.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Start oracle client: sudo service start oracle-xe start
Try to connect: conn SYS/pass@HostName (tried with different users and host IP)

Please help!

Your connection string is wrong. With easy connect you need to provide a hostname and servicename/SID. In your case you would provide
sqlplus user/"SomePass"@HOSTNAME:$PORT/servicename

If you want to configure your connection in the tnsnames.ora, you can connect to it using sqlplus user/"SomePass"@TNSALIAS

I actually don't understand the part of your question with your local Oracle XE installation. If you only want to connect to a remote database, you don't need it. All you need is an oracle Client (Instant Client with sqlplus will do fine), or a tool like SQLDeveloper. If you are unfamiliar with Database administration a tool like SQLDeveloper might make life a little bit easier.


Obfuscate data during migration with MySQL Workbench Is it possible to obfuscate sensible data during migration using MySQL Workbench?

We are going to work on site so we need to make a copy for develpment but the (MySQL) database contains sensible user data that need to be obfuscated for obvoius privacy reasons.

I already used MySQL WB migration wizard so it's not new to me, but i can't figure it out if it can obfuscate/alter data during migration apart from columns/tables definition.

Maybe it just can't, maybe there are better tools/ways to do what i need, but using a tool i already know and that can automagically create sql/ssh scripts would be better of course.

Precisation

with 'obfuscation' i mean any avalable way to alter data.
E.g.:

transform user first_name property to something like <user_id>_first_name
change any login password to a standard one
replace fiscal code with a random string
etc

No, that's not possible. Better you do the migration first and then run a script over the copy to obfuscate fields.


How to backup Oracle Production Database without stopping service? I have a Oracle 18XE database

I would like to create a backup for the database.

I did :

expdp sysdba full=y dumpfile=expdat.dmp directory=DatabaseDumps transportable=always logfile=export.log

But this command asks for the tablespaces to be in readonly. Wich I can not do in a production database running 24h/7.

Is there any other way to backup a database whitout stopping it or making it readonly ?

Thanks.
Regards,

The best tool to create a backup database is rman.

sqlplus "/  as sysdba"
ALTER DATABASE FORCE LOGGING;     -- If you have tablespaces with nologging option
alter system checkpoint global;   -- To flash sga data to disk before backup
ALTER SYSTEM SWITCH LOGFILE;      -- To do a whole cycle of redologs
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
rman target /
-- to open a channel to write the backup
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/internal/backup/RMAN_001/full01_%I_%n_%T_%u_%s_%p';
-- to ensure the controlfile backup
CONFIGURE CONTROLFILE AUTOBACKUP ON;
-- to open a channel to write the oracle controlfile.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/internal/backup/RMAN_001/%F_Autobackup'; # default
-- backup database with controlfile and archivelogs so you will recover the database to a specific point in the time between backup and backup
BACKUP AS BACKUPSET DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG TAG = 'RMAN_001';


Proof for this answer I am not sure whether this is offtopic. However, please read on.

A person whom I know has encountered a problem in the answer key published by CBSE, India. As their key challenging form contains a column for citation/proof, we have to find some authentic book which can be shown as the base of our claim.

I attach the question here, and I request you to verify the answer and suggest us some book/other ways to prove our challenge (She has checked the regular syllabus books, but didn't find something precise related to this problem).

Question:

Identify the minimal key for relational scheme R(A, B, C, D, E) with
functional dependencies F = {A->B, B->C, AC->D}

The options are A, AE, BE, and CE

The key shows the answer is A and she says it is AE actually.

Please help us find some proof/way to challenge the key.

The key is actually AE. The proof is simple, a key for definition is a minimal set of attributes whose closure contains all the attributes of the table. If you calculate the closure of A with respect to the given functional dependencies you will find:

A+ = {ABCD}

that does not contain the attribute E. So A is not a key, and E must be present in any key of R. And since:

AE+ = {ABCDE}

then AE is a key, and it is minimal (you cannot remove any attribute from it without losing the property of determining all the other attributes of the relation).


Capitalize only the first letter of a name I have a table Vendors that contains a column VendorContactFname, which
is written in lowercase.

I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first letter to the left, with all other remaining letters to its right in their original lower case, i.e., with the substring:

Upper(Left(VendorContactFname,1) ) +
Substring('VendorContactFName',2, Len('contactFname')-1 )

Is there a better way to do this?

Another way is to use STUFF to replace the first character directly:

-- Demo
SELECT
    STUFF
    (
        O.name,
        1, 1,
        UPPER(LEFT(O.name, 1))
    )
FROM sys.objects AS O;


Finding temporary table name in MariaDB As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.

Our temp directory is filling with .MYD/.MYI files up to the point that the process file descriptor limit is reached, for example:

-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b18.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b18.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b19.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b19.MYD
-rw-rw---- 1 mysql mysql  189376 Oct 24 13:25 #sql324a_1a28c_e4b0c.MYD
-rw-rw---- 1 mysql mysql    5120 Oct 24 13:26 #sql324a_1a28c_e4b58.MYI
-rw-rw---- 1 mysql mysql    3575 Oct 24 13:26 #sql324a_1a28c_e4b58.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:26 #sql324a_1a28c_e4b59.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:26 #sql324a_1a28c_e4b59.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYI
-rw-rw---- 1 mysql mysql    1204 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYD
-rw-rw---- 1 mysql mysql 1256372 Oct 24 13:28 #sql324a_1a28c_e4ae3.MYD

After that, the only solution is to stop the mysqld process and then start it again.

Of courdse, we have checked on the master and these same files are not leaked or stale.

The question: does anyone know how to find some information of these temporary tables (at least their names) to help in debugging this case with more detail?

Thanks

I ask about the PROCESSLIST in hopes of discovering whether only certain types of queries cause the leaked tmp table.  Knowing that might expedite finding a solution.

Based on a recent comment in the bug report, it sounds like a cron job doing this might be a workaround (but apparently not):

STOP SLAVE;
START SLAVE;


What is a good strategy for updating lot of rows in postgersql? I have an inventory table which contains item_id and the quantity remaining of the item (And also some other meta data). I have an administrator update the inventory by uploading a CSV file which contains the item_id and the quantity remaining.

Run `update for each row in the CSV file. If my CSV file contains 1 million rows, I will end up sending 1 million update statements from my application server to the database server.
Construct 1 million update queries and send them in a batch (JDBC allows batched statements)

At first glance approach number 2 looks like a better solution. But then can 1 million statements be batched? What happens if one of the statement fails for some reason?

The usual way is to import the CSV file into a staging table. Either an unlogged table which you only create once, or a temp table which you  create immediately before the import.
Something along the lines:
create temp table inventory_import (item_id integer primary key, quantity integer);
copy inventory_import from '/path/to/file.csv' ... ;

update inventory i
  set quantity = im.quantity
from inventory_import im
where i.item_id = im.item_id;


What's the complexity of renaming a column in MySql? We want to rename a column in our MySql (version 5.5) database with something like this:

ALTER TABLE t1 CHANGE a b INTEGER;

Is this an O(N) (N=number of rows in the table) or an O(1) operation? I can't seem to find an answer in the manual nor on Google. The table has millions of records, and it wouldn't be feasible directly if it's O(N).

It can be an O(1) operation if only the name is changing, see:

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

and search for:

  Renaming, Redefining, and Reordering Columns

That is in a section that explains that the operation only modifies table metadata.

Back in MySQL 5.5 the manual says it works there too, except for InnoDB tables.


Is the following index is good? What is the proper indexing plan for the below query?
Query 1 :
select id
from room_participants
where roomId=1
  and userId=1
  and status=1
  and isMuted=0

Query 2 :
select group_concat(b.phone)
from room_participants as a
left join users as b on b.id=a.userId
where a.roomId = 1
  and a.status = 1

Is it an advisable way to do indexing as per my example below? Does this is what we called redundant index as index combine3 is having the same column as index combine4 and index combine2 is having the same column as index combine1?
CREATE TABLE `room_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `isAdmin` tinyint(1) DEFAULT '0',
  `isMuted` tinyint(1) NOT NULL DEFAULT '0',
  `joinTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `leftTime` timestamp NULL DEFAULT NULL,
  `kickTime` timestamp NULL DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=join, 2=kicked, 3=left',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `combine2` (`isAdmin`,`userId`,`joinTime`,`leftTime`,`kickTime`,`status`),
  KEY `combine1` (`kickTime`,`userId`),
  KEY `combine3` (`roomId`,`userId`,`status`,`isMuted`),
  KEY `combine4` (`roomId`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=567 DEFAULT CHARSET=latin1;

Please guide me on this. Thanks.

Optimal for both queries:
INDEX(roomId, status,   -- first, in either order
      userId,           -- 3rd
      isMuted)

Notes:

In InnoDB, the PRIMARY KEY is tacked onto the end of each secondary index (so it can get to the record).
All 5 columns (including id) are in that index, so it is "covering" for the first query.
In both queries, the tests in the WHERE clause are completely handled by the first few columns.
In the 2nd query, userId is available, so the index is covering for that query, too.  (The extra isMuted` is not harmful.)
I assume that id is the PK of b, so that is optimal, too.
If you add my index, drop combine4.  (The left part exactly matches.)
More:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


SA account locked out then unlocked somehow This morning I noticed that our sa sql server account was locked out, and then somehow unlocked.

9/27/2019 5:28:26 AM - Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: local machine]
...
9/27/2019 5:28:47 AM - Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.  [CLIENT: xx]
...

I replaced the client IP with xx for posting here. Then at 5:45 AM EST the account was unlocked somehow and the errors stopped.
We also receive few of these messages during the time the account was locked

The client was unable to reuse a session with SPID 413, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
to the user Domain\user SID (S-1-5-21-1482476501-1715567821-725345543-24374) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

It looks like someone was on the server and trying to login as SA. We checked internally and everyone says they were not, we have a consultant at the moment who has his own sql server id, we asked him and he says he was not trying to login as SA during the time frame. It looks like he was logged in at the time this happened because his username is what is shown in that event log message regarding 'application-specific permission'. The consultant does not have the SA credentials.
Is there anyway I can check who was trying to login as SA and how the account ended up getting unlocked? Is there any auditing I can setup for the future in case this happens again? This is the first time we have seen this.

Thanks Ross and James for input in the comments. I found that our sa account has enforce password policy turned on, and our local security policy will unlock an account after 15 mins, so that answers how it got unlocked. Regarding future I think server audit of login failed event class will help us catch more information if this happens again. Internally we have a goal to move away from using sa and rename/disable it.


How to Permanently solve MySQL error 2002 (HY000) I have a problem on my cloud server with 2GB of RAM and 40GB Hard Disk that's hosting a Wordpress website that receives around 1000-2000 hits a day. My problem is that MySQL is always crashing and my only option is to reboot the server. I noticed that logs were not enabled so I enabled them and here is what i found:

150207 17:31:42 [Note] Plugin 'FEDERATED' is disabled.
150207 17:31:42 InnoDB: The InnoDB memory heap is disabled
150207 17:31:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150207 17:31:42 InnoDB: Compressed tables use zlib 1.2.3.4
150207 17:31:42 InnoDB: Initializing buffer pool, size = 1.2G
InnoDB: mmap(1285636096 bytes) failed; errno 12
150207 17:31:42 InnoDB: Completed initialization of buffer pool
150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150207 17:31:42 [ERROR] Plugin 'InnoDB' init function returned error.
150207 17:31:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150207 17:31:42 [ERROR] Unknown/unsupported storage engine: InnoDB
150207 17:31:42 [ERROR] Aborting
150207 17:31:42 [Note] /usr/sbin/mysqld: Shutdown complete

I am guessing this has something to do with memory allocation or some settings in my my.cnf file. These are the contents:

[client]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                         = /var/run/mysqld/mysqld.sock
log_error                      = /var/log/mysql/mysql_error.log

[mysqld]
user                           = mysql
pid-file                       = /var/run/mysqld/mysql.pid
socket                         = /var/run/mysqld/mysqld.sock
port                           = 3306
datadir                        = /var/lib/mysql
thread_cache_size              = 4
table_open_cache               = 800
table_cache                    = 800
key_buffer                     = 32M
query_cache_type               = 1
query_cache_size               = 64M
query_cache_limit              = 8M
innodb_buffer_pool_size        = 1G
innodb_io_capacity             = 2000
innodb_read_io_threads         = 64
innodb_thread_concurrency      = 0
innodb_write_io_threads        = 64
log_error                      = /var/log/mysql/mysql_error.log
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/ib_slow_log.log

Any help will be greatly appreciated.

You have a memory problem, something very typical in a memory-bound system, and that can be confirmed on the line:

150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool

As InnoDB cannot allocate memory for its buffer pool, it fails, and MySQL cannot start if the InnoDB engine fails (MySQL 5.5).

Nothing on your my.cnf seems out of the ordinary, but given that you only have 2GBs of ram, and if I understand it correctly, that is shared with the OS and a PHP/Wordpress installation, you have to be very conservative. MySQL is already taking 1.2 GB for the buffer pool, you should monitor the memory usage (top, ps) and making sure MySQL is the process to blame (compared to, for example, Apache). If it is, reduce your innodb_buffer_pool_size and/or some of the other buffers, such as the key cache or the query cache. I would set the buffer pool to ~500MB for this non-dedicated server.

You seem to be running 5.5, for other folks running 5.6, one of the main causes I have seen in MySQL support regarding out of memory killings in small-memory systems is the extra static memory needed for the performance_schema. In those cases, I recommend disabling it or reducing max_connections to avoid too much overhead.


How to enable a SQL Server 2012 login to create, alter and drop the created databases? I'm trying to assign a proper GRANT permission to CI login, that will be able to:

Create test DB
Alter it
Drop this test DB (and only it)

But it appears that SQL Server 2012 GRANT CREATE ANY DATABASE only allows the creation, not the altering and dropping of the database (as the newly database does not hold the creator as dbo).

As just giving a sysadmin role is too large of a security risk, any idea what other GRANTS are required?

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it  for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop

EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))

EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin]
set online

Below is the error :

  Msg 5011, Level 14, State 9, Line 2
  User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.


Information schema not showing reliable information? I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information and the source information.

However, I did a select count(*) from table; gave me the correct number of rows

Why is there a difference between the information_Schema.tables.table_rows and the select count(*) from table?

The root cause is the storage engine. InnoDB does not update the information_schema.tables.table_rows column.

I have old posts that explain this further

Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
May 16, 2012 : Why doesn't InnoDB store the row count?
Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table

You can get an approximation using SHOW INDEXES FROM against the table

If you would like to count all the tables in your current database regardless of the storage engine, just run this:

SET group_concat_max_len = 1024*1024*1024;
SELECT GROUP_CONCAT(sqltocount SEPARATOR ' UNION ') sqlcount INTO @sql
FROM (SELECT CONCAT('SELECT ''',table_name,''' tablename,COUNT(1) RowCount
FROM ',table_name) sqltocount FROM (SELECT table_name FROM
information_schema.tables WHERE table_schema=DATABASE()) AA) A;
SELECT @sql\G PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

GIVE IT A TRY !!!


Is it normal to share CPUs on a VM of SQL Server? Our IT set up a SQL Server as a VM on a big VMWare box that contains other VMs.
The CPUs are setup as shared.  As a result, any queries that might need multiple CPUs take 30x longer than if I limited it to a single CPU.  Example:

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

vs

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp
OPTION (MAXDOP 1)  ------------- Force it to run on a single CPU

1st example uses parallelism and takes about 30 seconds or so.  2nd one forces use of a single CPU and take 20 milliseconds.

Note: after running the single CPU query I go back to the running the multi-cpu one and the timing & plan is the same - so I don't think the issue is related to "cold cache" vs "warm cache"

So my theory is that because first query uses multiple CPUs, it must wait till all CPUs in question are idle, and therefore it just waits.

So my question.  Should SQL Server VMs have dedicated CPUs or shared ones are normal?

Here is the plan which uses parallelism.
Here is the plan which forces a single CPU.

Is it normal to share CPUs on a VM of SQL Server?

Yeah, it's pretty common.  A lot of times VMs are used to consolidate lots of SQL Servers (especially ones that don't have extreme performance requirements) onto one host.  This can save on licensing costs, since SQL Server can be licensed at the host level.

Is it a good idea?  I mean, it depends a lot on how oversubscribed the VM is, and how CPU intensive the workloads are.

Looking at the screenshots of the two execution plans, they are basically the same except for the parallelism.  One problematic area in the parallel plan is the serial zone where the "Top" operator lives:

There is some overhead associated with bringing all the rows together onto one thread, and then redistributing them for the parallel insert.  I wouldn't expect that overhead to be 30 seconds though.

  So my theory is that because first query uses multiple CPUs, it must wait till all CPUs in question are idle, and therefore it just waits.

No, that's not how parallelism works in SQL Server.  The threads that are scanning the clustered index at the top-right of the plan can do very uneven levels of work depending on how busy the different CPUs are.

Now, if this instance of SQL Server is so busy that all of the available threads are being used for other queries, then the parallel query might be waiting on THREADPOOL.  Which brings me to my next point:

The parallel query is most likely waiting on some resource.  I would start by looking at the "WaitStats" portion of the execution plan in SSMS:

That'll be in the "Properties" window of the left-most operator in your plan.  As one example, a really high SOS_SCHEDULER_YIELD value in this case could be a sign of this SQL Server instance not getting a turn on the hosts CPU.  Jonathan Kehayias has a really good post on that topic here:

CPU Ready Impact on SOS_SCHEDULER_YIELD

You could also compare the ratio of elapsed time to CPU time in the two queries.  Those numbers are in the same properties window:

If the ratio is significantly different between the two queries, that's another sign that the parallel query is waiting on some resource.

If you have access to the host / virtualization, you could look into stats directly there to see if guests are waiting long periods to be scheduled on the CPU.  Jonathan has another post about that here, which is specific to VMWare: CPU Ready Time in VMware and How to Interpret its Real Meaning


How to model that several variants of a merchandise item may affect its price? I am an absolute beginner to relational data modelling. I hope my question will be acceptable here nevertheless.

Let’s say there’s a merchandise. An item of merchandise may have variants.
(While it also has attributes common to all variants).
I don’t want to limit in the data model what the variants can be.
I know the merchandise can vary in size and in color, but I don’t want the model to be limited to these two possible variations. There might be other variants (such as in material or process).
It is not clothing, and the variants may be priced differently.
While I expect mostly sizes to affect the price, I don’t want to impose any such limit.I also wish to anticipate that several variants could combine to affect the final price. Say that a different process could affect the price and different sizes too but colors not. But I want to avoid any fixed limitations in the model.This is what I don’t know how to model properly.

(BTW, variants are identified as such by adding a variant code to the base of their SKU.)

If the variants are identified by a variant code at the base of their sku, is the variant code always in the same place?  If so, you can always group together with the base sku.

However, you might want to look at product grouping.  As an example, if your catalog of products is exercise products.  And you have fitbits.  The Fitbit flex can be sold with different colored bands.  Each one would have a sku of 1234 with a letter variant.  So bl for black, bu for blue, pk for pink.  so 1234bl is black fitbit flex.

Now you can set up categories.  So the next level would be the type of fit bit.  You also carry the fitibit Charge.  That sku is 2345.  So one item can have a sku of 1234bl, a subcategory of Fitbit, a category of pedometer, under the store area of technology.  And you can group the data at those various levels.  A vp of Electornics might only care about the Category and the technology level.  Yet a Category manager cares about the sub category and category.  They may also care at the sku level.

Does this answer what you are looking for?