Assuming your are able to code I'd write a conversion tool which reads from one database and writes into the other. That way you'll be able to do any conversion you like, do additional checks on the data and you could even have the program test the result. Just pick your favorite language. Any tool will still require you to define all the conversions one way or another, you'll still have to test etc. So the overall gain of a special to won't bee that big, and you'll have to learn it first. Assuming it's a one-off it probably simply isn't worth the effort to find a learn a new tool.

If you are planning to do these kind of conversions more often that might change though.


How can I get rid of this encoded, so that I only see the original
value of the column, in the text format

Bytes have 256 possible values, when there are only about 95 visualizable ASCII characters, (the range [32:126]), so the mapping between ASCII text and binary representation cannot be one-to-one.
The format produced by encode(metadata::bytea, 'escape') is just one possible representation, where some bytes are represented by sequences of characters. Others common representations are hexadecimal (outputs each byte as 2 characters) and base64 (outputs each 3 bytes as 4 characters).
If you don't want these and have a specific idea on how your metadata column should be output as text, then you can always provide a user function instead of  encode. Right now your question does not specify what output you want to see, only that it's not the escape format.


Accessing MySQL through dynamic IP address

you can use % as a wildcard to summarize source nets ands limit your attack vector.

so instead of 192.168.68.34, use 192.168.68.% assuming they use a /24 address space for vpn.


How to make dataguard switchover seamlessly

Starting with 11.2, you do not need to create the trigger, you can define a role based service with Grid Infrastructure.

foo:

srvctl add service -db foo -service virtual_foo -role primary
srvctl start service -db foo -service virtual_foo

bar:

srvctl add service -db bar -service virtual_foo -role primary
srvctl start service -db bar -service virtual_foo

If you do not use Grid Infrastructure, well, just use the trigger.

Your virtual service was misconfigured, that is why you received ORA-01033. You should not update the value of service_names manually. After you did that and performed the switchover, virtual_foo service still ran on foo. Your client tried the first address in the lists, found that the listener was alive, and a service existed with the requested name, so it tried to log in. But the standby database was in MOUNT state, so the client could not log in --> ORA-01033.

For the switchover back to foo part: do not use the same TNS entries for clients and Data Guard configuration. Clients do not need to know which site they connect to, but Data Guard Broker and database background processes need to connect exactly to foo or bar, a virtual service does not provide that functionality. Seperate the TNS entries needed for Data Guard configuration and the one used by clients.

Usually I configure DG with the below naming and TNS entries.

If the db_name is foo, then:

primary: db_unique_name = foo_site1
standby: db_unique_name = foo_site2

(or something similar, that identifies the site)

TNS:

foo_site1: TNS entry pointing to the host at site1, with service_name
defined as foo_site1 - used by DG configuration
foo_site2: TNS entry pointing to the host at site2, with service_name defined as foo_site2 - used by DG configuration
foo : TNS entry pointing to both sites, with service_name defined as foo (and that is defined as role based service) - used by clients

Finally, this part in your connection string:

(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))

Unless you have RAC databases with multiple instances per site, and foo_home and bar_home are actually SCAN names, this is unnecessary. These parameters are used to configure Transparent Application Failover (session failover between RAC instances).

By the way, this is far from a really seamless switchover, all that was achieved by this, was that clients can use a single connection string to connect to the database after a role transition.

Client sessions will fail and need to reconnect manually, that could take minutes. Or you need to stop your applications and have planned downtime for this.

Having a really seamless switchover requires far more work (like configuring ONS, FAN). More details at:

Client Failover Best Practices for Highly Available Oracle Databases

Fast Application Notification


What is the cheapest way to mirror/replicate data to multiple servers on SQL Server?

Ok here is what I'd suggest:

Transactional replication
If unfamiliar, it can be set up relatively easily using the UI. This link will help: (http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step)

Mirroring + log shipping would work alright also, as others have suggested.

If you have something like automated ETL queries you need to provide for, you can drop and recreate the snapshot of the mirror database prior to the query running. In the past I have added the drop and recreate snapshot as a step before running an SSIS package or whatever. If you are needing to make the mirror available for ad-hoc querying for a number of users, you can always work out a schedule with your users (like say every 30 minutes) to drop and recreate the snapshot such that it is almost completely up to date. Depends on the specific use case but I'm sure you can see what I'm getting at and tailor it to your needs. Make sure you have instant file initialization enabled for the SQL Server service for the snapshot file to be created instantly.

EDIT - Just realized snapshotting isn't supported in Standard edition, so mirroring won't help you.

Ultimately AlwaysOn is a great choice for your needs but if you have issues with editions then I'd just go with replication.


Prevent record from being inserted in the table in After Trigger

I do not see this as the most desirable implementation. Using a Foreign Key constraint to enforce correctness is (in my opinion) much better than  writing code to handle such issues.

However, regarding your question, Yes, TableA can have both an INSTEAD OF trigger and an AFTER trigger.  (In fact it can have several AFTER triggers.)

https://msdn.microsoft.com/en-us/library/ms189799.aspx outlines some rules, including:

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.
An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. An AFTER trigger will not recursively fire an INSTEAD OF trigger on the same table.


Does order matter for composite primary key in Oracle or any database?

It does, but perhaps not in the way you might expect.

Given col1 and col2, people's initial instinct is to put the more selective column first.

But, that's almost always irrelevant.

First, you need to consider how the index will be used in your SQL statements.  If some SQLs only specify col1, then col1 should probably be first.  If all SQLs will always specify both columns in the where predicate, then it doesn't matter which comes first.

But, what if most of the SQLs specify both col1 and col2, and a few provide only col1 or col2?

Well, in that case, the least selective column should (probably) go first.  There are two reasons for this.  First, if the least selective goes first, then, for those queries where only the second column is specified in the predicate, Oracle will be more likely to do an INDEX SKIP SCAN, if the leading column is not very selective.  Second, by putting the less selective column first, you'll be more likely to be able to take advantage of index compression.

Hope that helps.


Stored procedure runs fine when executed manually but fails in scheduled job

The probable reason I can think is when u execute manually it might be because you are executing with login with privileges or access to execute that proc.
However sql agent job might be using the account assigned to run sql agent services.
Hence you see the error for login failed.
Probably check the error log when failure happens to get more details on that login failure message


The main problems with the example from docs is that it doesn't force the copy to be synced to disk before it reports success, meaning the database might be allowed to delete the original file, but then a poorly timed OS crash means the copy never reaches disk safely and so is not truly available.
Another problem is that if the copy fails (or succeeds, but reports to have failed, because something prevented the success message from being heard) but first created a file with the correct name, then when it is tries again the test command will cause it to keep on failing forever, until someone manually intervenes.  If you remove the test, then if you accidentally configure two servers to archive to the same spot, they can silently overwrite each others files.  So a complete solution will only allow WAL files that belong to the proper basebackup to get saved in the first place, and will allow a file to be saved more than once as long as the new version is identical to, or an extension of, the earlier one.  Of course you generally want to archive to a different machine anyway, so the example archive command doing a cp operation is often just generally wrong in that regard.
pgbarman has its flaws and limitations, but does get around the problems mentioned above.  pgbackrest is another option (one I have not played around with).  If you use log shipping with pgbarman, you would need to set archive_timeout to one hour.  If your server doesn't see much activity, which can mean a lot of  WAL files with little useful data in each hanging around.  You can get around this by using streaming mode instead.
Another option is to use pg_receivewal.  Or to use a streaming replica with a replication slot and with archive_mode set to 'always'.  Then the replica will create the archive for you. You still have the problem though that the archive_command on the streaming replica still needs to force the copy of the file to be synced before reporting success, if you want it to be bulletproof.


Mysql thinks Master & Slave have the same server-id

The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.

Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:

On Slave_A, run the following in mysql:

mysql> STOP SLAVE;
mysql> RESET MASTER;

On Slave_A, run the following in the OS:

$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql

On Slave_A, run the following in mysql:

mysql> START SLAVE;

This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.

BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.


First have a look at the InnoDB Architecture (from Percona CTO Vadim Tkachenko)

Your problem is very simple. You have a broken data dictionary entry. Inside the system tablespace file ibdata1 is a list of tablespace ids.

There is an entry for appointments.business_users. Deleting the .frm and .ibd of appointments.business_users does not remove its tablespace id from the data dictionary. You need to remove that entry from the data dictionary.

The reason you are getting the error still ? Although you can the CREATE TABLE and generated the .frm and .ibd file, the tablespace id in the .ibd file does not match the original tablespace id in the data dictionary.

Here are some old posts on how to repair it in place

From @akuzminsky : InnoDB issue after power outage a few weeks ago - no backup
From @DerekDowney : ERROR Cannot find or open table?
From me : restore table from .frm and .ibd file?

If the database isn't too big, you could do the following:

mysqldump everything EXCEPT appointments.business_users
drop all databases (except mysql schema)
shutdown mysql
delete ibdata1
start mysql (recreated ibdata1)
reload the mysqldump
manually run the CREATE TABLE command for appointments.business_users

Either way, you have some homework to do.


