Clustered columnstore indexes and foreign keys I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following:

  "We view the clustered columnstore index as the standard for storing large data warehousing fact tables, and expect it will be used in most data warehousing scenarios. Since the clustered columnstore index is updateable, your workload can perform a large number of insert, update, and delete operations." http://msdn.microsoft.com/en-us/library/gg492088.aspx

However if you read further in the documentation you will find under limitations and restrictions:

  "Cannot have unique constraints, primary key constraints, or foreign key constraints."

This confuses me a lot! It is a good practice (not mandatory) to have foreign keys in the data warehouse for a variety of reasons (data integrity, relations visible for semantic layer ...)

So Microsoft advocates clustered columnstore indexes for data warehouse scenarios; however, it can not handle foreign key relationships?!

Am I correct on this? Which other approaches would you advise? In the past, I have used a nonclustered columnstore index in data warehouse scenarios, with drop and rebuild for data loads. However SQL Server 2014 then adds no real new value for data warehouses??

You've got lots of questions in here:

Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....)

A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes don't support that yet.

Q: So MS advocates Clustered Column store indexes for DWH scenarios, However it can not handle FK relationships?!

A: Microsoft gives you tools. It's up to you how you use those tools.

If your biggest challenge is a lack of data integrity in your data warehouse, then the tool you want is conventional tables with foreign keys.

If your biggest challenge is query performance, and you're willing to check your own data integrity as part of the loading process, then the tool you want is clustered columnstore indexes.

Q: However SQL 2014 than adds no real new value for DWH??

A: Thankfully, clustered columnstore wasn't the only new feature in SQL Server 2014. For example, check out the new cardinality estimator.

Q: Why am I so angry and bitter about the way my favorite feature was implemented?

A: You caught me - you didn't really ask that question - but I'll answer it anyway. Welcome to the world of third party software where not everything is built according to your exact specifications. If you feel passionately about a change you'd like to see in a Microsoft product, check out Connect.Microsoft.com. It's their feedback process where you can submit a change, other people can vote it up, and then the product team reads it and tells you why they won't implement it. Sometimes. Most of the time they just mark it as "won't fix, works on my machine" but hey, sometimes you do get some answers.


Foreign key set to cascading but fails to update - why? As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities.

I have a server running MySQL and set up the following table for testing:

Field        Type          Collation       Attributes   Null    Default   Extra
id           mediumint(8)                  UNSIGNED     No      None       AUTO_INCREMENT
domain       varchar(255)  utf8_unicode_ci              No      None
status       enum('...')   utf8_unicode_ci              No      None
replaced_by  mediumint(8)                  UNSIGNED     Yes     NULL

The idea was to have a list of domains that are valid for emails such as "gmail.com" and "hotmail.com".
Now if some domain is recognized as duplicate to another (such as "googlemail.com" to "gmail.com") the status column indicates this and the "replaced_by" column gives the ID to the main domain to be used.

Also, if a domain is recognized as invalid (such as "gymail.com" instead of "gmail.com") the database could store this information and, through status and replaced_by a script could suggest the correct domain.

While ID is the primary key of this table, I wanted to test a foreign key on the replaced_by column with ON DELETE RESTRICT and ON UPDATE CASCASE. so that the replaced_by column stays in-sync with the main ID.

But somehow when I try to update data, I get the following error. Why?

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`domains`, CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`replaced_by`) REFERENCES `domains` (`id`) ON UPDATE CASCADE)

EDIT - some more details to help resolve this case:

