Row numbering difference between mariadb and mysql I've recently updated my server into a docker setup and switched from mysql to mariadb. No I've run into an issue with different behavior between Mysql 5.7 on my local machine and MariaDB 10.5.6 on the server.
The problem is a different result for a query which should return row numbers.
I'll explain using a minimal working example
Consider the following two tables:
>SHOW CREATE TABLE cars\G
*************************** 1. row ***************************
       Table: cars
Create Table: CREATE TABLE `cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

>SHOW CREATE TABLE used_cars\G
*************************** 1. row ***************************
       Table: used_cars
Create Table: CREATE TABLE `used_cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `price` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

And some example data:
INSERT INTO `cars` (`brand`, `model`, `value`) VALUES
('Volvo', 'S40', 1),
('Porsche', 'Carrera', 4),
('Opel', 'Corsa', 3),
('Renault', 'Clio', 1),
('Renault', 'Megane', 3),
('Volvo', 'V40', 2);
INSERT INTO `used_cars` (`brand`, `model`, `price`) VALUES
('Volvo', 'S40', 2500),
('Porsche', 'Carrera', 7500);

Let's say I want to run a query which lists all cars and returns whether there is a used car in stock. And I want these to be ordered in a certain way:
SELECT (@row := @row + 1) AS score,
   qry.brand,
   qry.model,
   qry.used_present
FROM
  (SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars ON used_cars.model = cars.model
                          AND used_cars.brand = cars.brand) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC

this returns the following in MySQL
+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     1 | Porsche | Carrera |            1 |
|     2 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

While I get the following in MariaDB
+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     2 | Porsche | Carrera |            1 |
|     1 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

Can anyone help me how to fix this? In reality the innerquery qry is much more elaborate, but the results are similar as this MWE. Adding the ORDER BY to the innerquery doesn't change the results.
db<>fiddle

From a logical perspective, ORDER BY is applied after (@row := @row + 1), so which row gets which number is non-deterministic. However, there may be something in the documentation, that guarantees that assignment of @row respects the ORDER BY. In that case, you should file a bug, if not I would not trust that it does.
I really don't understand why you are developing against an ancient DBMS, and running a much newer version in production, I think you should upgrade your local DBMS so that you have the same version.
Upgrading and using window function will get you out of trouble:
SELECT row_number() over (order by qry.used_present DESC, qry.brand ASC) as score,
    qry.brand,
    qry.model,
    qry.used_present
FROM (
   SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars
       ON used_cars.model = cars.model
      AND used_cars.brand = cars.brand
) AS qry
ORDER BY qry.used_present DESC
       , qry.brand
;


Will auto_increment always grow in master-master setup? Let's suppose I have mysql master-master replication setup with only one server active at a time (i.e. all writes going to one server), and
I have ability to manually switch to another master.

Next, I have auto_increment_increment and auto_increment_offset setup:

Master1

auto_increment_increment = 2
auto_increment_offset = 1

Master2

auto_increment_increment = 2
auto_increment_offset = 2

Master1 is now active master, and I have a table "users", and insert few recrods:
1,3,5,7

then I switch to Master2, all writes going to Master2.

Question is: will next inserted record to users have auto_increment ID "2" or ID "8" ? Where is this documented?

Auto Increment ID is "replicating" to the slaves. Otherwise it would be hell of a job to promote a slave to a new master if the status of auto increment needs to synced. So to answer your question it would start from 8.

If you do not write actively to both master at the same time you don't need to do any auto_increment_* settings trickery. These settings are meant to avoid situation where the two master picks the same ID for new rows inserted concurrently on both masters which would lead to conflicting IDs while applying changes from the relaylog.


RMAN backup script failing I have a few linux machines and each of them have a few Oracle databases. On each machine we have 2 shell scripts to run the backups. The TSM scheduler calls a script which calls a different backup script for each DB on the machine.

The problem is if I create an array of SIDs and create a loop through the SIDs the backup fails.

#!/bin/bash

LEVEL="$1"

declare -a SIDS=('SID1' 'SID2' 'SID3')
su - oracle

for SID in ${SIDS[@]}
do
  ORACLE_SID=$SID; export ORACLE_SID
  export ORACLE_HOME='/u01/app/oracle/product/10.2.0.2'
  ORAENV_ASK=NO
  . /usr/local/bin/oraenv
  LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
  /usr/local/adm/backup-incrn.sh $LEVEL $SID 35
done

There is no error in the Logs on the TSM server and it is almost as if the scripts is never called. The logs that are on the machine are not updated with any error messages.

If I use this syntax the backups work perfectly

su - oracle -c "/usr/local/adm/backup-incrn.sh $LEVEL SID1 35"

su - oracle -c "/usr/local/adm/backup-incrn.sh $LEVEL SID2 35"

I think this has to be an error in my script, some variable not being set properly.

Can anyone see the obvious mistake I've made and point it out to me?

Gísli

I would guess that because you're issuing a login your declaration of SIDS is not transferred to the shell started by the su command.  You can test this by checking the value of SIDS after you issue the "su - oracle"


How to synchronize two MySql tables using command line I have two tables in the same database (MySql) where one has been populated and there are data been added to it frequently.

I want to have the data added to the first table to be present in the other (synchronized) using a MySql command in console (without any other methods such as triggers, events etc.)

Is this possible and if so what is the command?

This can be achieved using a mysql replace into feature provided you have either primary key or unique key implemented on both table to eradicate duplicates. Replace into have similar syntax as of insert into as when key is present on table it first delete the old row and insert the coming one.
mysql>Replace into table2 select * from table1

Either have cron job or you can use mysql event scheduler to populate the data to other table according to your desired timings.
Hope it helps


Change Data Capture in a services environment Change Data Capture looks to be exactly what I need... Except.

All of my database actions come from users that system accounts.  We have WCF service that is called by an application.  The WCF Service does the authorization for the call, then the system account that the WCF Service runs as makes the needed call to the database.

This allows us to tightly control permissions on the SQL Server.

Because of this, Change Data Capture is only going to be recording that generic user's information.

But I need to know the actual user that initiated the WCF Call.

Is there a way to plug in an alternate user name for Change Data Capture?  Or another way to get Column level logging of who made a change and when?

So, after a bit of thought, I realized that I can just treat my User Information as another piece of data.

So I can add to each table a "ChangedBy" and a "ChangedWhen" column.  Whenever my service updates a record, it will update the ChangedBy and ChangedWhen.   Then in the Change Data Capture history, the changes include needed data.


Is it always safe to use WITH SCHEMABINDING in a UDF? SO I have been reading about WITH SCHEMABINDING and how it can improve the performance of queries using a scalar UDF by omitting the table spool operator from the execution plan. I think I understand halloween protection.
My question is: If I add WITH SCHEMABINDING to a UDF used in a SP is it possible that a SP does not give the same results? If yes in what scenario?

In fact it's the other way around. If you check the Best Practices section of the CREATE FUNCTION doc, you'll see that:

If a user-defined function is not created with the SCHEMABINDING
clause, changes that are made to underlying objects can affect the
definition of the function and produce unexpected results when it is
invoked. We recommend that you implement one of the following methods
to ensure that the function does not become outdated because of
changes to its underlying objects:

Specify the WITH SCHEMABINDING clause when you are creating the    function. This ensures that the objects referenced in the function
definition cannot be modified unless the function is also modified.


What happens when the buffer pool size is insufficient in mysql Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer pool or will mysql fetch the data from the disk ?
Just curious to know the behaviour of mysql in this scenario.

If MySQL properly started and InnoDB initialized with the configured buffer pool size then there is no reason for any error (barring some bug of course). The buffer pool is just that - a memory area used to buffer your IO. When there is not enough of it to keep all the active data, the server just reads them from disk.

If you had for example 100GB table then many servers won't have enough RAM to contain it and still you may execute a query running full table scan.

The performance will be worse (probably substantially, 1-2 orders of magnitude or more) than in case there were enough memory.


Open a Oracle 7.3.x database in Oracle 11 Is there any way to open or convert an old Oracle 7.x database into Oracle 11g?
Thanks in advance!

No, you have to upgrade in stages, which wouldn't actually be possible now because Oracle 8.x and 9.x are now unsupported and the software is unavailable (unless you have an Oracle support contract and ask them for copies of the software). Read the Oracle® Database Upgrade Guide
11g Release 2 (11.2) for more information.

Your best bet, assuming you have the 7.x database up and running, is to use exp to export the schemas you require, then import them into a fresh 11.2.x database.


Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008? I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when changing field types?

I have a table with about a million records that has several VARCHAR type fields.  I would like to change these to NVARCHAR, but as I understand it, this will take some time and resources, as the fields are in the middle of the table, and SQL Server has to do a bunch of copying/re-ordering.

What is an efficient way of accomplishing this?

One way might be to:

Add a NULLable NVARCHAR column
Using batches, update a number of rows at a time (e.g. 1000 or 10000 rows)
Backup the log, checkpoint, what have you in between batches
When all the rows have been updated, drop the old column and rename the new one
Rebuild indexes

This won't be faster in the long run, and still requires a maintenance window (since you don't want users updating rows you've already updated unless you put a temporary trigger in place to counter that), but it will prevent a huge transaction and after a few updates will give you more predictability about how long it will take.

You could do the same thing by creating a new table, and renaming that once it's done... while this avoids the need for step 5, it would cause even more data churn and can be more problematic due to constraints, foreign keys, triggers etc. that might be involved with the table.