There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you two short examples.

If object is a city with name and zip code like this:

ID          NAME
90201       Bell
90202       Bell Gardens

And object are persons and where they live:

ID  OBJECT_ID   Name
1   90201       Name_Person_1
2   90201       Name_Person_2
3   90202       Name_Person_3

You could store where each person lives without the redundant information of the name in the second table. But each row in the Field-table has to match exactly one entry in the Object-table.

The second design is to be used, if there is a relation between Object and Field and one field can belong to many Objects and one Object may belong to many Fields. That can be the case for a relation used for classes. A student can be enrolled in many classes and in each class there are many students. So your Object table would be students and you Field table would be class. Then you would store the relationship between those two in the ObjectField table.

Students:
ID          NAME
123         Joe
124         Jill

Classes:
ID          NAME
1           Math
2           History

StudentClass:
Student_ID  Class_ID
1           1
2           1
1           2

The student-class table would store redundant information in the field-table in your first design. But on the other hand would the second design need more space due to a third table, which is unnecessary in the zip code example. So it really depends.

One last thing. In your first design it is enough, if only ID is a primary key in the field table. There is no need to have a combined primary key consisting of ID and OBJECT_ID.


Drop a replicated subscription table that has been orphaned after restore migration

When you migrate the database all the information goes together.

Try this at the new server:

USE master
GO

EXEC sp_replicationdboption @dbname = N'YOUDATABASENAME',
                            @optname = N'publish',
                            @value = N'false';
GO

EDIT

OK, my first answer will kill all replication at the new server.

This will kill the subscriber at the table/article and after drop the article itself from replication:

USE YOUR_DATABASE
GO

EXEC sp_dropsubscription @publication = N'YOUR_PUB',
                         @subscriber = N'SUBSCRIBER_SERVER',
                         @destination_db = N'DATABASE_DSESTINATION',
                         @article = N'TABLE';
GO

EXEC sp_droparticle @publication = N'YOUR_PUB',
                    @article = N'TABLE',
                    @force_invalidate_snapshot = 0;
GO


Create previously created MySQL database without .sql file

This will depend a bit on your table type. With MyISAM tables, you can generally just copy the folder and files and everything will work. With other table types, it sometimes doesn't work as well.

The first thing you can try is to completely replace your MySQL data directory with the one from the backup. I would move it out of the way (mv /var/lib/mysql /var/lib/mysql-orig) so that you can easily restore it. Do this with the MySQL server not running and preferably on a testing server so you don't accidentally break something on your production machine.

Since you have the old hard drive data but can't get the drive to boot, it may be relatively painless to start MySQL from that drive (you can do this in a couple of ways; chroot to the old drive and try to start it there, copy the files to a server with a working MySQL instance, stop mysqld, edit the configuration file to point to the old mysql data directory, then restart MySQL, or finally just follow the steps above to completely replace the mysql data directory on the working instance with the one from the old server.

As D. Kasipovic points out, you should check the file permissions on the files you have restored, it could be as simple as a file permission problem.

You should make sure you're connecting as the root user or another user with administrative permissions; otherwise it's likely that a MySQL permissions will keep you from being able to see the databases. Depending what method ends up working for you, you may need to create any users that had permissions on that database.


How do the Oracle DB instance and the local listener communicate?

On Unix:
Oracle is multi-process database. It does not use threads (on the contrary to other databases). Some of the processes are background ones (Oracle internal) the other ones are user sessions processes. They all communicate via IPC (messages, shared ram).

When user opens a TCP connection to listener, the listener process forks and then executes(exec) oracle binary. This new process becomes a new Oracle session process. In this case Oracle protocol is wired - i.e. it uses only a single TCP connection (port 1521).

There is also more complicated way(especially on RAC). Listener instructs Oracle background process(PMON) and this one forks a new Oracle process, listening on a new dynamically allocated port. Listener then sends a "redirect" message to client process. Client then connects (via new TCP) to the session process.
This redirect message can point onto another cluster node(having different IP address)
PS: due this redirect, you should not use NAT and also both client and server should use the same DNS server(or at least server serving the same records).
I think this redirect message contains a hostname, not IP address.


documents don't go into newly added shard

Add Shards to a Cluster

As mentioned in MongoDB documentation,

  Adding a shard to a cluster creates an imbalance since the new shard
  has no chunks. While MongoDB begins migrating data to the new shard
  immediately, it can take some time before the cluster balances.


show users doesn't show anything, although it has users

First of all, I thought you are talking about MongoDB users. So, I had to change my answer.
You can try the below command for seeing to the collection which is users
db.users.find({})


Should I index before or after importing a dump?

Yes, you can index them after you have imported (there will then only be the default _id index on the collection).  This is also recommended because the resulting indexes will be more compact and more efficient (for similar reasons foreground vs background indexing is preferred if you can afford to do it).  It will take some time to complete though, especially with 10 indexes to build.

To build after the import, simply do not define any indexes until after your import is complete, then use the ensureIndex() command to create the required indexes afterwards (with the usual caveat that such index creation will be resource intensive).  For more information:

http://docs.mongodb.org/manual/core/index-creation/


MySQL - Binary Log File size growing more than the maximum limit

The reason your binary logs are so gigantic is simple: YOU ARE COMMITTING TOO MUCH DATA PER SINGLE TRANSACTION !!! Why is this the case ? According to the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

SUGGESTION
Please try to commit data in smaller chunks.


Take Tail-Log backup before restore option

No, it does not restore the tail.  The tail BEFORE the restore enables you to restore to the previous full backup and then play all transaction logs back and the last thing it will play back is the tail.  This will get you back to the instant just before the bad restore.


Revoke write privileges vs setting database to readonly

I would choose Option 2

If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up.
Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant.
With Option 2, grants remain unchanged on disk and in memory.

Option 2 could also be written as
FLUSH TABLES;
SET GLOBAL read_only = 1;

That way, you only have to run SET GLOBAL read_only = 0; to get writes going again. FLUSH TABLES WITH READ LOCK does not halt InnoDB writes to Transaction Logs, Redo Logs, an Undo Logs. That's why SET GLOBAL read_only = 1; is your friend.
CAVEAT
In my early days a a DBA, I used to do this:
CREATE TABLE mysql.usercopy LIKE mysql.user;
CREATE TABLE mysql.root     LIKE mysql.user;
INSERT INTO mysql.usercopy SELECT * FROM mysql.user;
INSERT INTO mysql.root SELECT * FROM mysql.user WHERE user='root';
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.root;
FLUSH PRIVILEGES;

When I was done, I did this
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;
FLUSH PRIVILEGES;

Then, I discovered SUPER.
EPILOGUE
Go with Option 2 as is, or change FLUSH TABLES WITH READ LOCK; to FLUSH TABLES;


List minutes instead of grouping by them

You can use the timestampdiff function. You need to use the start date as a second argument, like this:

SELECT TIMESTAMPDIFF(MINUTE,'2014-06-12 10:00:00', timestamp) AS mins,
COUNT(field)
FROM table
WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY mins;


SQL Server 2008R2 - how to install replication components

You might get away with using the eval ISO.

Try download that, burn it to a disk (or mount it by other means) and see if it will cooperate. It'll certainly contain everything, assuming it is as happy to be used to update your licensed install as it is an eval one.

EDit: I've removed the link as it was incorrect. It looks from trying to search on their site that SQL 2008r2 eval isn't available direct from MS any more. You might be able to find it elsewhere though.


That functionality does not exist. There are two things to keep in mind:
Create the Index Anyway
You can generate index in such a way that the index is created without checking if the index exists ahead of time. For example, you can run the following:
ALTER TABLE table_name ADD INDEX (column_to_index);
ALTER TABLE table_name ADD INDEX (column_to_index);

