Execution Plan not matching Stored Proc Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists.

The execution plan shows an INNER JOIN of the M and A tables, where in the statement I am doing a LEFT OUTER JOIN.

I would like to count the number of widgets made in a time frame, join that to a month table (Jan-Dec), and have the results shown in an SSRS report. The join is because I cannot COUNT data that isn't there.
Currently I am getting:

MonthName   Widget Count
February    2
March       3
April       4
May         6
June        5
July        4
August      6
September   2
October     4
November    1
December    2

I would like to have the months where no widgets were made included in the list.

Here is the code:

DECLARE @OName varchar(50)
DECLARE @Start_Date DATE
DECLARE @End_Date DATE

SET @OName = 'John'
SET @Start_Date = '01/01/2012'
SET @End_Date   = '12/31/2012'

SELECT   M.[MonthName]
    ,COUNT(A.[Widget_ID]) AS 'Widget Count'
FROM [Connector].dbo.[Months] AS M
        LEFT OUTER JOIN  [SERVER].[DATABASE].[dbo].[Widget] AS A
        ON MONTH(A.[Widget_Date]) = M.[MonthID]
WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')
          AND A.[PlantID] = '00000001'
          AND (
               (A.Widget_Date >= @Start_Date AND @End_Date IS NULL)
            OR (A.Widget_Datet <= @End_Date AND @Start_Date IS NULL)
            OR (A.Widget_Date >= @Start_Date AND A.Widget_Date <= @End_Date)
            OR (@Start_Date IS NULL AND @End_Date IS NULL)

GROUP BY  M.[MonthName], M.MonthID
ORDER BY  M.[MonthID]

Below is the execution plan for this query.

::UPDATE1::
I just inserted the names of the months into this table. I cannot change the remote table.

CREATE TABLE [dbo].[Months](
    [MonthID] [smallint] IDENTITY(1,1) NOT NULL,
    [MonthName] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED ([MonthID] ASC)
)
GO
INSERT INTO [dbo].[Months]
           ([MonthName])
     VALUES
           (<MonthName, varchar(25),>)
GO

The "widget" table has over 250 fields, that will take me much too long to anonymize.

Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

DECLARE
  @OName      VARCHAR(50) = 'John',
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT
  m.m,
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.


mysql diff and add diff records into new database I have two database on two different hosts:

host A has database A and table A

table A contains records with primary key 1 to 10

host B has database B and table B

table B contains records with primary key 1 to 5

Now I have to take a diff of the two separate host/databases/tables
i.e. "host A/database A/table A" contains 1-10 and "host B/database B/table B" contains 1-5 so records 6-10 are missing in host B/database B/table B. Now add these diff records into "host B/database B/table B" from "host A/database A/table A".
In short, update development database with new production data since last update.
What I have tried:
mysqldump  --replace --complete-insert --skip-disable-keys --no-create-info -h A
  --port=3306 -u A --password=A database_A table_A
  | mysql -h B -u B --password=B database_B

mysqldump --insert-ignore --extended-insert=FALSE  --complete-insert
  --skip-disable-keys --no-create-info -h A --port=3306 -u A
  --password=A database_A table_A | mysql -h B -u B --password=B database_B

--insert-ignore eventually worked as diff and made the rows unique while dumping data across database.


Connect to SQL Server Management Studio over VPN (Hamachi) I just got my Hamachi VPN set up. For anyone familiar with Hamachi, I have it set up as a gateway so I'm part of the network when I'm away.  Almost everything seems to be working perfectly.  I can even backup using Windows Home Server if I want.

I cannot connect to my SQL Server from SQL Server Management Studio.  Of course, when I'm at home, everything works perfectly.  I can communicate with the database server just fine remotely (i.e., ping).  I just can't connect with SSMS.  The network configuration is at the default (TCP Enabled).

Does anyone know

Why I can't connect?
How I can determine why I can't?

Extra info:

Using a Workgroup, not a domain.
Using port 1433 to connect
Connecting to the default instance

Have you allowed remote connections in Surface Area Configuration -> Services and Connections -> Remote Connections?

I haven't specifically tried recently, but I have enabled TCP & Named pipes. You can also force a protocol if you need to, e.g. in the server name, use NP:myserver in order to force named pipes, or TCP:myserver in order to force TCP.

Try enabling named pipes in the connection setup too - client and server.

Also, hamachi normally uses its own IP range, so you should check that you can actually telnet to port 1433 of your server.


SQL - Finding Duplicates Across Columns I have a table that has 4 e-mail fields. A single contact can have anywhere from 1-4 e-mail addresses on file.

I'm having trouble writing a query that displays all duplicates across columns. For my example, I'll only discuss 2 email fields.

Example:

John Smith, Personal Email fake@fakejohn.com Work fake@fakejohn.com
Jane Smith, Personal Email jane@fake678.com Work NULL
Jane Smith, Personal Email NULL Work jane@fake678.com

In the above example, I can't find a way to say "I have 2 Jane Smiths that are duplicates" because they have the same email, but in different columns while also excluding John Smith, because while his email appears twice, I don't want to self-reference, as this is not a duplicate of itself.

Iterations of my attempt to find a cross-column duplication check have been unsuccessful but include the following:

Select First, Last, Personal
From Contacts C
where C.Personal = C.Work
  and c.ID <> c.ID -- my attempt to prevent self reference

This doesn't work, I receive 0 results, but should have thousands.

Without the c.ID <> c.ID bit, I receive too many results, as I retrieve results for non-duplicate records that simply have the same email address in both fields.

I've also added "Group By" clauses based on First & Last name and filter by greater than 1, but when checking our database, I'm still finding that I have contacts with the same name and no e-mail addresses in common with each other but have the same scenario as John Smith above.

Is there any way to achieve this? I haven't found any queries to get me started.

Formally you may try to use something like

WITH cte1 AS (SELECT name, email1 email FROM table
              UNION ALL
              SELECT name, email2 FROM table
              UNION ALL
              SELECT name, email3 FROM table
              UNION ALL
              SELECT name, email4 FROM table),
     cte2 AS (SELECT email
              FROM cte1
              GROUP BY email
              HAVING COUNT(DISTINCT name) = 1)
SELECT *
FROM table
LEFT JOIN cte2 ON cte2.email IN (table.email1, table.email2, table.email3, table.email4)
WHERE cte2.email IS NULL
UNION ALL
SELECT table.name, MAX(table.email1) email1, MAX(table.email2) email2,
                   MAX(table.email3) email3, MAX(table.email4) email4
FROM table
JOIN cte2 ON cte2.email IN (table.email1, table.email2, table.email3, table.email4)
GROUP BY table.name

But I don't know what you must do with the records like

Name   email1           email2            email3           email4
John   john@gmail.com   johnX@gmail.com   -                -
John   -                Xjohn@gmail.com   john@gmail.com   -


sqlite - very slow performance of WHERE EXISTS without index on EXISTS I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause.

For example:

UPDATE tbl1
   SET col1 = "Y"
 WHERE EXISTS (SELECT NULL FROM tbl2 WHERE tbl1.col2 = tbl2.col2)
   AND tbl1.col3 IN (1,2);

tbl1 has ~4.5M rows. When filtered above by col3, it comes down to ~400k rows.
tbl2 has ~100k rows. All rows are unique on col2, and every row in tbl2 has a match in tbl1.
tbl1 has an index on col2 and an index on col3.
I am using sqlite version 3.29.0 (from Python 3.7.2)

When I run this query without an index on tbl2.col2, the query never completes (I let it run for multiple minutes). If I add a unique index on tbl2(col2), the query finishes almost immediately.

This is confusing to me because I would think that the query should be constrained by the lookup into tbl1, since I'm using an EXISTS query for tbl2 (rather than IN()). The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it, since every row in tbl2.col2 is unique.

Why is the performance so slow without that unique index?

The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it

That is true; the question is, how many table scans do you think are there?

SQLite documentation will tell you that its optimizer is capable of only nested-loop joins, so regardless of whether it is smart enough to rewrite the EXISTS subquery into a join1 or will naively evaluate the predicate for each row of the driving table, you will still end up doing 4.5 million table scans, each retrieving on average 50K rows.

In the presence of a unique index on tbl2.col2 this turns into 4.5 million index seeks using the B-tree, each requiring only 2-3 logical I/O operations and retrieving exactly one row, which is 11 orders of magnitude less I/O.

1 - Which would not make any difference anyway, since it would then rewrite that join into a combination of simple predicates against the two tables.


Please enlighten me about FILESTREAM and BLOB files This is a really confusing topic for me.

I can understand what a BLOB is, I can easily use it.
I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database and restore it with the folder created to store these files. Things like these, I think, I can understand well.

What I don't get is:

What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?
When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.
With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file? As it is inside the database, I can easily recover it.

I'm using .PDF as an example

I can use and implement both approaches but it still not clear to me what happens with them. For me, it's the same thing, but one is stored outside the database, and the other one inside the database.

Based on the whitepaper written by Paul Randal -

BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database.
BLOBs larger than 1 megabyte (MB) are best stored outside the database.
For those sized between 256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”. Storing BLOB data solely within the database (e.g., using the varbinary (max) data type) is limited to 2 gigabytes (GB) per BLOB.

Answering your questions :

  What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?

Once you insert the pdf into the database as SINGLE_BLOB, you dont need the original .pdf file. You can export out if you need it at later time.

  When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.

File Streams uses a garbage collection process to clean up files that are not longer needed.   A system task wakes up periodically (~10 seconds or so) and checks for file stream garbage collection needs. Read "Why don't all the files get collected?"

  DANGER:  Deleting a file directly from the file stream container is considered database corruption and dbcc checkdb will report corruption errors.  These files are linked to the database in a transactional way and the deletion of a file is the same as a damaged page within the database.

-

  With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file?

For BLOB Varbinary(max) --> You can delete the original file. For Filestream, do not delete the file from the filestream folder. If the original file is not in the same folder than the filestream folder, then you can delete it.

References :

Excellent whitepaper (pdf) : To BLOB or Not To BLOB:
Large Object Storage in a Database or a Filesystem?
Free book from redgate : The Art of
SQL Server FILESTREAM


Out-of-Range and Overflow Handling for float values am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3)