CREATE TABLE `test`.`domains` (
`id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`domain` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`status` ENUM( 'valid', 'invalid', 'replace' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`replaced_by` MEDIUMINT( 8 ) UNSIGNED NULL ,
INDEX ( `replaced_by` ) ,
UNIQUE (
`domain`
)

ALTER TABLE `domains` ADD FOREIGN KEY ( `replaced_by` ) REFERENCES `test`.`domains` (
`id`
) ON DELETE RESTRICT ON UPDATE CASCADE ;

UPDATE `test`.`domains` SET `id` = '31' WHERE `domains`.`id` =1

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`domains`, CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`replaced_by`) REFERENCES `domains` (`id`) ON UPDATE CASCADE)

It appears as if this is not permitted in innodb which I assume is the engine you are using. From  http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html:

  If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.


Repmgr: Error to promote Standby Cluster to Master Cluster in failover I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3
IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2
Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000
hot_standby = on
archive_mode = on
listen_addresses = '*'
max_wal_senders = 18
wal_level = hot_standby
shared_preload_libraries = 'repmgr_funcs'
archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.2/32             trust
    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.1/32             trust
    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster
    node=1
    node_name=host1
    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

Standby

    cluster=cluster
    node=2
    node_name=host2
    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all

REPMGRD_ENABLED=yes
REPMGRD_CONF="/etc/repmgr.conf"

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show
Role      | Name   | Upstream | Connection String
----------+--------|----------|--------------------------------------------------------
* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2
  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

check out this useful link :

•   http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html


Oracle SYS Auditing with Audit Trail to DB I have an Oracle Database 11g R2 on Oracle Enterprise Linux. I have enabled SYS auditing as:

SQL> ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;

SQL> ALTER SYSTEM SET audit_trail=OS SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

The audit information is storing in adump folder. But I want to set audit trail to Database(i-e: in SYS.AUD$). I have tried this:

SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

but after this setting, audit records are not going to SYS.AUD$ but instead these are going to operating system adump folder. So how can I set audit trail to database?

This is expected behaviour SYS auditing will always go to the filesystem.

The documentation for AUDIT_FILE_DEST states:

  Oracle Database also writes mandatory auditing information to this
  location, and if the AUDIT_SYS_OPERATIONS initialization parameter is
  *set, writes audit records for user SYS*.

... and the documentation for AUDIT_SYS_OPERATIONS states:

  Enables or disables the auditing of top-level operations directly
  issued by user SYS, and users connecting with SYSDBA or SYSOPER
  privilege. Oracle Database writes the audit records to the audit trail
  of the operating system. If you set the AUDIT_TRAIL initialization
  parameter to XML or XML, EXTENDED, it writes the audit records in XML
  format. On UNIX systems, if you have also set the AUDIT_SYSLOG_LEVEL
  parameter, then it overrides the AUDIT_TRAIL parameter, which writes
  the SYS audit records to the system audit log using the SYSLOG
  utility.

Documentation link here.


SQL Server bulk transfer help needed We've got this very badly designed logging table that we want to add functionality to. The problem is that it's already a scalability nightmare and we want to fix the design before adding to it, but we only have the nightly upgrade window to do it in.

I've seen a lot of articles about the various bulk copy options with SQL Server, claiming "We could move 80M rows in 10 minutes!" but so far my testing doesn't get anywhere near that, and I'd like suggestions on how to improve on what I'm seeing.

Before the upgrade, there's always a full backup. I'm only interested in the end result and don't want a huge transaction log. I also don't want it to take too long and I don't want to blow out the disk space with transaction logs or temp files.

The table's been out there a while, so in our bigger customer dbs, it's already over 50 million rows. Each row is about 350-400 bytes. The columns are something like this

IdentityColID int, [type] int, [subtype] int,
created datetime, author nvarchar(100), Message nvarchar(max)

The problems with the design are

The primary clustered key is (type, subtype, created, identitycolid), so it's an insert nightmare. Blocksplits all over the place. And even doing a SELECT COUNT(*) takes like 8 minutes.
There aren't good indexes to support the types of queries desired

I wanted to make a new table where the primary clustered index is the IdentityColId and add the indexes to support the type of necessary queries, and then copy the existing data over and drop the old table.

So far, I tried using BCP to get the data out, and importing with

BCP
BULK INSERT
INSERT INTO ... FROM OPENROWSET

The bcp export took about 25 minutes and the imports all took about 1.3 hour - about 1.5 hours. With Recovery Model Simple, the transaction log didn't grow but the cpu consumption was in the 60-65% range most of the time.

I tried just using T-SQL INSERT INTO NewTable SELECT * FROM OldTable, but even with Recovery Model Simple, the transaction log gets to 100 gig.

I tried using SSIS data import packages with the from/to model, and the net time was about an hour 20 minutes.  With Recovery Model Simple, the transaction log stayed small.

Then I tried an SSIS Execute SQLTask package to effectively do the INSERT INTO NewTable... line within SSIS.  That got the execution time down to about 1:15, but no matter what the recovery model, the transaction log ended up around 100 gig, though CPU consumption stays modest.

I'd like the end result to be one new table, so the suggestion from some of the articles I've read to parallelize into multiple result tables doesn't seem a profitable path.  But so far, I just can't seem to approach  those stats from the articles I've read.

Anyone have any suggestions on how I can goose this a bit?

I can't give the credit to Sir Swears-A-lot because his response was in a comment, but he was right.  My assumption that dropping the primary key and creating a new one would be effectively the same or more expensive than loading up an empty new table was incorrect.

Setting recovery mode simple, dropping the primary key, recreating it (forcing the reshuffle), and adding all my new indexes afterwards clocked in at 40 minutes.  That beat any bcp/SSIS method I'd tried by more than a half hour, so that was a big improvement.

And the transaction log grew by 10 gig but didn't go nuts like some of my other experiments.


Get problem with Binlog_Do_DB in Master configuration I have followed all the rules for replication. The steps are
I have changed the my.cnf file. My db name is new_db.

server-id   = 1
log_bin = D:/xampp/mysql/mysql-bin.log
binlog_do_db = new_db

Grant the replication for slave

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE new_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

After that the result is

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      481 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Binlog_Do_DB column is empty. new_db should be there.

I don't understand what I missed. Please help to solve the problem.

In Windows, you would need to modify the my.ini file and not the my.cnf file

Locate you my.ini file

If MySQL is being run as a service

Enter "services.msc" on the Start menu search box.
Find MySQL service under Name column, for example, MySQL56.
Right click on MySQL service, and select Properties menu.
Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Otherwise the my.ini file is probably in a sub-directory of D:\xampp\mysql\

Once you've added in the binlog_do_db parameter, be sure to restart the MySQL service.

Edit by RolandoMySQLDBA

You can restart mysql from services.msc window by right clicking and choosing or you can login from DOS CommandLine as Administrator as run

C:\> net stop mysql
C:\> net start mysql

Also, make sure your settings are under the [mysqld] group header in my.ini


Why does this query cause a deadlock? Why does this query cause a deadlock?

UPDATE TOP(1) system_Queue SET
  [StatusID] = 2,
  @ID = InternalID
WHERE InternalID IN (
    SELECT TOP 1
      InternalID FROM system_Queue
    WHERE IsOutGoing = @IsOutGoing AND StatusID = 1
ORDER BY MessageID ASC, InternalID ASC)

Deadlock graph added:

<keylock hobtid="72057594236436480" dbid="9" objectname="Z.dbo.system_Queue" indexname="PK_system_Queue" id="lock5b25cc80" mode="X" associatedObjectId="72057594236436480">
    <owner-list>
     <owner id="processc6fe40" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc7b8e8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594405453824" dbid="9" objectname="Z.dbo.system_Queue" indexname="IX_system_Queue_DirectionByStatus" id="lock48cf3180" mode="S" associatedObjectId="72057594405453824">
    <owner-list>
     <owner id="processc7b8e8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc6fe40" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>

ADDED:

Thank you Sankar for article that has solutions how to avoid this type of deadlock:

eliminate unnecessary columns from reader’s projection so he does not have to look up the clustered index
add required columns as contained columns to the non-clustered index to make the index covering, again so that the reader does not have look up the clustered index
avoid updates that have to maintain the non-clustered index

It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table.

The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess is clustered and also part of the IX_system_Queue_DirectionByStatus key value).

Anyway, my guess is this query would only succeed on the rare chance that the index values it is selecting and updating are not conflicting. Is it deadlocking each time you execute (I assume that it would be).

Here is a link that explains deadlocks in more detail: http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx


Feature SQL Server 2012 - Filetable - The location of the file can not be opened When trying to access the files available on the virtual directory created for FileTable, you receive the following error message:

  "The location of the file can not be opened. Access is not enabled or not you have the permissions for it."

Already granted all permissions, however, the application displays this error.

You need to have

  "Allow remote clients access to FILESTREAM data"  selected  as shown below :

Also, you need to leave the windows share as a Globalroot path and manage permissions through table properties, not Windows share properties.

Refer to : Prerequisites for FileTable


UPDATE on column referenced in sub-query We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following:

update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
      workSet.SELECTABLE = 0
where workSet.MARK = 1 and
        (workSet.TRANSFORMED_TYPE, workSet.TRANSFORMED_KEY) in (
            select source.TRANSFORMED_TYPE, source.TRANSFORMED_KEY
            from CE_WORKSET_READ_ENTRY source
            where source.SELECTABLE = 0 and source.MARK = 1);

I expect the database to evaluate the condition on the 'old' state, which is indeed the case. However, the statement takes surprisingly long to complete as the number of updated elements grows. Why is this?

Additional infos: there are no indexes, no primary keys, it is used as a  temporary store like a queue. The table held  ~100'000 entries when the update took extremely long. Unfortunately, I can no longer reproduce the original explain plan since the data is now gone.

Can you create indexes? Are TRANSFORMED_TYPE and/or TRANSFORMED_KEY unique?
The DB hast to compare xxx rows from "where workSet.MARK = 1" with yyy rows from "where source.SELECTABLE = 0 and source.MARK = 1" in subquery(FTS).
So you can calculate yourself: xxx * yyy rows = zzz rows to compare with full table scans.

This is faster but with indexes could perform even better.

update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
workSet.SELECTABLE = 0
where workSet.MARK = 1 and
exists (
select 1
from CE_WORKSET_READ_ENTRY source
where
source.TRANSFORMED_TYPE = workSet.TRANSFORMED_TYPE
and source.TRANSFORMED_KEY =workSet.TRANSFORMED_KEY
and source.SELECTABLE = 0
and source.MARK = 1);

If TRANSFORMED_TYPE and/or TRANSFORMED_KEY are unique then you can remove the subquery.


Best approach for moving tempdb .ndf files During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and  not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files are on 'C:'.

I'm thinking of moving the .ndf files by performing following steps:

 1.

    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 , FILENAME = 'F:\MSSQLData\temp2' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 , FILENAME = 'F:\MSSQLData\temp3' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 , FILENAME = 'F:\MSSQLData\temp4' )

Stop SQL Server (the instance isn't doing anything currently).
copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location
Restart SQL Server.
Check if it worked:

    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');

Is that the best approach? The instance supports only 3 small databases (<10GB total).

The best approach is the one you can find on the Moving the tempdb database section of the documentation.

Your method is close to the one Microsoft recommends, except by the 2nd and  3rd steps you listed and the reason is:

  Because tempdb is re-created each time the instance of SQL Server is
  started, you do not have to physically move the data and log files.


Is it possible to gain the precise position of a live mysql database when performing full backup I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups.

The production server is on the west coast while business hours are on the east coast.

When I perform the fullback up I query the master for is log and position. I put these values in the filename of the zip that I compress the databases to.

I'm having difficulties using mysqlbinlog to create incremental sql file from a certain position that I know the backup has ended at.

Would running Show Master Status then then running mysqldump --flush-logs --single-transaction... suffice to get accurate information in order to restore.

I know the ideal setup is to backup a slave database since locking won't hurt the production environment but at the moment I haven't made the switch.

Am I not getting the desirable results because I'm not locking all the tables?

Thanks in advance

EDIT

I realized with the help of RolandoMySQLDBA that I wasn't pointing to the appropriate binlog file. I was instead pointing to the whole directory binlog/bin-log.[0-9]* > sql.txt. So with just the position I was hitting logs that started much earlier than desired. So I changed my script to pull the appropriate binlog file as well as the position and that worked like a charm.

Thanks

When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start.

Just add the --master-data option

mysqldump --master-data=2 --flush-logs --single-transaction...

Using --master-data=2 will record the binary log filename and position in the form of a  CHANGE MASTER TO command. It is recorded as a comment.

Using --master-data=1 will also record the binary log filename and position in the form of a  CHANGE MASTER TO command. It is not recorded as a comment, but as a command that will be executed when you reload the mysqldump.

In both cases, you can always find that CHANGE MASTER TO command on line 23 of the dump. I mentioned this before in my old post MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos under the heading "BUT WAIT..."

To retrieve that line from a mysqldump file named myfullbackup.sql, simply run

head -23 myfullbackup.sql | tail -1 > MasterLogFilePos.sql

and the CHANGE MASTER TO command will be in it

If you run the mysqldump with

mysqldump --master-data=2 --flush-logs --single-transaction...

the Master will continue allowing reads and writes to all InnoDB tables.


Access denied when disabling agent job, despite SqlAgentOperator membership I am attempting to disable a SQL agent job from an account which a member of the SqlAgentOperator role (but not sysadmin)

The doco says this should be possible

  3 SQLAgentOperatorRole members can enable or disable local jobs they
  do not own by using the stored procedure sp_update_job and specifying
  values for the @enabled and the @job_id (or @job_name) parameters. If
  a member of this role specifies any other parameters for this stored
  procedure, execution of the procedure will fail.

...and indeed the code for msdb.dbo.sp_update_job appears to support this - it has explicit handling for exactly this scenario. Yet if I run:

use [MSDB]
go
select suser_sname()
if(is_member('SqlAgentOperatorRole')<>1)
    raiserror('Not in required role',16,1)
go
exec dbo.sp_update_job @job_name='a job', @enabled=0

... I get the following error:

  Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1 The
  EXECUTE permission was denied on the object 'sp_update_job', database
  'msdb', schema 'dbo'.

The error message appears to indicate the proc's not even being run, but even granting that user explicit EXECUTE on that stored proc doesn't seem to fix it.

Does anyone know how to grant a user the ability to disable/enable SQL agent jobs that they don't own, or has successfully used this functionality. Maybe it's just broken in SQL 2012 SP1 CU4 (which is what I am using)

Please check to see if there is a DENY on the sp_update_job procedure.  This may be a direct DENY to your login or it may be a DENY on some group that has your login as a member.  DENY  always trumps a grant.

If so, then removing your login from the group or having an administrator REVOKE the DENY should solve the problem.

Response prompted by reading Piers7 request.  Time has passed, of course.


Can't rely on INFORMATION_SCHEMA.view_definition? I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code
select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'V_SC_ALL_CUSTOMER_DOCUMENTS'

is that VIEW_DEFINITION cuts view DDL short. Then I've learned to use sys.sql_modules to get full definition.
I ran these
sp_help 'INFORMATION_SCHEMA.VIEWS'
sp_help 'sys.sql_modules'

and found out that INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION nvarchar size = 8000 and sys.sql_modules.DEFINITION nvarchar size = -1. I believe nvarchar(max).
What is the point then to have VIEW_DEFINITION if you can't rely on it? And are there other types of objects that I should retrieve in sql_modules rather than from their corresponding tables, like Procedures, triggers, etc?

As Erik mentioned in the article he linked, INFORMATION_SCHEMA is a deprecated schema from the classic days of SQL Server 2000. You should read the article he linked as it goes through the reasons it's deprecated, and cites Microsoft's docs that support those reasons.
You can instead use the following query that leverages the sys.objects view to get the definition of a View:
SELECT m.[definition] AS ObjectDefinition
FROM sys.objects o
INNER JOIN sys.sql_modules m
    ON m.[object_id] = o.[object_id]
WHERE o.[object_id] = OBJECT_ID('dbo.YourViewName')
    AND o.[type] = 'V';

Or the function OBJECT_DEFINITION() to also get the definition as follows:
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourView')) AS ObjectDefinition;

One caveat to doing it either way, is it'll contain the last script used to modify the object. So if the View was altered since it's creation, it'll start with the ALTER VIEW statement otherwise it'll have the CREATE VIEW statement.


Oracle SCN_TO_TIMESTAMP (ORA_ROWSCN) fails Trying to run the following SQL which is basically an exact copy of an example I have seen on multiple sites, showing how to do this (I just change the tablename)

SELECT
SCN_TO_TIMESTAMP(ORA_ROWSCN) AS scn_time, ...
FROM
TABLE_ONE
WHERE
...;

But when I run it, it returns this error:

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
08181. 00000 -  "specified number is not a valid system change number"
*Cause:    supplied scn was beyond the bounds of a valid scn.
*Action:   use a valid scn.

What can I change to get this working? I am just looking to find the date/time a row was last updated.

Cheers in advance

K

SCN_TO_TIMESTAMP

  The association between an SCN and a timestamp when the SCN is
  generated is remembered by the database for a limited period of time.
  This period is the maximum of the auto-tuned undo retention period, if
  the database runs in the Automatic Undo Management mode, and the
  retention times of all flashback archives in the database, but no less
  than 120 hours. The time for the association to become obsolete
  elapses only when the database is open. An error is returned if the
  SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

Meaning the databases does not keep track of SCN-timestamp map forever and it is normal for SCN_TO_TIMESTAMP to fail for "too old" values.
With the SCN returned from ORA_ROWSCN, you can still try to find the period of the change from views such as V$LOG_HISTORY (FIRST_CHANGE#, NEXT_CHANGE#, FIRST_TIME, NEXT_TIME).

When the time of the last update is required, the actual value is typically maintained and stored in an extra column in the table. ORA_ROWSCN is not good enough for that.


Is this diagram possible or correct? just starting to learn to design. I don't know the term in english to describe this type of design, so I'm here for some help.

Can this "specialitzations" have a relation like this one? Thanks!

Yes, it is allowed. Every relation can be related to any other one (or even to itself.)

The naming does not help much but if the created by means the (natural) "ingredients" that an artificial product is composed of, then it makes sense to use a many-to-many relationship.


Sql server 2016 Change only server and without all databases How can i change server collation without changing whole databases, because i have
many databases which aren't belong to me, so i want change server collation and my database only.

SQL Server version:  2016
SQL Server Collation:  Arabic_CI_AI

in the server i have many databases, some of these databases i work on them and others are not belong to me and should not be touched.
I want to change the collation: Arabic_100_CI_AI

When i use this script:

sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"Arabic_CI_AI"

it change the server collation along with all databases in the server, but i don't want this i need to do change only the collation of the server only.

Is that possible?

You'll need to run setup with the REBUILDDATABASE option to change the server collation without changing the collation of existing user databases. This will create fresh system databases so you'll need to re-create server-level objects, re-attach user databases, and remap logins afterwards.

Note the tempdb collation will also be changed and can result in collation conflict errors after the operation depending on the existing user database collations and how queries are coded (e.g. creates temp tables without an explict DATABASE_DEFAULT column collation specified).

A system collation change is not a trivial matter and needs to be thoroughly tested.


Mongos - Query error After we configured six replica set servers and three config servers with mongos. we are not able to see records from mongos server. Kindly have a look my below errors and please suggest to us.

mongos>use test
switched to db test
mongos> show collections;
2014-12-03T15:11:09.312+0300 error: {
  "$err" : "could not initialize sharding on connection firstset/localhost1:10001,localhost2:10002,localhost3:10003 :: caused by :: mongos specified a different config database string : stored : X.X.X.1:20001 vs given : X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003",
  "code" : 15907,
  "shard" : "firstset"
} at src/mongo/shell/query.js:131 mongos>

You cannot mix and match resolvable and non-resolvable host addresses in your cluster.

When you started your config servers and another mongos, you had told them their hostnames are X.X.X.X (the IP you masked in your error message).   You are now trying to start a mongos using localhost for the host name/IP - that is not allowed.

You must use identical configdb string to every mongos every time in a sharded cluster.


Get Quarterly Data For a Year I need a way to query a SQL Table and pull back data on a quarter by quarter basis.  I know that SQL Server has a DatePart() function which has a parameter of q = Quarter and I came up with this syntax, but for a table that holds roughly 50,000 rows this syntax is extremely slow in it.  Is this the best way to achieve this result or is there more optimization that can occur?

Declare @startdate date = '20170101', @enddate date = '20171231'
Select
Employeename
,[Total Amount Paid] = SUM(ISNULL(Totalcheckamt,0))
FROM dbo.PaymentHistory
WHERE DatePart(q,[DatePaid]) = 1
AND [DatePaid] BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                   AND CAST(DateAdd(yy, -1, @enddate) As Date)
GROUP BY Employeename
Order By Employeename ASC

EDIT
My desired returned results is like so

Employee Name -- Q1 ---   Q2 ---   Q3 ---    Q4
James           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX
Roger           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX

EDIT 2
I tried this syntax using a case statement but it gives me an error of the error below.  What should I alter in order to be able to succesfully execute this statement?

  Msg 130, Level 15, State 1, Line 18
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT
Employeename
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42017
FROM dbo.PaymentHistory
    GROUP BY Employeename
    Order By Employeename ASC

Using your CASE expression example, here's how it could be written:

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    )
insert into @Paymenthistory values ('James','2016-01-01',100.00)
insert into @Paymenthistory values ('James','2016-02-01',100.00)
insert into @Paymenthistory values ('James','2016-03-01',100.00)
insert into @Paymenthistory values ('James','2016-04-01',100.00)
insert into @Paymenthistory values ('James','2016-05-01',100.00)
insert into @Paymenthistory values ('James','2016-06-01',100.00)
insert into @Paymenthistory values ('James','2016-07-01',100.00)

insert into @Paymenthistory values ('Roger','2016-01-01',100.00)
insert into @Paymenthistory values ('Roger','2016-02-01',100.00)
insert into @Paymenthistory values ('Roger','2016-03-01',100.00)
insert into @Paymenthistory values ('Roger','2016-04-01',100.00)
insert into @Paymenthistory values ('Roger','2016-05-01',100.00)
insert into @Paymenthistory values ('Roger','2016-06-01',100.00)
insert into @Paymenthistory values ('Roger','2016-07-01',100.00)
SELECT Employeename
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42017
FROM @PaymentHistory
GROUP BY Employeename
ORDER BY Employeename ASC

After you accepted my original answer, I noticed where other posters were advocating the use of PIVOT to achieve the same result as multiple CASE expressions.  For completeness of my answer, I decided to try my hand at using PIVOT to provide additional options for you (and learn something myself in the process).  I've found the information from Questions About Pivoting Data in SQL Server You Were Too Shy to Ask to be quite useful when trying to understand how to use PIVOT.  You'll learn a lot about PIVOT by working through the examples (static and dynamic PIVOT) in the link - I know I did.

To summarize some of the key points of PIVOT (from the link):

  You use the PIVOT operator within your query’s FROM clause to rotate
  and aggregate the values in a dataset. The data is pivoted based on
  one of the columns in the dataset. Each unique value in that column
  becomes its own column, which contains aggregated pivoted data.

  To better understand how this all works, let’s start with the basic
  syntax for a query that uses the PIVOT operator:

SELECT column_list
FROM table_expression
  PIVOT
  (
    aggregate_function(aggregate_column)
    FOR pivot_column
    IN( pivot_column_values )
  ) [AS] pivot_table_alias
[ORDER BY column_list];

  For the SELECT clause, you can specify an asterisk (*) or the
  individual columns, and for the FROM clause, you can specify a table
  or table expression. If you use a table expression, then you must also
  define a table alias. You can also include an ORDER BY clause, but
  that’s optional. You’ll get to see these clauses in action as we
  progress through the questions. For now, let’s focus on the PIVOT
  clause. You need to understand how this clause works to make sure your
  pivots work the way you want.

  After you specify the PIVOT keyword, you pass in what are essentially
  three arguments, enclosed in parentheses. The first is the aggregate
  function and the name of the column to be aggregated. You can use any
  aggregate function except the COUNT function, when used with an
  asterisk, as in COUNT(*).

  Next, you define the FOR subclause, which specifies the column on
  which the pivot will be based. It is this column whose distinct values
  are turned into their own columns. The FOR subclause also includes the
  IN operator, which is where you specify the pivot column values that
  will be turned into columns. The values you specify here must exist in
  the pivot column or they will be ignored.

I'm by no means a PIVOT expert and other posters may have better solutions (hey, I'm willing to learn a better way), but here's my take on it.

I'm including two examples of PIVOT - one static and one dynamic.
(Hopefully, by using my examples and the information in the link provided, you'll understand what I'm doing)

The first example is a static PIVOT and is similar to the CASE expression solution I originally provided (except it uses PIVOT).

Notice that my sample data now includes years 'outside' of the CASE expression.  That information won't show up in the static version, but you'll see how we can solve that in the dynamic version later.

Static PIVOT

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into @Paymenthistory values ('James','2016-01-01',100.00);
insert into @Paymenthistory values ('James','2016-02-01',100.00);
insert into @Paymenthistory values ('James','2016-03-01',100.00);
insert into @Paymenthistory values ('James','2016-04-01',100.00);
insert into @Paymenthistory values ('James','2016-05-01',100.00);
insert into @Paymenthistory values ('James','2016-06-01',100.00);
insert into @Paymenthistory values ('James','2016-07-01',100.00);
insert into @Paymenthistory values ('James','2017-01-01',100.00);
insert into @Paymenthistory values ('James','2018-10-01',900.00);

insert into @Paymenthistory values ('Roger','2016-01-01',100.00);
insert into @Paymenthistory values ('Roger','2016-02-01',100.00);
insert into @Paymenthistory values ('Roger','2016-03-01',100.00);
insert into @Paymenthistory values ('Roger','2016-04-01',100.00);
insert into @Paymenthistory values ('Roger','2016-05-01',100.00);
insert into @Paymenthistory values ('Roger','2016-06-01',100.00);
insert into @Paymenthistory values ('Roger','2016-07-01',100.00);
insert into @Paymenthistory values ('Roger','2020-10-01',900.00);
;
WITH cte_Paymenthistory
AS (
    SELECT CASE
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 1
                THEN 'Q12016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q22016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42016'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q12017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42017'
            END AS ColumnLabel
        ,EmployeeName
        ,TotalCheckAmt
    FROM @PaymentHistory
    )
SELECT EmployeeName
    ,coalesce([Q12016], 0) AS [Q12016]
    ,coalesce([Q12017], 0) AS [Q12076]
    ,coalesce([Q22016], 0) AS [Q22016]
    ,coalesce([Q22017], 0) AS [Q12017]
    ,coalesce([Q32016], 0) AS [Q32016]
    ,coalesce([Q32017], 0) AS [Q32017]
    ,coalesce([Q42016], 0) AS [Q42016]
    ,coalesce([Q42017], 0) AS [Q42017]
FROM cte_Paymenthistory
PIVOT(SUM(TotalCheckAmt) FOR ColumnLabel IN (
            [Q12016]
            ,[Q12017]
            ,[Q22016]
            ,[Q22017]
            ,[Q32016]
            ,[Q32017]
            ,[Q42016]
            ,[Q42017]
            )) AS pvt
ORDER BY employeename;

Did you notice the data outside the CASE expressions didn't show up?

Let's try to solve that by using a dynamic PIVOT.

Dynamic PIVOT

  How do I pivot data if I don’t know the values in the pivoted column?

(Due to using dynamic SQL, we have to switch to using a temporary table instead of a table variable)

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT
    'Q' +
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) +
    CONVERT(VARCHAR(4), year([DatePaid]))
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;

Your question prompted me to research and learn more about PIVOT - thanks.


Concurrent usage of rdbms, write 4K rows without lockdown I am working on a django project using Postgresql. The use case is as follows:

A user needs to upload an excel (containing 2.5-4K rows) 2-3 times daily. There will be 100s of users (somewhere between 1100-1200).

Now, the issue is the app is taking 1.2-1.8 seconds to write the data in the db. During this time, anyone else viewing the webapp is seeing a "server busy" error.

How can I get around this issue, so that:
1. Write speed can improve
2. Other users can use the webapp and maybe in worst case, we can also have concurrent uploading of excel possible?

I'm not sure you understand.

PostgreSQL has max_connections. If that number is hit people won't be able to connect to the database. If your server is waiting you may get a "server busy" message or the like. As @a_horse_with_no_name suggests connection pooling may solve this problem.
You can speed up writes by batching them. When you convert from your "excel format" to SQL are you doing one insert, or one insert per row? Consider inserting into an unlogged table if you're inserting one row at a time, and then running a single INSERT INTO SELECT to UPSERT into the master.
You should also check out the non-durability settings for faster writes


Do I have to filter the percona.checksums table in replication for use with pt-table-checksum? I've set up MySQL replication, one master and one slave. Now I just starting to check the data for integrity on both sides by using Percona's pt-table-checksum. Wonderful tool to detect any changes or drift on the slave.

From what I understand from its documentation, it runs on the master, writes checksums to the percona.checksums table and runs similarly on the slaves connected (automatically detected) using a regular remote MySQL connection. However, the percona.checksums table is actually also replicated! If data differs on the slave, the checksums from the master may be overwritten on the slave and not being detected.

Now, I am worried this might be causing my observed false negatives in detecting changes.

Does pt-table-checksum have safeguards put into place for preventing replication on the percona.checksums table?

If so, how does this work? (as far as I can see, one cannot change replication filters like this) This may help me to pinpoint some other reason for my observations (see below).
If not, then how can I be sure the contents of that table reflect checksums of that specific server? Should I put a replication filter in place? This is actually discouraged by Percona's toolkit documentation.

Actual observations

I have seen one occurrence in my test setup in which somehow running pt-table-checksum did not detect the change deliberately made on the slave. Inspecting the checksum table contents showed the master's checksums. Running the tool on the slave itself then showed the checksums should have been different! This makes me believe there's a race condition going on and the reason why this is hard to reproduce.

Another hypothesis

Suppose the slave has drifted and the network connection is down for a short while the moment running pt-table-checksum. It will show it is unable to connect to the slave in the stderr output, but not fail. Short time later, networking is restored, and replication resumes, copying over the master's contents of the table percona.checksums. Then running pt-table-checksum --replicate-check-only, will provide false negatives. Am I right here? If so, how can I prevent this in this case?

This is a regular MySQL server/client using Percona's toolkit with it.

Versions:

pt-table-checksum 2.1.4
MySQL client: 5.5.24
MySQL server: 5.5.24-0ubuntu0.12.04.1-log (Ubuntu 12.04)

You're incorrect. It only runs the checksums on the master, not on the slaves.

Example query run:

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db', 'table', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db`.`table` /*checksum table*/;

This works because it requires statement based replication, so the query is run fresh on every slave against their data, not the data the master found. This is documented on pt-table-checksums docs under LIMITATIONS

pt-table-checksum requires statement-based replication, and it sets
binlog_format=STATEMENT on the master, but due to a MySQL limitation
replicas do not honor this change. Therefore, checksums will not replicate
past any replicas using row-based replication that are masters for further replicas.

If you modify your data after the checksum query has replicated, of course it won't show that the data was changed, as it was correct when the checksum was calculated.

If you re-run the checksum after changing the data on the slave, then it should pick it up, assuming you're not using a n>2 tier replication setup.

re: Another hypothesis, it doesn't matter, the connection to the slave only matters for 'live' detection of mismatches, not for the checksums to actually calculate correctly. You shouldn't have false negatives, even with network interruptions.

Makes sense?


PostgreSQL - Date added row metadata Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with information and I'm trying to devise a means of efficiently working with a row subset defined as newly added rows, but I need that date meta information to make said differentiation possible. Is this at all possible?

A dirty solution, that sadly only works on some tables, I've thought up includes keeping track of the number of rows I've collected (this works because the primary key is generated by the database) and then making queries with an offset. If the date added meta information exists then I can devise a generic solution that will suite all of my tables.

Please note that I can't add an explicit data added column because I already have a ton of rows collected over several years.

The only information you have about 'when' a row entered the table is the ID of the inserting transaction.  The sad news is that this is in no way connected to any real timestamp.

So you either add the new column (where you can't figure out when the existing rows were inserted) - or probably you can create triggers on all tables which populate a transaction ID-timestamp table.

The number of rows approach can work only if you keep track of not only the inserts but deletes (and truncates) as well.

Alternatively, if this task is not needed too often, you can use PostgreSQL's point-in-time recovery mechanism to set up a database which mirrors the state of a given time in the past, then compare the data in the two versions.


Access denied when trying to delete files with xp_cmdshell On a SQL Server 2012 SP2 instance the query

xp_cmdshell
'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'

fails with the output:

  Access is denied.

Both of these return the windows account running the SQL Engine and SQL Agent:

EXEC master..xp_cmdshell 'set username'

  USERNAME=AngeliaSQL

EXEC xp_cmdshell 'whoami'

  ntdomain\angeliasql

The windows account has Full Control on the \morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ and all files in the folder.

Is there another way to check permissions?

You need to have a connection as well as permissions. SQL Server won't automatically connect to a network resource. You can do this either inside or outside of SQL Server but the connection has to be made on the machine. If there is a permission issue, then the 'net use' in the SQL query will return more info than just 'Access Denied'.

exec xp_cmdshell 'net use \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP mypassword /USER:ntdomain\angeliasql'

exec xp_cmdshell 'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'


How to use a CASE expression in the WHERE clause? I am using a CASE expression in my WHERE clause like this:

SELECT *
FROM    ASPECT.WR_AM_ADT_SUMM
INNER JOIN ASPECT.WR_AM_DLR_DTL
  ON    AS_CNTRY_CD = DD_CNTRY_CD AND
        AS_DLR_CD = DD_DLR_CD AND
        AS_YEAR = DD_YEAR
RIGHT JOIN ASPECT.DEALER_MASTER
  ON    COUNTRY_CD = AS_CNTRY_CD AND
        DEALER_CDE_VEGA = AS_DLR_CD
WHERE
        COUNTRY_CD = '81930' AND
        LANG_CD = '02' AND
        (CASE PARM_ADTR_ID
           WHEN 'ALL' THEN (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%')
           ELSE AS_ADTR_ID_P LIKE LOC_ADTR_ID
        END) AND
        DEALER_CDE_VEGA LIKE '8%'
        ;

In the above query when I pass 'ALL' in PARM_ADTR_ID input parameter then first condition should work, otherwise the second condition should work.

As of now it's giving the syntax error, could anyone please tell me how to do this?

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code:

WHERE
    COUNTRY_CD = '81930' AND
    LANG_CD = '02' AND (
      ( PARM_ADTR_ID = 'ALL'
        AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') )
       OR
      ( (PARM_ADTR_ID != 'ALL' OR PARM_ADTR_ID IS NULL)
        AND AS_ADTR_ID_P LIKE LOC_ADTR_ID )
    ) AND
    DEALER_CDE_VEGA LIKE '8%'

However, if you remove the redundant conditions it can be simplified like so (HT to ypercubeᵀᴹ, who wakes earlier:)

WHERE
    COUNTRY_CD = '81930' AND
    LANG_CD = '02' AND (
      PARM_ADTR_ID = 'ALL' OR AS_ADTR_ID_P LIKE LOC_ADTR_ID
    ) AND
    DEALER_CDE_VEGA LIKE '8%'


How to avoid invoking functions twice when using GROUP BY and HAVING? I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents.

The following query works and returns the correct results:

SELECT node,parents FROM
(
  SELECT nr.child AS node, COUNT(nr.parent) AS parents
  FROM node_relation nr
  GROUP BY nr.child
) AS count WHERE parents > 1;

The result set:

 node   | parents
--------+---------
 n21174 |       2
 n8635  |       2
(2 rows)

The table definition is:

            Table "public.node_relation"
   Column    |         Type          |   Modifiers
-------------+-----------------------+---------------
 child       | character varying(50) | not null
 parent      | character varying(50) | not null
Indexes:
    "node_relation_pkey" PRIMARY KEY, btree (child, parent)

I re-wrote the query to not use a sub-select:

SELECT child AS node, COUNT(parent) AS parents
FROM node_relation
GROUP BY child
HAVING COUNT(parent) > 1;

The new query works, but I wonder about the COUNT function being invoked multiple times.

Update: Here is the query plan:

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..1658.81 rows=19970 width=16)
   Filter: (count(parent) > 1)
   ->  Index Only Scan using node_relation_pkey on node_relation  (cost=0.00..1259.40 rows=19971 width=16)

I would prefer to use the parents alias but the following does not work:

SELECT child AS node, COUNT(parent) AS parents
FROM node_relation
GROUP BY child
HAVING parents > 1;

ERROR:  column "parents" does not exist
LINE 1: ...parents FROM node_relation GROUP BY child HAVING parents > ...
                                                            ^

Will PostgreSQL optimize out the multiple invocations of COUNT?

If not, is there an alternative form of this query that would be more efficient?

Your second query (the one where you implement it with the HAVING clause) is probably faster. In your first query (with the subselect), postgres has to calculate the count values for the entire table. In your second query, it can start ignoring rows to count once it reaches a count value above 1 (although I don't 100% know if postgres is smart enough to do that - I'm fairly certain it is, though).

Since COUNT() is an aggregate function, it's going to be run the number of times it'll be run regardless of the number of rows returned. If you had a function that was NOT an aggregate function, then running your group and where/having clause in a subselect would likely be faster.

Example of what I'm referring to:

SELECT
  some_non_agg_function(a.id, a.child)
FROM join_tab1 a
GROUP BY a.id, a.child
HAVING COUNT(a.id) > 1;
-- probably not as fast as
WITH rows_to_process AS (
  SELECT DISTINCT
    id, child
  FROM join_tab1 a
  GROUP BY a.id, a.child
  HAVING COUNT(a.id) > 1
) SELECT
  some_non_agg_function(id, child)
FROM rows_to_process;

To answer your question specifically - yes, postgres will keep track of what aggregate values it has calculated and re-use them (as opposed to re-calculating them) within the HAVING clause. I believe it will also re-use them in the SELECT clause (if for some weird reason you run the exact same aggregate more than once in the SELECT)

To quote Postgres's excellent documentation (bold mine)

  It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)

This doesn't specifically say that it re-uses the calculated values .. but it implies it with saying that the HAVING clause is used after aggregates are calculated.


MobiLink: How to propagade schema change for a synch profile to remote databases Once an application is online with a synchronisation profile it might happen, that the profile needs to be changed. E.g. a new index is introduced, columns are added etc.

Having the synchronisation profile created with Sybase Central, what's the designated way to propagate these changes to remote databases? The only option here seems to be to complete new create a profile or update current one including complete regeneration of remote database.

Mobilink can also pass schemachanges to it's replicated databases.
For this, there exists the start synchronization schema change statement

This looks like this:

START SYNCHRONIZATION SCHEMA CHANGE
  FOR TABLES DBA.Sales, DBA.Products
  SET SCRIPT VERSION = 'version_2';
ALTER TABLE DBA.Sales ADD SUBTOTAL NUMERIC (10,2);
ALTER TABLE DBA.Products ALTER QUANTITY BIGINT;
STOP SYNCHRONIZATION SCHEMA CHANGE;


Proper usage of Group by in Inner Join EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1

I'm currently making a report from a database records but I don't know how my query should look like, first of all I have 2 tables. Application Forms, and a table for Login Hours of each user

forms
->id
->agent_id
->SomeInfo
->created_at

loginhours
->id
->user_id
->loginhours (decimal)
->created_at

And I have report with the following columns

UserID, TotalLoginHours, TotalApplication, Application Per Hour (aph), Revenue Per Hour (rph)

So right now I have this query

SELECT a.agent_id, SUM(b.loginhours) as TotalLoginHours, COUNT(a.id) as TotalApplication, SUM(b.loginhours) / COUNT(a.id) as ApplicationPerHour,   (SUM(b.loginhours) / COUNT(a.id)) * 1.75 as RPH
FROM forms a
INNER JOIN loginhours b ON a.agent_id = b.user_id WHERE a.created_at = '2015-07-17'
GROUP BY a.agent_id

Note that user_id and agent_id is the same.

I want to get the result based on the date selected, example 2015-07-17 I got results but my problem is the loginhours is being SUM based on the number of application for each user. So for example the user1 has 2 records on forms table and his loginhours from 2015-07-17 is 2 then in my result the loginhours becomes 4 which is wrong, I think it is on my GROUP BY statement. Can you help me how to properly query this?
Thanks

To avoid the multiplication effect you are experiencing, you can do the aggregation first, and then join:

select a.agent_id, a.TotalApplication, b.TotalLoginHours
     , b.TotalLoginHours / a.TotalApplication as ApplicationPerHour
     , 1.75 * b.TotalLoginHours / a.TotalApplication as RPH
from (
    SELECT a.agent_id as agent_id
         , COUNT(a.id) as TotalApplication
    FROM forms a
    WHERE a.created_at = '2015-07-17'
    GROUP BY a.agent_id
 ) as a
 join (
     select b.user_id as agent_id
          , SUM(b.loginhours) as TotalLoginHours
     from loginhours b
     group by b.user_id
 ) as b
     ON a.agent_id = b.agent_id
 ;

Not sure I got all the details right, but it should give you a hint of a technique that can be used. I modified your fiddle in:
http://sqlfiddle.com/#!15/8d88b/9


Is automatic disabling of DML triggers possible during SSDT publish? During ssdt deployment I am concerned about having DML triggers in place on tables that may slow down performance of table recreation a lot.

We are facing the issue that the column order in the table scripts is different than in the target servers. Because Microsoft does not provide an option to ignore this, the tables are recreated during publishing to ensure the same column order.

This process is very very slow. And since some tables do have DML triggers enabled, I am concerned this makes it even more slow than required. I looked for options to disable / ignore those triggers during deployment but could not find any. How are we expected to realize that?

Based on my first-hand experience, SSDT refreshes table definitions before it refreshes triggers.

I had to manually disable all triggers before publishing for it to work (sample reasoning below). However, it is foolish to assume all table triggers were enabled before, so turning them all off then back on is prone to causing issues.

My solution was to create a script to generate the necessary SQL to turn off all currently-enabled triggers, and then to enable them again (make sure to set current database connection to your target database).

Warning: Single user mode will close all open connections. Make sure to re-enable multi-user mode after you're done. You can skip changing user mode altogether if nobody is holding any locks on your system.

SET NOCOUNT ON
DECLARE @DatabaseName NVARCHAR(255) = 'YOURDATABASE'
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
DECLARE @TableTriggersOnly BIT = 1
CREATE TABLE #Trigger
(
     SchemaName NVARCHAR(255)
    ,TableName NVARCHAR(255)
    ,TriggerName NVARCHAR(255)
    ,WasDisabled BIT
)
INSERT INTO #Trigger
(
     SchemaName
    ,TableName
    ,TriggerName
    ,WasDisabled
)
SELECT
     SchemaName = S.name
    ,TableName = T.name
    ,TriggerName = Tr.name
    ,WasDisabled = Tr.is_disabled
FROM
    sys.triggers AS Tr
    INNER JOIN sys.tables AS T ON
        T.object_id = Tr.parent_id
    INNER JOIN sys.schemas AS S ON
        S.schema_id = T.schema_id
WHERE
    Tr.is_disabled = 0
    AND (@TableTriggersOnly = 0 OR Tr.parent_class = 1)
DECLARE
     @SchemaName NVARCHAR(255)
    ,@TableName NVARCHAR(255)
    ,@TriggerName NVARCHAR(255)
    ,@WasDisabled BIT
DECLARE TriggerCursor CURSOR STATIC FOR
    SELECT
         SchemaName
        ,TableName
        ,TriggerName
        ,WasDisabled
    FROM #Trigger
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'DISABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
/*
 * YOUR STATEMENTS HERE
 */
PRINT '/*******************'
PRINT 'YOUR STATEMENTS HERE'
PRINT '*******************/'
/*
 * YOUR STATEMENTS HERE
 */
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'ENABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
DEALLOCATE TriggerCursor
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
DROP TABLE #Trigger

A particular table of mine has a trigger that was poorly written, encountering errors when handling multiple affected rows at a time, but the correction to the trigger to handle the case was never getting applied. So a column alteration in the table meant that SSDT was shifting data to a temp table then back, causing a multi-row invocation of the table trigger.


Cross-product between table column and input values I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values.

Something along the lines of:

WITH {1,2} as Input
Select *
From mTable.column, Input

With mTable.column containing the values 3 and 4, it should return:

1,3
1,4
2,3
2,4

Is there any way to achieve this?

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres.

And you don't need a CTE for this. You can use it, but it has no performance benefit.

Provide a set with VALUES:

  VALUES computes a row value or set of row values specified by value
  expressions. It is most commonly used to generate a "constant table"
  within a larger command, but it can be used on its own.

SELECT t.i, m.col1
FROM   mTable m
CROSS  JOIN (VALUES (1), (2)) t(i);

Provide an array and unnest()

2a. with an array constructor:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest (ARRAY[1,2]) i;

2b. With an array literal:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest ('{1,2}'::int[]) i;

Add ORDER BY i, m.col1 if you need the sort order in your result.

About row and array syntax:

Array of strings when updating a field


Seg faults in Percona Server 5.6.19 Trying to figure out what went wrong here. Any advice would be appreciated. This server is running a new installation of Percona Server 5.6.19 on CentOS 6.5

16:05:46 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=1073741824
read_buffer_size=1048576
max_used_connections=188
max_threads=2002
thread_count=177
connection_count=176
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 7228203 K  bytes  of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x17e85e60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f7c813f8d40 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bb80c]
/usr/sbin/mysqld(handle_fatal_signal+0x479)[0x6504b9]
/lib64/libpthread.so.0[0x3af3a0f710]
/usr/sbin/mysqld(_ZN10ha_archive10unpack_rowEP11azio_streamPh+0xf5)[0xadb385]
/usr/sbin/mysqld(_ZN10ha_archive8rnd_nextEPh+0x7a)[0xadb8ba]
/usr/sbin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x5d)[0x597c5d]
/usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x20)[0x7f5780]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x101)[0x6adbf1]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x2b8)[0x6ace68]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x275)[0x6f56b5]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x165)[0x6f5f15]
/usr/sbin/mysqld[0x559ee0]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1cec)[0x6cfe3c]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5c8)[0x6d5078]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfa7)[0x6d6807]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x162)[0x6a4882]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6a4970]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0xb0dc13]
/lib64/libpthread.so.0[0x3af3a079d1]
/lib64/libc.so.6(clone+0x6d)[0x3af36e8b5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f7c380009a0): is an invalid pointer
Connection ID (thread ID): 71
Status: NOT_KILLED

