Incremental backups: MySQL Enterprise Backup vs Percona XtraBackup? We're evaluating two tools incremental backup: MySQL Enterprise Backup and Percona XtraBackup. The only comparison I found is by Percona which has obvious cause for bias From this comparison, Percona XtraBackup is better suited for incremental backups as it is faster and supports streaming compression.
Which solution is likely to be more reliable and less of a load on the server.

I would like to understand if there can be trouble when working with one of these tools.
For example, I accidentally stumbled upon "Note" on this page which says that under high load on the server, creating a backup can, in principle will fail.

Load on the server. This is especially important for Backup-point-in-time, because it will allow you to execute it more often.

ZFS beats both by light years with snapshots and incremental transfers and transparent compression.
ZFS snapshots can be transferred incrementally. You only transfer the blocks that have changed between the two snapshots. And you get lz4 compression on everything for close to free, and the snapshot sending doesn't have to decompress and recompress.
It is guaranteed to be consistent. ZFS being copy-on-write, either the entire transaction sticks, or the entire transaction is lost, nothing in-between. Unless, of course, you disabled safeties at database level, in which case all bets are off anyway. - gordan-bobic


You can use generate_series() for this.

select t.avg_income, d.month
from the_table t
  cross join lateral (
    select g.m::date as month
    from generate_series(date_trunc('month', t.date_from), date_trunc('month', t.date_to), interval '1 month') as g (m)
  ) as d
order by t.avg_income, d.month;

Alternatively:

select t.avg_income, d.month::date
from the_table t
  cross join lateral generate_series(date_trunc('month', t.date_from), date_trunc('month', t.date_to), interval '1 month') as d (month)
order by t.avg_income, d.month;

Online example: http://rextester.com/ADG4243


Data Warehousing strategy I have a busy MySQL database with 1.6 million records a day, I'm looking to replicate this data to another server, then run a clean up job on production database to maintain small database in production.

I thought about Master / Slave setup, but I'm not sure how can I prevent deletes from replicating to slave.

I'm sure other BD admins / developers have run into same situation, I would appreciate any suggestion / recommendation

Thank you

I thought about Master / Slave setup, but I'm not sure how can I prevent deletes from replicating to slave.

You can't do that. Replication is intended to replicate as much as possible to keep the slaves as accurate copies of the master(s).

You might want to replicate to another machine so that you can have a copy to run the copy-to-archive archive process on, so that this does not impact performance of the application when being run, but that is optional (and not necessary if the process doesn't impact live performance much or you have a time window in which you don't care if it does (i.e. over-night if your application is predominantly used in one timezone so you have quiet periods when most users are sleeping)).

The most efficient way to update the archive/reporting database from the production one (either the main DB or a read-only replica), and clean up production once this is done, depends a lot on the structure of your data.


Is SQL Server sp_who2 deprecated? I keep hearing sp_who2 is deprecated soon, unsupported. We are on SQL Server 2016 Enterprise.

What is a good official Microsoft replacement item or DMV which team should utilize instead of sp_who2?

I heard one is sys.dm_exec_sessions?

Back in the days there was sp_who, sp_who2, but then came Adam Machanic and created sp_whoisactive. And he saw everything that he had made, and behold, it was very good. And there was documentation and downloads on the following days.


SQL installation fails with "Could not find the Database Engine startup handle". Tried almost all suggestions from internet, but no luck I am trying to install a new standalone SQL instance. It fails with

Could not find the Database Engine startup handle.
I am trying to install SQL server 2019 Express edition in windows 10 Home OS.

I have tried:

Uninstalling all instances, deleting all files and changing the account to "Local System" while installing. I even reset windows to factory settings . Still am getting same error.

I have exited installation wizard and then starting the SQL service from configuration manager. It either fails or stops immediately after starting.
The event log contains the following error:
FileMgr::StartLogFiles
2(The system cannot find the file specified.)
d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf

But there is no D drive in my laptop at all

I have also given full control to local system, administrator, users group to the installation folder.

Answering my own question.
Note: It is not a solution. It is not applicable to any professional environment.
I did a PC factory reset. Deleting all personal apps and files. Then installed it. It installed without any problem. Earlier, I reset the pc but did not remove the files. The installation of SQL had still failed.
My suspicion is that there was some problem with the log on account in my system. But I still do not know the root cause


How to change user privileges from 'Y' to 'N' in MySQL Server? I want to change a privilege to my glpiuser from 'N' to 'Y'.
In MySQL server, what is the command to do this task?
Here's the image that display my users.
I'm using Ubuntu 20.04.1

The GRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles.
The REVOKE statement revokes one or more privileges from a user account.
So, something like this will most likely do what you need:
REVOKE All ON <DB> FROM user@source;


Does database require block storage always? I was reading the difference between block, file and object storage and I read somewhere that database require the underlying storage to be block storage. Is this true? Can I not install a database on a system that has file storage?

No, it's not true. Many DBMSes today (eg Microsoft SQL Server, PostgreSQL, Oracle, MySQL) use file storage by default, although some (eg Oracle) can also use block storage.


Index fillfactor and index pages in PostgreSQL The postgres documentation says index fillfactor affect on "index pages" and says "for heavily updated tables a smaller fillfactor is better". I'm confused that its about update an index value or any value in the row?
Is table fillfactor override the index fillfactors of the table (whether index fillfactor defined or not (default 90 for btree index))?

Fillfactor in PostgreSQL documentation:
Table fillfactor:

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

Index fillfactor:

The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.

Table and index fillfactor are independent from each other.
Both tables and indexes are stored in files on the operating system. These files are organized in 8KB blocks. fillfactor determines to what extent these blocks are filled by an INSERT (in the case of a table) or the initial creation or extension on the right end (for an index).
Even though the fillfactors on table and index are independent, the indication for setting lower values is similar for tables and indexes: if the table receives many updates. Bear in mind that many table modifications also cause an index modification.
Note that a lower fillfactor on the table will only help if none of the updated columns are indexed. Otherwise, it is only a waste of space.
I would set a lower fillfactor on tables if the above condition is satisfied and the table receives many updates. I would not set a lower fillfactor on indexes unless I see that an index regularly gets fragmented by many updates.


Convert datetime to europe default without time I have a datetime field that I need to convert into the format DDMONYY (27Aug12).  I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time.  How can I easily convert a date to the format I need?

Instead of your hack with replace, why not just cast to the right data type? This doesn't need to be nvarchar and the only reason it contains time is because you included too many characters. You can of course apply your replace here as well (or you could do that at the client).

SELECT CONVERT(CHAR(11),GETDATE(),13)
UNION ALL
SELECT CONVERT(CHAR(11), DATEADD(DAY, 10, GETDATE()), 13);

Results:

27 Aug 2012
06 Sep 2012


Stored Procedure to update an adjacency Model to Nested Sets Model If you don't already know, these two models are the most common ways to store a tree in a relational DB.

Adjacency Model:

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Nested Sets:

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

You can also take a look here

I have a Table in adjacency Model in MySQL and I have decided to convert it to the nested sets model. I need a code to fill the LEFT and RIGHT columns based on the parent column to mix them both and reach to something like this

What I need:

+-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |
|           2 | TELEVISIONS          |      1 |   2 |   9 |
|           3 | TUBE                 |      2 |   3 |   4 |
|           4 | LCD                  |      2 |   5 |   6 |
|           5 | PLASMA               |      2 |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |
|           8 | FLASH                |      7 |  12 |  13 |
|           9 | CD PLAYERS           |      6 |  15 |  16 |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+

Finally I found the solution but it needed some optimization and tweaks to work with my case and I added sorting with ID to get the tree sorted too,
the answer is mainly got from here so credit goes to @deceze,

CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_recover`()
    MODIFIES SQL DATA
BEGIN

    DECLARE currentId, currentParentId  CHAR(36);
    DECLARE currentLeft                 INT;
    DECLARE startId                     INT DEFAULT 1;

    # Determines the max size for MEMORY tables.
    SET max_heap_table_size = 1024 * 1024 * 512;

    START TRANSACTION;

    # Temporary MEMORY table to do all the heavy lifting in,
    # otherwise performance is simply abysmal.
   DROP TABLE IF EXISTS `tmp_tree`;

   CREATE TABLE `tmp_tree` (
        `id`        bigint(36) NOT NULL DEFAULT '0',
        `parent` char(36)          DEFAULT NULL,
        `lft`       int(11)  unsigned DEFAULT NULL,
        `rgt`      int(11)  unsigned DEFAULT NULL,
        PRIMARY KEY      (`id`),
        INDEX USING HASH (`parent`),
        INDEX USING HASH (`lft`),
        INDEX USING HASH (`rgt`)
    ) ENGINE = MEMORY
    SELECT `id`,
           `parent`,
           `lft`,
           `rgt`
    FROM   `tree`;

    # Leveling the playing field.
    UPDATE  `tmp_tree`
    SET     `lft`  = NULL,
            `rgt` = NULL;

    # Establishing starting numbers for all root elements.
    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `parent` = 0 AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO

        UPDATE `tmp_tree`
        SET    `lft`  = startId,
               `rgt` = startId + 1
        WHERE  `parent` = 0
          AND  `lft`       IS NULL
          AND  `rgt`      IS NULL
          ORDER BY `id` ASC
        LIMIT  1;

        SET startId = startId + 2;

    END WHILE;

    # Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
    DROP INDEX `lft`  ON `tmp_tree`;
    DROP INDEX `rgt` ON `tmp_tree`;
    CREATE INDEX `lft`  USING BTREE ON `tmp_tree` (`lft`);
    CREATE INDEX `rgt` USING BTREE ON `tmp_tree` (`rgt`);

    # Numbering all child elements
    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO

        # Picking an unprocessed element which has a processed parent.
        SELECT     `tmp_tree`.`id`
          INTO     currentId
        FROM       `tmp_tree`
        INNER JOIN `tmp_tree` AS `parents`
                ON `tmp_tree`.`parent` = `parents`.`id`
        WHERE      `tmp_tree`.`lft` IS NULL
          AND      `parents`.`lft`  IS NOT NULL
        ORDER BY `tmp_tree`.`id` DESC
        LIMIT      1;

        # Finding the element's parent.
        SELECT  `parent`
          INTO  currentParentId
        FROM    `tmp_tree`
        WHERE   `id` = currentId;

        # Finding the parent's lft value.
        SELECT  `lft`
          INTO  currentLeft
        FROM    `tmp_tree`
        WHERE   `id` = currentParentId;

        # Shifting all elements to the right of the current element 2 to the right.
        UPDATE `tmp_tree`
        SET    `rgt` = `rgt` + 2
        WHERE  `rgt` > currentLeft;

        UPDATE `tmp_tree`
        SET    `lft` = `lft` + 2
        WHERE  `lft` > currentLeft;

        # Setting lft and rgt values for current element.
        UPDATE `tmp_tree`
        SET    `lft`  = currentLeft + 1,
               `rgt` = currentLeft + 2
        WHERE  `id`   = currentId;

    END WHILE;

    # Writing calculated values back to physical table.
    UPDATE `tree`, `tmp_tree`
    SET    `tree`.`lft`  = `tmp_tree`.`lft`,
           `tree`.`rgt` = `tmp_tree`.`rgt`
    WHERE  `tree`.`id`   = `tmp_tree`.`id`;

    COMMIT;

    DROP TABLE `tmp_tree`;

END


What should I ask my DBA in order to diagnose a slow application? I am working in the IT department of an industrial firm. The IT department is a handful of people, mainly project managers, and everything else is outsourced.

I have been managing a project, where a third party application has been bought off the shelf, and deployed by the vendor, but to be maintained on our own.

It consists of a central application server, connected to an SQL Server database, and a dozen heavy clients, all on our premises, on the same VLAN.

Most of the time, it runs fine, however, sometimes, when accessing records from the DB, the application is awfully slow.

I have been asking our DBA (outsourced) what is wrong, but have been responded by something along the lines of "Please ask me to do something with the DB, and I'll gracefully do it".

Hence the question, in a situation of sporadic sluggishness like this, which technical actions should I ask of my DBA to make progress on the case?

Please monitor the performance of the DB is apparently too vague, and they expect something along the lines of 'Enable this performance counter for that long' and so on...

While a good DBA can find out a lot on his own, it never hurts to give a lot of information.

Which application you're using. If you know server/db ect give that as well.
Test/Prod
When the issue occurs, eg. I open this form and it's slow, or when I do this...
What's slow and what's fast. For some applications it can be perfectly acceptable to wait 1-2 seconds, while others have to perform near instantly. It can often be really easy to get a first bump in performance, but after that it takes a lot longer to make it just a little bit faster
Which login you're using

Especially if he's external + perhaps a junior it never hurts to just give him all you've got.

After that it's mostly up to the DBA to setup monitoring.

Log WhoIsActive to a table
Use the build in query store if version is 2016 or higher
Buy a third party tool, eg. SentryOne/Red gate monitor

After he setup monitoring you could also mail him "I'm having issues right now, could you see if you notice anything special?" Do not expect him to reply right away, but it gives him a window that he can check later where he knows the issue occurred.


MySQL create table: error 1005 errno: 150 "Foreign key constraint is incorrectly formed" This fails when trying to create the EXCHANGE table:

CREATE TABLE BOOK
(PKACC_NO VARCHAR (20) PRIMARY KEY,
TITLE VARCHAR (50),
AUTHOR VARCHAR (50),
PUBLISHER VARCHAR (50),
EDITION INTEGER (4),
PRICE INTEGER (10));

CREATE TABLE MEMBER
(PKMEMBER_ID VARCHAR (20) PRIMARY KEY,
NAME VARCHAR (50),
TYPE VARCHAR (10),
CONTACT_NO BIGINT (10),
ADHAAR_NO BIGINT(12),
LAST_VISIT DATE );

CREATE TABLE STAFF
(PKSTAFF_ID VARCHAR (20),
NAME VARCHAR(50),
ADHAAR_NO BIGINT (12),
CONTACT_NO BIGINT(10) );

CREATE TABLE EXCHANGE
(TRANSACTION_ID VARCHAR(20) PRIMARY KEY,
FK1ACC_NO VARCHAR(20),
FK2MEMBER_ID VARCHAR (20),
FK3STAFF_ID VARCHAR(20),
DATE_OF_ISSUE DATE,
DATE_OF_RETURN DATE,
FINE INTEGER(20),
REMARKS VARCHAR(20),
CONSTRAINT FK1 FOREIGN KEY (FK1ACC_NO) REFERENCES BOOK (PKACC_NO)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK2 FOREIGN KEY (FK2MEMBER_ID) REFERENCES MEMBER (PKMEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK3 FOREIGN KEY (FK3STAFF_ID) REFERENCES STAFF (PKSTAFF_ID)
ON DELETE CASCADE
ON UPDATE CASCADE);

The last create statement returns:

ERROR 1005 (HY000): Can't create table `testdb`.`EXCHANGE` (errno: 150 "Foreign key constraint is incorrectly formed")

At times the best way to overcome an error within your InnoDB table is to check the InnoDB table status by using this command.
   SHOW ENGINE INNODB STATUS;

This will enlighten you on what you are doing wrong.


How to change password version by myself in oracle? Oracle documentation (18c) says:

....
You must expire the users who have only the 10G password version, and
do not have one or both of the 11G or 12C password versions.
For example:
ALTER USER username PASSWORD EXPIRE;
Ask the users whose passwords you expired to log in. When the users
log in, they are prompted to change their passwords. The database
generates the missing 11G and 12C password versions for their account,
in addition to the 10G password version. The 10G password version
continues to be present, because the database is running in the
permissive mode.
...

But, there is some way to do it by myself? (and to use the same old password)

If you have the proper permissions, you can change anyone's password. Of course, if you want to keep the password the same, just "changing" it so that it gets upgraded to new standards, you will need to know the current value.
And you don't have to expire it first. Expiring the password simply forces the user to set a "new" password the next time they log in. And even that depends on what client they are using. Some do not trap and handle expired passwords, and simply fail the logon.
Exactly which oracle doc were you quoting? (Never quote a source without full citation.)


PostgreSQL 8.4 no levenshtein fuzzymatching? I'm using debian squeeze package, it says function does not exist?

You have to install the additional module fuzzystrmatch.

In PostgreSQL 9.1 or later, simply run CREATE EXTENSION once per database:

CREATE EXTENSION fuzzystrmatch;

In PostgreSQL 8.4 on Debian Squeeze, you would run as OS postgres matching the DB superuser postgres (so with peer authentication without PW) the SQL script provided by the package postgresql-contrib-8.4 (install that if you haven't already):

sudo -u postgres psql mydb -f /usr/share/postgresql/8.4/contrib/fuzzystrmatch.sql

About CREATE EXTENSION:

Do I need to execute "create extension pgcrypto" everytime?


Logging queries and other T-SQL I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).

If yes, where can I see it? If not, how could I set it up?

By default, SQL Server activity is not logged the way you expect. Some write activity is recorded in the Transaction Log, but this also depends on how your databases are set up.

There are four main options for tracking SELECT activity on a server:

You can use SQL Server Profiler to connect to your server and watch for specific activity as it happens.
You can create a server-side trace to log activity to a trace file on the server, which can then be read by SQL Server Profiler, or by using fn_trace_gettable to load it into a table for further analysis.
You can use Extended Events, which offers more functionality than server-side traces, and which Microsoft recommends instead of server-side traces starting with SQL Server 2012.
You could use C2 audit mode.

You can use SQL Server Profiler to set up your trace (choose the specific events, filters, etc. that you want), then script it out using the File menu and execute it on the server to create a server-side trace, as described here.


Upgrading from single server to new GTID-replicated master and slave MySQL 5.7 I'm going from a single, non-GTID MySQL 5.6 server to a master-slave replicated setup on 5.7 (could be 8.0 too) with GTID on CentOS 7.x. Some details: format = ROW, minimal logging, file_per_table InnoDB

What's the best path to make the switch? The original server is in production, and I can stop it for discrete moments without much disruption.

If I set the new master and slave with replication from scratch and then import the database dumps (plain, no GTID data here) to the master, everything will get replicated right away, right?

My plan is this:

Stop the app associated with a database (WordPress blogs mainly, front-end caching can keep downtime unnoticed).
mysqldump individual database
Import dump into new master (replication propagates data to the slave)
Redirect app to new master, restart app

The slave will act as an emergency replacement and backup source while the master will bear the brunt of reads and writes. If load justifies it, maybe I will use the slave for reads and the master for writes in the future.

Am I too off in my plan?

I think your project is realistic.

I suggest a migration path below who can minimising the database downtime and make things smooth.

For this explanation, I named the Current database server PrdOld, the next database Master (RW) server Prd01 and the next database Slave (RO) Prd02.

As replication from 5.6 to 5.7 is supported, put your current MySql 5.6 database (PrdOld) as  a replication master

see at log_bin, relay_log, sync_binlog, server_id, expire_logs_days, binlog_cache_size, max_binlog_size my.cnf variables
don't search to enable GTID now
format=ROW and innodb_file_per_table are advisable

Prepare Prd01 as slave of PrdOld and master of Prd02
Prepare Prd02 as slave of Prd01

So we'll have a replication chain:

  PrdOld --> Prd01 --> Prd02

Do a full mysqldump (all replicated databases) of PrdOld
Write down File and Position from SHOW MASTER STATUS; command

For a simpler process I choose to enable replication right now from Prd01 to Prd02 but it's not mandatory, it depend of your database (size is a factor but not the only one) and activity (load, ...).

Restore your dump on Prd01 witch is propagated to Prd02 (it may generate some slave lag during this step)
Enable replication between PrdOld and Prd01

You have now three live and synced databases.

Observe for a while how things are working. And decide quietly when you want to switch the application db  connection context to prd01.
Then cut PrdOld to Prd01 replication mechanism and shutdown definitively PrdOld.

For the db HA aspect between Prd01 and Prd02 (in case of failure), choose to give some virtual IP to each db server (as IPmaster and IPslave) of use a load balancer in front of them (HAProxy, NGinx, ...).

Enable master-master replication (known also circular replication) between Prd01 and Prd02
Enable GTID mechanism

see at log_slave_updates, gtid_mode, enforce_gtid_consistency variables

Notice: You have to watch carefully the Seconds_Behind_Master indicator (during dump restore then after during production) in SHOW SLAVE STATUS\G. Big number is not necessary a problem is it doesn't stay for a while ...


Is the update in this MERGE Statement redundant? I'm trying improve the performance of a complicated stored procedure. Inside the stored procedure is the following MERGE statement. I'm not very familiar with the MERGE syntax.

The [Contact].[PhoneNumber] table looks like this:

CREATE TABLE [Contact].[PhoneNumber]
(
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [PhoneNumber] [nvarchar](255) NOT NULL
    [InsertedAtDateTimeUTC] [datetime2](7) NOT NULL DEFAULT(sysutcdatetime())
)

The merge looks like this:

;WITH _ContactPhoneNumbers ([PhoneNumber]) AS
(
    SELECT DISTINCT ([#ChannelData].[CallerAni])
    FROM [#ChannelData]
)
MERGE [Contact].[PhoneNumber] WITH (HOLDLOCK) _target
USING [_ContactPhoneNumbers] _source ON [_target].[PhoneNumber] = [_source].[PhoneNumber]

WHEN NOT MATCHED THEN
    INSERT ([PhoneNumber])
    VALUES ([PhoneNumber])

WHEN MATCHED THEN
    UPDATE
        SET [_target].[PhoneNumber] = [_source].[PhoneNumber]
        OUTPUT INSERTED.[Id], [_source].[PhoneNumber] INTO #Contact_PhoneNumber_Output ([Id], [PhoneNumber]);

I read this as:

If the PhoneNumber does not exist in the [Contact].[PhoneNumber] table the merge inserts a new row into the [Contact].[PhoneNumber] and returns the new Id. If PhoneNumber does exist then the merge updates the PhoneNumber to be the same value, and returns the Id for the existing row.

Is the UPDATE redundant?

If it's redundant I'll take it out and see if it increases the performance. There are several MERGE statements in the stored procedure which follows the same pattern (with the UPDATE) so it could be a useful gain.

The Id is returned even if it exists, as it's used as an FK in a further insert.

Something needs to be in the WHEN MATCHED THEN clause, otherwise you don't get the Id back when the row exists.

However, you don't need to update the target table.

DECLARE @dummy int;

;WITH    _ContactPhoneNumbers ( [PhoneNumber] )
              AS ( SELECT    DISTINCT
                            ( [#ChannelData].[CallerAni] )

                   FROM     [#ChannelData]
                 )
        MERGE [PhoneNumber]  WITH ( HOLDLOCK ) _target
        USING [_ContactPhoneNumbers] _source
        ON [_target].[PhoneNumber] = [_source].[PhoneNumber]
        WHEN NOT MATCHED THEN
            INSERT ( [PhoneNumber] )
            VALUES ([PhoneNumber] )
        WHEN MATCHED THEN
            UPDATE SET @dummy = 0
        OUTPUT
            INSERTED.[Id] ,
            [_source].[PhoneNumber]
        INTO #Contact_PhoneNumber_Output ( [Id], [PhoneNumber] );

Given the information in Use Caution with SQL Server's MERGE Statement, I'm going to ditch the MERGE completely. There's loads of LCK_M_RS_U contention too so I think I'll be better off with simpler SQL which I understand better.


Setting location of temp files for Postgresql backend (9.3.10) Is it possible to set the location of temp file creation for a Postgresql backend (ver 9.3.10)?

Here's my scenario: my Postgresql DB resides in a dedicated VM with Ubuntu 14.04 OS. My VM comes with 200GB of temporary high-performance SSD storage provided by my infrastructure provider, meant for short-term storage for applications and processes. Here's how it looks like:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt

I'm running low on disk space, and have to run some analytical queries that can take me to disk full due to the temporary files creation.

Apart from taking measures like deleting log entries to release more space or setting temp_file_limit in postgresql conf, I'm also interested in knowing whether I can set the location for temp files. After all, I have 220GB available for these kind of scenarios, so do want to put them to good use (swap space is set here too).

An illustrative example of how I can set this up would be very helpful since I'm a neophyte DBA.

There is a big problem with a_horse_with_no_name's solution. Not all instances of PostgreSQL even have a $PGDATA/base/pgsql_tmp because that entire path is actually created as needed in those instances. It doesn't even exist until needed, and it is destroyed when it is no longer required, so you can't create a symlink on an entire path that doesn't exist and that gets dynamically created then destroyed. You need to configue the temp tablespace location in the postgresql.conf file using the temp_tablespaces parameter (https://www.postgresql.org/docs/9.3/static/runtime-config-client.html#GUC-TEMP-TABLESPACES). This is because the temp_tablespace parameter not only designates the location of all temporary tables created by the CREATE command that do not have an explicit tablespace named at the time they are created, but also designates the location of all temporary files used for joins, sorts, and other overhead processing (https://www.postgresql.org/message-id/490ABE1D.3060700%40deriva.de).


Oracle 11g database- performance enhancement and sql tuning methods My production database(oracle 11g- windows platform) is growing(it's about 200 GB now) so that the risk of performance issue is also increasing.

Please give me some tuning tips used to follow by a DBA for high performance. What are the methods to follow while tuning the sql queries.

Also how to Identify the queries that are consuming considerable resources.It will be helpful if anyone can share the useful link of the sites mentioning about the same.

I'd recommend reading Oracle's official performance tuning guide for 11g:

https://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm


Optimization: One time vs Staging I ran into a situation with two options:

Use a single query with a cost of ~0.07

INSERT INTO @vals
SELECT ...
  FROM ...
  JOIN ...
  JOIN ...
 WHERE a.col = 1 OR b.col IS NOT NULL

Use two queries to get the same result -- the first cost ~0.05, the second cost ~0.3

INSERT INTO @temp
SELECT ...
  FROM ...
  JOIN ...

INSERT INTO @vals
SELECT ...
  FROM @temp
  JOIN ...
 WHERE a.col = 1
UNION ALL
SELECT ...
  FROM @temp
  JOIN ...
  JOIN ...
 WHERE b.col IS NOT NULL

I chose the first option - single query, so less concern of mutating data between queries and less overall cost.  Was this the prudent choice?

This was for an operation that originally took ~5+ minutes, timing out in our application.  The re-write got that down to 1.5 minutes consistently, using table variables with unique clustered indexes.

The difference between one and two queries may not be as large as you'd think. For example, compare this query:

declare @t table (id int)
insert into @t select id from Table1 where col1 = 7
update Table1 set col2 = 8 where id in (select id from @t)

to this query:

update Table1 set col2 = 8 where id in (select id from Table1 where col1 = 7)

For the second query, SQL Server could run the subquery first, and store the result in a  temporary table.  If it did that, that would be the EXACT SAME way it executes the first query!

So the effect of splitting a query in two is that it limits the query optimizer's choice.  The first query demands that the subquery is executed before the updates.  The second query leaves the optimizer free to fetch one row, then update the table, and then fetch the next row.

Now when you upgrade the hardware, SQL Server version, or if the database changes, you would ideally re-verify that your limitation is in fact beneficial. That's kind of expensive.

So I would prefer not split the query, or enforce a query plan, unless the expense is offset by a considerable advantage.  Going from 0.07 to 0.3 seconds doesn't look like a big difference to me, unless the query is run more than once every minute or so.


Triggers that inserts values to a table from another table I am trying to create a report table that contains drug_id, total sales and average sales from an inventories table. This code doesn't work as it should. It duplicates records and when I insert additional rows in inventories, it doesn't reflect. I am new to triggers and complex sql queries, please assist.

DELIMITER //
CREATE TRIGGER `report` AFTER INSERT ON `inventories`

FOR EACH ROW
  insert into report (drug_id,total_sales,average_sales,amount_remaining)
 select

i.drug_id,
coalesce(p.total_sales, 0) total_sales,
coalesce(s.average_sales, 0) average_sales,
coalesce(t.amount_remaining, 0) amount_remaining

from inventories i
left join
(
 select drug_id, sum(quantity_sold) total_sales
from inventories
WHERE MONTH(complete_sold) = 01
group by drug_id
) p
on i.drug_id = p.drug_id
left join
(
select drug_id, avg(quantity_sold) average_sales
 from inventories
WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'
group by drug_id
) s
 on i.drug_id = s.drug_id
 left join
(
  select drug_id, sum(amount_remaining) amount_remaining
from inventories
 group by drug_id
 ) t
 on i.drug_id = t.drug_id

 //
DELIMITER ;

It appears you have ommited the BEGIN and END tags in your TRIGGER declaration.

COALESCE(), SUM(), and AVG() calls need a table reference, looks like your passing a presumed column reference, also FROM keyword is missing after these calls.

Its difficult to follow your database structure from this example.  Tables 'report' and 'inventories' are clear enough, but 'i', 'p', 's', 't' are not.  The column names are descriptive, but its not always clear what table or tables they reside in.

The one thing that sticks out most to me is two table names supplied in the FROM clause on line 13, and the extra table supplied after all three closing parentheses in your LEFT JOIN statements.

Are you wanting this report to be run AFTER every time an UPDATE query is performed on the 'inventories' table?  You may be better served by building a INSERT query that builds this report on demand, see if it produces what you want, and then threading it into a TRIGGER if you want it automated.

For example, something like this, presuming it's supplied a specific drug_id to run with.  Though, with a separate database call and a while loop, you could easily run this on every drug_id.

INSERT INTO report ( drug_id, total_sales, average_sales, amount_remaining)
   VALUES (
( SELECT drug_id FROM i WHERE drug_id = $report_focus),
( SELECT COALESCE(total_sales, 0) FROM p WHERE MONTH(complete_sold) = 01 ),
( SELECT COALESCE(average_sales, 0) FROM s WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'),
( SELECT COALESCE(amount_remaining, 0) FROM t)
   );

I find it helpful to use ALL CAPS on MySQL SQL commands and functions for easier seperation between keywords and content.

Helpful links:
MySQL Trigger Syntax
Hive SQL query checker


How to create mysql user with CREATE USER grant but without access to new databases? I need mysql admin_user (not root user) which can create write_user with permissions: CREATE, UPDATE, INSERT, DELETE and SELECT.

admin_user and write_user have access to main_db.

write_user will create user_db and only he can access to that db.

If I want that admin_user can create new user with some permissions, admin_user has to be created "WITH GRANT OPTION" but then he has access to all databases.

Question: How to create mysql user with CREATE USER grant but without access to new databases?

With the GRANT command, you can use the ON keyword to set table privileges for users.

For example: GRANT ALL ON user_db to 'write_user'@'localhost'


ALTER TABLE ... SWITCHing from regular table to partitioned table fails The code below does the following:

Creates a database play_partition in C:\TEMP
Creates two identical partitioned tables play_table and archive_play_table
Switches play_table partition 1 to archive_play_table partition 1
Creates a new unpartitioned table temp_table with the same structure as play_table on the same filegroup as play_table partition 2
Switches play_table_partition 2 to temp_table
Tries to switch temp_table back to play_table partition 2 and fails with

Msg 4982, Level 16, State 1, Line 64
ALTER TABLE SWITCH statement failed. Check constraints of source table
'play_partition.dbo.temp_table' allow values that are not allowed by
range defined by partition 2 on target table
'play_partition.dbo.play_table'.

Why does it fail?

I am using SQL Server 2014 (Enterprise Edition Trial).

Regards,

Colin Daley

http://www.colindaley.com/translator

/* Playing with partitioned tables */

USE master;
GO

DROP DATABASE play_partition;
GO

CREATE DATABASE play_partition
    ON PRIMARY(
        NAME = play_partition
        , FILENAME = 'C:\TEMP\play_partition.mdf')
    ,FILEGROUP play_fg1(
        NAME = play_fg1
        ,FILENAME = 'C:\TEMP\play_fg1f1.ndf')
    ,FILEGROUP play_fg2(
        NAME = play_fg2f1
        ,FILENAME = 'C:\TEMP\play_fg2f1.ndf');
GO

USE play_partition;

CREATE PARTITION FUNCTION play_range(INT)
    AS RANGE LEFT FOR VALUES(3);

-- Partition scheme
CREATE PARTITION SCHEME play_scheme
    AS PARTITION play_range TO (play_fg1, play_fg2);

-- Partitioned tables
CREATE TABLE dbo.play_table(
    c1 INT NOT NULL CONSTRAINT PK_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

CREATE TABLE dbo.archive_play_table(
c1 INT NOT NULL CONSTRAINT PK_archive_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

-- partition 1 = {1, 2, 3}, partiion 2 = {4, 5, 6}
INSERT INTO dbo.play_table(c1) VALUES (1), (2),  (3), (4), (5), (6);

-- move partition 1 from play_table to archive play_table
ALTER TABLE dbo.play_table
    SWITCH PARTITION 1 to dbo.archive_play_table PARTITION 1;

-- create empty table with same structure as dbo.play_table
SELECT * INTO dbo.temp_table FROM dbo.play_table WHERE 1 = 0;

-- move temp_table to filegroup play_fg2
ALTER TABLE dbo.temp_table
    ADD CONSTRAINT PK_temp_table_c1 PRIMARY KEY CLUSTERED(c1) ON play_fg2;

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

-- move data back to partitioned play_table from unpartitioned temp_table
-- FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

SELECT 'archive_play_table' as table_name, t1.c1
    FROM dbo.archive_play_table AS t1
    UNION ALL
    SELECT 'temp_table' AS table_name, t1.c1
        FROM dbo.temp_table as t1
    ORDER BY 1, 2;

When you are working with partition switching, SQL Server will need to verify that the source table/partition boundaries can fit in the destination table/partition boundaries.  In other words, you're trying to switch data from dbo.temp_table to dbo.play_table's partition 2.  Think of it like this, the data for the c1 in dbo.temp_table is constrained only by the data type (int), so you can have values ranging from -2,147,483,648 to 2,147,483,647.  But conversely, you're destination (dbo.play_table partition 2) has a range from 4 to 2,147,483,647.

Your data does not violate this, but it is the metadata that can't allow this.  You could just as easily insert the value -10 into dbo.temp_table.  The partition switching would fail the same way and make more sense, as -10 does not fit in dbo.play_table's 2nd partition boundaries.

If you wanted to make this code work, you'd need to explicitly tell SQL Server that dbo.temp_table will never have any data that won't fit in dbo.play_table's 2nd partition.  You could do this with a check constraint:

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

/******************************************************************************
    added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
    end of added code
******************************************************************************/

-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

That above sample addition to your code makes this a working solution.  Now SQL Server knows that the data in dbo.temp_table can fit in partition 2 of dbo.play_table because of the added check constraint to dbo.temp_table.


What is the best way to filter or otherwise manipulate the results returned by a stored procedure? Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is the best way to achieve this?

One possible solution is to put the result set into a temp table and select from it. Here is an example for sp_who2: SQL Server: Filter output of sp_who2


Eliminate extra rows that intersect I have a table of geometry values where some rows intersect other rows.
I need a list of rows where the geometry overlaps other rows, but I'd like the list to be as concise as possible.
Here's the setup:
USE tempdb;
DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t
(
    n varchar(100) NOT NULL
    , i geometry NOT NULL
);

INSERT INTO dbo.t (n, i)
VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326))
     , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326))
     , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326))

SELECT t1.n
    , t2.n
FROM dbo.t t1
    INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1
WHERE
    t1.n <> t2.n;

The output looks like:

n
n

poly2
poly1

poly1
poly2

However, I'd like only a single row for brevity.  i.e. because poly1 overlaps poly2 and poly2 overlaps poly1 I'm getting two rows returned where I'd like only one, as in:

n
n

poly1
poly2

Then you need an < or > instead of a <>.
That is similar to when you try to find dupes in your data

CREATE TABLE dbo.t
(
    n varchar(100) NOT NULL
    , i geometry NOT NULL
);

INSERT INTO dbo.t (n, i)
VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326))
     , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326))
     , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326))

SELECT t1.n
    , t2.n
FROM dbo.t t1
    INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1
WHERE
    t1.n < t2.n;
GO

n     | n
:---- | :----
poly1 | poly2

db<>fiddle here


Send Email To Multiple Recipients Creating a group or mailing list is not an option, and this may not be possible, but I need a way of scanning EmailMe and pulling the email address(es) to email from there.  If two email addresses have the same `vendorID ONE email should be sent not multiple.  Below is DDL - and using this as an example purple and green should both be the recipient on one email and blue should be sent it's own email.

