What is considered for output from an sql server job step? In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output?

Background: Just executing a statement like UPDATE product SET ... will leave no trace in the job history (no matter whether "Include step output in history" is checked or not). Running the same statement in Query Analyzer shows
"(X row(s) affected)".

By creating a variable and assigning it the @@ROWCOUNT contents after the update I can build and PRINT a message that will include the number of affected rows. This will be included in the job history.
This works fine, but I'd like to know the definition of output (that is written to history) from a Transact-SQL Script job Step. What is included except PRINTed strings?

One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window (formerly known as the Query Analyzer) of SQL Server Management Studio – but that is not the case.

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it is only the PRINT / RAISERROR messages that are included.

Try this test:

Job Step 1

Definition:

PRINT ' ** Line 1 ** ';

SELECT ' ** Line 2 ** ' AS [Line Two];

RAISERROR(' ** Line 3 ** ', 10, 1);

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. ** Line 1 ** [SQLSTATE 01000] (Message 0)  ** Line 3 ** [SQLSTATE 01000] (Message 50000).  The step succeeded.

Job Step 2

Definition:

SELECT ' ** Line B1 ** ' AS [Line B-One];

--PRINT ' ** Line B2 ** '; -- uncomment and output will show this and not "Line B1"

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. Line B-One
  ---------------
  ** Line B1 **
  (1 rows(s) affected).  The step succeeded.

Job Step 3

Definition:

PRINT ' ** Start ** ';

RAISERROR(' ** Test Exception ** ', 16, 1);

PRINT ' ** End ** ';

Output in job history:

  Message
  Executed as user: NT SERVICE\SQLSERVERAGENT. ** Start ** [SQLSTATE 01000] (Message 0)  ** Test Exception ** [SQLSTATE 42000] (Error 50000)  ** End ** [SQLSTATE 01000] (Message 0).  The step failed.

Regarding the following statement in the Question:

  One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window [in SSMS] [for example: "(X row(s) affected)"]

One certainly might think that, but what is shown in the Messages tab of SSMS is not necessarily direct output from SQL Server. The "X rows(s) affected" is being generated by SSMS based on info it received from SQL Server that was not direct output but additional info that comes back in the Tabular Data Stream (TDS). The same applies to batch iteration using "GO x" where the x is an integer telling SSMS how many times to submit that particular batch (the one ending with the "GO" batch separator). The output in the "Messages" tab would show Beginning execution loop and at the end Batch execution completed 4 times., but those are messages from SSMS and not from SQL Server.


How to set up bidirectional replication with Postgresql I have two Linux servers having Postgresql-10 on each of them. On both of them, there is a database, say dbexample, having same schema, table, everything. Both of them are capturing data from an application and storing them on their database (dbexample).Let me tell you since both are capturing data from the same application, there is no mismatch in data at a time.

Now, what is my problem? At a time server 'A' fails due to any reason. That moment it is not able to capture data from the application. But since second is UP and have captured all the data on its database, it must have captured all the data for that time being when server A was DOWN.

Now, When Server A is UP I want all the data to be replicated on this server A from server B for that time, so that I can have all the data same on both the servers.

Considering the same scenario in case of failure of server B, I want the things to work in the opposite direction, i.e. data from Server A to be replicated to Server B.

I have gone through many tutorials and have come-up with below solutions:

To set Logical replication between both servers and make Publisher and Subscriber of each other. (But that is possible one way not bidirectional, if I am not wrong). So please suggest what could be the possible solution.
To arrange a server C and restore the schema only of database, and make Server A publisher and Server C its subscriber. Server C publisher and Server B its subscriber. Server B Publisher and Server A its subscriber. It will be like:-

A will pull data from B,

B will pull data from C, and

C will pull data from A.

Is it feasible when A and B already have data in their databases?

Which approach is better, or is there any other solution?

You don't need bidirectional replication and its additional complexity.

Simply use standard replication, and setup B as a standby for A.

If A fails, let B take over as master, and then reconfigure A as standby for B, possibly using pg_rewind to do this.

Repeat as necessary should either node fail.


Are there any advantages in SQL Server Always On vs. SQL Server 2008 R2 Mirroring with only two nodes? We are currently hosting a SaaS website on two Windows 2008 R2 / SQL Server 2008 R2 Servers. Would upgrading to SQL Server 2012 / Always On give us any advantages with respects to high availability?

Note that while we are looking for high availability, high performance is not really an issue for us at the moment, so being able to run read-only queries on backup database servers is not really important.

Readable Secondaries: you can query the 'mirror'. See AlwaysOn: High-Availability and reads Scale-Out.


Merging MongoDB files Mongo stores data in files named xxx.0 and xxx.ns under the db folder. Is it possible to copy these files to another machine where I have a mongo instance running and merge them to create a single document? Has anyone done this before? Is there a better way to do this?

what exactly are you trying to accomplish? do you want to have all db on another machine?

then you have to use these export and import it

use mongodump to export all you database (you can use mongodump -d your_db) http://docs.mongodb.org/manual/reference/program/mongodump/
use mongorestore to import it http://docs.mongodb.org/v2.2/reference/mongorestore/

you can also use --collection , -c for exporting/importing individual collections


Multiple Merge Replications on same SQL Server Database Conflicts I have 2 separate merge replications on the same database on a server, one named 'POC' with a single subscriber running continuously, and another named 'Clubs' with ~50 subscribers. As of recently, the Club subscriptions have been coming up with errors saying 'The subscription to publication 'POC' has expired or does not exist'. It seems to be confusing the publications, but the sysmergesubscriptions and MSmerge_agents tables still point to the correct publications. Odd one - anyone seen something like this previously?

Check Subscription expiration interval & put the value the same for the 2 publications.


Log Shipping: Bulk Remove Alerts of already deleted primary and secondary databases I'm facing a rather strange issue with Transaction Log Shipping reports in SSMS which is showing multiple red alerts of databases that have been created and deleted while performing multiple Log Shipping scenario between two servers.
I've used the following post:Remove the log shipping configuration on the secondary server, when the primary is no longer available to help clear those alerts but nothing came out of this with success.   Sometimes i'm getting the following error:

  Msg 32011, Level 16, State 1, Procedure
  sp_delete_log_shipping_primary_database, Line 51 Primary Database
  MyFirstDB has active log shipping secondary database(s). Drop the
  secondary database(s) first

Which is not true as the database is already deleted on both primary and secondary server as well as three log shipping jobs.
I have more than forty orphaned databases in this state. this is saturating SQL server log with the below error for all aforementioned databases:

  Date     19/08/2017 08:30:00 Log     SQL Server (Archive #3 - 19/08/2017
  08:31:00)

  Source        spid53

  Message
  The log shipping secondary database SQLTEST.MyFirstDB has
  restore threshold of 30 minutes and is out of sync. No restore was
  performed for 449177 minutes. Restored latency is 2 minutes. Check agent

Can you help please, clear these entries from wherever system database/tables holds these records.?
I really appreciate the help!

Information about databases involved in log shipping are contained in several system tables in the msdb database.  Looking at these tables will help identify whether you've properly reconfigured log shipping.

This first query shows databases that are configured as primaries, where the database no longer exists on the SQL Server instance:

SELECT lspd.primary_database
    , lspd.last_backup_date
FROM msdb.dbo.log_shipping_primary_databases lspd
WHERE NOT EXISTS (
    SELECT 1
    FROM master.sys.databases d
    WHERE d.name = lspd.primary_database
    );

This query will show secondaries for the databases identified above:

SELECT lsps.secondary_server
    , lsps.secondary_database
FROM msdb.dbo.log_shipping_primary_secondaries lsps
    INNER JOIN (
        SELECT lspd.primary_id
        FROM msdb.dbo.log_shipping_primary_databases lspd
        WHERE NOT EXISTS (
            SELECT 1
            FROM master.sys.databases d
            WHERE d.name = lspd.primary_database
            )
        ) p ON p.primary_id = lsps.primary_id;

Use sys.sp_delete_log_shipping_primary_secondary to remove primary databases from the log shipping configuration.

Use sys.sp_delete_log_shipping_secondary_database to remove affected secondaries, if desired, which should stop the alerts you're seeing.

The Microsoft Docs page on monitoring log shipping shows several useful details, including the following tables that are used by the monitor process:

log_shipping_monitor_alert - Stores alert job ID.
log_shipping_monitor_error_detail - Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.
log_shipping_monitor_history_detail - Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.
log_shipping_monitor_primary - Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
log_shipping_monitor_secondary - Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Querying the contents of those tables should help you narrow down the source of the alerts.  The page I mentioned above also contains details about several monitoring stored procedures you can use to query the tables.

The "Transaction Log Shipping Status" report calls the sp_help_log_shipping_monitor stored procedure to get its details - looking at the T-SQL definition of that stored proc may help you resolve where the issue is.


Missing files from mysql odbc driver installer I'm trying to install the latest (5.2.7) version of the MySQL ODBC driver on Windows server 2008 R2, but when starting the installation, I get the following error:

MySQL Workbench installs fine and works well. I just need to connect to MySQL from my windows desktop application to import some data.

Note. I've installed Microsoft Visual C++ redistributable and rebooted, the error remains.