It seems an invalid pointer was caught when doing a full table scan on the Archive engine. I recommend you to post the trace on Percona Server bug tracker. Developers are very friendly even if you are not a client. They will be able to tell you if it is a Percona specific problem or an upstream problem (if they have changed anything on the Archive stored engine).

My own experience with the Archive engine is that, while it is a nice theoretical engine for some workloads, it is currently mostly unmaintained and not ready for production, having astounding bugs. Percona does not recommend it, and if you are concerned about stability, you are probably better served by InnoDB compressed or TokuDB (no matter if you are running PS or stock MySQL).


How to get last IP address of DB update request ? I have a MySql Database hosted in the cloud.

From an embedded device connected to the Internet network via an ISP  I write some values (coming from a sensor) in a table of this Database.

Is it possible to know the last IP address of the embedded device  by querying the DB ?

No.

The file access.log contains connections, but that is not accessible via SQL.  (And your hosting service may not let you access that file.)


SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber? Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table?

Table exists on Database A (SQL Server 2012 - app DB) to store a varchar(max) field that is populated by a web app, and never referenced again. Table is replicated to Database B (SQL Server 2012 - data mining DB) where it will be used for reporting/data mining.

Would like to partition table on both DBs by quarter and only have data from current quarter on Database A (Database B will have historical quarters).