How can I perform this?  I am going to use dbsendmail to send the message.

Create Table EmailMe
(
  ID  int IDENTITY(1,1) PRIMARY KEY NOT NULL
  ,name varchar(100)
  ,vendorid int
  ,email varchar(500)
)

Insert Into EmailMe (ID, name, email) VALUES
('purple', 12, 'purple@hsbcglobal.com')
,('green', 12, 'green@hsbcglobal.com')
,('blue', 1, 'blue@hsbcglobal.com')

You can select from your table into a semicolon-delimited string.  Pass that string variable to the SQL send mail procedure.

DECLARE @EmailRecipients VARCHAR(MAX);

select @EmailRecipients = COALESCE(@EmailRecipients + ';', '') + Email
from EmailMe

SELECT @EmailRecipients


Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me:

CREATE TABLE tbl (created_at timestamp, status text);
INSERT INTO tbl VALUES
  ('2016-04-05 00:27:15', 'info')
, ('2016-04-05 03:27:15', 'info')
, ('2016-04-05 05:27:15', 'warn')
, ('2016-04-05 10:27:15', 'info')
, ('2016-04-05 11:27:15', 'warn');

The logic would be to count events that happened up to and excluding the next bound. This fits the often overlooked function width_bucket() perfectly. To be precise, it requires the variant with arbitrary bounds (since there is no regular pattern in the OP's bounds) introduced with Postgres 9.5. Explanation straight from the manual:

width_bucket(operand anyelement, thresholds anyarray)

  return the bucket number to which operand would be assigned given an
  array listing the lower bounds of the buckets; returns 0 for an input
  less than the first lower bound; the thresholds array must be sorted,
  smallest first, or unexpected results will be obtained

For regular buckets you can use another variant that's available in Postgres 9.1 as well.
Combine it with crosstab() re-using the same bounds as column names (the rest of the query works with Postgres 9.1):

SELECT * FROM crosstab(
 $$SELECT status
        , width_bucket(created_at, '{2016-04-05 01:00
                                   , 2016-04-05 04:00
                                   , 2016-04-05 08:00
                                   , 2016-04-05 12:00}'::timestamp[])
        , count(*)::int
   FROM   tbl
   WHERE  created_at < '2016-04-05 12:00'  -- exclude later rows
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$
, 'SELECT generate_series(0,3)'
   ) AS t(status text, "2016-04-05 01:00" int
                     , "2016-04-05 04:00" int
                     , "2016-04-05 08:00" int
                     , "2016-04-05 12:00" int);

Result:

 status | 2016-04-05 01:00 | 2016-04-05 04:00 | 2016-04-05 08:00 | 2016-04-05 12:00
--------+------------------+------------------+------------------+------------------
 info   |                1 |                1 |                  |  1
 warn   |                  |                  |                1 |  1

The second crosstab parameter ('SELECT generate_series(0,3)') is a query string when executed returning one row for every target column. Every value not found on either side - not in the raw data or not generated by the 2nd parameter - is simply ignored.

Basics for crosstab():

PostgreSQL Crosstab Query

Replace NULL with 0

If you need 0 instead of NULL in the result, fix with COALESCE(), but that's merely a cosmetic problem:

SELECT status
     , COALESCE(t0, 0) AS "2016-04-05 01:00"
     , COALESCE(t1, 0) AS "2016-04-05 04:00"
     , COALESCE(t2, 0) AS "2016-04-05 08:00"
     , COALESCE(t3, 0) AS "2016-04-05 12:00"
FROM   crosstab(
 $$SELECT status
        , width_bucket(created_at, '{2016-04-05 01:00
                                   , 2016-04-05 04:00
                                   , 2016-04-05 08:00
                                   , 2016-04-05 12:00}'::timestamp[])
        , count(*)::int
   FROM   tbl
   WHERE  created_at < '2016-04-05 12:00'
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$
, 'SELECT generate_series(0,3)'
   ) AS t(status text, t0 int, t1 int, t2 int, t3 int);

Result:

 status | 2016-04-05 01:00 | 2016-04-05 04:00 | 2016-04-05 08:00 | 2016-04-05 12:00
--------+------------------+------------------+------------------+------------------
 info   |                1 |                1 |                0 |  1
 warn   |                0 |                0 |                1 |  1

Adding totals

To add totals per status use the new GROUPING SETS in Postgres 9.5+

SELECT status
     , COALESCE(t0, 0) AS "2016-04-05 01:00"
     , COALESCE(t1, 0) AS "2016-04-05 04:00"
     , COALESCE(t2, 0) AS "2016-04-05 08:00"
     , COALESCE(t3, 0) AS "2016-04-05 12:00"
     , COALESCE(t4, 0) AS total
FROM   crosstab(
 $$SELECT status, COALESCE(slot, -1), ct  -- special slot for totals
   FROM  (
      SELECT status
           , width_bucket(created_at, '{2016-04-05 01:00
                                      , 2016-04-05 04:00
                                      , 2016-04-05 08:00
                                      , 2016-04-05 12:00}'::timestamp[]) AS slot
           , count(*)::int AS ct
      FROM   tbl
      WHERE  created_at < '2016-04-05 12:00'
      GROUP  BY GROUPING SETS ((1, 2), 1)  -- add totals per status
      ORDER  BY 1, 2
      ) sub$$
 , 'VALUES (0), (1), (2), (3), (-1)'  -- switched to VALUES for more sophisticated series
   ) AS t(status text, t0 int, t1 int, t2 int, t3 int, t4 int);

Result like above, plus:

...  | total
... -+-------
...  |     3
...  |     2

Note that total includes all rows not excluded before aggregation, even if filtered by crosstab().

This is in reply to @Vérace's request in the comments rather than to the unclear question.


How to have SQL Server's SQL Agent pick up a new Powershell module I have a Powershell module that I've written and uploaded to my SQL Server at C:\Program Files\WindowsPowerShell\Modules.I have a SQL Agent job with a powershell step that calls the module.  This all works and has been working.

However, I recently made a change to the way the Powershell module works.  I updated the code and uploaded it to the server, but SQL Server seems to be executing the outdated version of the code still, even though the file has been replaced.

Do I need to tell SQL Server or Powershell to "refresh" the module somehow?  How can I do that?

Do I need to tell SQL Server or Powershell to "refresh" the module somehow? How can I do that?

You can use import-module yourModuleName -Force.


how to add option(recompile) within this stored procedure? In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select exists is run and based on the result, different logic paths are run within the stored procedure.

I have a couple of questions regarding the procedure below:

1) is it a good candidate for parameter sniffing?

