Count Data on Third Table Through Link Suppose I have the following three tables: SupportTicket, Device, and Link. I've created these in SQL Fiddle. I want to display a list of support tickets within a time range that meet specified criteria and show a count of the types of devices linked to it. The problem is that my current query is duplicating Support Tickets rather than counting all the devices linked to it on one row. Here is my query:

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  CASE
    WHEN d.Type = 'Cellphone'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Mobile Devices],
  CASE
    WHEN d.Type = 'Computer' OR d.Type = 'Disc'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Computers],
  CASE
    WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Audiovisual]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
    OR st.SupportTicket_ID = l.Entity_ID2
  JOIN Device d
    ON l.Entity_ID1 = d.Device_ID
    OR l.Entity_ID2 = d.Device_ID
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.SupportTicket_ID, st.Submitting_User, st.Submitting_Department, d.Type
ORDER BY
  st.SupportTicket_ID

Here is the SQL Fiddle instance where you can see what is currently returned by this query.

A support ticket should only occupy one row with a count of how many devices (by type, as shown) were linked to it in the columns on that row.

Below is the desired output:

Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.


MySQL Delete Rows from Slave I want to truncate the majority of data on a slave such that it only has the most recent records.  I'll eventually demote the current master, making it a read-only archive, and promote the truncated slave to master.  The primary motivation for truncation is reclamation of disk space; I want to dump and restore the slave.

Questions:

Regarding the dump/restore operation on the slave, I'm planning on stopping the IO and SQL threads, then dumping the table, then dropping the table, the restoring from the dump, then starting the slave threads.  Does this sound like the right process?
What happens, after deleting rows from the slave, if the master sends an UPDATE or DELETE referencing a deleted row?

If you are using InnoDB with the option innodb_file_per_table option off (by default on MySQL 5.1 and before), you need to stop the server after the dump (make sure you are not using innodb_fast_shutdown = 2), then delete at least the ibdata1, ib_logfile0, ib_logfile1 and your database directory and finally restart MySQL and import only the final truncated data. Otherwise, your ibdata1 file won't shrink in size.
If you have active the option innodb_file_per_table (by default on MySQL 5.5 and later), you have separate .ibd files per table, and your ibdata1 file is not too big, you do not need to import/export the data and delete the InnoDB files. Just delete the appropriate rows and defragment the .ibd files by doing:

ALTER TABLE <your table> ENGINE=InnoDB; -- for 5.5 and before

ALTER TABLE <your table> ENGINE=InnoDB, ALGORITHM=COPY; -- for 5.6 and later

Replication will probably break. On ROW based replication, it will certainly break as it won't find the right rows to affect. On STATEMENT mode some queries may continue working, affecting 0 rows, but as time passes, it is highly probable to find some incompatible query, like an UPDATE that can be done on the master but not on the slave due to a unique key constraint. I do not recommend running a replication with different data on master and slave unless you know for sure that those parts are not modified or they are done in a very restricted way. I can tell you that most of the replication problems I fix are due to replication filters.


Why is there an option to rebuild/reorganize an index when SQL Server does this automatically? SQL Server automatically handles index fragmentation issue when a certain number of rows are inserted/updated.
Then why is there an option to rebuild/reorganize an index?

Are you confusing fragmentation with statistics?
There is no auto handling of fragmentation. Do you have some reference to point to?
There is, however, auto update of statistics. It is a bit crude, which is why we might complement it with scheduled stats update jobs.


Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT? I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.

It seems to be a good option to use.

However I cannot seem to find any disadvantages about it. Is it an option which should be used most of the time?

SQL_BUFFER_RESULT creates a temporary table on the server for every result set.  This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when using a GROUP BY clause or with a subquery.  As such, all the same rules apply.

First, let's talk the problem that SQL_BUFFER_RESULT is designed to solve:

When the client requests data from the server, until the entire result set has been transmitted to the client the query is still "running" and some locks may still be held.  While the data is being transmitted, it will appear in the Sending data state.  It is library dependent on whether the client just gets all the data at once when the query is executed, or if it trickles in as you select rows, but an example of a problem is as follows:

resultset = conn["SELECT * FROM bigtable"]
resultset.each do |row|
  data[:value] = row[:value]
  sleep 10 # do something expensive here
end

Obviously contrived, but in the case above if there are 1000 rows, the query will still be actively running for 10,000 seconds.  That may seem far fetched, but a lot of applications have "think time" in-between fetching each row because they do some processing.  This is "a very bad thing to do."  Another case where this type of trickle effect could occur is with a large result set over a slow connection.  Ultimately, the problem is the trickling of data to the client causing the query to remain in an active state.  SQL_BUFFER_RESULT resolves this problem by buffering the result into a temporary table first, which makes the query end faster, thus releasing all of its locks (what locks?).  The result set is then fed to the client from the temporary table rather than the query itself.

That sounds great!

But...

Temporary tables take up memory and other resources on the server.  Lots of temporary tables == lots of resources.
Tables that exceed the minimum of tmp_table_size or max_heap_table_size will end up being converted to on-disk temporary tables, which means additional I/O.  It also means that your queries take longer, as once the size of the MEMORY table reaches tmp_table_size, it is then converted to an on-disk MyISAM table.
BLOB/TEXT fields cannot be stored as in-memory temporary tables (the MEMORY storage engine does not support them), thus will always be created on disk.
creating temporary tables is expensive, especially when they are on-disk.
Consider that one of the first things we try to optimize away when looking at an explain plan is "Using temporary."  SQL_BUFFER_RESULT literally makes every query include "Using temporary", (nearly) all the time.  My little bit of testing showed that there were cases where MySQL did not use a temporary table even with this hint, but they were limited (single row lookups on primary key seemed to be the only case).

Here are a few examples to show the effect:

-- unindexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select  * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where
1 row in set (0.00 sec)

-- unindexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- indexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

-- indexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- primary key lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where actor_id = 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

What is the problem being solved for?  "locks."  Since this is a hint for SELECT queries, unless you are using FOR UPDATE or LOCK IN SHARED MODE, there are very few locks taken on reads anyhow and they are very fast, so you are mostly solving for a problem that doesn't exist.  Exceptions always exist, but the overhead of creating a temporary table for nearly every query will far outweigh any benefit gained from making locks go away faster.

MySQL's recommendation is to use the SQL_BUFFERED_RESULT hint when retrieving a very large result set over a network connection to the client.  I cannot see any value in using it in another context.

All that being said, you can test and set this "globally" by setting sql_buffer_result=1 at the beginning of every session.  In an environment with any concurrency, I would predict poor results.


Creating a Materialized view with fast refresh and on Demand option create materialized view XXARG_GOKU
AS
select
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;

I need a query for creating fast refresh with on demand option. I have already created MLOG$_XXARG_DUMMY and MLOG$_XXARG_DRAGON mlog tables for it.

Please note: the above query is for creating a Materialised view and I'm using oracle 10g DB

Restrictions on Fast Refresh on Materialized Views with Joins Only

  Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

  They cannot have GROUP BY clauses or aggregates.

  Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

  Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Example:

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DUMMY with rowid;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DRAGON with rowid;

Materialized view log created.

SQL> create materialized view XXARG_GOKU
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
select
XDU.rowid as XDU_rowid, XDR.rowid as XDR_rowid,
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;


Is my database secure? / What does "=Tc/postgres" allow? Given the following...

iproductiondb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                 +
               |          |          |             |             | postgres=CTc/postgres        +
               |          |          |             |             | iproduction=CTc/postgres

Are the postgres and iproduction roles the only roles that have any type of access to iproductiondb ?

=Tc/postgres is making me wonder if I need to fix something?

=Tc/postgres means that postgres has granted CONNECT and TEMPORARY privileges to PUBLIC. You can revoke these privilege with :

revoke connect on database iproductiondb from public;
revoke temporary on database iproductiondb from public;

In general you should also check pg_hba.conf.


Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows (either in a single table or combined with other tables).

I'm a person with a very little knowledge of database administration and query construction. I searched on Google and read few questions at Stackoverflow about 'creating a new table for each user or using one table with a user_id column to separate each user's data'.

It seems that the question I'm going to ask is more like 'Need directions' then 'approximation of MySQL query time'.

If I go with only one database-table design then the table will have structure like this:-