You can override the way DELETE commands are replicated to the subscriber by using the @del_cmd parameter for sp_addarticle. You can specify a custom stored procedure that takes care of the archive logic or ignore DELETE commands altogether by setting it to NONE.

However, the rows kept at the subscriber won't be replicated any more if for some reason you re-initialize the subscription, which I suppose is not exactly desirable.

I don't think that replication is the right way to skin this cat. Probably a custom SSIS package would do the trick more cleanly.


Informix onunload/onload to take a snapshot and then restore I have an Informix database. I want to take a snapshot, make some changes, then restore the database from that snapshot. (If Informix supported nested transactions, I would be using transactions.)

The database is of moderate size (it can run on a consumer laptop) and there is no replication. It is critical that this backup/restore procedure is as fast as possible (I would like to do a backup-modify-restore cycle several times per second if possible). Only a small part of the database would be modified between the backup/restore points.

Reading around a bit, it sounds like onupload/onload commands are the way to go. I can't quite figure out how to use them.

Can you please show me the way to take a snapshot of a running database, and restore that database after some changes? (All on one machine.)

Or if there is a better way to achieve this effect? (Perhaps there is a way to fork the process and all its files in a copy-on-write manner and have the child process serve a clone of the database on a different port?) If so please let me know.

First of all, Informix supports transactions if you create a logged database (WITH LOG or WITH BUFFERED LOG).  Further, it supports transactions (BEGIN) and transaction nesting with SAVEPOINT and COMMIT and ROLLBACK TO SAVEPOINT.

