Incorrect value for str_to_date I'm running a simple procedure that reads from a VARCHAR column and inserts data into a TIME column, using this format:

STR_TO_DATE(vTestTime, '%H:%i').

I'm getting the error

  SQL Error (1411): Incorrect datetime value: '09:22' for function str_to_date

I find that a similar statement, SELECT STR_TO_DATE("09:22", "%H:%i") gives me a NULL when I run it from a HeidiSQL client.

I'm sure this syntax used to work and I'm not aware of having changed any settings, can you suggest what I've done wrong?

Edit: Just remembered I have made a recent change, I installed a Wordpress database on the same server, might that have had some affect?

Here's what get running on the server:

mysql> select str_to_date("09:22", "%H:%i");

+-------------------------------+

| str_to_date("09:22", "%H:%i") |

+-------------------------------+

| NULL                          |

+-------------------------------+

1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '09:22' for function str_to_date

This looks like a bug (though it isn't technically a bug, see my first edit towards the end of the answer):

I see the behaviour on MySQL 5.7.20 and 5.7.21 (on Centos 7.4 if it matters):

mysql> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

And the warning is code 1411 Incorrect datetime value: '09:22' for function str_to_date.

ANSI_QUOTES is not enabled, and I get the same warning if I use single-quotes.

From what I understand reading the documentation, the query should work:

  STR_TO_DATE() returns a DATETIME value if the format string contains
  both date and time parts, or a DATE or TIME value if the string
  contains only date or time parts.

This query, containing a date as well as a time part, does work:

mysql> SELECT STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i");
+--------------------------------------------------+
| STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i") |
+--------------------------------------------------+
| 2017-03-17 09:22:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

Running the original query on MariaDB 10.2.14 works correctly:

MariaDB [(none)]> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

EDIT:
This was reported as a bug previously, see here:
https://bugs.mysql.com/bug.php?id=80064

  If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates
  or part of dates are disallowed. In that case, STR_TO_DATE() returns
  NULL and generates a warning:

These modes are enabled by default in MySQL 5.7 (but not in MariaDB 10.2). So, after removing these from the sql_mode system variable, we get the desired result:

mysql> SET sql_mode='';
mysql> select STR_TO_DATE("09:22", "%H:%i");
+-------------------------------+
| STR_TO_DATE("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

EDIT 2:
We don't want to accidentally remove helpful/important modes from the sql_mode system variable, so ideally we should only remove specific modes, in this case NO_ZERO_DATE and NO_ZERO_IN_DATE. This can be done this way:

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', '');

(Use SET GLOBAL sql_mode... if you want to change it for the global scope rather than just the session.)

If you want to set sql_mode in the MySQL config file, then I think you'll just have to "hard-code" it, i.e. just find out what the default modes are for the version you're using, remove the undesired modes, and add the resulting string e.g. to the [mysqld] section of the .cnf file:

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This should be correct for MySQL 5.7. When upgrading to the next version, you'll have to update the setting again.

Note that stored procedures run with their own sql_modes:

  MySQL stores the sql_mode system variable setting in effect when a
  routine is created or altered, and always executes the routine with
  this setting in force, regardless of the current server SQL mode when
  the routine begins executing.

Source: the MySQL stored procedure documentation.

You can also change the sql_mode within a stored procedure.


SSRS subscriptions after failover Shortly I will be migrating a 2008 R2 SSRS installation to a new SQL 2019 Always on AG in AWS.  This one will contain subscriptions, and I have never dealt with them before.  I read the documentation here: SSRS and AG's
and it states that the agent jobs that control the subscriptions are automatically recreated after a failover on the new primary, but still exist on the old primary.
For my other SSRS migrations, I wrote code that automatically takes care of restarting SQL agent and SSRS services on the appropriate nodes after a failover to use the new node.  I have the same jobs on all replicas, and I also have a job step in them that checks to see if the server is the primary.  If it's not, the job will not run.
Since my jobs are already synced and have controlled execution based on primary replica status, id rather use that to control the subscriptions, but will SQL overwrite the existing jobs for the subscriptions?  Or will it only add jobs that are not there?  I don't have a server to test this on at the moment, and I can't find this specific situation online.  I'd rather have the jobs always there, and let the job logic run the jobs based on the replica state.  Is that possible with subscriptions, or do I need to always disable the jobs on the old primary, and let SQL recreate the jobs on the new primary?
I'd also like to rename the sql agent jobs from their nonsense GUID to something more intuitive.  Would that mess with the jobs executing if I did that?

I managed this situation severals times.
The best thing is to not modify the subscrition jobs created by SSRS service. After all they do not have a job schedule. The schedules are managed internally in the SSRS database.
With SQL Server Always on AG the jobs are not replicated because their definition is inside the msdb.
So in an Alwayson AG setup for SSRS, you have an SSRS database inside an AG but you have two (or more) msdb that can contain ssrs jobs and two (or more) SSRS services.
Each of the runnig SSRS services will execute the scheduled jobs handling dividing the workload trough the ssrs database. Here I'm supposing you configured the SSRS to use the AG listener for db connection.
Remember to place the proper ssrs service user in msdb database role (RSExecRole) of each instances as described here:
https://docs.microsoft.com/it-it/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15
The SSRS subscription jobs are recreated every time ssrs starts so when a failover occurs you have to start or restart ssrs service to handle this.
Then what happend depends on what is the role of AG you designed: HA or DR.
In the DR design I suggest you to not leave SSRS service turned off because it will execute subscription on a DR site and this usualy is not good.
In an HA design you could leave the SSRS service turned on so you balance the workload between the two instances (keep attention to licensing too)


Query with a sequence of "and not like" I have a table called VHS_Metadata_Aug52014 with columns:

Title, Description,Tag, Speaker, VIP, FilmingLocation, Notes

I'm trying to write a query that returns all records for which the word "News" is not in any value. There are indeed records of this type. However, the query below returns nothing. So, I'm guessing I'm not quite understanding how to do it. I'm using MS Access, where the wildcard is *.

UPDATE: I just tried also using the syntax:

Not VHS_Metadata_Aug52014!Description like '*News*'

And I'm still getting no hits.

select * from VHS_Metadata_Aug52014 where (
VHS_Metadata_Aug52014!Title Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Description Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Tag Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Speaker Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!VIP Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!FilmingLocation Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Notes Not like '*' & 'News' & '*'
) ;

The problem is most likely some NULL values. Unfortunately, NULL LIKE 'anypattern' will not result in FALSE but in UNKNOWN and NOT UNKNOWN is still UNKNOWN. So any row where one or more of the values is Null, will not be in the result list because WHERE filters all rows that the condition is anything but TRUE (FALSE and UKNOWN get rejected).

Try this change:

select * from VHS_Metadata_Aug52014 where
(Title Not like '*' & 'News' & '*'           Or Title Is Null)
And
(Description Not like '*' & 'News' & '*'     Or Description Is Null)
And
(Tag Not like '*' & 'News' & '*'             Or Tag Is Null)
And
(Speaker Not like '*' & 'News' & '*'         Or Speaker Is Null)
And
(VIP Not like '*' & 'News' & '*'             Or VIP Is Null)
And
(FilmingLocation Not like '*' & 'News' & '*' Or FilmingLocation Is Null)
And
(Notes Not like '*' & 'News' & '*'           Or Notes Is Null)
;


Determing when a stored procedure was last compiled Is there a way to find when a procedure last had an execution plan compiled in Sybase ASE 16?

I don't see a "modified" datetime column in sysobjects, and I haven't been able to find a Sybase equivalent to a queryable plan cache to see if that has a date.

For compile times of procs currently residing in proc cache, see master..monCachedProcedures.CompileDate


Can we migrate Azure SQL Database's objects to In-Memory OLTP? Can we migrate Azure SQL Database's objects to In-Memory OLTP? IF yes then how to perform this step by step?

I cannot see how a write-heavy workload would be a good candidate for In-Memory OLTP on SQL Azure Database. That's because transaction log throughput is limited, no matter how many CPUs you have, and even in Business Critical:

https://www.brentozar.com/archive/2019/02/theres-a-bottleneck-in-azure-sql-db-storage-throughput/


MySQL replication error 1594 Is there a way to restart replication without going over MySQL :: MySQL 5.1 Reference Manual :: 16 Replication.

I'm NOT seeing ANY errors on master and I'm not entirely sure why slave is experiencing issues such as following:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host-FQDN.TLD
                  Master_User: Master_User-REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000057
          Read_Master_Log_Pos: 247673142
               Relay_Log_File: mysql:PR_DB-relay-bin.000041
                Relay_Log_Pos: 5537565
        Relay_Master_Log_File: mysql-bin.000044
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: RDD1,RDD2,RDDX
          Replicate_Ignore_DB: information_schema,mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 25338679
              Relay_Log_Space: 2190669763
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

--log-error=/var/log/mysqld.log (slave):

140630  9:10:53 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 255, event_type: 2
140630  9:10:53 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140630  9:10:53 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
140630  9:10:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 25338679

Can I use CHANGE MASTER TO (MySQL :: MySQL 5.1 Reference Manual :: 16.1.1.10 Setting the Master Configuration on the Slave) and point to respective values:

MASTER_LOG_FILE = Relay_Master_Log_File,
MASTER_LOG_POS Exec_Master_Log_Pos.

The safest position you should replicate from is

Log File mysql-bin.000044
Log Position : 25338679

These represent the Latest Binary Log Event that Executed on the Slave. That is where you pick up replication from. Running CHANGE MASTER TO will erase the relay logs and start with brand new relay logs.


Replace all column values with other values in the same column according to primary key I'm querying the following table:

id
value
applicable

id1
5
Unknown

id1
5
Unknown

id1
6
Yes

id 2
5
Unknown

id 2
5
No

id 2
3
Unknown

I would like to replace all "Unknowns" in the applicable column, with the non-unknown values pertaining to each id.
Ideally, I would like to see this:

id
value
applicable

id1
5
Yes

id1
5
yes

id1
6
Yes

id 2
5
No

id 2
5
No

id 2
3
No

Sorry if this a very easy question, I'm new to this!
Thank you

This expects one one value besides of unknown, if this si not the case you must choose another aggregation function

CREATE TABLE table1
    ([id] varchar(4), [value] int, [applicable] varchar(7))
;

INSERT INTO table1
    ([id], [value], [applicable])
VALUES
    ('id1', 5, 'Unknown'),
    ('id1', 5, 'Unknown'),
    ('id1', 6, 'Yes'),
    ('id 2', 5, 'Unknown'),
    ('id 2', 5, 'No'),
    ('id 2', 3, 'Unknown')
;
GO

UPDATE table1
SET    table1.[applicable]  = t1.[applicable]
FROM   table1
       INNER JOIN (SELECT [id],MIN([applicable]) as [applicable] FROM table1  WHERE  [applicable] <> 'Unknown' GROUP BY [id]) t1
         ON table1.id = t1.id

GO

SELECT * FROM    table1
GO

id   | value | applicable
:--- | ----: | :---------
id1  |     5 | Yes
id1  |     5 | Yes
id1  |     6 | Yes
id 2 |     5 | No
id 2 |     5 | No
id 2 |     3 | No

db<>fiddle here


To generate a script to drop or map the orphan users in a SQL Server Instance I am very new to SQL Server. I have a script which find the orphan users in all the databases and generate the script to either drop or map the user to correct login (If available).
Since i want to run the script in vast number of servers, I thought of using PowerShell to help me. But i got 2 problem now:

Error with the script
Error with Power-shell execution

Problem 1: Following script itself returns output, but still it shows some error. I tried to Debug, But i was unable to do so.
DECLARE @orphan_users TABLE (
    Server NVARCHAR(MAX),
    DBName sysname,
    [user_name] sysname,
    type_desc nvarchar(60),
    default_schema_name sysname NULL,
    create_date datetime,
    modify_date DATETIME,
    [sid] VARBINARY(85),
    cr_type int
);

INSERT INTO @orphan_users (Server,
    DBname,
    [user_name],
    type_desc,
    default_schema_name,
    create_date,
    modify_date,
    [sid],
    cr_type
)
EXEC sp_msforeachdb
'use [?]
SELECT
    @@SERVERNAME
    ,DB_NAME() DBname
    ,name
    ,type_desc
    ,default_schema_name
    ,create_date
    ,modify_date
    ,[sid]
    ,Datalength(sid) AA
from sys.database_principals
where type in (''S'')
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'' ,''MS_DataCollectorInternalUser'')';

DECLARE @db_users TABLE (
    [LogInAtServerLevel] NVARCHAR(MAX),
    [UserAtDBLevel] NVARCHAR(MAX),
    DBname sysname
);

INSERT INTO @db_users ([LogInAtServerLevel], [UserAtDBLevel],DBname)
EXEC sp_msforeachdb
'use [?]
SELECT
    susers.[name],
    users.[name],
    DB_NAME() DBname
from sys.database_principals users
inner join sys.database_role_members link
   on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
   on roles.principal_id = link.role_principal_id
inner join sys.server_principals susers
   on susers.sid = users.sid';

SELECT
    a.Server,
    a.DBName,
    a.user_name,
    b.name,cr_type,
    c.UserAtDBLevel,
    CASE
        WHEN (b.name IS NULL) and (cr_type > 17)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is null)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; ALTER USER ' + QUOTENAME(a.user_name) + ' WITH LOGIN = ' + QUOTENAME(b.name)+';'
        WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is not null)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS NULL)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
        WHEN (b.name IS not NULL) and (cr_type > 17)
            THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
    END as [remediation]