2) how could I add option(recompile) within the code?

There are differences between adding option(recompile) within the code and creating a stored procedure with recompile.

I would be more keen on the option(recompile) if that is possible.

ALTER PROCEDURE [dbo].[usp_upd_activity]
                    @activityId             INT,
                    @title                  VARCHAR(100),
                    @description            VARCHAR(MAX),
                    @inclusions             VARCHAR(MAX),
                    @locationId             INT,
                    @imageUriMain           VARCHAR(255),
                    @uploadToBucket         VARCHAR(200),
                    @path                   VARCHAR(200)
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN TRAN
    BEGIN TRY

        DECLARE @documentId INT

        IF NOT EXISTS (SELECT 1
                         FROM document
                        WHERE activityId    = @activityId)
        BEGIN
            INSERT INTO  document
                        (   [uploadToBucket], [path], [activityId])
            VALUES      (@uploadToBucket, @path, @activityId)

            SET @documentId = SCOPE_IDENTITY();

        END
        ELSE
        BEGIN
            UPDATE  document
            SET     uploadToBucket  = @uploadToBucket,
                    [path]          = @path,
                    activityId      = @activityId
            WHERE   activityId      = @activityId

            SET @documentId =
                (SELECT documentId
                   FROM document
                   WHERE activityId = @activityId)
        END

        UPDATE          activity
        SET             title           =       @title,
                        description     =       @description,
                        inclusions      =       @inclusions,
                        locationId      =       @locationId,
                        imageUriMain    =       IsNull(@imageUriMain, imageUriMain),
                        documentId      =       @documentId
        WHERE           activityId      =       @activityId

        COMMIT

    END TRY
    BEGIN CATCH
        DECLARE     @ErrorMessage   nvarchar(max),
                    @ErrorSeverity  int,
                    @ErrorState     int;
        SELECT      @ErrorMessage = ERROR_MESSAGE() + ' Line ' +
                                    cast(ERROR_LINE() as nvarchar(5)),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

        IF @@TRANCOUNT > 0
            ROLLBACK;

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