+----------------------+---------+-------+-------+-------+-------+
| col_with_primary_key | user_id | col_1 | col_2 | col_3 | col_4 |
+----------------------+---------+-------+-------+-------+-------+
|                    1 | A       | aa    | bb    | cc    | ll    |
|                    2 | A       | dd    | cc    | ff    | dd    |
|                    3 | B       | zz    | dd    | yy    | xx    |
|                    4 | C       | ii    | yy    | kk    | tt    |
+----------------------+---------+-------+-------+-------+-------+

The user_id is differentiating which row is of which user.

Approximately, how much time will it take for MySQL to run the following query, given 10 Million records in the table.

SELECT *
FROM one_table
WHERE user_id = 'A'

10 million records is nothing to MySQL.
Database design is VERY importmant - INDEX the field(s) that will be searched.
Don't index every field.
When you get into the billions of records then look into Partitioning tables.
RAM, more RAM, and just to be sure add more RAM. MySQL loves RAM.
For best speeds, make sure the MySQL server is a dedicated box - NOT a VM.
I would lean more towards Linux for the OS.
Hire a database admin.


How to speed up simple query I'm using PostgreSQL 9.6 and have the following table:

CREATE TABLE eco.tracks (
    id                   bigserial  NOT NULL,
    time_track           timestamp  NOT NULL,
    track                bigint  ,
    distance_1             int  ,

    CONSTRAINT pk_tracks PRIMARY KEY ( id )
 );

And I manipulate data it contains with a very simple query:

  SELECT (track, distance_1, time_track)
    FROM eco.tracks
   WHERE time_track - (%s) <=  INTERVAL '10 seconds'
     AND (%s)-time_track <=  INTERVAL '10 seconds'
     AND distance_1 IS NOT NULL
ORDER BY distance_1 asc LIMIT 1;

Some inputs:

INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23467, '2018-05-18 20:43:06', 386, 17965);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23468, '2018-05-18 20:43:07', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23469, '2018-05-18 20:43:08', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22974, '2018-05-18 20:40:07', 376, 8394);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22968, '2018-05-18 20:40:00', 376, 9051);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22837, '2018-05-18 20:39:17', 366, 13133);

This query is used to take the closest track number & other two params  in range of setted time +- 10sec. But in my system, this one is a bottleneck, is it possible to improve it?

I've tried to play around with value work_mem, no success.

Actually, I use it for checking like if-else. And then make another query.
Can I merge it?

    SELECT (track, distance_1, aircraft_time)
      FROM eco.tracks WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1,
    CASE track WHEN NOT NULL THEN
    (
    INSERT INTO eco.noise (
    time_noise, track, distance, aircraft_time)
      VALUES (%s, track, distance_1, aircraft_time) ON CONFLICT DO NOTHING;
    )

    OR

 WITH check_1 AS
  (SELECT track, distance_1, aircraft_time FROM eco.tracks
 WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1)
 INSERT INTO eco.noise (time_noise, track, distance, aircraft_time)
  VALUES (%s, check_1.track, check_1.distance_1, check_1.aircraft_time)
 WHERE
 check_1.track IS NOT NULL ON CONFLICT DO NOTHING;
                )

Note: I use it in Python 3 by using psycopg2.

assuming you have an index on time_track

move the offsets to the other side of the inequality so that the arithmetic is only done once and so that an index on time_track can be used.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks
  WHERE time_track >= (%s) + INTERVAL '10 seconds'
    and time_track <= (%s) - INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,

etc...

or you could use BETWEEN instead of and.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks
  WHERE time_track BETWEEN
        (%s) - INTERVAL '10 seconds'
    AND (%s) + INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,


Constraint on row value based on values in other rows Is it possible to have a table with four columns:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3)
);

and then constrain it so that if I already have one entry like

('2016-04-01', 3, 120, 25.6)

an attempt to insert another entry with the same date and user_id but a different rate would fail?  i.e.

('2016-04-01', 3, 140, 15)

would fail, but

('2016-04-02', 3, 140, 15)

or

('2016-04-01', 4, 140, 15)

or

('2016-04-01', 3, 120, 15)

would be fine?

To be clear, the date and user_id are basically like an index, except duplicate indices are totally fine as long as the rates are the same.  The amount is not constrained.

I am working with a postrgesql 9.5 database, and I am using PHP.  My guess is that the easiest thing to do is to write some PHP code to perform the check.  The closest question I could find to this was this one:

trigger or check constraint insert values based upon existence of others

but it is about an Oracle database and is slightly different.

Thanks for any suggestions!!!

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here.  The part I was unfamiliar with was the ability to use the <> operator.  As available post Postgres 9.0:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3),
    EXCLUDE USING gist
        (date WITH =,
        user_id WITH =,
        rate WITH <>)
);

Two helpful links are:

http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm -- a short tutorial on constraints in Postgres, with an example for the Exclusion constraint that uses the <> operator

and

http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints (specifically slide 23) -- a presentation on the need for and use of Exclusion constraints.  Slide 23 introduces the <> operator and its usefulness.


How to Paginate a MySql Ranking Query I have the following ranking query working fine with a limit of 25 records per page, it shows 1st - 25th for the first 25 records, but when i go to the next page, the rank stays the same 1st - 25th when it should say 26th - 50th.

SELECT image, name, wins, losses, level, xp, wins AS W, losses AS L,
TRUNCATE(wins/IF(wins=0,1,wins+losses)*100,0) AS PCT,
@curRank := @curRank + 1 AS rank
FROM teams p
, (SELECT @curRank := 0) r
WHERE `console` = 3 AND `game` = 23 AND `laddertype` = 1
ORDER BY xp DESC LIMIT " . mysql_real_escape_string($limit) . ", 25"

Here

FROM teams p
, (SELECT @curRank := 0) r

you are resetting the value for @curRank to 0 every time you execute the query. Make this dynamic as well, adjust it according to your LIMIT clause.

On the second run change it to

FROM teams p
, (SELECT @curRank := 25) r


Current state of INTEGER vs NUMBER in Oracle I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and I am trying to decide on using INTEGER or NUMBER for a whole number field that will hold a LONG from Java.

For reference:

Long.BYTES = 8
Long.SIZE = 64
Long.MAX_VALUE = 9223372036854775807 / 2^63-1

I was lead to a relatively old blog post from an answer on Stack Overflow that suggests that you should never really use INTEGER because it is has some signification performance issues compared to using NUMBER. Even for just ID columns.

  INTEGER is always slower then NUMBER. Since integer is a number with
  added constraint. It takes additional CPU cycles to enforce the
  constraint. I never watched any difference, but there might be a
  difference when we load several millions of records on the INTEGER
  column. If we need to ensure that the input is whole numbers, then
  INTEGER is best option to go. Otherwise, we can stick with NUMBER data
  type.

This article also states that "INTEGER is equivalent to NUMBER(38,0)" which I already knew. What I have not been able to determine if it is equivalent with the possible performance concerns that are raised.

I do want to ensure that all the values are nothing but whole numbers.
I will be working with millions of rows on a regular basis as well as joining on this column because it will be a foreign key to hundreds of tables.

The Oracle documentation says that

INTEGER, INT and SMALLINT all map to NUMBER(p,0)

So I am planning on using NUMBER(38,0) like I always have, lots of legacy databases and tables I am importing data from actually store this ID information in a VARCHAR(50) and the data is tremendously horrid trash because of people adding prefix/suffix characters to indicate state; like z343234 means disabled. So I am trying to clean this data up in the new systems and not allow the perversions that the previous developers/business analyst made. I am not concerned with saving the corrupt flags and other data, just the numeric part.

I work with so many different database engines I can not keep up with what the latest idioms are.

Are these performance issues actually issues to be concerned about 10 years later?

The BLOG

The logic behind that post is so ludicrous that it deserves a sarcastic answer.

  Oracle does the "is it an integer?" check while it is waiting for the
  I/O call to the disk to return the data it needs to validate the
  Primary Key constraint.

  I believe this Performance Enhancements was implemented back in Oracle Version 2.

In actuality, the number of clock-cycles needed to perform such check is so insignificant that it is easily hidden within the noise of normal operations of the computer. You probably couldn't measure the performance difference even on a VAX/VMS.  (much older than "10 years")