Using onunload and onunload might be workable, but you need to be confident you aren't using blobs (smart blobs), or UDTs (user-defined types).  I'd have to research the details of how to use them for your proposed task; it probably is fiddly.  I'm not sure that I've seriously used either command this millennium, and I didn't often use them in the last millennium either.

If you have several Informix server instances, you could consider using ER (Enterprise Replication) to make a copy of the relevant sections of the database in a separate server, and then do your experimentation on that other server.

If you have shared disks, you might be able to use an SDS (shared disk secondary) node with care.

If transactions are sufficient, I recommend using them rather than the alternatives.  Just ensure you have enough logical log space to be able to roll the transactions back (look up 'long transaction', LTXHWM and LTXEHWM).


Make MySQL print a timestamp at each time it starts I am investigating an issue in an environment where MySQL is often restarted, by various actors (backups admins etc).

To make analysis saner, I need to know exactly when MySQL is (re)started.

Here is the current slow.log:

/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

As you can see, there is no timestamp, I can only know that MySQL has been started 3 times that day, but I don't know when.

Question: How to make MySQL print a timestamp at each time it starts?

For unfortunate real-world reasons I can't get every single actor to reliably keep  track of when they restart MySQL.

You do not need to consult the log. mysqld can tell you.

For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
WHERE variable_name='Uptime';