END

No, this does not look to be a good candidate for recompile. From the code, it looks like activityId is unique and will result in a trivial plan so you'll get the same plan regardless of the parameters passed. Add DDL to your question if this is not the case in your situation.

In cases where the optimal plan differs based on the actual parameter values passed (because these are used in WHERE/JOIN clauses and the optimal plan varies accordingly), you could specify the OPTION(RECOMPILE) query hint on specific problem queries to avoid reusing non-trivial plans. However, if the query is executed quite frequently, the compilation cost could outweigh the benefits. It would be better in that case to use an OPTIMIZE FOR hint or use the query store (or plan guides) to coerce a specific plan.


Does changing `wal_keep_segments` require a restart? I have a need to increase wal_keep_segments on our master server. Can I do that on the fly or does it require a restart?

For checking the variable's context, you can execute the following query:

SELECT name, context FROM pg_settings WHERE name = '<variable name';

In the case of wal_keep_segments, the context is sighup which means that only requires a server reload. You can use pg_ctl reload from the shell prompt or select pg_reload_conf() from psql or database client.

In the latest version, there are 7 types of context. internal context means that only can be modified at compilation time, postmaster means that a service restart is needed, the others are more session/backend specific.


View dropped, now shows as table "in use", can't drop I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.

I tried dropping the view so I could recreate it, then MySQL said it was a table (not a view) and marked it "in use".

Now I can't drop nor repair that table because it says it is not a table.

I've googled for hours trying find a way to fix this :(

I do have root access on the MySQL server running Ubuntu. I stopped MySQL and tried myisamchk, but no luck.

I can see a .frm file for the view in the db data folder.

Do I need to delete that file to make the corrupt item go away,
or do I somehow delete the table from the db schema?

I have a db backup from last night, but would rather fix this 1 issue than restore from backup and lose today's changes.

You should be able to simply rename that file to just about anything that doesn't end in .frm and then SHOW TABLES.  It should be gone.  If it's still there, FLUSH TABLES, then SHOW TABLES again.


What are good resources on data modelling for business intelligence? We are a SaaS startup offering purchase & procurement solutions. The data model for operational transactions has been created (relational DB). We also want to offer analytics on those transactions to decision makers but using ad hoc queries to get this data out of the existing tables would be inefficient. What resources are out there on data modelling for business intelligence / data warehousing?

I recommend the Data Warehousing Toolkit by Ralph Kimball.  This is a solid book on dimensional modeling and provides many "soup to nuts" case studies.


using view (necessary) with where conditions (dynamically) Suppose we have two tables like A {a1, a2, a3 ...} and B{b1, b2, b3 ...}

We are using a MySQl view like

Select a.a1, b.b1 from A a Left JOIN  B b ON a.a2=b.b2

Now our requirement is that we have to put "where conditions" dynamically on some columns of A and B, so we have to again join table and putting a where condition (In current code).

Now as joining twice is increasing our query execution time and I am trying to reduce the time.

We can't remove the view as per the security requirement (SQL SECURITY).

Is there a way to handle this scenario, I thought of using functions with "SQL SECURITY" or there are any other alternatives.

Try a newer version of MySQL. The view implementation in MySQL-5.5 was very simplistic. I've seen later versions take the view expression into account when doing query planning.


INSERT INTO with SELECT subquery db2 9.5 I have the following query that takes 4 seconds to execute:

SELECT TX.* FROM TABLE_X TX
  INNER JOIN TABLE_Y TY
    ON TY.VALUE_ID = VALUE_ID_IN
      AND TY.PROD_ID = TX.PROD_ID
WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
  AND (TX.DATE > TY.FIRST_DATE)
  AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
  AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

However, I want to store it in a TEMPORARY TABLE.  When I execute the following query it takes 50 seconds:

INSERT INTO SESSION.TEMP_TABLE_X   /*new line to store result*/
    SELECT TX.* FROM TABLE_X TX
      INNER JOIN TABLE_Y TY
        ON TY.VALUE_ID = VALUE_ID_IN
          AND TY.PROD_ID = TX.PROD_ID
    WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
      AND (TX.DATE > TY.FIRST_DATE)
      AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
      AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

TABLE_X = 300 000 rows

TABLE_Y =  90 000 rows

How can I reduce the 50 seconds running time?

The server is running DB2 v9.5.

I'll put this as an answer as it is easier to comment on.