Currently if the user enter the  value above 999999.000 MYSQL will save its maximum value,ie
1000000.000  and it will not throw any error.

If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue  is greater than 999999.000

Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000.

what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3)..

In my view, you're on the right track and validating it in C# before sending it to the database is the better way, so you're able to show the changes before a submit.

A regular expression should indeed be able to solve your validating issue, but depending on how you allow the number to be entered plays a part in how complex it should be.
Say do you allow thousand-seperator, decimal points, negative numbers, the number zero and so on.
And such cases always make Regular Expression a daunting task if not rather familiar with the syntax.

So I'd say that in your situation, the very easiest way would be to simply check whether the entered number is indeed a number and then cast it to a decimal and simply check whether it's value is between -1000000.000 and 1000000.000 (not inclusive).
Decimal.TryParse and an if-statement or two would be all that's needed for this.


Does CREATE TABLE SomeSchema.#TempTableName have a bug? Simple test-bed:

USE tempdb;
GO

/*
    This DROP TABLE should not be necessary, since the DROP SCHEMA
    should drop the table if it is contained within the schema, as
    I'd expect it to be.
*/
IF COALESCE(OBJECT_ID('tempdb..#MyTempTable'), 0) <> 0
    DROP TABLE #MyTempTable;

IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'SomeSchema')
    DROP SCHEMA SomeSchema;
GO

CREATE SCHEMA SomeSchema AUTHORIZATION [dbo]
CREATE TABLE SomeSchema.#MyTempTable /* specifying the schema
                                        should not be necesssary since
                                        this statement is executed inside
                                        the context of the CREATE SCHEMA
                                        statement
                                     */
(
    TempTableID INT NOT NULL IDENTITY(1,1)
    , SomeData VARCHAR(50) NOT NULL
);
GO

INSERT INTO tempdb.SomeSchema.#MyTempTable (SomeData) VALUES ('This is a test');

SELECT *
FROM tempdb.SomeSchema.#MyTempTable;
GO

SELECT *
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'SomeSchema';

SELECT s.name
    , o.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name LIKE '%MyTempTable%';

DROP SCHEMA SomeSchema;
DROP TABLE #MyTempTable;

The above should create a temporary table named #MyTempTable in the tempdb under the schema named SomeSchema; however it does not.  Instead the table is created in the dbo schema.

Is this expected behavior?  I realize this is certainly an edge-case around the use of schema-specific temp tables; however it would be nice if the engine either provided an error when attempting to create a schema-bound temporary table, or actually did bind it to the schema specified in the DDL.

Also, I do not presently have access to SQL Server 2014 or 2016; does it work as expected on those platforms?

Both references are valid and will resolve correctly, but the #temp table is created under the dbo schema.