For everyone running MySQL 5.7

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';

EXAMPLE (MySQL 5.7.12 on my Windows Laptop)

mysql> SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
    -> FROM performance_schema.global_status
    -> WHERE variable_name='Uptime';
+----------------------------+
| MySQL_Started              |
+----------------------------+
| 2018-03-19 09:10:43.000000 |
+----------------------------+
1 row in set (0.00 sec)

As to why the time is not being printed, here is what I suspect

mysqld_safe starts mysqld and stays in an indefinite loop
mysqld terminates over some minor condition

Out of memory
Too many connections
Accessing a corrupted table

mysqld is started again in the indefinite loop
I mentioned this Mar 05, 2013 : mysqld_safe version different than mysqld?

I do not believe mysqld_safe will print timestamps on mysqld restarts. If you really want mysqld_safe to print a timestamp on mysqld restarts, you can edit mysqld_safe (it is just a bash script) and place a line in the indefinite loop that echoes the date


Non unique one to many relationship I am a noob to database design so bear with me. I have two tables in Microsoft Access:

Table 1:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |    1   |
| 2  |   A    |    2   |
| 3  |   B    |    5   |
| 4  |   C    |    8   |
| 5  |   D    |   20   |
| 6  |   E    |   32   |
| 7  |   F    |   22   |
| 8  |   F    |   7    |