INTEGER vs NUMBER vs PLS_INTEGER

As you have seen INTEGER is NUMBER(38,0).  There should be no measurable performance difference.

A difference can come in when you are talking about PLS_INTEGER vs NUMBER.  Most of the benchmarks I've seen that prove this to be true are computationally intensive.  Since you aren't doing Differential Equations with this value, the "performance gain" wouldn't apply to your case.

I recommend you stick with INTEGER or NUMBER(38,0)

Data Model Suggestion

The number and the flag should be in two different columns.  Feel free to use a virtual column (3rd column) to get back the original string.  I recommend that you record the original string value in a "comments/notes" like field so that the humans can search for it in case your virtual column logic does match what the human actually typed in.


Sql Server Fix Users I Create a user in my database with following command :

CREATE USER Test WITHOUT LOGIN

I created a login with the Test Name.

I want to link Test user with Test Login.

When I use following command :

EXEC sp_change_users_login 'Update_One', 'Test', 'Test'

SQL Server raised bellow error :

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'Test' is absent or invalid.

And when I use following command :

ALTER USER Test WITH LOGIN = Test

SQL Server Raised Bellow error :

Msg 33016, Level 16, State 1, Line 2
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

How can I link a Test user with Test login.

My purpose of this link :
I want to restore a database from a server to another server and after complete restoring, map users with existing login on the server.

thanks in advance.

A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact.

A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command.

To demonstrate I am going to create a database tst and then create a login tstlogin and an associated user tstuser within tst:

CREATE DATABASE tst;
GO
CREATE LOGIN tstlogin WITH PASSWORD = 'passw8rd';
GO
USE tst;
GO
CREATE USER tstuser FOR LOGIN tstlogin;

To show correct association we can use the following query:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

This results in

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin

Now we will drop the login tstlogin

DROP LOGIN tstlogin;

and recheck our user:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

which does not show an associated login anymore:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        NULL

Next step is to create a new login. (I chose a diffenert name for demonstration purposes, but it could be the same name too.)

CREATE LOGIN tstlogin2 WITH PASSWORD = 'passw8rd';

To associate that new login with our user we can use the ALTER USER statement:

ALTER USER tstuser WITH LOGIN = tstlogin2;

Checking the association once again:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

shows that the ALTER statement was successful:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin2

If you actually need to change a "WITHOUT LOGIN" user to be a "with login" user, you will have to drop that user and recreate it. Make sure in that case to script out the permissions first, as dropping a user causes SQL Server to forget those.


Connect to Oracle RAC from SQL Developer? We have set up RAC database on server. We can connect to database from sqlplus on the server using SCAN address. However, if I try to connect to RAC using SQL developer from client machine using SCAN address-based TNS entry, it shows the error

12-APR-2016 22:33:07 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=User))(SERVICE_NAME=racdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.227.110)(PORT=63694)) * establish * racdb * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack

Client machine is behind the firewall.
Here is the TNS entry.

RACDB =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.149)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.150)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.151)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

We cannot use DNS to resolve SCAN IPs from client side, so we used the IP address on the client machine. And IP addresses of SCAN are converted on client side using NAT. Below is their real addresses set on the server

# SCAN
#192.168.227.149 racdb-scan.esplink.mn racdb-scan
#192.168.227.150 racdb-scan.esplink.mn racdb-scan
#192.168.227.151 racdb-scan.esplink.mn racdb-scan

If we change the IP address of TNS entry to VIP addresses, the connection is successful and I can connect to RAC database.

Is it wrong to use SCAN addresses to connect to RAC?

Looks like the JDBC 11g NAT+RAC issue.

JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (Doc ID 1555793.1)

  JDBC thin 11g connections using  Single Client Access Name (SCAN)
  fail with errors:

  ORA-12520: TNS:listener could not find available handler for requested
  type of server

  ORA-12516: TNS : listener could not find instance with
  matching protocol stack

  when client and database are on different networks and Network Address
  Translation (NAT) router is used for translation.  JDBC OCI and
  SQL*Plus connections work fine. JDBC connections using the HOST vip
  names work fine over the NAT router, only connections using SCAN fail.
  JDBC thin connections using SCAN without NAT router work without any
  problems

The above note suggests upgrading the JDBC driver to 12c.

Whenever you connect through SCAN, your connection is redirected. Previous versions of the Oracle JDBC driver can not handle that properly.

If you download the latest SQL Developer, that comes with the 12.1.0.2 JDBC driver.

Another solution that I prefer is to use OCI with an Oracle Client instead of JDBC (this makes possible to cancel long-running queries in SQL Developer). After installing the Oracle Client, you can configure this in Tools / Preferences / Database / Advanced.


defragment SQL server database I have deleted some tables on a SQL server. Suddenly the size of the database jumped by 100Gb (whereas the deleted tables were taking 10% of that..).

I have read good and bad things about SHRINK hence to test I restored a copy of this database to another server and SHRINKed it: the massive unallocated space was released. However I am reading that SHRINK may cause the database to be fragmented:
- How can I avoid that,
- Or is there an easy way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?

How can I avoid that

The only way to avoid this is by not shrinking that is why shrinking is such a evil operation. But there are situations where conditions demand a shrink to be done to release excess space and for that case you have no option but to do it. You would then have to run defragmentation of the indexes. Paul Randal in This Blog showed alternate way (read the whole blog this is exactly what you may need).

Further I would also suggest you to read Mitigating Index Fragmentation

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

This may not always be possible in all environments but this is good alternate of shrinking.

  Or is there a way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?

If you restore a database which has index defragmnated you do not need to run index defragmentation again after the restore. Restore creates exact copy of the database


What are "Invisible Columns" and how are they used? I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature?

  Columns can be given an INVISIBLE attribute in a CREATE TABLE or ALTER
  TABLE statement. These columns will then not be listed in the results
  of a SELECT * statement, nor do they need to be assigned a value in an
  INSERT statement, unless INSERT explicitly mentions them by name.

  Since SELECT * does not return the invisible columns, new tables or
  views created in this manner will have no trace of the invisible
  columns. If specifically referenced in the SELECT statement, the
  columns will be brought into the view/new table, but the INVISIBLE
  attribute will not.

  Invisible columns can be declared as NOT NULL, but then require a
  DEFAULT value

Here's an actual use-case that came up just days after I learned about the invisible columns feature in MariaDB 10.3:

Following a new requirement, I had added a new column in the middle of one of our big tables. (This is accessed by multiple different applications.) While this is the kind of menial task that you wouldn't imagine could result in a problem even in your worst nightmares, this is exactly what happened.

Unbeknownst to me, we had some "innovative" legacy code that was executing SELECT * against that particular table (and several others, it turns out), and then populating a set of variables based on the column names and column order that existed at the time. The end result was that the wrong values were displayed in the user interface, causing much confusion with the users.

I could have fixed the issue in minutes by making the column "invisible", but since we were still running 10.2 that wasn't an option, so we decided to go the hard route by patching and re-deploying the software. Had we not had access to the source code or for other reasons not been able to modify the code, an upgrade to 10.3 could have been our only option.

So while this feature is not covered by the SQL Standard, it can nevertheless be useful in some rare cases.


Full database backup using xtrabackup stream I'm new to Percona XtraBackup. I've been trying to perform a full backup stream from my local ( test server around 600GB ) to remote server.

I just have some questions and I need guides, and I think this is the best place.

I have this command, which I executed in my local

innobackupex --user=user --password=password --stream=tar /which/directory/ | pigz | ssh user@10.11.12.13 "cat - > /mybackup/backup.tar.gz"

My questions are :

My log scan is not changing / increasing

>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)
>> log scanned up to (270477048535)

I've read a comment before and someone says log scan will not increase if no one is using the database. ( Yes, no one is using the database )
It's been running for a while.

I've tried to use xtrabackup to a local test server with around 1.7TB and finished in just a few hours. Is this because I'm using stream that's why it is slow?

What is the purpose of "/which/directory/" in my command? Is it going to store the file in /which/directory/ first and then transfer to my remote server ? Why do I have to specify a directory?
No created file on my local server /which/directory/ and to my remote
server /mybackup/.

Am I doing something wrong ?

Is there a much easier way to perform this?