This will definitely create two indexes without checking. Each index will be assigned a name (perhaps column_to_index,column_to_index_1). Of course, you are trying to avoid that.
Check INFORMATION_SCHEMA first
Here is the layout of INFORMATION_SCHEMA.STATISTICS:
mysql> show create table statistics\G
*************************** 1. row ***************************
       Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLLATION` varchar(1) DEFAULT NULL,
  `CARDINALITY` bigint(21) DEFAULT NULL,
  `SUB_PART` bigint(3) DEFAULT NULL,
  `PACKED` varchar(10) DEFAULT NULL,
  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
  `COMMENT` varchar(16) DEFAULT NULL,
  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

You could just query for the existence of the index by name. For example, before you run
CREATE INDEX index_name ON mytable(column);

You need to run
SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';

If IndexIsThere is 0, you can create in the index. Perhaps you can write a stored procedure to create an index on the table of your choice.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
CREATE PROCEDURE `adam_matan`.`CreateIndex`
(
    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)
)
BEGIN

    DECLARE IndexIsThere INTEGER;

    SELECT COUNT(1) INTO IndexIsThere
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_schema = given_database
    AND   table_name   = given_table
    AND   index_name   = given_index;

    IF IndexIsThere = 0 THEN
        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
        given_database,'.',given_table,' (',given_columns,')');
        PREPARE st FROM @sqlstmt;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    ELSE
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;
    END IF;

END $$

DELIMITER ;

Here is a sample run (Hey Remember This Table? It's from the question you asked back on June 27, 2012) :
mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
Query OK, 0 rows affected (0.20 sec)

mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`),
  KEY `type_timestamp_id_ndx` (`type`,`timestamp`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
+-----------------------------------------------------------------------+
| CreateindexErrorMessage                                               |
+-----------------------------------------------------------------------+
| Index type_timestamp_id_ndx Already Exists on Table adam_matan.pixels |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Give it a Try !!!


Oracle 12C peformance issue - waiting time increased drastically

There are some known performance issues after upgrading to 12c that are related to the new optimizer features. Try the following to see if it corrects your issue. Note that this will turn off these features for now.

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES=false scope=both;
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0 scope=both;
ALTER SYSTEM SET "_fix_control"='12914055:Off' scope=both;


The best way to rename the tables after development completed

I think the approach depends on whether the application(s) are live or if you are still testing.

For tables, the safest approach is to create a synonym using the new name. This way you can change the app(s) one at a time (or even one reference at a time), without having to change all of them at once. You don't have to drop the synonym and rename the table until you are confident you have all the changes in place.

CREATE SYNONYM dbo.NewName FOR dbo.OldName;
-- change app to point to dbo.NewName;

-- once all of your changes have been tested:
BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
COMMIT TRANSACTION;

For columns, it is a little trickier. You can create synonyms that point to a view instead, but not all views will necessarily be updatable depending on the base table. As a simple example:

CREATE VIEW dbo.vNewName
AS
  SELECT Column1, NewColumnName = OldColumnName
    FROM dbo.OldName;

CREATE SYNONYM dbo.NewName FOR dbo.vNewName;

Then like above, when you have changed all references to columns and the new table name, simply:

BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  DROP VIEW dbo.vNewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
  EXEC sp_rename N'dbo.NewName.OldColumnName', N'NewColumnName', N'COLUMN';
COMMIT TRANSACTION;

If the application is not live and is still going through testing, just rename the columns and fix what breaks after a global search and replace (or smart refactor using SSDT, RedGate, etc) through app code / procedures etc.

If the application is live, you will need to step a little more gingerly.


I don't need my transaction log.

Yes you do.

  can i delete my log to increase space?

No you can't.

  In this case, is there an "easy way" to copy the data from the old tables into new tables?

Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your current database.  Unless you logged workload changes, the same behavior will occur in the new database.

Find out first how large your transaction log file is and how much space is actually being used inside the file:

use YourDatabaseName;
go

select
    name,
    physical_name,
    type_desc,
    size_mb =
        convert(decimal(10, 2), size * 8.0 / 1024),
    used_space_mb =
        convert(decimal(10, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
from sys.database_files;

Note: you'll need to change "YourDatabaseName" to the appropriate database name.

Likewise, there are certain database settings (recovery model) and log reuse descriptions that can also aid in the troubleshooting of a transaction log that is just too large:

select
    name,
    recovery_model_desc,
    log_reuse_wait_desc
from sys.databases
where name = 'YourDatabaseName';

The above queries should be a decent starting point with troubleshooting and/or discovery regarding your problem.  Feel free to comment below with your findings and we can take this a step further.

Another thing, are you completely confined to SQL Server 2008 for your Express Edition?  In SQL Server 2008 R2 and above, the maximum database size is 10 GB.  Can you not use a newer version of SQL Server?


Database Hangs while impdp "DATABASE_EXPORT/STATISTICS/MARKER" at this stage

Known bug, still under investigation.

Workaround is to use exclude=statistics.


Copy database wizard-generated job blows up log file; alternative?

The package created by the copy wizard can be modified to bring a little better performance possibly but your log growth is expected. Every bit of data changed for the table and indexes is being modified, that has to be logged no matter what recovery model you are in.

I would go with the restore from backup as this will be a bit cleaner. You can pull the last backup file name from the msdb backup tables. There are a ton of examples of this online, would check MSSQLTips.com for a start.

You would simply pull the last backup, verify it exist, robocopy it over (unless it exist on network share), then restore it.


What's better here - a single column or multicolumn primary key?

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice:

CREATE TABLE item (
    item_id serial PRIMARY KEY,
    grp_id  integer NOT NULL REFERENCES grp(grp_id)
);

If you have an item_id, better make it unique and ideally a surrogate primary key without any specific meaning for the number itself. That's cheap and versatile. Per-group (not per-row) serials are typically expensive, pointless effort. If you desire a gap-less numbering per group, add some kind of rank column or use row_number() in a view. And don't rely on a gap-less numbering if you can avoid it. Sooner or later you'll have to delete or move around items.
It is convenient for maintenance to have a single column primary key that follows a naming convention (unless you have an obvious natural pk).
Don't use group as identifier. It is a reserved word. Even if these are just symbolic names.
If you have queries by grp_id, add another index:

CREATE INDEX item_foo_idx ON item (grp_id, item_id);

You might want to make it a UNIQUE INDEX for documentation, but that's redundant since item_id is unique already. With two integer columns, a multicolumn index is just as big as an index on just grp_id and there seem to be use cases where you need these two columns, so there is the chance for a covering index. This should be the optimum. More rationale here:
Is a composite index also good for queries on the first field?


ORDER BY too slow in Foreign Table using postgres_fdw

Sort operation is performed on local postgres server, not on remote. If your code always sort the results, you
  can just create a view on remote postgres with order by clause and
  then create a foreign table pointing to the view.

On remote server, adding a view

CREATE VIEW vw_user_info_raw
SELECT id, info
FROM user_info_raw
ORDER BY id

On local server, create foreign table references to that view

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb
)
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'vw_user_info_raw' );

Refer to Postgres Admin Forum


42702 'column reference "id" is ambiguous' for RETURNING of table-returning PL/pgSQL function

The solution is to qualify all columns from RETURNING which have the same name as the columns from RETURNS TABLE with the name of the table which was INSERTED INTO:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING foo.id;
  END
$$ LANGUAGE PLpgSQL;

If the name of the table is long and there are multiple columns, the name can be aliased:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID, foo INT, bar INT) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO table_with_a_very_long_name AS x DEFAULT VALUES
    RETURNING x.id, x.foo, x.bar;
  END
$$ LANGUAGE PLpgSQL;


What happens on the server when a MySQL client connection is lost?

In your case it shows that the session from client A is interrupted but not released on DB because of the MySQL instance has not terminated the connection from client A as it feels their is some calculation going on client side. The MySQL instance only stops/terminates the connections once it reaches the threshold time for session staying in IDLE state.
So, this IDLE state time limit is manage by wait_timeout parameter. So, you are suppose to set the value for wait_timeout parameter to your optimal usage of instance. Many of the cases 180 seconds seems to be ideal.


SQL Server 2008 user, group, or role already exists in the current database

Note : Whenever you move your database from one server to another
  server there is a problem of orphan user usually occur. FileListOnly
  is the new term in sql server that has all the details of the created
  backup that who has access to it.

So there are sequence of task that you have to follow

  Firstly you have to restore the FileListOnly from the created backup to the destination or new server.
  Restore the backup.
  Use the sp_change_users_login  as per the need. For help regarding this procedure you can refer
  http://msdn.microsoft.com/en-us/library/ms174378.aspx.

I put a example here hope it will help:

> RESTORE FILELISTONLY FROM DISK = N'C:\YourDB.bak'
>
> RESTORE DATABASE YourDb FROM DISK = N'C:\YourDB.bak' WITH MOVE
> N'YourDB' TO N'D:\YourDB.mdf', MOVE N'YourDB_log' TO N'D:\YourDB.ldf',
> REPLACE
>
> exec YourDB.dbo.sp_change_users_login 'update_one', 'UserName','UserName'


What is the correct term to describe a single "data point" in a database?

The term
Attribute
would be the most appropriate.

Relational Database Theory

Basically, you're talking about columns and 'Column' is most appropriate when talking about the database itself or in SQL terms, however in common parlance this may not be understood by the layperson. 'Field' is a somewhat archaic term that can also be used and is usually associated with forms, but 'Attribute' is the technically correct word in Relational Theory, and would probably be most likely to be convey the meaning that you want to people.


How to access a SQL Server database from other computer connected to the same workgroup?

There are two levels of security that need to be changed in order to allow remote access.

SQL Server configuration. By default in Express, Developer, and Enteprise Evaluation editions, connecting by the TCP/IP protocol is disabled. Enable this using SQL Server Configuration Manager.
Windows Firewall. While disabling the firewall entirely will work for this component, doing so is not a security best-practice (nor is it required). (Note: in this section, I assume a default configuration. There are many settings that can be changed which affect these steps slightly.)

There are two cases depending on the type of SQL Server instance you're connecting to:

Default instance (connect by computer name only). Add an allow incoming rule either on TCP port 1433 or the database engine service.
Named instance (connect by computer name + instance name). Add an allow incoming rule on UDP port 1434 to access to the SQL Browser service. Add an allow incoming rule on the database engine service.