Same answer (on your system, some number I couldn't possibly guess):

SELECT OBJECT_ID('dbo.#MyTempTable');
SELECT OBJECT_ID('SomeSchema.#MyTempTable');

Same answer (both 1, which is dbo):

SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.#MyTempTable');
SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('SomeSchema.#MyTempTable');

Being able to specify a schema doesn't buy you anything because you're not going to have collisions (two same-named #temp tables under different schemas) within a session, right?

This is expected behavior. A #temp table is tied to a session, but not to a specific schema. And it works the same all the way up to 2016 CTP 3.2. The parser is probably forgiving, allowing the meaningless schema name in much the same way it allows this errant trailing comma:

CREATE TABLE dbo.foo
(
        bar INT
        ,
);


misplacing of on condition in a join was executing successfully instead of throwing a syntax error I have executed the following CTE with joins

;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asd

the result of the above query was as follows:

The execution plan for the query was as follows:

I was happy with the result, now I cross checked my query which I have used above, identified that there was a misplaced near on condition.

The misplaced join was as follows:

inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asd

As per my understanding there must be a on condition after inner join, but sql server didn't throw an error but executed and results were as expected.

After I changed the on condition and verify the both results and execution plan both were same.

;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
on p.fgh=c.asd
inner join cte2 c2 on c.asd=c2.fgh

Can any one please explain why this thing happens, how sql server was executing?

This is from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying

I find this explanation (picture including) very helpfull


mysql showing two select statements as one output Hobby databasist here.

In my database, the statement below will output the number of posts per hour which discussed subject foo.

select
    count(postid)
from
    myTable
where
    (sub like ("%foo%") or comment like ("%foo%"))
    and replies != 'NULL'
group by
    time div 3600
order by time div 3600 asc;

Output: [time div 3600, foo count]

this statement below would output the number of posts per hour which discussed subject bar.

select
    count(postid)
from
    myTable
where
    (sub like ("%bar%") or comment like ("%bar%"))
    and replies != 'NULL'
group
    by time div 3600
order by
    time div 3600 asc;

Output: [time div 3600, bar count]

How to I combine the output such that:

Output: [Time div 3600, foo count, bar count]

You can combine them like this:

select
    count(CASE WHEN sub like '%bar%' or comment like '%bar%' THEN postid ELSE NULL END) AS count_bar,
    count(CASE WHEN sub like '%foo%' or comment like '%foo%' THEN postid ELSE NULL END) AS count_foo
from
    myTable
where
    (sub like '%bar%' or comment like '%bar%'
     or sub like '%foo%' or comment like '%foo%')
    and replies IS NOT NULL
group
    by time div 3600
order by
    time div 3600 asc;

Note that I also change and replies IS NOT NULL, assuming you have rows that are actually NULL. With the quotes you used, you treated it as a string, but NULL is really nothing. You can't compare it like other datatypes, you have to use the special IS NULL or IS NOT NULL comparison.


Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB.

I know the risks involved based on resources such as http://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html  and also https://serverfault.com/questions/262936/is-replication-from-mysql-5-5-master-to-a-5-1-slave-possible

However, this is a very special case because I am trying to replace the MySQL 5.1 DB with a 5.6 version, and the only way to have minimum downtime is to have a 5.6 DB set up first and then let it replicate from the 5.1 DB, which will in turn also replicate from the 5.6 DB to form a cyclic replication. The purpose of this is to ensure that during the switchover (when the 5.6 DB will be the active one receiving the live writes instead of the 5.1 ), no write data would be lost.

I was curious if anyone has ever had this kind of setup before with the 5.1 DB replicating from the 5.6 DB, and if there were any issues you encountered. I am not sure what SQL commands in the 5.6 DB could be problematic for the 5.1 DB during replication.

To be exact, the 5.6 version is 5.6.21 and the 5.1 version is 5.1.73

Thanks

Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:

120 in MySQL 5.6
107 in MySQL 5.5
106 in MySQL 5.1
 98 in MySQL 4.1/5.0
MySQL master binlog corruption

Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013 : MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

I also used position 4 in these other posts

Apr 02, 2014 : How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743
Mar 05, 2014 : Risk of changing clocktime on MySQL server host

Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
GIVE IT A TRY AT YOUR OWN RISK !!!
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.


Can someone explain the result set from sys.dm_tran_locks? I am trying to understand if the result I am seeing is normal for a long running process or an indication of a problem.

When I run
select * from sys.dm_tran_locks

I am getting over 60k rows showing all from different session ids.

resource_type = PAGE
resource_description = same value
resource_associated_entity_id = same value
request_mode = S
request_status = GRANT

I'm trying to understand if this means that the query currently running needs tuning or if it is normal to see this much rows showing different 'request_session_id' with a shared lock on the same object?

You said, there are different 'request_session_id' showing up, so there are many sessions, not one, to read the same table. Shared locks are compatible with other shared locks so there is no problem at all. If you are warring about the memory comsumed by locks, every lock takes about 96 bytes thes is quite small and server can decide to escalate to table lock when:

  A single Transact-SQL statement acquires at least 5,000 locks on a    single nonpartitioned table or index.
  A single Transact-SQL statement acquires at least 5,000 locks on a    single partition of a partitioned table and the ALTER TABLE SET
  LOCK_ESCALATION option is set to AUTO.
  The number of locks in an instance of the Database Engine exceeds    memory or configuration thresholds.

More about lock escalation here:  Lock Escalation (Database Engine)

Your situation is normal, no lock escalation takes place but just many sessions are reading the same table


Logging SQL warnings in MariaDB Is there a way to log SQL warnings on a MariaDB server (10.2)?

For example, I would like to be able to record (in a log file) this type of warning (strict mode is disabled in this example):

MariaDB [mysql]> select now() = 1;
+-----------+
| now() = 1 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

MariaDB [mysql]> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect datetime value: '1' |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

If you're OK with using strict mode so that warnings are converted to errors, then you can use the MariaDB SQL Error Log Plugin to log these into a special SQL error log file:

INSTALL SONAME 'sql_errlog';
SHOW VARIABLES LIKE 'sql_mode';

gives:

  STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So we have strict mode (STRICT_TRANS_TABLES), which means this will produce an error instead of a warning (title is varchar(5)):

INSERT INTO t1 (id, title) VALUES (1, 'foobar');

Yes, we get:

  ERROR 1406 (22001): Data too long for column 'title' at row 1

... and then that error can be found in the SQL error log at /var/lib/mysql/sql_errors.log.

(Note that the example in the question will not produce an error even in strict mode as it doesn't affect a table. As it's not an error it won't be logged to the SQL error log.)


implement this query inside a cursor I'm having problem ( I have problems with cursor since ever and I really need to understand how the hell it works ).

I would like to run this command in all databases:

SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

I can easily do this with a lot of ways, but I want to pratice cursors.

I made this, but it gives me sintax error:

declare @dbname varchar(max)

declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into dbname

while @@fetch_status=0

use @dbname
SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

close dbcursor
deallocate dbcursor

Where should I put the database variable for the loop in a cursor?

Thanks

EDIT1:

With this, it runs forever. god dammit:

declare @dbname varchar(max)
declare @string varchar(max)

declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status=0

select @string =
'
use '+@dbname+'
SELECT db_name() as  Dtabase_name,
dbtables.[name] as ''Table'',
dbindexes.[name] as ''Index'',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'

exec sp_executesql @string

close dbcursor
deallocate dbcursor

You need dynamic SQL for this, since you need to change the database context (and USE does not accept variables). Try this:

declare @dbname sysname,
  @exec nvarchar(4000),
  @sql nvarchar(max);

    SET @sql = N'SELECT db_name() as Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
   AS indexstats
INNER JOIN sys.tables dbtables
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

declare dbcursor cursor LOCAL FAST_FORWARD for
select [name] from sys.databases
-- WHERE state = 0 /* online */
-- and database_id > 4 /* primary system dbs */

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status = 0
begin
  set @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
  exec @exec @sql;
  fetch next from dbcursor into @dbname
end

close dbcursor; deallocate dbcursor

You can also do this without a cursor:

declare   @cmd nvarchar(max),
      @sql nvarchar(max) = N'';

SET @cmd = N'SELECT db_name() as  Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
  AS indexstats
INNER JOIN sys.tables dbtables
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

SELECT @sql += N'EXEC ' + QUOTENAME(name)
  + N'.sys.sp_executesql @cmd;'
  FROM sys.databases
  -- WHERE state = 0 /* online */
  -- and database_id > 4 /* primary system dbs */

EXEC sys.sp_executesql @sql, N'@cmd nvarchar(max)', @cmd;

You could also look at my sp_msForEachDB replacement.


MySQL running delete trigger on truncate I have a table that has an on delete trigger that updates another table.  THe table I'm trying to truncate has 130,000 rows.  I have duplicate schema and data on my dev server as well as the client's deployment stating server. (5,5,49)

On my dev server (5.5.29) when I truncate the table, it's instant, as in by the time I type show full processlist, the truncate is finished.

When I run the same truncate on the staging server I see this in the process list:

| 3377 | root | localhost:46410 | database | Query   |  533 | Sending data                    | DELETE FROM PriceCode44      |
| 3378 | root | localhost:46800 | database | Query   |  521 | Waiting for table metadata lock | truncate table `PriceCode44` |

There are no foreign keys in the database related to this table.

My solution was to drop the table and recreate it, but I'm concerned that at some point the client is going to need to truncate the table, and since the delete trigger is updating a lot of data it takes a long time.

What could be the possibly cause of this, and how can I avoid it?

Adding if it's not clear - the documented truncate behavior is the desired behavior.  I just want to whack the data in the table.

Here is the create table:

CREATE TABLE `InsynchPriceCode44` (
  `PriceCodeRecord` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `PriceCode` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ItemCode` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerPriceLevel` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ARDivisionNo` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerNo` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Method` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BreakQty1` decimal(9,0) DEFAULT NULL,
  `BreakQty2` decimal(9,0) DEFAULT NULL,
  `BreakQty3` decimal(9,0) DEFAULT NULL,
  `BreakQty4` decimal(9,0) DEFAULT NULL,
  `BreakQty5` decimal(9,0) DEFAULT NULL,
  `DiscountMarkupPriceRate1` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate2` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate3` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate4` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate5` decimal(12,3) DEFAULT NULL,
  PRIMARY KEY (`PriceCodeRecord`,`PriceCode`,`ItemCode`,`CustomerPriceLevel`,`ARDivisionNo`,`CustomerNo`),
  KEY `PriceCodeRecord_2` (`PriceCodeRecord`,`PriceCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord_3` (`PriceCodeRecord`,`ItemCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord` (`PriceCodeRecord`,`ItemCode`,`CustomerNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

This is identical to my dev server.

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them.

That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE (which is Waiting for table metadata lock). As soon as the other transaction commits (or rolls back) and the lock is released, TRUNCATE will manage to get the metadata lock and continue.

There are some differences across the minor 5.x versions regarding the existence of any foreign key constraints that reference the table that affect how TRUNCATE is processed. See this question “TRUNCATE doesn't run ON DELETE trigger” true or false for details but this seems irrelevant in your case, since you are on 5.5, where the TRUNCATE would raise an error if there were any foreign key references from other tables.

On relevant matters, you should upgrade to the latest 5.5 version (5.5.49). Your servers are 4 years and 22 releases behind the latest 5.5 version.

Since you edited that the staging server is 5.5.49 (that's good!) it would be best to update your dev servers to the same version.

I wouldn't entirely dismiss the possibility of a bug but we'll need more evidence to be convinced, like the output of SHOW ENGINE INNODB STATUS; when the issue appears (the Transactions part would show relevant info about locks and blocking).


Database design for millions of posts I have this issue. I have three types of posts a user can generate (some users can generate all some can only generate one type).  I have the following types: main, employee and admin posts.

I expect to have to deal with millions of posts (all three types).  But about 90% of all posts would be main_posts. Also, most reads from the DB would be of the main_posts too.

NOTE
The main_posts table would have about 50-100 more fields than what is currently there. update
those fields would be normalized into different tables. The ideea is that the main-posts table would have the bulk of the posts and therefore would have the most reads and writes.

Which design option is more sound?

Option1

or Option 2

Thank you.

Read this post how StackExchange does it:

http://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases/


Using `nextval` and `currval` inside one CREATE TABLE Let's say that we would like to model a team of people that can have subteams like so:

CREATE SEQUENCE seq_teams;
CREATE TABLE teams (
    tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'),
    sid INTEGER REFERENCES teams(tid) DEFAULT currval('seq_teams'),

    name VARCHAR(40) NOT NULL
);

So, if the team is not a "subteam" of any other team, then tid and sid have the same value. Otherwise, sid is the id of the superteam for team tid.

So, if I want to insert a superteam, I can do just:

INSERT INTO teams (name) VALUES ('superteam 1')

Is there a guarantee that tid and sid will be the same in this case? Is there some better (well known) design that can deal with recursive data structures?

The code you have posted works OK, and is guaranteed to insert the same IDs.

The other way of designing this is to have a separate table that stores the hierarchical relationships, but you don't really need to do so unless you have many:1 or many:many relationships.


Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys.dm_exec_query_stats DMV.  Obviously I can't do this calculation due to the data type mismatch, converting from varchar to bigint won't work in this scenario.  Is there any way around this?

SELECT  DB_NAME(qt.dbid) AS [Database], cp.objtype AS [Cached_Plan_Type],
    cp.usecounts AS [Use_Counts],
    qp.query_plan.value('(//@CardinalityEstimationModelVersion)[1]','INT') AS [CE_Version],
    qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') AS [Estimated_Rows],
    qs.last_rows AS [Last Rows],
    --(qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') - qs.last_rows) AS [Estimate_Skew],
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)],
    CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU Time Average (s)],
    qt.text AS [SQL_Statement],
    qs.query_hash AS [QueryHash],
    qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt
    INNER JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   qt.text NOT LIKE '%sys.%'
ORDER BY [CPU Time Average (s)] DESC OPTION(RECOMPILE);
GO

If you can't do it in one query, then put the results from the first query into a #temporary table.  Now do a second query to do the math.


How can I improve speed of select? I have a table set up like this:
The DB has 10B rows.
CREATE TABLE [Tick].[X_H](
       [utcDT] [datetime2](7) NOT NULL,
       [Symbol] [nvarchar](50) NOT NULL,
       [Bid] [float] NULL,
       [Ask] [float] NULL,
       [BidSize] [float] NULL,
       [AskSize] [float] NULL
) ON [PRIMARY]

ALTER TABLE [Tick].[X_H] ADD  CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED
(
       [utcDT] ASC,
       [Symbol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I want to select a single day's worth of data for a particular Symbol. I am using:
SELECT *
FROM [Tick].[X_H]
WHERE [Symbol] = 'DONKEY'
AND CONVERT(Date, [utcDT]) = CONVERT(Date,'2011-01-02');

This takes 3 times longer than reading the same data from a binary file and deserialising it. Binary read is 42ms and SQL read is 115ms.
What might I try to speed it up?

The suggestion was made to create a separate [Date] column, which I have and I added 2 indexes.
ALTER TABLE [Tick].[FX_HS] ADD [Date] DATE NULL;
UPDATE [Tick].[X_H] SET [Date] = CONVERT(Date,[utcDT])
CREATE INDEX I_Date ON [Tick].[X_H] ([Date]);
CREATE INDEX I_SymbolDate ON [Tick].[X_H] ([Symbol],[Date]);

And I am using query:
SELECT *  FROM [Tick].[X_H] WHERE [Symbol] = 'DONKEY' AND [Date] = '2011-01-02';

But it takes approximately the same amount of time!

Execution plan
Paste the Plan
Query 1: Query cost(relative to batch): 100%
SELECT * FROM [Tick][X_H] WHERE [Symbol]=@1 AND [Date]=@2

As Martin Smith suggested:
SET STATISTICS TIME ON

DECLARE @utcDT DATETIME2(7)
DECLARE @Symbol NVARCHAR(50)
DECLARE @Bid FLOAT
DECLARE @Ask FLOAT
DECLARE @BidSize FLOAT
DECLARE @AskSize FLOAT
DECLARE @Date DATE

SELECT @utcDT = utcDT,
       @Symbol = Symbol,
       @Bid = Bid,
       @Ask = Ask,
       @BidSize = BidSize,
       @AskSize = AskSize,
       @Date = Date
FROM   Tick.X_H
WHERE  ( Symbol = 'DONKEY' )
       AND ( Date = '2011-01-02' )

This takes:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 43 ms.

Your where condition that checks for equality for utcdt converted to a date is SARGable, but uses a dynamic seek.

  The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan.  So, even if you get a seek, the plan might be way off overall. - Dynamic Seeks and Hidden Implicit Conversions - Paul White

Try converting your where condition to an explicit range and see if there is any improvement:

select *
from Tick.X_H
where Symbol = 'donkey'
  and utcdt >= convert(datetime2(7),'20110102')
  and utcdt <  convert(datetime2(7),'20110103')

Based on this answer by Martin Smith the explicit range may reduce unnecessary reads.

References:

Dynamic Seeks and Hidden Implicit Conversions - Paul White
Cast to date is sargable but is it a good idea?
SARGable functions in SQL Server - Rob Farley


Storing time ranges as JSON? I have table with jsonb field - timetable which has next structure:

{
1: [{start:0, finish: 12}],
2: [{start:0, finish: 12}, {start:12, finish:17}],
3: [{start:12, finish: 17}],
4: [{start:0, finish: 12}],
5: [{start:12, finish:17}, {start:17, finish: 0}],
6: [{start:0, finish: 12}, {start:12, finish:17}],
7: [{start:17, finish: 0}]
}

1...7 are days of week, which represent when user is available - morning/afternoon/evening.
I'm doing filter where user could choose morning/afternoon/evening and days of week and get available users. If user checked all three time periods and all days I will have 21 variants. I wrote next query:

SELECT * FROM users WHERE
timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
......
timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}

Please advice me how to optimize my query. I know if user checked all possible periods and days I could just query with timetable is not null. But I want to optimize my task in general.

Moving away from JSONB

JSONB is not the right tool for every job. I'm going to fix your schema because everyone here is about to cry.

Here we use an ENUM type and timerange

CREATE TYPE dow AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  'Friday', 'Saturday', 'Sunday'
);

CREATE FUNCTION time_subtype_diff(x time, y time)
RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM (x - y))
$$ LANGUAGE sql
STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

CREATE TABLE available (
  user_id  int,  -- REFERENCES users,
  dow      dow,
  trange  timerange,
  PRIMARY KEY (user_id, dow)
);

now you can be sane (and we support minutes!)

CREATE TABLE users(user_id) AS VALUES (1::int);

INSERT INTO available VALUES
  ( 1, 'Monday', '[13:42,16:12]' );

SELECT *
FROM users AS u
JOIN available AS a USING (user_id)
WHERE dow = 'Monday' AND a.trange @> '[14:00,15:00]';


Locked out of an old 2008R2 Database We just discovered another server with a database on it. The database is owned by a SQL account SA, with the public and sysadmin roles. The other login is BUILTIN\Users, with only public. I can only connect through this one. SA is disabled, and even if it wasn't, we might not know the password.

Question: Is there any way to enable SA, or create another sql login with sysadmin?

Short answer: start SQL Server with the -m flag from a local admin account.

Step-by-step instructions here:


Select TOP N Rows missing from SSMS temporal table I am using temporal tables within my database and when I right click on my table  in Management Studio 2017 (v17.4 14.0.17213.0) I do not see the Select Top 1000 Rows in the context menu (there is no problem with non temporal tables)

Any ideas how to get this context menu back? I have a feeling it is to do with the version of SQL Server I am running (SQL 13.1.4001.0 Express Edition)

My SSMS 17.5 does show the context-menu for "Select Top 1000 Rows" against a SQL Server 2016 temporal-enabled table on Enterprise Edition.

In SQL Server 2016 Express, via SSMS 17.5, I'm not able to see the "Select Top 1000 Rows" context menu.

This appears to be a purposeful decision by Microsoft within SQL Server Management Studio.


Move Complete ORACLE from one system to another I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server]. [Oralce 11G Enterprise Edition]

Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd Disk (which is not a OS disk)

Since OLD server had reached end of life (from HP) a new HP machine with upgraded RAM (OLD system had 16 GIG where as new system as 64 GIG).

Now Since All oracle Dependencies (RPM, libraries, environmental variables, JAVA, Users and groups) are already made on new server.

So Now Question is ..

Can I remove Disk [Oracle HDD] from OLD server and insert it on NEW server and just start Oracle (sqlplus "/ as sysdba" or dbstart and lnsctrl).

Can it will give any challenge.
(I have cold backups, export backup, & RMAN backup too).

I just want to give a try on above case.

Will this work ??

If it's the same Oracle version, then it might work. Since the old system had 16GB, both have to be 64bit so that shouldn't be an issue, either.

NOTE You're going to save a few minutes at the risk of introducing a subtle problem in your production system. So unless your data isn't worth the time to do a proper export/import, I wouldn't do it.

Anyway. Make a backup of the data before trying this. Use the exp tool for this. The counterpart imp is able to import the data into a new databse. Incidentally, this is how you should do it in the first place.

If you don't want to make a backup, I suggest to at least make a raw copy of the old disk.

Some things that can go wrong:

Your old hard disk might have a faulty sector which now, you won't notice.
There might already be data corruption in your database.
Oracle might have saved the CPU ID or similar data somewhere in the database as a means of copy protection.


SQL Server Migration restore backup vs copy data and log files I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either:

Perform a backup of the database on SQL Server 2000, ship that backup to the new hardware and restore the database in SQL Server 2005; or
Detach the database on SQL Server 2000, copy the data and log files to the new hardware and attach the files in SQL Server 2005.

Is there a difference between these methods?
Which is the safest?

I've been through the pre-upgrade tasks of checking the database with Upgrade Advisior and I have no warnings to resolve before the migration.

Any comments would be greatly received.

For speed (eg less downtime) backup/restore. Backup/restore is quicker because of Instant File Initialisation (only for MDF). The actual backup file is a lot smaller then the MDF/LDF sizes so copies quicker.

It's arguably slighty less risky because there is no downtime on the SQL 2000 instance.

However, detach removes access to the old database. You'll have to take this offline with a backup/restore. If there is a risk of end users connecting, use this (or take offline as soon as backup is complete)

Basically, it's whatever suits you best. I've used both methods for a recent server migration.


How to view databases and tables after 'imp' import using Oracle SQL developer Maybe that's a wrong form of answer...
The problem is:

I've got an Oracle database dump file (very old, created with imp utility)
At last, I've imported it. The command was imp file=E:\Downloads\db_dispatcher\disp.dmp full=y
The console shows me there was success.
Now when I open my Oracle SQL developer and create any connection, I can't list my database (there must be at least one): in 'tables' section I only see this system stuff.

I the DB name should be 'meltshop', so I tried to find it in my Windows Services list. But there's none.

Oracle SQL developer 19, Windows 10. DB was created in 2009.

I am a newbie with Oracle(
The last year I worked with MySql database. Maybe here are any similarities?
I also have php interface; the config shows me

$DBNAME = "<ip>/orcl.meltshop";
$DBLOGIN = "ml2_rt";

Instance, version, etc.:

select instance_name, host_name, version from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION
-----------------
xe
DESKTOP-T6U2873
11.2.0.2.0

The log of my last try:
google disk

SQL Developer shows you the objects of the user you connect as.  It looks like you are connected as SYS or other high privilege user.  Way, way at the bottom of the file tree on the left is the "Other Users" icon.

Click on this and all the database users  (or schemas) will be shown. If "meltshop" is a user they will be listed there.

Now that you have provided more details I see that the user name is ML2_RT. This schema has been imported into the SYSTEM user which is unfortunate as this should not be used for user data. You should try this which points the data to a user

CREATE USER ML2_RT identified by "aPassword" DEFAULT TABLESPACE USERS;
GRANT CONNECT, unlimited tablespace TO ML2_RT;
then reimport again

imp database name/password@XE file=<your file path and dump file name>
  fromuser=ML2_RT touser=ML2_RT

Also you seem to have some errors with the character set due to the import having a different character set.  That will be harder to solve.


Select from a list of values from JSON encoded data I have saved the details of the product in JSON format.
I am wondering how to select from a list of values from a "JSON_ENCODED DATA"

For Example
I want to select TV with screen_type as LED or LCD.

And the value is saved as

{"screen_size":"22 - 24", "screen_type":"LED", "smart_tv":"no", "3d":"no", "resolution":"HD Ready", "hdmi":"1", "usb":"1"}

It is possible to do with UNION query like:

SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LED"%'
UNION
SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LCD"%'

But the query will be too lengthy if there are lot of choices.
Is there are any solution in a simpler way.

I got a solution:

Single check of value in JSON array

SELECT * FROM `nw_object_detail`
WHERE ((`details` like '%"screen_type":"%LED%"%'))

One value from multiple possibilities

SELECT * FROM `nw_object_detail`
WHERE ((`details` like '%"screen_type":[%"%LCD%"%]%')

PHP Solution

if(count($options)>1):
    $condition = '(';
    $count = 1;
    foreach( $options as $option ):
        $condition .= '(`details` like \'%"'.$key.'":[%"%'.$option.'%"%]%\')';
        if(count($options)!=$count):
            $condition .= ' OR ';
        endif;
    endforeach;
$condition .= ')';


MySql - set my.cnf location at server start I want to have two MySql servers running on the same system (for master-slave replication tests purposes).
To be able to do this I need both servers read different my.cnf files.
As I see at startup MySql tries to read from one of default locations.
How can I specify it own my.cnf location for MySql server?

PS.
I am about to use MySql 5.6 under Debian environment.

mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.

These links can be helpful to you
Running multiple instances on the same host
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html


Setting up DBLink between two Sybase Databases I am having dificulty finding anything on the net on this subject.

I am tasked with creating a DBLink between two Sybase Databases. At the moment it's not yet clear if they will be on the same server or on seperate machines in the same network. What I do know is that they are both gonna run on the same Version of Sybase SQL Anywhere 12.

All I can find is how to creat a DBLink from Oracle or MSSQL to Sybase.

I can't seem to find anything about Sybase to Sybase.

I am sorry if my terminology is a little wonky, I am new to Sybase/ASA.

I think it's a terminology problem. SQLA has the concept of a Remote Server - which could be Oracle, MS SQL Server or another SQLA database. Once you have set up a Remote Server, then you can set up remote tables etc.

It doesn't make any difference to the way you use it whether the other SQLA database is on the same database engine, another engine on the same server or a completely different server. Don't forget that SQLA has no concept of a Master database, every database is autonomous.

There are a lot of details for v12.0.1 at this URL : http://dcx.sybase.com/index.html#1201/en/dbusage/ug-accessrd.html

In Sybase Central you will see an entry for Remote Servers in the object tree. The SQL commands are CREATE SERVER and CREATE EXISTING TABLE.

If you have problems it would be worth asking on the dedicated SQLA site: http://sqlanywhere-forum.sap.com/


How to determine if a hyphen (-) exists inside a column In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):

CASE
     WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7')
      AND al.NEW_ADDRESS CONTAINS '-'
     THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

The hyphen can be located anywhere in the column, and so I just need to know if it exists, regardless of where it actually is in the column.

I'm currently using the exact same code that @Josh provided (LIKE '%-%'), but it doesn't work, because it doesn't return the correct data for several specific instances where I know that it "should" be. The exact text in ALT_ADDRESS is: "2754 Churchill Circle". The exact text in NEW_ADDRESS is: "O-89421". However, the results that are returned, does not include the NEW_ADDRESS (O-89421).

I have confirmed that dash in NEW_ADDRESS really matches the dash I'm using the search (ASCII 45).

You didn't mention why the code you provided doesn't work.  CONTAINS is for use with SQL Server's full text search feature.  If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%'
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

You might double check that the character is really a hyphen, something like this should work:

SELECT
    ASCII('-') as RealHypen,
    ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

As an aside, the LIKE expression above is not able to make use of the seeking abilities of a b-tree index in SQL Server, so performance may suffer on large tables if a large scanning operation is required. The best way to mitigate that (should it apply to you or others in a similar situation) depends heavily on context, but in general the main alternatives are:

Indexing a computed column that evaluates the search condition
Using triggers to persist the search result in advance
Use an external tool that's more suited to searching text (Elasticsearch is popular)
Use n-grams (typically for substrings of 3 characters or more)
Use Full Text Search (this won't help with wildcards per se, or this specific case, but can work for word-based searching).

If you are interested in exploring the performance aspect, please ask a follow up question.


Confusion with Tuple Relation Calculus Operations I came upon a question like,

Given the following relational schemas

Student (studId, name, age, sex, deptNo, advisor)

Department (deptId, DName, hod, phoneNo)

Which of the following will be the tuple relational calculus query to obtain the department names that do not have any girl students?

The correct answer given is

{d.Dname | department(d) ∧ ~ ((∃(s)) student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)}

My interpretation to it  is the for every department, the inner logic is trying to find at least one student who is from the same department and is a female. Now if it fails to find one, it return FALSE and the negation will make it TRUE and thus that Dname will be printed. This is perfectly fine.

But if I negate the logic, i.e flow the negate sign then the inner logic becomes,

((∀s ∈ student) s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId)

How does this mean the same as above? I cannot imagine it.

Any help is appreciated. Thanks in advance.

∃s (student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)

For some s, s is a student & s is female & s is in d's department.

~∃s (student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)

For no s, s is a student & s is female & s is in d's department.

~∃x P is ∀x ~P. So ~∃x (Q ∧ R) is ∀x (~Q V ~R) is ∀x (Q → ~R). We get:

∀s (student(s) → (s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId))

For all s, if s is a student then either they are not female or their department is not d's.

For all students, either they are not female or their department is not d's.

For some reason you changed notation (but not meaning) & wrote that as:

∀s ∈ student(s) (s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId))

You don't explain what you think this means so it's not clear why you think it couldn't mean the same as the negated existential. But they mean the same thing.

When all the students are either non-female or not in d's department, the forall is true. But the negated existential is also true. Otherwise some student is female & in d's department, and the forall is false. But the negated existential is also false. (That "otherwise" clearly is the existential.)

  Now if it fails to find one, it returns FALSE and the negation will make it TRUE

You don't explain your notion of universal quantification failure & success. Your "fails to find one returns false" is for the existential. (Ie "finds one returns true".) But "fails to find all returns false" is for the universal. (Ie "finds all returns true".) (Not "fails to find one returns true" or "finds one returns false".)

When the existential fails to find a student that is female & in d's department--false, negated to true--the universal finds all students are either non-female or not in d's department--true. Otherwise, both expressions return false. They always return the same result.


Is it possible to have mysql treat row based updates as "upserts"? When processing row based replication events, mysql replication will break if it's unable to find a row matching the "before image".

Is there any setting to tell mysql, if you don't find that row, just create it?

Consider using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE statements on the master server.

Just note that:

These are unsafe statements for replication, so statement based replication will produce a warning and divergence is possible - consider using MIXED bin log.
These only work on tables with a single unique key or primary key
If there are differences in auto-increment primary key columns, the statements will update divergent records (hence the unsafe classification) (see the manual)


Query to find products with all the given attributes I have a table in mySQL to store product attributes like this:

------------------------------------
| product_id | att_val1 | att_val2 |
------------------------------------
| 1001001    | x202     | 2200     |
| 1001002    | t100     | 180000   |
| 1001003    | T100     | 1080     |
| 1001001    | T100     | 700      |
| 1001005    | SS120    | 1080     |
| 1001001    | SS120    | 1080     |
| 1001004    | SS120    | 1080     |

I want to get all product_ids that have all the asked properties, I came up with this query

SELECT product_id FROM product_attributes
WHERE product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'x202' AND att_val2 = '2200')
AND product_id EXISTS (SELECT product_id from product_attributes WHERE att_val1 = 'SS120' AND att_val2 = '1080')

Is there a better way to achieve this result?

UPDATED (better sample)

I'm going to change the sample data to string values for better clarification

-------------------------------------------
|  product_id  |  attribute  |  value     |
-------------------------------------------
| iPhone 6s    | Screen Size | 4.7        |
| iPhone 7     | Screen Size | 4.7        |
| iPhone 8     | Screen Size | 4.7        |
| iPhone 6s    | Storage     | 64GB       |
| iPhone 7     | Storage     | 128GB      |
| iPhone 8     | Storage     | 64GB       |
| iPhone 6s    | NFC         | FALSE      |
| iPhone 7     | NFC         | FALSE      |
| iPhone 8     | NFC         | TRUE       |

I want to get the iPhone 8 which has Screen Size = 4.7 AND Storage = 64GB AND NFC = TRUE. There could be more than 3 parameters to filter.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = 'Screen Size' AND value = '4.7')
AND product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = Storage AND value = '64GB')
AND product_id IN (SELECT product_id from product_attributes
                        WHERE attribute = NFC AND value = 'TRUE')
GROUP BY product_id

If I use OR in WHERE statement I will end up having iPhone 6s, 7 and 8.

SELECT product_id FROM product_attributes
WHERE product_id IN (SELECT product_id from product_attributes
        WHERE (attribute = 'Screen Size' AND value = '4.7')
        OR (attribute = Storage AND value = '64GB')
        OR (attribute = NFC AND value = 'TRUE')

So I think I've to repeat the AND product_id IN ... part for as many filters as I have.

I hope I'm wrong! and there is a better way to achieve this result.

Your example has syntax errors.  Were you hoping to get just 1001001 from that sample data?

One way:

( SELECT product_id from product_attributes
        WHERE att_val1 = 'x202' AND att_val2 = '2200' )
UNION ALL
( SELECT product_id from product_attributes
        WHERE att_val1 = 'SS120' AND att_val2 = '1080' )
GROUP BY product_id
HAVING COUNT(*) = 2;

The best way:

SELECT product_id
    FROM product_attributes AS a
    JOIN product_attributes AS b  USING(product_id)
    WHERE ( a.att_val1 = 'x202'  AND a.att_val2 = '2200' )
      AND ( b.att_val1 = 'SS120' AND b.att_val2 = '1080' )

Both ways will benefit from INDEX(att_val1, att_val2, product_id).

The schema is called EAV (Entity-Attribute-Value) or Key-Value.  It is a nuisance for writing queries and for performance.  More discussion:   http://mysql.rjweb.org/doc.php/eav


Is there a way to measure replication lag in MySQL with a resolution less than 1 second? Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second?

That is, can replication lag be measured on the microsecond or millisecond level?

Have you tried maatkit heartbeat ? This is the one I'm currently using. You will need another perl process running, but from my perspective, this is much more helpful than the current MySQL mechanism.

I'm also using mk-slave-delay to generate lag. I keep a 1 hour lag in one of the slave to have an extra layer of protection in case of SQL injection or user errors.

These two scripts do the trick.

Saludos


How would I exclude a group of tables based on a name prefix foo_* from a mysqldump? I'd like to exclude a group of tables from a mysqldump command (from a bash script). I might not know the exact table names ahead of time but they will be named with the prefix foo_ or might contain another known suffix like _BAK_[%Y%m%d]

This is based on answers from How do you mysqldump specific table(s)?

To exclude all tables from a mysqldump that starts with foo_, here is the shell script to do it

MYSQL_DATA=mydb
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 102400;"
SQL="${SQL} SELECT GROUP_CONCAT(CONCAT('--ignore-table=',table_name) SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${MYSQL_DATA}'"
SQL="${SQL} AND table_name LIKE 'foo_%'"
EXCLUSION_LIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
mysqldump ${MYSQL_CONN} ${MYSQL_DATA} ${EXCLUSION_LIST} > ${MYSQL_DATA}_tables.sql

To exclude all tables from a mysqldump that starts with foo_, adjust this line

SQL="${SQL} AND table_name LIKE 'foo_%'"

to whatever pattern you need. Maybe you can use the REGEXP operator

SQL="${SQL} AND table_name REGEXP '\_BAK\_2[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Give it a Try !!!


Why does SQL try to convert rows that are excluded by my where clause? I've come across this multiple times, and I'm sure there is a good reason for it - but how do I avoid it?

I'm sure it has to do with the quirks around isnumeric. In English, I have a view that filters on isnumeric(somefield) = 1. I then try to query it using an int in my where clause, and because some other field in the table has character values, the whole thing fails.

I created a SQL fiddle that shows the error.

I tried doing a cast/convert in the select on the view, but the query engine seems to ignore it.

So - why does this happen, and is there a clean way to deal with it?

The original fiddle had a very similar error due to me missing quotes on the insert. That is NOT the problem I was trying to highlight. I have fixed the view by adding quotes to all values being inserted.

The view is not being used, since it is being "optimized away" by the query optimizer.  You can see this by looking at the query plan:

You could create an indexed materialized view, then use the NOEXPAND table hint on the target query to prevent this optimization.

An example:

USE tempdb;

IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
    DROP VIEW dbo.TestView;
    DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test
(
    TableKey int
    , TestField nvarchar(24)
);
GO

Create a the view, with SCHEMABINDING:

CREATE VIEW dbo.TestView
WITH SCHEMABINDING
AS
    SELECT
        t.TableKey
        , t.TestField
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;
GO

Create an index on the view:

CREATE UNIQUE CLUSTERED INDEX PK_TestView
ON dbo.TestView (TableKey);
GO

Insert the test data:

INSERT INTO dbo.Test
select 0,        '0'
union select 1,  'Rejected'
union select 2,  'Unlinked'
union select 0,  '0'
union select 3,  '1'
union select 162,'1000'
union select 16, '10000'
union select 17, '10010'
union select 18, '10011'
union select 19, '10012'
union select 20, '10031'
union select 21, '10041'

Query the view:

SELECT *
FROM dbo.TestView WITH (NOEXPAND)
WHERE Testfield = 1000

Results:

╔══════════╦═══════════╗
║ TableKey ║ TestField ║
╠══════════╬═══════════╣
║ 162      ║ 1000      ║
╚══════════╩═══════════╝

The query plan:

Notice the triangle exclamation in the SELECT node in the query plan above - that is a warning:

  Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)=CONVERT_IMPLICIT(int,[@1],0)) may affect "SeekPlan" in query plan choice

The type conversion warning can be eliminated if we modify the view like so:

CREATE VIEW dbo.TestView
WITH SCHEMABINDING
AS
    SELECT
        t.TableKey
        , TestField = TRY_CONVERT(int, t.TestField)
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;

Now, the view will return the TestField column typed as an integer, and since we are using NOEXPAND in combination with persisted data, the implicit type conversion is eliminated:


ActiveX script throwing runtime error inside DTS package I am a DBA. We have migrated SQL 2000 databases + DTS packages to SQL 2008. One of the DTS packages runs an ActiveX script to open an Excel file (which has runs macros when you open it).  Coding inside the ActiveX script is:

Function Main()
    Set XlApp = CreateObject("Excel.Application")
    call XlApp.Workbooks.Open("\\server\c$\path.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path2.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path3.xls",3)
    set XlApp = nothing
    Main = DTSTaskExecResult_Success
End Function

Sometimes it throws "Active X component has experienced a run time error" without even telling the details about the error and sometimes it runs fine. The DTS package is run using a SQL agent job.
Is there a better and reliable way to do this without using the ActiveX script?

Thanks!

Although I am NOT proficient with .net code, this particular example seems like a good fit to use .net or SSIS components listed below. You could even use PS along with SSIS.

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm

http://pragmaticworks.com/help/dtsxchange/scr/FAQ%20-%20How%20to%20convert%20Scripting.FileSystem%20object%20of%20ActiveX%20Script%20to%20native%20SSIS%20Task.htm

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm


MySQL describe | more With my current setup, it's going to be very hard, if not impossible, to get my terminal window to scroll up and down. That means I can only see 25 rows of text at once. If I have to display something longer, I use |more to be able to scroll through it, like this:

ifconfig | more

But what can I do when a MySQL statement returns more than 25 lines?

describe mysql.user;

I can't just add "| more" in MySQL, and "limit 5" would be invalid syntax here. How can I view the rows a few at a time?

In the MySQL shell, enter

pager less

Then enter your query. Use Enter to scroll down and 'q' to stop browsing the results.

My system doesn't have less, so I used more instead:

pager more

More can scroll down and quit like less, but it can't scroll up.


how to count distinct value per day with start time and end time my title may seem a bit confusing but here's the context.

I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user connects today and disconnects after 2days. So in the table it only has 1 record containing starttime which is today and endtime that contains 2days after today.

Now I want to extract a report of user connection per day. In this case when I group by on the reference code per day, it will only count as 1 for 3 days. but i want it to be counted as 1 per day. What are some ways that I can do this?

@MrBrownstone
The result of that query would be

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

This is not the result that I am looking for. Below is the result that I need:

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760933             2019-02-06            1
445425760933             2019-02-07            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

Since the code 445425760933 was used until 2019-02-07

For MySQL 8+:

WITH RECURSIVE dates AS (
SELECT MIN(DATE(starttime)) `date`
  FROM table1
UNION ALL
SELECT `date` + INTERVAL 1 DAY
  FROM dates
  WHERE `date` < ( SELECT MAX(DATE(endtime))
                   FROM table1 )
),
users AS (
SELECT DISTINCT username
  FROM table1
)
SELECT /* DISTINCT */ users.username, dates.`date`, 1 `count(*)`
FROM users
JOIN dates
JOIN table1 ON table1.username = users.username
           AND dates.`date` BETWEEN DATE(table1.starttime)
                                AND DATE(table1.endtime)


Enforce an "At least one" Relationship The Many-to-many (data model) Wikipedia article uses the example of Authors and Books:

  For example, think of A as Authors, and B as Books. An Author can
  write several Books, and a Book can be written by several Authors.

  In a relational database management system, such relationships are
  usually implemented by means of an associative table (also known as
  cross-reference table), say, AB with two one-to-many relationships A
  -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A
  and B).

With constraints, how would you enforce that a Book must have at least one Author?

Notes:

For this example, there is no such thing as a "primary" Author for the Book.
Answer in SQL (any RDBMS) or in general terms.

How to do this in SQL, using only DDL (FOREIGN KEY and UNIQUE constraints):

CREATE TABLE author
( author_id INT NOT NULL,
  author_name VARCHAR(100),
  CONSTRAINT author_pk
    PRIMARY KEY (author_id)
) ;

CREATE TABLE book
( book_id INT NOT NULL,
  book_title VARCHAR(100),
  CONSTRAINT book_pk
  PRIMARY KEY (book_id)
) ;

CREATE TABLE book_author
( book_id INT NOT NULL,
  author_id INT NOT NULL,
  CONSTRAINT book_author_pk
    PRIMARY KEY (book_id, author_id),
  CONSTRAINT book__book_author_fk
    FOREIGN KEY (book_id) REFERENCES book (book_id),
  CONSTRAINT author__book_author_fk
    FOREIGN KEY (author_id) REFERENCES author (author_id)
) ;

So far this has implemented only a common many-to-many relationship between books and authors. Which means that a book can have 0, 1 or many authors and an author can have 0, 1 or many books.

If we want to have the strict "a book must have at least 1 author", we can add another column in book and the additional constraint:

ALTER TABLE book
  ADD COLUMN author_id INT NOT NULL,
  ADD CONSTRAINT book_must_have_at_least_1_author_fk
    FOREIGN KEY (book_id, author_id)
    REFERENCES book_author (book_id, author_id) ;

Notes:

For the above constraints to work we need a DBMS that has implemented deferrable constraints (eg. Postgres, Oracle) or can insert into / delete from multiple tables in one statement (Postgres). Without any of these, we can relax some of the constraints, for example lifting the NOT NULL from book (author_id) and doing the insert (say of a new book) in 3 steps:

insert into book (with author_id null)
insert into book_author
update book (setting author_id accordingly).

We can then wrap the above steps in a transaction/procedure and then restrict access to the tables only via these transactions/procedures (one procedure for each operation, insert new book and its authors, delete book and its authors, update author, remove author from book, etc).

If not entirely obvious these procedures may get a bit complicated, for example when the "primary" author of a book has to be removed, as the procedure has to search the remaining authors and update book.author_id accordingly or fail/deny the removal if there in no other author left.
Another option - since we mentioned procedures - is to use them from the beginning for all insert/update/delete operations. If designed and tested correctly, they can enforce all the constraints, allowing to not declare any foreign keys.


Including rows from base table with left joins when these joins are 'empty' I've got a query which counts the number of reservations in a table over several 15minutes intervals retrieved from an other table:

SELECT
   t.*, count(r.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval`
   WHERE time_start < '18:00:00'
   AND time_end > '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)

-- we need to filter the assets on their type, which is
-- stored in the assets table
LEFT JOIN assets b
ON r.asset_id = b.id

WHERE r.deleted_at is null
AND b.asset_type_id = 6 -- asset type we need
GROUP BY t.time_start, t.time_end;

This produces the desired table. But when there are no reservations (nof_reservations=0) the whole row disappears:

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+

As I'm retrieving every interval <1800 >1000 I would have thought to get lines like

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 10:00:00   | 10:15:00 | 0                |
| 10:15:00   | 10:30:00 | 0                |
| 10:30:00   | 10:45:00 | 0                |
| 10:45:00   | 11:00:00 | 0                |
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+

What am I missing in the before mentioned query?

Schemas:

CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `asset_id` int(11) NOT NULL,
  `start_utc` datetime NOT NULL,
  `end_utc` datetime NOT NULL
  PRIMARY KEY (`id`),
  KEY `idx_van_utc` (`start_utc`),
  KEY `id_tot_utc` (`end_utc`),
  KEY `idx_boot_id` (`asset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `assets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `asset_type_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `interval` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `time_start` time NOT NULL,
 `time_end` time NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

As a rule of thumb -
when having ... LEFT JOIN t ON ..., conditions on t should be in the ON clause and not in the WHERE clause.

SELECT
   t.*, count(b.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval`
   WHERE time_start < '18:00:00'
   AND time_end > '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null

-- we need to filter the assets on their type, which is
-- stored in the assets table
LEFT JOIN assets b
ON r.asset_id = b.id
AND b.asset_type_id = 6 -- asset type we need

GROUP BY t.time_start, t.time_end;

It's possible you want a slightly different join instead (the rest of the query, SELECT ... FROM ... t and GROUP BY stays the same as above):

-- reservations of the assets
-- and filter the assets on their type
LEFT JOIN reservations r
    JOIN assets b
    ON r.asset_id = b.id
    AND b.asset_type_id = 6 -- asset type we need
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null


What are my options for GUI based Administration & Management of PostgreSQL I have been given the task of working with PostgreSQL 9.1. I have previously worked with Microsoft SQL, and had used the SQL Management Studio. I had used Oracle's SQL developer as well as TOAD when I had to deal with Oracle.

When Compared to such tools, the pgAdmin seems rather limited. I have checked the list at http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools#Lightning_Admin_for_PostgreSQL but many of links I checked were dead.

I would like something which gives me the following features:

Import and Export of data into various formats like .xls/.xlsx, .csv
etc
Ability to see the data in the tables without having to click so much. (for example, in Oracle's SQL developer, once I sign in, I can see the data in my tables in 3 clicks; In pgAdmin, I have to navigate through the hierarchy, to reach my table, and then rightclick to get the 'View Top 100 Rows option')
Append data from an external source (.xls/.csv) to an existing Table.
Intelisence/ Code Completion while writing SQL commands.

you can check EnterpriseDB PEM. http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager


Foreign keys become untrusted after bulk insert In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet)
with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are consistently marked as not trusted in the database. I've dropped and re-created them without NOCHECK options, but within 5-10 minutes they become untrusted again and if I generate a CREATE script it comes out as:

ALTER TABLE [dbo].[Points]  WITH NOCHECK
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

The create script being used is:

ALTER TABLE [dbo].[Points]
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

ALTER TABLE [dbo].[Points] CHECK CONSTRAINT [FK_BadgeId]
GO

There is no replication, there are no third party tools, and I am monitoring all DDL statements on the database so it isn't another user.

I am able to check the constraints fine (using WITH CHECK CHECK on each) but they still become untrusted shortly after. Only maintenance jobs that run are Ola's in the early AM and this happens throughout the day.

Update:

So, after a couple of traces to narrow down possibilities, it seems like a BULK INSERT may be causing the FK to become untrusted. This msdn question states that this is a valid route for the key to become untrusted, which is the first I've heard of it.

So my question now is, is there an alternative strategy to using BULK INSERT that can maintain foreign key is_trusted status? It's being executed in the context of an application running several times per hour. I could have the developers batch their insert statements instead, but I'd prefer not to put an ultimatum on using BULK INSERT if I don't have to.

A BULK INSERT from our application was the cause of untrusted foreign keys, as confirmed by an MSDN question. Similar to BCP, anytime a BULK INSERT is performed then the foreign key is not checked during insert, therefor making it not trusted.

As Kin mentioned, there are simple scripts available to find and fix untrusted foreign keys, but in my scenario the inserts are happening at frequency which makes effort to constantly fix this issue not worth it.

And ypercube suggested using CHECK_CONSTRAINTS option within the bulk inserts which would force the constraint to be obeyed.

I plan to review with the developers to ensure that each usage of BULK INSERT is justified in terms of rows inserted, otherwise it will have to be something to live with.


Left Join table with first match historically What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous value like this:

postgres version: PostgreSQL 9.2.24
Scripts:
CREATE TABLE public.table_a (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_a
(value, "date")
VALUES
('Value 1', '2020-01-29'::date),
('Value 2', '2020-02-01'::date),
('Value 3', '2020-02-12'::date);

CREATE TABLE public.table_b (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_b
(value, "date")
VALUES
('Task A', '2020-02-05'::date),
('Task B', '2020-02-15'::date);

WITH cte AS ( SELECT table_a.value value_a,
                     table_b.value value_b,
                     ROW_NUMBER() OVER (PARTITION BY table_b.value ORDER BY table_a."date" DESC) rn
              FROM table_a
              JOIN table_b ON table_a."date" <= table_b."date")
SELECT value_b, value_a
FROM cte
WHERE rn = 1;

fiddle
Check does this is applicable to your server version.


What is com_set_option? As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's not really any documentation on the subject.

Here's a part of the report done by mysqlreport:

__ Questions ___________________________________________________________
Total          17.43M     7.5/s
  DMS           8.58M     3.7/s  %Total:  49.23
  QC Hits       3.91M     1.7/s           22.42
  Com_          2.54M     1.1/s           14.54
  COM_QUIT      2.48M     1.1/s           14.21
  -Unknown     68.28k     0.0/s            0.39
Slow 10 s          17     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS             8.58M     3.7/s           49.23
  SELECT        6.05M     2.6/s           34.71         70.51
  INSERT        1.36M     0.6/s            7.80         15.85
  UPDATE        1.15M     0.5/s            6.60         13.40
  DELETE       20.35k     0.0/s            0.12          0.24
  REPLACE          61     0.0/s            0.00          0.00
Com_            2.54M     1.1/s           14.54
  set_option    2.32M     1.0/s           13.30
  show_proces  83.16k     0.0/s            0.48
  create_tabl  60.20k     0.0/s            0.35

It probably means that you ran a lot of commands that start with "SET something = somethingelse;"

I would say to set your general_log on for a short while on your dev environment and search for SET commands. It would probably be something to do with setting the server time.

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Com_xxx


Limit Postgres results for a user I would like to grant a Postgres user access to a public_data view and allow them to execute user defined SQL queries against it.

However, the view is quite large (5M rows), so I'd like to prevent users from executing queries that could accidentally return huge results sets.

CREATE VIEW public_data AS SELECT * FROM data limit 10; only seems to give me the first 10 results no matter what query I run against public_data.

Ideally, I'd like users to be able to run SELECT * FROM public_data WHERE ...; but somehow have LIMIT 10 automatically included prior to executing the statement.

Is there any way to do this without creating a custom function?

Alas, no. You cannot parameterize views like that, you need a function to do it.

I rather hope someone proves me wrong, as I much prefer views to set-returning functions.  But I doubt they will.


clone collection in mongoDB in the same db I want ti copy my collection including is indexes without the data.
how can I do it in the most efficient way?
I am using a replica set  version 4.0.3

Another way would be to use the copyTo() function.

NOTE: That will only work for MongoDB 4.0 and earlier.
Starting 4.2 it'll be deprecated. The dump/restore way is the proper way.


SQL Server - Database per company. How to query across databases? We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data.

We are using SQL Server 2012 for the job.

One of the features is a news feed with an ability to post messages, comments, likes etc.

The news feed also has the ability to post to "community."  These "community" posts should be visible to all the companies.  We will have to store those posts at the common level.

My question is - how would you go about designing this?

I was thinking of having duplicate tables in the common database and each of the company databases and just joining the tables for the company feed that appears in the UI.

This brings up another question; how do we manage user IDs for example?  Each post or comment is associated with a user ID.  Would we have to make user IDs unique for all the databases to do that?

Is there a better way to design this?

I don't know if this will help, and this maybe a little much for your situation, but here goes our solution in use today.

All users are grouped into logical entities under a domain (something.com) umbrella. Our particular scenario required an additional layer of Domain->Company->Group->User break out. Not sure if you ever worked with Active Directory or domain trees, but it follows that logic.

Using the domain model, the server itself is at the top, presumably the client of your services would be the administrator. Each company flows like a branch from the server itself. Then flows into user-defined groups which contain users. Each company in this scenario would have a Domain Admin which can administer the xyz.com, abc.com, etc domains, but can't access any other domain.

Each of the service containers (the databases) use a full trust model with the security database to provide services to the users contained in this security database (Think OpenID for perspective). We use a home brewed C++ compiled module for Apache 2.x to provide an application firewall and session security host.

Each run, the module "asks" the security database to record the page hit, produce a random (64 character string) cookie and a session cookie (two total cookies), and authenticate the session. A new session or the same session is returned based on if the cookies match. The session key and anything else relevant is provided to the end-user UI application code over HTTP headers (so the firewalls could sit in front of Google Apps or AppEngine).

Once the UI has the code, the UI can act on behalf of the user and the service database accepts this token and provides direct permissions to the user based on the token. Our application provides the username as well to match a unique user within the service database to provide extended permissions. Since we also use per-transaction random keys, the state is unable to be cached.

This model supports isolation (UI developers are unable to eavesdrop), allows a central security with a delegation aspect, and the ability to provide centralized services. Such as providing a central forum, trending (statistical compiled) data stored centrally (which maybe restricted to paying clients), or maybe even weblogs as we provide to each of our service databases to make decisions to gauge a fraudulent checkout request.

    SERVERS TABLE Minimum of id (unique key), domain, adminuser
                |
                | -------> OPTIONAL Access Control List
                |
    SERVER USER CONTROL LIST Minimum of id (unique key), serverid, actual url you wish to protect.
                OPTIONAL restrictions on AdminOnly, NoSearchEngines, Restricted (Authentication Required)
                |
                |
                | --------> OPTIONAL ENTITY (Sub-Company) Abstraction
                |
                | --------> OPTIONAL GROUPS
                |
                |
    USERS   Minimum of id (unique key), username, serverid (unless you use the entity container which are already tied to the server)
                |
                |
                |
    PERMISSIONS Minimum of id (unique key), server_ucl_id, groupid/entityid/userid (Depending on preference)

    SESSIONS    Minimum of id (unique key), serverid, userid if authenticated

    Provide the sessionid and either userid or username to tamper-resistant proxy code that will provide it to the end-user UI.

We have a lot of bells and whistles in our app to provide a more robust multi-tenant solution, but this is the basics of what we are doing.

The objective is to totally isolate each company into it's own container. Proxy code of some sort should help. C++ is not required, just need code to sit in front of the web application. Also if you want to provide security in one database and shared services in another database, your shared database, which presumably will also be under your client's control, would be allowed to query directly to the security database to see if the user is logged in based on the tokens provided to it, such as when the user posts.

There are no error messages to humans using this model. If anything, any errors would either be pushed via some type of messaging que, email, or log files. The authentication layer is between the proxy code and the security database. User management is within an application that you create for the end-user companies.

This model also scales well with SQL Azure in the middle if needed as it does not need anything like CLR, FT, or etc. The only true weak point will always be the proxy code for security enthusiast. Physical security, and limited user access should lock that down.

Hope I wasn't to verbose and that this helps!