FROM ((@orphan_users a
    left join sys.server_principals b on a.user_name = b.name)
    left join @db_users c on c.DBname=a.DBName and c.LogInAtServerLevel=b.name)
ORDER BY a.user_name;

and results are

But with the error, similar error appear for all databases in the instance

Incorrect syntax near 'tempdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'modelfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'msdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'dbamonfrom'. Msg 102, Level 15, State 1, Line 3

Problem 2: Since the Script is accurate (at least in my Environment) so i tried to run it with power-shell as follows. It only returns the errors, no Output
Invoke-SqlCmd `
    -InputFile "D:\temp\up.sql" `
    -ServerInstance 'abc\sql2012' `
    -IncludeSqlUserErrors

I have tried all the means from redirection of output using > to start and stop transcript. None of them seemed to capture the result that the query gives, However the errors are captured accurately..
It would be a great help for me if any one of the problem is solved.

The dbatools module has already solved this for you.

Get-DbaDbOrphanUser - reports all users in one or more databases with no corresponding login
Repair-DbaDbOrphanUser - finds orphan users and if there is a login with the same name, it will remap them.
Remove-DbaDbOrphanUser - locates orphaned users and drops them from the database (you can do this with Repair-DbaDbOrphanUser -RemoveNotExisting as well).


How to restore single table in Sql Server 2016 I'm wondering how I can restore a single table in SQL Server 2016.
For example,
I backup the table using SSMS task script option.
Delete a row from that table.
Now I want to restore the table to bring the deleted row back, How can I do this in SSMS?
Thanks!

If you used the "Generate Scripts" option and included both the schema and the data, you'll have a script containing something like "CREATE TABLE xyz INSERT INTO xyz VALUES ..." with a series of data values to insert. Possibly with a "DROP TABLE" statement at the top, if you included that option when you scripted it.

In that case, execute the generated script (the .sql file) to re-create the table and insert the values to it. Hopefully if there were any indexes etc. you selected the option to include those too. If it's not as simple as dropping and re-creating that table (like if there are foreign key constraints, identity columns etc) you may need to 'restore' the scripted table to a copy of itself, like my_table_new (if the original is my_table) and then re-insert the row from the 'copy' to the original - using ALTER TABLE my_table SET identity_insert on if necessary.

In the future please consider having a proper backup and restore schedule in place, to take care of this kind of thing. Please tell us you aren't running this update in production...!


MongoDB replica set node failure without arbiter I'm baffled by the fact that this isn't addressed in the official docs (or at my inability to track it down).  From the 3.4 docs:

  Only add an arbiter to sets with even numbers of voting members. If you add an arbiter to a set with an odd number of voting members, the set may suffer from tied elections.

So we have two possible scenarios:

A replica set with an odd number of data nodes, which requires no arbiter (and in fact, adding an arbiter can be detrimental per above)
A replica set with an even number of data nodes, and an arbiter

This is great so long as things are running smoothly.  But what if a data node goes down in each of these scenarios?

We now have an even number of nodes with no arbiter. As I understand it this means you cannot have a successful election.
We now have an odd number of nodes, plus an arbiter. Above docs state that this can lead to tied elections.

This is so painfully obvious, why can't I find any discussion or documentation on it? But what I really want to know is, how do I correctly handle these scenarios?

The context you are missing is that failure of a replica member does not affect the number of configured members the replica set has (or the required voting majority to maintain a primary). Any changes in fault tolerance or replica set election requirements will involve a re-configuration of the replica set (eg. adding/removing members or changing voting members) rather than a replica set member state change (eg. DOWN, RECOVERING, ..).

A three node replica set with one down member still has three members (with a strict majority of two). If you think of it as analogous to a RAID configuration, a three node replica set with one member down is running in degraded mode and cannot tolerate the failure of any further voting members. This allows for continued availability, but you will want to recover or replace the unavailable member to return the replica set to a healthy state.

You can check the current replica set configuration with rs.conf() and the current state of members with rs.status().


exceeding 'max_updates' resource when importing large database I need to import a large database on my shared server.I'm using the following statement in a shell to import my database

mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < NAME_OF-FILE

Unfortunately, I receive the following error

User 'has exceeded the 'max_updates' resource (current value: 36000).

From my understanding max_updates is the number of updates that an account can issue per hour

My host doesn't want to temporarily increase this value.

Can someone recommend a better strategy of perhaps importing the database in smaller chunks to avoid triggering this limit.Perhaps there is a switch or option in mysql that would allow me to do this.

Yes, the "switch or option" that would allow you to do this is the one your host doesn't want to change.  If there were a way for a user to simply work around resource limits, there wouldn't be much point in resource limits.

It's not obvious from the documentation, but the source code makes it fairly clear that this is a limit on the number of queries you can run, per hour, that change almost anything at all about the server.  The use of the word "update" is unfortunate, because this resource limit impacts any query that does something like...

INSERT UPDATE DELETE TRUNCATE LOAD OPTIMIZE ANALYZE REPAIR GRANT REVOKE

... as well as any valid pairing of these:

[ CREATE | DROP | ALTER ] [ TABLE | TRIGGER | VIEW | PROCEDURE | FUNCTION | EVENT ]

If you take a look inside the dump file, it should become readily apparent that you're doing quite a lot of all those things.

But aside from the dump, for a minute, what sort of tiny little service will you be able to host on a server that only lets you execute 36,000/hour ... 600/minute ... 10/sec queries that INSERT/UPDATE/DELETE max, averaged over an hour?  That's tiny... so you have either purchased something really small and inexpensive... or your host has already oversubscribed the server and is trying to mitigate that problem... or your data has already caused more loading than the host anticipated, so you earned a special extra "feature."

The most plausible workaround, to me, seems like it will be to pace your restore by imposing a small delay between each query that gets issued to the server, to stay within your time limit.

cat FILENAME | perl -MTime::HiRes -pe '$| = 1; Time::HiRes::usleep(100000)' | mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME

You can see this in action without actually applying it to the server by just trying this:

cat FILENAME | perl -MTime::HiRes -pe '$| = 1; Time::HiRes::usleep(100000)'

You should see the dump file coming at you, one line at a time, use control-C to break out.  The value 100000 is 100000 microseconds, or 1/10th of one second, which is approximately your limit.  The server won't be executing the queries instantaneously, as it will require some number of milliseconds to process each one, so setting the value at 100000 will probably work.  Also, there is some white space in mysqldump files, and statements like CREATE TABLE will be broken out on multiple lines, even though they'll only count as a single query, which will stretch out the time a bit, also.

Remember, though, that resource limits are per-user, so you can't be doing anything else on the server at the same time with the same account, or the two sessions, together, will combine to push you toward that limit.

If deliberately slowing the pace of your import seems counterintuitive, that's because it is.  Most of us try to get our imports to load faster, not slower, so you'll need to consider also whether this host's limits are going to work for you or whether you need a different plan.

You can get an idea of how many SQL statements are in the dump file with this:

grep -c . FILENAME

This will return a count of lines, which will be close to the number of statements... slightly higher, again, because of how mysqldump files are formatted.  This number divided by 36000 is approximately the fastest possible loading time, in hours, that you could achieve on this file, with this host, with these restrictions in place.  For reference, I ran this against a randomly-selected mysqldump file of 72GB, and the count is 475,309... so, we could estimate a restore time of maybe 13 hours for that particular dump file under these conditions. This number will, of course, vary significantly, so check it against your data source and see what you find.


Best way to flag a row for future deletion? I have users table where a row can be marked for future deletion, so I'm looking for the best way to do this.

I've thought of two options so far:

Add a delete_date to the users table.

Every night a process will query the table and get all users that match delete_date with current date and delete them.
Create a users_to_be_deleted table with the id of the row in users table to be deleted along with a delete_date.

Every night a process will query users table and join on users_to_be_deleted in order to identify which users should be deleted (matching delete_date with current date), and then delete them.

Basically, Option 1 adds a column that can (and in most cases will) contain NULL marks, while Option 2 has no columns that accept NULLs at all, but will require an additional JOIN.

Considering that most users will not be marked for deletion (an estimate is that only 10-20% will), which option is the most efficient? What are the pros and cons of each option?

Normal guiding principles are to,

Not delete data. It's easy to put a boolean flag on the data deleted and then for it to seem like the data is deleted to users, without it really being deleted. Do you really want the data gone forever?
Not create 1:1 relationships. If it's 1:1 just merge it together.
"Match delete_date with current date and delete them" I would suggest storing the delete_queue_date instead. The date that the data entered the queue. That's what matters. The date the row actually gets deleted is a function of that. Generally you don't store f(x) on the table, you store x.


Remove extra return from median calculation I have a FileMaker database where I want to calculate the median value of 3 different fields. I used the code that can be found here.

In my setup, it looks like this:

Let ([
beak.trait = List (beak_depthA; beak_depthB; beak_depthC);
valuelist = SortValues ( beak.trait; 1);
count = WordCount ( valueList );
odd =  MiddleValues ( valueList ; Truncate (count/2;0) +1 ; 1 );
even = MiddleValues ( valueList ; Truncate (count/2;0)    ; 1 )];
Case( Mod ( count ; 2 ) ; odd ; (even+ odd) / 2))

When there is an even number of data in the cells it generates this:

However, when including 3 measurements, I get this:

But if I look into the data, it's there, with an extra line return...

Would there be a way to get the value, when the number of fields is odd without the extra return?

How the code would be affected?

Extra return might comes from SortValues function. Use Trim4 custom function to remove it.

Trim4 ( SortValues ( beak.trait; 1) )

Use it for MiddleValues functions too.


automating a database backup, restore and stored proc call How do I create a process that will:

backup Database A restore it as Database X (needs to be definable)
then run a stored procedure against Database X.
shrink and truncate its log file.

Anybody got ideas?  Could it be a stored proc on the Master db?

The whole process takes hours and is taking me away from valuable time.

BACKUP DATABASE [Database A]
TO DISK = 'C:\Temp\DatabaseA.bak' WITH FORMAT
GO
use master
GO
RESTORE DATABASE [Database X]
FROM  DISK = N'C:\Temp\DatabaseA.bak'
,  MOVE N'DataFile' TO N'C:\Foo\DatabaseX.mdf'
,  MOVE N'LogFile' TO N'C:\Bar\DatabaseX.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 10

GO
use [Database X]
GO
exec StoredProcedureName...
GO
use master
GO
DBCC ShrinkDatabase( [Database X] )


How do I reclaim space in filestream data files for database in a SQL Server Availability Groups I have a SQL 2016 Ent. Edition AG with 3 nodes. We have a database in the AG with 5 filestream tables. Each table is in it's own filestream data file. Today I fixed a bug where all of the files were being saved to one filestream data file by rebuilding the indexes.

In dev, we don't have an AG and the database is in simple recovery. The space was reclaimed. The before and after looks like this:

When I ran the same code in the AG, all of the nodes look like this:

The files were moved to the right filestream data file, but space wasn't recovered from the original data file.

At first I thought it was garage collection, I read Paul Randal's blog post. I shrunk the logfile, then created a junk table, added a ton of rows via an explicit trans, ran a log backup and checkpoint, all on the primary node. The log file did grow, the previously active VLF was marked inactive.

To complicate matters, backups are full copy-only \ log backups on a secondary node.

What's the correct way to reclaim the space in this scenario?

Edit: After following Andy's steps in his blog, space was reclaimed. Each AG node looks like:

I believe you are experiencing a sightly different scenario related to the bug described here, and officially reported here.

When a filestream table is dropped (or in your case rebuilt & moved), garbage collection will not clean up if the database is in an AG.

To work around the bug, you would need to:

Remove the database from the AG
take a log backup
manually run garbage collection
apply log backup(s) to secondary replicas WITH NORECOVERY
rejoin the database to the AG (by applying log backups, you can avoid a full resync)


Join two tables where the other table has no equivalent value in the other table using MySQL I want to join two tables where some rows does not have a value in the other table.

This is the first table:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO
------  ------  ------  ------  ---------  ------  --------
100     1       111     100     100.00     2015    ASDASD
101     2       222     100     100.00     2015    SDFSDF
102     3       333     200     100.00     2015    TEST
103     4       444     200     200.00     2015    FOO
104     5       555     330     200.00     2015    BAR
105     100     100     100     11,000.00  2016    100

This is the second table:

tdno    hiid    sequence_no
------  ------  -------------
100     qwerty              0
A) 1    qwerty              1
2       qwerty              2
3       qwerty              3
4       qwerty              4
5       qwerty              5

What I want is that I want to join the two tables but as you can see there is a row that has no value in the other table. How to join this?

This is my query so far:

SELECT
  ht.ID,
  ht.TDNO,
  ht.lotNO,
  ht.area,
  FORMAT(ht.assess_value, 2) AS 'VALUE',
  ht.EFF AS 'DATE',
  ht.memoranda AS 'MEMO',
  ts.hiid,
  ts.sequence_no
FROM
  history_table ht
  RIGHT JOIN td_sequence ts
    ON ts.`tdno` = ht.`TDNO`
WHERE hiid = 'qwerty'
  AND sequence_no >= '0'
  ORDER BY sequence_no ASC

And the result is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
(NULL)  (NULL)  (NULL)  (NULL)  (NULL)     (NULL)  (NULL)  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5

What I want is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
100     A) 1    111     100     100.00     2015    ASDASD  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5

How to achieve this?

For example, it can look like

FROM table1
JOIN table2 ON table1.TDNO = SUBSTRING_INDEX(table2.tdno, ' ', -1)


My SQL Server table identity is incrementing way too fast I have a table with pretty basic design: 1 primary key (id), 1 unique column (filename) (the filename of the file I am tracking)

The primary key is an identity column (bigint) (I had to make it bigint recently because it's growing so fast)

There are only 82318 rows in the table but the identity for the last row I inserted is 2147891928.

Identity spec is on, starts at 1 increment by 1

If I go through the table ordered by id DESC I can see it jumped from time to time, sometimes by 40, sometimes by 100, 1000, 10000 or more. Is this related to the SQL Server restart seed "FEATURE" in SQL Server 2012? Because it sounds slightly different

I have a script that runs and tries to insert filenames into the SQL from a folder, if the filename already exists nothing should happen because its setup as unique right? or does it use an auto increment digit?

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway.

Example:

IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL
    DROP TABLE #test_identity;

CREATE TABLE #test_identity (
    i int identity(1,1),
    name varchar(50) UNIQUE
)

INSERT INTO #test_identity (name) VALUES ('aaaa')
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 1 row inserted, ident_current = 3
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 5
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('bbbb')

-- 1 row inserted, ident_current = 6
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 8
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('cccc')

-- 1 row inserted, ident_current = 9
SELECT * FROM #test_identity

The SQL Server 2012 jump in identity columns might be part of your issue, but I guess it has a minor impact on what you're seeing.

Identity values are not guaranteed to be contiguous by design, so if you're relying in "no holes" in your identities, you may want to reconsider your assumptions.


How does MySQL rotate binary log files when number extension reaches its maximum? Assume the bin-log file format is: something-bin-log.000122. With each file has a maximum of 1GB size before rotating, and with cap on the number of old binary log files - say 14 days.

What will be the next file when the current one is something-bin-log.999999 and it is about to get rotated?

Will the next binary log file be something-bin-log.000001? If so, how to prevent this from happening and use a bigger numeric extension instead?

An error is generated when the numbers run out:

source code reference


MySQL:Represent years greater than 9999 According to the MySQL reference manual, the year type is restricted to at most 9999 if I understand it correctly. (See http://dev.mysql.com/doc/refman/5.7/en/year.html).

So how then do you store large future or past dates? For example 6000BC or what got me asking, 21974AD? I have tried to find examples of using larger dates but haven't been able to and wondered if it's at all possible to do so.

(Small background to this, was testing some Java date-time validations, and found trying to insert record for the year 21974 into the database, causes an exception, due to the previously mentioned limitation)

If you need to work only with years, you can represent them by means of any integer (that is: from smallint to bigint (tinyint is too small) or numeric/decimal) type of a size big enough for your needs.

If you really need date calculations involving years > 9999, you're out luck. MySQL documentation about dates clearly states that

  The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

If your needs for date ranges are really critical, and you have the possibility of choosing your database, PostgreSQL allows much larger ranges (4713 BC to 5874897 AD for dates, 4713 BC to 294276 AD for timestamps).

Other databases (Firebird, Oracle and MS SQL Server for instance) have date possible values similar to those of mySQL.


Disabled SQL Server Endpoint - now I cannot login I have SQL Server 2005 on my local machine.  During the studies of SQL Server network configuration, I logged into my SQL Server Instance and ran the following script:

USE Master;
GO

ALTER ENDPOINT [TSQL Default TCP]
STATE=STOPPED;

After that I am unable to login.  I can't even login using Windows authentication.

How can I revert the change above?  Is there any another way to fix this situation?

STATE = STOPPED on the [TSQL Default TCP] endpoint prevents the SQL Server service from listening for incoming connections via TCP.  You have several options available that will allow you to reverse this.

Connect with the DAC dedicated admin connection.  You can connect to the DAC by specifying the name of the server as: ADMIN:MyServerName.  Once connected to the DAC, you should be able to start the endpoint again with the following statement:

ALTER ENDPOINT[TSQL Default TCP] state = started;

Use named pipes to connect to SQL server. Use SQL Server
Configuration Manager to enable named pipes if disabled.


ERROR 1114 (HY000): The table is full I am trying create a table using a cross join or Cartesian Product subquery that creates a table with a large number of rows, like at least 148 million rows.  I get this

  ERROR 1114 (HY000): The table is full.

I plan on filtering and querying the table later on, but how can I create this table?

Check, if you did not run out of disk-space (this is the first step, check the tmp file)

From the comments, You seem to have a too low maximum size for your innodb_data_file_path, You cannot host more than 512MB of data in all innodb tables combined.

I think you should switch to an innodb-per-table scheme using innodb_file_per_table.


What does pg_stat_all_tables.idx_scan actually mean? We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan values go up with considerate rate while we're having performance problems.

Does that (idx_scan increasing) actually mean that

the system is actually running lots of full scans through indexes (meaning, reading the whole index from the disk if not already in the cache), or
the system is actually getting some only some tuples ("rows") from those indexes (that is, using the index as intended)?

If the option 1 is true, how to figure out how to get system into state 2? Do I need some additional indexes or are some of my queries bad? All queries get nice performance while the system is under low load but during very high load pretty much any query can get unexpectedly poor execution time.

(The documentation at https://www.postgresql.org/docs/current/monitoring-stats.html only says "Number of index scans initiated on this table".)

Whenever an index is used, that counts as an index scan.  There is no separate counter for full index scans.  You could compare idx_scan to
idx_tup_fetch and see how any rows are returned from the index per scan on average.  But what is the point?  For the most part, it fetches the number of rows it needs to in order to do the job you assigned it.  Maybe you are missing an index that could do the job better, but looking at this stat will not tell that that is the case, nor tell you what hypothetical index that might be.

I do a lot of performance investigation and almost never look at those values, unless I'm trying to tune/debug autovac.  EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, auto_explain are the right tools for the job.


Exadata RMAN vs. Non-Exadata RMAN - any differences? Can you please let me know the differences, if any,  in the architecture of the non-Exadata RMAN vs. that of the Exadata-RMAN from an SBT plugin perspective?

Have you observed from your vast experiences when non-Exadata RMAN and Exadata RMAN existed as separate architectures?

Do you anticipate any behavioral change from a RMAN perspective going forward from now between the standard and Exadata infrastructures ?

There are no architecture differences in RMAN itself.  The difference is in the speed of the backups if written to the Exadata on-board storage.  So the advantages that Exadata brings in terms of hardware would also translate to advantages in backup speed and MTR (mean time to recovery).  But none of those advantages are the result of changes to RMAN.


Reset the numbering at the tabs of the Microsoft SQL Server Management Studio I notice there are some numbering on the tab of each new query (such as the number 52, 56, 57 and 58 as shown in the following pictures)

What does these numbers means and is it possible to reset to the first original values?

The numbers represent the process id of the session associated with that tab - as every time you open a new tab it opens a new connection to SQL Server for you. They are not necessarily sequential but they can be. If you run the following command you should be able to see an entry for each of the tabs/connections that you have open:

select * from sys.dm_exec_sessions

I hope this helps you.


MySQL general query log performance effects One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours.

I wouldn't know how much data it will actually write.

The amount queries for any 24-hour period would be somewhere around 5M.

I could either allow a couple of GB of ramdisk, or mount an NFS share located on an otherwise unused JBOD.

Questions

What would be the effect if the destination where the log is written would get filled?
Would performance of the mysqld in any way (other than the general log write) be affected if the (dedicated) NFS share will start to perform slow due to heavy I/O?

Thanks in advance,

Instead of using the general log, how about going with a query profiler? In fact, you could do query profiling without using any of MySQL's log files and while the queries are still running.
You must use mk-query-digest or pt-query-digest and poll the processlist.
I learned how to use mk-query-digest from this youtube video as a replacement for the slow log: http://www.youtube.com/watch?v=GXwg1fiUF68&feature=colike
Here is the script I wrote to run the query digest program
#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Make sure

you a user called queryprofiler whose password is queryprofiler and who only has the PROCESS privilege
you put */20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.64.95.141  in the crontab to run every 20 minutes (Each profile is 20 min less 10 seconds, Keeps the last 144 copies, and only runs if specfifc DBVIP is present [Alter script to bypass checking for DBVIPs])

The output produces a file with the 20 worst running queries based on the number of times the query was called X avg sec per query.
Here is the sample output of the query profiling summary of mk-query-digest
# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x812D15015AD29D33   336.3867 68.5%     910   0.369656 SELECT mt_entry mt_placement mt_category
#    2 0x99E13015BFF1E75E    25.3594  5.2%     210   0.120759 SELECT mt_entry mt_objecttag
#    3 0x5E994008E9543B29    16.1608  3.3%      46   0.351321 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x84DD09F0FC444677    13.3070  2.7%      23   0.578567 SELECT mt_entry
#    5 0x377E0D0898266FDD    12.0870  2.5%     116   0.104199 SELECT polls_pollquestion mt_category
#    6 0x440EBDBCEDB88725    11.5159  2.3%      21   0.548376 SELECT mt_entry
#    7 0x1DC2DFD6B658021F    10.3653  2.1%      54   0.191949 SELECT mt_entry mt_placement mt_category
#    8 0x6C6318E56E149036     8.8294  1.8%      44   0.200667 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    9 0x392F6DA628C7FEBD     8.5243  1.7%       9   0.947143 SELECT mt_entry mt_objecttag
#   10 0x7DD2B294CFF96961     7.3753  1.5%      70   0.105362 SELECT polls_pollresponse
#   11 0x9B9092194D3910E6     5.8124  1.2%      57   0.101973 SELECT content_specialitem content_basecontentitem advertising_product organizations_neworg content_basecontentitem_item_attributes
#   12 0xA909BF76E7051792     5.6005  1.1%      55   0.101828 SELECT mt_entry mt_objecttag mt_tag
#   13 0xEBE07AC48DB8923E     5.5195  1.1%      54   0.102213 SELECT rssfeeds_contentfeeditem
#   14 0x3E52CF0261A7C3FF     4.4676  0.9%      44   0.101536 SELECT schedule_occurrence schedule_occurrence.start
#   15 0x9D0BCD3F6731195B     4.2804  0.9%      41   0.104401 SELECT mt_entry mt_placement mt_category
#   16 0x7961BD4C76277EB7     4.0143  0.8%      18   0.223014 INSERT UNION UPDATE UNION mt_session
#   17 0xD2F486BA41E7A623     3.1448  0.6%      21   0.149754 SELECT mt_entry mt_placement mt_category mt_objecttag mt_tag
#   18 0x3B9686D98BB8E054     2.9577  0.6%      11   0.268885 SELECT mt_entry mt_objecttag mt_tag
#   19 0xBB2443BF48638319     2.7239  0.6%       9   0.302660 SELECT rssfeeds_contentfeeditem
#   20 0x3D533D57D8B466CC     2.4209  0.5%      15   0.161391 SELECT mt_entry mt_placement mt_category

Above this output are histograms of these 20 top worst-performing queries
Example of the first entry's histogram
# Query 1: 0.77 QPS, 0.28x concurrency, ID 0x812D15015AD29D33 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count         36     910
# Exec time     58    336s   101ms      2s   370ms   992ms   230ms   393ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                905 10.64.95.74:54707 (2), 10.64.95.74:56133 (2), 10.64.95.80:33862 (2)... 901 more
# Databases              1     mt1
# Time range 1321642802 to 1321643988
# bytes          1   1.11M   1.22k   1.41k   1.25k   1.26k   25.66   1.20k
# id            36   9.87G  11.10M  11.11M  11.11M  10.76M    0.12  10.76M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_entry'\G
#    SHOW CREATE TABLE `mt1`.`mt_entry`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_placement'\G
#    SHOW CREATE TABLE `mt1`.`mt_placement`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_category'\G
#    SHOW CREATE TABLE `mt1`.`mt_category`\G
# EXPLAIN
SELECT `mt_entry`.`entry_id`, `mt_entry`.`entry_allow_comments`, `mt_entry`.`entry_allow_pings`, `mt_entry`.`entry_atom_id`, `mt_entry`.`entry_author_id`, `mt_entry`.`entry_authored_on`, `mt_entry`.`entry_basename`, `mt_entry`.`entry_blog_id`, `mt_entry`.`entry_category_id`, `mt_entry`.`entry_class`, `mt_entry`.`entry_comment_count`, `mt_entry`.`entry_convert_breaks`, `mt_entry`.`entry_created_by`, `mt_entry`.`entry_created_on`, `mt_entry`.`entry_excerpt`, `mt_entry`.`entry_keywords`, `mt_entry`.`entry_modified_by`, `mt_entry`.`entry_modified_on`, `mt_entry`.`entry_ping_count`, `mt_entry`.`entry_pinged_urls`, `mt_entry`.`entry_status`, `mt_entry`.`entry_tangent_cache`, `mt_entry`.`entry_template_id`, `mt_entry`.`entry_text`, `mt_entry`.`entry_text_more`, `mt_entry`.`entry_title`, `mt_entry`.`entry_to_ping_urls`, `mt_entry`.`entry_week_number` FROM `mt_entry` INNER JOIN `mt_placement` ON (`mt_entry`.`entry_id` = `mt_placement`.`placement_entry_id`) INNER JOIN `mt_category` ON (`mt_placement`.`placement_category_id` = `mt_category`.`category_id`) WHERE (`mt_entry`.`entry_status` = 2  AND `mt_category`.`category_basename` IN ('business_review' /*... omitted 3 items ...*/ ) AND NOT (`mt_entry`.`entry_id` IN (53441))) ORDER BY `mt_entry`.`entry_authored_on` DESC LIMIT 4\G

There is no performance impact doing this because one DB Connection is maintained to poll the processlist for the duration you specify and hundreds of queries against the processlist are totally lightweight within the confines of a single DB Connection. In light of this, you wll not need a NFS share or any hardware considerations for query performance and analysis.
Give it a Try !!!
UPDATE
mk-query-digest can use either the processlist (via live DB Connection) or tcpdump (via pipe). Here are the options:
--processlist
   --processlist
       type: DSN
       Poll this DSNâs processlist for queries, with "--interval" sleep between.
       If the connection fails, mk-query-digest tries to reopen it once
       per second. See also "--mirror".
   --interval
       type: float; default: .1
       How frequently to poll the processlist, in seconds.

--tcpdump
       tcpdump
           mk-query-digest does not actually watch the network (i.e. it
           does NOT "sniff packets").  Instead, itâs just parsing the out-
           put of tcpdump.  You are responsible for generating this out-
           put; mk-query-digest does not do it for you.  Then you send
           this to mk-query-digest as you would any log file: as files on
           the command line or to STDIN.

           The parser expects the input to be formatted with the following
           options: "-x -n -q -tttt".  For example, if you want to capture
           output from your local machine, you can do something like

             tcpdump -i eth0 port 3306 -s 65535 -c 1000 -x -n -q -tttt \
               â mk-query-digest --type tcpdump

           The other tcpdump parameters, such as -s, -c, and -i, are up to
           you.  Just make sure the output looks like this:

             2009-04-12 09:50:16.804849 IP 127.0.0.1.42167 > 127.0.0.1.3306: tcp 37
                 0x0000:  4508 0059 6eb2 4000 4006 cde2 7f00 0001
                 0x0010:  ....

           Remember tcpdump has a handy -c option to stop after it cap-
           tures some number of packets!  Thatâs very useful for testing
           your tcpdump command.

After 8.5 years, I decided to update this post with a version of my wrapper script using pt-query-digest instead of mk-query-digest:
#!/bin/bash

#
# qdlive : Wrapper Around pt-query-digest
# BASE_FOLDER     : Target Folder for This Wrapper's Output
# PT_QUERY_DIGEST : Absolute path to the pt-query-digest script
# MYCNF           : Absolue path to my.cnf with User Credentials to run pt-query-digest and mysqladmin
# QD_FOLDER       : Folder Where pt-query-digest Output are Stored
#
BASE_FOLDER=${HOME}/qdlive
MYCNF=${BASE_FOLDER}/my.cnf
ERRLOG=${BASE_FOLDER}/err.log
PT_QUERY_DIGEST=/usr/bin/pt-query-digest
QD_FOLDER=${BASE_FOLDER}/reports
mkdir -p ${QD_FOLDER}
cd ${BASE_FOLDER}

#
# 1st Parameter is the Number of Minutes for pt-query-digest to run
# 2nd Parameter is the Number of Copies of the pt-query-digest Output to Keep
#
[   "${1}" == "" ] && exit
[   "${2}" == "" ] && exit
[ ! "${3}" == "" ] && exit

OK1=`echo "${1}" | grep -c "^[1-9]$"`
OK2=`echo "${1}" | grep -c "^[1-9][0-9]$"`
OK3=`echo "${1}" | grep -c "^[1-9][0-9][0-9]$"`
(( OK = OK1 + OK2 + OK3 ))
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Minutes (${1})" >> ${ERRLOG}
        exit
fi

OK=1
[ ${1} -lt  2 ] && OK=0
[ ${1} -gt 60 ] && OK=0
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Minutes (${1})" >> ${ERRLOG}
        exit
fi
RUNTIME_MINUTES=${1}
(( RUNTIME_SECONDS = RUNTIME_MINUTES * 60 ))

OK1=`echo "${2}" | grep -c "^[1-9]$"`
OK2=`echo "${2}" | grep -c "^[1-9][0-9]$"`
OK3=`echo "${2}" | grep -c "^[1-9][0-9][0-9]$"`
(( OK = OK1 + OK2 + OK3 ))
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Copies (${2})" >> ${ERRLOG}
        exit
fi
OK=1
[ ${2} -lt   7 ] && OK=0
[ ${2} -gt 300 ] && OK=0
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Copies (${2})" >> ${ERRLOG}
        exit
fi
COPIES_TO_KEEP=${2}

#
# Parse my.cnf
#
# [mysql]
# host = ....
# user = ....
# password = ...
#
MYSQL_HOST=localhost
MYSQL_HOST=`grep ^host     ${MYCNF} | awk '{print $3}'`
MYSQL_USER=`grep ^user     ${MYCNF} | awk '{print $3}'`
MYSQL_PASS=`grep ^password ${MYCNF} | awk '{print $3}'`
MYSQL_PORT=3306

MYSQL_AUTH="--defaults-file=${MYCNF}"
PTOOL_AUTH="-F ${MYCNF}"

#
# Make Sure mysqld is running
#
MYSQL_RUNNING=`mysqladmin ${MYSQL_AUTH} ping 2>&1 | grep -c "mysqld is alive"`
if [ ${MYSQL_RUNNING} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : mysqld Not Running" >> ${ERRLOG}
        exit
fi

#
# Execute Query Digest
#
DT=`date +"%Y%m%d_%a_%H%M%S"`
QD_OUTPUT=${QD_FOLDER}/qd_${DT}_${RUNTIME_MINUTES}m.rpt
${PT_QUERY_DIGEST} --processlist ${MYSQL_HOST} ${PTOOL_AUTH} --run-time=${RUNTIME_SECONDS}s >${QD_OUTPUT} 2>&1

#
# Rotate Old Reports
#
COPIES_ON_HAND=`ls -l | grep -c rpt$`
if [ ${COPIES_ON_HAND} -gt ${COPIES_TO_KEEP} ]
then
        (( COPIES_TO_ZAP = COPIES_ON_HAND - COPIES_TO_KEEP ))
        FILELIST=""
        SPC=""
        for FIL in `ls *.txt | head -${COPIES_TO_ZAP}`
        do
                FILELIST="${FILELIST}${SPC}${FIL}"
                SPC=" "
        done
        for FIL in ${FILELIST} ; do rm -f ${FIL} ; done
fi


SQl Server: Corrupt Logs Files: Why? I have a SQL Server 2008r2 database server running on Windows Server 2008. On this server every week the log of at least one database throws this error:

  Backup detected log corruption in database dbname. Context is
  FirstSector. LogFile: 2
  'L:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbname_1.ldf' VLF SeqNo: xe1c94
  VLFBase: x280900000 LogBlockOffset: x282d9d600 SectorStatus: 2
  LogBlock.StartLsn.SeqNo: xd0010047 LogBlock.StartLsn.Blk: x213b Size:
  x6400 PrevSize: x8496

I can fix this but I want to know why the error appears frequently. I do not see disk problems in the event viewer. I'm doing log backups every hour with Tivoli TSM. It is a virtual server over VMWare.

Why do log files get corrupted frequently? Where can I start looking?

Paul Randal is probably the undisputed expert in Sql Server corruption.

According to him, 99.8% of all cases of corruption that he has seen were caused by the IO subsystem.  You might find some interesting information here (Myths around causing corruption).

He also talks about doing root-cause analysis here (My SQL Server Database is Corrupt - Now What?!)

The first thing I'd be doing is asking my storage guys for another disk drive and moving all of my logs there.  This 'might' solve the problem if your current log disk drive is having problems, but it wouldn't be a guarantee since there are other possibilities that might be causing your problem.


Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause? Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select?

(assuming unique alphanumeric index and no auto-increments if these matter)

No it won't make a difference. You're using the same records to query and order by regardless of the order of the rows. I'm sure there's other tuning metrics that can be assessed if your query(ies) is(are) running slow.


SQL Query error driving me crazy! I'm wondering if anyone can find the mistake in my SQL-Query which I can't seem to find.. Been starring at the same lines of code for like 2 hours and it's driving me nuts!

So, here's the query... (Quite long but hopefully it's readable..) I'm working in VBA connected to the DB hence the ComboBox values and variables.

The error I get says

  Incorrect Syntax near RfD_

So, here it comes. Brace yourselves:

SQLString =
"SELECT
RfD_.RfD_ID
, RfD_.RfD_Version
, RfD_Status
, RfD_ProjectRef
, RfD_ProjectName
, RfD_StartDate
, RfD_FinishedDate, " & _

"RfD_ActivityName
, Deliverables_.Deliverables_ID
, Deliverables_.Deliverables_Deliverable
, Deliverables_.Deliverables_AcceptanceCriteria
, Deliverables_.Deliverables_Deadline
, Deliverables_.Deliverables_Delivered
FROM [RFD_New].[dbo].[RfD_] " & _

"Inner join [RFD_New].[dbo].[Deliverables_]
    on RfD_.RfD_ID = Deliverables_.RfD_ID" & _
    "And RfD_.RfD_Version = Deliverables_.RfD_Version
where RfD_.RfD_StartDate >= '" & DateFrom & "'
    and RfD_.RfD_FinishedDate <= '" & DateTo & "' " & _

    "And RfD_.RfD_ID in (
                        SELECT RfD_ID
                        From [RFD_New].[dbo].[Approval_]
                        Where Approval_ID = '" & 1 & "'
                            And (Approval_Name = '" & ComboBox2.value & "'
                            or Approval_Name = '" & TwistName(ComboBox2.value) & "') " & _

    "And RfD_.RfD_ID in (
                        SELECT RfD_ID
                        From [RFD_New].[dbo].[RfD_]
                        Where RfD_ProjectRef = '" & ComboBox1.value & "') "

Formatted it a bit to make it more readable but haven't changed anything.
Funny thing is that it works in Microsoft SQL Server Management Studio but not in VBA.

if this is your exact code, you forgot to put a space at the end of this line:

FROM [RFD_New].[dbo].[RfD_]" & _


Why is x  1 false when x is null? I have a query where a row is selected when field1 <> 10.

When field1 is null, this predicate is false!

According to my primitive maths, if null <> 10 = false, then 10 = null.

What is the rational for this?

This is because a comparison to NULL actually results in unknown. NULL is the absence of data and so you are comparing a value against nothing and the result cannot be determined.

  When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.

Taken from the documentation on MSDN.


Failure sending Excel attachments larger than 1.5MB I have innumerous reports that I send from SQL Server to people.
But for some reason there's this single one, with 10k+ rows, that I can't send via dbmmail.
I tried top 7000 and it worked but top 8000 still creates the file, I can open it, but I can't send via dbmail. No errors or alerts about SQL attachment size limit.
File is 1.5MB and I don't receive any error message. I changed only the attachment name to see if the code was wrong but it sent the new file (the smaller one).
It's not an attachment size error because I configured it.
Is there a row limit to send Excel files via dbmail?
SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed' Only says "failed" on these items.
Well code is here for people that wants to send excel files by email with header:
EDIT: I did some tests. I created an .xls file with 7k rows and only 1 column. 15kb file and it was sent.
Then I created a file with 26k rows. 1 column. File was sent. 170KB.
And to my surprise, I ran the query, 9krows (bcp crates a 2mb file) and I just copied into an .xls file. The file was 160KB and I could send it via dbmail.
I'm sure it's a size problem and not row limits.
why is bcp creating such big file?
SELECT * FROM msdb.dbo.sysmail_sentitems says the file was sent, but it wasn't.
SELECT * FROM msdb.dbo.sysmail_unsentitems says "retrying". After some minutes there's 0 rows and still I didn't receive the email.
SQL is configured to send 20MB files.
create TABLE ##tempsss
    (
        ..fields VARCHAR(XX) --it needs to be varchar to be able to use header
    )

insert into  ##tempsss
SELECT
...fields...
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='path\filename.xls'
-------------------------------------------------------------

drop table ##tempsss
-------------------------------------------------------------

Database mail has a max file size limit and that might be preventing it. Please check and increase max size in your database mail and I guess it should resolve the issue


Displaying Distinct Max Value autonumber rows from tables in MySql i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck.

SELECT DISTINCT p.sno AS sno, p.short_order,p.fir_sno
FROM pre_trials p, fir f
WHERE p.fir_sno = f.sno
GROUP BY p.fir_sno
ORDER BY p.sno ASC

For the above query i have the following table:

It should return sno 4,5 because both having the maximum value of sno column.
I don't know how to retrieve the record according to my requirements?
Any help will be appreciated. thanks

EDIT: Show Create Table fir,pre_trials

Assuming that fir is the master table and (sno) its primary key:

SELECT p.sno, p.short_order, p.fir_sno
FROM fir AS f
  JOIN pre_trials AS p
    ON  p.fir_sno = f.sno
    AND p.sno =
        ( SELECT pi.sno
          FROM pre_trials AS pi
          WHERE pi.fir_sno = f.sno
          ORDER BY pi.sno DESC
          LIMIT 1
        ) ;

A composite index on (fir_sno, sno) would help greatly with efficiency of the query.

Also note that I used DESC while your code has ASC beacuse in your explanation and wanted results you mention you want the row with maximum sno.

Look also at a similar question, for further explanation of how the query works: Select ONE most recent post for each author


View the final query executed by PostgreSQL Function I have a PostgreSQL function with Language plpgsql.
Since the EXPLAIN ANALYZE VERBOSE for my function executed like this:

EXPLAIN ANALYZE VERBOSE SELECT foo()

doesn't seem to log any valuable details except the following:

"Result  (cost=0.00..5.25 rows=1000 width=32) (actual time=122.115..122.204 rows=25 loops=1)"
"  Output: postgre.foo()"
"Planning time: 0.026 ms"
"Execution time: 122.243 ms"

I wanted to know if there is any way I can see the real query\queries used by the function, so that I can do EXPLAIN ANALYZE on these SQL queries.

Thanks.

In psql just do \sf name_of_function and it will show you the source code. Or \ef will load it into an editor.


Best database and table design for billions of rows of data I am writing an application that needs to store and analyze  large amounts of electrical and temperature data.

Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for tens of thousands of locations and then analyze the data in a not very complex manner.

The information that I need to store (for now) is Location ID, Timestamp (Date and Time), Temperature and Electricity Usage.

About the amount of the data that needs to be stored, this is an approximation, but something along those lines:
20 000+ locations, 720 records per month (hourly measurements, approximately 720 hours per month), 120 months (for 10 years back) and many years into the future. Simple calculations yield the following results:

  20 000 locations x 720 records x 120 months (10 years back) = 1 728 000 000 records.

These are the past records, new records will be imported monthly, so that's approximately 20 000 x 720 = 14 400 000 new records per month.

The total locations will steadily grow as well.

On all of that data, the following operations will need to be executed:

Retrieve the data for a certain date AND time period: all records for a certain Location ID between the dates 01.01.2013 and 01.01.2017 and between 07:00 and 13:00.
Simple mathematical operations for a certain date AND time range, e.g. MIN, MAX and AVG temperature and electricity usage for a certain Location ID for 5 years between 07:00 and 13:00.

The data will be written monthly, but will be read by hundreds of users (at least) constantly, so the read speed is of significantly more importance.

I have no experience with NoSQL databases but from what I've gathered, they are the best solution to use here.
I've read on the most popular NoSQL databases, but since they are quite different and also allow for very different table architecture, I have not been able to decide what is the best database to use.

My main choices were Cassandra and MongoDB, but I since I have very limited knowledge and no real experience when it comes to large data and NoSQL I am not very certain. I also read that PostreSQL also handles such amounts of data well.

My questions are the following:

Should I use a NoSQL database for such large amounts of data. If not can I stick to MySQL?
What database should I use?
Should I keep the date and time in separate, indexed (if possible) columns to retrieve and process the data quickly for certain time and date periods, or can this be done by keeping the timestamp in a single column?
Is a time series data modeling approach appropriate here, and if not could you give me pointers for a good table design?

Thank you.

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my experience:

Don't use NoSQL for this. The data is highly structured and fits a
relational database perfectly.
I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a
PostgreSQL instance when I started my job and it couldn't handle the
volume of data as it was on a small AWS instance.
I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.

General tip: I store most of the data between two databases, the first is straight-up time series data and is normalized. My second database is very de-normalized and contains pre-aggregated data. As fast as my system is, I am not blind to the fact that users don't even want to wait 30 seconds for a report to load – even if I personally think 30 seconds to crunch 2 TB of data is extremely fast.

To elaborate on why I recommend storing the hour separate from the date, here are a few reasons why I do it that way:

The way that the electrical data is presented is by Hour Ending – therefore, 01:00 is actually the average of the electrical power for the previous hour and 00:00 is Hour Ending 24. (This is important because you actually have to search for two dates to include the 24 hour value – the day you are looking for plus the first mark of the following day.) However, the weather data is actually presented in a forward manner (actual and forecast for the next hour). In my experience with this data, consumers wish to analyze the effect that the weather has on the power price/demand. If you were to use a straight-up date comparison, you would actually be comparing the average price for the previous hour versus the average temperature for the following hour, even though the time stamps are the same. Storing the hour separate from the date allows you to apply transformations to the time with less performance impact than you would see applying a calculation to a DATETIME column.
Performance. I would say at least 90% of the reports that I generate are graphs, normally plotting the price against the hour either for a single date or for a range of dates. Having to split out the time from the date can bog down the speed of the query used to generate the report depending on the date range that you want to see. It is not uncommon for consumers to want to see a single date, Year-on-Year for the past 30 years (in fact for weather this is required to generate the 30 year normals) – this can be slow. Of course you can optimize your query and add indexes, and trust me I have some insane indexes that I would rather not have but it makes the system run fast.
Productivity. I hate having to write the same piece of code more than once. I used to store the date and time in the same column, until I had to write the same query over and over again to extract the time portion. After a while I just got sick of having to do this and extracted it to its own column. The less code you have to write the less chance there is of an error in it. Also, having to write less code means that you can get your reports out faster, nobody wants to be waiting all day for reports.
End users. Not all end users are power users (i.e. know how to write SQL). Having the data already stored in a format that they can bring into Excel (or other similar tool) with minimal effort will make you a hero in the office. If the users cannot access or manipulate the data easily, they will not use your system. Believe me, I designed the perfect system a couple of years ago and nobody used it because of this reason. Database design is not just about adhering to a predefined set of rules/guidelines, it is about making the system usable.

As I said above, this is all based on my personal experience, and let me tell you, it has been a hard few years and a lot of redesigns to get to where I am now. Don't do what I did, learn from my mistakes and make sure you involve the end users of your system (or developers, report authors etc...) when making decisions about your database.


ORACLE - Trying to return MAX or MIN based on a CASE statement I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2.

SELECT
        CASE mod(ROLE_TYPE, 2)
            WHEN 1 THEN MAX(SALARY)
            ELSE MIN(SALARY)
            END
FROM HR;

Is this possible?  I get an error when I run this:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

My guess is that case can't handle aggregate functions like this.  Is there a way to do this in a single select statement so I don't have to do a IF/THEN SELECT?

When you want to display aggregate results for each row without grouping, use the analytic versions of standard aggregate functions. For example:

SELECT
  CASE mod(ROLE_TYPE, 2)
    WHEN 1 THEN MAX(SALARY) OVER ()
    ELSE MIN(SALARY) OVER ()
  END
FROM HR;


How to create login and logout trigger for client audit? I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration...

Can anyone offer me a simple method to create login and logout trigger for audit specific client not the sysadmin,

Thanks in advance.

You can add a simple IF statement to your LOGON trigger, as such:

USE test;
GO
CREATE TABLE LogonAudit
(
    AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID
                PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName NVARCHAR(255)
    , LogonDate DATETIME
    , spid INT NOT NULL
);
GO
GRANT INSERT ON Test.dbo.LogonAudit TO public;
GO
CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() <> 'sa'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;
GO
ENABLE TRIGGER MyLogonTrigger ON ALL SERVER;

This will only audit logons for users not named 'sa' by virtue of the line IF SUSER_SNAME() <> 'sa'

Alternatively, if you want to ONLY audit the login of a specific user, you could write the trigger like:

CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() = 'SomeUser'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;

This will only record when SomeUser logs into the server.


Possible Double-Hop issue. SSIS User changing to anonymous when making a remote query I am attempting to execute a PowerShell script that uses Microsoft.SqlServer.Management.IntegrationServices in an Azure pipeline. The SSIS package is successfully invoked, however, the package fails when a remote connection is attempted. I feel this is double-hop related but can't figure out how to resolve the issue.
Build Server(Domain\ServiceUser) --> Integrations Server(Domain\ServiceUser) --> Database Server(Domain\ServiceUser)
NOTE: Domain\ServiceUser has rights to the SSISDB on that instance and to the remote database.
My Project Import:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

In order to help diagnose if the problem is security related. I am using the procedure below to attempt to invoke the package. When I do that, I get the following error:
--EXECUTE AS LOGIN  = 'Domain\ServiceUser'
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name='ProjectFolder',
    @project_name='ProjectFolderImport',
    @use32bitruntime=False,
    @reference_id=Null
EXEC [SSISDB].[catalog].[start_execution] @execution_id

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Msg 6522, Level 16, State 1, Procedure start_execution_internal, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal":
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
System.Data.SqlClient.SqlException:

And when I uncomment the EXECUTE AS, I get the following:
Cannot execute as the server principal because the principal "Domain\ServiceUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I know the package can make a remote connection to the db server because when I create a task to execute the package and select "Run as" SSIS Package Execution Proxy User "SSIS_Exec" it works fine. I need to figure out how to have my code or stored procedure do something similar to run as SSIS_Exec.

Either configure the directory to allow Kerberos Constrained Delegation to work or configure a SQL Agent job to run your package with a proxy (or the perhaps just Agent Service Account), and kick off the job with sp_start_job from your applciation.


Always fetch 3 records in a particular order I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books.

Table:

StudentID|||BookName|||RowNumber
1000|||ABC1|||27
1000|||ABC2|||31
1000|||ABC3|||28
1005|||ABC2|||28
1005|||ABC3|||20
1005|||ABC1|||25
1006|||ABC1|||12
1006|||ABC2|||14
1006|||ABC3|||15

So my data would be something like the above. I need for each student, ABC1,ABC2,ABC3 should come in the order AND Rownumber to be UPDATED in descending order. So for student 1000:

ABC1||27,ABC2||28,ABC3||31

For student 1005:

ABC1||20,ABC2||25,ABC3||28

For 1006: no change (they're already in the right order).

I have all the above data into a temp table and master data into another temp table. I'm trying to write an update statement but nothing works as expected. I need some help.

CREATE TABLE #t(StudentID INT, BookName VARCHAR(10), RowNumber INT);

INSERT #t(StudentID,BookName,RowNumber) VALUES
(1000,'ABC1',27),    (1000,'ABC2',31),    (1000,'ABC3',28),
(1005,'ABC2',28),    (1005,'ABC3',20),    (1005,'ABC1',25),
(1006,'ABC1',12),    (1006,'ABC2',14),    (1006,'ABC3',15);

;WITH x AS
(
  SELECT StudentID, BookName, RowNumber,
   rn1 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY BookName),
   rn2 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY RowNumber)
  FROM #t
)
SELECT x.StudentID, x.BookName, y.RowNumber
 FROM x INNER JOIN x AS y
  ON x.StudentID = y.StudentID
  AND x.rn1 = y.rn2
ORDER BY x.StudentID, x.rn1;
GO

DROP TABLE #t;

Results:

StudentID  BookName  RowNumber
---------  --------  ---------
1000       ABC1      27
1000       ABC2      28
1000       ABC3      31
1005       ABC1      20
1005       ABC2      25
1005       ABC3      28
1006       ABC1      12
1006       ABC2      14
1006       ABC3      15


Omitting [INTO] in the INSERT ... SELECT statement Does the INTO keyword only makes the query more readable, or does it bring another benefits as well? The queries beneath both seem to run equivalently .

insert ignore
tbl (type, details)
values ('1', '2');

&

insert ignore
into tbl (type, details)
values ('1', '2');

http://www.sqlfiddle.com/#!2/96146de

Check out the correct answer to this question - and Bill Karwin's comment on that answer. I was quite surprised to learn that INTO was optional - but indeed it is, although according to the standard it shouldn't be. Amazingly enough, Oracle does require it - must be the only time that Oracle actually enforces one of the ANSI standards where (many*) others leave it optional!

(*) Sybase, MS SQL Server and MySQL amongst others. IBM DB2, Firebird and PostgreSQL seem to enforce the standard.


How do I resolve Postgresql error, 'no collation was derived for column "foo" with collatable type citext'? Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:

  ERROR:  no collation was derived for column "vin" with collatable type citext
  HINT:  Use the COLLATE clause to set the collation explicitly.

This is a continuation of the problem that I had earlier. It seems the fix suggested in the Release Notes did not catch custom domains. It seems as if the CREATE DOMAIN statement in 8.4 didn't even support the COLLATE clause.

This is how I created the vin type,

CREATE DOMAIN inventory.valid_vin AS citext
  CHECK ( inventory.valid_vin( VALUE ) );

For more information on this find our correspondence on this thread:

Thread: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

Problem

The problem comes right from the horses mouth, Tom Lane,

  Well, if that's how you're creating citext *, then yeah it's broken.
  As of 9.1 the citext type needs to be created with the attribute
  "COLLATABLE = true".  The suggested UPDATE statements are a means
  of correcting a failure to do that after-the-fact, but they don't
  cover any domains that have already been created on top of citext.

Source: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

* I was creating citext however my specific dump had it created, there was nothing special.

So a dump/restore from 8.3 -> 9.1 is not supported if you've got a domain over citext.

Solution

You can get around this problem by first doing:

CREATE EXTENSION citext before you load the dump.
or, by hacking the patch to operate on DOMAINS over citext.


Error creating foreign key from MySQL Workbench I'm trying to synchronize schema changes from MySQL Workbench to my database.  I'm getting the following error when it tries to create a foreign key:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'tomato.#sql-2730_1b8' (errno: 121)

Here's the statement it's trying to execute:

ALTER TABLE `tomato`.`ing_allergy_ingredient`
ADD CONSTRAINT `fk_ai_allergy`
FOREIGN KEY (`allergy_id` )
REFERENCES `tomato`.`ing_allergy` (`allergy_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION

Any ideas what this error means?

You will get this message if you're trying to add a constraint with a name that's already used somewhere else.

According to this post you can check your constraint like this:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
  constraint_name,
  table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
ORDER BY
  constraint_name;


date formatting in Oracle this query:

select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual

will return:

Eighteenth MARCH , Two Thousand Fourteen

i am wondering if there are any additional formatting keywords that will allow including a list of characters like "of" , "the" ... inorder to have an output like:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

or is that requires splitting the date conversion and using a concat function? thanks

for example :

select 'Today is the ' || to_char(sysdate, 'Ddspth') || ' of' || to_char(sysdate,' MONTH, Yyyysp') from dual

figured this out, here is a solution in case someone needs to do something similar...

using double quotes "" around characters will display them as they are...

select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual

will display:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen


Active Directory group permissions not being applied to user I have an odd situation. An Active Directory group was recently created for the sole purpose of being a securable in SQL Server. I created a login based on the AD group, a user based on the login, and granted permissions to the user. All pretty standard stuff. When I impersonate one of the group members (using execute as login='domain\username'), I'm able to perform the granted action. However, when the user himself tries it, he gets "permission denied". I'm at a loss to explain the behavior and subsequently fix it.

Is your service account an AD user? It would need to be to evaluate group memberships, for example. (If you're logged in as an AD user, SQL can evaluate those permissions.)

Microsoft recommends using a domain user for service accounts.
http://msdn.microsoft.com/en-us/library/ms143504.aspx


get account activity in postgresql I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information.

Is there any info in the system table in Postgresql when was the last login or executed command for a user? Or may be who and when created the user.

I do not find any system table show the  last login  time of roles。 if you want to see the
last login time of a role and the executed command of a user ,I suggest you set log_connections
parameter in postgresql.conf ,and you can see those information in your database log files.

In addition, you can use "\s" meta command see history command ,as the following:

[pg90@redhatB ~]$ psql
psql (9.0.9)
Type "help" for help.

postgres=# \s /home/pg90/script/tf/history.sql
Wrote history to file ".//home/pg90/script/tf/history.sql".

But only can see the history of one client.

To see other roles's connections information ,you can see pg_stat_activity for more informations。

postgres=# select usename,datname,count(*) from pg_stat_activity group by usename,datname order by 3 desc;

After that ,  you can make decision where drop a role or not。I also suggest that you shoud check  whether a role owner any objects.


Differences between "Unique Key" and "Primary Key" What are the major differences between Unique Key and Primary Key in MySQL?

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.

Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.


SQL Server 2005 Blocking Issue I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g.  select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on end .

the end user querys run just fine when I test the. It seems to get into a blocking issue when qanother process tries to truncate and reload a table.

how can I troubleshoot this?

The easiest way I look at it is, right click on the database in SSMS and under Reports => Standard Reports => All Blocking Transactions. There are other standard reports with blocking transactions as well but I found all one pretty useful.

The other thing you could do is setup a SQL Server Agent Alert for figuring our locks for your database.

Type: SQL server performance condition alert
Object: SQL Server:Locks
Counter: Anything you want
Instance: Database
Rises above: {x} number of occurrences you want to look for.

And have the alert set to email you when the condition is met (considering you have Database Mail setup already).


standard solution for storing units of measure INTRODUCTION AND RELEVANT INFORMATION:

Let us say I have a table that stores height and weight of a person, something like this :

PersonTable < # ID int, Name varchar(50), Height double, Weight double >

Height is measured in centimeters and Weight in kilograms.

PROBLEM:

My problem is about storing unit of measure for the height and weight.

MY EFFORTS TO SOLVE THIS:

 I could try to implement EAV model.
I could modify the table by adding extra columns that hold unit of measure for Height and Weight ? Something like below:

PersonTable < # ID int,
                Name varchar(50),
                Height double, $ HeightUnit_Id int,
                Weight double, $ WeightUnit_Id int >

Units < #ID int, Desription varchar(20) >

HeightUnit_Id and WeightUnit_Id are foreign keys that reference Id from table Units.

QUESTION:

Is there a better solution than the obvious ones I mentioned above, since I dislike both ( EAV can get messy if done badly, and adding extra "unit columns" seems like a waste of space to me )?

Assuming that measurement types will not be mixed (i.e. any particular row will not mix "kg" and "inches" OR "pounds" and "cm"), and also assuming that at least part of the intention of this Question relates to this now deleted related Question (please note that the following link will not work unless you have enough rep points to see deleted items:  Handling composite attributes with constant part ), then you need only to indicate the system of measurement being used by that row. In this model, you would have a single MeasurementSystem table that is Foreign Keyed to any tables containing measurements. For example (using Microsoft SQL Server syntax):

CREATE TABLE dbo.MeasurementSystem
(
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [PK_MeasurementSystem] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemName NVARCHAR(50) NOT NULL
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [FK_Person_MeasurementSystem]
                      FOREIGN KEY
                      REFERENCES dbo.MeasurementSystem (MeasurementSystemID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (1, N'Metric');

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (2, N'United States customary units');

If you will be mixing measurement systems and/or if you will be allowing for multiple units of measurement even if confined to one system (i.e. allowing for "mm", "cm", "m"), then there will need to be an additional layer to handle the increase in granularity, including the need to have one FK field per each measure column in the Person table. (I can adapt the model above to fit this but will wait for some clarification before doing so).

Or, if there will be a fairly finite / limited amount of combinations of various weight units and height units, then you can instead use each row to represent one of the accepted combinations (e.g. "cm & kg", "m & kg", "mm & kg" / "inch & lb", "foot & lb"). And then "US" vs "Metric" is just a property of each row of the lookup table. For example:

CREATE TABLE dbo.MeasurementUnit
(
  MeasurementUnitID     TINYINT NOT NULL
                                CONSTRAINT [PK_MeasurementUnit] PRIMARY KEY
                                           CLUSTERED,
  MeasurementSystem     CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  MeasurementSystemName NVARCHAR(50) NOT NULL, -- "Metric" or "US Nonsense"
  HeightUnitsName       NVARCHAR(20) NOT NULL, -- "Centimeters"
  HeightUnitsAlias      NVARCHAR(5) NOT NULL, -- "cm"
  WeightUnitsName       NVARCHAR(20) NOT NULL, -- "Kilograms"
  WeightUnitsAlias      NVARCHAR(5) NOT NULL -- "kg"
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementUnitID   TINYINT NOT NULL
                              CONSTRAINT [FK_Person_MeasurementUnit]
                                         FOREIGN KEY
                                        REFERENCES dbo.MeasurementUnit (MeasurementUnitID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

Or, if there needs to be combinations of Units that cross different measurement systems, then I would use separate tables -- one for each measurement type: "WeightUnits" and "HeightUnits". I wouldn't mix units for heights & weights in the same table (i.e. "kg" and "cm" on different rows). For example:

CREATE TABLE dbo.WeightUnit
(
  WeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_WeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  WeightUnitName    NVARCHAR(50) NOT NULL, -- "Kilograms"
  WeightUnitAlias   NVARCHAR(5) NOT NULL -- "kg"
);

CREATE TABLE dbo.HeightUnit
(
  HeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_HeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  HeightUnitName    NVARCHAR(50) NOT NULL, -- "Centimeters"
  HeightUnitAlias   NVARCHAR(5) NOT NULL -- "cm"
);

In this model, each measurement type in any given table has its own personal FK to its measurement units lookup table.


How to turn SELECT statement into a DELETE statement I'm trying to turn this select statement into a delete statement and I'm struggling to not delete all the records in the table.  Here is the SQL select statement I'm using

SELECT  *
    FROM  message_cus_board mcb
    WHERE  mcb.board_id IN (
        SELECT  mb.board_id
            FROM  message_boards mb
            WHERE  mb.customer_id = :customerid
              AND  mb.universal_board = 'Y'
                          )
    ORDER BY  mcb.created_on DESC"

This queries all of the messages from the message_cus_board table and basically I want to delete all of the messages this would normally display except the last 15.  One thing I've tried is taking all of those message_id's and making an array of the most recent 15 then creating a delete statement with those id's like so ->

"DELETE FROM message_cus_board WHERE message_id NOT IN ($string_of_ids)"

The only issue is that this delete query gets rid of ALL other messages, not just the ones associated with the right customer.  I'm struggling to create a delete query that limits the delete to the right customer using similar logic to the select statement above.  I'm sure it's a fairly simple solution but it's currently eluding me.  Thanks for any help!

You can just use the select statement in the where clause and just select for the element you are matching on

DELETE FROM message_cus_board
WHERE message_id NOT IN (
    SELECT message_id
    FROM message_cus_board mcb
    WHERE mcb.board_id IN
        (SELECT mb.board_id
         FROM message_boards mb
         WHERE mb.customer_id = :customerid
         AND mb.universal_board = 'Y'))