My only goal is to backup my local database to a remote server, I'm doing this stream because I don't have enough disk space to store my backup locally.

I'm using MariaDB 5.5 and Percona XtraBackup 2.2

Just ran into the same problem. As it turned out in this case, the problem was caused by dumping to a NFS volume. Writing the dump to local disk solved this issue.


How should I Optimize storage for this table? I'm using MySql to store a basic table of this format:

    id      int(11)          //Auto-Incrementing ID
    data    varchar(5120)    //Random input data, compressed by a program, not mysql.
    -----------------------
    Row size ø  916 B

That's kind of troubling, because right now I have about 5,000 records each month, and I'd like to optimize that better than ending up averaging out at 1mb/1000 records.

I set it up this way originally because I wanted to be able to capture the larger sets, but they're very infrequent, as you can see here by this graph:

Link to graph API

count   n*128 bytes
+-------+----------
1       28
1       26
1       24
2       22
8       21
4       20
13      19
12      18
16      17
27      16
43      15
58      14
69      13
114     12
184     11
262     10
399     9
588     8
807     7
1224    6
1245    5
546     4
73      3
9       2
6       1
1       0

My main concern here is how much space I've wasted just to have accommodated the tail end of that chart, and what happens when I end up with even larger outliers? I'll have to bump up my row size again, when roughly 80% of the data fits very nicely into a varchar 1024 block, 1/5th the size of what I'm using now.

So how should I have built this thing in the first place?

Since it is built already, let's look at what you have. There is an interesting way to plan columns definitions for data currently present. If the table name is mydata, try running this query:

SELECT data FROM mydata PROCEDURE ANALYSE();

This will not display any of your data. This will examine the column data itself, calculate statistics based on the first 256 distinct values (by default, you can specify the distinct value count) and recommend the proper column type the table should have.

As long as the table is MyISAM, don't worry too much about the rows size because the default row format is DYNAMIC. If the table is InnoDB (and I sure hope it is not), please make sure the data is not part of the PRIMARY KEY. Your clustered index will grow like pouring undiluted MiracleGro in your backyard.

You may need to break up the data column into chunks of 128 and run either MD5 (32 character output) or SHA1 (40 character output) and concatenate those MD5 or SHA1 outputs and store them. That would save at 75% of storage. Have fun trying to code that breakup. This was just a rambling suggestion off the top of my head.

You may want to consider using Sphinx Indexing as well.


Store a small log file as a blob in sqlite I am utilizing a small sqlite database to keep track of some jobs that run on a linux box. Most of them have small (less than 50 lines) log files... I'd like to store those log files as a record in my database....

How can I get a text log file into my sqlite database? I want it stored as text in the record, not as a file.

For me your last line contradicts the subject (blob vs text), so probably I mistook your idea.
But what about making your log file an INSERT statement on the fly?  Then feed that into your desired sqlite database.  I mean something like this:

$ cat test.sql
INSERT INTO test
SELECT 'something
anything';

$ sqlite3 test < test.sql

$ sqlite3 test
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM test;
something
anything
sqlite>


How to get the difference between two timestamps in minutes? Demo

create table my_table (ts1 timestamp,ts2 timestamp);

insert into my_table (ts1,ts2) values
    (timestamp '2017-01-29 07:35:14.52',timestamp '2017-01-29 15:01:59.14');

I'm familiar with the following method:

select  (   extract (day    from (ts2-ts1))*24*60*60
        +   extract (hour   from (ts2-ts1))*60*60
        +   extract (minute from (ts2-ts1))*60
        +   extract (second from (ts2-ts1))
    )/60  as ts_diff_minutes

from    my_table
;

+------------------------------------------+
| TS_DIFF_MINUTES                          |
+------------------------------------------+
| 446.743666666666666666666666666666666667 |
+------------------------------------------+

I was wondering if there is a shorter version to achieve the same result?

After hours of searching over the internet, I was able to find a shorter version of the code that I was asking for. After several attempts of deriving the code I found here.

(CAST((SYSTIMESTAMP + interval '35' minute) as date) - CAST(SYSTIMESTAMP as date)) * 1440

Result:

+--------+
| Result |
+--------+
|     35 |
+--------+

This version is faster compared to the version that uses EXTRACT. The only downside of this version is that it doesn't include fractions of a second.


How to setup a SQL Cluster between 2 sites in active passive mode without shared storage we need to install SQL at 2 sites, 1 primary and 1 DR. We don't to pay for license for the DR and need to know how will we setup a SQL cluster/replication/alwayson/bag or any other method that will keep the Sql service down at DR and only when primary is unavailble then the SQL service starts.

currently we installed a BAG and the 2 sites have sql service running, though we use only one site at a time, Auditors flagged it as active/active while we thought it was active/passive.

thanks in advance for your suggestions.

It depends on what type of replication. If you can access only 1 Primary DB at a time then its active/passive. If you can access both then its active/active.

Overview : https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-2017

Details : Microsoft SQL Server 2017 Licensing guide