Table 2:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |  one   |
| 2  |   B    |  two   |
| 3  |   B    | eight  |
| 4  |   D    | seven  |
| 5  |   D    | twenty |
| 6  |   F    | thirty |

I am trying to establish a one to many relationship between the tables based on Field1. However Field1 is not unique in either table. This is conceptually possible, however, because each record in Field1 does have a corresponding record in Field2. I could create an intermediate table containing just the ID's as such

| ID |   ID1  |  ID2   |
|:---|--------|:-------|
| 1  |   1    |   1    |
| 2  |   2    |   1    |
| 3  |   3    |   2    |
| 4  |   3    |   3    |
| 5  |   5    |   4    |
| 6  |   5    |   5    |
| 7  |   5    |   5    |
| 7  |   7    |   6    |
| 7  |   8    |   6    |

However this would be too tedious since I have thousands of records (above tables are just a simplified example). Is there a way in Access to relate these tables directly without having to worry about the ID's? I know I used to be able to do this in ArcGIS using the "Relate Tables" feature so hopefully theres a way to do it in Access?

What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing).

I see two options depending on what your requirements are:

If the relationship between rows of tables 1 and 2 is only inferred by matching the "Field1" values and you don't need to store any more info on that many-to-many relationship, then you don't need any extra table at all. The 2 columns (Field1) in the respective tables are enough to find the related data. You could use a simple join on the two tables:

SELECT
    a.id AS ID1,
    b.id AS ID2
    -- any additional column needed from any of the 2 tables
FROM table1 AS a
  INNER JOIN table2 AS b
    ON a.Field1 = b.Field1 ;

If the relationship between rows of tables 1 and 2 is not inferred by matching the "Field1" values (but there are extra conditions/requirements) or you need to store extra info about the relationship, you need an intermediate table. To make sure (enforce) that only rows with same "Field1" are actually related in this table, the common column ("Field1") has to be added, too, and participate in the two FOREIGN KEY constraints. (you will also need UNIQUE constraints on (Fieldd1, ID), on each of the 2 tables, for the foreign keys to work):

CREATE TABLE intermediate
  ( Field1 VARCHAR(5),
    ID1    INT NOT NULL,
    ID2    INT NOT NULL,
    -- possible extra columns
    PRIMARY KEY (ID1, ID2),
    FOREIGN KEY (Field1, ID1)
      REFERENCES table1 (Field1, ID),
    FOREIGN KEY (Field1, ID2)
      REFERENCES table2 (Field1, ID)
) ;


How can I ONLY get the query execution time on the command line without waiting till the data is displayed? I am testing and comparing execution times of queries with big result sets using the command line.

It takes too long to display the result set! Is there any way to see only the query execution time without waiting till all of the result set is displayed?

Solution for Getting only Execution time of Query without printing result set :

Query :Select C1 , C2, C3 from Table where C3='1'; -- 40571 rows in set (0.079 sec)

Get Count of query :

Do : Select count(*) from (Select C1 , C2, C3 from Table where C3='1') as X;

Time : 0.035 sec
Redirecting output to /dev/null :

Do : time mysql  -u'user' -p'password' Database < query.sql > /dev/null

Time :

    real    0m0.180s
    user    0m0.112s
    sys     0m0.005s

Using mysqlslap :

Do :

vi query1.sql                   // Addin the query.
mysqlslap   -u'user' -p'password' --query="query1.sql"  --delimiter=";" --concurrency=1 >> output.log

Time :
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Average number of seconds to run all queries: 0.080 seconds
    Minimum number of seconds to run all queries: 0.080 seconds
    Maximum number of seconds to run all queries: 0.080 seconds
    Number of clients running queries: 1
    Average number of queries per client: 1