This procedure is fully documented here, or you can watch me explain and then walk through the configuration for a named instance here.


SQL Server Full-Text Index rebuild causing latency on other indexes

Using swasheck's comment as a potential answer, you can define a job to run the fulltext rebuild whenever the CPU becomes idle.

I also included the location of the crawl logs that SQL Server maintains everytime a catalog is populated.

Idle CPU

Defining when a CPU idle condition occurs is done in the properites of the SQL Server Agent.

http://msdn.microsoft.com/en-us/library/ms189065(v=sql.105).aspx

Using perfmon to identify an inactive timeframe

Another option would be to use Perfmon's Data Collector.  You can use the SQL Server General Statistics Login counter to identify a timeframe when the server is less active. You can then define your Full Text Rebuild to run during that timeframe.

I didn't notice any particular way to limit the population or rebuild to one cpu with MAXDOP, like you can with rebuilding indexes.

ALTER FULLTEXT INDEX ON database_name.schema_name.table_name START FULL POPULATION;
http://msdn.microsoft.com/en-us/library/ms188359.aspx

ALTER FULLTEXT CATALOG catalog_name REBUILD;
http://msdn.microsoft.com/en-us/library/ms176095.aspx

Checking the FT Logs

There are logs that monitor the population of full text catalogs. These can be used to identify problem areas and also give you an idea of how long each catalog is taking to populate.

  %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

  2012-07-13 04:00:04.62 spid105s    Informational: Full-text Full
  population initialized for table or indexed view
  '[database_name].[schema_name].[table_name]' (table or indexed view ID
  '66099276', database ID '13'). Population sub-tasks: 4.

  2012-07-13 04:04:14.59 spid131s    Informational: Full-text Full
  population completed for table or indexed view
  '[database_name].[schema_name].[table_name]' (table or indexed view ID
  '1621580815', database ID '13'). Number of documents processed: 101.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.


I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

Using REPEATABLE-READ means that your SELECT transaction gets to view a "snapshot" of the state of data as of the moment the transaction started.  Even if other sessions subsequently insert more data, the SELECT transaction will not see them.  That's the way it's supposed to work.

So your query is returning 100% accurate results, with respect to the data that existed at a specific point in the sequence of INSERTs.  But you are rapidly changing the data, so your SELECT is bound to return an outdated report.

Using another transaction isolation level probably won't help.  InnoDB refreshes a transaction's snapshot only at the beginning of a statement.  In other words, if the SELECT takes 0.2 seconds, and during that time another 100 INSERTs happen, those 100 INSERTs won't be included in the result of the SELECT.

http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html says:

  With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

The only way you can ensure your SELECT is totally in sync with the latest committed data is to prevent any new data from committing while the SELECT is executing.  You can lock the tables temporarily with SELECT ... LOCK IN SHARE MODE.  See http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

But even if you do this, as soon as you finish that transaction, the lock is released and new INSERTs start committing, making the result of your SELECT out of date almost instantly.


When should I mark a column as the primary key?

It depends on many things. Assuming you're using innodb it comes with a big price. So you need to value and balance pros and cons for your use case.

Downsides of big varchar column:

Due to the random order of tokens

your inserts are going to be significantly slower,
will cause massive fragmentation and
much more frequent page splits

Due to this fragmentation the table will actually use you more space than you can save on omitting the the 4 or 8 byte integer key you're using.
If you have any other keys they are going to contain this big primary key at the end further bloating your table: https://www.percona.com/blog/2006/10/03/long-primary-key-for-innodb-tables/

Upsides of natural primary key:

By the nature of InnoDB primary key lookups are very fast so if you query your table by only this columns this might give you some edge (although adaptive hash index helps quite a lot with secondary key lookups)

Most of the times people are trying to use natural keys to eliminate the id column for space consideration which is just the opposite of what is actually happening. If you want that for query optimization and that outweighs the downsides than go for it.


There has to be some invariant between two versions to say the versions relate to the same thing. This is the job of the primary key. It exists to differentiate this thing from that thing and to say that all that stuff over there relates to this one thing over here, via the foreign key of stuff.thing_id.  If you change the primary key - Name, or GroupID in your case - you have a new thing, not an old thing with a new name. This is one of the reasons we have impersonal identification like social security numbers.

You have a couple of options. A) re-design your tables so there is an invariant identifier. An integer surrogate key works well for this. B) add two columns Previous_Version_Name and Previous_Version_GroupID. Populate these as you accumulate values for the new version. Use them in your join. "A" would be better.

You may also want to think about using a partitioned table and / or views rather than move data around between TableA and TableB yourself.


I found a solution to my problem; here is what I did:

<?php
ini_set('display_errors', 1);
set_time_limit(0);
$db_host = 'localhost';
$db_user = 'root';
$db_password = 'PASSWORD';
$db_1 = $argv[1];
$db_2 = $argv[2];
$verbose = isset($argv[3])? '-'.$argv[3] : '';

$conn = mysqli_connect($db_host, $db_user, $db_password, $db_1);
$show_tables_query = mysqli_query($conn, 'SHOW tables');

$tables = array();
while ($row = mysqli_fetch_assoc($show_tables_query))
{
    $tables[] = $row['Tables_in_'.$db_1];
}

foreach($tables as $table)
{
    echo "$table\n*******************************\n\n";
    system("mysqldiff $verbose --difftype=differ --server1=$db_user:$db_password@$db_host --server2=$db_user:$db_password@$db_host $db_1.$table:$db_2.$table");
    echo "\n\n";
}

?>


I've solved it by doing the following:

SELECT nWidth, nHeight, nColumn,
(SELECT MAX(t.nRow + (t.nHeigth - 1)) FROM test t WHERE t.nRow = tp.nRow
GROUP BY t.nRow LIMIT 1) AS nRow
FROM test tp ORDER BY nRow, tp.nColumn

This will get the max from the row number plus it's heigth minus 1.
So, if we have a row that has a cell with height equal or higher than 2, all its cells are going to be in the same row. So, inside that row, we order by Column.

Hope this helps someone.


Is it a good choice to choose NoSQL for a finance management app?

The first thing you should do is to read this (from 2010). If that doesn't convince you, the read this (from 2014) and this (from 2017). Developers are discovering that SQL, transactions and consistency weren't such old-hat after all!

NoSQL is a tasty soufflé composed of more air than substance. There are some cases for which NoSQL is a good fit, and those are NOT finance management apps!

You abdsolutely want strong consistency for anything to do with monetary activities! NoSQL is one of those industry bandwagons which come along from time to time, like a star which is about to go supernova, briefly shines brightly, only to explode, then withers and dies a lingering and painful death much like a white dwarf!

NoSQL paradigms are good for social media type sites (i.e. the user typically isn't paying anything, or for only a small subset of services) where absolute consistency (through proper transactions) isn't a priority.

Systems such as marketing and/or analysis and/or reporting apps could be NoSQL. However, SQL is also great for slicing and dicing data for marketng, analysis and reporting. See Brian Aker's (former MySQL chief architect) hilarious take on NoSQL here.

From a similar thread about banking, there is a suggestion that NoSQL could be suitable for banking if the system has strong consistency and transactions - which brings us back to the RDBMS anyway. This is sort of the point of the 2014 link above - i.e. that NoSQL systems are all trying to put a strongly consistent SQL layer above their systems - we're back to the classic database: It's like déjà vu all over again :-).

Take a look at what Michael Stonebraker - a man who knows a thing or two about databases calls NoSQL a giant step backwards. He proposes "NewSQL" which "all support the relational data model and use SQL as their primary interface".

Re. MEAN stack, there's nothing wrong with using JavaScript frameworks for your front-end, just change the backend to an RDBMS. You should let the functionality decide the stack, not the other way round! If you want to go with Open Source, then my recommendation is that you use PostgreSQL (the best one in the field but a country mile!).


Partitioning - when to create new scheme?

Firstly, I would encourage you to read Dan Guzman's excellent post on Table Partitioning Best Practices.  It does a great job of explaining why:

  "One needs a good understanding of how the RANGE LEFT/RIGHT
  specification affects partition setup and management."

Now, on to your question.

I'm a little puzzled why you're adding a new partition for '2019-01-01', when your latest partition seems to be for '2016-01-01'.  Since you're trying to add a new partition for '2019-01-01', I would've expected to already see partitions for '2017-01-01' and '2018-01-01'.

In any event, when you're planning on using the SPLIT command, the main thing you want to consider is minimizing data movement between existing partitions.  Pre-defining empty partitions 'before' the minimum value you can have and 'after' the maximum value you can have will allow you to minimize data movement when using the SPLIT.  Referencing Dan's post:

The actions performed by a SPLIT of a RANGE LEFT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are less than or equal to
the new boundary into the newly created partition on the left

The actions performed by a SPLIT of a RANGE RIGHT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are greater than or equal
to the new boundary into the newly create partition on the right

It looks like you might be using RANGE RIGHT partition.  As long as you currently do not have any dates that are greater than or equal to '2019-01-01', there should be no data movement and the SPLIT should execute very quickly.  You definitely want to get the '2019-01-01' partition defined before you get too much data in your table with dates greater than or equal to that date.