Old, but for those searching, as me recently ;) Part of documentation section 4.1:

  Make sure the Visual C++ Redistributable for Visual Studio is installed.
  • Connector/ODBC 8.0.14 or higher: VC++ Runtime 2015 or VC++ Runtime 2017
  • Connector/ODBC 8.0.11 to 8.0.13: VC++ Runtime 2015
  • Connector/ODBC 5.3: VC++ Runtime 2013
  Use the version of the package that matches the system type of your Connector/ODBC driver: use the
  64-bit version (marked by “x64” in the package's title and filename) if you are running a 64-bit driver, and
  use the 32-bit version (marked by “x86” in the package's title and filename) if you are running a 32-bit
  driver.


Cannot rejoin or add instance to MySQL InnoDB cluster I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster after I restored a backup in a test database on the primary server and then deleted the backup before it was replicated to the other two secondary nodes. Now, when I want to rejoin or add those secondary databases back to the cluster, they cannot be added. Below are the screenshots of the error messages I get.
When I want to rejoinInstance(), I get the below error:
MySQL  pdb1-mod:3306 ssl  JS > cluster.rejoinInstance('root@ddb-mod')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Rejoining instance to the cluster ...

Please provide the password for 'root@ddb-mod:3306': ************
Cluster.rejoinInstance: The instance 'ddb-mod:3306' does not belong to the ReplicaSet: 'default'.
(RuntimeError)

When I want to add it through the AddInstance() method, I get the following error:
 MySQL  pdb1-mod:3306 ssl  JS > cluster.addInstance('root@ddb-mod')
 Please provide the password for 'root@ddb-mod': ************
 The safest and most convenient way to provision a new instance is through
 automatic clone provisioning, which will completely overwrite the state of
 'ddb-mod:3306' with a physical snapshot from an existing cluster member. To use
 this method by default, set the 'recoveryMethod' option to 'clone'.

 The incremental distributed state recovery may be safely used if you are sure
 all updates ever executed in the cluster were done with GTIDs enabled, there
 are no purged transactions and the new instance contains the same GTID set as
 the cluster or a subset of it. To use this method by default, set the
 'recoveryMethod' option to 'incremental'.

 Incremental distributed state recovery was selected because it seems to be safely usable.
 Validating instance at ddb-mod:3306...

 This instance reports its own address as ddb-mod:3306

 Instance configuration is suitable.
 A new instance will be added to the InnoDB cluster. Depending on the amount of
 data on the cluster this might take from a few seconds to several hours.

 Adding instance to the cluster...

 WARNING: User 'mysql_innodb_cluster_622884765'@'%' already existed at instance 'pdb1-mod:3306'. It
 will be deleted and created again with a new password.
 ERROR: Unable to start Group Replication for instance 'ddb-mod:3306'. Please check the MySQL server
 error log for more information.
 Cluster.addInstance: Group Replication failed to start: MySQL Error 3092 (HY000): The server is not
 configured properly to be an active member of the group. Please see more details on error log.
 (RuntimeError)

I googled it many times but did not get enough help from there.
Any help is appreciated in advance. Thank you.

This has been fixed in MySQL Shell 8.0.23: https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/news-8-0-23.html
An instance that belongs to an InnoDB Cluster is identified by its server UUID. If the UUID changed after the instance had left the cluster, for example because you used MySQL Enterprise Backup to restore from a backup, then the instance could not be rejoined to the cluster. Now, if the cluster encounters this situation, it checks the metadata to see if the instance can be identified using its host and port. If found, the metadata is updated based on the options used for the rejoin operation. This check is executed during the Cluster.rejoinInstance() and Cluster.rescan() operations.

Additionally, a check is executed to verify the serverId of all the instances is registered in the metadata as an instance attribute. If it is not, the metadata is updated accordingly. This check is executed on add, rejoin and rescan operations. (Bug #26649039)

Please upgrade MySQL Shell to 8.0.23.


How to allow user access to non-owned objects in Postgres? I am in the process of creating a pre-prod Postgres database, and want to ensure the accessing user has limited access.

I want to setup tables in such a way that the accessing user can't CREATE, DROP or ALTER objects. Currently the only way I can see it to change the ownership to another user, such as postgres and then somehow grant only the permissions needed:

 ALTER TABLE mytable SET OWNER TO postgres;
 REVOKE connect ON DATABASE mydatabase FROM PUBLIC;
 GRANT USAGE, SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO dbuser;
 GRANT connect ON DATABASE mydatabase TO dbuser;

The issue is when I do the above I am unable to do a simple SELECT when I connect with the user the grant was applied to. For example:

 select * from mytable;

Yields:

ERROR:  permission denied for relation mytable

How to get this right?
Additionally, should I use a named schema other than public?

Read the manual on GRANT and ALTER TABLE.
There is no USAGE privilege for tables. You would see an error message if you tried your GRANT command.
You need permissions on the schema, too. Here USAGE is right. But you certainly want to revoke CREATE.
Ownership for sequences that are owned by serial columns is changed to the new table owner automatically. But any other sequences you'll need to reown manually. Or you go a more aggressive route with REASSIGN OWNED.
You may need permissions on sequences, too. (If you grant INSERT or want them to use currval() or nextval() functions). Those are currently (v9.6) separate from table permissions.
I strongly suggest to bundle permissions in a group role and grant / revoke membership in that role to selected user roles.

Something along these lines, as superuser:

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;                      -- you may or may not want this
-- REVOKE ALL ON ALL TABLES    IN SCHEMA public FROM PUBLIC;  -- only for more existing permissions
-- REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;  -- only for more existing permissions

ALTER TABLE mytable OWNER TO postgres;    -- or some admin role. best keep the superuser out of this
-- more tables? or even:
-- REASSIGN OWNED BY dbuser TO postgres;  -- to reassign *all* owned objects the current DB

CREATE ROLE usr_permissions;
GRANT usr_permissions TO dbuser;

GRANT CONNECT        ON DATABASE mydatabase            TO usr_permissions;
GRANT USAGE          ON SCHEMA public                  TO usr_permissions;  -- if you revoked from PUBLIC
GRANT SELECT, UPDATE ON ALL TABLES    IN SCHEMA public TO usr_permissions;  -- DELETE, INSERT?
GRANT USAGE          ON ALL SEQUENCES IN SCHEMA public TO usr_permissions;  -- if you also granted INSERT

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
Grant access to all tables of a database

  Additionally, should I use a named schema other than public?

The schema public is in no way special. It only happens to be installed by default with permissions for PUBLIC and preset in the default search_path. Some DB admins even delete it. You can use it like any other schema. Depends on the complete situation. Related:

Is it recommended to install extensions into pg_catalog schema?


pessimistic locking and client death We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock not getting released ever).

The most common concern that people have is obviously deadlocks, however we have another concern: what if the client dies, or looses connection to the server, while holding a SELECT ... FOR UPDATE lock?

In many cases it would not be relevant because if the client dies the application dies, however in this case we will have a cluster of servers with failover, so we wouldn't want other nodes in the cluster to be overly affected if one node dies.

On this topic I have found this 2003 message, concerning postgreSQL:
http://www.postgresql.org/message-id/3E81D7F0.2010604@xythos.com

We have also tested the tcp_keepalives_idle postgreSQL configuration parameter, and confirmed that if we leave it low enough and kill the client, the lock gets released. However if we didn't touch that parameter, the lock did not get released (but maybe we didn't wait long enough).
And that's without considering the concern raised by this 2003 message, about the difference between the lifecycle of the client application and the life-cycle of the connection pool (we'll keep the default on this but we expect each node to have its own connection pool, no sharing across the cluster).

The problem gets even more complicated because we intend to support several database servers (at least Microsoft SQL Server and PostgreSQL, possibly Oracle).

Our solution would use JavaEE and Glassfish as an application server.

So to summarize the question: Is it safe to assume that apart from deadlocks, it is not possible that a lock stays held forever, the lock will be released after a reasonably short time (which range of duration?), even in case of a sudden death or loss of connectivity to a client holding a lock?

No, I don't think it's safe to assume locks from dead/vanished clients are released in a bounded and deterministic amount of time with all DBMSes and drivers. You'll need to investigate each configuration separately.

In the case of PostgreSQL you're generally but not always OK if you have TCP keepalives set quite aggressively, because:

If the whole client application process dies but the client host stays up the host's kernel will RST the TCP connection as part of process cleanup;
If the client host dies entirely then it'll stop responding to tcp keepalives; and
If the client host remains alive but the network fails in one or both directions between client and server then it'll stop responding to tcp keepalives.

However, there are a few cases that will not be handled:

Connection pool bugs that result in a connection being returned to the pool with a transaction still open and holding locks;
Connection pools that don't DISCARD ALL and thus fail to release and reset session-level resources like advisory locks (if you use them);
App server based applications that 'leak' connections with open transactions so the connection pool can never reclaim them;
Badly written programs that intentionally hold a transaction open during user "think time" like a dialog box or data entry window, where the user might go away and make a coffee ... or go on holiday for a month;
Cases where the application process remains in existence but is totally non-responsive due to being SIGSTOPped, having been paused by a debugger, hitting an internal threading deadlock, etc. The OS will keep on responding to tcp keepalives but the app won't respond to Pg protocol messages or advance its work.

In the case of PostgreSQL you can use active lock monitoring to scan for and terminate long running transactions that haven't done anything in a while. In particular, you can deal with <IDLE> in transaction sessions by scanning pg_stat_activity (though it's only possible to do this RELIABLY and EASILY in 9.2). With a bit more effort you can use pg_locks to watch for queries blocked on a lock for more than x seconds and kill the session holding the lock, though this can make it hard to run some DDL like index creation.

What you really need is application level keepalives, where the app says "Yup, I'm alive and responsive". These are rather harder to implement, though.

One thing that will help is that both PgBouncer and PgPool-II (external connection pools for PostgreSQL) support controls for session and transaction timeouts. We've wanted to implement similar options in the core PostgreSQL for some time, but nobody's come up with a design that's robust enough to handle all the corner cases, so for now your best bet is to use an external pooler. You can do this even if you're also using an application-level connection pool.