Generally you need to run RUNSTATS on every table (I'd vote for at least once a week). This tells the optimizer the number of records (and if specified, which I do recommend) the distribution of data across the records. With this information the optimizer can make better decisions regarding access paths.

The optimizer also uses packages as well. REBINDING the packages essentially alters the packages (ie, their access paths) based on the RUNSTATS found.

In your case you could run RUNSTATS on the tables you care about

RUNSTATS ON TABLE <schema>.<table> WITH DISTRIBUTION AND DETAILED INDEXES ALL

If you have a lot of tables, then I'd recommend hunting for them dynamically

--do note syntax is for Unix, having to worry about escape characters and what not.
db2 -tnx  "select 'runstats on table ' ||
ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname))
|| ' with distribution and detailed indexes all ;'
from syscat.tables where type = 'T' and ownertype = 'U' and
tabschema not in ('SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS')
order by tabschema asc" >> runstats.sql

You can then read this in to your database and execute it

db2 -tvf runstats.sql

REBINDs I find the easiest to do using the db2rbind utility. It beats having to know all the specific package names. Since we are a small shop I just tend to use the following syntax each time.

db2rbind <my db> -l rebind.log all -r any

If there are errors, you will see it in the log.

So generally you would want to issue the RUNSTATS first, then issue a COMMIT to release any locks. Then you can run the REBINDs through db2rbind. If you have not run these in a while you may wish to throw a db2 flush package cache dynamic in between the RUNSTATS and the db2rbind.


Getting all descendants of a parent I have a table with two columns, Parent and Child.

I need to get the list of all descendants associated with the parent records.

Source Table
+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |
|  b     |     c |
|  c     |     d |
|  d     |     e |
|  e     |     f |
|  f     |     x |
+----+-----------+

Expected Result:
+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |  // As b is the child of a, all the descendants of b
|  a     |     c |  // are also descendants of a.
|  a     |     d |
|  a     |     e |
|  a     |     f |
|  a     |     x |
|  b     |     c |  // As c is the child of b, all the descendants of c
|  b     |     d |  // are also descendants of b.
|  b     |     e |
|  b     |     f |
|  b     |     x |
|  c     |     d |
|  c     |     e |
|  c     |     f |
|  c     |     x |
|  d     |     e |
|  d     |     f |
|  d     |     x |
|  e     |     f |
|  e     |     x |
|  f     |     x |
+----+-----------+

Any ideas how to get all the descendants records of a parent?
I have tried self join using T1.Child = T2.Parent but the logic did not work.

I am using Teiid VDB which doesn't support Recursive WITH clause.

You need a recursive CTE (common table expression):

with    -- recursive
        -- some DBMS (e.g. Postgres) require the word "recursive"
        -- some others (Oracle, SQL-Server) require omitting the "recursive"
        -- and some (e.g. SQLite) don't bother, i.e. they accept both
descendants
  (parent, descendant, lvl)
as
  ( select parent, child, 1
    from source
  union all
    select d.parent, s.child, d.lvl + 1
    from descendants  d
      join source  s
        on d.descendant = s.parent
  )
select *
from descendants
order by parent, lvl, descendant ;

See the SQLfiddle. The level I added in not needed. It gives you the "distance" between the parent and the descendant, (1 is child, 2 is grandchild, etc).

Link for the related Teiid documentation about: (recursive) CTEs.

PS:
For Oracle:

LEVEL is a restricted keyword


Dynamic Select with error in where conditon @P_SqLCommand NVARCHAR(100) OUTPUT, --output which will be return
    @P_SqlList NVARCHAR(30),-- columns that needs to be filtered out
    @P_Table NVARCHAR(30),--Table Name needs to be use in the select statment
    @P_Input NVARCHAR(50),--the filter variable
    @P_Num varchar(5)
SET @P_Input='@P_Input'

    SET @P_SqLCommand='SELECT Top '+ @P_Num +' '+@P_SqlList+'  FROM '+
    @P_TABLE +' where '+ @P_SqlList + '=' + @P_Input;

give me an error:

  Incorrect syntax near '='.

Any suggestions ?

I am using SQL Server. What am trying to do is to use stored procedure to for dynamic select statement and pass through by parameters, it is work fine till the where statement.

How I can treat @P_Input as character?

Whatever value of @P_SqlList the final WHERE sentence will be something like:

WHERE [SOME_FIELD] = @P_Input

due you are setting it here:

SET @P_Input='@P_Input'

To solve this, add @P_Inputto the parameters list. Or, build the whole filter outside the dynamic query and pass it as a single value:

WHERE ' + @Filter

This form allow you to build a more complicated WHERE sentence.


MongoDB Insufficient free space for journal files I have installed a standalone mongo 2.6 in amazon ec2. The instance has reach the limit of disk space and it doesnt run now. I have set before the smallfiles=true in mongod.conf file, but for some reason this doesn't work neither.
The log file says:

  2015-03-12T19:41:26.881+0000 [initandlisten] ERROR: Insufficient free space for journal files
  2015-03-12T19:41:26.881+0000 [initandlisten] Please make at least 422MB available in /data/journal or use --smallfiles
  2015-03-12T19:41:26.881+0000 [initandlisten] exception in initAndListen: 15926 Insufficient free space for journals, terminating

I tried to set journal path in another volume >

  sudo ln -s /journal /data/journal

But this doesn't work neither and logs the same error above :(

I want to mantain the /data (with all dbs files) in the same disk , I only want to move the journal path to another disk , is that possible?
Please , any help is welcome!

pd: I used before tune2fs for get more space , but now I don't have it.

I got the solution by typing the
mongod --smallfiles


How to display True & False for 1 & NULL In my query I am able to get column values as 1 and NULL. I would like to display True & False for 1 & NULL?

/*Declare Variable*/
DECLARE @Pivot_Column [nvarchar](max);
DECLARE @Query [nvarchar](max);

/*Select Pivot Column*/
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(PracticeLocationServiceCode) FROM
(SELECT DISTINCT [PracticeLocationServiceCode] FROM PracticeLocationService)Tab

/*Create Dynamic Query*/
SELECT @Query='
;WITH ServiceOffered
AS
(
SELECT PracticeLocationID, '+@Pivot_Column+'FROM
(SELECT PracticeLocationID, PracticeLocationServiceCode,CAST(ServiceFlag AS INT) ServiceFlag FROM PracticeLocationService )Tab1
PIVOT
(
MAX(ServiceFlag) FOR [PracticeLocationServiceCode] IN ('+@Pivot_Column+')) AS Tab2
)
SELECT REGION,LocationShortName,GLCODE,C.* FROM ServiceOffered C
INNER JOIN VWLOCATIONS V ON V.PracticeLocationID = C.PracticeLocationID
ORDER BY REGION,LocationShortName
'
/*Execute Query*/
EXEC  sp_executesql  @Query

SQL Server does not have the Boolean data type. There are no built-in values true and false. One alternative is to use strings 'true' and 'false', but these are strings just like any other string. Often the bit type is used instead of Boolean as it can only have values 1 and 0. Typically 1 is used for "true" and 0 for "false". It is still an integer type, though. Boolean logic will not work. For example, if (CONVERT(bit, 1) and CONVERT(bit, 1))... is not legal syntax.

Likely the best option is to use a CASE and return the requisite strings, as @Akina commented:

CASE
    WHEN TheColumn = 1 THEN 'True'
    WHEN TheColumn IS NULL THEN 'False'
    ELSE 'Oops!'
END


When installing Oracle, what's the quickest way to check rpm packages and install what is  missing? When installing Oracle, there is a step which is to install all necessary packages. I use the following command to test whether a package is installed and install them if they are missing:

rpm -qa | grep binutils

I know I can verify all packages in this way, but I want to know whether there is a better and quicker way to do this task.

Thanks.

Check out Howard Roger's gladstone shell script. His scripts have always done a great job for me in performing all the necessary pre-installation tasks.


Design for graph polymorphism I have a graph database with multiple edge types. For example

create table Person(Name varchar(50)) as node;
create table Friend as edge;
create table Manager as edge;

insert Person(Name) values ('Alice'), ('Bob'), ('Chris');

