When do I need transactions /locks? I know that transactions are meant to group several operations as one.
But if for example in the same table one transaction does
 DELETE FROM table where state = 'done'
and another does either
SELECT FROM table where state='new_row'
OR INSERT INTO TABLE VALUES (123, 'new_row')
 do I need any transactions or locks for the DELETE vs INSERT/SELECT? It seems not but I was thinking I may be misunderstanding something

Note:
My question is not about transactions and their benefits.My question is if 2 statements from different processes on the same table SELECT/DELETE need for any reason to be synchronized

In general when running queries (SQL Statements) against a database you want all of the statements to successfully completed and if they do not revert data to what is was before you ran the sql statement. The overall concept you are looking for is ACID

In order to provide this functionality, Databases generally support the concept of a transaction.

When you start a SQL Statement with a Transaction you are telling the database that all statements within the transaction must complete successfully and if they do not the database will rollback(revert) your changes putting the database back to the way it was before you made a change.

Take the following two statements:

Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)

If you run the above statements without a transaction and the first statement failed then the second statement will still run thus inserting a row into the UserCommission table without an associated row in the UserName table.

If you then run the following

BEGIN TRANSACTION
Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)
IF NO Errors Commit ELSE Rollback

In this scenario if the first statement failed you can check for errors and then rollback the transaction (sql statements) leaving the database as though you had never run the statement so you no longer have a row in the UserCmomission table without a corresponding row in the UserName table.

In general you only need a transaction when you are actually changing the data such as Insert, Delete, Update etc but sometimes as part of procedures you may have select statement to get some data that maybe use in Insert, Delete, Update etc statements.

Locks on the other had deal with how the database actually internally marks that the data is in use so that if other user are querying the same data the database knows that the data is in use.

Depending on how your table is constructed (keys, indexes etc), the size of the table etc and the type of action you are performing ( Select, Insert, Update, Delete) the database will perform many different types of locks such as page, table, row locks etc

Of course all of these can impact the performance of your queries and/or database.


Where does mysql store data? Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the 'bot' subdirectory (var/lib/mysql/bot) have only 280KB. Where is the rest?

One more thing - 99% of the database's size is a text column in one of the tables - I know that mysql stores it in separate files, but does it create one file per record?

If you're using InnoDB and have a reasonably default configuration, tablespace data is in the ibdata files, you should have at least ibdata1 by default.

You may want to read up about its configuration:

https://dev.mysql.com/doc/refman/5.7/en/innodb-configuration.html

Specifically splitting it so that each table gets its own ibdata file:

https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html

And there are some related questions:

Ibdata usage and Recommendations?

If you're not using InnoDB, disregard that :)


Tablestructure for fast inserts/deletes with foreign keys Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:

id | competitors | some infos about competitor        | product
---------------------------------------------------------------
1  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
2  | C_2         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
3  | C_3         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
4  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2
5  | C_4         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2

Every 30 minutes we read in the new competitors. To do so we first delete everyone with the specific product (P_1) and insert afterwards the new ones. So there will never be a competitor in our DB, which isnt at the moment listed for this product.

When we got the new infos, we do some kind of research with the products.
(We also have a table called p with ca. 100,000 rows. In that table we store the infos about the products.)

So back to the research process. We run through every product an have to get the competitors with their infos. For this purpose we have do do a mysql request like SELECT * FROM c WHERE product LIKE "P_1";. Not to say that this is not very performant.

My Goal
I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.
So that these infos get stored in an array like this:

[0]['id'] => '2'
[0]['product_identifier'] => 'P_1'
[0]['c'][0]['competitors'] => 'C_1'
[0]['c'][1]['competitors'] => 'C_2'
[0]['c'][3]['competitors'] => 'C_3'

Possible Solution
To get the needed information with just one query, i came up with this solution.

In table c the product row contains a foreign key which links to the p table. That would be very easy to realise. But I am asking myself if it would be possible to delete c or p rows easaly. I never worked that intensive with foreign keys - seemed to me always a bit error generating.

And I also wonder if I could make a join that easy, cause I want from p the related competitors and not the other way around. You know what I mean? Not sure if I can do that this way. And if I could, how to tread this foreign keys. What do I have looking for in order to not get messed up with an unclean database.

Example
We will use our table c from above, where c.product stores the foreign key to our table p.product_identifier. Like this:

id | product_identifier | some infos about the product
---------------------------------------------------------------
1  | P_1                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
2  | P_2                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3  | P_3                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I am not sure how this works out or if it's even a good idea, but how has my query look like? If i want to SELECT every product from p and also their competitors from c?

Don't be scared to create a foreign key in a database. One of the biggest reasons why you should use a foreign key is to enforce referential integrity. Depending on your foreign key it can be possible to use the CASCADE option to delete all competitors for a product when you delete a product (so it makes it easy to manage).

If you set your tables and foreign key up properly the DELETE statement to delete a product and its competitors should something like:

DELETE FROM Product WHERE Product.Product_identifier = [insert your identifier here];

For an example about the DELETE CASCASE option of a foreign key I suggest you read the following article: http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

To list a product and all competitors you can use:

SELECT Product.product_identifier, Competitors.[some infos about competitor]
FROM Product
LEFT JOIN Competitors
   ON Product.Product_identifier = Competitors.Product
WHERE Product.Product_identifier = [insert your identifier here];


Oracle DB size decreased after import My database size was 1TB and its full export size is 300GB.

After imported db to new environment, db size reduced to 700GB.  What could be reason for this ?

There could be many reasons.
But generally, it may be due to:

Free space in data files or temporary files
Undo data
Row chaining and migrating
Data blocks fragmentation


MYSQL Trigger not working I am trying to create the following trigger:

CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
    FOR EACH ROW
    BEGIN
            if new.wp_metakey = first_name then insert into test_customer(first_name) values (new.wp_meta_value);
    END

I keep getting a 1064 error that says, "You have an error in your SQL syntax; check the manual that corresopons to your MySQL server version for the right syntaxt to use near " at line 5"

What am I doing wrong?

The first problem is that you don't have an END IF;.

The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ; to something else so that the client will send the entire CREATE TRIGGER statement to the server as a single statement.

Finally, the column "first_name" probably needs to be qualified with NEW (assume you're referencing a column in wp_usermeta called "first_name".

DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
  IF NEW.wp_metakey = NEW.first_name THEN
    INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
  END IF;
END $$
DELIMITER ;


Upgrading Greenplum DB that includes PostGIS extension We've got a number of 4.2.3.2 Greenplum databases that are running PostGIS 1.4.  We are trying to get these databases onto a more recent version of Greenplum, such as 4.3.8.1 or the re-released version of 4.3.9.1, but we're running into a mess of issues when attempting to upgrade PostGIS to version 2.0.  We've attempted a number of different ways to upgrade the database without compromising data and functions that rely on PostGIS 1.4, but it looks like when we run the gppkg utility with the new PostGIS extensions we only get errors, similar to the following:

gppkg -i postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:21:20:024675 gppkg:s-md-pv-gp-01-01:gpadmin-[INFO]:-Starting gppkg with args: -i postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:21:20:024675 gppkg:s-md-pv-gp-01-01:gpadmin-[CRITICAL]:-gppkg failed. (Reason='__init__() got an unexpected keyword argument 'postupdate'') exiting...

We've also run gppkg passing the -u flag, with similar results:

gppkg -u postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[INFO]:-Starting gppkg with args: -u postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-WARNING: The process of updating a package includes removing all
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-previous versions of the system objects related to the package. For
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-example, previous versions of shared libraries are removed.
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-After the update process, a database function will fail when it is
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-called if the function references a package file that has been removed.
Do you still want to continue ? Yy|Nn (default=N):
> y
20160919:13:35:59:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[CRITICAL]:-gppkg failed. (Reason='__init__() got an unexpected keyword argument 'postupdate'') exiting...

A major caveat may also be related to the fact that only Greenplum versions 4.2.6.X to 4.2.8.X support both version 1.4 and 2.0 of postGIS.  At this point, we're looking for some direction on how to proceed in hopes others may have come up against some of these hurdles.

We've got tickets in with the vendor, Pivotal, but these are not proving fruitful so I'm posting here in hopes the community has some direction they can point me in.

Thanks,

John

My bet is that the postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg package has a bug and it does not have implemented the postupdate as defined in the Gppkg class. The TAG is optional (SPECFILE_OPTIONAL_TAGS = ... 'postupdate'] ) so it looks like it is calling

Check if the upgrade script ($GPHOME/share/postgresql/contrib/postgis-2.0/postgis_upgrade_20_minor.sql) exists in that location. Package details.

From that specification:

PostUpdate:
10  - Master: "echo 'Please run psql -d mydatabase -f
$GPHOME/share/postgresql/contrib/postgis-2.0/postgis_upgrade_20_minor.sql
to finish the update.'"

Also, you may want to try the --clean option