On the good news front, PostgreSQL automatically detects and breaks deadlocks between transactions, so one thing you don't have to worry about much is deadlocks at the SQL level when using PostgreSQL.


Can I select the rows that have the greatest Column A values compared to all other rows which have identical columns B in MySQL? Perhaps not the clearest explanation.
I have this table:
+-----+------+
| ID  | Time |
+-----+------+
|  1  |  5   |
|  1  |  2   |
|  1  |  4   |
|  2  |  1   |
|  2  |  5   |
|  3  |  6   |
+-----+------+

I want the row with ID=1 that has the greatest Time compared to other rows where ID=1.  The same with WHERE ID=2, etc.  So essentially "get me all the rows that have greatest Time as compared to other rows that have the same ID as them".  So the results in this case would be:
+-----+------+
| ID  | Time |
+-----+------+
|  1  |  5   |
|  2  |  5   |
|  3  |  6   |
+-----+------+

SELECT id, MAX(`time`) AS `time`
FROM sourcetable
GROUP BY id;

PS. Stop thinking in iterations, think in datasets.


Confused about the Oracle CONTROL_FILES initialization parameter This is the explanation of this parameter from Oracle. This is the description of the description in the explanation. From the documentation we know that this parameter is not modifiable (Modifiable: No). But in fact I can modify it:

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';
alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope = spfile;

System altered.

sys@ORCL>

So, what does it mean by Modifiable: No in the documentation?

BTW, this is my Oracle version:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>

Thanks,

When you see Modifiable you should read MEMORY Modifiable.  For ALTER SYSTEM then this would mean can the parameter be modified in memory for the system.  The Database Reference document you site includes part of this information:

  Modifiable Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all
  sessions in the current instance (by an ALTER SYSTEM statement):

  ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

To understand what will happen in entirety you have to understand the SCOPE parameter and what happens when no SCOPE is specified.  From the SQL Language Reference we can see that the ALTER SYSTEM statement can have three different SCOPE specifiers or none at all.

  SCOPE The SCOPE clause lets you specify when the change takes effect.
  Scope depends on whether you started up the database using a
  traditional plain-text parameter file (pfile) or server parameter file
  (spfile).

  MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you
  started up the database using a parameter file (pfile), then this is
  the only scope you can specify.

  SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down
  and started up again. You must specify SPFILE when changing the value
  of a static parameter that is described as not modifiable in Oracle
  Database Reference.

  BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists
  after the database is shut down and started up again.

  If a server parameter file was used to start up the database, then
  BOTH is the default. If a parameter file was used to start up the
  database, then MEMORY is the default, as well as the only scope you
  can specify.

So, since you were able to specify a scope of SPFILE, we can infer that you are using an SPFILE, so the default for the first ALTER SYSTEM when you did not specify a scope was BOTH which would fail because the parameter is not MEMORY modifiable.  You can prove this by repeating your first ALTER SYSTEM and adding SCOPE=BOTH or SCOPE=MEMORY both of which will also fail.


Security implications of backing up to UNC path I have a few servers which backup to a Data Domain. This is accessible via a UNC path, i.e. \\datadomain\SqlBackups\ and all that seems to be done is we give the SQL Server access to the data server via Firewall rules, and we give SQL DB Service Account full access to the SqlBackups folder.

The server is configured to map the Y:\ drive to this location so backups go to Y:\<InstanceName>\<DatabaseName>.

This works until something happens with the drive mapping. For example, sometimes on reboot the mapping is lost so backups fail.

I just want to backup to the UNC path which I've tested and it works without fault. However I'm told there are security implications for this, something about making the UNC path visible to potential hackers... which doesn't make sense to me as you can just easily find out where a mapped drive points to.

Can anybody shed any light on this from a SQL Server / Windows Server Security point of view? What's so bad about backing up the UNC path?

We have a lot of SQL Server VMs and we've found it best to backup database to a centralized location via a Windows share and keeping our VMs slim. However, we've been backing up to the administrative Windows $ shares--which I have yet to see any administrator turn off -- though it can be done.

As for your question: usually you map a drive only for convenience sake because you want to or provides and expected benefit, but you still have to map it to a UNC name. The network packets to and from the mapped drive are still going to contain UNC information. That information doesn't get obfuscated because you mapped it to a drive letter. Actually systems that have mapped drives have been more vulnerable to trojans like Cryptolocker!

If there are legitimate data hacking concerns, just turn on backup encryption--knowing it will add some CPU cycles to the mix(and the new task of acquiring, managing, and maintaining certificates). Our network is fairly secure so we are not currently encrypting our backups--but that day is probably coming sooner rather than later. Additionally, it sounds like the questions need to be re-directed to the owners and creators of the data domain, since that's where the share resides and most of the risk exists (not to mention the UNC share name).

If the powers that be are wondering about a UNC hack, I'd have start asking questions like:

1.> "is our network in danger of being hacked or have we been hacked already? What's your confidence level for this scenario?"

2.> "are server backups currently being encrypted?"

3.> "have Windows admin/$ UNC shares turned off?"

4.> "are certificates readily available for encryption -- should we go that route?"

5.> "do we even have a  corporate certificate policy we can rely on in order to acquire certificates from a reliable source?"

6.> and my personal favorite, "do I need to air gap my SQL Server?"


Loop through tables or copy paste I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA to convert these tables in SQL Server.

It is part of a migration through SSIS. I have to merge all these tables in one table without duplicates. A portion of the treatment is the same for all the tables but there are some subtleties. One problem is that the column names have slight differences (like X_Y instead of X). And each table have specific columns but I do not want to retrieve the data in those.

I hope it is not too broad a question, but:

Is it better to try to do a generic part, taking the table name as a variable to apply the same treatment with conditions in it to take care of the differences?
Or should I just copy & paste a core/template data flow and manage each difference in separate packages?

Given that:

There are some columns you don't want to pull through for each table
Column names are different for each table
You only have 40 tables
This is a one-time migration (this is safe to assume, correct?)

Then I would just copy and paste the core dataflow and make per-table changes as appropriate. For a non-repeating process that is a small amount of tables (not 200 or more), you don't want to spend too much time creating a repeatable process that will never be repeated ;-). And you are going to have to manage the differences anyway, so you might as well just put your effort into that part.

And it should be possible in SSIS to define a reusable component (script task) to handle some of the "common" manipulations. If not, just create a stored procedure that accepts a table name and handle the common steps there. Then, in SSIS, just call that Stored Procedure for each table and that way you will have a single definition for the common stuff, and if you need to make a slight change in the common changes, then you will only need to make them in one spot :).

(please see UPDATE section below) Another option for handling data migration when you have issues of not pulling all fields over and possibly renaming fields is to use the SqlBulkCopy Class in .NET as it handles those things quite nicely. You can either create a C# Task in SSIS to use this class, or you can use a SQLCLR stored procedure such as DB_BulkCopy which is part of the SQL# SQLCLR library (which I am the author of, but this stored procedure is in the Free version). I describe this in  a little more detail in the following answer:

Import data from one Database to another script

UPDATE

The following requirement was added a day after I posted the suggestions above:

  I have to merge all these tables in one table without duplicates.