According to me ideal is 3rd solution using mysqlslap


Storing date as integer (numeric), what are the advantages Question 1

I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example

20120101  -- 01 Jan 2012

Is there any advantage of keeping numeric date system and not using SQL Datetime?

Question 2

Now I am trying to loop through numeric date to find customers between two dates. If the start and enddate encompass two months, I get thousands of records instead of just 60. Example:

create table #temp1(day int,capacity int) /* just a temp table */

declare @start int
declare @end int

set @start=20111201
set @end = 20120131

while (@start <= @end)
Begin
    insert into #temp1  /* I am storing things in #temp table so data looks pretty */
    exec usp_GetDailyCap @date1= @start

    set @start = @start + 1;
end

select * from #temp1

This pulls 8931 records instead of 60. Is there a better way to improve the logic above so I pull only valid dates? I tried IsDate and sub-queries but that did not quite work in an efficient way.

To answer your first question, I would recommend using the DATETIME data type within SQL Server.  Not necessarily for performance reasons, but to leverage the RDBMS-specific functionality.  For instance, you would have to re-invent a lot of logic just to do basic date math (think DATEDIFF(), DATEADD(), DATEPART() and many other functions.  They are obviously tailored to the DATETIME data type and are easy to work with).

As for your second question, you are running into the exact problem that the first question (and my answer) is geared towards.  You are looking at 20111201 and 20120131 as dates, and your brain is telling you that should be a difference of 60 days.  Well, you're looping through based off of the delta...which is:

20120131 - 20111201 = 8930 (with the inclusive loop it'll be 8931)

In other words, your WHILE loop is executing 8931 times.  This is happening because those are integer values and your loop will not jump from 20111231 straight to 20120101.

You integers aren't going to take into account the cap of years and months (i.e. your Question 2 problem).


What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database? In Postgres 11, the CREATE DATABASE command that makes a new catalog has an option ALLOW_CONNECTIONS. The documentation says this option can be used to block all connections to the database:

[ ALLOW_CONNECTIONS [=] allowconn ]
allowconn
If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT/REVOKE CONNECT).

➥ Why would anyone want to create a database that nobody can connect to?
➥ If you cannot connect, how can you ever change this setting to true to allow connections?

It gives you a chance to revoke connect from unwanted users before doing alter database ... allow connections

Also in all cases users with superuser permission are allowed to connect, so you can create an unconnectable database, then as superuser populate it and create constraints ans indices before making it publicly available.


Tuning advisor with Extended events? With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...).

Now, with SQL 2012 and Extended Events, how can I do something similar?

Thanks

Unfortunately you cannot do this with Extended events unless the data from XE can be converted into the format that DTA uses or can be stored in the tables generated by DTA.

Not recommended though but Trace is is still available, so can be used if you want.

You can even use DMV's for finding missing indexes, etc.

Also worth mentioning Aaron's post : Don't just blindly create those "missing" indexes!, since you are using DTA for analysis.


What can I do about this MariaDB 10.0 error? I have a serious error that crashes the mariadb service, and it won't start up again until I delete the database folder in the data directory of the database I was working on at the time. Here is the .err file printout. Anyone know what to do about this?

2015-02-21 13:48:23 7dc InnoDB: FTS Optimize Removing table bolingerfield/tag
2015-02-21 13:49:44 4bc4  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 13924:3, should be 13877:3!
2015-02-21 13:49:44 4bc4  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 13924:1, should be 13877:1!
2015-02-21 13:50:23 798  InnoDB: Assertion failure in thread 1944 in file btr0btr.cc line 713
InnoDB: Failing assertion: mach_read_from_4(seg_header + FSEG_HDR_SPACE) == space
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

You may have moved around manually the tablespaces or improperly importing the tables physically. The error assertion is a detection by MySQL InnoDB that pages have internal values that do not correspond to their physical location, thus forcing the kill of the server to avoid further corruption.

I have very very rarely seen that as a problem due to InnoDB programming (at least on upstream and stable MySQL), and more usually due to a problem in datafile or configuration management by an operator. While it should be possible to go back to a consistent state, without knowing more about what kind of processes you have performed, I would start by recommending performing a bull backup of your system so no data is lost.


Insert Trigger to a Linked Server error I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the error below occurs.

OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure AuditlDealerDebitAuditTrailTEST, Line 22
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" was unable to begin a distributed transaction.

Here's my trigger query:

    create trigger dbo.AuditlDealerDebitAuditTrailTEST
on [dbo].[lDealerDebitAuditTrailTEST]

after UPDATE, INSERT, DELETE
as

declare @TableName varchar(20), @User varchar(20), @PCNo varchar(20), @server nvarchar(30), @Database varchar(30),
        @table varchar(30),@activity varchar(20), @date datetime;

begin
         set  @TableName = 'lDealerDebitAuditTrailTEST'

if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    SET @activity = 'UPDATE';
    set @date = GETDATE();
      INSERT into [TMP-DBA-DB].[Josel_TestDB].[dbo].[audit](ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
end

If exists (Select * from inserted) and not exists(Select * from deleted)
begin
    SET @Activity = 'INSERT';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end
If exists(select * from deleted) and not exists(Select * from inserted)
begin
    SET @activity = 'DELETE';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end

    end
GO

Have you tried setting up the distributor transaction coordinator?

Distributed transactions are transactions that require SQL Server to get data from a source outside of the instance of SQL Server you are using (this most frequently entails the use of Linked Servers et al.

An example of a distributed transaction would be for example:

INSERT MyTable (Col1, Col2, Col3....)
SELECT Col1, Col2, Col3 ... FROM OPENQUERY(MyLinkedServer, 'SELECT Col1, Col2, Col3 ...
FROM RemoteTable
WHERE predicate1 = Condition1')
INNER JOIN Table2 ON Table2.ColX = Col1

to set up the distributed transaction coordinator follow the steps below:

Now you click apply, it will ask for restarting the service and you say yes.

this article is also worth reading:

Do I need DTC for my SQL Server?


What are "folded" and "pivoted" tables? The answers and discussion on questions like this one talk about folded vs pivoted tables.

eg.

  The only difference between a paper presentation and a sound
  electronic presentation is that a folded table structure is more
  convenient in the latter case while a pivoted table structure in the former...

I couldn't find anything on Google about what those terms mean (Google gave results about protein structures).

Could someone please explain what those terms mean with examples?

It's just a different way of representing the data. Also known as wide (pivoted) and long (folded). As an example and to keep it simple, imagine that we have a record with 3 properties: id, name, description in the wide (pivoted) version each property would be its own column like so:

CREATE TABLE `example_table` (
  id int not null,
  name archer(50) not null,
  description archer(100) null
)

In the long (folded) format you would have two columns like so:

CREATE TABLE `example_table` (
  property varchar(50) not null,
  value varchar(100) not null
)

The later gives you greater flexibility if the columns are not known in advance or are likely to change often. However, it is generally more complicated to implement because you have to design a way of knowing what type the value is, and then you have the overhead of serialization/deserialization to the storage type (in this example varchar(100)).

The wide format is better when the properties do not change very often and are known in advance. This will offer better performance but will be less flexible if changes are required.

Generally, where possible choose the wide format when you know the structure of the data you are storing, and choose long if you need maximum flexibility and are willing to accept the added complexity.


Reorganize full text catalog is offline or online? I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to rebuild/reorganize the unique index used by the Full Text Search and then reorganize the Full Text Catalog. I am only concerning here, if this process of reorganize the Full Text Catalog is done online or offline? When the catalog reorganize is ocurring the users will still be able to query the full text data?

I appreciate any help here!

Regards,
Marcos Freccia

First of all, Welcome to stackexchange and thanks for your 1st Post.

To answer your questions :

if this process of reorganize the Full Text Catalog is done online or offline?

The reorganize process is Online, but is slower than Rebuild.

When the catalog reorganize is occurring the users will still be able to query the full text data?

Yes, but there might be blocking. Also you need to take account of below from BOL :

Depending on the amount of indexed data, a master merge may take some time to complete. Master merging a large amount of data can create a long running transaction, delaying truncation of the transaction log during checkpoint. In this case, the transaction log might grow significantly under the full recovery model.

Also, I would suggest you take a look at the fragmentation of the full text Indexes using :

I normally go for REORGANIZING if you have between 30-50 fragments per table

 -- for SQL Server 2008 and up
SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS Fragments
    FROM sys.fulltext_index_fragments
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) >=30

Also, Improve the Performance of Full-Text Queries is a good reference along with this whitepaper.