insert Friend($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob');

insert Manager($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris');

I can find all the nodes connected to a given node (graph polymorphism) through UNION queries:

select
    b.Name
from Person a, Person b, Friend f
where a.Name = 'Alice'
and match(a-(f)->b)

union

select
    b.Name
from Person a, Person b, Manager m
where a.Name = 'Alice'
and match(a-(m)->b);

However, this rapidly becomes cumbersome. SQL Server does not have a more elegant solution built into the syntax.

Is there a design pattern which allows for polymorphism?

The disparate edges tables can be combined into a single table, holding all associations:

create table Connection(ConnectionType varchar(20)) as edge;

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob'),
    'Friend';

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris'),
    'Manager';

This table can be queried to retrieve all associations

select
    b.Name,
    c.ConnectionType
from Person a, Person b, Connection c
where a.Name = 'Alice'
and match(a-(c)->b);

Giving

Name     ConnectionType
-------  --------------------
Bob      Friend
Chris    Manager

Column ConnectionType is optional, but allows for the same level of specificity as the original schema did.

This approach is analagous to an EAV design, and likely to suffer the same performance issues at scale. Filtered indexes will help

create nonclustered index IX_Friend on Connection($from_id, $to_id)
where ConnectionType = 'Friend';


SQL Server 2014 SP1 installation iso file question I read a lot regarding the issues with the original SP1 slipstream file and if
the file names to download can be clarified it will be very helpful.

What files do I need to install SQL 2014 SP1 CU6? I have the developer edition license and will not be using this in production. Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used?

I expect the file size to be around 4GB so the file size indicated does not seem correct for slipstream SP1 ISO

Is there a separate download required for SSMS 2014 etc.

I apologize as this is a very basic question. Thank you

SQLServer2014SP1-KB3058865-x64-ENU.exe
Date Published:5/14/2015
File Size: 639.3 MB

SQL14SP1CU6\x64\SQLServer2014-KB3144524-x64.exe
Date Published:4/18/2016
File Size: 579.0 MB

What files do I need to install SQL 2014 SP1 CU6?

You would need

SQL Server 2014 RTM setup
SQL Server 2014 SP1 Setup. This can be downloaded from here
You would need SQL Server 2014 Sp1 CU6 setup. This can be downloaded from here.

Note: Quite a few times you would find SP1 embedded in SQL Server 2014 installation files in that case you dont need to download Sp1 you just need to download CU6. When SP1 is embedded it wuld be mentioned in product software like SQL Server 2014 Sp1 Developer edition. If you have option go for this one.

  Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used?

There is no Slipsteam file available for download its a way to install updates with SQL Server. Yes you can install SQL Server 2014, SP1 and Sp1 CU6 all together

Microsoft blogs already has information about Create a merged (slipstream) drop containing SQL Server 2008, Server Pack 1 and a Cumulative Update (CU) based on Server Pack 1.

I also suggest you read SQL Server 2008 Slipstreaming FAQ

Boris Hristov has also written good article see This link. This uses command line to install SQL Server and the updates.

Here is what we should do to slipstream SQL Server 2012 and 2014:

Download SQL Server 2012 or SQL Server 2014 media
Download all the needed SPs, CUs and Hotfixes that you want to
“embed”
Put all of the .exe files in one directory
Launch the setup.exe from CMD by issuing the command:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”path_to_the_directroy_where_the_hotfixes_are”

Place all the Sp and CU on one location after extraction and give the location in /UpdateSource

For example if location is C:\SQLServerFixes the command would be

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”C:\SQLServerFixes”

  Is there a separate download required for SSMS 2014 etc.

No, while installing the update you can select the features you want to update.

EDIT:

  Do I have to download a different file for developer edition Or download EE and provide developer license information.

Developer edition is same as enterprise in functionality but you can ONLY use developer edition for development. Starting from 31 March 2016 Dev edition is free to download for Visual studio DEV essential members. So if you are grab it. Their is no point in going for EE edition.

  SQL 2014 RTM download version without CU as SP1 would cover it? What filename / size would this be - if this information is easily available.

You have to just download as I have said in starting


Where does extra bytes come from Here is my table:

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| tckno       | bigint(20)    | YES  |     | NULL    |                |
| adid        | mediumint(9)  | NO   |     | NULL    |                |
| soyadid     | mediumint(9)  | NO   |     | NULL    |                |
| anaadid     | mediumint(9)  | NO   |     | NULL    |                |
| babaadid    | mediumint(9)  | NO   |     | NULL    |                |
| cinsiyet    | enum('E','K') | NO   |     | NULL    |                |
| dogumyeri   | mediumint(9)  | NO   |     | NULL    |                |
| dogumtarihi | date          | NO   |     | NULL    |                |
| nufusil     | tinyint(4)    | NO   |     | NULL    |                |
| nufusilce   | smallint(9)   | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

Now, according to https://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html my row size is

id(4) + tckno(8) + next 4 rows (4*3) + cinsiyet(1) + dogumyeri(3) + dogumtarihi(3) + nufusil(1) + nufusilce(2) = 34 bytes

I should have 48166478 (row count) * 34 = 1637660252 bytes database. But when I navigate to mysql/data/<database name> folder, I see that <table name>.idb file is 2826960896 bytes on disk. There is a discrepency of 2826960896 bytes - 1637660252 bytes ≈ 1.1GB. What is this extra data?

Here is what I get from exporting said table

CREATE TABLE `optimumtablo` (
  `id` int(11) NOT NULL,
  `tckno` bigint(20) DEFAULT NULL,
  `adid` mediumint(9) NOT NULL,
  `soyadid` mediumint(9) NOT NULL,
  `anaadid` mediumint(9) NOT NULL,
  `babaadid` mediumint(9) NOT NULL,
  `cinsiyet` enum('E','K') NOT NULL,
  `dogumyeri` mediumint(9) NOT NULL,
  `dogumtarihi` date NOT NULL,
  `nufusil` tinyint(4) NOT NULL,
  `nufusilce` smallint(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `optimumtablo` ADD PRIMARY KEY (`id`);

ALTER TABLE `optimumtablo` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

This is show table status output for said table;

+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| optimumtablo | InnoDB |      10 | Compact    | 47621825 |             57 |  2759852032 |               0 |            0 |   4194304 |       48166479 | 2015-09-11 20:53:45 | NULL                | NULL                | utf8_general_ci   |     NULL |                |         |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+

Part of the difference can be explained by:

InnoDB stores the table as B-tree inside its Primary key (primary key is clustering index for the table). And B-tree has some storage overhead (pointers on each node and internal nodes - for your row count the tree might have say 3-5 levels). + each secondary index would add some space too but you do not have any. Not sure if it accounts for the entire 1.1GB difference but it will be a big part of it.

Another thing is that the file does not automatically shrink on deletes. Only on optimize/alter which rebuilds the entire table. But it would be reported in data free column of the table status.


Alter Postgres Tablespace I can't seem to create a table in a particular tablespace.  For instance, when I do:

CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space;

and then I do:

select * from pg_tables where schemaname ='myschema' limit 10;

...the tablespace is blank.  The tablespace shows up in PgAdmin as pg_default.  What do I need to change to create the non-default tablespace?

By definition, "tablespace" field of pg_tables says "Name of tablespace containing table (null if default for database)". To get an exact tablespace name for a table or a table list you should first get the default tablespace for particular database. The query below lists all tables in schema myschema

WITH tbl_spc AS (
  --Get default tablespace for the database
  SELECT ts.spcname AS spcname
    FROM pg_tablespace ts
    JOIN pg_database db ON db.dattablespace = ts.oid
    WHERE db.datname = current_database()
)
SELECT
  t.schemaname,
  t.tablename,
  --if null then get default tablespace
  COALESCE(t.tablespace, ts.spcname) AS tablespace
FROM pg_tables t, tbl_spc ts
WHERE
  t.schemaname = 'myschema'


Linked Server Creation Error This is a strange issue, i am unable to create linked server using provider Oracle Provider for OLE DB. But am able to connect to it using sqlplus, was able to do telnet and tnsping.

when i'm trying from ssms it is giving an error saying cannot initialize the data source object of OLEDB provider.

I have enabled the allowin processes for the Oledb provider.

What providers are available on your SQL Server?In SSMS --> Server Objects --> Linked Servers --> Providers

Do you see an Oracle provider? If not install the provider which I believe you can find here:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

If that doesn't work you can try this:
Create an ODBC connection on the server, and then create a linked Server with Microsoft OLE DB for ODBC drivers. Kind of a dirty way to do it, but it should work if you don't have the ability to install the oracle provider.


What does the error "Ambiguous column name 'id'" mean? I am getting an error with the following query that uses a right join.  My table has a column named 'id'.

SELECT TOP 6 id
FROM   dbo.tbldata
       RIGHT JOIN tblimg
               ON tbldata.id = tblimg.newsid
ORDER  BY createdDate DESC

The error is:

  Ambiguous column name 'id'

The error is referring to "id" in the first line of your query.  SQL Server doesn't know whether you are referring to tbldata.id or tblimg.id.

It is good practice to always use an alias so that table schema changes are less likely to break your query:

SELECT TOP 6 td.id
FROM   dbo.tbldata td
       RIGHT JOIN tblimg ti
               ON td.id = ti.newsid
ORDER  BY createdDate DESC


Can you see other users' session variables in MySQL? In MySQL, Using SHOW GLOBAL VARIABLES gives the ability to see global variables.
SHOW SESSION VARIABLES are the variable settings for that session.

Is there a way to see the SESSION variables of other active, connected users, from a superuser connection? For example, is there a query or view that would show me which active sessions have variables set differently from the global variables?

As of MySQL 5.7 the performance_schema can provide this information.

  Is there a way to see the SESSION variables of other active, connected users, from a superuser connection ?

You can get these from performance_schema.variables_by_thread.

You will need to know the Thread ID. The ID from SHOW PROCESSLIST is Process ID. Knowing the Process ID you can then look up the Thread ID from performance_schema.threads.

  is there a query or view that would show me which active sessions have variables set differently from the global variables?

You sure can.

SELECT VARIABLE_NAME, gv.VARIABLE_VALUE, tv.VARIABLE_VALUE
FROM performance_schema.global_variables gv
  JOIN performance_schema.variables_by_thread tv USING (VARIABLE_NAME)
WHERE gv.VARIABLE_VALUE <> tv.VARIABLE_VALUE
  AND THREAD_ID = ?;


Prevent consecutive duplicate values without a trigger Within a group, I'd like to prevent INSERTs of consecutive duplicate values, where "consecutive" is defined by a simple ORDER BY clause.
Imagine a set of experiments which is regularly sampling values from a sensor. We only want to insert a value if it is new for that experiment.
Note that older values are allowed to be duplicates. So this is allowed:
id    experiment    value
 1             A       10
 2             A       20
 3             A       10

but this is not:
id    experiment    value
 1             A       10
 2             A       10

I know how to find the previous value per experiment:
  SELECT
  *,
  lag(sample_value) OVER experiment_and_id
  FROM new_samples
  WINDOW experiment_and_id AS (
    PARTITION BY experiment
    ORDER BY id
  );

From the docs I know that CHECK constraints are not allowed to use other rows in their checking:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and reload to fail. The reload could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.
If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem because pg_dump does not reinstall triggers until after reloading data, so that the check will not be enforced during a dump/reload.)

The EXCLUDE constraint looks promising, but is primarily for cases where the test is not equality. And I'm not sure if I can include window functions in there.
So I'm left with a custom trigger but this seems like a bit of a hack for what seems like a fairly common use case.
Can anyone improve on using a trigger?
Ideally, I'd like to be able to just say:
INSERT ....
ON CONFLICT DO NOTHING

and have Postgres deal with the rest!

Minimum working example
BEGIN;
  CREATE TABLE new_samples (
    id INT GENERATED ALWAYS AS IDENTITY,
    experiment VARCHAR,
    sample_value INT
  );

  INSERT INTO new_samples(experiment, sample_value)
  VALUES
  ('A', 1),
  -- This is fine because they are for different groups
  ('B', 1),
  -- This is fine because the value has changed
  ('A', 2),
  -- This is fine because it's different to the previous value in
  -- experiment A.
  ('A', 1),
  -- Two is not allowed here because it's the same as the value
  -- before it, within this experiment.
  ('A', 1);

  SELECT
  *,
  lag(sample_value) OVER experiment_and_id
  FROM new_samples
  WINDOW experiment_and_id AS (
    PARTITION BY experiment
    ORDER BY id
  );
ROLLBACK;

The INSERT itself, without a trigger, can avoid inserting the same value as the last one for the same experiment in the order of IDs. For instance, if $1 is the experiment and $2 the value:
INSERT INTO new_samples(experiment,value)
SELECT $1, $2 WHERE NOT EXISTS
 (SELECT 1 FROM new_samples
   WHERE id=(SELECT max(id) FROM new_samples WHERE experiment=$1)
     AND value=$2);

If there were multiple sessions that inserted concurrently for the same experiment, there would be a potential race condition, because the SELECT within the INSERT won't see a just-inserted row from a concurrent transaction that hasn't committed yet. But there is the same race condition with a trigger or a check constraint that tries to look beyond the row being modified or inserted.
Race conditions can be handled by using the serializable isolation level (auto-aborting conflicting transactions) or pessimistic locking (lock out other writers before doing the operation, effectively forcing a serialization).


SQL Error (2027): Malformed packet I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error:

  SQL Error (2027): Malformed packet

This error only occurs if the result set is more than a certain number of rows (using LIMIT 14561 rows works, 14562 gives this error).

I believe this error is not due to Heidi since TeamCity is failing to start when running the same command.

I assume it is something to do with the data size becoming too large and something failing but I can't find any way to identify more specifically what the problem is and thus how to fix it.

If anybody can tell me what I can do to prevent this problem or at the very least how I might be able to better diagnose this problem I'd be most grateful.

My guess would be that you have TEXT/BLOB data and that the row data in the 14562 row is bigger than your current MySQL Packet, which is sized by max_allowed_packet.

MySQL Perspective

You need to reserve more space for max_allowed_packet

Jul 29, 2013 : Max_packet_allowed setting for Windows 7
Jul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
Jun 12, 2012 : MySQL Error Reading Communication Packets

Your InnoDB Redo Logs (sized by innodb_log_file_size) may need to be increased as well. I first learned of it (in relation to the MySQL Packet) from ServerFault. I refer to that ServerFault post in some of my posts:

Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?

I would max out the max_allowed_packet to 1G and resize your redo logs to 1.5G

[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 1536M

Next, run this to purge all transactions out of the redo logs:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Then, restart mysql (which is required).

Note to everyone running MySQL 5.5 and prior

MySQL 5.6 will handle resizing logs for you when restarting. Previous versions of MySQL require that you do that manually. Your steps after changing my.cnf and setting innodb_fast_shutdown to 0, you would do the following

service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

HeidiSQL Perspective

One very common complaint is Error 2027, particularly with using LOAD DATA INFILE

Some have suggested using older versions of libmysql.dll

HeidiSQL 8.1 released
sql error(2007) Malformed packet

Some suspect a character set issue : Error Code: 2027 Malformed packet

GIVE IT A TRY !!!


Search speed increase I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table.

First: is this true , or not?

Second: is there any way to increase it's speed again? (sth like Defragment maybe!)

If you doing on a regular basis UPDATE and/or DELETE statements on a table the SELECT speed will decrease over time because the data and indexes (if present) will be fragmented thus the search process will take more depending on the degree of fragmentation so i purpose :

to run OPTIMIZE on the afflicted table on a regular basis (once a day should be enough) because this will defragment the data and rebuild the indexes (if present)
add indexes (if you do not already have) on the columns most commonly used in SELECT statement


B Tree and B+Tree differences I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate:

Why can I store more search keys on an B+ Tree Index? My guess would be that the reason is because the nodes of an B+ Tree point out to sub-trees instead of data.
Is there any type of comparison of data that will not work with an B+ Tree index or can I use all of them (=, >=, !=, <, <> ...) ?

In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the tree is a fixed size, removing non-key data from internal nodes frees space for more keys to be held.

All operators can be made to work with the BTree/ B+Tree structure. Some are more efficient than others so, in practice, the query optimizer may choose not to use the index if one of the inefficient operators is involved.

The best case is equality (=). Here the matching key intervals can be followed from root to leaf and the corresponding row(s) returned. It may even be possible to determine from the root node alone that the query will return an empty set so tree walking can stop at that point.

Range queries (<=, >=) can be efficient. The tree can be walked to a leaf for the given predicate value. If leaf pages are a doubly-linked list the remainder of the range can be found by following these leaf-to-leaf links.

Wildcard searches can use the tree, depending on where the wildcard character is. If it is in the middle of the predicate or at the end then the search can proceed as for a range query, with additional checking of each value read. If the predicate starts with the wildcard the tree cannot be used because a B+Tree is an ordered set and a leading wildcard gives no indication of where in that ordering to start.

An inequality operator (!=) can be treated as two range conditions: (value < predicate) or (value > predicate). If the distribution of values in the data is very skewed it may still be efficient to use the tree. For typical cases it is more likely the optimizer will scan the leaf nodes from start to end.


status table join slows down query by 10 seconds Got an issue with a fair few queries in our system that all rely on a status table (contains 3 rows)

CREATE TABLE `assessment_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(30) DEFAULT NULL,
  `Completed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Started` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Sequence` smallint(6) DEFAULT '1',
  `Archived` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `Archived` (`Archived`),
  KEY `Completed` (`Completed`),
  KEY `idAndcompleted` (`id`,`Completed`),
  KEY `idCompletedArchived` (`id`,`Completed`,`Archived`),
  KEY `idCompletedTitle` (`id`,`Title`,`Completed`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Data

id      Title           Completed   Started     Sequence    Archived
'1'     'Not Started'   '0'         '0'         '1'         '0'
'2'     'Started'       '0'         '1'         '2'         '0'
'3'     'Completed'     '1'         '1'         '3'         '0'

When running the query which has lots of other table joins but to this is:

select
...
ass_s.Title AS `Assessment Section`
...
from au
INNER JOIN assessment_status as_s ON au.idStatus = as_s.id
....
where as_s.Completed = 1

explain extended is:

id  select_type table   type    possible_keys                                                               key         key_len     ref         rows    filtered    Extra
'1' 'PRIMARY'   'as_s'  'ref'   PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle'   'Completed' '1'         'const'     '1'     '100.00'    'Using temporary; Using filesort'

and the query takes about 12+seconds to return

Yet if I switch it round to a subquery for title and where exists it runs in 0.6seconds

select
(select as_s.title from assessment_status as_s where au.idStatus = as_s.id and as_s.Completed = 1) AS `Status`
...
from au
....
where exists (select as_s.Completed from assessment_status as_s where au.idStatus = as_s.id and as_s.Completed = 1)

explain extended is:

id  select_type         table   type    possible_keys                                                                   key         key_len     ref                 rows    filtered    Extra
5   DEPENDENT SUBQUERY  as_s    eq_ref  PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle        PRIMARY     4           fslcms.au.idStatus  1       100         Using where
3   DEPENDENT SUBQUERY  as_s    eq_ref  PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle        PRIMARY     4           fslcms.au.idStatus  1       100         Using where

It's obviously still selecting on the same values, yet runs a hell of a lot faster as it's using where instead on const. The thing is, reading MySQL docs on ref const states it's because it's only using one value it treats it as a const, which is the fastest way!?

I'm quite new with MySQL, come from MSSQL and never come across such a small table that could cause such a big performance issue before... As you can see from the table create, I've even tried varied versions of the index to see if it helps.

Regards

Liam

A PRIMARY KEY is a UNIQUE KEY and is an INDEX.  Don't re-declare id as UNIQUE.

Don't start an index with the PRIMARY KEY; it is likely never to be used.

Don't index flags; they are unlikely to be used.

So, I have just argued against all 6 secondary indexes, leaving only PRIMARY KEY(id)

There are only 3 rows in assessment_status, correct?  It is actually faster to do a table scan than to use an index.

Your first query seems to need to find the one row with completed = 1, then find the rows in au with au.idStatus=3, correct?

The EXPLAIN that you provide does not match the SELECT.  There are 2 tables in the select, but only one row in the explain??

Ditto for the second EXPLAIN.  But, there I see id=5 and id=3, implying that there are even more tables!  It could be that the order of tackling the tables was drastically changed; this could be a cause or clue of the timing differences.

How big is au?  The missing line in the EXPLAINs probably says that it will be a table scan, and gives an estimate of the number of rows.

Was the 12-second run tried twice?  I ask because the first run may have spent a lot of time fetching data from disk and the second would be running entirely from cached data in RAM.  This could also explain why the other query took only 0.6 sec.

If you are going to have the Question focus on a portion of the real query, then provide a shrunken test case that demonstrates it.  Else it may not be a valid shrinking, and we cannot necessarily help your real query.

Still, I will guess at what is going on.  The first query (I guess) had only one thing in the WHERE clause, a reference to as_s.  This tricked the Optimizer into deciding to start with that table.  But because of stuff not in evidence, some other table would be better to start with.  The second query seems to have taken that path; it could not start with as_s anyway.  A guess: there is no WHERE in the second query?


Automatic Page Repair without AlwaysOn or Mirroring Do Microsoft SQL Server 2012 Enterprise regular databases (without AlwaysOn) have Automatic Page Repair in any manner?

I know from below, by incorporating AlwaysOn, Secondary replicas receive Automatic Page repair. Just inquiring if this feature is available to SQL Server databases without Alwayson?

Per MSDN documentation https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-2017 , it states

"Each availability replica tries to automatically recover from corrupted pages on a local database by resolving certain types of errors that prevent reading a data page. If a secondary replica cannot read a page, the replica requests a fresh copy of the page from the primary replica. If the primary replica cannot read a page, the replica broadcasts a request for a fresh copy to all the secondary replicas and gets the page from the first to respond"

Yes, if the database participates in Database Mirroring.

But otherwise, no. If you want page repair, you'll need to do it manually with page level restores.