Setup innodb_io_capacity I want to configure the innodb_io_capacity option for my MariaDB server. In the documentation I found the following:

  Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

Does anyone have experience in configuring the value? In what unit is the value measured? Is it IOPS?

For example my setup is:

Samsung NVMe SSD

Reading 500.000 IOPS
Writing 400.000 IOPS

My current value for innodb_io_capacity is 2000 and innodb_io_capacity_max is set to 3000.

Am I right in thinking that I can set innodb_io_capacity up to 500.000, because the hard disk has such a high IOPS value (minus X because of some operation system processes)?

How does the innodb_read_io_threads setting affect the innodb_io_capacity parameter?
My current value is 16 threads (having Intel Xeon E5-1660v4 [8x 3.2 Ghz])

Basic Google-Fu produced the following information:

  The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.

I wouldn't set this to the maximum IOPS value of your SSD disk as you need IOPS for InnoDB foreground tasks. This is also commented on in the link that can be found just underneath the documentation for the parameter.

  For information about configuring the innodb_io_capacity variable, see Section 14.8.8, “Configuring InnoDB I/O Capacity”.

This lead to:

  The InnoDB master thread and other threads perform various tasks in the background, most of which are I/O related, such as flushing dirty pages from the buffer pool and writing changes from the change buffer to the appropriate secondary indexes. InnoDB attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the available I/O bandwidth and tune its activities to take advantage of available capacity.

  The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.

  You can set innodb_io_capacity to a value of 100 or greater. The default value is 200. Typically, values around 100 are appropriate for consumer-level storage devices, such as hard drives up to 7200 RPMs. Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

  Ideally, keep the setting as low as practical, but not so low that background activities fall behind. If the value is too high, data is removed from the buffer pool and change buffer too quickly for caching to provide a significant benefit. For busy systems capable of higher I/O rates, you can set a higher value to help the server handle the background maintenance work associated with a high rate of row changes. Generally, you can increase the value as a function of the number of drives used for InnoDB I/O. For example, you can increase the value on systems that use multiple disks or SSDs.

Carrying on to the innodb_read_io_threads parameter...

  InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages using the innodb_read_io_threads and innodb_write_io_threads configuration parameters. These parameters signify the number of background threads used for read and write requests, respectively. They are effective on all supported platforms. You can set values for these parameters in the MySQL option file (my.cnf or my.ini); you cannot change values dynamically. The default value for these parameters is 4 and permissible values range from 1-64.

  The purpose of these configuration options to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread, to increase the chances of coalescing the requests. If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.

I interpret this as the parameters not being really related to each other. Each parameter can be configured individually based on the performance of the system.


Postgresql responses are slow without internet I have a Postgres service in Centos 7 and it works normally.

Each time when the internet service (by an IPS) is down, the responses are slow, other times the DNS is reset by an electrical trouble, forcing to set 192.168.2.1, and the responses are slow too.

Trying to solve the trouble, I change the DNS (192.168.2.1 - this is my lan dns) to [8.8.8.8] (google's one) and the responses comes back to work normally.

Anyone can tell why this happens?
Have I configured something wrong in the installation?

Note: this happens in 15 database servers (one per store).

something is doing dns lookups on your connections, check your logging settings are not logging domain names, and ensure that pg_hba.conf contains no host names (only ip addresses)

perhaps use wireshark or tshark to check for DNS traffic on the database server and see what the lookups are.


Sybase query: How can I sort the extraction (by well formed columns) community
I built a Sybase query statement in a script (using the isql command/line ) below is the code:

My script contains:

 isql -U databasename_dba -P password  <<EOF!
    select  customerid, notional, counterparty2 from table_1  inner join table_2
    on id = id2
    go
    quit
    EOF!

I'm getting this:

My desired output would be:

Thinking that extracting just the data (excluding the headers) might fix the output. I added in my code -b

isql -U databasename_dba -P password  -b <<EOF!

But the output doesn't change that much:

Thanks in advance for your ideas

Try adding-w parameter, this would dictate size of window. You may use -w99999 and output can be copied to a text editor. You could see that in the correct or desired format.

This seems to be working and hence adding it as an answer.


How to update a UID throughout the database? Our database makes use of uniqueidentifier and we have the need to update that value everywhere it's found in our system.
Sample minimal structure:
CREATE TABLE Users
(
    id uniqueidentifier not null default newsequentialid() primary key,
    [name] nvarchar(100) not null
)

CREATE TABLE Tasks
(
    id uniqueidentifier not null default newsequentialid() primary key,
    [name] nvarchar(max) not null,
    userId uniqueidentifier not null foreign key references Users(id)
)

DECLARE @users TABLE(id uniqueidentifier)
DECLARE @uid uniqueidentifier

INSERT INTO Users(name) OUTPUT INSERTED.id INTO @users VALUES('Jim Bob')
SET @uid = (SELECT TOP 1 id FROM @users)
INSERT INTO Tasks(name, userId) VALUES('Some task', @uid)

P.S. Obviously I have many more tables
I know how to search all database tables for a given value (I've used queries similar what's written here this before). I'm unaware of a simpler solution for uid's if any exist.
With the above, is there a simpler way than looping over tables, then columns of datatype uniqueidentifier, then locating the uid created in order to update its value to say 0x0?

Our database makes use of uniqueidentifier and we have the need to update that value everywhere it's found in our system.

Question: Why?
The whole point on using an arbitrary, sequential identifier is that they should never change them over the entire lifetime of a record, from the point they're created until they are finally deleted.  If you do find you need to change them, then something is badly wrong with the way you're using them.
Also, don't query the "largest" value to find out the id value that MySQL generated - it may not work correctly in a multi-user environment.  Use the provided LAST_INSERT_ID() function instead:
INSERT INTO Users( name )
VALUES( 'Jim Bob' );

INSERT INTO Tasks( name, userId )
VALUES( 'Some task', LAST_INSERT_ID() );   <-- gets the last inserted User


What query can give me this result? I have these three tables:

Table1

pro_id(Pk)  pro_name
1           ABC
2           def

Table2

pid pro_id
1   1
2   1
3   1
4   2
5   2
6   2

Table3

pro_id  code
1       dc23
1       ef45
2       456F
2       4er

How can I write a query that would give this result?

pro_id    noof pids  noofcodes
1         3          2
2         3          2

The following would do it. SQL Fiddle

WITH T2
     AS (SELECT pro_id,
                COUNT(pid) AS [noof pids]
         FROM   Table2
         GROUP  BY pro_id),
     T3
     AS (SELECT pro_id,
                COUNT(code) noofcodes
         FROM   Table3
         GROUP  BY pro_id)
SELECT T1.pro_id,
       COALESCE([noof pids], 0) AS [noof pids],
       COALESCE(noofcodes, 0)   AS noofcodes
FROM   Table1 T1
       LEFT JOIN T2
         ON T1.pro_id = T2.pro_id
       LEFT JOIN T3
         ON T1.pro_id = T3.pro_id


What makes up "Queries per second avg" in MySQL When running the status command in MySQL, one of the metrics that is sent back is "Queries per second avg: 5910.758"

I was wondering if anybody know what makes up that number?

Reason for asking is we tracked all the selects, updates, inserts from the performance_schema and we don't get anywhere near that number.

Tracing it through it ends up being a COM_STATISTICS in the protocol, resulting in this bit of server code.

So based this 'Queries per second avg' is the, the thread_id as the number of queries, divided by the total uptime of the server.


How to change table name in MySQL? i want to change table name in mysql it seems easy but every times gives a error

I want to change table name
    from qwerty to qwerty_2

So, how can i do so?

Any help will be appreciated thanks

There are two ways

ALTER TABLE qwerty RENAME qwerty_2;

or

RENAME TABLE qwerty TO qwerty_2;


SQL Server shrink command 'estimated_completion_time' keeps going up Running SQL 2012 R2...

Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)  Standard Edition (64-bit)

Ran the shrink command

DBCC SHRINKDATABASE(databaseNameHere)