Can't create table with this script Trying to create a table with the script below. Every time I execute it I get the following error and can't work out why?!

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ,
  CONSTRAINT cat_id
    FOREIGN KEY ()
    REFERENCES waitron.`catego' at line 7 **

CREATE  TABLE IF NOT EXISTS `waitron`.`dishes` (
      `dish_id` INT NOT NULL AUTO_INCREMENT ,
      `dish_name` VARCHAR(90) NULL ,
      `dish_desc` VARCHAR(200) NULL ,
      `price` DECIMAL(19,4) NULL ,
      PRIMARY KEY (`dish_id`) ,
      INDEX `cat_id` () ,
      CONSTRAINT `cat_id`
        FOREIGN KEY ()
        REFERENCES `waitron`.`categories` ()
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;

The index you have named cat_id doesn't have any columns in it.  You must actually index something in your index.  The FOREIGN KEY () column list is also empty.  You must actual point at some foreign keys.


fixing mySQL replication (large DB) I have a large mySQL database and I am replicating to two slaves.  All three servers are windows (i know).  One of my slaves seems to have corruption, the other is fine.  This is huge DB, a few hundred gigs.  I am trying to figure out the best way to fix the replication on the one slave that is corrupted.  I need to minimize downtime to the master so a mySQLdump seems problematic unless its the only way.  I was wondering if because I have two slaves and I am pretty sure all 3 environments are close to identical there might be another creative way to solve this.  It is InnoDB but might I be able to shut all three down and do a directory copy of the good slave (datadir) and use it on the bad slave, restart?  I think that might only take a few hours as opposed to a mySQLdump.  Any thoughts on how to accomplish this correctly would be helpful.

Since you have a functioning Slave, you can rebuild the bad Slave in two different methods
For these examples

MAS : Master:
SL1 : Slave (Good one)
SL2 : Slave (Bad one)

METHOD #1
This method requires that you stop using the good Slave for the duration of the recovery

STEP 01 : On SL2, Run STOP SLAVE;
STEP 02 : On SL1, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : Record Read_Master_Log_File and Exec_Master_Log_Pos From STEP 03
STEP 05 : mysqldump data from SL1 to a SQL text file
STEP 06 : Load mysqldump into SL2
STEP 07 : ON SL2, CHANGE MASTER TO master_log_file='(Read_Master_Log_File From STEP 03)',master_log_pos=(Exec_Master_Log_Pos From STEP 03)
STEP 08 : On SL2, Run START SLAVE;
STEP 09 : On SL2, Run SHOW SLAVE STATUS\G and Look at Seconds_Behind_Master
STEP 10 : Repeat STEP 09 until Seconds_Behind_Master is 0

After this, you can use SL1 and SL2 for reads
METHOD #2
This method requires that you shutdown mysqld on SL1 and SL2

STEP 01 : On SL1, Run STOP SLAVE;
STEP 02 : On SL2, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : On SL1, Run net stop mysql
STEP 05 : On SL2, Run net stop mysql
STEP 06 : Copy entire datadir of SL1 over to SL2. Make sure you copy master.info file because it contains replication coordinates needed of SL2 to continue replication on startup. Make sure you copied the relay logs as well.
STEP 07 : On SL1, Run net start mysql
STEP 08 : On SL2, Run net start mysql

CAVEAT
If every table is InnoDB, MAS does not need to be shutdown at all. SL1 just needs a stable place to stop replication before copying to SL2.
Give it a Try !!!
UPDATE 2014-03-14 15:51 EDT
If you are planning to use the Master (since you are moving the Master to another Data Center), here is some advice.

STEP 01 : Stop application writes to MAS
STEP 02 : On MAS, run RESET MASTER; (Deletes all binary logs)
STEP 03 : On MAS, net stop mysql
STEP 04 : Copy the MAS server's datadir to the Slave's datadir
STEP 05 : On the new MAS, net start mysql (DO not have any application write to MAS yet)
STEP 06 : ON the New MAS, SHOW MASTER STATUS;
STEP 07 : On the Slave, net start mysql
STEP 08 : On the Slave, run CHANGE MASTER TO master_log_file='binlog from STEP 06',master_log_pos=(Position from STEP 06);
STEP 09 : On the Slave, run START SLAVE;
STEP 10 : Start application writes to MAS

No need to copy binlogs doing it like this. All a Slave cares about is the latest binlog.


Microsoft SQL - How to merge when certain values match Here is the database's structure:

^ Table:  Response_Master_Incident      ^
| ID                     | int          |
| Master_Incident_Number | varchar(20)  |
| Time_CallEnteredQueue  | datetime     |
| Time_CallClosed        | datetime     |
| Jurisdiction           | varchar(30)  |
| Response_Area          | varchar(30)  |
| Priority_Description   | varchar(30)  |
| Address                | varchar(400) |
| Apartment              | varchar(10)  |
| City                   | varchar(35)  |
| State                  | varchar(5)   |
| Postal_Code            | varchar(10)  |
| Call_Disposition       | varchar(30)  |
| Response_Date          | datetime     |
| Problem                | varchar(30)  |

^ Table:  Activity_Log                  ^
| ID                     | bigint       |
| Radio_Code             | varchar(20)  |
| Activity               | varchar(40)  |
| Date_Time              | datetime     |
| Master_Incident_ID     | int          |

^ Table: Case                           ^
| MasterIncidentID       | int          |
| CaseNumber             | varchar(20)  |

Here is my query currently:

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

SELECT Response_Master_Incident.Master_Incident_Number
  ,Response_Master_Incident.Problem
  ,CaseNumber.CaseNumber
  ,Response_Master_Incident.Time_CallEnteredQueue
  ,Response_Master_Incident.Time_CallClosed
  ,Response_Master_Incident.Jurisdiction
  ,Response_Master_Incident.Response_Area
  ,Response_Master_Incident.Priority_Description
  ,CONCAT (Response_Master_Incident.Address, ' ', Response_Master_Incident.Apartment, @NewLineChar, Response_Master_Incident.City, ', ', Response_Master_Incident.[State], '  ', Response_Master_Incident.Postal_Code) AS Full_Address
  ,Response_Master_Incident.Call_Disposition
  ,Activity_Log.Radio_Code
  ,Activity_Log.Activity
  ,Response_Master_Incident.Response_Date
  ,Activity_Log.Date_Time
  ,JurisdictionName
  ,CASE WHEN Activity_Log.Activity = 'Available' THEN 0
    WHEN Activity_Log.Activity = 'Terminated' THEN 0
    ELSE DATEDIFF(SECOND, (Activity_Log.Date_Time), (LEAD(Activity_Log.Date_Time, 1, 0) OVER (ORDER BY Response_Master_Incident.ID, Radio_Code, Activity_Log.ID))) END AS Seconds_In_Status
FROM
  Response_Master_Incident
  LEFT JOIN Activity_Log
    ON Response_Master_Incident.ID = Activity_Log.Master_Incident_ID
  LEFT JOIN Status
    ON Activity_Log.Activity = Status.Description
  LEFT JOIN CaseNumber
    ON Response_Master_Incident.ID = CaseNumber.MasterIncidentID

WHERE
  (Activity_Log.Radio_Name LIKE '[0-9][0-9][0-9][0-9]LM')
  AND Activity_Log.Radio_Code IS NOT NULL
  AND Response_Master_Incident.Response_Date BETWEEN '2016-09-01' and '2016-09-30'
  AND ((JurisdictionName = 'SomeJurisdiction') OR (JurisdictionName IS NULL))

ORDER BY Response_Master_Incident.ID,
Radio_Code,
Activity_Log.ID

So I'm querying another table using a key on the CaseNumber table, but I want the results returned in one row, concatenated.

For example, the following result:

^ Master_Incident_Number ^ CaseNumber ^ Radio_Code ^ Activity  ^ JurisdictionName ^
| 0000-000-000001        | 16-000001  | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000002  | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000001  | 1000PD     | Activity2 | SomeJurisdiction |
| 0000-000-000001        | 16-000002  | 1000PD     | Activity2 | SomeJurisdiction |

Would become:

^ Master_Incident_Number ^ CaseNumber            ^ Radio_Code ^ Activity  ^ JurisdictionName ^
| 0000-000-000001        | 16-000001, 16-000002 | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000001, 16-000002 | 1000PD     | Activity2 | SomeJurisdiction |

You need to use the SQL equivalent of GROUP_CONCAT to achieve this result.

Please see this stack overflow answer

You could also use the CROSS APPLY method from that query.

I wrote an example using your query above. In your case, you will want to add this subquery:

SELECT STUFF((SELECT ',' + c.CaseNumber
             FROM CaseNumber as c
             WHERE Response_Master_Incident.ID = c.MasterIncidentID
             FOR XML PATH (''))
             , 1, 1, '')


What happens when we create index What happens exactly when I create an index on the empid column of this employee table

CREATE TABLE employee
(
  empid number(5),
  ename varchar2(30),
  sal   number(10,2)
);

Let's assume the table contains the below data

empid   ename      sal
1       hema       10000
3       suma       10000
2       bheema     15000
4       bhama      20000
5       panama     25000

what exactly my doubt is what will be created and stored internally in index..... to improve the performance , whether sorted content of empid or some other information related to rows....?

Indexes, their various types and internal structures, are described in the documentation.


SELECT INTO that won't stop on SQL Server I ran a query a number of hours ago with a select into statement. I canceled the query, but it took awhile and my SQL Server client froze. The database that query was running on is now locked.

When I run sp_who2, I see these statistics:

Status: Runnable
BlkBy: "." <-- meaning blocked by nothing?
Command: Select into
CPU Time : 0
Disk IO : 0
LastBatch : 12/30/2016 (today)

Is it that the query is still taking time to close? The status runnable apparently means its waiting for something -- is it waiting for more space allocation? I do not have sufficient privileges to kill the process and no one is around to help.

Could someone assist with this?

Well, what is the last wait type ?

If you cancelled the query, it has to rollback, so the cancel is not immediate.
For instance you want to insert into billions of rows in one statement (not sure it is a good idea, but let's imagine). It takes several hours to do it (and your disks are probably burning) and if you cancel it, it may also take hours to rollback everything that has been done.

Also, I often experienced similar issue when using a linked server to Oracle. Because the network was a bit sensitive and Oracle config a bit strict, long query using this linked server (more than 1 hour) were stuck : process wasn't there anymore on Oracle side, but still on SQLServer side, and this one was impossible to kill and was locking destination table. Only solution : restarting SQLServer service.

But that does not explain why SSMS is freezing...


What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option.

Any advice on how to use "Backup set will Expire:" options while creating Maintenance plan will be appreciated.

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.


Are temp tables automatically created when referencing to them? I came across the following script and I am trying to understand it.

select columnA, columnB, columnC
  into #tmpTable
  from tableA

The #tmpTable is referenced after that query, but not before it. There is not a declaration for #tmpTable anywhere in the sql script.

My somewhat general question is:

Will the above create the temp table automatically and why? Is this only for T-SQL or applies generally?

It's not only about Temporary table it's about the INTO Statement in Sql Server. The INTO statement creates a table in the Database and this table is equal to same table we create with Create Table statement.

But this statement does not inherit any constraints.


How to prevent a column from repeating in the final result when using the LISTAGG function I have a table like this:
CUST_NUMBER | CUST_NAME | CUST_PROFESSION
------------------------------------------
   1        |   Mike    |    Teacher
   1        |   Mike    |    Engineer
   2        |   Jack    |    Driver
   3        |   Charlie |    Swimmer
   4        |   Negin   |    Engineer
   5        |   Zahara  |    Manager

In want to use the LISTAGG function to produce this result:
  CUST_NUMBER | NAME_PROFESSION
  -----------------------------
       1      | [Mike:Teacher,Engineer]
       2      | [Jack:Driver]
       3      | [Charlie:Swimmer]
       4      | [Negin:Engineer]
       5      | [Zahara:Manager]

What I've written so far is the query below:
select cust_number,
       '['||listagg(cust_name||':'||cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

The problem I have is that this gives me [Mike:Engineer,Mike:Teacher] (as you can see , the name Mike is repeated) instead of [Mike:Teacher,Engineer] . How can I solve this problem?
Thanks in advance

You want to see employee name out of aggregated professions list - so you must concatenate it to aggregating expression, not to separate profession in arguments.
If do this directly
select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

then cust_name become non-grouped, and Oracle produces an error "ORA-00979: not a GROUP BY expression".
This can be solved either by wrapping this column with aggregated function, for example, MAX():
select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

or by adding this column into grouping expression (preferred):
select cust_number,
       '['||cust_name||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number, cust_name;

fiddle


How to add a named constraint in Oracle SQL? I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution:

alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col)

This produces ERROR at line 1: ORA-00904: : invalid identifier on the NOT NULL part. Why?

Oracle 12c:

Table

create table clookup ( clookup_col varchar2( 64 ) );

Add a NOT NULL constraint

alter table clookup
modify ( clookup_col constraint lookup_9 not null ) ;

Optional

select
  table_name
, constraint_name
, constraint_type
from user_constraints
where table_name = 'CLOOKUP' ;

-- result
TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE
CLOOKUP     LOOKUP_9         C

Addendum

It may not always be necessary to use the ALTER TABLE ... MODIFY ... technique.  There is a "special rule" for defining NOT NULL constraints (see documentation):

  You can define constraints syntactically in two ways:

  As part of the definition of an individual column or attribute. This
  is called inline specification.

  As part of the table definition. This is called out-of-line
  specification.

  NOT NULL constraints must be declared inline. All other constraints
  can be declared either inline or out of line.

Also (here):

  Use the ALTER TABLE statement to alter the definition of a
  nonpartitioned table, a partitioned table, a table partition, or a
  table subpartition.

The following examples show what does NOT work:  just adding a NOT NULL constraint (not a column) to a table, via ALTER TABLE ... ADD ... (out-of-line).

-- Oracle 12c
create table T ( name varchar2( 32 ) ) ;

alter table T
add constraint name_nn not null ( name ) ;
-- ORA-00904: : invalid identifier

alter table T
add (
  n number
, constraint name_nn not null ( name )  -- out-of-line
) ;
-- ORA-00904: : invalid identifier

ALTER TABLE ... ADD ... can be used for other constraints eg UNIQUE or PRIMARY KEY "out-of-line" (as in the examples above), though.  It also works when we want to add NOT NULL constraints "inline" ie when specifying a column and a constraint eg

create table T ( name varchar2( 32 ) ) ;

alter table T
add n number not null ;
-- Table T altered

-- or
alter table T
add n number constraint number_nn not null ;
-- Table T altered


Redshift Deep Copy Disk Space I am trying to perform a deep copy on RS. See http://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html

I created the new/temporary table using the SAME DDL with compression settings as the original table.

I have 3 nodes, each node is using 21% disk space.

When I run the copy:

insert into events_tmp (select * from events);

I run out of disk space.
Does the deep copy not compress the data?

Thanks!

See vceron's post on https://discourse.snowplowanalytics.com/t/redshift-maintenance-best-practices/1124/2

When doing a Deep Copy in a highly unsorted table Redshift needs to sort this table before inserting into the new one.
This sort operation will be taken place in a intermediate temporary table, which at first be placed in memory, but as the data set is too big, will eventually spillover to disk.
However, those temporary tables are not compressed, and Redshift will allocate temporary disk space for that operation which results in disk full error if there is not sufficient space for the temporary data.


Can't list users - "select * from user" returns only one row with one column I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is:

  current_user
----------------
 rtsjlhdfptlaqd
(1 row)

The table name is definitely user. There is no users table. There are definitely a bunch of users in my app, but I can't seem to list them.

Thoughts?

Ok this is kind of a stupid question, but I'll leave it in case someone else makes a similarly dumb mistake.

For the record, I'm not the one who created a set of models including user instead of users.

Postgres has its own table user which it holds postgres users in. When you select * from user what you're getting is the result of the postgres user table. What you have to do instead is select * from "user" where user is in double quotes.


How to use a foreign key? I'm creating the following table:

CREATE TABLE fund_identifier
(
    id BIGSERIAL PRIMARY KEY NOT NULL,
    identifier TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS fund_identifier_pkey ON fund_identifier (id);
CREATE UNIQUE INDEX IF NOT EXISTS fund_identifier_identifier_uindex ON fund_identifier (identifier)

However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this:

    CREATE TABLE security_papers (
      id                                        BIGSERIAL,
      fund_identifier                           TEXT,
      as_of_date                                DATE,
          ...
      CONSTRAINT security_papers_fund_identifier_fk FOREIGN KEY (fund_identifier) REFERENCES fund_identifier (identifier)
    }

Or, should I use its id? Like this:

    CREATE TABLE security_papers (
      id                                        BIGSERIAL,
      fund_identifier_id                        BIGSERIAL,
      as_of_date                                DATE,
          ...
      CONSTRAINT security_papers_fund_identifier_fk FOREIGN KEY (fund_identifier) REFERENCES fund_identifier (identifier)
    }

My guess is on the second approach when it comes to the normalization rules. On the logical side, my application would need to look for  the id of fund_identifier first, before persisting anything at security_papers table. Right ?

But if I go with the second approach, this would be delegated to the database. Making my application logic, easier to implement.

Please let me know your thoughts, and if I'm missing some concept here. Thank you!

However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this

A couple of points before hand.

There is no need to mark a PRIMARY KEY NOT NULL, and DO NOT create a UNIQUE index on it. That'll just slow things down. It's already implemented with a ``UNIQUE` index.
Reconsider bigserial, do you really need it?
If you do not use id, but instead fund_id, or id_fund or the like, you can use the USING (fund_id) clause which is