In light of this new requirement, I think that using the SqlBulkCopy alternate option is less attractive than sticking with SSIS in the manner that I described first. The only way to de-dupe the data using SqlBulkCopy / DB_BulkCopy would be to run the process from the destination / target server, load the data into a temp table (it would have to be a global temporary table -- meaning, starting with ##) from the source server using SqlBulkCopy or DB_BulkCopy, and then insert into the real destination table from the global temporary table while filtering out the "duplicates" based on whatever rules you have to determine what a duplicate is.


Sync logins and Agent jobs across all replicas in Distributed Availability Group I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on..
Is there any way to sync all logins and permissions from global primary to forwarder and other replicas?
Also, how to sync SQL agent jobs using any automated way?

For Database level permissions:
Maintain SID of the Login same across all replicas (SQL Instances). I think, you don't have to even maintain Login sync jo. Instead, create login using Registered Servers using following steps, as long as the SID remain same, the database permissions assigned in Primary replica would automatically synchronized into all secondary (including forwarder) replicas.
Step1: Create login in DEV SQL Instance
Step2: Get the SID of Login (from DEV)
Step3: Create login in Production Instances using Registered Servers
Create Login MyLogin with password = 'xxxx', SID=0x000000000000;

Step4: Assign database level permissions for the login (Only in primary replica)
For SQL Agent Jobs:
You can use anyone of approach:

Script-out and execute via Registered servers.
If it feasible in your environment, you can configure multi-server administration feature.


Query must be a SELECT I try to do a select union with order by but it seem like order by can not use union.

I receive following message

  Query must be a SELECT

This is my code

(SELECT
  `tabsales_action`.responsible as "Responsible:data:200",
  `tabsales_action`.action_type as "Action Type:data:200",
  count(*) as "Nb:Int:60"
FROM
  `tabsales_action`
WHERE
  `tabsales_action`.workflow_state = 'To do'
GROUP BY
  `tabsales_action`.responsible,
  `tabsales_action`.action_type
ORDER BY
  `tabsales_action`.responsible )
UNION (SELECT `tabmfi_loan_action`.responsible,
`tabmfi_loan_action`.action_type FROM  `tabmfi_loan_action`
WHERE
  `tabmfi_loan_action`.workflow_state = 'To do'
GROUP BY
  `tabmfi_loan_action`.responsible,
  `tabmfi_loan_action`.action_type
ORDER BY
  `tabmfi_loan_action`.responsible)
UNION (SELECT `tabin_house_credit_action`.responsible,
`tabin_house_credit_action`.action_type FROM `tabin_house_credit_action`
WHERE
  `tabin_house_credit_action`.workflow_state = 'To do'
GROUP BY
  `tabin_house_credit_action`.responsible,
  `tabin_house_credit_action`.action_type
ORDER BY
  `tabin_house_credit_action`.responsible)
UNION (SELECT `tabservices_planning`.responsible,
`tabservices_planning`.action_type FROM `tabservices_planning`
WHERE
  `tabservices_planning`.workflow_state = 'To do'
GROUP BY
  `tabservices_planning`.responsible,
  `tabservices_planning`.action_type
ORDER BY
  `tabservices_planning`.responsible)
UNION (SELECT `accounting_action`.responsible,
`accounting_action`.action_type FROM `accounting_action`
WHERE
  `accounting_action`.workflow_state = 'To do'
GROUP BY
  `accounting_action`.responsible,
  `accounting_action`.action_type
ORDER BY
  `accounting_action`.responsible)

What can I do?

You've got a few issues here.

When using the UNION or UNION ALL operator, each query must return the same number of columns. The first query in your list has three columns in the SELECT but the queries that come later only have two. You can create a static value for these if you need to, something like this (using the second query as an example);

 SELECT
`tabmfi_loan_action`.responsible,
`tabmfi_loan_action`.action_type,
'' --Use a zero length string
FROM  `tabmfi_loan_action`

You cannot order your queries that you use with the UNION operator, you'll have to take your ORDER BY statements out. If you do need to order the result then you will have to use your current statement (all of it) as a subquery and ORDER in an outer SELECT. Something like this

SELECT
a.Field1
,a.Field2
FROM
    (
        SELECT Field1, Field2 FROM Table1
        UNION
        SELECT Field1, Field2 FROM Table2
    ) a
ORDER BY a.Field1

Try fixing these first and see if this resolves your issues.


How to implement an effective ACL/auth in a relational db? We are looking at implementing an ACL that fulfills the following requirements:

Most users have access to an item (access to the item by default), but a few is denied access.
Most users do not have access to an item (no access to the item by default), but a few is granted access.
A user only has access to a few items.
Default is that all users have access to an items.

We are talking about hundreds of thousands of items and tens of thousands of users. They are all stored in a relational DB. We think that having a many-to-many relation between items and users and defining the access in this relation would be ineffective, but are open for comments about this. We also think that access groups are irrelevant here, but we might be wrong.

What are your thoughts on how to do this?

Your many-many table is a good idea. In this table, the existence of a row can mean "no access" or "has access" depending on your need.

I'd aim to store least data and make "no row" mean the most common state by implication.

Looking at your requirement 2, I'd have it so that "row exists = has access". However, the opposite can be be read from item 1. Ditto for 3 and 4. So you need to explain this more

I'd also look at grouping users. Do you really permission users individually? Ditto for items: can thes be grouped somehow?


MySQL Innodb_row_lock_current_waits is 2^64 - 1 I have a MySQL 5.6 server which we monitor using Nagios.

Over the weekend, it's started complaining about the Innodb_row_lock_current_waits, which currently has a value of 18446744073709551615, yes, that's more than 10^19! As I understand it, this value is the number of current row locks. Basically, I don't believe it, that number is far too large?

As Phil pointed out, the number is 2^64 - 1, so now my question is: Why is MySQL returning this number, it must be an overflow/error code of some sort.

There is a open bug report on this for MySQL 5.6.14

Bug #71520 Constantly increasing Innodb_row_lock_current_waits value

From the bug report, note this entry

  [25 Jun 2015 6:58] Zhenye Xie in my environment, I got a strange
  value. (-1 as uint64)

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';

+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
+-------------------------------+----------------------+
1 row in set (0.06 sec)

  in lock0wait.cc, srv_stats.n_lock_wait_current_count.inc() srv_stats.n_lock_wait_current_count.dec(); is not thread safe.
  srv_stats is a global variable, but it has just a lock on trx
  surrounds changing the value.

You were right when you said it must be an overflow/error code of some sort

As recent as Jun 14 of this year, this issue may or may not exist in MySQL 5.7.11

  [14 Jun 9:55] Ángel OR Good morning,

  I am seeing this in MySQL 5.7.11-log:

mysql> show status like '%Innodb_row_lock_current%';

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 45    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.00 sec)


Return All Data From Left Table My left table holds more store names than the right table.  I thought a quick left join would have that dataset returned, but to no avail!

Data In From clause has the most options and I want to show all of them
Data In Left Join clause has some but not all of the options I want to show

I created the CTE to get a SUM() and pull the data into the main query
If I use the CTE to display the storeName I get 15 returned that are null instead
of the storeName(s) that exist in the From table that do not exist in the Left Join
Table

Here is query I use that is not giving results I am after.

;With DataFromOne As
(
    Select   StoreName
             ,DataFromOne = Count(ramalad)
    FROM     DataFromOne
    WHERE    EntryDate BETWEEN '20170101' AND '20170331'
    GROUP BY StoreName
)
Select   ec.StoreName
         ,[Emp Count] = COUNT(Employee)
         ,[PT Count] = COALESCE(ec.DataFromOne,0)
FROM     DataFromOne ec
LEFT OUTER JOIN DataFromTwo fji
ON       ec.StoreName = fji.StoreName
WHERE    fji.saleDate BETWEEN '20170101' AND '20170331'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName ASC;

If you add a filter on the right table, you effectively turn your outer join into an inner join.

Move the date filter from WHERE to ON.

Also I suggest an open-ended range rather than BETWEEN.

What do BETWEEN and the devil have in common?

Finally, I suggest not naming your CTE the same name as the base table (this is confusing), using proper schema references, and end with a semi-colon.

Bad habits : avoiding the schema prefix
Ladies and Gentlemen, Start Your Semi-Colons

Here's a start:

;With ec As
(
    Select
      StoreName
      ,DataFromOne = Count(ramalad)
    FROM dbo.DataFromOne
    WHERE EntryDate >= '20170101'
       AND EntryDate < '20170401'
   GROUP BY StoreName
)
Select
  ec.StoreName
  ,[Emp Count] = COUNT(fji.Employee)
  ,[PT Count] =      COALESCE(ec.DataFromOne,0)
FROM ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName;

In fact, you can simplify the query logic quite a bit (though I am not sure exactly how that will change your plan):

SELECT
  ec.StoreName,
  [Emp Count] = COUNT(fji.Employee),
  [PT Count]  = COUNT(ec.ramalad)
FROM dbo.DataFromOne AS ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
WHERE ec.EntryDate >=  '20170101'
  AND ec.EntryDate <   '20170401'
GROUP BY ec.StoreName
ORDER BY ec.StoreName;


Is there a way to do the following query with joins instead of a sub query? I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view.

The only thing similar to my problem is the greatest-n-per-group queries but I want all the values, instead of only the last measured factor ones.

This is my query:

SELECT
      eu.[Name],
      pp.[MonthDay] --date type,
      pp.[Value] *
            (SELECT TOP 1 [Factor] FROM [em].[CO] co
            WHERE co.[EUID] = eu.[Name]
            AND co.[MonthDay] <= pp.MonthDay
            ORDER BY [MonthDay] DESC) AS [CO]
            --multiply by the last measured factor
            --I also want to remove the sub query
FROM em.[PPGenData] pp
JOIN em.[PPEUID] eu
ON eu.ID = pp.ID

this just trying to figure out your syntax:

    SELECT eu.[Name],
           pp.[MonthDay] --date type,
           pp.[Value] *  ( SELECT TOP 1 [Factor]
                             FROM [em].[CO] co
                            WHERE co.[EUID] = eu.[Name]
                              AND co.[MonthDay] <= pp.MonthDay
                            ORDER BY [MonthDay] DESC
                         ) AS [CO]
    FROM em.[PPGenData] pp
    JOIN em.[PPEUID] eu
             ON eu.ID = pp.ID

I think this will work:

select *
(
    SELECT eu.[Name],
           pp.[MonthDay] --date type,
           pp.[Value] * co.[Factor] as [ValueTimesFactor],
           row_number over(partition by co.[EUID] order by co.[MonthDay] DESC) as rn
      FROM em.[PPGenData] pp
      JOIN em.[PPEUID] eu
               ON eu.ID = pp.ID
      JOIN em.[CO] co
               on co.[EUID] = eu.[Name]
              AND co.[MonthDay] <= pp.MonthDay
)     tt
where tt.rn = 1

or

SELECT eu.[Name], pp.[MonthDay], pp.[Value],
       pp.[Value] * max(co.[Factor]) as [ValueTimesFactor],
  FROM em.[PPGenData] pp
  JOIN em.[PPEUID] eu
           ON eu.ID = pp.ID
  JOIN em.[CO] co
           on co.[EUID] = eu.[Name]
          AND co.[MonthDay] <= pp.MonthDay
 GROUP BY eu.[Name], pp.[MonthDay], pp.[Value]


Exclude procedure call from transaction rollback How can I exclude a statement in a transaction rollback?

Begin
Insert into table a
...
...
call proc_1()
...
...
Rollback

How can I rollback all the commands except/prevent proc_1() from rolling back?

You can make the procedure have its on transaction - called an autonomous transaction, but be careful with this one, you can easily get yourself into deadlocks if you use it carelessly without knowing your transactions.

create or replace procedure p1 (...) as
  pragma autonomous transaction
begin
  ...
