Replication with MySQL 5.6 as the master and 5.7 as the slave When trying to do replication with 5.6 as the master and 5.7 as the slave, after source dump.sql I entered this;

mysql> CHANGE MASTER TO MASTER_HOST="<MASTER_HOSTNAME>", MASTER_USER="slave_user", MASTER_PASSWORD="password", MASTER_AUTO_POSITION = 1;

obviously I changed  to an actual hostname, I got the following error;

ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

Is this a problem with using 5.6 as the master and 5.7 as the slave?
I have tried the dump several times with the same error. Although is says it's supported here http://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html

Drop the mysql.slave_relay_log_info table on the 5.7 slave. Log out of mysql, run mysql_upgrade -uroot -p -s to recreate the dropped table. Log back in to mysql instance then stop slave / start slave all should be good.

The problem was when you source dump.sql it somehow changed the table definition in 5.7 hence the error you were getting.

A simple test I tried was on a working 5.6 master to 5.7 slave repl, I dropped the slave_relay_log_info table on 5.7 and recreated it with 5.6's table structure and got the same error when I started slave replication.

mysql [localhost] {msandbox} ((none)) > use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {msandbox} (mysql) > CREATE TABLE `slave_relay_log_info` (
    ->   `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
    ->   `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
    ->   `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
    ->   `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
    ->   `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
    ->   `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
    ->   `Number_of_workers` int(10) unsigned NOT NULL,
    ->   `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (mysql) > start slave;
ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

EDIT:
You can drop the table with the wrong structure, and recreate it with this:

CREATE TABLE `mysql`.`slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
  PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';


MySQL 5.6 events are created disabled and they don't run I am using the normal syntax to create an event, but they are created disabled and they never run:

create event p1 on schedule at now() on completion preserve enable do call proc1();

I added enable although it is default to solve this problem, but no good as well, and I need to preserve the event such that I can find it show events / select * from information_scheme.events;

I appreciate your help very much with any privileges required, configuration, or syntax to solve this problem.

You didn't specify the interval

create event p1
    on schedule
    every week
    at now()
on completion preserve
enable do call proc1();

I have several examples of how to set up events

BTW make sure you enable the croning mechanism in my.cnf by adding this line

[mysqld]
event_scheduler = 'ON';

You don't have to reboot. Just run this

mysql> SET GLOBAL event_scheduler = 'ON';

As the last step, you can create the event.


How do I set up privileges for different schemas? How do I set up 2 schemas with a total of 5 users?
Schemas:

hp
hpc

Users:

dba - has full access to both schemas (hp, hpc)
hp_dbo - owns all objects in hp schema
hpc_fbo - owns all objects in hpc schema
hp_application_user - has select, create, drop, alter, execute privilege in hp schema
hpc_application_user - has select, create, drop, alter, execute privilege in hp schema

How do I realize this privilege structure? I was playing around with MySQL-Workbench but I ended up setting wrong permissions and losing access to my database for my application...

You can always have root as user t access when all fails, so don't touch the user and give a string password.
Besides that you can always recover whwn you log in as su or administrator see manual
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'dba '@'localhost';
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc. * TO 'dba '@'localhost';
CREATE USER 'hp_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'hp_dbo'@'localhost';
CREATE USER 'hp_application_user  '@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hp_application_user '@'localhost';
CREATE USER 'hpc_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc.* TO 'hpc_dbo'@'localhost';
CREATE USER 'hpc_application_user'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hpc_application_user`@'localhost';

If you need more privileges for the user hpc_application_user or hpc_application_user
you can use the same command with the privilege you want to add or revoke
If you have multiple user with the same privileges you can define a role


LOB indexes corruption in Oracle Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.


SQL Server Agent - Report Failure but continue When intermediate step fails I have a SQL Server Agent job that has three steps with the following control flow:

Step 1 - on success - Go to next Step. on fail - job fails
Step 2 - on success - Go to next Step. on fail - Go to next Step
Step 3 - on success - report success, on fail - report fail

However, What I want to happen is, if step 2 fails, run step 3 but report that the job has failed (regardless of whether step 3 is successful or not)

The only way I can think to do this is as per the screenshot below which duplicates the final step but the duplicate step reports failure if it succeeds

Is there a better way of doing this?

What I usually do is just add one more step "Error" between step #2 and #3 that sends an  email.
Something like this:


Sql server snapshot isolation overhead when read_committed_snapshot is enabled When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation?

Snapshot isolation causes some overhead by making rows a little larger and requiring some tempdb to store previous versions until commit, even for non-snapshot transactions.  As I understand it, this infrastructure is also necessary for read-committed-snapshot.  Can I assume therefore that enabling snapshot isolation has no additional overhead for non-snapshot transactions whenever read committed snapshot is already on?

The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.


Where is the cache for the MySQL caching_sha2_password auth plugin stored? Where is the cache for the new default authentication plugin caching_sha2_password in MySQL 8 stored? I want to know for future reference how to clear it in case I ever run into any problems using it.

'when there exists a cached copy of the password hash in memory' (ref).

So its all in memory.

FLUSH PRIVILEGES can clear it


How to serialize a record / row in SQL? Is there a way to serialize a record/row in SQL? And conversely, to deserialize something that has been serialized.

By serialize, I mean put all of the fields in a record into one field or string.

Preferably with MySQL compatibility.

I am guessing that xml, a udf or stored procedure might be necessary.

is there a way to use xml or json directly in sql/mysql?

Thanks!

I feel my answer may lack what you are looking for but with out more context here are a couple options:

Use the mysqldump command.

mysqldump -u user -pPassword   --no-create-db --no-create-info --where="primary_key=N"

will give you a sql insert command which you can use to reconstruct that row.

run a query like

select * into outfile '/tmp/rowdump.data' from table where primary_key=N

This will create a file /tmp/rowdump.data in tab separated format.  This can be reimported with

load data infile '/tmp/rowdump.data' into table `table`;

If these don't suit your needs please elaborate on your use case on what you are trying to accomplish.


MySql Query for Max(Date) not Today I have the following table:

Name -   Date
Adam - 7-13-19
Stan - 7-13-19
Adam-  7-11-19
Bob -  7-10-19
Stan-  7-10-19
Eric-  7-10-19
Lucy-  7-09-19
Eric-  7-09-19
Bob -  7-09-19
Lucy -  7-08-19

I would like to pull each unique name (which I'm using GROUP BY to do) and the latest corresponding date for each name (which I'm using Max(date) to do).  However, I don't want the name to appear if the max(date) is equal to today's date (7-13-19).  So the end result I'm looking for is:

BobEricLucy

The list would not include Adam or Stan...since they both have max dates of 7-13-19.  I've tried a whole bunch of queries, but can't get it to work.  My initial thought was something like this:

SELECT *, max(date)
FROM table
WHERE max(date) <> date(now())
GROUP BY name

Any help would be greatly appreciated.

If you want to use aggregate results as a filter, you need to use the HAVING clause.

Something like this should work for you.

SELECT name
    , MAX(date) AS max_date
FROM table
GROUP BY name
HAVING MAX(date(date)) <> DATE(NOW())


visual studio 2017 vs sql management studio I just installed Visual Studio with Data Tools and removed my previously installed Management Studio, due disk constraints, and the transition has not been as easy as I expected.

Most of the time I am programming web and VS2017 is great on it. I used SSMS, though, for developing stored procedures and some power user work, as profiling and ad-hoc queries. The problem is that I don't know where to find the tools I was used to (profiler?!) and the organization of the environment doesn't feel natural for working with my utility scripts on the existing database...

So, is there a way for using VS2017 on this context or should I reinstall SSMS?

Although Management Studio is based-upon Visual Studio, the two are different products and have different target audiences. You will not be able to use Visual Studio to do all of the tasks that you used to use Management Studio for. Even installing the data tools and creating a SQL Server project within Visual Studio will not give you the functionality available in Management Studio.

Visual Studio is primarily used to make development easier whereas Management Studio is orientated towards making the management of SQL Server easier (with some development components.)

Like I tell a lot of the guys that work with me, choose the right tool for the job. I have both installed, though I do agree that they use a hefty amount of space when installed together.


Does PostgreSQL support online schema modification (DDL)? Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table.

Does PostgreSQL support this to any level? That is, can I add/drop columns from a table while I concurrently read/write rows to it (even if performance is degraded)?

TL;DR: No, except for some basic cases.

Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below.

Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done with a very short exclusive lock even in older versions. A brief moment is needed where queries don't run, but it's almost instantaneous, since no table rewrite is required.

There are some things that could be optimised, like ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL, by storing the default for old rows in the table metadata and looking it up when reading old rows. This has not been implemented in PostgreSQL.

In theory PostgreSQL could use its MVCC catalogs to support what you describe by background-writing new versions of rows into a table while continuing to use the old versions for queries. I'm not aware of anyone attempting to do that, and it'd require some special handling because PostgreSQL's code currently assumes that table rows only have one possible structure at any given time.


Querying an enormous table (~150 million rows) with multiple joins - SQL Server First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes set up on both tables using the join variables in the order in which they appear in the join.

Here is the code:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate >= md.EffectiveDate OR md.EffectiveDate IS NULL)
    AND (rt.EditEffectiveDate < md.ExpirationDate OR md.ExpirationDate IS NULL)
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

#RatingDataTemp1 is 250K rows, and modelingdata is 150M rows. This join currently takes 4 minutes, but I really feel it should be faster. Any way I can speed it up?

Try running the cases seperately.  If that's faster you can work on combining them into one query.  eg something like the not strictly-equivilent:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md
    ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate >= md.EffectiveDate )
    AND (rt.EditEffectiveDate < md.ExpirationDate )
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

and

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md
    ON md.BranchID = rt.BranchID
    AND md.EffectiveDate is null
    AND md.ExpirationDate IS NULL
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

And if the target date range is small, consider changing the #RatingDataTemp1 to have one row per day per BranchID and doing a straight inner join on both columns.


Using fulltext search to find a sequence of digits We have a column of ten digit numbers (with leading zeros). We are trying to find rows that have a specific sequence of digits.

For example, all rows containing "5088" in any place in the column.

Can full text search (FTS) help us in this case?

We tried other methods such as LIKE, but the performance was very poor.

FTS is language specific; it can help you to find stuff in SQL Server which is specific to a language rather than treating it just like any other string in the databases.

5088 is a base 10 number, I don’t think FTS will be of much help here. I think you’re only option is to use the LIKE operator.

To learn more about why your query is running slow with like operator read Sargability: Why %string% Is Slow article by Brent Ozar.

Alternatively, if you always look for a specific value in the column in question, you can make use of the Computed columns, something like...

Test Table

CREATE TABLE #Test (ID INT , Value VARCHAR(100))
GO

INSERT INTO #Test VALUES (1 , 'ABC'), (2 , '') , (3 , 'EFG')
                       , (4 , 'A123BC'), (1 , 'AB123C') , (1 , 'ABC123')
GO

Let's say you are only interested in the rows where it has a value of 123 and this the only value you always check for, then you can add another column (Computed) to the table, something like...

ALTER TABLE #Test
ADD IsValid  AS (CASE WHEN Value LIKE '%123%' THEN 1 ELSE 0 END)
GO

And probably index the column too, something like....

CREATE INDEX NIX_IsValid_Record
ON #Test (IsValid)

Now your query for records with 123 string should be...

Select * from #Test Where isValid = 1

Even though you are looking for records with a string 123 anywhere in there but you will benefit from the Index on isValid column.


SSAS -> New cube contains two fact tables. How do I relate these so I can use measures from both at the same time? I am working on a new cube.  I know that you cannot directly create a relationship between two fact tables (and expect good results).

I am just wondering how to go about doing this I have read around and looked at examples, but I am just having difficulties wrapping my head around how this works.

My example would be these two fact tables.

Fact Table A:

FactDeliveryDriver (example measure: years of service)

Fact Table B:

FactDeliveries (example measure: miles)

Each of these fact tables contain measures specific to them.  I am required to have these two both in the same cube.  So I have a primary surrogate key in factdeliverydriver called driverSid and a primary surrogate key in factdeliveries called deliverySid.  I also have the driverSid in factdeliveries even though I know I cannot directly link them.

I am guessing that I need some sort of bridge table.  Again, I have read up on these but haven't really had much luck finding anything that shows how this would work with two fact tables.  Maybe I have been looking at this for too long.

Any direction/advice is greatly appreciated.

Thanks.

You're focusing on facts and forgetting about dimensions.

Years of service is a measurable attribute of a Driver, so, you should have a Driver dimension associated with the FactDeliveryDriver fact.

driverSid is a surrogate key for the Driver dimension, and not for the fact table. For the fact table it's a lookup column.

Also, deliveries are (probably) made by a Driver, so you should have the Driver dimension associated with the FactDeliveries fact as well.

There should also exist a Delivery dimension, that is associated with the FactDeliveries.

A simple schema should be something like:

Driver(sk_driver, name)
Delivery(sk_delivery, address, date_delivered)
FactDeliveryDriver(fk_driver, val_years_of_service)
FactDeliveries(fk_driver, fk_delivery, val_miles)

Since the Driver dimension is common to both fact tables, you can use the Driver dimension to relate measures from both of them.


MySQL Question: ORDER BY /// DATEDIFF There's two columns dateRequested and dateCompleted in DATE type and I want to select only 4 of the rows with the least time between the two.

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
ORDER BY Time;

With the above statement I get what I want but since there are nulls and in the case of the question for the sake of a challenge I guess there is negative time between the two so I really only want those where Time >= 0

I've tried with a CASE statement but I don't fully grasp that yet so any help would be appreciated.

Thanks

In this case I would use a where clause to further qualify the data you are getting back in this case something like:

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
WHERE ((dateCompleted IS NOT NULL) AND (dateRequested IS NOT NULL))
ORDER BY Time;

Since you said you are also trying to return only 4 rows you should use LIMIT 4 in your query, however I'm not sure if that would go before or after your ORDER BY.

If you have tried some things, let us know what you have already tried and what the errors are, this can help prevent people from reinventing the wheel and get your problem fixed faster.


Bulk Inserts in Postgres This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be migrated as the Big Bang approach.
In oracle database, I used to use the below template of code for migration -
create or replace PROCEDURE PRC_TEST
AS
DECLARE
    CURSOR CUR
    IS
    SELECT ID,NAME  FROM test;
TYPE test_typ IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
test_tbl test_typ;

BEGIN
OPEN CUR;
LOOP
FETCH cur BULK COLLECT INTO test_tbl LIMIT 1000;
DBMS_OUTPUT.PUT_LINE(test_tbl.COUNT);
     FORALL I IN 1..test_tbl.COUNT --SAVE EXCEPTIONS
                INSERT
                INTO test1(ID,NAME)
                VALUES
                (
                test_tbl(I).id,
                test_tbl(I).name
                );

       FORALL I IN 1..test_tbl.COUNT
                  UPDATE test1
                  SET name     = name||test_tbl(I).NAME
                  WHERE id =test_tbl(I).id;

                DBMS_OUTPUT.PUT_LINE('AFTER'|| test_tbl.COUNT);
        EXIT WHEN CUR%NOTFOUND ;
     END LOOP;
     commit;
 close cur;
 EXCEPTION
      WHEN OTHERS THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
        dbms_output.put_line('error'||sqlerrm);
        END LOOP;
END;
End PRC_TEST;

Is there a plpgsql equivalent code available for the same?
What approach to be used in Postgres while handling such migration activity ? please provide some best practices to be followed for better performance and handling/storing the error records in Postgres.
Thanks..!

I would convert this into a single INSERT statement using on conflict do deal with the "insert or update" part:
insert into test1(id, name)
select id, name
from test
on conflict (id) do update
  set name = excluded.name;

This assumes that there is a unique index (or constraint) on test1.id


Auto-Shrink, Encryption & Recovery Model properties in SQL Server 2008 R2 I am creating a new database in SQL Server 2008 Express R2. In the New Database dialog, I could see an option to set the Recovery Model, which is by default set to: Simple. If I set it to Full, what will happen?

I also see properties like: Auto-Shrink and Encryption. I checked online help for Auto-Shrink property where it shows information about the Compact Edition. But my database will reside on the Server and it is not a compact db. Should I enable it? What are the advantages of enabling it on a server-class database?

I also want to use the Encryption property but I worry that it might make the database unreadable if not handled correctly.

Please throw some light on these.

The simple recovery model means that you don't have the option of point in time recovery - essentially, if you take a full backup of the database each day, any data you enter after the backup might be lost if there was a problem. You'd restore to how the data was in the last good backup. As long as your data doesn't change that much between full backups, this is the simplest solution.

Full recovery means you also need to take regular transaction log backups as well - it allows you to restore to a point in time if there is a problem. Typically, you might take a daily full backup of the databases, and transaction log backups every hour. The problem is that if the transaction log backups fail, the transaction logs themselves will grow - and fill the discs if it's not fixed.

Auto-shrink is a setting where SQL Server will trying to shrink the database files when it is idle. It causes performance havoc, and will be removed in a future SQL Server release. Don't use it! (gbn added bold!)

Encryption is a bit more complicated, and best left alone unless you really understand all that is involved IMHO.


Troubleshooting extremely long running query (SQL Server 2012) We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours).  A week ago, this same query completed in less than a minute.  I've restored the DB from last week to confirm the end user's story, and sure enough, even with similar row counts with last weeks and yesterday's backup, the difference in query run time is there.

Table1 contains 1.4 million rows.  Table2 contains 16k rows.  In this case, none of the records from Table2 exist in Table1, so when the query is actually allowed to complete, no records are returned.

The table schemas are identical, with the exception that Table1 has a non-unique, non-clustered index on Field10.  There are no other indexes that exist on either table.  All fields in the join are (NVARCHAR(40), null) types.

The query looks like this:

DELETE Table1
FROM Table1 t1
INNER JOIN Table2Temp t2
    ON      t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
        AND t1.Field3 = t2.Field3 AND t1.Field4 = t2.Field4
        AND t1.Field5 = t2.Field5 AND t1.Field6 = t2.Field6
        AND t1.Field7 = t2.Field7 AND t1.Field8 = t2.Field8
        AND t1.Field9 = t2.Field9 AND t1.Field10 = t2.Field10
        AND t1.Field11 = t2.Field11

If I simply do a "SELECT * INTO Table3 FROM Table1" and "SELECT * INTO Table4 FROM Table2" and repeat the query about, substituting Table3 for Table1, and Table4 for Table2, the same query (against the new tables) takes 4 seconds.

I'm not an expert at Execution plans, but it looks okay to me. No suggested indexes, index seek on Table1 (cost of 89%), and a table scan on Table2 (but cost is only 9%).  Here's a link to the actual execution plan that has been anonymised.  http://pastebin.com/jcyATezX

There is no blocking that is taking place.  When using sp_WhoIsActive, I can see that the Reads are sky-rocketing into the billions.

Again, this is a boxed application, so I can't really change the query.  Any ideas on what I'm missing here?  It's driving me nuts that I can't figure this out.

After some excellent help from Kin, wBob, and Aaron Bertrand, I finally found my root cause: Table1 statistics needed updating.

I had already confirmed that statistics were updated 2 days prior during our weekly index maintenance, so didn't really think anything of it.  However, I gave it a try, and that made the query run time go from 2hrs 30min down to 2 seconds!

Command run was:

UPDATE STATISTICS Table1;

Thanks for all the help, particularly in the article "The "Runaway" Query"


UPDATE statement processing records that should be eliminated by WHERE clause I am getting a very odd error. Consider the table below:

CREATE TABLE #MyTable (
    Key1 INT , Key2 INT ,
    x SMALLINT , y INT , z INT ,
    a FLOAT , b FLOAT , c SMALLINT , s FLOAT
)

-- insert many records

CREATE UNIQUE CLUSTERED INDEX CI ON #MyTable ( Key1 , Key2 )

For some reason, the following update statement tries to divide by zero. This can only happen if c=0 or c=1. The WHERE clause clearly specifies c>1.

-- this fails with divide-by-zero error
UPDATE  #MyTable
SET s = CASE
        WHEN a - SQUARE ( b ) / c <= 0 THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

The problem is completely eliminated if I redundantly check for c<=1 in my CASE expression:

-- this completes without an error
UPDATE  #MyTable
SET s = CASE
        WHEN ( c <= 1 ) OR ( a - SQUARE ( b ) / c <= 0 ) THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

Has anyone encountered this before? Why would SQL Server touch the records with c>1?

The problem is also avoided if there is no index on the table (the index is useful in steps later in the procedure). Why would the existence of an index cause a condition in the WHERE clause to be ignored?

You should not make any assumptions about how SQL Server will process your query, except this: you should always assume that SQL Server can process your query in a way that is different from how it is explicitly written on the screen. And also this behavior can change based on any of the factors that can influence whether a new plan will be used for the next execution of even the same query, so if you apply a hint or change the query in any way or add or remove an index and the error goes away, don't assume the error won't come back tomorrow.

In this case, SQL Server is processing a calculation before it is eliminating rows from the WHERE clause. The way you avoid this is, like you said, ensuring that those rows are also filtered out inside the CASE expression (not statement).

A more common but similar approach is this kind of thing:

SELECT DATEPART(MONTH, varchar_column)
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

In many scenarios you will get an error message because SQL Server tried to apply the date functions against some values in the column that didn't turn out to be dates (and this attempt occurred prior to filtering). The workaround is tedious - use the CASE expression - but necessary unless you have some other way to verify the worthiness of the column (e.g. a computed column or fixing the data type in the first place). Just keep in mind that even this can fail to "short circuit" in some scenarios.

SELECT CASE WHEN ISDATE(varchar_column) = 1
  THEN DATEPART(MONTH, varchar_column) END
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

This is explained more thoroughly in the following feedback item by Erland Sommarskog:

SQL Server should not raise illogical errors


Select where datetime is greater than other datetime which may not exist I have a table with a field eventtime of type datetime and a different table with a datetime field.

I need the minimum eventtime that is greater than the greatest datetime field from the other table which may not have any rows. If there are no rows in the other table, I just need the minimum eventtime.

My query is

SELECT
    min(eventtime) time_min
FROM
    `cel`
WHERE
    eventtime > (SELECT COALESCE(MAX(other_datetime), 0) FROM `call`)

This gives me exactly what I want. The problem is there are about 6.5 million records in the table and this query takes about 10 or 11 seconds to run.

I tried using other values instead of 0 in the coalesce such as an empty string, and '1000-01-01 00:00:00' thinking that a type conversion was causing a performance hit, but those did not help at all.

mysql explain for the query

Should I add an index on the eventtime field?
Is 0 an appropriate value to compare against a datetime field?
If not, what is the right way to represent the minimum datetime value for comparison?
Is there a better way to achieve this to optimize for execution time?

Pull the COALESCE out of the subquery, to make it sargable:

        SELECT  min(eventtime) time_min
            FROM  `cel`
            WHERE  eventtime >
                COALESCE( ( SELECT  MAX(other_datetime)
                                   FROM  `call` ), 0)

And indexes:

call:  INDEX(other_datetime)
cel:   INDEX(eventtime)

That should be able to use both indexes to good advantage and take essentially zero time.

If there is an issue with 0 not being a date, try "0000-00-00"


What is the difference between ROLE and PRIVILEGE for Oracle? Is role a collection of certain privileges?

Yes, a role is a collection of system and/or object privileges and/or other roles.  It simplifies privilege management by allowing you to manage bundles of privileges.


Circular dependency between 2 table with one to one and one to many relationship I have 2 tables in SQL database, User and Company.

User belongs to one company.

So user table has UserID & CompanyID (foreign key)

Company can have multiple users. One of them will be the company owner.

So company table has CompanyID & UserID (foreign key)

One user can be the owner of only one company. How can I enforce that?

Is it ok to have such a circular reference? Or should I have a third table CompanyOwner with CompanyID and UserID columns?

In SQL Server, I might accomplish this using a cross reference table between "companies" and "users" where only a single row can be an owner for a given company, via the presence of a unique non-clustered index.

For example:

USE tempdb; --create this test in tempdb

IF OBJECT_ID(N'dbo.CompaniesUsers', N'U') IS NOT NULL
DROP TABLE dbo.CompaniesUsers;

IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;

IF OBJECT_ID(N'dbo.Companies', N'U') IS NOT NULL
DROP TABLE dbo.Companies;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(100) NOT NULL
        CONSTRAINT UQ_Users_UserName
        UNIQUE
);

CREATE TABLE dbo.Companies
(
    CompanyID int NOT NULL
        CONSTRAINT PK_Companies
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CompanyName varchar(100) NOT NULL
        CONSTRAINT UQ_Companies_CompanyName
        UNIQUE
);

CREATE TABLE dbo.CompaniesUsers
(
    CompanyID int NOT NULL
        CONSTRAINT FK_CompanyUsers_CompanyID
        FOREIGN KEY
        REFERENCES dbo.Companies(CompanyID)
    , UserID int NOT NULL
        CONSTRAINT FK_CompanyUsers_UserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , CompanyOwner bit NOT NULL
        CONSTRAINT DF_CompanyUsers_Owner
        DEFAULT ((0))
);

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers
ON dbo.CompaniesUsers(CompanyID, CompanyOwner)
INCLUDE (UserID)
WHERE CompanyOwner = 1;

Here I'll insert some test data to show how this works.  First, two "users":

INSERT INTO dbo.Users (UserName)
VALUES ('owner 1')
    , ('owner 2');

And, now, a "Company":

INSERT INTO dbo.Companies (CompanyName)
VALUES ('Company 1');

If I attempt to make both users an owner of the company, the statement fails:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 1);

  Msg 2601, Level 14, State 1, Line 62
  Cannot insert duplicate key row in object 'dbo.CompaniesUsers' with unique index 'IX_CompanyUsers'. The duplicate key value is (1, 1).

However, if I only allow one of the users to be the "owner", then the insert works:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 0);

SELECT *
FROM dbo.CompaniesUsers

╔═══════════╦════════╦══════════════╗
║ CompanyID ║ UserID ║ CompanyOwner ║
╠═══════════╬════════╬══════════════╣
║         1 ║      1 ║            1 ║
║         1 ║      2 ║            0 ║
╚═══════════╩════════╩══════════════╝

If you need to ensure a "user" can own at most a single "company", then this unique, non-clustered index, should do the trick:

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_1
ON dbo.CompaniesUsers(UserID, CompanyOwner)
INCLUDE (CompanyID)
WHERE CompanyOwner = 1;

I originally missed the requirement that a "user" can only be a member of a single "company".  With that in mind, you'd need to add this index (the "IX_CompanyUsers_1" index would be unnecessary):

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_2
ON dbo.CompaniesUsers(UserID)


adding node to existing availability group Currently have a 2 node availability group, trying to add a 3rd.

Added Node3 to the Windows Cluster. Node3 appears as UP in "Nodes" on the Failover Cluster Manager console. However I have the following message on Cluster Events:

    Cluster network name resource 'CAUBC-CLamf' encountered an error enabling the network name on this node. The reason for the failure
    was:   'Unable to obtain a logon token'.

  The error code was '1326'.

  You may take the network name resource offline and online again to
  retry.

Notably, the Cluster Core Resources console doesn't have the IP address for the new cluster node.

Another source of confusion here is found in the cluster log file:

  Logon failed for user CAUBC-CLamf$

Where is this user coming from?

EDIT: despite this error, I have been able to add the new node to the availability group and automatically seed the databases. I don't get why the error persists, however.

In addition, only the new node's ip address has registered in DNS - the listener/cluster resource ips are not in DNS

Is it necessary to add the new node's secondary ip to the Cluster Core Resources, in a multi-subnet setup?

I think this is normal behavior as only one cluster IP will be up at one time. When trying to force the secondary or third IP up you should get the error message you stated.


What ports does SQL Server use on Linux? What ports does SQL Server use on Linux? I want to be sure I'm forwarding the ports required when I run it in a container.

Assuming the defaults, there are two ports SQL Server listens on which can be verified with netstat -ln,

ms-sql-s TCP 1433 for the server
ms-sql-m TCP 1434 for the Dedicated Administrator Connection which by default is enabled for local connections on 127.0.0.1 only. For more information see "remote admin connections Server Configuration Option"

While my install was only listening for TCP connections, /etc/services shows entries for UDP too,

ms-sql-s        1433/tcp                        # Microsoft SQL Server
ms-sql-s        1433/udp
ms-sql-m        1434/tcp                        # Microsoft SQL Monitor
ms-sql-m        1434/udp

The "Microsoft SQL Monitor" is the DAC connection.

The log also shows similar information,

spid19s     Server is listening on [ 'any' <ipv6> 1433].
spid19s     Server is listening on [ 'any' <ipv4> 1433].
Server      Server is listening on [ ::1 <ipv6> 1434].
Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].


Is there a valid reason to alias a column to the exact same name/case? I inherited some code like this:

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

Does the Col_2 alias actually accomplish anything, or is it completely unneeded?

In the code as presented in your question, no there is no need but it will not cause any problems. The alias could be there because the column name used to be different and has been renamed, with the references to it being updated by some automated method.

I have sometimes deliberately aliased columns to the same name just to keep line syntax alignment when working with many columns some of which are aliased and some of which are not like so:

SELECT tbl1.col1          AS column1
     , tbl1.anothercolumn AS column2
     , tbl1.column3       AS column3
     , tbl2.somevalue     AS column4
FROM   ...

Here column 3 has an alias specified that is does not need as the output would have the desired name anyway.


Altering a parent table in Postgresql 8.4 breaks child table defaults The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered.

How to replicate:

First, create table and a child table. The child table should have one column that has a default value.

CREATE TEMPORARY TABLE temp_person (
    person_id SERIAL,
    name      VARCHAR
);

CREATE TEMPORARY TABLE temp_person_two (
    has_default character varying(4) DEFAULT 'en'::character varying NOT NULL
) INHERITS (temp_person);

Next, create a trigger on the parent table that copies its data to the child table (I know this appears like bad design, but this is a minimal test case to show the problem).

CREATE FUNCTION temp_person_insert() RETURNS trigger
LANGUAGE plpgsql
AS '
BEGIN
INSERT INTO temp_person_two VALUES ( NEW.* );
RETURN NULL;
END;
';

CREATE TRIGGER temp_person_insert_trigger
    BEFORE INSERT ON temp_person
    FOR EACH ROW
    EXECUTE PROCEDURE temp_person_insert();

Then insert data into parent and select data from child. The data should be correct.

INSERT INTO temp_person (name) VALUES ('ovid');
SELECT * FROM temp_person_two;
 person_id | name | has_default
-----------+------+-------------
         1 | ovid | en
(1 row )

Finally, alter parent table by adding a new, unrelated column. Attempt to insert data and watch a "not-null constraint" violation occur:

ALTER TABLE temp_person ADD column foo text;
INSERT INTO temp_person(name) VALUES ('Corinna');
ERROR:  null value in column "has_default" violates not-null constraint
CONTEXT:  SQL statement "INSERT INTO temp_person_two VALUES (  $1 .* )"
PL/pgSQL function "temp_person_insert" line 2 at SQL statement

My version:

testing=# select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
(1 row)

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying |
 foo       | text              |

db=> \d temp_person_two
   Column    |         Type         |                            Modifiers
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    |
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

  Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.


Add partition works but not the drop partition We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any reason for this?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

I have solved it the issue is here CONCAT(' p' when I remove the space it is solved. But I don't understand why it does not work with the space because when we created it is with space?


Operand data type time is invalid for add or subtract operators I'll to calculate how many hours a person has worked on SQL server. For that I'm storing the Arrival, Departure and Break in my database table as time(7). A bit for DepartureNextDay determinate that the Departure is on or after midnight or not.

Here is an example of my data:

| ID | Arrival  | Depatrure | Break    | DepartureNextDay |
| -- | -------- | --------- | -------- | ---------------- |
| 1  | 23:00:00 | 01:00:00  | 00:00:00 | 1                |
| 2  | 08:00:00 | 17:00:00  | 01:00:00 | 0                |

Now I'll calculate the worked hours using this formula:

Departure - Arrival - Break

When DepartureNextDay is 1 there must 24 hours being added to Departure to get a positive result. So the results must be this:

| ID | WorkedHours |
| -- | ----------- |
| 1  | 02:00:00    |
| 2  | 08:00:00    |

For this I'm using this code:

select (Departure + (case DepartureNextDay
                         when 1 then convert(time(7), '24:00:00') --> this is invalid too because it's bigger then 23:59:59
                         else convert(time(7), '00:00:00')
                      end
                    )) - Arrival - Break as WorkedHours
from WorkTable

But I've this error:

  Operand data type time is invalid for add/subtract (when case is removed) operator.

How could I do this?

P.S.: I'm storing the DepartureNextDay on a different column because:

time(7) must between 00:00:00 and 23:59:59 (most important reason)
It's more logical to store 1 o'clock A.M. as 01:00:00 and not as 1.01:00:00 or 25:00:00 and keep DepartureNextDay in a different column.

You should use datetime instead of time, nevertheless it is also possible using your current data:

;with temp
AS
(
    SELECT t.ID,
    CAST(t.Arrival AS time(7)) Arrival,
    CAST(t.Departure AS time(7)) Departure,
    CAST(t.[Break] AS time(7)) [Break],
    DepartureNextDate
    FROM (
        VALUES
        (1, '23:00:00', '01:00:00', '00:00:00', 1),
        (2, '08:00:00', '17:00:00', '01:00:00', 0)
    )t(ID, Arrival, Departure, [Break], DepartureNextDate)

)
SELECT ID, CONVERT(time(7),
    CAST(Departure AS Datetime)
    + DATEADD(DAY, DepartureNextDate, '')
    - CAST(Arrival AS Datetime)
    - CAST([Break] AS Datetime)
    )
FROM temp


Difference between DEFINE_CHAIN_EVENT_STEP and DEFINE_CHAIN_STEP procedure As per oracle help:*

  DEFINE_CHAIN_EVENT_STEP Procedure:
  This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step will not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step will replace the old one.

  DEFINE_CHAIN_STEP Procedure:
  This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name argument, the new step replaces the old one.
  The chain owner must have EXECUTE privileges on the program or chain associated with the step. Only one program or chain can run during a step.

Though the help documentation is elaborate, unfortunately I'm failing to understand fully. what is the difference between these two procedures?

First of all a chain is a name referred to a series of programs which are linked together for a specific objective.

So the DBMS_SCHEDULER.DEFINE_CHAIN_STEP procedure define an step to point to a program or a nested chain.

BUT, if you wish to define a step which waits for a event to occur to be executed then you need to use DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP procedure.

The following blog post from Database Journal contains a very good example of using Job Chains.

Figure: Flowchart is taken from database journal blog post by Jim Czuprynski.

For example, according to the above flowchart, to perform employee paycheck file validation steps it file has to be arrived. To define such step you need to user DEFINE_CHAIN_EVENT_STEP procedure.

Go to the referenced link for details.

Reference: Job Chains


A 'deeper' PostgreSQL autovacuum We have a PostgreSQL 8.3.7 database suffering severe bloat after an algorithmic change. Unfortunately upgrading isn't an option at this time.

For a particular group of partitioned count roll-up tables, we used to update them by selecting then either inserting for new counts or updating existing counts. To avoid network saturation we switched instead to updating, checking for failed updates and then inserting.

I've read this is a bad scenario for PostgreSQL (at least circa 8.3.7) where the dead tuples from the updates are in the middle of the table rather than the end (as previously) and so are not being reclaimed by the autovacuum which works from the back of the table.

It seems to me that autovacuum_vacuum_cost_limit is the most likely setting I should change.  Currently it is set to the default of 200 - perhaps I should start at 2,000 and go up from there?

I have a small window to make production changes to use trial and error, and no test database of equivalent size.

You have a couple options. The basic thing is that dead tuples in the middle of the table, as you have guessed are not reclaimed for the filesystem with autovacuum.  Rather they are marked as free so that future inserts in the same table can use that space.  The space isn't bloated permanently.  It is available for re-use within the same table regarding future inserts or updates.

If you want to reclaim that space for the filesystem you have two options:

1)  vacuum full.  This locks things.  It is usually not ideal but sometimes it is the best option.  Also it takes a while.

2)  cluster on an index.  This rewrites the table into a new file.  It requires a lot more disk space, but is also a lot faster.  It does fully return free space back to the filesystem at the end however.

Both these require table locks.  They are not concurrent safe.  You don't want autovacuum deciding that now is a good time to lock tables for you so this is just the way it is.

Edit:  This wasn't obvious from the question initially but from the comment reply it looks like the problem here is that the free space map settings may be insufficient.  These settings were removed in 9.1 iirc but basically set the size of the free space map per table.  What happens in this case is that the free space map is insufficient to hold all dead tuples.  In this case tuples get deleted but the space can't be re-used.

When this happens bloat is the result and vacuum full or cluster can be necessary to recover space.  As an intermediate solution barring these, you can significantly increase your free space map settings and re-run vacuum (regular, not full) to update the fsm.  This won't recover space, but it will make more of the space available for re-use.


How do you grant permissions to a MySQL user using a shell script with variables? I'm trying to create a provisioning script for Scotch Box (Vagrant) that will populate a database with seed content when I fire up the server.

When I name the actual user, database, password, and host in the lines of code being piped to mysql, it worked perfectly.

But when I added a "config" section with those items defined as variables, everything worked except the GRANT: The user was created, the database was created, and the data was imported, but the user didn't get access to the database.

Here is the script:

#!/usr/bin/env bash
# ...

echo "CREATE USER '$USER'@'$HOST' IDENTIFIED BY '$PASS'" | mysql -uroot -proot
echo "CREATE DATABASE $DB" | mysql -uroot -proot
echo "GRANT ALL ON $DB.* TO '$USER'@'$HOST'" | mysql -uroot -proot
mysql -uroot -proot $DB < /var/www/data/db.sql

Is there a way to pass these items as variables to the GRANT command?

I did the following. I wrote a script (testfile) as below and it worked. The big difference AFAICS is that with my script I export my variables before trying to use them. BTW, I wouldn't call it $USER - I'd go with $MY_USER - there's already a Linux system variable called $USER. I also didn't have these lines

#!/usr/bin/env bash
# ...

I just chmod'ed the file - $> chmod 755 testfile and ran it thus ./testfile.

[pol@localhost inst]$ more testfile
export MY_USER=puser
export MY_PASS=ppass
export MY_HOST=localhost
export MY_DB=ptest
echo "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'" | ./bin/mysql -S ./mysql.sock -u root -pdba
echo "CREATE DATABASE $MY_DB" | ./bin/mysql -S ./mysql.sock -uroot -pdba
echo "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'" | ./bin/mysql -S ./mysql.sock -u root -pdba
[pol@localhost inst]$

I have since added the line (to test the running of an sql script file following the user and database creation - with grants).

./bin/mysql -S ./mysql.sock -u $MY_USER -p$MY_PASS $MY_DB < test.sql

to the end of testfile. This has the advantage of not using root, but rather the user you have just created. It is always good practice to run commands as root as rarely as possible. Keeping the root password in a script file is rather insecure. You can keep it in your .bashrc - check out the MySQL environment variables here, in this case $MYSQL_PWD.

[EDIT] - here is an alternative method. The -e stands for execute (mysql --help) - and is rather more conventional than the echo construct that you used.

./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'"
./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE DATABASE $MY_DB"
./bin/mysql -S ./mysql.sock -u root -pdba -e "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'"
./bin/mysql -S ./mysql.sock -D $MY_DB -u $MY_USER -p$MY_PASS -e "SOURCE test.sql"

(This worked on 5.6 - the previous example worked on a milestone release).

You can see the result below. Don't worry about the ./bin/mysql, nor the -S ./mysql.sock - I have a non-standard source install. For a normal install, just use mysql.

You can get rid of the annoying "password ... insecure" warnings by issuing the command this way ./testfile &>/dev/null

[pol@localhost inst]$ ./testfile
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[pol@localhost inst]$
[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u puser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.7.7-rc-log Source distribution

Copyright (c) 2000, 2015, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ptest              |
+--------------------+
2 rows in set (0.00 sec)

mysql> exit;
Bye
[pol@localhost inst]$


Packaging SQL Server Express 2012 install won't install due to user rights Is there a way force the above permission window to elevate the user permissions?

It may be that you do not have enough rights granted for your installation.  See:

SQL Server installation fails if the Setup account does not have certain user rights

Partial comments under Cause:

  This behavior is by design. In addition to adding the user account
  that is running the Setup as a local administrator, the Setup user
  account requires the following default user rights for the Setup to be
  completed successfully

 -  Local Policy Object Display Name    User Right
 -  Backup files and directories        SeBackupPrivilege
 -  Debug Programs                      SeDebugPrivilege
 -  Manage auditing and security log    SeSecurityPrivilege

Also review the comments at:

InstallShield Limited Edition
 The Install Shield link also suggests ClickOnce Security and Deployment.
 Install SQL Server 2012 from the Installation Wizard (Setup)

If you do not have enough privileges then you probably need to either (1) pursue the extra privileges or (2) have someone with those privileges handle the install.


Order of operation with LTRIM/RTRIM/ISNULL Does the order of operation that you place your LTRIM and RTRIM matter when used in conjunction with ISNULL? For instance, take the following example where a user potentially enters a bunch of spaces in a field, but we trim their input to be an actual NULL value to avoid storing empty strings.

I am performing the TRIM operations outside of ISNULL:

DECLARE @Test1 varchar(16) = '    '

IF LTRIM(RTRIM(ISNULL(@Test1,''))) = ''
BEGIN
    SET @Test1 = NULL
END

SELECT @Test1

This appropriately returns a true NULL value. Now let's place ISNULL on the outside:

DECLARE @Test2 varchar(16) = '    '

IF ISNULL(LTRIM(RTRIM(@Test2)),'') = ''
BEGIN
    SET @Test2 = NULL
END

SELECT @Test2

This also returns a NULL value. Both work well for the intended usage, but I'm curious if there is any difference to how the SQL query optimizer handles this?

Your tests are redundant. First both LTRIM and RTRIM return NULL when given NULL input:

declare @Test1 varchar(16) = null;

select
    lft = LTRIM(@test1),
    rgt = RTRIM(@Test1);

lft              rgt
---------------- ----------------
NULL             NULL

Second, standard SQL ignores trailing spaces when comparing strings:

select
    A =case
        when '' = '   ' then 'equal'
    end,
    B = case
        when '  ' = '     ' then 'equal'
    end;

A     B
----- -----
equal equal

SQL Server provides the NULLIF function. This takes two parameters. If they differ the first will be returned. If they are equal it will return NULL. I think this will address your requirement.

declare @Test1 varchar(16) = '    ';

select
    first = nullif(@Test1, '');

set @Test1 = NULL;

select
    second = nullif(@Test1, '');

set @Test1 = 'some value';

select
    third = nullif(@Test1, '');

first
----------------
NULL

second
----------------
NULL

third
----------------
some value

You may still need the LTRIM, depending on your input validation.


How to select distinct record? I have a table, as follows:

ID   |  Name     |  MyID
1    |  Alan     |  2
2    |  Alan     |  2
3    |  John     |  3

I want to select the records as follows:

If two records have the duplicate values in ‘Name’ column, then only the first record is selected and the other record is discarded, even if the two records have different values in other columns.
If two records have different ‘Name’ value, then they are ordered via ‘MyID’ column in ascendant order.
All columns of the records should be returned.

So for the above records, the query should select the following ones:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

How to write the query? It seems I should use DISTINCT or GROUP BY, but my queries are all fail. Please help.

Thanks

This query with a DISTINCT:

SELECT DISTINCT Name, MyID
FROM data;

Returns distinct Name and MyID:

Name     |  MyID
Alan     |  2
John     |  3

This query with a GROUP BY:

SELECT MIN(ID) AS ID, Name, MyID
FROM data
GROUP BY Name, MyID;

Returns Name and MyId with the smallest ID like your sample:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

Both query are in this SQL Fiddle and will work with most RDBMS. (Sample uses SQL Server)


Is there any way to find the exact Query responsible for replication lag? I've tried checking slowquery.log and binlog.

Slow query log kind of helps but not all the time.

Binlog entries are inaccurate. I used the following command to check binlog.

show binlog events in <binlog name> from <log position> limit <n>;

log_slow_slave_statements = ON in the Slave's my.cnf.  Also long_query_time=1.  Turn on the slow log; wait a day; look in the slowlog.  mysqldumpslow -s t or pt-query-digest will summarize the slowlog, putting the 'worst' query first.

(From the Changelogs)
----- 2013-04-23 5.7.1 Milestone 11 & 2013-04-18 5.6.11 -- Bugs Fixed -- -----

  The --log-slow-admin-statements (
  http://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-slow-admin-statements
  ) and --log-slow-slave-statements (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_log-slow-slave-statements
  ) command options now are exposed at runtime as the
  log_slow_admin_statements (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_log_slow_admin_statements
  ) and log_slow_slave_statements (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_log_slow_slave_statements ) system variables. Their values can be examined using SHOW VARIABLES
  ( http://dev.mysql.com/doc/refman/5.7/en/show-variables.html ). The
  variables are dynamic, so their values can be set at runtime. (The
  options were actually replaced by the system variables, but as system
  variables can be set at server startup, no option functionality is
  lost.) (Bug #59860, Bug #11766693)

In older versions, the command line --log-slow-slave-statements existed even though the variables did not.


SQL Server 2008: sequence which restarts daily I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are allowed.

My approach is rather naive: make a table with current date and current sequence value and maintain a single record in it:

create table DailySequence
(
    date date,
    sequence int
)

insert into DailySequence values (getdate(), 1);

CREATE TRIGGER MakeHumanReadableId ON dbo.AuditMeasures
FOR INSERT
AS
    DECLARE @ret int;
    DECLARE @tempDate date;
    DECLARE @nowDate date;

    SET @nowDate = getdate();

    SELECT @ret = t.sequence, @tempDate = t.date from DailySequence as t;

    IF @nowDate = @tempDate
    BEGIN
        SET @ret = @ret + 1;

        UPDATE DailySequence
        SET sequence = @ret;
    END
    ELSE
    BEGIN
        SET @ret = 0;

        UPDATE DailySequence
        SET sequence = @ret, date = @nowDate;
    END

    UPDATE AuditMeasures
    SET [HumanReadableId] = CAST(@nowdate AS VARCHAR(10)) + '_' + CAST(@ret AS VARCHAR(10));
    FROM inserted
    INNER JOIN AuditMeasures On inserted.id = AuditMeasures.id
GO

Questions:

Are there any pitfalls for my solution? e.g code inside the trigger won't run inside a transaction thus giving incorrect values.
Am I missing a better solution?

One potential method of doing this would be (see the better method, at the end):

USE tempdb;

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL
    , LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED
    (
        [IDName] ASC
    ) WITH
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    )
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from
                                tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            UPDATE dbo.tblIDs
            SET @NewID = LastID = LastID + 1
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

CREATE TABLE dbo.HumanReadableSequence
(
    HumanReadableSequence_ID VARCHAR(20) NOT NULL
        CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(386) NOT NULL
);

GO
CREATE PROCEDURE dbo.HumanReadableSequence_Insert
(
    @SomeData VARCHAR(386)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NextID INT;
    DECLARE @Today VARCHAR(20);
    DECLARE @t TABLE
    (
        ID INT NOT NULL
    );
    SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

    INSERT INTO @t (ID)
    EXEC dbo.GetNextID @IDName = @Today;

    INSERT INTO dbo.HumanReadableSequence (HumanReadableSequence_ID, SomeData)
    SELECT (@Today + '_' + CONVERT(VARCHAR(20), ID, 0))
        , @SomeData
    FROM @t;
END
GO

EXEC dbo.HumanReadableSequence_Insert N'this is a test';

SELECT *
FROM dbo.HumanReadableSequence;

The results:

Having said all that, I would ask why not simply maintain two separate columns that could be concatenated in the presentation layer:

CREATE TABLE dbo.HumanReadableSequence
(
    CreateDate DATETIME NOT NULL
        CONSTRAINT DF_HumanReadableSequence_CreateDate
        DEFAULT (DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))
    , HumanReadableSequence_ID INT NOT NULL
    , SomeData VARCHAR(386) NOT NULL
    , CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
        (CreateDate, HumanReadableSequence_ID)
);

DECLARE @ID INT;
DECLARE @t TABLE
(
    ID INT NOT NULL
);
DECLARE @Today VARCHAR(20);
SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

INSERT INTO @t (ID)
EXEC dbo.GetNextID @IDName = @Today;

SELECT @ID = t.ID
FROM @t t;

INSERT INTO dbo.HumanReadableSequence (SomeData, HumanReadableSequence_ID)
VALUES ('This is a test', @ID);

SELECT HumanReadableSequenceValue =
        REPLACE(CONVERT(VARCHAR(20), hrs.CreateDate, 101)
        + '_'
        + CONVERT(VARCHAR(20), hrs.HumanReadableSequence_ID, 0), '/', '-')
    , SomeData
FROM dbo.HumanReadableSequence hrs;

The results:

The above method is much more capable of scaling well, and offers flexibility in the presentation of the human readable sequence number.


What connection do internal sessions belong to in MS SQL Server? By querying

select * from sys.dm_exec_connections

I do not see corresponding connections for internal sessions, about the existance of which I know from sys.dm_exec_sessions DMV.
My questions is whether do they need a connection to run and how can I find out their connection ID, if the connection exists(because I can't join on dm_exec_connections)?

Thanks!

The internal sessions < 50 are background processes, and have no corresponding connection, which is why you won't see them show up in
 sys.dm_exec_connections.

The connections are reffered to as external user processes apart from the internal processes.

Note also, as Dan Guzman added:

  System sessions in newer SQL Server versions may have session_id values > 50. These can be identified by is_user_process = 0 in sys.dm_exec_sessions


MySQL Replication With single Master DB to multiple Slave DBs I am creating the MySQL master-slave replication on CentOS 7 with MySQL 5.6

I have one Database like DBMaster on the Master server and multiple Database on the slave like DBSlave1, DBSlave2 and more. All the tables are same into the Master and Slave Databases.

I want when ever any record insert, update or delete in the Master DB tables that replicated to all the Database of slave.

For example: I have table name like student in Master and slave Databases. When ever I insert data into the student table in Master Database it will be replicated into the all the Databases student table of slave.

I have already got success in single Database and same name on Master and slave server.

Below are the configuration files details of both the server:

Master server's my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave Server's my.cnf

[mysqld]
server-id=2
replicate-wild-do-table=DBSlave%.dbtb1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Database details given below:

Master Server

create database DBMaster;
create table DBMaster.dbtb1(name varchar(100));

Slave Server

create database DBSlave1;
create table DBSlave1.dbtb1(name varchar(100));

create database DBSlave2;
create table DBSlave2.dbtb1(name varchar(100));

AS you can see the Database name on both the sever are different for that I have use the below statement into the slave server for replication.

replicate-wild-do-table=DBSlave%.dbtb1

But when I try to insert data into the Master Data base server I got the below error or slave server status:

Error 'Table 'DBMaster.dbtb1' doesn't exist' on query. Default database: ''. Query: 'insert into DBMaster.dbtb1 values ('Punu')'

Please tell me what is wrong in the configuration. I am trying this this first time.

Make sure each of your replicas has a different value of server_id. If all have same server_id, then the master may only send an event (such as the create table event) to one of the replicas.

Regardless, and since you are new to MySQL, I'd like to discourage you from using replicate-do* and replicate-ignore*, at least until you're really sure of what you're doing. Replication filters have many surprising effects. Normally, you don't use any filters in your production setup.


Problem filtering information in MySQL query I've a little problem with a SQL query.

The objective is to get an employee profile related to a role.

Table EMPLOYEE (Louis,Mary,Christian):

EmpId
EmpName

Table ROLE (Chef,Waiter,NULL):

RoleID
RoleNAME

Table EMPROLE:

EmpId
RoleId

An employee should have 0..n roles. To get an employee profile:

IF Chief -> BOS
ElseIF waiter -> CHA
Else -> CUS

The objective is to print EmpName, all roles, and the profile.

I can print all roles with group_concat and get the role using case but that only works if the role is the first occurrence in the group_concat column.

My query:

select EMPLOYEE.EmpName,GROUP_CONCAT(ROLE.RoleName) as role_,
(CASE WHEN ROLE.RoleName = "Chief" THEN "BOS"
CASE WHEN ROLE.RoleName = "waiter" THEN "CHA"
ELSE THEN "NULL"
END) AS PROFILE
FROM EMPLOYEE,ROLE,EMPROLE
WHERE EMPLOYEE.EMPLOYEEID=EMPROLE.EMPLOYEEID AND EMPROLE.ROLEID=ROLE.ROLEID group by EMPLOYEE.EmpName;

Output:

+----------+--------------------------------+--------+
| EmpName  | roles                          | PERFIL |
+----------+--------------------------------+--------+
| Louis    | Waiter,Chief                   | NULL   |
| Mary     | Chief,Waiter                   | BOS    |
| Christian| NULL                           | NULL   |
+----------+--------------------------------+--------+

How can I get the information right?

This might be closer:

select  e.EmpName,
        GROUP_CONCAT(r.RoleName) AS roles
        CASE
            WHEN FIND_IN_SET("Chief",   GROUP_CONCAT(r.RoleName)) THEN "BOS"
            WHEN FIND_IN_SET("waiter ", GROUP_CONCAT(r.RoleName)) THEN "CHA"
            ELSE                                                       "CUS"
        END  AS Profile
    FROM  EMPLOYEE AS e
    JOIN  EMPROLE  AS er  ON e.EMPLOYEEID = er.EMPLOYEEID
    JOIN  ROLE     AS r   ON er.ROLEID = r.ROLEID
    group by  e.EMPLOYEEID;

Notes:

Please use the JOIN ON syntax instead of commajoin and WHERE.
The GROUP_CONCAT occurs 3 times; that can be avoided with the expense of a nested query.  I did not think it was worth it.
Be sure to have something like this on EMPROLE:

PRIMARY KEY(employeeid, roleid),
INDEX(roleid, employeeid)


Clustered indexed fields hash matching. Does it matter? I'm trying to performance tune some lengthy queries and I'm slicing bits out and running them in isolation.

The query below features three tables D500M, D550M and D580M, all joined on person_ref. All of these tables have clustered indexes on the person_ref fields. They also have non clustered indexes which include the fields in the select list.

These tables are not large - just under 10k rows in each.

select p.PERSON_REF,p.surname,p.first_forname,e.employee_number,ph.MAIN_FLAG
from D500M p
INNER join D550M e on e.PERSON_REF=p.PERSON_REF
INNER join D580M ph on ph.PERSON_REF=p.PERSON_REF

I'm studying the Actual Execution Plan and seeing Index scans on the three tables (nonclustered), and then two Hash Matches on the joins, with costs of 39% and 41%.

The question is this; Is having these hash matches good, bad or irrelevant?

SQL Server is very good at deciding the best, most efficient execution plan for any given query.  Since you have no predicates, such as a WHERE D500M.PERSON_REF = 123456, SQL Server is most likely assuming an index scan is the quickest way to gather all the relevant details from each table.  If you add a predicate to the query, you will most likely see an index seek instead.  This is to be expected.

"Good, bad or irrelevant" is only something you can decide.  Is the performance acceptable for your needs?

You could try forcing the join type to see what happens if a loop join or a merge join were used instead.  Something like:

SELECT p.PERSON_REF, p.surname, p.first_forname, e.employee_number, ph.MAIN_FLAG
FROM D500M p
INNER LOOP JOIN D550M e ON e.PERSON_REF=p.PERSON_REF
INNER LOOP JOIN D580M ph ON ph.PERSON_REF=p.PERSON_REF

If you want a more conclusive answer, you should provide code for a minimally complete verifiable environment that matches your environment. i.e. provide us table definitions, the actual queries involved, and the actual query plans you're seeing.


How can we pass default value as null to Associative Array in Procedure? How can we pass NULL as a default value to an associative array?

I have tried using

DEFAULT cast (null as t_test)

Here t_test is an associative array.

Casting it as NULL should work fine:

DECLARE
  TYPE t_test IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  PROCEDURE test_me ( in_test IN t_test DEFAULT CAST( NULL AS t_test ) )
  AS
  BEGIN
    dbms_output.put_line( in_test.COUNT );
  END test_me;

BEGIN
  test_me();
END;


Microsoft SQL server 2008 and linked servers I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any of these databases to a dedicated server for itself if required and just create a linked server to the other server(s). however doing so I also need to change all the codes which are accessing the other databases.

does anyone know of any way to avoid this later modification in case of using a linked server ?

or in other words :

Is that possible to have stored procedures which would work regardless of the fact that it needs to access to a linked server or same local server ?

example :

DatabaseA and DatabaseB are both located on SQLInstannceA
Sp on DatabaseB :

SELECT * FROM DataBaseA.dbo.tbx

and now if I move DatabaseB to SQLInstanceB, and create a linked server to SQLInstanceA, I have to change the SP code to the following :

SELECT * from linkedserverName.DatabaseA.dbo.tbx

is there anyway to avoid this change in case of moving databases ?

Using loop back linked servers isn't the greatest idea.  This can lead to some interesting performance problems once you start doing joins between local tables and "remote" tables as you are going through a linked server so suddenly instead of doing a normal join you may end up transferring the entire table across the linked server (which means possibly reading it off of the disk), stuffing it into tempdb and joining to a hash table which doesn't have any indexes on it.

Don't setup linked servers unless you actually need them.  You can get some very powerful servers these days for not much money (I'm currently working on a server which has 32 cores and 256 Gigs of RAM) so scaling a server up isn't all that hard.  If the application actually needs to be scaled out across multiple servers you'll need someone who is used to doing very complex tuning to help out to make sure that you aren't shooting yourself in the foot in the process (which isn't hard to do).

Once you do get to needing linked servers, there's going to be so much stuff that needs to be done to move the database and test everything going through and fixing the code isn't going to seem all that bad.


Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016? Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions?

The reason I'm asking is that I was looking at a query whose performance degraded in compatibility mode 130 and the reason was related a change in the estimate of the number of rows that match a WHERE clause that contained a call to SUBSTRING(). I corrected the problem with a query rewrite, but am wondering if anyone is aware of any documentation about changes in this area in SQL Server 2016.

Demo code is below. The estimates are very close in this test case, but the accuracy varies depending on the data.

In the test case, in compat level 120, SQL Server appears to be using the histogram for the estimate, whereas in compat level 130 SQL Server appears to be assuming a fixed 10% of the table matches.

CREATE DATABASE MyStringTestDB;
GO
USE MyStringTestDB;
GO
DROP TABLE IF EXISTS dbo.StringTest;
CREATE TABLE dbo.StringTest ( [TheString] varchar(15) );
GO
INSERT INTO dbo.StringTest
VALUES
( 'Y5_CLV' );
INSERT INTO dbo.StringTest
VALUES
( 'Y5_EG3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_NE' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_PQT' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_T2V' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_TT4' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_ZKK' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_LW6' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_QO3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_TZ7' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_UZZ' );

CREATE CLUSTERED INDEX IX_Clustered ON dbo.StringTest (TheString);

/*
Uses fixed % for estimate; 1.1 rows estimated in this case.
    Plan for computation:
        CSelCalcFixedFilter (0.1) <----
            Selectivity: 0.1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 130;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/*
Uses histogram to get estimate of 1
 CSelCalcPointPredsFreqBased <----
      Distinct value calculation:
          CDVCPlanLeaf
              0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
      Individual selectivity calculations:
          (none)
    Loaded histogram for column QCOL: [DBA].[dbo].[StringTest].TheString from stats with id 1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 120;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/*
-- Simpler rewrite; works fine in both compat levels and gets better estimate.
SELECT *
FROM dbo.StringTest
WHERE TheString LIKE 'ZZ[_]%'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
*/

I'm not aware of any documentation. I did look into this and make some observations however that are too long for a comment.

The 10% estimate is not always a degradation. Take the following example.

TRUNCATE TABLE dbo.StringTest

INSERT INTO dbo.StringTest
SELECT TOP (1000000) 'ZZ_' + LEFT(NEWID(), 12)
FROM   master..spt_values v1,
       master..spt_values v2;

and the WHERE clause in your question.

WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'

The table contains a million rows. All of them match the predicate. Under compat level 130 the 10% guess yields an estimate of 100,000. Under 120 the estimated rows is 1.03913.

The 120 behaviour uses the histogram but only to get the number of distinct rows. The density vector in my case shows 1.039131E-06 and this is multiplied by the table cardinality to get the estimated row count. All of the values are in fact different but all match the predicate.

Tracing the query_optimizer_estimate_cardinality extended event shows that under 130 there are two different <StatsCollection Name="CStCollFilter" events. The first one estimates 100,000. The second one loads the histogram and uses the CSelCalcPointPredsFreqBased/DistinctCountCalculator to get the 1.04 estimate. This second result appears unused.

The behavior that you observed is not consistently applied in 130. I added ORDER BY TheString expecting this to be a clear win for the 130 estimator as the 120 struggles on with a memory grant for one row but this minor change was sufficient to bring the estimated rows down to 1.03913 in the 130 case too.

Adding OPTION  (QUERYRULEOFF SelectToFilter) reverts the estimate going into the sort to 100,000 but the memory grant doesn't increase and the estimates coming out the sort are still based on the table distinct values.

Similarly tweaking the cost threshold for parallelism so that the query gets a parallel plan was sufficient in the 130 case to revert to the lower estimate. Adding QUERYTRACEON 8757 also causes the lower estimate. It looks like the 10% estimate is only retained for trivial plans.

Your proposed rewrite with

WHERE TheString LIKE 'ZZ[_]%'

Shows much superior estimates to both. The output for this is

  CSelCalcTrieBased

      Column: QCOL: [MyStringTestDB].[dbo].[StringTest].TheString

Showing that it used tries. More info about this is in the string summary statistics section just above here.

It is not the same as your original query however. As the first instance of _ is now assumed to always be the third character rather than being found dynamically.

If this assumption is hardcoded into your original query

 WHERE SUBSTRING(TheString, 1, 3) = 'ZZ_'

The estimation method changes to CSelCalcHistogramComparison(INTERVAL) and the estimated rows become accurate.

It is able to convert that into a range

WHERE TheString >=  'ZZ_' AND TheString < ???

and use the histogram to estimate the number of rows with values in that range.

This applies only to the cardinality estimation however. LIKE is preferable as it can use a range seek at runtime. SUBSTRING(TheString, 1, 3) or LEFT(TheString, 3) can't.


Constraint via Foreign Key I have the following schema in SQLite:

CREATE TABLE calendars
  (
     id    INTEGER PRIMARY KEY,
     title TEXT NOT NULL
  );

CREATE TABLE entry_types
  (
     id          INTEGER PRIMARY KEY,
     red         INTEGER NOT NULL CHECK(red BETWEEN 0 AND 255),
     green       INTEGER NOT NULL CHECK(green BETWEEN 0 AND 255),
     blue        INTEGER NOT NULL CHECK(blue BETWEEN 0 AND 255),
     description TEXT NOT NULL
  );

CREATE TABLE entries
  (
     id            INTEGER PRIMARY KEY,
     calendar_id   INTEGER NOT NULL,
     entry_type_id INTEGER NOT NULL,
     date          TEXT NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id),
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id),
     UNIQUE(calendar_id, date)
  );

CREATE TABLE calendars_entry_types
  (
     calendar_id   INTEGER NOT NULL,
     entry_type_id INTEGER UNIQUE NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id),
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id)
  );

Each entry_type can only be used for one particular calendar, which is encoded as the UNIQUE constraint in the calendars_entry_types table, restricting that many-to-many pattern to only one-to-many.

  SIDEBAR: Originally I defined the entry_type table like this:

CREATE TABLE entry_types
  (
     id          INTEGER PRIMARY KEY,
     -- SNIP color & description columns
     calendar_id INTEGER NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id)
  );

  This obviated the need for the calendars_entry_types table but then the calendar_id data was in two places: in the row in entries and in the row in entry_types, so I decided to change. Perhaps this was the wrong decision? I needed to keep the calendar_id in the entries table in order to keep the UNIQUE(calendar_id, date) constraint. /SIDEBAR

This is all well and good, but I now have nothing preventing a situation like

INSERT INTO calendars (id, title) VALUES (1, 'test1'), (2, 'test2');
INSERT INTO entry_types (id, red, green, blue, description) VALUES (1, 0, 0, 0, 'entrytype1');

-- entry_type id 1 goes with calendar id 1
INSERT INTO calendars_entry_types (calendar_id, entry_type_id) VALUES (1, 1);

-- this shouldn't work... calendar 2 doesn't go with entry_type 1
INSERT INTO entries (calendar_id, entry_type_id, date) VALUES (2, 1, 'entry1');  -- (I know that's not a date but this is just an example...)

Is it possible to write a constraint that would prevent the insertion of rows to the entries table that match this query?

SELECT id
FROM   entries
WHERE  ( calendar_id, entry_type_id ) NOT IN (SELECT calendar_id,
                                                     entry_type_id
                                              FROM   calendars_entry_types);

If not, is there a better or alternative way to design my schema that takes the "each entry type is only valid for a certain calendar" constraint into account?

You only need some small changes. First add a UNIQUE constraint on calendars_entry_types (this is needed for the new foreign key below):

UNIQUE (calendar_id, entry_type_id)

Then remove these two foreign keys from entries:

FOREIGN KEY (calendar_id) REFERENCES calendars (id),
FOREIGN KEY (entry_type_id) REFERENCES entry_types (id),

and replace them with a composite one:

FOREIGN KEY (calendar_id, entry_type_id)
    REFERENCES calendars_entry_types (calendar_id, entry_type_id),


Oracle 11g .DMP viewer I have received a dump of a Oracle 11.2.0.2.0 db, which I'd like to export to some readable format like plain text, CSV, excel...

Is that possible in some sort of a simple way? I've found a few converters, but they're not free.

If nothing else, I did just install the latest Oracle Express Edition and Oracle SQL Developer, but I can't seem to find a way to import my .DMP file.
I've tried using the imp and impdp tools, but they both gave me an error:

imp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

IMP-00038: Could not convert to environment character set's handle

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39088: file name cannot contain a path specification

UPDATE:

It seems I needed to copy the dump to "C:\oraclexe\app\oracle\admin\XE\dpdump" for the IMPDP to work. But now I get the following error message (or maybe just a warning, because as far I see, the tables were created and the data was imported?):

ORA-39083: Object type REFRESH_GROUP failed to create with error:

ORA-23421: job number 1389 is not a job in the job queue

Failing sql is:

BEGIN
dbms_refresh.make('"DB"."TABLE"',list=>null,next_date=>null,interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>1389,rollback_seg=>NULL,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,parallelism => 0,heap_size => 0);
dbms_refresh.add(name=>'"DB"."TABLE"',list=>'"DB"."TABLE"',siteid=>0,export_db=>'XE');
 END;

Job "SYS"."SYS_IMPORT_FULL_01" completed with 32 error(s) at 06:59:30

UPDATE 2:

Yeah, I've just checked via Toad for Oracle, and all the data was successfully imported. I also managed to export to excel file using the same application.

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR


What are the compression options for storing large amounts of text in Postgres? I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data transparently with a tool like zlib, or some other native space saving methods.

The data is primarily read only data, so writing is not a problem.

By default Postgres automatically compresses everything TEXT. It uses a simple lzcompress algorythm:

https://www.postgresql.org/docs/9.3/storage-toast.html

There is a plugin that will probably evolve to LZ4 compression support for TEXT:

https://github.com/zilder/pg_lz4

There is a FDW that also support compression:

https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/


Issue with CAST using linked servers (possible bug?) So I've got a some SQL Server 2008 DBs arranged in a star schema.  Server A is the one users submit queries to.  On Server A there is a DPV called Detection that's basically a UNION of table Detection on servers X, Y and Z.

One of the columns in Detection is a BIGINT called infoFlag that is basically a bitmask.  Users need to use bitwise operators to filter their queries on this mask.  This works fine in situation such as this:

    SELECT TOP 500 colA colB from Detection
    WHERE (infoFlag & 16 <> 0)

But if you use a mask that is too big for an INT, for example:

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & 3758333096 <> 0)

we get an error:

    System.Exception: The data types bigint and numeric are incompatible in the '&' operator.

Fair enough.  A simple cast should fix the problem, right?  It should, but it doesn't.

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & CAST(3758333096 as BIGINT) <> 0)

gives the same error.

While troubleshooting this, I ran the query directly on the Detection table on Server X and it worked fine (with the cast.) So I figured that the problem had something to do with the linked servers.

So I fired up profiler on Server X and submitted the query against the Detection DPV on Server A.  Here is what came through:

    SELECT TOP (5000) "Tbl1002"."colA" "Col1008","Tbl1002"."colB" "Col1009"
    FROM "myDbName"."dbo"."Detection" "Tbl1002"
    WHERE ("Tbl1002"."infoFlag"&(3758333096))<>(0)

It seems that the CAST statement is not getting propogated to the remote server!

I did figure out a workaround, which is to do this:

    declare @a bigint
    set @a = 3758333096

    SELECT TOP 500 colA, colB
    FROM Detection
    WHERE (infoFlag & @a <>  0)

However I am concerned that this might be a bug.  Or is there something I'm missing?

thank you!

--suz (@capnsue)

Version: Microsoft SQL Server 2008 (SP1) - 10.0.2710.0 (X64)   Apr  7 2009 20:36:04   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

EDIT: Here is the definition of the Detection table.   http://pastebin.com/ErGJ9fSG

Do you have the table definition for the table Detection?  I'm betting that infoFlag might not be set up as a bigint, but rather as a numeric with no decimal points.  You might also make sure that the data type is consistant on all of your servers.


Why is MYSQL not starting? When running the command 'sudo service mysql start' this is the output.
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

Here are the steps taken to troubleshoot already:
1. Checked permissions for /var/run/mysqld, it's owned by mysql but the user is root.
2. The syslog file has been checked and gives the following,

ERROR 2002 (HY000): 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

After checking the my.cnf file the path's for the socket matched.

When running which mysql this is the output /usr/bin/mysql

Monit has recently been installed on the server as well as the port was changed to port 99.

Everything has been running fine up until now. (About a week and a half after said changes were made.)

Linode has been rebooted a couple of times to troubleshoot memory issues but to no avail. Here is some output from various commands

            user@hostname:~$ df -h
            Filesystem      Size  Used Avail Use% Mounted on
            /dev/root        24G  7.3G   16G  32% /
            devtmpfs        494M     0  494M   0% /dev
            tmpfs           100M  208K   99M   1% /run
            tmpfs           5.0M     0  5.0M   0% /run/lock
            tmpfs           250M     0  250M   0% /run/shm

            user@hostname:~$ df -i
            Filesystem      Inodes  IUsed   IFree IUse% Mounted on
            /dev/root      1556480 177140 1379340   12% /
            devtmpfs        126432   1379  125053    2% /dev
            tmpfs           126898    780  126118    1% /run
            tmpfs           126898      2  126896    1% /run/lock
            tmpfs           126898      2  126896    1% /run/shm

            user@hostname:~$ free -m
                         total       used       free     shared    buffers     cached
            Mem:           991        501        489          0         52        237
            -/+ buffers/cache:        211        779
            Swap:          255          0        255

We have ruled out the possibility of a failed mysql process that is still running with the following commands

            user@hostname:/var/run/mysqld$ ps aux | grep mysql
            user  28834  0.0  0.2   7856  2068 pts/0    S+   13:00   0:00 grep mysql
            user@hostname:/var/run/mysqld$ kill
            kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
            user@hostname:/var/run/mysqld$ kill mysql
            -bash: kill: mysql: arguments must be process or job IDs
            user@hostname:/var/run/mysqld$ kill 28834
            -bash: kill: (28834) - No such process
            user@hostname:/var/run/mysqld$ kill 7856
            -bash: kill: (7856) - No such process
            user@hostname:/var/run/mysqld$ kill 2068
            -bash: kill: (2068) - No such process
            user@hostname:/var/run/mysqld$ man kill
            user@hostname:/var/run/mysqld$ kill -l
             1) SIGHUP   2) SIGINT   3) SIGQUIT  4) SIGILL   5) SIGTRAP
             6) SIGABRT  7) SIGBUS   8) SIGFPE   9) SIGKILL 10) SIGUSR1
            11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
            16) SIGSTKFLT   17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
            21) SIGTTIN 22) SIGTTOU 23) SIGURG  24) SIGXCPU 25) SIGXFSZ
            26) SIGVTALRM   27) SIGPROF 28) SIGWINCH    29) SIGIO   30) SIGPWR
            31) SIGSYS  34) SIGRTMIN    35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3
            38) SIGRTMIN+4  39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
            43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
            48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
            53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7
            58) SIGRTMAX-6  59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
            63) SIGRTMAX-1  64) SIGRTMAX

You may have corruption in your database.
Add the following to your my.cnf  under [mysqld] file and see if mysql will start

innodb_force_recovery = 1

Increment the value as needed.
See http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html