(Yes, I know this shouldn't be done regularly, if ever... we have a 200 GB database and cleared out many years worth of data, and should be able to reclaim about 100 GB of space)

When I checked the status of the task at 1.5 hours, it was 49.1xxx percent_complete.

It's been running for 2.5 hours... and now at 49.5xxx percent_complete.

Additionally, just in the last 20 minutes, the estimated_completion_time (found in sys.dm_exec_requests) has gone from 8,741,035 milliseconds to 9,385,086 milliseconds...

There is still a ton of space available on the drive.  It is a development/test database that nobody is using... so whats the deal? why does the estimated time keep increasing?

I've been using sp_who2 active to verify there are no blocks...

SHRINKDATABASE and SHRINKFILE won't actually release the space to disk until the very last moment: it has to move all the contents around within the files first (which is the part that takes a long time).

For why the progress doesn't seem constant: the free/used space is spread out across a large file, so it is going to "skip ahead" when it encounters an empty patch and "slow down" when it hits a section of used pages.

As mentioned in the comments, I would highly recommend using SHRINKFILE instead of SHRINKDATABASE, since you can control the target sizes of each individual file, and give each one a reasonable target. For example, I usually try to leave 15-25% free space in each data file.


Relational modeled design to store versioned files The scenario

I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the directory path which the images are stored, if a original image is stored in dir : 1, the altered version of this exact image would be dir : 1-1.(This I am thinking has room for improvement but I am unsure of anyway of doing it(BLOB and storing within the db is out of the question).

Current model

 Files
+---------+-----------+-----------+--------+
| file_id | file_name | file_hash | folder |
+---------+-----------+-----------+--------+
|    1    |  bmw.png  | d29djkj38 |    1   |
|    2    |  bmw.jpeg | e9sljd551 |    1   |
+---------+-----------+-----------+--------+

Altered_Files
+---------+------------------+
| file_id | original_file_id |
+---------+------------------+
|    2    |         1        |
+---------+------------------+

file_id and original_file_id here are both foreign keys to Table : Files Column : file_id.

file_id is also the Primary Key

I have never used two columns as foreign keys where both reference the same one column in another table, this makes me wonder if its a incorrect design(or perhaps its not even allowed architecturally).

As for the altered images directory folder, we can generate this by joining both tables and then using contact('-') with the results.

Overall concern

I am still new to Database modelling and wondering if this design is ok(won't need refactoring at a later date as the pros say(Edgar Codd, C Date)), perhaps it can be improved even more?

Also what do you suggest for the image directory issue, integrity is not enforced in the database but with this idea it seems possible to maintain with application code and permitting access to the main directory to only the db.

Your Altered_Files design is absolutely fine.  In fact I would award bonus points for separating the files per se from the hierarchy.  @chillworld's suggestion is workable and very common but yours is better because of the NULL issues you mention.  I can't think of any update which is better in one schema or the other.  Change a single row - either the file data or the files' relationship - and you have a single update on a single table.  No risk to integrity there.  Altered_files contains only primary keys (PK) and pointers to PKs.  By definition these should never change.  On the off chance you should have to change one then, yes, you have to be very, very careful to maintain consistency.  For INSERTs and DELETEs wrap the statements in a transaction.  This is precisely why transactions exist.

I would suggest you store the full directory path for an altered image, not just its new sub-folder.  What happens if you derive image 4 from image 3, from image2, from image 1?  Will contact('-') handle that?  Will the continuous constructing of image 4's ancestry be a burden on the system?  You know your data, workflow and hardware best.  If the volumes are small this may not be an issue.

The file-in-database-or folder is an age-old one.  SQL Server has functionality called FILESTREAM where the image passes through the database on the way to the file system.  It is held on disk as a JPG or PNG or whatever and can be read by normal applications.  The database retains ownership of it and its related data.  The best of both worlds.  I don't think MySQL has anything similar, however.  I think you may want to grant other applications read access to the image filestore and reserve write access for the DB.


Is there a way to track page splits on an InnoDB table? Is there a way to track the number of page splits on an InnoDB table? I've inherited a couple high-volume tables with integer primary keys in which values are often inserted out of order and I'd like some indication of how much my insert performance is suffering due to page splits from out-of-order inserts. I know SQL Server exposes metrics on that, but I've been unable to find anything in the MySQL docs about that.

Here's the schema for the table with the highest insert volume. Even though the PK is defined as AUTO_INCREMENT, the keys are manually inserted out of order:

CREATE TABLE `data1` (
`data1_id` bigint(20) NOT NULL AUTO_INCREMENT,
`uuid` char(36) DEFAULT NULL,
`headline` varchar(255) DEFAULT NULL,
`status` varchar(10) DEFAULT NULL,
`starred` tinyint(1) DEFAULT '0',
`invalid` tinyint(1) NOT NULL DEFAULT '0',
`reason_invalid` varchar(255) DEFAULT NULL,
`created_by` bigint(20) NOT NULL,
`associated_team_id` bigint(20) DEFAULT NULL,
`calculated_result` bigint(20) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`version` int(11) NOT NULL,
`updated_from_legacy` tinyint(1) DEFAULT '0',
`customer_id` bigint(19) DEFAULT NULL,
`result_description` varchar(16) DEFAULT NULL,
`sort_date` datetime DEFAULT NULL,
PRIMARY KEY (`data1_id`),
KEY `ix_data1_createdBy_16` (`created_by`),
KEY `ix_data1_calculated_result_17` (`calculated_result_id`),
KEY `created_at` (`created_at`),
KEY `updated_at` (`updated_at`),
KEY `headline` (`headline`),
KEY `starred` (`starred`),
KEY `status` (`status`),
KEY `fk_data1_customer_id` (`customer_id`),
KEY `result_description` (`review_disposition`),
KEY `associated_team_id` (`associated_team_id`,`status`,`invalid`),
KEY `sort_date` (`sort_date`),
KEY `associated_team_id_2` (`associated_team_id`,`sort_date`,`status`,`invalid`),
CONSTRAINT `fk_data1_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `fk_data1_createdBy_16` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `fk_data1_calculated_restult_17` FOREIGN KEY (`calculated_result_id`) REFERENCES `calculated_results` (`calculated_result_id`),
CONSTRAINT `data1_ibfk_1` FOREIGN KEY (`associated_team_id`) REFERENCES `teams` (`team_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

I don't think there is that specific metric.  However, looking at

SHOW STATUS LIKE 'Innodb%';

Some interesting things:

Innodb_pages_written / Innodb_buffer_pool_write_requests --
    over, say, 15% is "too high"
Innodb_buffer_pool_pages_flushed / Uptime --
    I/O writes per second
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests --
    "wait to get into buffer_pool" - don't want that to go over about 1%

To cut back on the physical I/O, be sure to have innodb_buffer_pool_size set to about 70% of available RAM.

If you have RAID or SSD, we should discuss further.

If you have less than 4GB of RAM, consider getting more.

Indexing UUIDs is evil, even when not the PRIMARY KEY.

Please provide SHOW CREATE TABLE; I want to see the row size and table size.

TEXT and BLOB can cause issues.  Please provide SHOW CREATE TABLE.


Copy Azure SQL Database Between Azure Subscriptions Getting straight to the point, is this even possible?

I have successfully copied an Azure SQL database within the same subscription.  In doing so, I kept it simple by copying the database to the same server (within the subscription).  I used the Powershell commands Add-AzureRmAccount and New-AzureRmSqlDatabaseCopy.

I have tried executing the Add-AzureRmAccount twice (once for each subscription) but this seems to replace the first subscription with the second in the context as the New-AzureRmSqlDatabaseCopy command reports that it cannot find the specified resource group (which exists in the source subscription).

All that said, if this is something that is possible, what are the steps / commands that I need to execute?

Another option is to use CREATE DATABASE, as per Richard Hauer's answer on Stackoverflow (which I executed from SQL Server Management Studio v17.5):

CREATE DATABASE db_copy AS COPY OF ozabzw7545.db_original;

Some important notes:

SQL admin account and password were the same on both servers.
Servers were in different subscriptions.
Servers were on the same Tenant, checked via Azure PowerShell console with:

(Get-AzureRmSubscription -SubscriptionName <your-source-subscription>).TenantId
(Get-AzureRmSubscription -SubscriptionName <your-destination-subscription>).TenantId

You can monitor the progress with:

SELECT state_desc, name
FROM sys.databases

Reference Links:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database?view=azure-sqldw-latest#creating-a-copy-of-a-database-on-another-server
https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-move-resources
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy


TSQL or SSIS for Direct Insert Speed Performance I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data.

If TSQL Server and SSIS have the same hardware specs for both cpu core and memory, and the two tables reside in a Same database, which tool is better performance wise? Table is 50 million rows, around 30GB.

Our performance testing shows T-sql; I would like the confirm what Microsoft or vendor recommends.

Purpose of question is to understand speed difference between Tsql and ssis.

To briefly quote the documentation:

  Microsoft Integration Services is a platform for building
  enterprise-level data integration and data transformations solutions.
  Use Integration Services to solve complex business problems by copying
  or downloading files, loading data warehouses, cleansing and mining
  data, and managing SQL Server objects and data.

  Integration Services can extract and transform data from a wide
  variety of sources such as XML data files, flat files, and relational
  data sources, and then load the data into one or more destinations.

You say that you're loading data from one table to another in the same database without transformations. SSIS isn't really the right tool for the job. With a reasonable table structure, properly written T-SQL inserts will perform better than loading through SSIS because you avoid the overhead of sending data to another server, passing it through buffers, and sending it back to the destination server. Behind the scenes SSIS is just doing a BULK INSERT which isn't going to be faster than a T-SQL INSERT except in edge cases.

In the interest of giving a complete answer, I'll describe a few of the features of SSIS which can be helpful from a data loading performance perspective. Consider a table in SQL Server with a complex five column clustered index and many nonclustered indexes. Suppose you can't disable NCIs. You might get the best possible loading performance by splitting up the insert into batches because smaller sorts will be used to do NCI maintenance. It may be difficult to code an efficient batching solution in SQL Server with a five column clustered key. However, this is trivial to do in SSIS. SSIS can read the data in order and split the data up into batches for you just by changing a parameter. So in that case, you may get better performance with SSIS because the T-SQL code wasn't written to be as efficient as possible.

For another example, a BULK INSERT is able to take a BU lock on a target heap. BU locks are compatible with other BU locks. As a result, you can get multiple concurrent T-SQL inserts all loading minimally logged data into the same heap without blocking. That cannot be accomplished with T-SQL alone. The BU lock is not available in that context. SQL Server 2017 offers parallel inserts into heaps in T-SQL, so the benefit of a BU lock is diminished when loading data from a table with an identical structure. An example where a BU lock is better than anything in T-SQL would be loading from a table with a clustered index into a heap on SQL Server 2014 Standard Edition. In T-SQL you can only run a single insert at a time but in SSIS you could have many concurrent inserts running.

In summary, SSIS is almost certainly the wrong tool for the scenario that you described. You should expect to see better performance with properly written T-SQL code, with the exception of a few edge cases, because it avoids the overhead inherent to moving data between servers.


Oracle:How to determine the size of available shared memory of operating system while setting MEMORY_TARGET parameter for oracle instance? According to the oracle document:"The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer."Oracle document also suggest that we can use the command df –k /dev/shm to get the available shared memory.
I got a little confuse of of shared memory while I am reading the definition of the shmall parameter: This parameter sets the total amount of shared memory pages that can be used system wide , it means that the total amount of shared memory available on the system equals to (shmall*PAGE_SIZE)
So when I am going to set the value of  MEMORY_TARGET, which should I go for, shmall*PAGE_SIZE or  df –k /dev/shm

You should go for the solution that fulfills your requirements.

Both shmall and /dev/shm are configurable, so first you should to configure them, and after that, you can set MEMORY_TARGET. The default value (on Oracle Linux 7) of shmall is 4 TB, which is irrelevant for most systems. The default size of /dev/shm is 50% of the physical memory.

If you use the default settings, following /dev/shm for sizing can be a viable option, but it may be suboptimal ("Unused memory is wasted memory").

To make things more complicated, MEMORY_TARGET includes PGA, which is not shared memory.

Also, my personal opinion/practice: whenever I can, I use ASMM (sga_target/sga_max_size + pga_aggreate_target) instead of AMM (memory_target/memory_max_size). Especially since hugepages works with ASMM, but not with AMM.


What is the function of rebuilding indexes? I'm young in the IT world, and I'm learning daily. I just started working with SQL databases, and I have lots of basic questions.

One in particular, What is the function of rebuilding indexes? I have yet to find a good answer through Google.

Any feedback is appreciated.

Indexes are rebuilt to remove fragmentation. There a thousand and one articles and blog posts on the nature of index fragmentation but @BrentOzar recently posted a particularly concise explanation in Stop Worrying About SQL Server Fragmentation.

  Let’s take a step back for a second and pretend that your database is
  a phone book organized by last name, first name.

  As people move into your city, we have to add them to the phone book.
  Ideally, each page has some empty space, and we govern that with the
  fill factor.  When SQL Server rebuilds indexes, it uses the fill
  factor to decide how much free space to leave on each page.  If
  there’s not enough free space, SQL Server has to do some rearranging –
  but it can’t exactly shove a brand new page in the middle of the phone
  book.  The book’s already bound.  We’ll have to tack more blank pages
  onto the end.

  Problem #1 – Internal Fragmentation: We’ve got a newly added page with
  hardly any stuff on it.
  Problem #2 – External Fragmentation: The phone book pages are out of
  order.

Brent's recent article offers an updated perspective on one of his previous series, Index Fragmentation Findings. The older article highlights statistics from much older studies on the damaging effects of fragmentation, the more recent makes a case for mitigating a large percentage of the performance downsides by ensuring your database is fully cached.

Ram is now so comically cheap that it is probably the cheapest, easiest, lowest risk solution to a heavily fragmented database. Especially if the nature of the database design is such that it will naturally become fragmented despite maintenance efforts.


Minimal permissions required to read index size in SQL Server I'm involved with managing an application with a large number of SQL Server 2014 databases and am extracting table and index sizes in order to track capacity. I'm using the following query:

select
    db_name() as DBName,
    object_schema_name(i.object_id) as SchemaName,
    object_name(i.object_id) as TableName,
    isnull(i.name,'(HEAP)') as IndexName,
    ps.SizeInPages
from
    sys.indexes i
        inner join
        (
            select ps.object_id,ps.index_id,sum(ps.row_count) as IndexRows, sum(ps.used_page_count) as SizeInPages, count(*) as PartitionCount
            from sys.dm_db_partition_stats ps
            group by ps.object_id,ps.index_id
        ) ps on
            ps.object_id = i.object_id and
            ps.index_id = i.index_id
where
    object_schema_name(i.object_id) not in ('cdc','sys')

Ideally I'd like to run this query from a SQL Login with the minimum required permissions, but so far it seems like the user needs VIEW SERVER STATE and SELECT permissions on all the tables. I've also tried putting the above query in a stored procedure and giving the user account EXECUTE permissions on that, but sys.indexes is still filtered based on what the user has select rights to.

Is there another way of doing this that I'm missing?

There are couple ways to achieve what you are after.

I will show you two, but there are other ways as well.

The first one is signing a procedure, and allowing other users to use it with specifying grant execute on object::yourprocedure only.

First of all you need a master key, which i assume you already have, and you can check it using sys.symmetric_keys DMV, and looking for record that has '##' as a prefix. If not simply create a DB master key

'CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@$$W0RD'

This is very importaint key, and make sure you back it up using this guide

Now you are going to create a certificate based on which you will create module signing user. Note it is only for singing modules and you cannot use it to test privileges etc. So first things first - Certificate :

CREATE CERTIFICATE SignModules
WITH SUBJECT = 'Certificate for signing modules'

Just like the master key, you need to back up this certificate as well. guide

Now we are creating a user with certificate which will have all the rights:

CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules

There are two ways of providing necessary rights:

becoming a db_reader as exec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
giving appropriate rights GRANT VIEW DEFINITION TO UnlockCertifiedProcedures, GRANT VIEW DATABASE STATE TO  UnlockCertifiedProcedures

Once this is done, the next step is to actually sign this procedure, so whoever has rights to 'execute' the procedure will inherit rights from the certificate user.

ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules

To test:

create user TestUser WITHOUT LOGIN
grant execute on OBJECT::YourProcedure to TestUser
execute as user = 'TestUser'
exec Schema.YourProcedure

The second option is creating a user and executing procedure as that specified user.

CREATE USER AnotherTest WITHOUT LOGIN
GRANT VIEW DEFINITION TO AnotherTest
GRANT VIEW DATABASE STATE To AnotherTest
GRANT EXECUTE ON OBJECT::YourProcedure To AnotherTest

Create procedure executing as that user:

CREATE proc  Schema.YourProcedure WITH EXECUTE AS 'AnotherTest'
AS
--your code--

Now simply give execute rights on a procedure to a specified user :

GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser

Like i said there are other ways, including roles but these two could get you a job done. Just remember you cannot track a user that executed procedure by specifying 'execute as'(login is possible tho). Also creating a certificate/database keys can be a headache when you`re migrating DB, or simply restoring it somewhere else.


Oracle EXPDP using QUERY parameter with results from SELECT Oracle 11.2g XE

I'm trying to write a initial deployment script for my application. There are several tables with data that I want to include in datapump export with a QUERY.

I have a table which includes these queries and table names to be exported.

Table: T_DEFINITION
============================

TABLE_NAME | WHERE_CONDITION
-----------+----------------
TBL1       | WHERE CMP_ID=0
TBL2       | WHERE 1=0
TBL3       | WHERE SEQ_ID=10 OR CMP_ID=0
...

Relevant portions of my expdp PARFILE as follows:

INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM T_DEFINITION WHERE (WHERE_CONDITION!= '1=0' OR WHERE_CONDITION IS NULL))"
QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
...

As you can see, I must have manually define QUERY parameter for each table that I need. Is there a way to define QUERY parameters that SELECT where conditions from my T_DEFINITION table?

Something like this:

QUERY="SELECT TABLE_NAME||':'||WHERE_CONDITION FROM T_DEFINITION"

No, there is not.

You have already defined manually the filters for each table, why not use that? Just dynamically generate the parameterfile before running expdp:

select 'QUERY=' || table_owner || '.' || table_name || ':"' || where_condition || '"'
from t_definition;

QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
QUERY=DEV.TBL3:"WHERE SEQ_ID=10 OR CMP_ID=0"
...

Or use DBMS_DATAPUMP.


Update SQL Server 2016 from RTM to SP1 I have SQL Server 2016 RTM installed and would like to update it to SP1. When I run an SP1 cumulative update, it fails because there is nothing to update. Presumably this is because it needs SP1 to be installed first. When I run the SP1 installer, it wants to create a new instance.

How can I update an existing instance from RTM to SP1 without having to uninstall and reinstall the instance?

It sounds like you've downloaded a copy of the full SQL Server 2016 (with SP1) installer. (Microsoft often distributes the installer with the latest service pack built in.)

Instead, download Service Pack 1 by itself, then apply the latest cumulative update.


Have: Postgres database files. Need: Functioning database I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously convinced myself my current postgis setup relied on 9.3 alone.

That was not the case, and to make matters worse, I removed it while the instance was running, so I headed to the repositories to restore 9.1.

The repositories for Ubuntu don't have PostgreSQL 9.1 for 14.04, so I instead compiled from source so I could run pg_dump or pg_upgrade, which requires binaries from both.

But my issue then was that just because I now had a working version of 9.1 again, that didn't make my databases from earlier visible.

So, as far as I am aware, my database files are intact, but I need to 'hook' them up with the new 9.1 service I installed today. I hope it is trivial, but it has stumped me for hours. Any pointers for where to start with updating the config?

@a_horse_with_no_name is quite right. Because you compiled PostgreSQL from source, the usual tools provided by pg_wrapper like pg_ctlcluster and pg_lsclusters are not available. As a result the PostgreSQL init scripts on Ubuntu won't start your PostgreSQL 9.1 DB.

You could hook your custom binaries into pg_wrapper, but it's not worth the hassle for a one-off.

Assuming your 9.1 data directory is still owned by user postgres and in the default location:

sudo -u postgres -i
PGPORT=5440 PATH=/path/to/your/9.1_install/bin pg_ctl -D /path/to/your/9.1/db
PGPORT=5440 /path/to/9.3/bin/pg_dump -Fc -f mydb.pgdump mydb
PGPORT=5440 /path/to/9.3/bin/pg_dumpall --globals-only > my_91_globals.sql

then run my_91_globals.sql against your 9.3 server, or examine it and make any required changes.

Then pg_restore the mydb.pgdump using the pg_restore from 9.3.

You might need to adjust this if you're working from a restored backup copy of your 9.1 datadir, because its permissions may not be those of the postgres user. It does not matter what user owns it, but you might need to tweak pg_hba.conf if you're using peer auth and connecting as a different user.


Existence of Missing Index in MySQL. Does it exist? I have been working a bit with MySQL and SQL Server, and I have a question: if the "Missing Index" script exists in MySQL

In SQL Server I know that it shows the missing indices of each table in a database, and by that I would like to know if it exists in MySQL or something similar to this SQl Server script that can be executed.

Beforehand I would be very grateful.

There is another article here: https://stackoverflow.com/questions/30626876/how-to-find-missing-indexes-in-mysql

I, too, would recommend using the explain plan and looking at results.  I have had instances where adding an index to a table actually slowed the results down to a crawl.

Here is an interesting way to look up columns that could possibly need indexed:
http://www.youdidwhatwithtsql.com/spotting-missing-indexes-mariadb-mysql/2036/

Again, like the author said, "a query I use for MySQL / MariaDB to spot any columns that might need indexing" [emphasis mine]


How to export data from a SQL Server database with hundreds of tables and timestamp columns? I have a SQL Server database with hundreds of tables and they all have timestamp columns. I don't have access to backups or the server's file system. To use the export data wizard, I have to go into each table's mapping and set the timestamp column destination to Ignore. It's very time consuming. Using SSMS 2017 (17.3).

Any ideas on how to export the data with minimum effort?

If the account that the SQL Service is running under has access to any network shares, then you can use database backup to a shared folder, and restore from there as well.

BACKUP DATABASE [dbname] TO DISK = '\\servername\sharedfoldername\dbname.bak'
RESTORE DATABASE [dbname] FROM DISK = '\\servername\sharedfoldername\dbname.bak'

Or you can use bcp.exe. Use SSMS Object Explorer to script out all the tables, and execute the script on the desired database
Use TSQL to build out all the bcp.exe commands, something like this:

select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' out "\\server\someshare\' + name + '.bcp" -n -S ' + @@servername + ' -d "sourceDb" -T '  from sys.tables order by name;
select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' in "\\server\someshare\' + name + '.bcp" -S "DestinationInstance" -d "destinationDb" -T '  from sys.tables order by name;


Return rows one at a time from variable table I have a database which contains job numbers, each job has a list of invoices and a list of journals. Its an IRQ request accounting system.
In order to see which jobs have a zero balance I'm subtracting a sum of values from another sum of values. I'd like to see the result per job number.

I currently have a query:

 DECLARE @job_nos AS TABLE ( job_no INT )

    INSERT  INTO @job_nos ( job_no )
            SELECT  jm_job.job_no
            FROM    [FCU_LIVE].[dbo].[jm_job]
                    INNER JOIN [FCU_LIVE].[dbo].[xe_log] ON ( CAST(jm_job.job_no AS VARCHAR(8000)) = xe_log.keystring )
                    INNER JOIN [FCU_LIVE].[dbo].[jm_job_status] ON xe_log.to_value = jm_job_status.status_code
                    INNER JOIN [FCU_LIVE].[dbo].[jm_division] ON jm_job.division_no = jm_division.division_no
                    INNER JOIN [FCU_LIVE].[dbo].[sys_phone_list] ON jm_job.cust_id = sys_phone_list.list_id
            WHERE   field_id = 'active'
                    AND jm_job.division_no = 4
                    AND jm_job.billing_type = 'I'

    SELECT  ( SELECT    SUM(jm_installment.billing_amount)
              FROM      [FCU_LIVE].[dbo].[jm_installment]
              WHERE     jm_installment.job_no IN ( SELECT   job_no
                                                   FROM     @job_nos )
            )
            - ( SELECT  SUM(jm_wo_transaction.billing_amount)
                FROM    [FCU_LIVE].[dbo].[jm_work_order]
                        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                                                                           AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                WHERE   jm_work_order.job_no IN ( SELECT    job_no
                                                  FROM      @job_nos )
                        AND jm_wo_transaction.override = 'N'
              ) AS WIP

Which gives me a single amount. The TOTAL sum of all job numbers returned by the multiple variable.
How can this be changed to output, the sum result for each job number on a separate row?

I'm blindly using this approach after googling possible options. If you have a better approach overall please let me know.

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs

SELECT i.job_no, installment_billing_amount - transaction_billing_amount
FROM (
        SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_installment]
        GROUP BY job_no
    ) i
    INNER JOIN (
        SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_work_order]
            INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                AND jm_wo_transaction.override = 'N'
        GROUP BY jm_work_order.job_no
    ) t ON i.job_no = t.job_no
WHERE   i.job_no IN ( SELECT    job_no
                      FROM      @job_nos )

It is easier to follow as a CTE query.

WITH
    installmentSum AS
    (
    SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_installment]
    GROUP BY job_no
    )
    ,
    transactionSum AS
    (
    SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_work_order]
        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
            AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
    WHERE jm_wo_transaction.override = 'N'
    GROUP BY jm_work_order.job_no
    )
SELECT j.job_no, installment_billing_amount - transaction_billing_amount
FROM @job_nos j
    INNER JOIN installmentSum i ON j.job_no = i.job_no
    INNER JOIN transactionSum t ON i.job_no = t.job_no;


Database latency vs throughput I'm a bit confused by terms latency and throughput in case of databases.

My understanding is that latency represents time needed to perform a single request (e.g. insert or select), while the throughput represents the number of such operations during a certain amount of time (e.g. how many inserts per second).

If my understanding is correct, shouldn't it always be the cases that the two are correlated? If latency decreases throughput should increase and vice versa? So a database having low latency should have high throughput?

What comes to my mind is that many requests can be performed in parallel, so that could lead to the fact that even a single request is slow (latency high) in general the database can perform well (can process large number of requests in parallel, especially if we are talking about scalable distributed database). Is that the point?

I started thinking about this by reading about Cassandra:

  Cassandra also places a high value on performance. In 2012, University of Toronto researchers studying NoSQL systems concluded that "In terms of scalability, there is a clear winner throughout our experiments. Cassandra achieves the highest throughput for the maximum number of nodes in all experiments" although "this comes at the price of high write and read latencies."

Is it the case that read and write are slow, but it achieves high throughput because it is distributed?

Your intuition is close, but disregards concurrency.

You can have medium/high latency, high throughput, massively concurrent systems - any individual request may take longer on Cassandra than on some other systems, but you can run far more requests in parallel, allowing total throughput to be higher. In Cassandra's case, the SEDA architecture allows many concurrent requests per machine (typically on the order of ~128 or ~256 read and write threads per machine), and the distributed nature allows you to scale up with many machines (single clusters on the order of thousands of machines).


Distributed Availability Group Failover Issue I have created Distributed Availability group set up as below:

In Data center A, created 2 node AG1 setup (Synchronous) in Datacenter B, created 2 node AG2 setup (asynchronous).

Created Distributed AG setup (synchronous) on top of these two AGs and it is working fine.

When i am executing Failover command (on Primary of DC A) to failover manually it is not failing over properly, it changes the role of AG1 as secondary but shows disconnected from AG2.

I executed
ALTER Availability group DistAG FAIlOVER_ALLOW_DATA_LOSS command on Primary of AG1.

please help to clarify if any config problem is there or i need to follow certain steps to fail-over.

I believe you should run the following command on the primary of the secondary group.

ALTER AVAILABILITY GROUP <AG name> FORCE_FAILOVER_ALLOW_DATA_LOSS;


Parallel Execution Causing Duplicates [SQL Server] There are multiple procedures on our database, handling a "inventory". Let's say each user can store items, and then request those items to be moved to another user's inventory, or to be stacked with other items.

For example, you have 5 wood, here, and 25 here, you can combine them to have 30 wood. Each individual user can send post requests to your c# WebApi, which then runs some checks, and finally executes the necessary procedures.

Everything is checked, quantity, ids, slots, everything is double checked. But here is the problem:

If a user, sends hundreds of parallel requests, with a web debugging proxy, or an automated posting client, since the api manages each request individually, and in parallel, this causes bugs where duplicates are caused. In other words, botting the api, with parallel requests, causes many checks in the procedure to pass, since they happen in parallel. eg: is there enough of this item? check, is there enough space for the item in the target container? check. etc etc...

I tried limiting the requests per X miliseconds on iis, but then again, we use cloudflare, which make all requests look like they come from the CF ip's as we resolve the cf ip in the application level. What about only allowing 1 execution at a time per user in the webapi?

What should I do to prevent this from happenning?

Fix the application. There is no magic setting in the database that will prevent a badly written application from inserting wrong data in the database.

Fixing the application means using the appropriate isolation levels and keeping transactions as short as possible.

Here is an example of how you check and decrease quantity in the same query:

IF OBJECT_ID('tempdb..#stock') IS NOT NULL DROP TABLE #stock;
IF OBJECT_ID('tempdb..#basket') IS NOT NULL DROP TABLE #basket;
GO

SET NOCOUNT ON;

CREATE TABLE #stock (
    product varchar(50) PRIMARY KEY,
    quantity int
);

INSERT INTO #stock
VALUES
     ('pants', 30),
     ('shirt', 18),
     ('shoes', 4);

CREATE TABLE #basket (
    product varchar(50),
    quantity int
);

-- At this point the stock covers the basket
INSERT INTO #basket
VALUES
    ('pants', 2),
    ('shoes', 4);

-- Uncomment this statement to make it fail
INSERT INTO #basket
VALUES
    ('shoes', 1);

DECLARE @invalid_stock TABLE (
    product varchar(50) PRIMARY KEY,
    quantity int
);

BEGIN TRAN

-- Here you decrease the stock of the quantity required by your basket
-- If you have stock that was turned to negative by the operation, it
-- will be inserted in the @invalid_stock table.
INSERT INTO @invalid_stock
SELECT *
FROM (
    UPDATE s
    SET quantity = s.quantity - b.quantity
    OUTPUT inserted.*
    FROM #stock AS s
    INNER JOIN (
        SELECT product, SUM(quantity) AS quantity
        FROM #basket
        GROUP BY product
    ) AS b
        ON s.product = b.product
) AS newstock
WHERE quantity < 0;

IF EXISTS (SELECT * FROM @invalid_stock)
BEGIN
    ROLLBACK;
    THROW 50001, 'Not enough stock to complete this order!', 1;
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction complete!'
END


Mysql FLUSH LOGS in live server with replication I need to run the slow query and unindexed query logging for some time on a live production db with replication. Changing GLOBAL const value sounds like a good solution. This article also provides good read on that..

The problem is that I need to run FLUSH LOGS; on the master / slave. And I'm not sure how that will effect the replication. Can someone tell me the impact of running FLUSH LOGS; on live master and on live slave.

Thanks

FLUSH LOGS; is safe to do anytime.  It will rotate the replication logs; this is harmless; replication knows how to move on to the next log.  The binlog is automatically rotated when it exceeds max_binlog_size.

Before 5.6, FLUSH LOGS did not rotate the slowlog.  See http://dev.mysql.com/doc/refman/5.6/en/flush.html or the page for your version.

I have never done reset master; I would not include it.  I use expire_logs_days for automatically purging binlogs.  (If a slave has been offline for that many days and I don't notice it, I deserve the hassle.)

I prefer log_queries_not_using_indexes = OFF; otherwise it clutters the slowlog.  (A tiny table without an index is often not-a-real-problem.)


Identify sequence of events based on certain rules I need help on identifying a sequence of events in SQL Server 2008 R2 rows please:

This is the sample data with SQLFiddle:

   ID   EventTime       TypeID             EventNr
    1   06:34:51        1
    2   06:35:51        3
    3   06:36:51        40
    4   06:37:51        10
    5   06:48:51        1
    6   06:49:51        2
    7   06:50:51        4
    8   06:51:51        40
    9   06:52:51        5
    10  06:53:51        10

I need help on identifying Event Nrs please -
Rules that I must follow:

An event starts start with TypeID 1 (@StartTypeID).
An event ends with TypeID 10 (@EndTypeID).
An event has a different TypeID after @StartTypeID, and before @EndTypeID.
And there must be more than 5 minutes between EventNrs.

This is the explected result for the above example:

 ID EventTime   TypeID  Notes                                               EventNr
1   06:34:51    1       Starts with TypeID @StartID 1     (OK)                     1
2   06:35:51    3       Has different TypeID in sequence before @EndID reached(OK) 1
3   06:36:51    40                                                                 1
4   06:37:51    10      Ends with @EndID 10 + >5min before next Event(OK EventEnd) 1

5   06:48:51    1       Starts with TypeID @StartID 1                              2
6   06:49:51    2       Has different TypeID in sequence before @EndID reached     2
7   06:50:51    4                                                                  2
8   06:51:51    40                                                                 2
9   06:52:51    5       Has different TypeID in sequence before  @EndID reached    2
10  06:53:51    10      Ends with @EndID 10                                        2

SQL for creating the data sample:

declare @FirstTypeID int = 1

declare @LastTypeID int = 10

create table #T (ID int identity , EventTime datetime, TypeID int)
insert into #T(EventTime,TypeID)
values
 (DATEADD(minute,-30,getdate()),1)
 ,(DATEADD(minute,-29,getdate()),3)
 ,(DATEADD(minute,-28,getdate()),40)
 ,(DATEADD(minute,-27,getdate()),10)
 ,(DATEADD(minute,-16,getdate()),1)
 ,(DATEADD(minute,-15,getdate()),2)
 ,(DATEADD(minute,-14,getdate()),4)
 ,(DATEADD(minute,-13,getdate()),40)
 ,(DATEADD(minute,-12,getdate()),5)
 ,(DATEADD(minute,-11,getdate()),10)

select * from #T

drop table #T

This query gives the correct output:

With ord as (
    Select first, last
        , EventNr = ROW_NUMBER() Over(order by first)
    From (
        Select first = t1.EventTime, last = t2.EventTime
            , rn = ROW_NUMBER() Over(partition by t1.ID order by t2.eventTime)
        From #T t1
        Inner Join #T t2 On t2.EventTime > t1.EventTime  and t2.TypeID = @LastTypeID
        Where t1.TypeID = @FirstTypeID
    ) ids
    Where rn = 1
)
Select t.ID, t.EventTime, t.TypeID, o.EventNr From #T t
Inner Join ord o On t.EventTime >= o.first and t.EventTime <= o.last
Order By o.EventNr, t.EventTime

I didn't use Column ID for ordering because it seems to be specific to your samplte.


SQL Server Log Reader Agent while trying to add node in p2p Replication I am trying to add one more node in peer-to-peer cluster.
In current state, I have 3 servers A, B and C.
I need to add a node D with node A(only).
For this, I used following steps:

Took full backup from Node A
Copied it to Node D
Restored the backup
Added publications in both nodes.
Added subscription for Node D in publisher Node A with sync_type 'initialize with backup'

As soon as the commands were run, the Log Reader in Node A started throwing the below errors:

The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.
Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000)
Get help: http://help/MSSQL_REPL2000
The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

As this was affecting the p2p replication between Nodes A, B and C, I removed the subscription for Node D.
This didn't resolve the error.
On further investigation I found that the Distribution Cleanup job was still running.
Probably that caused the issue.

Now, I am just hoping to make the p2p replication between already existing nodes A, B and C working again, the way it was before.
But, all my attempts have resulted in no change in Log Reader Agent.
It is stuck at the same error.
- The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
- The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.
Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
- IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000)
Get help: http://help/MSSQL_REPL2000
- The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Please advise on any steps to resolve this.
Thanks

Adding the resolution for the benefit of anyone who faced this issue on SQL 2012:

We engaged MS support on this. Apparently their stored procedure "sp_dropsubscriber" misses some tables sometimes. In our case, this entry was not deleted from table MSnosyncsubsetup. Once they found this out after few hours of analysis, a backup was taken and the row related to new subscriber was deleted.
This fixed the log reader and replication hummed along properly.


Could not find a log backup file that could be applied to secondary database Was getting this error when trying to do a Log Shipping Restore, even though I had a bunch of TRN files that were sequential. Seems that the filename of the log file are required to be in the following format [database_name]_[utc_time_stamp].trn. Any other filename format will result in SQL Server not being able to find the file.

Run the following from the primary node.  It will output a Recovery Chain for each database hosted on the Instance.  It should show you not only the files you need to recover, but the order in which you need to do so.  You should be able copy the commands from the query output, paste them into a query window connected to your secondary, and so long as you also brought over the backups files, run the commands in the order supplied and get the database up to a point where you can join it to the AG.  This script supports striped backups, differentials, COPY_ONLY, etc.

WITH BackupHist
AS
(
        SELECT
                s.server_name
            ,   d.name AS database_name
            ,   STUFF(( SELECT  ''', DISK = ''' + physical_device_name
                    FROM    msdb.dbo.backupmediafamily
                    WHERE   media_set_id = s.media_set_id
                    ORDER BY family_sequence_number
                    FOR XML PATH('')),
                1 ,
                3 ,
                '') + '''' AS physical_device_name
            ,   (   SELECT  TOP 1
                        CASE device_type
                            WHEN 2 THEN 'Disk'
                            WHEN 102 THEN 'Backup Device (Disk)'
                            WHEN 5 THEN 'Tape'
                            WHEN 105 THEN 'Backup Device (Tape)'
                            WHEN 7 THEN 'Virtual Device'
                        END AS device_type
                    FROM    msdb.dbo.backupmediafamily
                    WHERE   media_set_id = s.media_set_id) AS device_type
            ,   CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
            ,   CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
            ,   s.backup_start_date
            ,   s.first_lsn
            ,   s.last_lsn
            ,   s.checkpoint_lsn
            ,   s.backup_finish_date
            ,   s.database_backup_lsn
            ,   s.is_copy_only
            ,   CASE s.[type]
                    WHEN 'D' THEN 'Database (Full)'
                    WHEN 'I' THEN 'Database (Differential)'
                    WHEN 'L' THEN 'Transaction Log'
                    WHEN 'F' THEN 'File or Filegroup (Full)'
                    WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                    WHEN 'P' THEN 'Partial (Full)'
                    WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
            ,   s.recovery_model
        FROM    msdb.dbo.backupset s RIGHT OUTER JOIN sys.databases d
                ON s.database_name = d.name
                AND s.recovery_model = d.recovery_model_desc
        COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   database_backup_lsn
        ,   is_copy_only
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   physical_device_name
        ,   ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM    BackupHist
    WHERE   backup_type = 'Database (Full)'
), BackupHistDiffIterations AS
(
    SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   database_backup_lsn
        ,   is_copy_only
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   physical_device_name
        ,   ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM    BackupHist
    WHERE   backup_type = 'Database (Differential)'
), BackupHistFullDiffRestores AS
(
        SELECT  *
            ,   MAX(last_lsn) OVER (PARTITION BY base.database_name ORDER BY backup_finish_date DESC) AS recent_last_lsn
            ,   ROW_NUMBER() OVER (PARTITION BY base.database_name ORDER BY backup_finish_date DESC) AS recent_bak_num
        FROM (
                SELECT  *
                FROM    BackupHistFullIterations
                WHERE   BackupIteration = 1 -- Show the most recent iteration

                UNION ALL

                -- Get most recent Differential based on Most Recent Full Backup if exists and most recent full is NOT a COPY-ONLY
                SELECT  *
                FROM    BackupHistDiffIterations d
                WHERE   d.BackupIteration = 1
                    AND d.database_backup_lsn = (   SELECT MAX(checkpoint_lsn)
                                                FROM BackupHistFullIterations
                                                WHERE BackupIteration = 1
                                                    AND database_name = d.database_name
                                                )
            ) base
)
SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   is_copy_only
        ,   CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
                WHEN 'Database (Differential)' THEN 'RESTORE DATABASE [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5'
                WHEN 'Transaction Log' THEN 'RESTORE LOG [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5'
                ELSE ''
            END AS RestoreStatement
FROM (
    SELECT  fdr.server_name
        ,   fdr.database_name
        ,   fdr.backup_finish_date
        ,   fdr.backup_type
        ,   fdr.physical_device_name
        ,   fdr.first_lsn
        ,   fdr.last_lsn
        ,   fdr.checkpoint_lsn
        ,   fdr.database_backup_lsn
        ,   fdr.is_copy_only
    FROM    BackupHistFullDiffRestores fdr

    UNION ALL

    SELECT  bhlr.server_name
        ,   bhlr.database_name
        ,   bhlr.backup_finish_date
        ,   bhlr.backup_type
        ,   bhlr.physical_device_name
        ,   bhlr.first_lsn
        ,   bhlr.last_lsn
        ,   bhlr.checkpoint_lsn
        ,   bhlr.database_backup_lsn
        ,   bhlr.is_copy_only
    FROM    BackupHistFullDiffRestores bhfdr
            INNER JOIN BackupHist bhlr
                ON bhfdr.database_name = bhlr.database_name
                AND bhlr.last_lsn >= bhfdr.recent_last_lsn
                AND bhfdr.recent_bak_num = 1
    WHERE   bhlr.backup_type = 'Transaction Log'
) restore_cmd
ORDER BY 1, 2, 6


Failover in MySQL Chain Replication We have a 4 server setup, 1 master, 3 daisy-chained slaves, in the following setup:
A (master)   ->   B (slave)   ->   C (slave)   ->   D (slave)
(the servers B and C and D are running with log-slave-updates)
In normal operation everything works as expected: if we add new data to A, we see it show up quickly in B and C and D
Now we want to create a failure scenario -- we shutdown A and want to make B the new master:
B (master)   ->   C (slave)   ->   D (slave)
It seems like what we want to do is fairly simple -- switch B from Slave to Master
We are trying to follow the documentation "Switching Sources During Failover"
https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html
The doc says " On the replica Replica 1 being promoted to become the source, issue STOP REPLICA | SLAVE and RESET MASTER."
So if we're reading correctly, to switch B from Slave to Master all we have to do is run:
STOP SLAVE
RESET MASTER

Running "STOP SLAVE" causes no issues, but running "RESET MASTER" breaks the replication to downstream staves C and D. This is the error on C:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
or sometimes:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'I/O error reading log event; the first event 'mysql-bin.000001' at 14282060, the last event read from './mysql-bin.000001' at 14282683, the last byte read from './mysql-bin.000001' at 14282683.'
So what is the point of "RESET MASTER" and why does it break the chain? Is there any harm in omitting it/how does one properly do a failover in MySQL chain replication?

You're not switching sources so there is nothing required to migrate from an A master to a B master.
To clear up the record of A being a master of B however the following may be desirable:

(B) STOP SLAVE will stop (B) from trying to get more binlog events
(B) RESET SLAVE ALL will make (B) forget about the (A) master.
(B) RESET MASTER is wrong. (B) is the master of (C). It was even before (A) is removed.


Quickest Method To Invalidate Statistics I'm doing some testing on sync vs async auto statistics updates. I'd like to quickly invalidate all statistic objects (headers, density vectors, and histograms) to ensure that next time the statistic is used that it will be updated.

I'm trying to simulate an auto update of statistics, not an auto creation.

Ideally I don't want to change the row count so I've dismissed INSERT/DELETE operations.  Ideally, I don't want to change any data values either, I have considered using UPDATE statements but I'm thinking this could take too long on some of my larger tables.

I had looked at UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT but I don't think this is what I'm after. I was hoping there was maybe a trace flag or undocumented command that would invalidate statistics.

Is there an quick, efficient way to do what I want to achieve that I haven't considered?

I'm testing on SQL Server 2016.

The most reliable sequence I can find to cause an automatic statistics update is:

Update statistics sampling zero rows
This results in an empty statistic object.

-- Example
UPDATE STATISTICS
    Person.[Address]
    IX_Address_StateProvinceID
    WITH SAMPLE 0 ROWS;

Update the target column(s) in a single row of the table
This increments the column modification counter. The combination of an empty statistics object and an incremented modification counter enables a special case statistics update (it simulates creating statistics on an empty table, then adding a row).

-- Example
BEGIN TRANSACTION;
    UPDATE TOP (1)
        Person.[Address]
    SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;

Run the query with OPTION (RECOMPILE)
This causes an automatic update of detected stale statistics, even if a matching plan for the query is already present in cache. The resulting statistics update will subsequently cause an optimality-based recompilation for the original cached plan, if it is matched again.

-- Example
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54
OPTION (RECOMPILE);

Demo

Using a similar AdventureWorks query as used in jyao's answer, the following script puts everything above together:

DBCC FREEPROCCACHE;
GO
-- Cache a plan for the query
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54;
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Empty stats object
UPDATE STATISTICS
    Person.[Address]
    IX_Address_StateProvinceID
    WITH SAMPLE 0 ROWS;
GO
-- Perform and rollback a single row update
BEGIN TRANSACTION;
    UPDATE TOP (1)
        Person.[Address]
    SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Run the query again to trigger stats update
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54
OPTION (RECOMPILE);
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO

Output

The DBCC SHOW_STATISTICS results show the original statistics header, the empty header, and the desired updated header at the end of the process:


What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean? I have a question regarding following output:
[nikola@192 ~] $ sudo su - postgres
[postgres@192 ~] $ psql dvdrental postgres
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql (11.7, server 11.8)
Type "help" for help.

dvdrental=#

What do following two messages stand for and why? Messages I am asking about:

psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql (11.7, server 11.8) - BTW, for this psql --version command I get following output:
psql (PostgreSQL) 11.8 (had to install PostgreSQL Server version 11.7 in order to perform command such as \dt and \l)
...and also this output:

postgres=# select * FROM version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), 64-bit
(1 row)

P.S. I'm running PostgreSQL 11.8 on Fedora 30.
UPDATE1:
As requested by @LaurenzAlbe output for rpm -qa|grep postgres command is:
[nikola@192 ~] $ rpm -qa|grep postgres
postgresql-11.7-2.fc30.x86_64
postgresql11-11.8-1PGDG.f30.x86_64
postgresql11-libs-11.8-1PGDG.f30.x86_64
postgresql11-server-11.8-1PGDG.f30.x86_64
postgresql-server-11.7-2.fc30.x86_64

UPDATE2:
I've ran dnf remove postgresql postgresql-server command -> databases are still there and I can access them via pgAdmin4 and psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql message is no longer showing up. But, there is now new issue -> when I access postgres shell in terminal using sudo su - postgres command and then perform psql some-database-name postgres I get message

bash: psql: command not found...
Install package 'postgresql' to provide command 'psql'? [N/y]

I presume this is asking me to install AGAIN Fedora package which in 1st place I am trying to get rid off (to not show psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) message).
Any suggestion how to solve this without installing Fedora package but from PostgreSQL Global Development group?
P.S. BTW, I've installed Postgres server by following this link which uses PGDG packages (but something didn't work right on my machine and I guess I've been offered by Fedora30 OS to install Fedora packages which is obviously what I did :) ).

You have installed a mixture of packets: some from the Fedora distribution, some from the PostgreSQL Global Development group.
These don't work well together. In particular, you have psql from the Fedora packages in the PATH, but libpq from the PGDG packages is on the library path.
That explains your question #2.
Ad question #1: that warning is printed if the library version of the installed library is lower than the one the software was linked with.
Remove the Fedora packages to get rid of the problem:
dnf remove postgresql postgresql-server

I hope that puts the correct psql on your PATH; if not, reinstall the postgresql11 package.


What is wrong with this SQL statement? I cannot get this statement running on MySQL server:

DECLARE @tabl varchar(40)
SET @tabl = :uId;
SET @location = CONCAT('data_',@tabl);
SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address, phone)
VALUES (:Id, :uId, :name, :address)';
Prepare stmt FROM @b;
Execute stmt;

I get this error:

  No value specified for parameter 1 - SQLException

Bindings will be done in another function.

How can I fix it?

I am not sure what you mean by

  Bindings will be done in another function.

You have an EXECUTE statement that is going to invoke your query right there and then, and that statement is not providing parameter values for your :name parameters. That is what the error message is about.

Your EXECUTE statement must have a USING clause, as per the manual:

EXECUTE stmt
USING @Id, @uId, @name, @address;

where @Id, @uId, @name, @address would probably be the names of arguments of the stored program containing the script in your question.

And then you will also need to address the issues mentioned by Julien Vavasseur and ypercubeᵀᴹ:

revisit the number of values vs the number of columns in the INSERT statement (and correct the USING clause above if necessary);
make sure your INSERT statement is syntactically correct (no missing spaces between its various tokens where required).


Postgres 9.6 - Taking differenctial backup I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet.

What is the best approach to take differential backup? I am considering below points :

Central Backup Server may not be up 24/7.
Internet Link may fail.
Bandwidth Consumption on the DB server.

And I am considering the below approaches :

Directly Rsync the Postgres data directory.
Some kind of log archive, to be read by Postgres.
Best of open source solution such as the pitrtool or pgpool2.

Which is the most efficient or even possible from above? Any other recommendation?

Please don't re-invent the wheel! There are so many traps in backup up and recovering the probability you fall into them is pretty high!

  Directly Rsync the Postgres data directory.

Please don't! You'll copy unconsistent data...

I think you should consider one of these 3 tools :

barman
backrest
probackup

Barman and probackup have real differential backups and will check backup consistency before restoring a backup. That's why I prefer one of those tools.

You can find a complete comparison in Anastasia Lubennikova's excellent conf talk Advanced PostgreSQL backup and recovery methods slides.


Hiring a database Dev To start, I have no technical experience. I own a home services business and I want a mySQL database for all my data. I know how I want it, I've played around with Caspio so I know how it will look/function from a user's perspective.

My question is in regards to how to have someone built it for me and what security measure to take to keep my website safe. I have hosting with siteground for my wordpress website. If its best to just create a new siteground account and incur another separate billing cost for it than I will do that but if it is safe to keep it on the same account than that is preferable due to savings and practicality. I went to cPanel, created the database (nothing in it, just created it), and created a user account for my developer to use when I hire one. I learned how to have a link for them to access it (mydomain.com/phpMyAdmin) and that is ready. I tested it, the login info for the user I created works.

So my actual question...Is giving them that link and the login credentials safe? That should only allow them to do things in the database right? By safe I mean my website and files and stuff, they wont have any access to any of that or cPanel right?

First, if you haven't built anything don't use MySQL. If you're doing a new product -- go PostgreSQL. It's better and free.

If you insist on MySQL, at least go MariaDB. While both have their advantages MariaDB is a fork and that's where the community is at. MySQL is Oracle's newly purchased pretend-to-be-open-source-friendly PR campaign.

There is nothing wrong with cPanel for you. That said, database developers don't use cPanel. If you're going to hire someone and they can't administer their database without a 3rd party GUI tool -- RUN.

  Is giving them that link and the login credentials safe?

It depends on safe. When you give login credentials they have at least the level of access granted by intent so look that up. And if your software is out of date and they're malicious usually more than that.

  By safe I mean my website and files and stuff, they wont have any access to any of that or cPanel right?

At that point it's a cPanel question. Often times db-clients leak privileges because they're improperly sandboxed. I can't tell you how many times I get to a psql box which launches right into psql, and I can get out simply doing \!.


what are the impacts if I convert one of the column to primary key or add a primary key to an existing table One of my MSSQL table don't have any primary key, I would like to add one. Just want to know:

First, if I convert an existing column (or multiple columns) to a primary key, what would be the impact? Will it affect the web applications rely on this table?

Furthermore, what if I add a new column and make it primary key on an existing table?

Last but not least, if none of the above is a good practice, what would be one? Creating a new table, migrating data and deleting old one and renaming the new table to old name?

the applications using this table is nothing but some CRUD operations.

Thank you in advance.

Having primary key is absolute necessity for any table.

But obviously before you do that you need to see whether adding a primary key will affect the application itself. If written in ASP.Net it will require model update. Also without primary key entity model looks at it as a view, which is mostly used for displaying data. Adding primary key would cause a conflict and you would have to explicitly create a new view.

If you already have a clustered index on some column, adding a primary key on another column would simply create a primary key constraint. Where as if you do not have a clustered index (heap table), and create a primary key it will implicitly create a clustered index as well on that column.

Probably worst option would be creating a new table and migrating data, simply due to the fact that you would loose all the indexes(if any) were on the table,also if the table had some relationship with the other table you wouldnt be able to delete it. Or if explicitly some one before you added some security rights on the table, would be lost.

If possible try to add primary key on already created table, and existing column.


Appropriate Windows version for Mysql I have a HP ProLiant DL380 G5 server and I would like to install mysql on it .

My questions are:

Which MySQL version should I install from this link for better performance, 32 bit or 64 bit?
Which version of Windows should I install on server for better performance: 2003, 2008, XP, or 7?

64 bit always. You do understand 32 vs 64 bit generally?
Windows Server 2008 R2 of course. Windows 7 is desktop, Windows 2003 is obsolete

Have you considered Linux though?


Providing access to the SQL server database for outside developers I am beginning a project that will involve insert and updates to a sql server database from a website. I maintain the SQL server database and am providing access to the sql server db.

My plan is to create a user with read/write access and provide the outsourced developers with a connection string with access to views that I create. Please let me know if this is outside the development expectations or if anyone has recommendations.

Thank you

Not quite sure what exactly your question is. However, let me try an answer...

In general it is a good idea to have all application access go exclusively through a layer of views and procedures. That allows to run multi step migrations under the covers without impacting the running application. Through that you can implement changes like moving a column of a 2 TB table to another table without downtime.

It is also a good practice to restrict application access to only the minimum required level. So giving the app read/write access to just the views and procedures is a good idea.

Finally, any web developer should know how to use a connection string. So if that is your question, you are good there too.

If you were going for something else, leave a comment below.


Trying to design database for eSports Teams - Issue with dependencies I am trying to design a database in Access for an eSport league (specifically League of Legends North America). I will post what I have and then explain my issue.

My issue comes when I try to normalize TEAMROSTER and PLAYERNAME. Each player can only belong to one team, but each team has 5 players in the 5 positions that you see I have listed in TEAMROSTER. I am not sure if that will be allowed when I try to create the relational schema. I will post the schema that I have created.

I am not sure if this is correct or not, as I am trying to relate the tables together and just truly don't understand how I should do this. This is the first database that I have tried to create.

IMHO This is not good design.
You have teamname in league table and teams table.
What also is not good to have mixed conventions (plural and singular for table names)

I would note that you should also put unique index on this fields:
toplane, jungler, midlane, adc, support.

create unique index teamroster_unique_idx on (toplane, jungler, midlane, adc, support);

This is valid for PostgreSQL, check your RDBMS documentation.

And also CHECK constraint that every value is different, you don't want to have all positions with same players. ;)

Unique index is to have unique combinations (no duplicates),
and check constraint to have all different values.

PS. You created logical design well, but you need to translate it to physical design
(create that in your RDBMS)


Chunk size bigger than configured We have a sharded mongo cluster. We have configured the chunk size to be 256M but they are ~1.5G each. There are no jumbo chunks as our shard key is well balanced (we have also checked for them specifically).
What can be the cause of having bigger than configured chunk sizes? And what can I do to auto split them?

EDIT

Shard Mongo at Mongo/
 data : 416.39GiB docs : 134529720 chunks : 368
 estimated data per chunk : 1.13GiB
 estimated docs per chunk : 365569

Shard db3repl at
 data : 402.97GiB docs : 129491879 chunks : 372
 estimated data per chunk : 1.08GiB
 estimated docs per chunk : 348096

Shard db4repl at
 data : 396.18GiB docs : 127334616 chunks : 368
 estimated data per chunk : 1.07GiB
 estimated docs per chunk : 346017

Shard db5repl at
 data : 43.89GiB docs : 14141333 chunks : 368
 estimated data per chunk : 122.14MiB
 estimated docs per chunk : 38427

To split you can use something like this...

sh.stopBalancer();
var c=0;
db.chunks.find({ns:"database.collection", "shard" : "S0RS0"}).sort({$natural:1}).limit(1400).forEach(function(d)
{
  printjson(d.min);printjson(d.max);
  var tulos=db.adminCommand( { split: d.ns, bounds: [ d.min, d.max ] } );
  if (tulos.ok < 1) {
    print("Split ERROR");
    printjson(tulos);
  }
  print(c);
  c++;
})
sh.startBalancer();


Setting default charset/collation for MySQL database I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I have a crazy mixture of latin and utf8. Basically, any new tables and columns are different.

Is there a way to set the default charset and collation for the database, so that when I add new tables to this database they are utf8?

Yes, you can set the default character set and collation on various levels.

First, in your question, on the database level:

CREATE DATABASE IF NOT EXISTS foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

or

ALTER DATABASE foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

All new tables in that database should be created with the new character set and database if it's not specified in the table creation command (if it is specified in table command, the specification will override the database default).

You can set the default at a server level (so that all tables are created with the default for all databases unless explicitly stated otherwise) by providing the --character-set-server and --collation-server options in your my.cnf and restarting the server.

And finally, verify the client connection settings. This doc explains how the client connection can fine-tune (and override) the default settings.