easier to type
less error-prone

PostgreSQL provides for a shorter more terse syntax to refer to primary keys..

-- it'll guess primary key
fund_identifier  type  REFERENCES table;

That said, I think your questions is if your primary table uses bigserial how does your foreign table declare the column that refers to it.. Like this, using bigint

fund_identifier  bigint  REFERENCES table;

So your schema should look like

CREATE TABLE funds (
    id_fund    bigserial PRIMARY KEY,
    identifier text      UNIQUE NOT NULL
);

CREATE TABLE security_papers (
  id_security_paper    bigserial  NOT NULL
  id_fund              bigint     REFERENCES funds
  as_of_date           date,
);

Then

SELECT * FROM security_papers
JOIN funds
  USING (id_fund);


How does AWS DMS on-going replication works internally? From the documentation it's mentioned that "DMS collects changes to the database logs using the database engine's native API" and replicates to target. But, I didn't see anywhere that, at what rate it replicates from source to target in terms of number of records and is there any way we can control this settings or can I know each time how many rows it replicates?

The reason you didn't find any specific information in AWS's documentation on the replication rate is because it mostly depends on the source database. For example, in Microsoft SQL Server, there's a feature called Change Data Capture (CDC) which logs data changes in real-time. This means DMS will gather all logs from the CDC tables in the source Microsoft SQL Server database since the last time DMS ran.
Other database systems might log changes at slower rates, so there isn't once answer from AWS's perspective. Additionally, I believe you can schedule DMS Tasks to run at their own intervals as well.
But in general, the goal of AWS's DMS Tool is to replicate all changes since it last synced.


How to design tables - dabbler question I can understand that this is a basic question, but it's not about knowledge (I think) it's about practical experience.
I came from another field so, be kind, please.
I have my ORDER
Every ORDER has ITEMS
Every ITEM has his VARIANTS
Every VARIANT has his QUANTITY
When I'm required to store the content of an order, my first instinct is to create an array with items, variants, and quantities and then convert it to a string and finally store it as a field of the ORDERS table.
I can smell there is something wrong.
The ITEMS in the order can be a table, and so can VARIANTS and QUANTITY. And this seems to me as a tidier way to solve the problem.