end;

Usually this is used for logging purposes.


The target principal name is incorrect. Cannot generate SSPI context When attempting to log on to a SQL Server that lives on a different domain, I get the following error:

  The target principal name is incorrect.  Cannot generate SSPI context.

I am using Windows Credential Manager to enable Windows Authentication across domains.

My problem was that when setting up the credentials for the remote server in windows credential manager, I had not specified the domain alongside the username.

I had

sql.domain.name:1433
username
pwd

the correct version was:

sql.domain.name:1433
domain.name\username
pwd

So, specifying the fully qualified domain name in the username field of credential manager resolved the issue.


Transaction Log Shipping doesn't use the latest transaction log I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2.  The problem is that when the logs are being restored on the secondary server, it always uses the second-most-recent log file rather than the most recent one available.  I suspect this is because the restore happens at the same time the backup occurs on the primary server.  I thought I could use the "delay" option to get around this, but it doesn't seem to do the trick.  What am I missing?

Converting my comment to answer:

can change the restore frequency to be more frequent on secondary server, but then it wont keep up with the 15 min frequency of backups on primary server. Logshipping will pick up the logs (.trn and not the .wrk files) to restore


Decryption not working when SQL Server database copied to new server I am copying a database from an old server to a new server. The database has an encrypted column. From my searches I see that I need to backup the master key from the old server and restore it in order to be able to retrieve previously encrypted data, however, I must be doing something wrong.

Please see the process I am following. Can anyone please explain why it isn't working? When the encrypted column is decrypted, the values shows NULL, which is not right.

-- OLD SERVER ---------------------------------------------------------------
BACKUP MASTER KEY TO FILE = 'D:\MasterKey' ENCRYPTION BY PASSWORD = 'MyPassword1'
GO

-- Copied file to new server

-- NEW SERVER ---------------------------------------------------------------
RESTORE MASTER KEY FROM FILE = 'D:\MasterKey'
    DECRYPTION BY PASSWORD = 'MyPassword1'
    ENCRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
GO
-- Command(s) completed successfully.

select * from sys.symmetric_keys
-- (1 row(s) affected)
-- name principal_id    symmetric_key_id    key_length  key_algorithm   algorithm_desc  create_date modify_date
-- ##MS_DatabaseMasterKey## 1   101 128 D3  TRIPLE_DES  2013-01-17 09:37:05.873 2013-01-17 09:37:05.873

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
-- Command(s) completed successfully.

CREATE CERTIFICATE MyDBNameCertificate01 WITH SUBJECT = 'MyDBName Certificate'
GO
-- Command(s) completed successfully.

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyDBNameCertificate01
GO
-- Command(s) completed successfully.

select * from sys.certificates
-- (1 row(s) affected)
-- name certificate_id  principal_id    pvt_key_encryption_type pvt_key_encryption_type_desc    is_active_for_begin_dialog  issuer_name
-- MyDBNameCertificate01    268 1   MK  ENCRYPTED_BY_MASTER_KEY 1   MyDBName Certificate

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyDBNameCertificate01
select
    name,
    Password,
    decryptbykey(Password) as [DecryptedPass]
FROM
    [tbl_Users]
CLOSE SYMMETRIC KEY SSN_Key_01
--name  Password    DecryptedPass
--David 0x00CF72EF69A517......34EBC1EDB4F1D0    NULL

The NULL value above should be the original value from the old database, so I assume the encryption objects have not been installed correctly...

The sequence of steps is entirely depended on you current (old db) encryption hierarchy. There is no cookie-cutter recipe that can be applied w/o knowing your encryption hierarchy, which you did not present. It cannot be deduce from the code you posted, steps like creating a new certificate after the database copy is attached make not much sense. Normally after you copy the file and attach it to the new server all you have to do is open the master key and add the server master key encryption:

USE [MyDatabase];
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

Assuming a typical data->symmetric key->certificate->database master key->service master key encryption hierarchy the steps above are sufficient.


How to free postgres space after a DELETE without VACUUM Is there anyway of freeing up disk space after a major delete without using VACUUM?

When I was learning about postgres I created a very large (unpartitioned) table that has since swollen to nearly 1 TByte.

To rectify this I decided I needed to partition the table by month.

I created a table for one months data and populated it with this....

WITH archived_rows AS
( DELETE FROM tabArc WHERE fixTime >= DATE '2018-02-01' AND fixTime <
DATE '2018-03-01' RETURNING * )
INSERT INTO tabArc_201802 SELECT * FROM archived_rows;

This worked fine, the new table has a months worth of data, the old table has a months less. The problem is that the disk it was on has now swollen to about 90% full, so there no room to do a similar process with a second months worth of data.

The reason isn't complicated - the big table hasn't freed up any space.

The catch is that I've never managed to do a VACUUM on this table because I simply run out of time, I've let the process run for over 24 hours and it hasn't completed.

Is there any other way to free up space? or to speed up the VACCUM process? I can't continue to partition the data until I have some more space!

You are in a tough spot.  You will probably need to schedule some down time and get some more disk space to act as scratch space.  VACUUM might not free up any space anyway because it can only free up contiguous chunks of completely empty pages at the physical end of the table.  With downtime, the fastest solution may be to copy the data out of the table with \copy, perhaps compressing as it goes so that it all fits on your existing storage.  Then drop the table and re-import it into the individual partitions.  But note that partitioning the data won't make it smaller, unless there is currently lots of bloat.  So it doesn't solve the fundamental problem.  If you want to keep a 1TB of data around, you need to have enough storage to do that.

If you have the luxury of patience, you can try to use pg_repack to squeeze the space out of the table while it is still live.

  or to speed up the VACCUM process?

The default settings for manual VACUUM are for it not to be IO throttled.  That is because the default setting for vacuum_cost_delay is zero.  Make sure you haven't changed that.  Make maintenance_work_mem be 1GB if you have enough RAM to support that.  Drop any indexes on the table you can get away with dropping.  Use system tools (e.g. top, vmstat, perf, strace) to figure out where the bottlenecks in your VACUUM are.


Is it possible to make a trigger that does not allow INSERT? I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during a certain period of time).
I have a database History and in this database exists the table events with the following schema:
CREATE TABLE events
    (
     id integer primary key autoincrement,
     msg varchar(20) NOT NULL,
     ago varchar(30) NOT NULL
    );

I know a mySQL implementation would likely use INSTEAD OF INSERT on the table, though I also know INSTEAD OF can't be executed on tables, only views in sqlite. (To be honest I'm new to databases so I only kinda understand this concept).
Would anyone with some more database experience be able to help me with writing (or at the very least understanding the theory behind) a trigger that does not allow data to be inserted to the table?
Edit: This article is where I first got the idea that this would be possible

It will be something like
CREATE TRIGGER forbid_events_update
INSTEAD OF UPDATE
ON events
FOR EACH ROW
BEGIN
    RAISE(ROLLBACK, 'Events update not allowed'); /* maybe ABORT of FAIL */
END;

And the same for INSTEAD OF DELETE.


Database table update using single mysql query I need a single update query to update the gender in a table - swap 'F' with 'M' and 'M' with 'F'.

Many ways to do this.

One way is using case when ... (assuming no NULLs or other gender values):

update yourtable set gender = (case when gender ='F' then 'M' else 'F' end);


Is it possible to commit a transaction that is waiting to RollBack? I'm using Aurora MySql 5.6.10a and a query that was loading a table with 700 million rows crashed and now it's rolling back. I turned autocommit off to try to speed the data load up, but now it's giving me a huge headache.

When i run SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; it returns the following:

3175241 ROLLING BACK    0000-00-00 00:00:00         335482132
3098119 RUNNING         0000-00-00 00:00:00         441106325

Is it possible to revert this rollback and commit those changes? At least, can I commit the running query?

Thank you in advance!

The ROLLBACK must finish in order to get the database back to a table state.

(Without knowing the details of the load, I cannot advise on autocommit.)


Converting ER diagram to tables and relationships in sql Department : Branch is 1 : M

Branch : Course is 1 : M

Branch : Student is 1 : M

Branch : Applicant is M : N

Applicant : Student is 1 : 1

These are the conditions prescribed in the ER diagram.

This is the ER diagram, for which tables have to be made in SQL code implementing all the constraints. I made tables and tried implementing all the relationship via foreign keys, i just wanted to confirm, whether these tables are correct or not.

1) Department table:

create table department(dpet_id number primary key, dept_name varchar2(15)
not null);

2) Branch table:

create table branch(branch_id varchar2(5) primary key, electives varchar2(10),
dept_id number references department(dept_id));

3) Course table:

create table course(course_id number primary key, course_name varchar2(10)
not null,branch_id varchar2(5) references branch(branch_id));

4) Student table:

create table student(stud_id number primary key, stud_name varchar2(30) not null,
branch_id varchar2(5) references branch(branch_id);

5) Applicant table:

create table applicant(app_id number primary key, stud_id number constraint fk
references student(stud_id) constraint stu_unq unique);

6) Applicant_branch table:

create table applicant_branch(app_id number references applicant(app_id),
branch_id varchar2(5) references branch(branch_id));

Do these tables conform to the ER diagram ?

I have tried to give a picture of ER diagram above giving cardinality conditions. Now please tell me if there is any mistake in my SQL code.

The Student - Applicant relationship is 1:1 and your implementation is correct. An alternative is to remove the app_id (or the stud_id) from the Applicant table and to use the (one and only) column both as a Primary Key and for the Foreign Key constraint (see the code below).

I assumed from your code that a Student can be selected as an Applicant. If it's the other way around (some of the Applicants are selected as Students), the foreign key constraint has to be reversed.