MySQL does not use index for wildcard-free query on an INT field - why? So I have a table with two indexes, one on a VARCHAR field (url) and one on an INT field (id). If I do a query on either of those fields using a WHERE condition with the usual =, the index is used in both cases. But for LIKE queries, the following uses the index:

SELECT * FROM user WHERE url LIKE 'somevalue';

But this one does not:

SELECT * FROM user WHERE id LIKE 1;

Is there an explanation somewhere in the docs for this or does anyone have any ideas why this is so?

To answer the question: http://sqlfiddle.com/#!9/1be04/2

The LIKE works on strings, so it has to CAST the id to a string - casting is a type of a function call (an expression) so index cannot be used.

I was not able to locate that specific information in the MySQL manual as it is an universal concept known as sargability (the article lists LIKE as sargable, but that holds only for string columns/indexes and prefix matching, not in general).

Another answer about what is and is not sargable: https://stackoverflow.com/a/799616/1786423

As ypercube pointed out in the comment - the 1 too is converted to '1' when the query is executed. It is done just once, not for every row, because the value is constant. It should not change the plan for this query but there are some cases when it can hurt you too - doing col IN (1,'2') will forbid the usage of any index no matter what type is set for the col.


Target/total memory is higher than max_server_memory I have the max_server_memory setting configured to 141 GB on our production SQL Server and target/total server memory is 144 GB.

I thought max_server_memory represents how much memory I am willing to let the SQL Server engine use, if so why is it using more than that?

Running SQL Server 2012 SP3, physical machine with 160 GB memory installed, no other instances running on the box but we are also running Integration Services.

We have 75 user databases, for a total size of 928 GB. I am not sure if columnstore is being used, or how could I check that. This is a vendor application.

Right now target memory = 144,384,008 and total = 144,378,261.

The instance has been up since last night, 2019-05-01 22:20:21.097 EST. We had some infrastructure maintenance that required the instance to be stopped. However I have noticed for quite some time that total/target is always at 144 while max has always been set to 141. PLE during main business hours Monday-Friday is about an .5 to 1 hour on average, sometimes it jumps to 2 hours.

The max server memory setting does not represent how much memory you are willing to let the SQL engine use. From the Memory Management Architecture Guide:

Starting with SQL Server 2012 (11.x), SQL Server might allocate more
memory than the value specified in the max server memory setting. This
behavior may occur when the Total Server Memory (KB) value has already
reached the Target Server Memory (KB) setting (as specified by max
server memory). If there is insufficient contiguous free memory to
meet the demand of multi-page memory requests (more than 8 KB) because
of memory fragmentation, SQL Server can perform over-commitment
instead of rejecting the memory request.
As soon as this allocation is performed, the Resource Monitor
background task starts to signal all memory consumers to release the
allocated memory, and tries to bring the Total Server Memory (KB)
value below the Target Server Memory (KB) specification. Therefore,
SQL Server memory usage could briefly exceed the max server memory
setting. In this situation, the Total Server Memory (KB) performance
counter reading will exceed the max server memory and Target Server
Memory (KB) settings.
This behavior is typically observed during the following operations:

Large Columnstore index queries.

Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.

Backup operations that require large memory buffers.

Tracing operations that have to store large input parameters.


MySQL: Merge Same Database on Several Location into Single Location I have a registration form, which is exactly the same for several locations. Each of them has a REG column, which is unique for each inserted row. Basically, each data input has a different REG number for it, which is auto incremented.

The location's form references a location's table which has the same structure for each individual location.

However, we have one central database which pulls all the data from several locations. This central database has the job of manipulating and populate ing all the location's databases.

Let's say, I have 3 locations, A, B and C. All those locations are using same database structure to save the data of each individual location.

What I need help on here, is how must I change or configure the database table or column so I can "restore" each individual database from the different location's into one single database on the central location. If the structure is same, then the previous data will be replaced by the new one when restore/add to the database in the central location.

I have a column called CODE which stored specific hard code for each location, it is just a simple character either A, B or C based on where the location of the database is saved.

So, at the specific time, from 3 different locations, the form table will be backed up and sent to the central database. And there, it will be restored or added to the database to populate all from those 3 locations.

Any idea and little help, please? Thanks in advance.

Here the database layout I would like to be done.

And the engine is InnoDB.

Let me first suggest an approach that may not work for you, but it seems the ideal approach for saving bandwidth and resources:

Multi-source replication is a relatively new feature, available on MariaDB 10 and MySQL 5.7 (not yet released). It is used precisely for the case you are talking about: merging data from different servers into one, typically for analytical purposes.

Here it is an overview of how it works for MariaDB 10 and for MySQL 5.7. If the tables have the same name, and you set the proper filters, you could have the table almost up-to-date in real time without having to do imports and exports every single time (which will become more and more inefficient each time). Even if you do not want that, it would be just easier to start and stop the replication each time, unless the tables are completely rewritten between synchronisations.

As writing to the same table could become insecure (for the data integrity)- depending on the kind of DML queries that you execute at each location-, you could alternatively write to separate tables and use a VIEW to see it as a single table. This may or may not be better for performance depending on the queries executed on the central location.

If this does not work for you (for example, because you cannot use those versions of MySQL- after all, this is a relatively new features), I can suggest alternative methods.

EDIT:

As you seem to not be able to use a stable internet connection (although that wouldn't be a problem, replication is asynchronous and it will work even if it stops very frequently), I will assume you want to do the transfer via a USB drive.

My second recommendation would be to backup the table or tables using CSV if the data is not too big, and in binary format if they are relatively big.

For CSV format, you can use mysqldump --tab or mydumper and then import them back with mysqlimport/myloader.

If CSV format is too slow, I would recommend you to use something like Percona XtraBackup that, combined with InnoDB, allows you to export tables in binary format and then reimport them separately (it requires Percona Server or MySQL 5.6 on the importing node and innodb_file_per_table). You can first import each table separately and then merge it into a single table as partitions with EXCHANGE PARTITION. That would allow you to do the export/import at nearly disk speed.


Shouldn't mysql be using both indexes in a join? I have the following tables:

CREATE TABLE `tx_in` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `txid` char(64) DEFAULT NULL,
  `hashPrevOut` char(64) DEFAULT NULL,
  `address` char(34) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hashPrevIndex` (`hashPrevOut`(8))
) ENGINE=InnoDB AUTO_INCREMENT=641391046 DEFAULT CHARSET=latin1

CREATE TABLE `tx_out` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `txid` char(64) DEFAULT NULL,
  `btc_value` double DEFAULT NULL,
  `address` char(34) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `txidIndex` (`txid`(8))
) ENGINE=InnoDB AUTO_INCREMENT=695391886 DEFAULT CHARSET=latin1

And I am running the following query:

UPDATE tx_in LEFT JOIN tx_out ON tx_in.hashPrevOut = tx_out.txid SET
tx_in.address = tx_out.address;

I modified it into a SELECT statement so that I could run EXPLAIN on it.

EXPLAIN SELECT * FROM tx_in LEFT JOIN tx_out ON tx_in.hashPrevOut = tx_out.txid;

And got this output

+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref                          | rows      | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+
|  1 | SIMPLE      | tx_in  | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                         | 635293613 |   100.00 | NULL        |
|  1 | SIMPLE      | tx_out | NULL       | ref  | txidIndex     | txidIndex | 9       | blockchain.tx_in.hashPrevOut |         2 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+

But the execution plan doesn't have any reference to the index on hashPrevOut.  Should I be concerned about this?  It seems like it should just be using the two indexes to do the join.  As you can see the tables are quite large so this could make a big difference in execution time.

I think this is due to the partial index. i.e. the index has only 8 char of hashPrevOut

It estimated that the number of matching first 8 char will lead to a second read from the table to compare the full value from first table with the full value in the other table, so the optimizer decided to skip the index, and deal with the table directly.


Speedup restore of 160 GB SQL in 24 core + Optane NVME system i set up a new sql-server, ubuntu 18.4 (MYSQL 5.7), 24 core (48 threads), 256G RAM, System is SATA Raid1 XFS SSD /var/lib/ is mounted on Optane SSD XFS.

In restore i can see that the server has unter 1.0 load so it is quit sleeping. I dont know what i can improve to speedup the restore.

Dump size is 30 GB gzip about 160gb 300 mio entries, attached my mysql.cnf
It is a stand alone server just for mysql.

The old server ist mysql 5.6, i dont know if it is able to copy the ibdata files?

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
explicit_defaults_for_timestamp = 1
#
key_buffer_size     = 6M
#max_allowed_packet = 16M
#thread_stack       = 192K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 10000
table_open_cache       = 15000
open_files_limit= 17000
innodb_doublewrite = 0
log_error_verbosity = 1
innodb_buffer_pool_size = 220G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 48
innodb_lock_wait_timeout=120
innodb_io_capacity = 400000
max_heap_table_size = 5G
max_allowed_packet=1G
sort_buffer_size=9M
join_buffer_size=9M
# * Query Cache Configuration
#
query_cache_limit   = 16M
query_cache_size        = 512M
query_cache_type    = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Restoring from an SQL dump will be always slow. You can make it faster by:

Increasing innodb_buffer_pool_size to size of the old datadir or what available RAM allows. 220G in your config is a good value, so this option not for you.
Setting innodb_flush_log_at_trx_commit to 0 or 2.
Increasing innodb_log_file_size to several GB. 4GB will be OK.
Disable query_cache.
disable binlogs.

Do not disable innodb_doublewrite - unsafe.

Yet, I doubt you'll gain much.

To restore the database faster use XtraBackup or cold copy (if you shutdown the old database and copy datadir to the new server it will pick it up w/o problems assuming same MySQL version).


Use cases for NoSQL databases: Time series I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance).

There're also some articles that recommend the same  approach: see Andrew Oliver's article.

Why is not convenient to record a temporal series in Oracle (or any other relational database)? Could you please help me in giving an example?

Oracle is a SQL DBMS not a truly relational database.  It implements as its logical model of data a variant of SQL.  Its architecture was developed in the late 1970s along the same lines as IBM's System R which was an initial implementation of a DBMS based on the relational model using SQL as the data sub-language.  This short background is necessary to understand that SQL and Oracle are not the same as relational.  The relational model, as defined by Codd and further developed by researchers like Date, is a purely logical model of data where data is to be presented as relations, with a relational algebra defined to manipulate the relations, and a data integrity component to make it possible for the DBMS to maintain data consistent with its real world intent.  The relational model is mute on implementation by the DBMS.  Therefore, when identifying a use-case that a given SQL DBMS does not handle well purely due to performance reasons, the issue lies with the implementation, not the relational model.
Given this, I suspect NoSQL solutions are sometimes recommended over SQL DBMS' for time series analysis as time series analysis is a very narrow use case and the SQL DBMS architecture was aimed at more generalized online transaction processing use cases.  I know little about time series analysis but do recognize that it is purely analytical and not OLTP, and so transaction support - which is a mainstay of SQL DBMS' but orthogonal to the relational model - is pure overhead in such a use case.  I recall seeing Michael Stonebraker a few years ago discuss time series analysis and argue the solution was to store data in arrays not rows.  Since all the SQL DBMS's are row-stores, that may be another reason why other solutions are recommended.
I would caution against diving right into a NoSQL solution.  These systems are much less mature than traditional DBMS'.  Secondly, time series analysis I believe is pretty heavy statistics and you will likely have to add that yourself with a NoSQL solution.  A mature SQL DBMS like Oracle may have some built in statistical features that are much easier to use.  Third, SQL is, despite its flaws, a complete query language giving you the power to write queries of arbitrary complexity.  Most NoSQL solutions require you to write programs to perform the analysis you want.  Finally, and perhaps most importantly, it is likely that to get any useful information out of your time series data you need to "enrich" it with other related data.  For example, I work for an electric utility and in this business just having a huge amount of time series data on how much power was used for an interval of time isn't very useful unless you can correlate it to weather, demographics, and so on.  A SQL DBMS, precisely because it is a generalized data management solution, makes that easy.  You can place the time series data in the same database as the enriching data and have the full power of SQL to join and analyze it.  With a NoSQL solution you will have to perform the enrichment yourself as an additional step - potentially extracting, transforming, and loading the data from the very SQL DBMS that wasn't used to store the time series data in the first place!  It will be a lot of extra work to write the ETL programs, and you have to decide at the time you write them what data will be useful for analysis.  If later you decide you didn't have everything useful, you now have to write more programs.  If instead you placed the time series data in Oracle right along with all your other data it is already in place and ready for analysis once you discover a need.
Bottom line I would say that unless can prove you have so much data coming in so fast as to exceed the capacity of your existing SQL DBMS installation, and you have the time and the skill sets necessary to write the additional infrastructure on top of a NoSQL solution (assuming of course the NoSQL solution you choose does have the capability to scale to the data volume and velocity), you are better off sticking with the SQL DBMS.


Streaming replication and failover on PostgreSQL I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is not providing automatic failover for streaming replication. We can switch the slave to master using a trigger file but it is not manageable. So I'd like a solution with automatic failover and high availability.

Different solutions are available:

Repmgr
Linux Heartbeat
Pgpool-II (for automatic failover only)
Any other tool in case you used.

My question is which solution should be used?

In our shop we selected repmgr and pgbouncer instead of pgpool. repmgr has some nice tooling to setup and maintain the cluster of replicated database servers. In our case 1 master and 2 slaves (one failover and one live read performance test that can become the failover of the new master). pgpool has issues with changes in the configuration, in most cases you have to restart the service and therefor you have some downtime. This is a problem when you need 24x7x365 availability.

repmgrd (the deamon) helps to select the new master after a failover, you really don't want a split brain situation. We have one virtual ip-address for the master database, the database that is master at that moment. When another server becomes master, this is the only server using this address. Every database server also has it's own ip-address for read only queries.

repmgr is maintained by the same guys that created streaming replication in the first place, so they know what they talk about. Version 2.0 is about to be released.

Prepare for the worst situation, do some serious testing by pulling some power and network plugs! When something goes wrong, many other things already went wrong and will bite you in the back when you can't afford it.

Replication is one thing, a working failover after some serious problems, is another thing.


How can I compare the number of transactions in the first weeks or months of different stores I need to make a comparison of the number of transactions per week and month of different stores since its opening date. The problem is that the stores has a different opening date.

So, The idea is to compare the number of transactions in the first weeks or months

That's what I have:

 Canal Key 1

   Recount FactTransactions

                              Month1-2015 Month2-2015 Month3-2015 Month4-2015 Month5-2015 Month6-2015

 Type of store     Cod Store

Spain
         2          Store 1                                              5         6        10
                    Store 2      10          20            40            50        60       85
         4
                    Store 3      31          45            100           315       441      625
                    Store 4                                10            20        32       45
Portugal
        1
                    Store 5                                                                 12

That's what I want to obtain (comparing different stores since its opening date):

    Canal Key 1

   Recount FactTransactions

                              Month1 (or Week) Month2 Month3 Month4 Month5 Month6

 Type of store     Cod Store

Spain
         2          Store 1       5              6      10
                    Store 2       10             20     40     50     60       85
         4
                    Store 3       31             45     100    315    441      625
                    Store 4       10             20      32    45
Portugal
        1
                    Store 5                                                                 12

That's my data model:

 FacTransaccion: *Measures* Recount FactTransaction / Amount / Amount discount / Unit Price

 DimDate: *Hierachy* Date (YYYYMMDD) / Month / Month Name / Week of Month / Week of year / Year (It's the same Dimdate than AdventureWorksDW2008 with its member calculation and relationship)

 DimProduct: *Hierachy* Product Key

 DimStore: *Hierachy* Type of store / Store key / Opening date / Country

 DimType: *Hierachy* Type of transaction

 DimCanal: *Hierachy* Canal  (there are two channel where people can buy)

Reading a lot about MDX, after a thousand times of MDX. I don't have anything.

Several attemps:

Attempt #1:

SELECT CROSSJOIN({TAIL([Dim Store].[Opening date].Children, 30) }, { [Measures].[Transactiones] }) ON COLUMNS
    ,{ [Dim Store].[Store names].Children} ON ROWS FROM [CUBE]

Attempt #2:  (It does not work) It seems to me better than first attempt.

WITH MEMBER [Measures].[Count] AS
SUM([Dim Store].[Name Store].currentmember*Tail([Dim Store].[Opening Date].children,1),
[Measures].[Count Fact Transacciones])
SELECT {[Measures].[Count]} ON COLUMNS,
{[Dim Store].[Name Store].[All]} ON ROWS
from [CUBE]

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply.

If you add more detail I can expand my answer to be in line with your data model.

Since you want to compare the number of transactions you should have a measure showing the number of transactions.

As you want to compare them over time you would also need a Time Dimension with a hierarchy.

If you have both those prerequisites you can resort to MDX to calculate sums and differences of your measure over your dimension hierarchies and members.

In essence what you need to try is to construct a set and sum your measure over that set.

Sum({[opening date]:[opening date+2weeks]},[Measures].[Number of transactions])

The hard part in your problem will be constructing the set however. And that's the part we can't answer without your data model and some sample data.

As you say you have different start dates for different stores you could use a property on the store dimension indicating it's start date and use that to construct a set to sum over.

A great resource to get started with MDX is the Stairway to MDX series on SQL Server Central.

You should look at functions like PeriodsToDate.

You also have the option of resorting to VBA functions and could use that to use DateDiff and DateAdd if you can't get there with pure MDX.

You can even write your own SSAS Stored procedure if all else fails.


Query to Linked Server Ignores Configured Timeout Connection I need to know where to set the timeout for a login or a query to a linked server. If the server is down I don't want to waste too much time waiting for a timeout. (Our servers are on the same network with a gigabit connection. All are running SQL Server 2017.)
For example,
SELECT * FROM SERVER_A.master.sys.databases
runs instantaneously if the linked server is up. If the linked server is down, e.g. if I stop the SQL Server service on SERVER_A, it takes 21 seconds to timeout with the following message:

OLE DB provider "SQLNCLI11" for linked server "SERVER_A" returned
message "Login timeout expired".
OLE DB provider "SQLNCLI11" for
linked server "SERVER_A" returned message "A network-related or
instance-specific error has occurred while establishing a connection
to SQL Server. Server is not found or not accessible. Check if
instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books
Online.".
Msg 2, Level 16, State 1, Line 2
Named Pipes Provider: Could not open a connection to SQL Server [2].

EXEC sp_testlinkedserver SERVER_A times out in the same 21 seconds with the same message.
I can't figure out where the timeout is set. Here is what I've tried:

Linked Server Properties for SERVER_A\Advanced, I set the Connection Timeout to 1.
Linked Server Properties for SERVER_A\Advanced, I set the Query Timeout to 1.
Local server Properites\Advanced, I set Remote Login Timeout to 1. (Same as EXEC sp_configure 'remote login timeout', 1)
Local server Properites\Connections, I set Remote query timeout to 10.(Same as EXEC sp_configure 'remote query timeout', 10)
I looked through all of the settings in sys.configurations and couldn't find any that are anywhere near 21.

Regardless of what settings I change, the timeout still takes 21 seconds and returns the above error message.
What am I missing?

UPDATE:
I tried setting the linked server connection timeout to 43. After that, it took 63 seconds to timeout. We're getting somewhere! Setting it to 30 or 33 times out in 42 seconds, etc. Something is adding either 12 or 20 seconds to the login timeout.
After more testing, it appears that the timeouts I'm experiencing are in multiples of 21 seconds. It's taking 21, 42 or 63 seconds to timeout. It takes longer as I increase the Connection Timeout past a multiple of 21. e.g. if I set the Connection Timeout to 21, it takes 42. If I set it to 50, it takes 63.

I was able to achieve it in two ways:
Configuring a timeout for the whole server
You should configure the server timeout option (remote login timeout) using sp_configure:
EXEC sp_configure 'remote login timeout', 1 ;
GO
RECONFIGURE ;
GO

Configuring a timeout for the specified Linked Server
You should configure the Connection Timeout with the value 1 or more:

If you query sys.servers by now, you should see the specified value for the column connect_timeout.

I noticed a strange behavior after configuring it. SQL Server seems to try the remote connection longer than the specified connection timeout the first time you try to run a query using a linked server (as if it was insisting to be sure it can't reach the remote server). But the following attempts of running a query it threw the connection error quickly as specified.


Wich is the most efficient/easy way for end-user to get data from Analysis database? I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can access the attribute and the value related only by writing a text.

For example: for Branch dimension, instead writing cubemember("connections";"[DimBranch].[Name].[All].[London])", you can write in the cell only "London". However, this won't work if you have a parent-child dimension and want to retrieve the amount for one of the intermediate levels.

Does anyone know how I can avoid writing these formulas directly by the end-user? Can I pass the text "London" as a parameter?

You can put your cube formula in a separate cell and reference the cell where users type in the name they want.  For example, if you have users put the branch name in cell B2, you can put this formula in another cell.

=cubemember("Connection","[DimBranch].[Name].[All].[" & B2 & "])"


Does a computed column break 3NF (third normal form)? From my understanding of 3NF, there should not be any transitive dependency.

However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is dependent on OrderQty and ScrappedQty, there is a transitive dependency in this table, am I right?

The DDL for the aforementioned table is as follows:

CREATE TABLE [Production].[WorkOrder](
        [WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
        [ProductID] [int] NOT NULL,
        [OrderQty] [int] NOT NULL,
        [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
        [ScrappedQty] [smallint] NOT NULL,
        [StartDate] [datetime] NOT NULL,
        [EndDate] [datetime] NULL,
        [DueDate] [datetime] NOT NULL,
        [ScrapReasonID] [smallint] NULL,
        [ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED
    (
        [WorkOrderID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

My questions are:

Is a computed column in fact transgressing 3NF or is it not, considering that such a column cannot be updated manually by a user?
If it is indeed violating 3NF, then why is the computed column shown in the example above?
Ignoring the factor of whether a computed column infringes 3NF or not, what are the criteria of measure to choose between a computed column over computing when querying?

Computed columns are not a construct of the relational model, so it doesn't make sense to try and evaluate them using relational model terms such as 3NF.
Computed columns are a proprietary extension to SQL added by some vendors, and since they aren't logically stored (although you may persist them physically) they are not part of the tuple as they are not an attribute of the relation, but some expression based on it.

BTW - if that's the only modeling flaw you find in AdventureWorks, and most other sample databases for that matter, look closer :-)

HTH


Linked Server in SSMS 2014 - Shared Memory Provider, error: 0 - The pipe has been ended I'm trying to connect to Mongo database from Microsoft SQL Server Management Studio through Linked Servers using Mongo ODBC Driver. Right now I'm testing Easysoft ODBC Driver.

I can connect to the database and can perform some queries. But if I try to execute query which will return a lot of data I receive this error:

Msg 109, Level 20, State 0, Line 2
A transport-level error has occurred when receiving results from the server.
(provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

I receive this error exactly after 31 seconds.  I will appreciate any help.

I am not a mongodb developer, but I am not surprised that you had hit the 30 second problem.

See Do you want a timeout? (mLab Blog) for hints.

  The default driver connection timeout value ranges anywhere from 1 second (e.g. the Node.JS driver in some cases) to 30 seconds (e.g  the Ruby driver), so you really need to think about what the optimal setting is for your use case.

The socket timeout ideally needs to be set to 0 which means 'forever'.

  Disclaimer
  The original answer was posted as a comment by @RLF


SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type UPDATE - I originally said 'estimate significantly lower when set to 'NONE'. I'm amending that to say 'estimate significantly lower when set to [the compression in use].

When I execute the sp_estimate_data_compression_savings procedure on various tables with the 'data_compression' parameter set to 'NONE', it shows large size differentials on the non-clustered indexes.

The 'size_with_current_compression_setting' is correct and reflects the actual size of the indexes. The 'size_with_requested_compression_setting' though is much smaller.

If the request is for table/index size with compression set to its current compression type, why is it showing that the indexes would be far smaller than they actually would be if I chose that compression type?

Here's an example of what I'm seeing with real values, names changed:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Estimate Row Compression:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Index Sizes:

Current Compression Settings:

----- UPDATE: After Degragmenting

New Compression Estimates:

Just for kicks, I compressed the indexes with 'NONE':

ALTER INDEX [INDEX_2] ON dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);

Compression estimates again:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Finally, I set the compression level for all 4 indexes to 'ROW' (e.g.):

ALTER TABLE dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);

ALTER INDEX INDEX_2 ON dbo.FOO_TABLE REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW); // (And INDEX_3 & INDEX_4)

Now, the estimate for 'NONE:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Now, the estimate for 'ROW:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Checking fragmentation again:

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[DATABASE]'),
OBJECT_ID(N'dbo.FOO_TABLE'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Here's the DDL for the table:

CREATE TABLE [dbo].[FOO_TABLE](
    [COL1] [char](6) NOT NULL,
    [COL2] [char](4) NOT NULL,
    [COL3] [datetime] NOT NULL,
    [COL4] [datetime] NOT NULL,
    [COL5] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [COL6] [int] NULL,
    [COL7] [int] NULL,
    [COL8] [char](4) NOT NULL,
    [COL9] [datetime] NULL,
    [COL10] [char](5) NULL,
    [COL11] [char](1) NOT NULL,
    [COL12] [char](1) NOT NULL,
    [COL13] [datetime] NULL,
    [COL14] [char](1) NOT NULL,
    [COL15] [char](1) NOT NULL,
    [COL16] [int] NULL,
    [COL17] [char](1) NOT NULL,
    [COL18] [datetime] NULL,
    [COL19] [char](8) NOT NULL,
    [COL20] [datetime] NOT NULL,
    [COL21] [timestamp] NULL,
 CONSTRAINT [pk_meterset_volume] PRIMARY KEY CLUSTERED
(
    [COL1] ASC,
    [COL2] ASC,
    [COL3] ASC,
    [COL4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_2] ON [dbo].[FOO_TABLE]
(
    [COL16] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_3] ON [dbo].[FOO_TABLE]
(
    [COL3] ASC,
    [COL1] ASC,
    [COL9] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_4] ON [dbo].[FOO_TABLE]
(
    [COL5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Check out this link - http://www.bobpusateri.com/archive/2012/04/compression-estimates-that-dont-make-sense/

  BOL states that “If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.”

The above link does point to a Microsoft Connect item that was opened that seemed 'somewhat' similar, but not exactly like your issue.  Microsoft close it indicating no fix would be forthcoming.


What does Can't find file: 'table_name' (errno: 2) mean? I have very little experience with databases, and today I encountered this error today and I have no clue what it means. I have done some research but I haven't been able to find a solution. The error started a day before when some results wouldn't show up. Let's say that the name was table1 then the table form today is table2. So, I was unable to display the information from table1 yesterday, and today I am unable to display content from table1 and table2. Table2 is the one that displays the Can't find file: 'table_name' (errno: 2) Any help or guidance would be highly appreciated.

Connect to your database and run the following:

show tables;

This will list all tables in the DB. Are the ones you are trying to query from in the list?


create difference column of time series in MySQL I have a table with 2 columns, the date and the value for the date, e.g.

date         value
2015-09-01   15
2015-09-02   17
2015-09-03   21

and I want to create a column with the difference between the value and the value of the last day, like

date         difference
2015-09-01   NULL
2015-09-02   2
2015-09-03   4

Can this be done with MySQL?

Hope this helps. It's SQL Server code, but you should be able to perform the same join on your table with the date_add function.

DECLARE @TABLE TABLE ([Date] Datetime, [Value] int);

INSERT INTO @TABLE VALUES ('2015-Sep-01',11);
INSERT INTO @TABLE VALUES ('2015-Sep-02',13);
INSERT INTO @TABLE VALUES ('2015-Sep-03',15);
INSERT INTO @TABLE VALUES ('2015-Sep-04',20);
INSERT INTO @TABLE VALUES ('2015-Sep-05',68);

SELECT
    Main.Date,
    Main.Value - Previous.Value as [Difference]
FROM @TABLE Main
LEFT OUTER JOIN @TABLE Previous
    ON Previous.[Date] = DATEADD(DAY,-1, Main.Date);


Return a value only when conditions satisfy for entire column Can anyone help me to how to achieve below result

Input data

ORDER_NUMBER    LINE_ITEM   FLAG
LP-13288    1   TRUE
LP-13288    2   FALSE
LP-13288    3   (null)
LP-13288    4   FALSE
LP-13289    1   FALSE
LP-13289    2   FALSE
LP-13290    1   TRUE
LP-13290    2   TRUE
LP-13290    3   TRUE
LP-13292    1   FALSE
LP-13293    2   TRUE

But I am expecting result like below:

ORDER_NUMBER    LINE_ITEM   FLAG    RESULT
LP-13288    1   TRUE    0
LP-13288    2   FALSE   0
LP-13288    3   (null)  0
LP-13288    4   FALSE   0
LP-13289    1   FALSE   0
LP-13289    2   FALSE   0
LP-13290    1   TRUE    1
LP-13290    2   TRUE    1
LP-13290    3   TRUE    1
LP-13292    1   FALSE   0
LP-13293    2   TRUE    1

Scenarios that need to implement

For a group of order_number, if all line items are 'true' then result field need be '1'
For a group of order_number, if any of line items contains mixed flags (true, false, null) then result field need to populate '0'

Any help would be appreciated.

You could use window aggregation (an aggregate function with an OVER clause) to apply the condition to a column of values whilst calculating the results for detail rows. Here is how you could use window aggregation in your situation:

SELECT
  order_number,
  line_item,
  flag,
  CASE COUNT(CASE flag WHEN 'true' THEN 1 END)
       OVER (PARTITION BY order_number)
  WHEN COUNT(*)
       OVER (PARTITION BY order_number)
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

The COUNT(CASE flag WHEN 'true' THEN 1 END) OVER (PARTITION BY order_number) expression calculates the number of true values per order_number and returns the same value for rows that have the same order_number. As a result, you can generate values identical within groups of rows as well.

A variation on the above (using reverse logic and one count instead of two):

SELECT
  order_number,
  line_item,
  flag,
  CASE WHEN
       COUNT(CASE flag WHEN 'true' THEN NULL ELSE 1 END)
       OVER (PARTITION BY order_number)
       = 0
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;


Inserting into related tables I have a database setup with the following schema:

CREATE TABLE siteInfo (
    siteID             SERIAL  NOT NULL,
    siteName           TEXT    NOT NULL,
    siteHost           TEXT    NOT NULL,
    notes              TEXT    NOT NULL,
    allowableCountries TEXT    NOT NULL,
    displayChance      INT     NOT NULL,
    imagePath          TEXT    NOT NULL,
    PRIMARY KEY (siteID)
);

CREATE TABLE siteLinks (
    siteID             SERIAL  NOT NULL,
    URL                TEXT    NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

CREATE TABLE siteDemographicLinks (
    siteID             SERIAL  NOT NULL,
    URL                TEXT    NOT NULL,
    country            TEXT    NOT NULL,
    chance             INT     NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

CREATE TABLE linkVisits (
    siteID             SERIAL  NOT NULL,
    rawVisit           BIGINT  NOT NULL,
    redirects          BIGINT  NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

Data from my form is in the following format:

array(7) {
["siteName"]=>string(9) "Site Name"
["siteHost"]=>string(9) "SIte Host"
["notes"]=>string(5) "Notes"
["links"]=>array(2) {[0]=>string(6) "Link 1" [1]=>string(6) "Link 2"}
["countries"]=>string(4) "None"
["displayChance"]=>string(2) "15"
["extraLink"]=>array(2) {
   [0]=>array(3) {
      ["url"]=>string(7) "dSite 1"
      ["countries"]=>string(4) "NONE"
      ["chance"]=>string(2) "11"
      }
   [1]=>array(3) {
      ["url"]=>string(6) "dSite2"
      ["countries"]=>string(4) "None"
      ["chance"]=>string(2) "13"
      }
   }
}

How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?

I have this query constructed:

INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath)
VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');

I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.

INSERT INTO siteLinks (siteID, URL)
VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');

Is there a better way of doing this?

Chain your insert with data-modifying CTEs:

WITH ins0 AS (
   INSERT INTO siteInfo (siteName, siteHost, notes, ... )
   VALUES ('Sample', 'sample.com', 'note', ... );
   RETURNING siteid
   )
INSERT INTO siteLinks (siteID, URL)
SELECT siteid, 'test'
FROM   ins0;

Or use lastval() or currval():

BEGIN;

INSERT INTO siteInfo (siteName, siteHost, notes, ... )
VALUES ('Sample', 'sample.com', 'note', ... );

INSERT INTO siteLinks (siteID, URL)
VALUES (lastval(), 'test');

COMMIT;

Related:

Reference value of serial column in another column during same INSERT
PostgreSQL multi INSERT…RETURNING with multiple columns


Change default timezone to America Pacific SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
Result: SYSTEM  SYSTEM

 SELECT CURRENT_TIME;
 Result: 06:25:07

  But MY System time is 11.55 AM IST  and PST TIME is 11.25 PM

  How can I change timezone to AMERICA PACIFIC as 11.25 PM in both
  MYSQL Workbench and using python.

I used below query to convert UTC timezone to America Pacific. Field
value would be in timestamp(6).

CONVERT_TZ(field,'UTC','US/Pacific')


Design database to query a date range for scheduling I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made periodically I would like to design the table for them to be as simple as possible.

Every minute I will check table to see if "it's time!" but..

if I put fields like begin_time, end_time in every row, then when I do the query the database engine has to walk all rows, for example with a consult like

SELECT * FROM table WHERE begin_time<=actualTime AND end_time>=actualTime

Perhaps I am thinking as a closer-to-hardware programmer and overthinking but I would like to avoid some naive design that overload the server with a simple scheduler..

Other idea I have is to make a table with ranges alone and a key so if I make a query at the moment of insert perhaps there are repeated ranges and this will reduce the db work.

I've read this related question but doesn't adress same issue and have few answers Unique time range or database schema for a scheduling system so I hope to get some clues on this.

...if I put fields like begin_time, end_time in every row, then when I
  do the query the database engine has to walk all rows, for example
  with a consult like

If you create an index on begin_time and an index on end_time, your dbms should use the indexes to avoid a full table scan. I just tried that with PostgreSQL and a table of 50k rows. It used the index on begin_time, and returned in 0.02ms. A multi-column index (an single index on both begin_time and end_time) might perform even better.

You might want to update a column in those rows to make sure the notification (or whatever) doesn't happen every minute between begin_time and end_time.


When installing a service pack on one instance will this hinder the running of the other instances on a standalone SQL Server? I have a SQL Server 2016 with an instance A and an instance B. Both have SP1 installed, no reporting server.

Can I assume that, when I install SP2 on instance B only, that, while installing, the running of instance A will not be hindered ? (no restarting of the SQL Server service)

Since sqlbrowser is a shared feature, it will also be updated, will this hinder any remote access to instance A (fix port) for the time of the install?

The Service Pack installation process will identify if there are any files in use that will necessitate a restart of the server.

Each instance has it's own independent set of executables, so instance B should not be affected by installation of a new service pack on instance A.

The SQL Server Browser Service will be restarted pretty quickly, however there may be a short period of time where name resolution for named instances may not work as expected.

Best practice for this kind of work on a production server is to schedule it to occur during a period of low or no activity.


Pre-production database replication solution I would like to start by specifying I am no DBA and have basic understanding of advanced DB concepts. I am trying to find the optimal solution to replicate our production database to our pre-production environment automatically.

Our setup is as follow in production:

Batch runs at night and populate staging database
Once complete then our Mart database is loaded using staging data.
Mart data does not change until the next load the following day.
Mart data is currently 200Gb and we have planned growth up to 1TB
over a few years.
DB currently set to simple recovery mode and backup taken every night.

What I would like to achieve is

One off replication of Mart data to separate pre-prod server
Minimum impact on prod server during business hours or if prod impacted this has to be performed outside of business hours.
Ability to run batch in pre-prod on an adhoc basis to test batch changes from recent prod data. This means pre-prod mart cannot be read only.

Currently this process is manual and due to DB size and resources availability cannot always be done as required.
In order to execute performance tests and reconciliation after changes it would be tremendously useful to have a daily process which can achieve this automatically.

Any personal experience or recommendation on the best way to achieve this would help.
Thanks.

My first thought was to restore the backup.  At 1TB this becomes impractical, however.  Log shipping would not allow you to write to the pre-prod instance either.  I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in piecemeal fashion.  I have not tried this; I'm just hypothesising.

Replication will add more load onto the produciton server to read changed values and publish them.  Having the pre-prod copy writable makes things a little more complicated here.

Instead, for the one-off load, I'd suggest you restore a backup of prod.  It may take a while but it will be complete and simple to script with minimal load on the prod box.

For daily updates I'd go with copying the staging table(s) to pre-prod and re-running the load job there.  There will be no additional strain on the production DB.  You can roll back pre-prod and re-run a batch as often as you like, for performance tuning or debugging.  If the staging DB starts each batch empty it would be simple to script a full backup - copy - restore - rerun sequence.  This will be a good way to test future changes to the loader.

We've had good results with change tracking, using it to copy hundreds of thousands of rows per day.  If your staging DB isn't rebuit each day, this may be a good way forward.  It will put a modest run-time load on the instance hosting the staging DB.


Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer declared as favorite in a violation-free and non-blocking manner:

I want to delete as much orphaned toys as possible, while avoiding a foreign key violation because we attempt to delete a toy which is still in use.
I do not want to wait for other ongoing transactions, which are possibly introducing a reference to a toy (which requires a key share lock) or simply updating a toy (which requires a (no key) update lock), to finish. Both lock types block our update lock request, required to delete the toy.

The first, naive approach would be:

delete from toy
where       not exists(select 1 from person where person.favorite_toy_id = toy.id)

This will not work in a concurrent environment: after the completion of the not exists predicate, a concurrent transaction could declare the toy in question as favorite. In such case, we end up with a foreign key violation. Also, as said, I prefer this delete to happen in a non-blocking fashion, which is not attempted in this query.

So, my second approach in attempt to avoid this foreign key violation and any blocking is:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            )

However, this doesn't solve the requirement to avoid the foreign key violation, because the lock is taken after the evaluation of the not exists predicate. So there is a small chance that we attempt to delete a toy which is still marked as favorite, resulting in a foreign key violation.

My third attempt to fix this is the following:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            ) and
            not exists(select 1 from person where person.favorite_toy_id = toy.id)

This applies double-checked locking (https://en.wikipedia.org/wiki/Double-checked_locking). This would work if and only if we have the guarantee that the subquery is always evaluated before the additional not exists predicate. As far as I know, there is no such guarantee.

My question is rather educational: can this be solved in a pure SQL query? We can of course implement this in a plpgsql function as shown below, but let's assume we want to solve this in one single plain SQL query.

create function prune_toys() returns void as
$$
declare
  _id int;
begin
  for _id in select toy.id from toy where not exists(...) for update skip locked loop
    delete from toy where toy.id = _id and not exists(...);
  end loop;
end;
$$
language plpgsql;

In all this, I assume the read committed transaction isolation level.

This is more a business process / UI race. It is often solved by deprecating / hiding values for a period before deleting and/or not accepting them as valid anymore.
One approach would be to add a DateDeprecated column to toys. Update it to the current date when you check and nobody has this toy as a favorite. Clear the field if/when the toy is selected as a favorite. In the UI don't show/list deprecated toys at all or don't show toys deprecated for a certain period of time like over 7 days. At some point delete long deprecated toys, say after they have been deprecated for 14 days.


I have this two models, which one is better? what is a better design??

Model 1:

+------+          +--------+
| type |-|--+     | Data   |
+------+    |     +--------+
| id   |    |     | Mach ID|
| type |    |     | Time   |
| len  |    +----<| type ID|
+------+          | data   | (binary max 8)
                  +--------+

Model 2:

+---------+
| Data    |
+---------+
| Mach ID |
| Time    |
| Type_1  |
| Type_2  |
| ...     |
| Type_n  |
+---------+

(there are other tables too, like machine and client)

all types are previusly defined, ex:

id 08 is the temperature1 and its a float 4 bytes.
id 16 is a sensor and its an int 4 bytes.
id 125 is status 2 bytes.

there is only 22 types defined, but types can grow in the future.
the problem is not all MachID have all the types.
If i do the model 2 a lot of fields will have null.

All i have to do is store these data and display graphics per MachID.
And to make graphs, i only need time vs type. so i can make sql selects to get tables like this

+------+--------+--------+-----+--------+
| Time | type_1 | type_2 | ... | type_n |
+------+--------+--------+-----+--------+
|      |        |        |     |        |

and graph, but getting this is more difficult with Model 1.

thanks for your help :)

ps:
every month i get like 15000 records average per MachID.
and sorry for my bad english

I would use your second model.  The first might be more compact, but would require constantly CASTing your data; the second will have many NULLs, but they take up little space.

The second model looks denormalized at first, but if I understand your model right it's really not.  You're not really packing 22 records into one, rather you're storing reports each of which may have up to 22 distinct strongly-types measurements.  Some reports do not have every measurement, and that's fine.


Materialized Subquery Not Optimized I am having trouble optimizing a materialized subquery in MariaDb 10.0.15 and having difficultly understanding the documentation on whether I can even optimize this query any further.

This query takes a few seconds to run but it seems it should possible to run much quicker than that. I am using "id = IN(subquery)" but have also tried "EXISTS(subquery)". My understanding is that with this version of MariaDB it shouldn't make a difference, and both types of queries take 2+ seconds.

Query:

SELECT rem.id
FROM ri_email_messages rem
WHERE rem.id IN(
    SELECT remc.message_id
    FROM ri_email_messages_contacts remc
      INNER JOIN ri_email_contacts rec ON remc.contact_id = rec.id
    WHERE rec.email = 'email@address.com'
);

Explain result:

+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+
| id | select_type  | table       | type  | possible_keys                 | key        | key_len | ref         | rows   | filtered | Extra                                                        |
+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+
|  1 | PRIMARY      | <subquery2> | ALL   | distinct_key                  | NULL       | NULL    | NULL        |      2 |      100 |                                                              |
|  1 | PRIMARY      | rem         | index | PRIMARY                       | PRIMARY    | 4       | NULL        | 126156 |       75 | Using where; Using index; Using join buffer (flat, BNL join) |
|  2 | MATERIALIZED | rec         | ref   | PRIMARY,email                 | email      | 768     | const       |      1 |      100 | Using where; Using index                                     |
|  2 | MATERIALIZED | remc        | ref   | PRIMARY,contact_id,message_id | contact_id | 4       | orgs.rec.id |      2 |      100 | Using index                                                  |
+----+--------------+-------------+-------+-------------------------------+------------+---------+-------------+--------+----------+--------------------------------------------------------------+

My understanding is a materialized view creates a temporarily table with the result of the subquery, and the id values in the temp table are used to constrain the result in the top query.

If I run the subquery itself, it runs instantly.

If I replace the subquery just with the list of ids that are returned in the subquery, it runs instantly:

SELECT rem.id
FROM ri_email_messages rem
WHERE rem.id IN(105703,110676,111628,105844,105854,105877,110508);

What am I doing wrong here :)

I have now found the source of the issue. It is because my ri_email_messages table was using the Aria engine. I had done this because InnoDB did not support full text search at the time. In MariaDB > 10.0.5, you can now do fulltext search in InnoDB, so I have switched back.

The way to tell in the EXPLAIN statement is that the PRIMARY query was using type=index. This should be type=eq_ref, since it is constrained upon the primary key, but this wasn't happening with Aria. After switching to InnoDB, this correctly shows as type=eq_ref, and the row match went from 126156 to 1.


Compress JSON String Stored in PostgreSQL, such as MessagePack? JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string:

{"0.000295":1544.2,"0.000324":1050,"0.000325":40.1,"0.000348":0}

Question: Is it possible to store it in a format that consumes lesser space? Using some CPU cycles to serialize/deserialize the JSON string is acceptable as a compromise for using lesser storage space. The JSON data does not need to be searchable. The JSON object keys are almost always different in different rows.

I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

Note: Also using Node.js with Knex.js to communicate with PostgreSQL. Currently converting JSON objects to strings using Node's JSON.stringify function.

Individual PostgreSQL fields over 2000 bytes are automatically compressed, for TOAST storage.  However, this compression is not very good, and is local to just that one PostgreSQL value, so can't compress out repetition that occurs between values (like the same keys being used repeatedly, but only once in each JSON structure).  You might want to look at ZSON for compression that uses the whole column for compression context (I've never use it myself).

  I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

You could always compress it on the client, then pass it to and from the database as bytea. Of course then you can't do JSON manipulation inside the database.  Also working with bytea is generally unpleasant.

If you can find an implementation of MessagePack in Perl or Python, you could use those pl languages to allow compression and decompression inside the database.


How To identify old and new value of a column in a same row while using GridDB Is there any script to identify the OLD and NEW value of a column (last updated), output need in a single row with two different columns with OLD_Value and New_Value like this?
I am using GridDB on the Windows platform.

Answered by mustaccio in a comment:
This can only be done while the row is being updated, in a trigger.


Join 3 tables of a one-to-many relationship I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles (GIS data format), they are essentially database tables with rows and columns that have spatial geometry such as point, line, polygon etc., which can be displayed visually and used in calculations in a GIS software.

trucknetwork (shapefile) - 564 rows (column sri links to table1) - specific truck road network in NJ with no road names:

gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass),
id numeric(10,0),
sri character varying(20),
mp_start double precision,
mp_end double precision,
descriptio character varying(50),
road_type character varying(50),
geom geometry(MultiLineStringM),
CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)

table1 - 488715 rows (links to shapefile with sri column and table2 with seg_guid column) - start and end points of all NJ roads:

gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
sri character varying(20),
route_type numeric,
sld_mp_st numeric,
sld_mp_end numeric,
CONSTRAINT table1_pkey PRIMARY KEY (gid)

table2 - 669557 rows (links to table1 with seg_guid column) - all NJ road names:

gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
s_name character varying(254),
CONSTRAINT table2_pkey PRIMARY KEY (gid)

I want to join these 3 tables into 1 table - with the result being 564 rows of each road segment with its correct name. It is a one-to-many relationship.

I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software.

I have tried left join, inner join - and I have tried the group by but for every query I keep getting over 100,000 + records

select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name
      into networkEX
from trucknetwork as shp
   join table1 as t1 on shp.sri = t1.sri
   join table2 as t2 on t1.seg_guid = t2.seg_guid;

I am not sure what the logic should be. Subquery? Do I have to break this up into smaller steps?

Obviously, there can be multiple rows with the same value in table1.sri and / or in table2.seg_guid. That's the only logical explanation for the multiplied row count in your query result.

Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.

My educated guess is that the multiplication happens in table1 (many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL join with LIMIT 1 is a hot contender. Be sure to support it with an index.

Since you want to join to exactly 1 row in the result for each row in trucknetwork, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.

And there is also the possibility that some of the rows in trucknetwork might find no matching row in table1 and/or table2 at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY constraints. To make do with what we have, use LEFT [OUTER] JOIN to avoid eliminating trucknetwork rows (which would probably indicate data errors in table1 or table2).

So:

CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name
FROM   trucknetwork shp
LEFT   JOIN LATERAL (
   SELECT seg_guid
   FROM   table1
   WHERE  sri = shp.sri
   -- ORDER  BY ???      -- define which one to pick if that matters
   LIMIT  1              -- ! just the one !
   ) t1 ON true
LEFT   JOIN table2 t2 USING (seg_guid);

Ideally, you would have these two multicolumn indexes to make this fast:

CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);

If there can be duplication in table2 (as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.

Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
Optimize GROUP BY query to retrieve latest record per user

Asides:

Use CREATE TABLE AS. SELECT INTO is discouraged:

Creating temporary tables in SQL

Avoid unquoted CaMeL-case identifiers in Postgres.

Are PostgreSQL column names case-sensitive?


How can I "shift" certain column values to different rows in an existing table? I'm implementing a queue as a ring buffer in a table along the lines of what Thomas Kejser talks about here: Implementing Message Queues in Relational Databases If the ring buffer fills up, I have to increase the number of slots and possibly shift some of the values around. This is part of my routine to add slots to the ring buffer

I have a table that looks like this:

SlotId    SlotVal1  SlotVal2
1          3           3
2          4           4
3          1           1
4          2           2
5          NULL        NULL
6          NULL        NULL
7          NULL        NULL
8          NULL        NULL

I would like to "shift" the values from slots 3 and 4 forward by 4 slots to rows 7 and 8 so the table would look like this:

SlotId  SlotVal1 SlotVal2
1       3        3
2       4        4
3       NULL     NULL
4       NULL     NULL
5       NULL     NULL
6       NULL     NULL
7       1        1
8       2        2

I tried two different UPDATE queries to shift the rows forward and then clear the previous rows.  The first query (as reproduced below) doesn't update any rows in my table and I'm drawing a blank on what I must have left out:

        -- shift slot values forward
        DECLARE @firtRowToMove AS int = 3;  -- slot id of the first row to move
        DECLARE @rowsToShift AS int = 4; -- number of rows forward to shift
        UPDATE [SlotTable]
        SET [SlotVal1] = (SELECT [SlotVal1]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift)),
            [SlotVal2] = (SELECT [SlotVal2]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift))
        WHERE [SlotId] >= (@firtRowToMove + @rowsToShift);

You need to qualify SlotId in your sub-query with SlotTable or it will use the SlotId from the sub-query itself.

UPDATE [SlotTable]
SET [SlotVal1] = (SELECT [SlotVal1]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift)),
    [SlotVal2] = (SELECT [SlotVal2]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift))
WHERE [SlotId] >= (@firtRowToMove + @rowsToShift);

SQL Fiddle

BTW, your CTE version does not work either

Another way to do what you do in this update statement a bit more efficiently (at least in my tests).

update s1
set SlotVal1 = s2.SlotVal1,
    SlotVal2  = s2.SlotVal2
from SlotTable as s1
  inner join SlotTable as s2
    on s2.SlotId = s1.SlotId - @rowsToShift
where s1.SlotId >= @firtRowToMove + @rowsToShift


Collation conflict when running dynamic sql FOR XML PATH I am getting the following error message when running the query below:

  Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation

This query works fine and basically gives me the scripts to CREATE USER in my current database.

In order to run the query in ALL (or a selection) of my databases in the current server, I have it done through dynamic SQL and for XML PATH.

The problem is,
when I uncomment the XML part of the script below, I get the error above.

It is not in ALL servers that I get this error. Some servers work fine, others are bogged.

I want this query to work in ALL servers, independently of server collations.

How can I achieve this?

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name]=[master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

--SET @SQL = (
--          SELECT STUFF(
--  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf
--                              FROM sys.databases SD
--                              --INNER JOIN @DBS D ON SD.NAME = D.DB
--                              WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
--                                AND SD.COMPATIBILITY_LEVEL > 80

--    FOR XML PATH(''),TYPE)
--  .value('text()[1]','nvarchar(max)'),1,2,N'')
--)

EXECUTE MASTER.DBO.sp_executesql @SQL

HERE IS THE WORKING VERSION

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' COLLATE Latin1_General_CI_AS +  [master].[sys].[server_principals].[name] + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] COLLATE Latin1_General_CI_AS = [master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf
                                FROM sys.databases SD
                                --INNER JOIN @DBS D ON SD.NAME = D.DB
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND SD.COMPATIBILITY_LEVEL > 80

      FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

EXECUTE MASTER.DBO.sp_executesql @SQL

Another method would be to use COLLATE DATABASE_DEFAULT vs specifying the collation explicitly.

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @sql = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] collate database_default + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] collate database_default + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @sql = (
          SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @sql + @vCrlf
                              FROM sys.databases SD
                              --INNER JOIN @DBS D ON SD.NAME = D.DB
                              WHERE  SD.state_desc = 'ONLINE' -->Skips the database if it is not online
                                AND SD.compatibility_level > 80

    FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

--EXECUTE master.dbo.sp_executesql @SQL
print @sql

Above query will produce below print statement :

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
'CREATE USER ['
 + [sys].[database_principals].[name] collate database_default + ']' + ' FOR LOGIN [' + [master].[sys].[server_principals].[name] collate database_default + ']'

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in ('U', 'G', 'S')


Mount mdf file to sql server desktop edition I'm trying to attach a .mdf file to a SQL Server Desktop Edition.

After reading this I connected to the database using the

osql -E -S servername\instancename

command.

I tried to attach the .mdf file with the following command

EXEC sp_attach_db @dbname = N'mydb',
     @filename1 = N'C:\Backup\mydb.mdf',
     @filename2 = N'C:\Backup\mydb.ldf'`

and then I get the following error.

  Msg 602, Level 21, State 50, Server serverName, Line 1
  Could not fine row in sysindexes for database ID 5, object ID 1, index ID 1.

I don't really know what any of this means I have never used osql before.

I suspect this is not a SQL Server 2000 database you are attempting to attach to SQL Server 2000. That appears to be a common cause of the error as indicated here also.

Can you attach this to SQL Server 2005 or 2008 Express and see what error message you get? Do you have access to the underlying database that your files are from?

I imagine if you attach this to SQL Server 2005 or 2008 you will be able to attach it unless there are other impediments to attach going on that that approach will reveal.


update values everyday I have a table with 2 columns. Days is the days from Start Date to today.

How should Trigger be to automatically increase Days every day?

Start Date      Days
19/07/17         19
25/07/17         13
07/08/17         0

Triggers only work on the rows which are affected by the statement causing the trigger to execute.

When you want to update a whole table, use a normal statement, not triggers.

When you have data, that can be easily calculated, don't store this in a table. Either you calculate it in your SELECT statement or create a view.

Example:

CREATE VIEW view_with_day_difference_calculated AS
SELECT start_date, TIMESTAMPDIFF(day, start_date, NOW()) AS days
FROM your_table;

Then all you have to do is this in the future.

SELECT * FROM view_with_day_difference_calculated;


How to update one column with the value of another if one of their columns match I've found a lot of similar questions, but not exactly what I need. I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in the note of one of them.

In the example below I would like to update row 2,4 and 6 with the words "do not email" in the note.

ID | email | note
--------------------
1 | A | do not email
2 | B |
3 | C |
4 | A |
5 | D |
6 | A |
7 | B | do not email

Does anyone have any hints?

This will update all rows in the target table that match an email address in the same table that has been marked as 'do not email'

update a
   set a.note = 'do not email'
  from tbl as a
  join (select distinct email
          from tbl
         where note = 'do not email'
       ) as b
    on a.email = b.email
 where a.note <> 'do not email'
    or a.note is null;


Issues Designing ER Diagram I'm trying to design an ER diagram in the relational database module of my course. We were given a scenario to draw a diagram for and then create a database within Access. I have the database covered, but want to be sure I'm on the right track for the diagram.
Scenario:

The model is for business functions within a car dealership. Minimum
and maximum cardinalities must be indicated. Entities and processes to
be considered include Customer; Cash Received; Cash; Salesperson; AR
Clerk; Sell Vehicle; Inventory.
A few assumptions must be made about resources and the payment event. Assume the company only sells inventory that is in stock, thus
customers will not be ordering vehicles and every sale is only for one
vehicle. Inventory is categorized based on model information.
Additionally, assume that the dealership has multiple cash accounts
and that customer payments are always deposited in the same cash
account. Since the dealership offers financing, customers can make
monthly payments on their vehicles so customers can buy now and pay in
installments later. Any other assumptions that need to be made should
be consistent with the business processes in a typical auto
dealership.

My answer (I don't believe I have a firm grasp on this at all -- I also don't have the ability to embed pictures yet):
https://i.stack.imgur.com/iZrv9.png
Thank you in advance for your help!!

Is that the whole problem statement?  How come AR Clerk, Salesperson, etc are not described anywhere?  You shouldn't just include only the entities listed.  The italicized paragraph contains more entities and processes that should be modeled.
You can't have 2 Customer entities in the diagram.  Draw another line from Customer to Receive Cash if you need to.  You should use a few sentences to define each entity so others know what you intend them to be.


How to revert multiple impersonations? When there are nested impersonations (as in exec as user = 'user1'; exec as user = 'user2'; exec as user = 'user1';), is there a way to revert all of them without having to use the revert command multiple times (e.g. revert all;)?

Or at least, if one must use revert repeatedly, is there a way to check if there is an active impersonation so one could loop the command as many times as needed (e.g. while @@IMPERSONATION_COUNT > 0 revert;)?

You could assign the current context to a cookie then revert to that cookie when you desire to.


Why does formatting a time with AM/PM specifier return null? I have a query where a formatted expression is returning null:

declare @FormatString nvarchar(max) = N'hh:mm tt'
select M_STARTTIME as [Memo Time 1]
    , convert(time,M_STARTTIME) as [Memo Time 2]
    , format(convert(time,M_STARTTIME), @FormatString) as [Formatted Memo Time]
    , convert(time, getdate()) as [Current Time]
    , format(getdate(), @FormatString) as [Formatted Cur Time]
from MEMO
where M_STARTTIME is not null

M_STARTTIME holds an nvarchar field with values such as '10:27', '13:01', '8:25' and null. In this query:

[Memo Time 1] displays the string field
[Memo Time 2] displays the memo time as a time field correctly
[Formatted memo time] returns a null - for all rows
[Current Time] shows the current time
[Formatted Cur Time] shows the current time formatted using the format specifier.

According to MSDN, FORMAT returns null for errors other than a culture that is not valid. For example, null is returned if the value specified in format is not valid. I have not been able to identify where the error is though (I'm assuming i've missed something really obvious, or the issue is something really subtle).

Converting the nvarchar to a time should not be the problem, the time displays correctly in column 2. Formatting using the time specifier should not be the problem, since I use the same time specifier on another time value.

Interestingly, according to example D in the MSDN link (time values) that time format specifier should cause an error because the ':' is not escaped. If that is the case why is [Formatted Cur Time] working? Also, if I escape the format specifier with:

declare @FormatString nvarchar(max) = N'hh\:mm tt'

then the [Formatted Memo Time] still returns null and the [Formatted Cur Time] still returns the current time.

dbcc checktable (MEMO)

...identified no issues

What is going on?

Edit: tt is a valid format specifier for datetime, but not time. The \ is also optional in datetime, but not time. Example at end.

I think the tt is the problem
I am not finding tt for time formatting - what is it supposed to do?

Did you try without using @FormatString?
Below works

declare @FormatString nvarchar(200) = N'hh\:mm'
declare @TimeChar nvarchar(200) = N'10:27'
select @FormatString
select @TimeChar
select convert(time, @TimeChar)
select format(convert(time, @TimeChar), N'hh\:mm')
select format(convert(time, @TimeChar), @FormatString)

Example:

select format(convert(time    , '10:27'), N'hh\:mm')   -- returns 10:27
     , format(convert(time    , '10:27'), N'hh:mm')    -- returns NULL (need to escape ':'):
     , format(convert(time    , '10:27'), N'hh:mm tt') -- returns NULL
     , format(convert(datetime, '10:27'), N'hh:mm tt') -- returns 10:27


Person unique identification I am working on a sports database.
I have table for persons(players), teams, leagues etc.
The problem is, how do I identify a person uniqely? I was thinking that a new person could be added by either league organizer, team, or the player themselves, by registering to the website with this database.
One player can play in multiple leagues. So when they are already in the database and a new league will want to add him again, there will be a duplicate.
The only solution I can think of is using the persons national ID, as there potentially could be two persons with the same name, birthplace, and birth date.
Another solution I can think of is alerting the user that there is already a person with the same or similar data. Then they can check if it is really new person or just a duplicate. However, this doesn't work well as the user could still add duplicates.
What are your opinions on this problem? Can you give me some advice?

This isn't really a database administration question, but one about your business rules (real or imaginary). If you look at the internets, the most common way to uniquely identify users is by their email addresses or usernames (handles, aliases etc.).
Storing national IDs, which constitute sensitive private information, is a bad idea, as  your application then will need to comply with all sorts of regulations that apply to the processors of SPI.
Allowing users to view other users' data is an even worse idea, as that would be a direct violation of various privacy laws.


Monitor network traffic per client I'm investigating intermittent performance issues on a database running MariaDB 10.4 and I notice extraordinarily high network traffic.  Bytes_sent is about 20TB after running for 58 hours (379GB per hour).  There are only 8 clients, and typical traffic is expected to be about 2GB per hour.  Is there some way to monitor bytes_sent per client to help track down the issue?

MariaDB has a user statistics plugin that provides a breakdown per user.
It can be enabled dynamicly:
SET GLOBAL userstat=1;

And examined:
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G

It seems like such a increase is potentially a brute force attempt or some abnormal use. I'd look at the SHOW PROCESSLIST and see if the queries being run are extraordinary and/or you'll probably see there a client with more connections than expected.