However, when I have to rebuild an ORDER with the first approach it's very easy and I just have to convert the string to the original 3D array, while with the second I've to look at 3 different tables, and this is not something I feel comfortable with, mainly because it seems like a lot of work to do, and if there are only a few requests, it's easy, but if I have 1000 users at the time I fear that the DBMS can't cope with the workload.
I've tried some basic queries with MySQL and all my fears seem excessive, but I don't know how things work in a real work application.
I'd be glad to know how a pro would solve this problem. In production, would you use strange concatenations or separate tables (and the DB remains always stable and responsive)?

I have my ORDER
Every ORDER has ITEMS
Every ITEM has his VARIANTS
Every VARIANT has his QUANTITY

In general:
CREATE TABLE t_order (order_id INT AUTO_INCREMENT PRIMARY KEY,
                      {other columns});

CREATE TABLE t_items (item_id INT AUTO_INCREMENT PRIMARY KEY,
                      order_id INT REFERENCES t_order (order_id),
                      {other columns});

CREATE TABLE t_variants (variant_id INT AUTO_INCREMENT PRIMARY KEY,
                         item_id INT REFERENCES t_items (item_id),
                         quantity DECIMAL(10, 2),
                         {other columns});


Merge/Intersection between table and dataset - How to achieve? Consider the following table:

Id          Hash
----------- ----------------------------------
1           0x31F777F0804D301936411E3ECD760859
2           0xD64A593F3E9ACC972158D522A4289EA0

(Id is an identity column)

In to that table I want to merge the following dataset:

Hash
----------------------------------
0x31F777F0804D301936411E3ECD760859
0x31F777F0804D301936411E3ECD760859
0x0C5A65264F92A543E7AAA06375349C06

(Id is NOT present in the dataset)

The rules for the merge are:

If the hash does not exist in the table, insert it to the table;
If the hash does not exist in the dataset, delete it from the table;
If the hash does exist on both sides, and there are X instances in the table and Y instances in the source, then (Y-X) instances should be inserted in to the table.

The result of the merge should leave the table looking like this:

Id          Hash
----------- ----------------------------------
1           0x31F777F0804D301936411E3ECD760859
3           0x31F777F0804D301936411E3ECD760859
4           0x0C5A65264F92A543E7AAA06375349C06

What is the most efficient way to write a query to achieve this operation? FYI, other columns are omitted for brevity.

Using the sample data:

DECLARE @T table
(
    Id integer IDENTITY NOT NULL PRIMARY KEY,
    [Hash] binary(16) NOT NULL INDEX h
);

INSERT @T ([Hash]) VALUES (0x31F777F0804D301936411E3ECD760859);
INSERT @T ([Hash]) VALUES (0xD64A593F3E9ACC972158D522A4289EA0);

DECLARE @S table
(
    [Hash] binary(16) NOT NULL
);

INSERT @S
    ([Hash])
VALUES
    (0x31F777F0804D301936411E3ECD760859),
    (0x31F777F0804D301936411E3ECD760859),
    (0x0C5A65264F92A543E7AAA06375349C06);

You could write it as a MERGE:

WITH
    T AS
    (
        SELECT
            T.[Hash],
            rn = ROW_NUMBER() OVER (
                PARTITION BY T.[Hash]
                ORDER BY T.[Hash], T.Id)
        FROM @T AS T
    ),
    S AS
    (
        SELECT DISTINCT
            S.[Hash],
            rn = ROW_NUMBER() OVER (
                PARTITION BY S.[Hash]
                ORDER BY S.[Hash])
        FROM @S AS S
    )
MERGE T
USING S
    ON S.[Hash] = T.[Hash]
    AND S.rn = T.rn
WHEN NOT MATCHED BY TARGET THEN INSERT ([Hash]) VALUES (S.[Hash])
WHEN NOT MATCHED BY SOURCE THEN DELETE;

db<>fiddle

But for performance reasons (as well as some bugs), I would normally write it as two separate statements:

WITH ToDelete AS
(
    SELECT
        T.*
    FROM @T AS T
    WHERE
        NOT EXISTS
        (
            SELECT
                S.*
            FROM @S AS S
            WHERE
                S.[Hash] = T.[Hash]
        )
)
DELETE ToDelete;

WITH ToInsert AS
(
    SELECT
        S.[Hash],
        rn = ROW_NUMBER() OVER (
            PARTITION BY S.[Hash]
            ORDER BY S.[Hash])
    FROM @S AS S
    EXCEPT
    SELECT
        T.[Hash],
        rn = ROW_NUMBER() OVER (
            PARTITION BY T.[Hash]
            ORDER BY T.[Hash], T.Id)
    FROM @T AS T
)
INSERT @T
    ([Hash])
SELECT
    ToInsert.[Hash]
FROM ToInsert;

db<>fiddle

You should have a unique index on the target on ([Hash], [Id]). Chances are you have this already, or equivalently an index on [Hash] and a unique (possibly clustered) index on [Id].

There may be other issues depending on what else was omitted from the question for brevity. In any case it should provide a couple of possible starting points for your own solution.


How to import multiple CSV files to a postgres table using pgadmin or other method? I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs?

COPY schema.table FROM ‘C:\Documents\Data’ DELIMITER ‘,’ CSV HEADER;

Use your computer's command-line environment to list the names of all the files in your directory

On windows, use cmd + dir or PowerShell+gci
On macOS, probably terminal+ls
This might also be a good opportunity to familiarise yourself with psql and the meta-commands which you could use for this purpose

Use the list of files to create the commands you want

Some people use excel for this
I prefer multi-line editing in something like VSCode

Manually tweak your commands line-by-line or change the formula/process you used in step 2 until you get the output you want.

Gl;hf ;)


how to store multiple value in column in sql server I am creating job post application. In my first table the columns are This is my master table

ID
Position
jobDescription
minExp
maxExp
LastDate
InterviewDate
Project
HiringManager
interviewer
Primaryskills
SecondorySkills

and second table contains the primary skills PrimarySkill table

ID
PrimarySkills

third Table contains the secondory skills

ID
SecondarySkills

fourth Table contains the Interviewer

ID
Interviewer

the problem is that if a person have multiple skills and multiple interviewer for a job, Then how can insert the value from primary skill table, secondory skill table and interviewer table in Master table. i do not want to store value in comma separated

I don't think you want to store multiple values in a single column in the jobs table; this calls for what is known as a junction or many-to-many table.

CREATE TABLE dbo.Jobs
(
  JobID             INT PRIMARY KEY,
  JobTitle          VARCHAR(255),
  JobDescription    NVARCHAR(4000),
  MinimumExperience TINYINT,
  MaximumExperience TINYINT,
  LastDate          DATETIME, -- what is this? Needs a better name I think
  InterviewDate     DATETIME, -- is this really a property of the job?
  ProjectID         INT, -- foreign key references dbo.Projects?
  HiringManagerID   INT, -- foreign key references Employees table?
  InterviewerID     INT  -- foreign key references something?
);

(Assuming there is only one interviewer. If there are multiple, consider a many-to-many table.)