You also have several columns (that are used in the foreign key constraints) defined as NULL. Not sure how the diagram is to be interpreted. I guess that these should be NOT NULL. Otherwise, the course.branch_id for example being null means that you may have a course that is not related to any branch at all.

I would also name the (FK) constraints, and since this is homework, I'd name them according to the provided diagram.

It's also not bad to add the NOT NULL to primary key columns as well. It changes nothing in the created tables - until you decide to change the primary key and you forget to add the not null constraint:

create table department
 (dept_id number not null primary key,
  dept_name varchar2(15) not null
 );

create table branch
 (branch_id varchar2(5) not null primary key,
  electives varchar2(10),
  dept_id number not null,
  constraint Department_Has_Branches
    foreign key (dept_id)
    references department(dept_id)
 );

create table course
 (course_id number not null primary key,
  course_name varchar2(10) not null,
  branch_id varchar2(5) not null,
  constraint Branch_Offers_Courses
    foreign key (branch_id)
    references branch(branch_id)
 );

create table student
 (stud_id number not null primary key,
  stud_name varchar2(30) not null,
  branch_id varchar2(5) not null,
  constraint Student_BelongsTo_Branch
    foreign key (branch_id)
    references branch(branch_id)
 );

create table applicant
 (app_id number not null primary key,      -- stud_id removed
  constraint Student_SelectedAs_Applicant
    foreign key (app_id)                   -- app_id used in the FK to Student table
    references student(stud_id)
);

create table applicant_AppliesFor_branch
 (app_id number not null,
  branch_id varchar2(5) not null,
  primary key (app_id, branch_id),
  constraint Student_AppliesFor_Branch
    foreign key (app_id)
    references applicant(app_id),
  constraint Branch_AppliedBy_Student
    foreign key (branch_id)
    references branch(branch_id)
);

For Oracle, see:

Display all names of constraints for a table in Oracle SQL


How to access a SQL Server database from other computer connected to the same workgroup? I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

  A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is

Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?

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.


DB mail sending attachment of size 64 KB only even after increasing maximum file size limit I am facing an issue with DB mail functionality. I am using SQL Server Agent to trigger mail on success or failure. While sending this, I am also providing an attachment, which consists of some query-generated report.

The problem here is I am able to send mail with attachment but the attachment size is fixed to 64Kb only. I have increased maximum attachment size limit from Database mail configuration, still the same problem exists.

I am executing following lines of code:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profileName,
    @recipients = @recipientsList,
    @body = @Body,
    @subject = @Subject ,
    @body_format = 'HTML',
    @query= @Query,
    @query_attachment_filename=@EmailFileName,
    @attach_query_result_as_file=1,
    @query_result_no_padding = 1,
    @query_result_separator= ' '

You need to apply Cumulative update package 4 for SQL Server 2014. There was a bug which was fixed in CU4.

Symptoms

  Assume that you use Database Mail and sp_send_dbmail stored procedure
  with the @query and @attach_query_result_as_file option in Microsoft
  SQL Server 2014. The stored procedure run a query and set the
  result-set as an attachment of the email. When the query has a large
  result-set, the attachment is truncated to 64 kilobyte (KB) maximum.

Resolution

  The issue was first fixed in the following cumulative update of SQL
  Server.

Details in below link:

FIX: "sp_send_dbmail" stored procedure truncates email attachments to 64 KB in SQL Server 2014


What should be done before upgrading MySQL? I need to perform an upgrade of MySQL on my web server. There could be many users connected to the server at any time, performing queries or updates on the database, or uploading files to the server. Besides putting up an advanced notification that the system will undergo maintenance during a certain period of time, what should I do to avoid the scenario of stopping MySQL service or the server while some user operations are still being carried out, which may lead to a corruption of the file system or the database?

If you stop the MySQL Server process gracefully, the filesystem and databases won't be corrupted.

Here are a couple of things I typically do:

Verify that innodb_fast_shutdown is disabled.  If it isn't, disable it.

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

According to the documentation, this is only needed when doing a major version upgrade, but I maintain that it's still a good idea -- this step increases your shutdown time but leaves your instance in a more stable state for the restart.

Next, you can lock all of the tables to prevent clients from doing anything.  This step, also, isn't technically necessary, but I like it because it gives me a sense of knowing what's going on -- and what isn't -- and when -- and it shortens the next part of the shutdown, since there won't be any client activity to wait for.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Note that on a busy server, that statement won't return so quickly.  It won't return until the server has successfully closed all open tables and acquired a lock on every table in the entire database -- so, if you have queries executing when you issue this statement, they will be allowed to finish, then the locks acquired, and then is when you'll get your prompt back.

mysql>

Important: leave this connection open.  Just let it sit there and move to a new window, because the connection being open is what holds the global read lock.  If you close or otherwise lose this connection, the read lock is released.

If you want to look at any variables, the processlist, etc., you can still use this connection if you want to.  Just don't disconnect from the server.

At this point (since you got your prompt back), your tables are locked from further changes and your application tier may start to behave badly because a backlog might be growing.  That's to be expected, since any queries against the server will just wait indefinitely for that read lock.  The number of client connections may be increasing, but they won't be able to do anything.

In another window, tail your MySQL error log.

shell> tail -f /usr/local/mysql/data/hostname.err

In yet another window, shutdown MySQL Server:

shell> mysqladmin shutdown

You will most likely need to add --username --password and possibly other options to this command.  You won't see much from this window, so go back and watch your error log:

The server says, "we're going to do a graceful shutdown." (Not in so many words, but that's what this next log entry means...)

130119 13:29:17 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Normal shutdown

The server shuts down any system threads.  In this case, I left the event scheduler and even replication running to illustrate that a graceful shutdown is indeed graceful.  (Yes, my server started right back up, including replication, right after I captured this output.)  In practice, it might be better to stop replication manually with STOP SLAVE; before you flush the tables and acquire the read lock.  The Event Scheduler is an optional feature, which you may or may not be using.  Here is example output:

130119 13:29:17 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
130119 13:29:17 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130119 13:29:17 [Note] Slave I/O thread killed while reading event
130119 13:29:17 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001539', position 849647640
130119 13:29:17 [Note] Event Scheduler: Stopped
130119 13:29:17 [Note] Event Scheduler: Purging the queue. 3 events
130119 13:29:17 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.001539' at position 849626716

InnoDB does its cleanup (including the "slow shutdown")...

130119 13:29:19  InnoDB: Starting shutdown...
130119 13:29:20  InnoDB: Waiting for 6 pages to be flushed
130119 13:29:26  InnoDB: Shutdown completed; log sequence number 2718873505306

The server shuts down...

130119 13:29:26 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Shutdown complete

The mysqld_safe wrapper notices that the server has stopped running.  This process is charge of trying to restart the server if it crashes, but if the server has removed its own pid file, no restart is attempted, because the shutdown is assumed to be graceful.

130119 13:29:27 mysqld_safe mysqld from pid file /usr/local/mysql-5.5.28-solaris10-x86_64/data/psi2.pid ended

You're now gracefully shut down.


trying to get date to MM/DD/YYY I have a coding using sqldeveloper under Oracle database using a student database, PowerSchool:

PS_CUSTOMFIELDS.getStudentsCF(s.id,'CA_LastIEPDate') AnnDate

This is a custom field, ca_lastIEPdate getting info from Students table and put in a column under AnnDate as AnnualDate and got 12-AUG-2018.  I tried to add to_Char( ,'MM/DD/YYYY') within the coding but could not make it out of it.

PS_CUSTOMFIELDS.getStudentsCF returns varchar2.

Convert to date, then convert back to the expected format.

select to_char(to_date(PS_CUSTOMFIELDS.getStudentsCF(s.id,'CA_LastIEPDate'), 'DD-MON-YYYY'), 'MM/DD/YYYY') AnnDate ...


Beginner Question: Trouble turning a field with comma separated values into a multi-valued field It's my first time using Access, and I have my first of what I assume will be many questions. Yay!

So I imported some data from Excel to a table, including a column with a comma separated tagging system (let's say x, y, z). After some poking around, I went to design view and did the following in the Lookup tab for this "tagging" field:

What happened: entries with just an x, y, or z tag worked. When I click the drop-down arrow for a given entry, I see x, y, and z as choices with the correct one selected. For entries with multiple tags though, it shows up as a new box to be checked. So if I have x, z in the cell, there's a x, y, z, and x, z selection in the drop-down box.

So my question is, how can I make the boxes with multiple tags work correctly?

I think your best bet would be to use a many to many relationship. Generally this is done with a table for the records (like you have now), a table for the comma delimited field and a primary key field with rows for each of the entries (one for x, one for y and one for z) and a third table with nothing but that keys from the other 2 tables.

So, in your join table, you might have a section of 3 rows with the same key on the main table but different entries for the reference table.  This link explains it reasonably well.


MySQL .ibd file is too big I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all memory. Finally i encountered with one table with the extention .ibd has almost 20GB. When i hit sudo du -a | sort -nr | head command, i finally found actual table. it was like this:

20937708        ./var/lib/mysql/databasename/tablename.ibd

I don't know what is going on. Please help me out. My server stopped almost due to the storage issue.

It appears that *.ibd files (like the file ibdata1) by default never shrink, they can only grow.  They are created if you have
innodb_file_per_table
configuration set (which is the default on ubuntu).  If that is not set then you'll see the same behavior from the ibdata file which is basically all tables rolled into one.  Apparently if you have the "all rolled into one" option then there's no easy workaround besides dumping all tables and reloading them from disk.  But you don't so you're good there.
With the .ibd files if you want to reclaim them apparently there's an option optimize table tablename which basically locks the table and rewrites it, and reclaims all the disk space.
Another command that is mentioned (and I assume does the same thing as optimize is ALTER TABLE ... ENGINE=InnoDB;)
See also here.
Somewhat related, even with innodb_file_per_table turned on, the idbdata1 file can grow "a bit" (and never shrinks) because it still includes rollbacks for "your largest transaction to date". Go figure.


Migrate Management Data Warehouse to a New Instance Is there a defined procedure to follow for moving a SQL Server 2012 Management Data Warehouse to a new server\instance?

We intend to setup MDW on a test SQL Server instance first, but will then want to migrate it to a production installation. I can't find any information on this. Two points which are concerns are:

To migrate the databases without losing data.
How to update the registered instances to report their data to the new MDW instance.

I understand this is an old thread, but it was the first hit when I searched "how to move mdw database" on Google.

I had configured MDW (Data Collection) on a SQL Server 2008 R2 production server, and I hosted the MDW database in a QA instance. Later on I had to move the QA instance to a different machine, with a different name (migration and name change mandatory for business reasons) and I wanted to keep the MDW data that was already collected. I could not find anything on that, so I gave this procedure a try and it worked for me:

Stop all data collectors in the monitored server (production in my
case)
Back up the MDW database, optionally put it offline (old QA server)
Restore the MDW database to the new host instance (new QA server)
Re-configure the monitored server to use the MDW database in its new
location (Right click on Data Collection, select Configure Management Data       Warehouse. Select "Set up Data Collection" and click Next. Click on the ellipsis button and connect to the new instance, then from the drop down list select the MDW database that you restored in the previous step. Click Next, then Finish)

The data collectors should start automatically after the Configure Management Data Warehouse wizard finishes, and you can view the MDW reports in the new database location.

Then again, this worked for me in a SQL Server 2008 R2 environment, I am not sure if it works in SQL Server 2012.


How to model complex systems like networks in Postgres I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented perspective, I can code this in my sleep in C++. Switches, for example, would inherit from TwentyFourSwitch, and if they have POE, then from POESwitch, then in each class I would create 24 EthernetPort instances, and each would have a connect function making sure not to get an ethernet port to an SFP port, or a non POE port to a POE device and so on.

With RDBMS, we keep running into problems. First we organized everything to minimize data redundancy. Here is a sample of what we would do (to keep code short I used 2 and 4 port switches instead of 24 and 48).

CREATE TABLE MACAddress{
   id SERIAL INT PRIMARY KEY,
   mac1 INT,
   mac2 INT,
   mac3 INT,
   mac4 INT,
   mac5 INT,
   mac6 INT,
   UNIQUE(mac1,mac2,mac3,mac4,mac5,mac6)
);

CREATE TABLE IPAddress{
   id SERIAL INT PRIMARY KEY,
   ip1 INT,
   ip2 INT,
   ip3 INT,
   ip4 INT,
   UNIQUE (ip1,ip2,ip3,ip4)
);

CREATE TABLE NetworkDevice{
   id SERIAL INT PRIMARY KEY,
   ip INT REFERENCES (IPAddress),
   mac INT REFERENCES (MACAddress),
);

CREATE TABLE TwoPortSwitch{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

CREATE TABLE FourPortSwitch{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

CREATE TABLE Camera{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

But the problem we ran into was that it took many joins and unions and it got very confusing very fast. For example, if you want to know what ip eth0 is connected to, you have to do joins/unions on TwoPortSwitch, FourPortSwitch, Camera, NetworkDevice and IPAddress. And mind you we have about 10 different cameras, 10 different switches, and 5 different computers.

We just got fed up and flattened everything, so now all switches are in one big switch table. It has 48 ports of 1 gig ethernet, 48 ports of 1 gig SFP and 48 ports of 10 gig SFP Plus. All in all, there are more than 1000 columns!

Is there a better way to approach this? I am seriously considering building a webserver and having the C++ server run at all times.

TL;DR: Normalize, normalize, normalize. Joins good, unions bad. Narrow tables with lots of rows good, wide tables with few rows bad. Fewer more general tables good, lots of very specific tables bad.

Denormalized structure

You're trying to model relations as objects. This can work, but it gets clumsy fast. It looks like a pretty direct mapping of your C++ classes, so it has lots of tables for specific types of things, and isn't normalized.

PostgreSQL has table inheritance features that make this approach more manageable, if you really want to pursue it. However the use of table inheritance is basically incompatible with the use of foreign keys due to the lack of unique index support across all child relations of an inheritance base relation. This makes inheritance much less useful than it could otherwise be.

Instead try to model it relationally. There are two schools of thought here, bottom-up and top-down modelling. In practice you use a mix of the two most of the time. Your goal is a well normalized database structure.

First, though...

IPAddress and MACAddress are unnecessary

You're also modelling IPAddress and MACAddress as entities, which they aren't. They're just scalar values. As far as I can tell these are probably direct translations of a C++ class or C struct  that stores IP addresses as four uint8_t octets, MAC addresses as six uint8_t octets, etc. This is completely unnecessary in the database and should be done away with entirely. PostgreSQL provides native data types that serve these specific needs:

inet
macaddr

... but if you have to remain cross-database portable you would be better advised storing your IP addresses as numeric with a boolean indicating IPv4 or IPv6, rather than storing them as octets. You need numeric because a bigint (signed 64-bit integer) isn't big enough for an IPv6 address.

BTW, in the network a MAC address may have multiple IP addresses. While no IP address within a given connected subnet may have multiple MAC addresses, the same IP address may appear in discrete networks with different MAC addresses, so I wouldn't try to model this at the schema level. If I did, I'd create an NetworkIP relation with a primary key of (network_id integer REFERENCES Network, ipaddr) to allow the same IP to exist only on disconnected networks. But then you have to consider multilevel NAT and it gets horrifying. Don't go there. I'd leave them as scalars.

Relational model

Now that we've done away with MACAddress and IPAddress, lets look at structure.

You should flatten everything, but not like this:

  It has 48 ports of 1 gig ethernet, 48 ports of 1 gig SFP and 48 ports of 10 gig SFP Plus. All in all, there are more than 1000 columns!

Rather than "wide", go "tall". Don't fear joins, joins are your friend.

You have network devices, of various specific kinds, where each kind has various additional characteristics. Each device may have one or more IP addresses and MAC addresses assigned to it (e.g. switch management addresses). Additionally, each device may have zero or more ports, and each port has one MAC address and zero or more IP addresses. To simplify things we define management interfaces as virtual ports.

So lets model that core structure.

CREATE TABLE device (
    id serial primary key,
    device_type text not null,
    device_name text,
    description text
);

-- A port is a logical port, which may correspond to a physical
-- plug, or a virtual port like an internal management interface,
-- IPMI controller, etc.
CREATE TABLE port (
    device_id integer not null references device(id),
    port_name text not null,
    port_mac macaddr not null,
    port_is_virtual boolean not null default 'f',
    PRIMARY KEY (device_id, port_name),
    UNIQUE(device_id, port_mac)
);

-- Now the IP address(es) for a port
CREATE TABLE port_ip (
    port_id integer not null references port(id),
    port_ipaddr ipaddr not null
);

-- A socket is a physical plug, which generally has a logical
-- port associated with it.
CREATE TABLE socket (
  device_id integer references device(id),
  socket_index integer not null,
  socket_name text,
  -- This might be a good use for an enum type, but for now
  -- just use a check constraint
  socket_type text not null check (socket_type IN ('rj45', 'sfp', 'sfpplus'),
  PRIMARY KEY(device_id, socket_index)
);

-- A port may have a socket or not, and a socket may have a port
-- or not, so model via a join table. If you want to prevent
-- a socket having multiple ports or a port having multiple sockets
-- you could use additional unique constraints.
CREATE TABLE port_sockets (
   device_id integer references device(id),
   port_name text references port(port_name),
   socket_index integer references socket(socket_index),
   PRIMARY KEY (device_id, port_name, socket_index)
);

See how it's "vertical" - we have a port table with entries for each port for each host, etc.

So now you want to model switches. These are network devices. They have a certain number of ports based on the model.

You can keep track of the number of ports directly in the entity, or you can just count the number of ports for that switch when you want to know. While keeping track of the port count in the switch entity is a denormalization that duplicates data, in this case it's probably worth it. So I'd do something like:

CREATE TABLE switch (
  switch_id serial primary key,
  device_id integer references device(id),
  nports integer not null,
  model text,
  management_port_name text,

  constraint switch_management_port
   foreign key (device_id, management_port_name)
   references port(device_id, port_name)

);

etc.

The switch is a network device, and ports are associated with it via the device table.

All this is a bit hand-wavey since I don't know exactly what you need to model, but should help you start thinking about it in a way that'll get you moving again.

Learn to love joins.

Modelling connections

To model connections between devices you might want to use an adjacency list to model it as an undirected cyclic graph. That sounds hairy, but it's really not, it's just a simple table saying "this one is connected to this one" without bothering to say which is "from" and which is "to", treating them as symmetrical.

There's lots of established literature on querying adjacency lists from SQL. You'll probably want to get friendly with recursive common table expressions (WITH RECURSIVE) if you want to do it efficiently, but for simple cases you can just do loops in C, or fetch relevant subetss of the adjacency list and search it with boost::graph.

e.g.

CREATE TABLE socket_connection (
    first_device_id integer references device(id),
    first_socket_index integer,
    second_device_id integer references device(id),
    second_socket_index integer,

    PRIMARY KEY (first_device_id, first_socket_index,
                 second_device_id, second_socket_index),

    UNIQUE(first_device_id, first_socket_index),

    UNIQUE(second_device_id, second_socket_index),

    FOREIGN KEY (first_device_id, first_socket_index)
      REFERENCES socket(device_id, socket_index),

    FOREIGN KEY (second_device_id, second_socket_index)
      REFERENCES socket(device_id, socket_index),

    -- A simple way to prevent both (1, 1, 2, 1) and (2, 1, 1, 1)
    -- being stored as separate connections:

    CONSTRAINT first_device_id_is_always_lowest
      CHECK (first_device_id < second_device_id)
);

... which looks hideous, but is actually just a mapping between two sockets plus a lot of sanity checking.

A note on composite keys

I've used composite keys like (device_id, socket_index) a lot above.

Some people like them, some don't. There are advantages and disadvantages to their use. If you want to use generated surrogate keys everywhere, that's fine too.

Reinventing the wheel

You might think "hey, has this been done before?".

I'm quite sure it has. Whether the existing models match your needs is less certain, but I suggest looking over some of them before you go and invent your own from scratch.


Updating databases that user's can manipulate I have a product that includes a MySQL database with some defined countries (very few, not all of them). The user has some control over this table - they can add / edit / delete countries. When we update their software, we run an SQL text file that updates the database, including this table. In this case, for a new version upgrade, we might add a few more default countries.

We use one SQL text file for upgrades that is simply appended to over time, so that the user can upgrade from version X to version Y without going in some specific order. To avoid stomping over user data, I think INSERT IGNORE INTO is the best method to add these default countries. However, this will also have the effect of re-inserting countries that the user may have deleted.

What could be a better method of adding default rows to a table that the user actually has full control over? There's no option of a separate "defaults" table because the user needs control over the table.

Is it possible to check what primary keys no longer exist, and if they don't, do not insert into that table?

Let's say my INSERT statement goes

INSERT IGNORE INTO myTable ('id', 'countryName')
VALUES
(1, USA),
(2, Canada);

If the primary key is at 3, and 2 is missing, how can I avoid re-inserting it?

One possible way is adding an is_deleted tinyint column, with 0 as default. Instead of deleting, you can set it to 1, and then filter these out in the queries. This way you always keep all countries that ever got into the table.

In some previous projects of mine we had quite a lot of these, setting them using ON DELETE triggers.  In turn, the queries nearly all went to views that were defined with a condition in the WHERE clause like

...
AND is_deleted = 0 -- well, it was Postgres, so originally AND NOT is_deleted
...

The downside of this is an additional level of dependencies (or two). The other solution is changing the queries to filter out the rows that are marked as deleted.


How to Determine the input and output values of stored procedures? I have hundreds of SPs and I would like to find out:

input parameters with type
output fields with type (not output parameters)

Of course I could manually go through each one and write it down but where is the fun in that...No, literally where IS the fun in that :)

Can this be done or does Sql Management Studio 2008 R2 have this capability already?

I do not even know where to start so any answer is acceptable.

Edit for enhancment of question:
If we look at it as 2 different tasks, could we accomplish this easier. Even using reflection on the client side. (A quick and dirty console app would suffice.)

You should be able to do that using the system view INFORMATION_SCHEMA.PARAMETERS.
You'll have there what you need.

It "returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information."

PS: if the answer to Martin's question is "columns in the result set", then ignore my answer, it is only for the parameters of the procedures, not for any result.


MYSQL primary key strategy for microservices architecture We are currently using auto-generated primary keys and we would like to switch to an approach which is more suitable for microservices applications: either we are going to use business defined primary key (a tax code for persons) or global unique identifiers.

In the past we had performance issues when using business-defined primary keys instead of autoincrement, is it still the case? Does MySQL support strong GUID so that we could have unique uuid even across databases?
Eventually, is there a better approach we are not considering?

Corrected:

with MySQL You can store GUID/UUID data as char/varchar in case of UUID() and UNSIGNED INTEGER in case of UUID_SHORT()

My personal vision - UUID_SHORT not working case, it is unsigned INT same as Auto-Increment and You can read restrictions in function description.

UUID() will return result as utf8 string - "eabd691c-b552-11e6-942e-ee8ae5dda6a8"

Business logic for primary key - work well, You always mange this process and do not need spend time for call function.

add after question in comment:

performance not depend from business logic or auto increment, it depend mostly from 2 parameters:

size of column
function duration time

INT (BIGINT) always will be smaller than CHAR(36) - as result less memory usage, more data in memory for index recreate operations (such as massive insert)

Increment will be faster any other form of random generator in 99% of cases

So in case of TAXNUMBER as primary key:

advantage - no any function for generate this type of key
disadvantage - most of them will take more memory than INT (BIGINT)

Add some visual examples:
make a proper test it very hard, but this is small example:
was created 3 tables, with only 1 column ID - primary key

UNSIGNED INT AUTOINCREMENT
BINARY(16)
VARCHAR(36)

was created stored procedure which insert 11M of records in each table, time results, very close:

11m41s - INT
12m40s - UUID() -> binary(16)
12m40s - UUID() -> varchar(36)

with time difference will need adjust server settings
but if we check size on disk (tables include only 1 column, PK)

-rw-r-----  1 _mysql  _mysql   1.1G 29 Nov 01:00 t__binary.ibd
-rw-r-----  1 _mysql  _mysql   268M 29 Nov 01:01 t__uint.ibd
-rw-r-----  1 _mysql  _mysql   1.6G 29 Nov 01:00 t__varchar.ibd

we can see the difference, and as bigger will be data, as much more will be difference


How to append value of column with double quotes (add quotes around string) I have a table with a column that is TEXT type. In the column are numeric characters. What I'm trying to achieve is to wrap those characters in double quotes.

EXAMPLE:

NAME     ID     QTY
Apples   A1     1
Oranges  O1     1
Foo      F1     0

IDEAL OUTPUT:

NAME     ID     QTY
Apples   A1     "1"
Oranges  O1     "1"
Foo      F1     "0"

I attempted to run the following SELECT statement but it didn't give me the result I was expecting. Perhaps you can guide me in the right direction?

SELECT `qty`, CHAR('"'|| qty ||'"')
FROM `myTable`;

Thank You

Use the CONCAT function

SELECT NAME,ID,CONCAT('"',QTY,'"') QTY FROM `myTable`;

If you want single quotes, use the QUOTE function

SELECT NAME,ID,QUOTE(QTY) QTY FROM `myTable`;

Give it a Try !!!


Getting statistics of a table in Oracle What is more efficient to gather table statistics:

Using dbms_stats.gather_table_stats?
Using analyze table?

It's not recommended to use analyze to gather statistics (from 8i onward) because CBO(cost base optimizer) is build to recognize statistics that have been gathered using dbms_stats. So dbms_stats is a preferable way to gather statistics.


Blue icon with question mark - what does it mean? The SQL Server instance is accessible and seems to be fine.

  Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
  Mar  6 2017 14:18:16   Copyright (c) Microsoft Corporation  Enterprise
  Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build
  9600: ) (Hypervisor)

But what does the white question mark mean?

these icons dont go away when I refresh.
I am sysadmin inside sql server and outside I am administrator on that box.

Another thing I noticed, you can see on the picture below.
These are 2 different Management Studio sessions.

On the top one, I am logged in as myself, DBA and sysadmin,  on the second one
I use Management Studio with run as a different user and I use a domain account that I use for the replication, which is not sysadmin.

The second one has the blue icon in this and other servers as well, whilst mine is the normal green one.

Enabling these Windows Firewall rules did the trick for me

Windows Management Instrumentation (WMI-In)
Windows Management Instrumentation (DCOM-In)

These two rules are predefined and you can enable them by right clicking and enabling these rules in Windows Firewall advanced settings

I Also opened port 135 in the windows firewall, but I don't know if this was really neccessary.

I now confirmed it for Windows Server 2016 and Windows Server 2019 and SQL Server 2017


how to script out all the permissions in a folder using powershell? Using the script below I find out where are the data and log folders:

    declare @rc int,
    @dir nvarchar(4000)

    declare @data    nvarchar(4000)
    declare @log     nvarchar(4000)
    declare @SQLPath nvarchar(4000)
    declare @Backup  nvarchar(4000)

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir output, 'no_output'
    select @SQLPath = @dir

    ----------------------------------------------------------------
    --For the default data location:
    ----------------------------------------------------------------

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

    if (@dir is null)
    begin
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
    select @dir = @dir + N'\Data'
    end

    select @data = @dir

    ----------------------------------------------------------------
    --For the default log location:
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output'

    select @log = @dir

    ----------------------------------------------------------------
    --For the default Backup location:
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir output, 'no_output'

    select @Backup = @dir

    SELECT
    [data path] =     @data,
    [log path] =      @log,
    [sql path] =      @SQLPath,
    [Backup Folder] = @Backup

Then I manually go to that folder and have a look at the permissions sql server (the sql server services account) has on that folder.

I would like to script those permissions so that I can apply them to a new folder.

Using powershell, how can I script all those permissions from this folder let's say folder a:\data and apply those permissions to folder b:\data on the same machine?

Get-Acl worked for me to copy permissions from my dev machine DATA folder to a test folder.

$Acl = Get-Acl "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQL\DATA"
Set-Acl "C:\Program Files\Microsoft SQL Server\test" $Acl

This overwrites the permissions on the new folder, handle with care.

(https://blogs.msdn.microsoft.com/johan/2008/10/01/powershell-editing-permissions-on-a-file-or-folder/)