The main point I'm trying to stress is to make sure you already have partition boundaries defined well ahead of the data that currently exists on the table.  Many times, you can go ahead and define partitions ranges years in advance of the data, but you should always monitor your partitioned tables to ensure you aren't creeping up on an ending boundary.


It's probably fastest to use COPY (or \copy) to copy the data into unencrypted tables. Disable triggers, foreign keys, indexes.

Then encrypt, and add your indexes and keys again.

Use one SQL operation to encrypt, like

update my_table set
    bytea1 = pgp_sym_encrypt_bytea(bytea1,var_pwd),
    bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd)
    ....;

Or do a insert into...select into a new table, encrypting in the select.

See also https://wiki.postgresql.org/wiki/Performance_Optimization


mysql 5.7 general_log table has user_host but the file does not

IDEA #1 : USE THE SLOW LOG !!!

The Slow Log ??? Yea, I know what I said : USE THE SLOW LOG !!!

How can you ???

With the slow log being written to a text file, the header info for each slow log entry has the user@host right on the first line of each entry (ignore the # Time: line since one or more slow log entries can be saves within a single time window)

For example, the post Mysql slow query log always include "# Time:" has this example slow log:

# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;
# Time: 150419  6:00:45
# User@Host: web[web] @ localhost []
# Query_time: 3.480173  Lock_time: 0.000101 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
# User@Host: web[web] @ localhost []
# Query_time: 3.388204  Lock_time: 0.000133 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..

In your case, you could probably run

grep -A 3 billybob log_file

which shows for each occurrence of billybob

The line matching billybob
The Query Time
Actual timestamp
Line 1 of the actual SQL

In order to make the slow log behave like a general log you must run

mysql> SET GLOBAL long_query_time = 0.01;

Then, everything and its grandmother will be written to the slow log.

When you are done with your analysis, set the long_query_time back to its old value:

mysql> SET GLOBAL long_query_time = 10;

Don't forget to truncate the slow log after the analysis by doing

echo -n > log_file

IDEA #2 : Poll the Processlist

The problem with using either the slow log or the general log is that entry will not entry the log until the action is done. In other words, if a query takes 10 min to run, the entry will not land in the log file until the command is done executing 10 minutes after it started.

Wouldn't it be nice to catch the bad queries in the act ???

You should use pt-query-digest. Even with queries that are not done, a histogram will be generated with the pattern of queries that have executed or are still in progress.

Rather than write up an example, please see my posts where I discuss using pt-query-digest

Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
Nov 24, 2011 ": MySQL general query log performance effects

GIVE IT A TRY !!!


How to select user that doesn't exist in the second table

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL:

SELECT
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follower ON  follower.users_id = user.id
                            AND follower.shown_users_id = 2
LEFT JOIN
   user_follows AS followed ON  followed.shown_users_id = user.id
                            AND followed.users_id = 2
WHERE
   follower.users_id IS NULL
  AND
   followed.users_id IS NULL
  AND
   user.id <> 2 ;

or with two NOT EXISTS subqueries, which I find far more readable:

SELECT
   user.id
FROM
   users AS user
WHERE
   NOT EXISTS
      ( SELECT *
        FROM user_follows AS follower
        WHERE follower.users_id = user.id
          AND follower.shown_users_id = 2
      )
AND
   NOT EXISTS
      ( SELECT *
        FROM user_follows AS followed
        WHERE followed.shown_users_id = user.id
          AND followed.users_id = 2
      )
AND
   user.id <> 2 ;


Cursors may be the right solution to a given problem, but usually not in SQL Server.  Try to use set-based solutions instead of an iterative one if at all posible.  Worst case scenario, you can look at using an iterative SQL solution.  I've done that before and ended up an order of magnitude faster than an equivalent cursor implementation.

You can return BLOBs from the DB in normal SQL statements so you don't need a cursor for that - granted, if your application can't handle the incoming data stream, you might be changing cursor waits for async_network_io waits (meaning SQL is done, but your app is too slow - Access does this all the time).

If you post more details about what your app is doing or more specifics, we might be able to get more focused advice.


Join a table to itself, each row needs to join to the ordered last record

You can use LEAD() function to get the next value:

Quoted from docs:

  Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

SELECT
  UID,
  historyFor,
  DateUpdated,
  [Table],
  [Column],
  oldValue,
  LEAD(oldValue) OVER (PARTITION BY [Table], [Column]
                       ORDER BY DateUpdated) nextValue
FROM
  t;

UID | historyFor | DateUpdated         | Table  | Column    | oldValue | nextValue
--: | :--------- | :------------------ | :----- | :-------- | :------- | :--------
  1 | uid=123    | 01/01/2019 00:00:00 | Person | firstname | Joe      | Bill
  2 | uid=123    | 01/02/2019 00:00:00 | Person | firstname | Bill     | Elliot
  3 | uid=123    | 01/03/2019 00:00:00 | Person | firstname | Elliot   | null

db<>fiddle here


Below is an example that uses a recursive CTE to create weekly values and a simple INSERT...SELECT to populate your table.  I assumed a few identity IDs for testing's sake in addition to the postdate being the current date.  You don't need a cursor to do this work.  Enjoy!

    --CREATE SCENARIO TEMP TABLE
    IF OBJECT_ID('tempdb..#TempUserPaid','U') IS NOT NULL
        DROP TABLE #TempUserPaid

    CREATE TABLE #TempUserPaid
    (
          aid INT IDENTITY
        , uid INT
        , eid INT
        , name VARCHAR(50)
        , dob DATE
        , paid DECIMAL(19,4)
    )

    INSERT #TempUserPaid
    VALUES
         (100,10,'Mick','1980-03-29',40.00)
        ,(101,11,'Jack','1969-11-21',40.00)
        ,(205,11,'Lynn','1990-07-10',80.00)
        ,(217,10,'Dana','1987-02-15',40.00)

    --CREATE WEEKLYPAYMENTS TABLE IF IT DOESN'T EXIST TO MODEL USER TABLE
    --DROP TABLE WeeklyPayments  --> CLEAR OUT FOR TESTING PURPOSES
    IF OBJECT_ID('WeeklyPayments','U') IS NULL
        CREATE TABLE WeeklyPayments
        (
              id INT IDENTITY
            , uid INT
            , eid INT
            , [week] INT
            , postdate DATE
            , paid DECIMAL(19,4)
        );

    --INSERT YOUR CODE TO GENERATE NUMBER OF WEEKS HERE TO SET @weeksInMonth
    --HARDCODING FOR THIS EXAMPLE
    DECLARE @weeksInMonth SMALLINT;
    SET @weeksInMonth = 4;

    --RECURSIVE CTE TO GENERATE THE NUMBER OF WEEKS
    WITH cteWeeks AS
    (
        SELECT 1 WeekInMonth
        UNION ALL
        SELECT WeekInMonth+1
        FROM cteWeeks
        WHERE WeekInMonth < @weeksInMonth
    )
    INSERT WeeklyPayments
    SELECT t.uid, t.eid, c.WeekInMonth, CONVERT(DATE,GETDATE()), CAST(t.paid/4 AS money)
    FROM #TempUserPaid t, cteWeeks c
    ORDER BY t.uid;

    SELECT * FROM WeeklyPayments;


Using oracle easy connect does it really connect to the remote DB?

The answer is very simple. The ! prefix in sqlplus executes the following text as a command in the local shell.

In your case it's executing the Unix hostname command locally.

Query v$instance to check the name of the machine you're connected to.

There are ways to execute local Unix commands via the database, but it's seen as a huge security hole, so I won't document them here.

ssh (assuming Unix) into the database host instead.


Fixing a query that returns failed job information

This is a little scrappy, but it is a start...

I have extracted the Schedule ID from the [last_outcome_message] field, and joined it to the Schedule ID.  That should give you the correct result set

I could not find anywhere else where the schedule_id is stored against a History
Event.

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id

     AND CONVERT(INT, LTRIM(RTRIM(REPLACE(LEFT(last_outcome_message,
                   PATINDEX('% (%', last_outcome_message)),
                   'The job failed.  The Job was invoked by Schedule ', ''))))
          = sjc.schedule_id

LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE
run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC


Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a slow drive this growth activity is going to be quite painful.

If you do shrink the database, what are you going to use the freed up disk space for? Again if you are just going to keep that space free in case this database grows again, then you're just spinning your wheels.

What you might consider doing, now that you've got all this free space in the file, is rebuilding your indexes so that they are better optimized (and it will be much less painful to do this when you have free space to do so - think about trying to change a sweater in a tiny closet vs. a big bedroom).

So unless this was a major cleanup operation and you really won't be ramping up to the same level of data again, I'd just leave it as is and focus on other areas of optimization.


Call to default values (MySQL) in a procedure

The point of "default" values is that they are the default values.  You Don't need to list them in the insert query at all.  They'll be inserted automatically.

To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW()

Stop and consider DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.  The default value here is only CURRENT_TIMESTAMP -- ON UPDATE ... is a completely unrelated directive, so here again, you need to just pass NOW() or CURRENT_TIMESTAMP() if you want to pass these into procedures... though that us unnecessary if you modify the inside queries not to specify the columns you are free to exclude, because they have default values.


Brent Ozar has a good list of use cases for extended events and how to implement them.
More specifically Erik Darling also released a procedure sp_HumanEvents: Humanizing Extended Events that simplifies monitoring extended events for the usual use cases.


Handling misspellings when searching across tsvector columns

I don't think there is any built-in way to do this. I think it would usually be handled by spell checking the search phrase before it ever gets submitted to the database (or by spell checking each word in it after it had been submitted and found no results).

You can use pg_trgm to do fuzzy searching which will be likely to find misspellings, but it doesn't integrate with tsvectors the way you apparently want.  One way to use that would be to create two indexes, one FTS based on stemming, and the other for fuzzy searching based on trigrams.  Then you could do the query one way (the @@ operator over tsvector column), and if it got no results then redo it the less-preferred way (likely using the %>> operator over raw text column/expression).

If you do find something that integrates these the way you want automatically, please post an answer to your own question.


MongoDb: sharded replica set: data distribution

Sometimes, the 3 config server are out of sync. What does it mean? How can I quickly get rid of these issues?

  Most convenient way would be to copy (cp or rsync) the configdb data directory content between hosts in cases of inconsistency

When should I choose MongoDb Shard with Replica Set vs MongoDb Replica set only?

  Replica ONLY: When you desire to make your mongo data highly available
  and add redundancy in your solution. The benefit can be to offload
  reporting requests to secondary nodes and/or make mongodb cluster
  sustain network partitions or disaster recovery

  Sharding ONLY: When you desire to scale your mongodb cluster
  horizontally to achieve load balancing and distribute traffic to
  multiple hosts you must choose sharding. For example, you can achieve
  partitioning benefits with sharding enabled on databases.

  Sharding with replica set: When you desire to enable all benefits
  stated above i.e. scale you cluster horizontally to handle large
  volumes of data for load balancing and also achieve HA within each
  shard you must choose this approach


GIS Point or discrete longitude and latitude columns?

Points are useful for spatial relationships.  You can determine what points are within polygons (addresses within a city boundary, as a city is rarely a simple square), what point is closest to another point (bakeries near your home), etc.  Using Lat/Long you can calculate some of this but there are times that Lat/Long will get the answer very very wrong (http://workshops.boundlessgeo.com/postgis-intro/geography.html).

A great intro to geometry in Postgres:
http://workshops.boundlessgeo.com/postgis-intro/

Another way of looking at it:
Looking at a map of the United States how would you quickly determine if a point is within the country?  By using min/max lat/long you would have to include AK and HI (and maybe Puerto Rico, Guam, etc) and the min/max box of lat/long suddenly won't work because it overlaps Canada/Mexico/etc.  But using a point you can then check via ST_Within() in a quick geometry check.


Replication stops with GTID_NEXT error after creation/drop of memory table in mysql5.6

I encountered a similar problem that generated the same error. On a master instance I dropped a database.
On a slave there was a file left over <tablename>.exp (I forget what purpose this serves).
The slave replication died with

last_Error: Error 'DROP DATABASE failed; some tables may have been
dropped but the database directory remains. The GTID has not been
added to GTID_EXECUTED and the statement was not written to the binary
log. Fix this as follows: (1) remove all files from the database
directory ./db_to_drop/; (2) SET
GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'; (3) DROP
DATABASE db_to_drop.' on query. Default database: 'db_to_drop'.
Query: 'drop database db_to_drop'

I removed the file, ran the SET and restarted replication:

SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506';
start slave; ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a
GTID, you must explicitly set it to a different value after a COMMIT
or ROLLBACK. Please check GTID_NEXT variable manual page for detailed
explanation. Current @@SESSION.GTID_NEXT is
'c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'.

And this, from following the suggestion. Since it's only a development cluster, I wasn't worried about losing it entirely, so I punted and tried setting it to the next GTID.
SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066507';
(i.e. + 1). Restarted replication and things got back to normal.


SYMMETRIC key & certificates role in sql server?

You are protecting agains accidental media loss (a lost laptop with the database, your drive showing up on a flea market with a copy of the database or of a backup) etc etc. Any scheme in which the processing itself (the database engine or the application) requires to access the data without the user providing a password only offer access agains media loss. The encryption key hierarchy is rooted on the system DPAPI encrypted key, in other words on the password of the service account. Such a scenario never protects agains a hacker that gets access to your SQL Server, and is not meant to protect such.

The alternative is to ask the user for a password each time it uses the application and use this password to open the key at the top of the encryption key hierarchy (usually a certificate in the database). This scheme is very seldom deployed in such cases as some multi-tenant scenarios when the tenants do no trust the hosting operations/administrators.


How to delete the old backup differencial files?

Method1: The Best method to achieve this would be using Olla hallengren Backup scripts.

You can find the scripts at https://ola.hallengren.com/downloads.html

First thing you need to do is execute  CommandExecute.sql script and then run the databasebackup.sql script.

Once done you can execute below via SQL Agent job as per you're required schedule:

  EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'C:\Backup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 24

Here Backup type can be changed from FULL, DIFF to LOG(If any DB in full recovery model exist) as per the need. And fro @ cleanup time- Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

Refer to below link on further details:

https://ola.hallengren.com/sql-server-backup.html

Method 2: You can always refer to Maintenance plans for clean up within SQL server

Use below query as an example to delete the old backup files as per the need:.

 EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'
GO

Refer to below link for using setting up cleanup task.

http://www.biztalkadminsblogging.com/index.php/blog-archive/item/20-automatically-remove-old-backup-files


SQL 2008 Developer Edition, not using all CPU´s

Check the SQL Log Viewer in SSMS from the last time the database service was restarted, it will note how many CPU's are detected; and like @Aaron Bertrand noted, be sure you are running queries that will take advantage of parallelism, and that your MAXDOP is set correctly.


Database design help. many-to-many relationship

Just have a table with a composite primary key on value1, value2, value2

If any of the 3 are restricted then have FK relationship on the column


What is the formal definition of a Primary Index and Primary Key?

A "key" is a logical construct used to identify a specific record. Given their purpose, keys require uniqueness. Primary Key fields are also required to be declared as NOT NULL. There are also "alternate" keys which allow key fields to be declared as NULL. Both Primary and Alternate keys have an optional, secondary purpose of establishing relationships via Foreign Keys (declarative referential integrity).

An "index" is a physical construct used to access one-or-more records. Uniqueness is not required for this purpose. When an index is declared as unique, it is not required that the indexed column(s) be declared as NOT NULL; NULL is allowed so long as it is unique, or a unique combination when working with multiple index columns.

Given the two quoted paragraphs, here is a terminology translation table:

BOOK → SQL Server
"file" →  Table or Index
"primary index" →  UNIQUE CLUSTERED Index
"clustering index" → non-unique CLUSTERED Index (due to the cluster key column(s) being the row identifier, there is a need for true uniqueness, and so SQL Server will guarantee that uniqueness by adding a hidden, auto-incrementing INT value called the uniqueifier, but only for actual non-unique key values: please see Clustered Index Uniquifier Existence and Size for details)
"secondary index" → NONCLUSTERED Index
"primary key" → PRIMARY KEY defined on a UNIQUE CLUSTERED Index

In SQL Server, a Primary Key does not need to be defined on a UNIQUE CLUSTERED Index, it just requires a Unique Index (though the default behavior when creating a PK when no clustered Index exists is to make the PK clustered).

Also, a Foreign Key can reference either a Primary Key or an "Alternate" Key (i.e. Unique Constraint or Unique Index)

The following example shows both a PK on a NONCLUSTERED Index, and an FK referencing an Alternate Key defined on a column allowing NULL values:

USE [tempdb];

-- DROP TABLE dbo.Parent
CREATE TABLE dbo.Parent
(
  [ParentID] INT NOT NULL
             CONSTRAINT [PK_Parent] PRIMARY KEY
             NONCLUSTERED,
  [AltKey]   INT NULL
             CONSTRAINT [UQ_Parent_AltKey] UNIQUE NONCLUSTERED
);

-- DROP TABLE dbo.ChildID
CREATE TABLE dbo.ChildID
(
  [ChildID]  INT NOT NULL
             CONSTRAINT [PK_Child] PRIMARY KEY
             NONCLUSTERED,
  [AltKey]   INT NULL
             CONSTRAINT [FK_Child_Parent_AltKey] FOREIGN KEY
             REFERENCES dbo.Parent ([AltKey])
);


It selects the logical negation of 0 and returns it in the implicitly converted datatype.  Rather than selecting 8 bytes containing all 0's, it selects 8 bytes containing all 1's, returning the maximum possible unsigned value.


Failed to create, join or add replica to availability group in SQL Server 2014

Msg 19405, Level 16, State 17, Line 3 Failed to create, join or add
  replica to availability group 'AGName', because node 'Node3' is a
  possible owner for both replica 'Node3\ReadOnly' and
  'Primary/Primary'. If one replica is failover cluster instance, remove
  the overlapped node from its possible owners and try again.

This happens for two main reasons that I've witnessed.

Reason #1 - The resource/group is set to have ownership on the node in error

Sometimes (for a multitude of reasons) resources and resource groups in windows clustering won't always have the same ownership. The best way to diagnose this error is to first check to see what SQL Server (which calls windows clustering APIs) thinks the cluster nodes are:

SELECT * FROM sys.dm_os_cluster_nodes

Once we know what is in the cluster, check via Powershell to see what the cluster thinks the ownership is for the FCI:

Get-ClusterOwnerNode -Resource "SQLFCIInstanceName"

This will return the nodes that could own the cluster resource. Chances are it'll include the node name of a node that we know shouldn't really be there.

To fix this, run the following powershell command:

Get-ClusterResource -Name "SQLFCIInstanceName" | Set-ClusterOwnerNode -Owners NodeName1,NodeName2

Double check by running the first powershell command to check ownership, then try to add the replica to the AG again.

Reason #2 - Node Names + Language != Node Names

If the language used wasn't US_English there would be a good chance that the node names (when compared to each other) wouldn't necessarily compare properly. This would cause a whole bunch of other issues (and it does) with the cluster outside of the AG.

This can be tested by taking the node names, converting them to upper or lower and the comparing them against themselves. Sounds like it should always work... but some languages have special characters that don't do the UPPER and LOWER conversions well.


Every table should have a PRIMARY KEY.  In MySQL this implies two things: a UNIQUEness constraint, and an index.
Any kind of index can be "composite" ("compound"), that is be composed of more than one column.
It is rarely useful to have two UNIQUE keys in a single table.  (Remember: PRIMARY counts as UNIQUE.
A common exception is when you are 'normalizing' a long string and 'mapping' it to a short INT.
If you already have a unique key (a), it is unreasonable and unnecessary to have also have UNIQUE(a,b) or UNIQUE(b,a).  On the other hand, it may be useful to have a non-unique INDEX(a,b) and/or INDEX(b,a).  Think how the uniqueness of (a) implies the uniqueness of the other two.
With INDEX(a,b), there is virtually no reason to also have INDEX(a)  (Note:  I am not talking about UNIQUE in this bullet item.)

If I have not addressed your specific question (perhaps indirectly), please rephrase your question.


How would you write the FULL OUTER JOIN if it were available.  (I don't see the need for it in your case.)

p.title like '%%' is a costly no-op.  Remove it from the WHERE clause.  (I assume you are building the WHERE on the fly?  If so, make your code a little smarter.)

pl.stageNumber = 60.60 OR
pl.stageNumber < 60.60

-->

pl.stageNumber <= 60.60

ORs are costly because they often make it so the index cannot be used.

Are those stage numbers always dd.dd ?  If so, use DECIMAL(4,2).  DOUBLE has potential rounding errors, an may fail to match when you expect.


Temp tables not being created in SQL Server Azure

To do this you will need to use global temp tables, i.e. ##TempTable.  Additionally, the TransactionOption property will need to be set to Required for the the parent container of all the objects involved.  This will keep all of the SSIS components in the same transaction.  Within the container, the tasks can use use the same Required level or Supported, which will inherit this property from the parent container.  All the components involved can either be placed within a Sequence container with TransactionOption set accordingly to isolate them, or this can be applied at the package level.


How do I connect to a database with a blank password using a shell script?

If root has no password you could just do this:
mysql -u$USER database_name < backup.sql

If you want to code so that root can be given a password later, do this in the script
USER="root"
PASS=""
if [ "${PASS}" == "" ]
then
    PASSWORD=""
else
    PASSWORD="-p${PASS}"
fi
mysql -u${USER} ${PASSWORD} database_name < backup.sql

Give it a Try !!!
CAVEAT
If is usually not a good idea to have root without a password. Please give it one.


Issue after moving the ib_logfile1 and ib_logfile0 files

How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?

Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.

If you start MySQL up again, it will probably zip through the files to the latest Log Sequence Number and realise that there's no more work to be done (someone familiar with the source code that handles the innodb logs would be able to confirm/deny this).

Your steps should be absolutely fine, but I'd take Baron Schwartz's suggestion and move the ib_logfiles somewhere instead of deleting them, just in case it turns out that the server doesn't shut down cleanly for some reason.


I found the normal ULimit setting for mongo db is as follows:

It's not a matter of the number of operations you will be doing, rather the number of connections and (possibly, though it would be unusual for this to play a big part) the number of server side javascript operations you plan to do (Map Reduce, mainly).

To explain: there will be a thread (and a file descriptor) for each connection made to/from the mongod process (similar for mongos) - therefore it is generally a good idea to have both values set beyond the hard coded 20,000 limit in MongoDB.  You can see this if you run htop or something like this command while you spin up new connections to the mongod or mongos processes:

ps uH p <PID_OF_U_PROCESS> | wc -l

Most users will never get anywhere near these maximum levels, so this is merely a precaution on most systems to avoid problems with low ulimits.  In a large cluster with many mongos processes you may see levels approaching this, but unless you are planning that level of deployment you will not have to worry.

For more information on the Map Reduce side of things, there is an excellent article on it, which includes thread use here.


debugging PostgreSQL serialization failures

It'd be good to have, but I don't think there's really much there at the moment. It's particularly tricky because multiple statements are often involved and the culprit(s) aren't always the current running ones, especially on at-commit failures. Collecting the extra info would be ruinous for performance and memory use, but it'd be a great option to be able to turn on when debugging.

Your best bet is probably to greatly increase your log levels and make sure you have a log_line_prefix including %m, %p, %c:%l and %v:%x so you have the transaction and session information required to identify and reassemble what sessions and their xacts did from the logs.


Full text search in PostgreSQL with search term including unrelated hyphen and ending

The problem is that the token 'vein-trailer' gets emitted and is located before the bare token 'vein' is, so is between 'code' and 'vein'.

One way to fix this is to make a new text search configuration which doesn't emit hyphenated words:

create text search configuration simple2 (copy=simple);
alter text search configuration simple2 drop mapping for asciihword;

Then the following returns true:

select
  to_tsvector('simple2','Yakumo Shionome featured in new Code Vein-trailer')
  @@
  phraseto_tsquery('simple2','Code Vein');

Whether this will fix all your problems, or create new problems, is hard to tell based on your description and also hard to intuit, but it does fix the literal problem you presented.  You probably need to do something with non-ascii hwords as well.


Cannot use GRANT ALL anymore on RDS MariaDB 10.5 but can on 10.2

It appears that in 10.3 there was a new privilege introduced, DELETE HISTORY, which means ALL will not work anymore if the user doesn't have it.

It still works. However, in 10.3 there is a new privilege DELETE HISTORY which your sys_dbcreator is missing, so it doesn't have ALL on the database level anymore, and thus cannot grant it.

The only way to do this is to manually specify all the privileges now. I hope I am wrong but this appears to be the case. I suspect this may be a bug in the RDS provisioning and they should add this privilege to the new admin user that is created.
Update: Just chatted with AWS support and they are aware of the issue and agree that the DELETE HISTORY privilege should get GRANT ALL working again. I also asked them to update their provisioning scripts to get this added. They recommended using 10.2 in the meantime.
sources:

https://jira.mariadb.org/browse/MDEV-18884 (GRANT ALL returns Access denied for user with GRANT OPTION)
https://mariadb.com/kb/en/grant/#table-privileges


How to see the value of a variable being used in an sql statement during a server trace?

For debugging purposes you can trace them explicitly into the Profiler using a custom event, via sp_trace_generateevent:

declare @tracedata varbinary(8000);
set @tracedata = cast(@transferdate as varbinary(8000));
exec sp_trace_generateevent 82,
        N'@transferdate',
        @tracedata );
set @tracedata = cast(@oldcust_id as varbinary(8000));
exec sp_trace_generateevent 82,
        N'@oldcust_id',
        @tracedata);

You need to modify your Profiler session to monitor for the user User-Configurable Event Class, otherwise you won't see the generated events. I would also comment out the call to sp_trace_generateevent in production once the debugging is done. The call is not critically expensive (specially if there is no monitoring listening for it), but it should be removed none-the-less.

Updated

The exec call doe snot allow for a CAST to be inlined in the parameter list. I modified the code to show how to cast it before the exec. The value will be in the BinaryData column, in HEX representation (so 'daf' will show up as 0x646166).


How does creating mysql user per webapp user make the webapp less secure?

All you have to do is run SHOW GRANTS; within the app's DB Connections and all grants for the connected user is exposed. That may include seeing host IPs, subnets. Grants for specific tables, views and columns also become visible.

Does the disk I/O increase considerably?

It would definitely do so. Although grants and views are stored in the information_schema database (in RAM), each view would be stringently checked to make sure the underlying tables exist for using the view definition. That would requiring accessing the .frm for every table involved. Any grants on tables or columns are also checked against the underlying .frm files.

Is there a better way to implement row-level-security in MySQL?

This may be a little crazy, but you could probably use triggers

Example
delimiter $$
CREATE TRIGGER check_row_priv BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
 IF (USER()='user1@localhost') && (OLD.is_editable=0)
 THEN
    -- Make Trigger Fail Here
 END IF;
END; $$
delimiter ;

I wrote another post that show how to abort stored procedures and triggers. Other answers in the question may give you insght into aborting triggers or, at least, makikng data immutable.
What are the pros/cons of implementing row-level-security by the above method?

IMHO In a nutshell, it would be the complexity of management.


Exporting Very Large Database from mysql 5.5

There wouldn't be anything broken , mysqlworkbench is a pretty mature software.
You just need to watch the filesystem of the storage where you are going to store the file.
ntfs would be fine, but fat32 will limit you to around 4GB.

However, if you are just looking for an exporter with UI, Navicat Premium is more comfortable in my opinion. Hope it helps.


Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

I have managed to create a test case that at least for MySQL 5.6 demonstrates, for all intents and purposes, that the behaviour is undefined. Consider these 3 examples creating a "diamond"-shape between A, B, C and D.

Example 1

CREATE TABLE `a` (id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Delete from a is successful as expected, as all keys are CASCADE.

Example 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that d now has a RESTRICT to c. I would consider this case ambiguous as it forces you to consider which foreign key "wins". CASCADE or RESTICT, what is picked?

The result is that all rows are deleted.

Example 3

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that the only difference is the change of what foreign key is RESTRICT from d. This example however, fails with

  Error Code: 1451
  Cannot delete or update a parent row: a foreign key constraint fails (hello.d, CONSTRAINT d_ibfk_1 FOREIGN KEY (id) REFERENCES b (id))

While logically, it's the same as Example 2. Without having looked at the source code of MySQL, I strongly suspect that the foreign keys are "applied" in lexical order based on their name. This is also the order the foreign keys are listed by mysqldump.

Without knowing whether what I'm testing here has a defined behaviour or not based on a higher logic, it's hard to know if this demonstrates either a bug, or simply the behaviour in MySQL.

Regardless, as it stands, this example shows that in practical terms I'd say that for all intents and purposes, when having mixed CASCADE and RESTRICT, the behaviour is undefined as it's not in any way sound that you should depend on the names given to foreign keys.


MongoDB update multiple sub documents inside array, each according to a condition

Use the arrayFilters property:

db.getCollection("test").updateOne(
    {
        "wid": 1
    },
    {
        $inc: {
            "items.$[aa].sent": 37,
            "items.$[bb].sent": 58,
            "items.$[cc].sent": 172
        }
    },
    {
        upsert: false,
        arrayFilters: [
            { "aa.nid": 2 },
            { "bb.nid": 3 },
            { "cc.nid": 4 }
        ]
    }
);

For each named identifier you create (aa, bb, cc in the example), should be created a matching document in the arrayFilters array.


Default instance name different than server name

Sounds like someone thought it would be easy to create a "go-to" SQL Server VM that you can clone. Nobody is bothering to change the standard CO-SQLTEMPLATE SQL Server name. If replication is actually working, I would question whether it is working properly because the SQL Server name should match the VM/Physical server name--minus the instance. What's worse, people are probably connecting to SQL Server using the VM/Physical name and don't know about CO-SQLTEMPLATE.

If you're not going to trash and rebuild, you will probably want to rename SQL Server to match the Windows Server name where it's installed:

exec sp_dropserver 'CO-SQLTEMPLATE'
exec sp_addserver 'MyWIndowsServerName'.

I wouldn't trust that SQL Server setup. If you can, I'd check the Availability Groups folder to see which if the replicated database says myDB (Primary) or myDB (Secondary). Then on the server with myDB (Primary) break and rebuild the AlwaysOn replication and rebuild the replication with the AlwaysOn Wizard. (However, I don't add the listener with the wizard as that part seems buggy to me. I add the listener separate).

But before that, if the servers are as messed up as you say, I'd validate that the Windows cluster is even working properly via Failover Manager. And, validate that DNS actually contains the names for your Windows Servers and your Listener (and possibly a witness)--if you have one.

Caution:
Your servers might even be worse than you imagined with users connecting to both instances of CO-SQLTEMPLATE on two different servers as they both "could" represent two completely different sets of data and are completely unrelated. Because, after all, they used the handy-dandy VM template to make their SQL Server life easier.


The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

So each level only has as input the level above not the entire result set accumulated so far.

The above is how it works logically. Physically recursive CTEs are currently always implemented with nested loops and a stack spool in SQL Server. This is described here and here and means that in practice each recursive element is just working with the parent row from the previous level, not the whole level. But the various restrictions on allowable syntax in recursive CTEs mean this approach works.

If you remove the ORDER BY from your query the results are ordered as follows

+---------+
|    N    |
+---------+
|       3 |
|       5 |
|       7 |
|      49 |
|    2401 |
| 5764801 |
|      25 |
|     625 |
|  390625 |
|       9 |
|      81 |
|    6561 |
+---------+

This is because the execution plan operates very similarly to the following C#

using System;
using System.Collections.Generic;
using System.Diagnostics;

public class Program
{
    private static readonly Stack<dynamic> StackSpool = new Stack<dynamic>();

    private static void Main(string[] args)
    {
        //temp table #NUMS
        var nums = new[] { 3, 5, 7 };

        //Anchor member
        foreach (var number in nums)
            AddToStackSpoolAndEmit(number, 0);

        //Recursive part
        ProcessStackSpool();

        Console.WriteLine("Finished");
        Console.ReadLine();
    }

    private static void AddToStackSpoolAndEmit(long number, int recursionLevel)
    {
        StackSpool.Push(new { N = number, RecursionLevel = recursionLevel });
        Console.WriteLine(number);
    }

    private static void ProcessStackSpool()
    {
        //recursion base case
        if (StackSpool.Count == 0)
            return;

        var row = StackSpool.Pop();

        int thisLevel = row.RecursionLevel + 1;
        long thisN = row.N * row.N;

        Debug.Assert(thisLevel <= 100, "max recursion level exceeded");

        if (thisN < 10000000)
            AddToStackSpoolAndEmit(thisN, thisLevel);

        ProcessStackSpool();
    }
}

NB1: As above by the time the first child of anchor member 3 is being processed all information about its siblings, 5 and 7, and their descendants, has already been discarded from the spool and is no longer accessible.

NB2: The C# above has the same overall semantics as the execution plan but the flow in the execution plan is not identical, as there the operators work in a pipelined exection fashion. This is a simplified example to demonstrate the gist of the approach. See the earlier links for more details on the plan itself.

NB3: The stack spool itself is apparently implemented as a non unique clustered index with key column of recursion level and uniqueifiers added as needed (source)


Backup SSRS Encryption Keys Using Powershell

Using WMI to backup RS key might not be so efficient. You may try the below script that I found at http://powershell-tips.blogspot.com/2015/05/backup-report-server-encryption-key.html

#------------------------------------------------------------------------------------------
#     Script : Backup_RSKey.ps1
#     Author : Som DT.
#    Purpose : Backup Report Server Encryption Key with Powershell
#------------------------------------------------------------------------------------------
#     Syntax : Powershell .\Backup_RSKey.ps1 -InstanceName <InstanceName> -PassWord <Password> -BackupLocation <BackupLocation>
#------------------------------------------------------------------------------------------
#    Example : Powershell .\Backup_RSKey.ps1 -InstanceName "MSSQLSERVER" -PassWord "rsdal@2015" -BackupLocation "C:\temp"
#------------------------------------------------------------------------------------------

param (
    [string]$InstanceName="MSSQLSERVER",
    [string]$PassWord="rsdal@2015",
    [string]$BackupLocation="C:\temps"
)

cls
$FileTimestamp=$(get-date).tostring("yyyyMMddHHmmss")

function Backup-RSKey([string]$InstanceName , [string]$PassWord, [string]$BackupLocation)
{
    Trap {
        echo  "Failure : $($Error[0].Exception.Message)"
        return
    }

    $KeyFile="${BackupLocation}\KEY_$InstanceName_$FileTimestamp.snk"

    echo "Backing up to file : $KeyFile"

    $Error.Clear()

    Out-String -InputObject( echo "Y" | RSKeyMgmt.exe -e -f"$KeyFile" -p"$PasWord" -i"$InstanceName")

    if (!(Test-Path "$KeyFile") )
    {
        return "Failure : Error occured while backing up [$KeyFile]."
    }
    else
    {
        return "Success"
    }

}

$Output=Backup-RSKey "$InstanceName"  "$PassWord"   "$BackupLocation"

echo $Output

#--------------------------------------------------------------------------------------------------------------
#-- End of Program
#--------------------------------------------------------------------------------------------------------------


Integrate Full cast and crew in a Movie Database

This looks good to me.

You have correctly defined 3 entities: Movies, Jobs, and People

You have correctly defined 2 associative entities: Cast & Crew

Your entities are independent of each other and the associative entities handle the many-to-many relationship...ie many People can work on many Movies.