CREATE TABLE dbo.Skills
(
  SkillID SMALLINT PRIMARY KEY,
  SkillName VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE dbo.JobSkills
(
  JobID INT NOT NULL FOREIGN KEY REFERENCES dbo.Jobs(JobID),
  SkillID SMALLINT NOT NULL FOREIGN KEY REFERENCES dbo.Skills(SkillID),
  IsPrimary BIT NOT NULL DEFAULT 1
);


How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL We have a Mysql5.5 running on Windows server 2008 from past 4 years.

Recently we have moved ib* files to new locations because of space constraint in c drive.

When we started the Mysql it created all 3 ib* files in the default location. We could able to see the databases and tables through sql workbench.  But when selecting data it says <schema>.<table> doesn't exists.  So we copied back all 3 ib* files back to default location.  But the service is not getting started. Getting error code 1067.

And My Error Log is as below.

160603 12:23:33 [Note] Plugin 'FEDERATED' is disabled.
160603 12:23:34 InnoDB: The InnoDB memory heap is disabled
160603 12:23:34 InnoDB: Mutexes and rw_locks use Windows interlocked functions
160603 12:23:34 InnoDB: Compressed tables use zlib 1.2.3
160603 12:23:34 InnoDB: Initializing buffer pool, size = 753.0M
160603 12:23:34 InnoDB: Completed initialization of buffer pool
160603 12:23:34  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

Running service as default LocalSystem.

And checked administrator login has full rights on data folder.

Can anyone suggest how to get back my tables with data using the old ib* files which I moved to new location.

Thanks in advance.

The important part of the error log shows your instance is not able to open() the ibdata1 file (for reading and writing).

First to check in such situation are user rights. In Windows another possibility is the read-only file attribute which can block writes too.


In what cases are BLOB and TEXT stored in-line on InnoDB? I've googled this and can't find it, you can see Rick James mention it here.

... BLOB and TEXT are not always stored separately

The answer on What is the difference between MySQL VARCHAR and TEXT data types? says,

InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

That's not the only massively upvote answer with this stance. From StackOverflow you can find an answer with 754 upvotes that says it quite clearly,

TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage.
VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a realworld scenario with your data.

The comment here links to a post on a mailing list that says

MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' -- costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.
InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.

I've seen other posts that talk about storing text and blobs in-line if they're under some limit? What is the limit? When, if ever, does InnoDB store blobs and texts in line?

Keep in mind that there are 4 "row formats".  A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few months before DYNAMIC and COMPRESSED were introduced in the "InnoDB plugin".
So, I claim that that other Q&A is out of date!  That is the 754 Upvotes are no longer valid.
Here are some 'current' manual references:
(emphasis mine)
From https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html :

Columns such as BLOB and VARCHAR that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages.

From https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html :

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.
[DYNAMIC and COMPRESSED] When the row is too long, InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.
The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.
The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format

From https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html (even a big CHAR may be treated like TEXT):

InnoDB [all formats?] encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.
ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED handle CHAR storage in the same way as ROW_FORMAT=COMPACT.

(The links are for 5.6, but I think the text applies at least to newer versions.)
I see nothing specific that says that VARCHAR(...) and *TEXT are handled differently.


MySQL, import SQL dump with another database I have an SQL dump of db1 that contains this line:

"USE db1"

If I import that file into db2 with:

mysql -D db2 -uroot -p < /var/backups/db1.sql.gz

Is there the risk that db1 will be overriden?

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database.

MySQL Use db syntax

  The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.

The solution is the one proposed by Stuart, use a grep-like tool to find and replace "use db1" with "use db2" inside the dump file. Maybe even other references to db1 inside the dump script (procedures that use complete table names..). There are solutions for both Linux or Windows variants to edit that big text file.


SSRS encryption keys are broken I had an SSRS service account password expire. This is on an Azure Windows server.

I've tried the following:

Updating the password caused "The report server can’t access or use the encryption key"
Attempting to delete the key via all the normal methods fails.
Manually deleting the key (keys table) doesn't give me an option to re-create a new one.

The only solution I can think of at this point is un-installing and reinstalling SSRS.

Is there a better way, or is this approach even likely to work?

Ended up not trying the uninstall/reinstall of SSRS.

Here's what got me working:

Stop SSRS service
detach reporting service databases
restart SSRS -- it complains about missing DBs
stop SSRS
reattach
restart SSRS
Using SSRS configuration wizard, re-point SSRS to its databases
Now I was able to delete the encryption keys

This is obviously a hack solution and fails to identify what went wrong in the first place, but it worked


sql server 2005 deadlock error 3930 I am trying to simulate a solution for a deadlock issue happened on our production server.

Run this to create a table to be used in 2 and 3.

IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL
DROP TABLE dbo.MyProduct;
GO
CREATE TABLE dbo.MyProduct (ProductID INT ,Price MONEY);
INSERT INTO dbo.MyProduct VALUES (1, 15.0)

select * from MyProduct

create a proc. I set DEADLOCK_PRIORITY LOW to ensure the transaction will be rolled back in case of deadlock.

create PROCEDURE [dbo].[SP_xxx]
as
DECLARE @Price INT

SET DEADLOCK_PRIORITY LOW

BEGIN TRAN NormailizePrice

    SELECT @Price = Price
    FROM dbo.MyProduct AS mp WITH(REPEATABLEREAD)
    WHERE mp.ProductID = 1

    --Allow transaction 2 to execute*/
    WAITFOR DELAY '00:00:10'

    IF @Price > 10
    UPDATE dbo.MyProduct
    SET Price = Price - 10
    WHERE ProductID = 1
COMMIT

the other session. set deadlock priority to high to ensure the transaction will continue in case of deadlock.

--Transaction 2 from Connection 2
SET DEADLOCK_PRIORITY HIGH

BEGIN TRAN ApplyDiscount
UPDATE dbo.MyProduct
SET Price = Price * 0.6 --Discount 40%
WHERE Price > 10
COMMIT

Testing code to run the proc in 2. It is supposed to rerun the proc in case of deadlock.

DECLARE
  @retry TINYINT
  ,@retrymax TINYINT -- 3 times
  ,@retrycount TINYINT

SELECT
  @retry = 1
  ,@retrymax = 2
  ,@retrycount = 0

WHILE @retry = 1 AND @retrycount <= @retrymax BEGIN
    SET @retry = 0
    BEGIN TRY
        PRINT 'RetryCount: ' + CAST(@retrycount AS VARCHAR(5))
        exec SP_xxx
    END TRY
    BEGIN CATCH
        PRINT 'ERROR_NUMBER: ' + CAST(ERROR_NUMBER() AS VARCHAR(5))
        PRINT ERROR_MESSAGE()
        IF (ERROR_NUMBER() = 1205) BEGIN
            SET @retrycount = @retrycount + 1
            SET @retry = 1
        END
    END CATCH
END

Now first run 4, then run 3 in 10 seconds. The expected result is a deadlock will happen and 3 will finish and 4 will retry after 3 is finished. But I got this: Why?

RetryCount: 0

ERROR_NUMBER: 1205

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

RetryCount: 1

ERROR_NUMBER: 3930

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

You must always check the XACT_STATE() inside a catch block. If the value is -1 then you must issue a ROLLBACK.


Does the 'quality' of query optimization differ by database provider? I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.

But the example in the blog post seems trivial to optimize - i.e.

SELECT * FROM foo WHERE id = 500000;

vs

WITH cte AS (
  SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;

If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).

And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?

Are there known differences between databases to the extent/ability they have in optimizing queries?

A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.

For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).

The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.

SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.

But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.


How to split a wide table in Production? Due to some changing business requirements, I will be needing to split a large table in the near future to support new business requirements allowing a 1<=>M relationship from a table that was designed for a 1<=>1 relationship.

The challenge is that this table is fairly large and continuously used.  Somewhere around 150 columns, and 50 million rows.

The approach I'm considering is as follows: (the table is named "Customers")

Create two new tables, (Customers_1, and Customers_Many representing the new table structure.
Copy all the data across, as appropriate.
Create a view, selecting from Customers_1 and Customers_M that duplicates the Customers table exactly.
Create a trigger to handle inserts / updates on the view to update the parent tables.
Update foreign keys to point to the new tables.
Use sp_rename to Swap the table and the view.
Change code in subsequent iterations to point to the new tables, instead of the view.
(Eventually) Drop the view, drop the old table.

Can anyone recommend any challenges with this approach, validate this approach, or offer different approaches?

From my own experience of doing this your approach is almost identical to the approach that I took when I needed to do it. There are a few bits that I did extra prior to performing the split which gave me a better understanding of the potential impact this would have such as:

Query sys.sql_modules for any objects that reference the table in their definition
Create a scaled-down copy of the end-design in test and point clients to it (this enables you/app developers to know what is likely to need changing in the future)
Run a profiler trace to capture queries that are executed against the database which are not stored in the database itself.
Design indexes for the new table structure based on the queries identified from the steps above.

The only major difference between your approach and the one I took is I only created a single new table (I altered the original by dropping columns I did not need and then renaming it) so that it reduced the time to perform the release (I needed to copy less data doing it this way). I took a full backup prior to the release and I wasn't willing to have two copies of the same data in my database.