How to solve multidimensional data relationship I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.

Imagine a complex fashion product Pants with these Properties as dimensions:

Length (35,36,37,38)
Size (32,33,34,35)
Color (White, Red, Blue, Yellow, Green)
Kneepatch (Woodcutter, Brown, Silver)
Linked-belt (Brown, Beige, Black)

So there are 4*4*5*3*3 = 720 product variants. I know this is extreme, but I'm purely doing this to immediately test if my system will be able to handle it in a later phase.

So trimmed down I have:

Products

id
name

Properties

id
name

Thinking of RDBMS logic aside I would get a table like this, which of course isn't possible as it would constantly add properties and adds tons of nulls:

Productvariants

 id
 product_id
 property_1_id
 property_2_id
 property_3_id
...etc.

So this is a bit harder than a usual Color/Size combination you normally have on a product.

Now I can fake this methodology and just dump a serialization of Property ID's in one field on the Productvariants, like so:

Productvariants

id
product_id
properties {1, 2, 3}

.. and then split them in my ORM, but I'm afraid this will greatly increase the needed queries in the future (with tens of thousands of product variants) and optimization doesn't seem possible anymore that way.

Does anyone have experience with this and how to maybe intelligently do this? I'm using MySQL.

Products

ProdId
ProdName

Properties

(PropId) (primary key, if required)
ProdId (composite key)
PropCode (composite key)
PropValue (composite key) (if storing a specific value, otherwise you can remove this to simply store the possible properties for any given product)

Basically you store multiple values for a given property that all point back to a single product.  The composite key would need to be unique and you would need to ensure data integrity by either linking PropCode to another table or restricting the values on entry.


Can SQL Server Audit be used to track hostnames or IP adresses of the users performing DML on a table? I have been reading about SQL Server Audit, and so far I have assimilated that it tracks the command that has been executed, and some other additional information(SPID, object ID and stuff).
But can this be used to uniquely identify the individual that did it, or some type of unique ID of the computer from which the command was executed, such as host name or IP address?

I just do not want to waste my time reading it if it can not uniquely identify the machine or the person executing the command.

With SQLAudit you cannot track HostName or IP address.

However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year.

Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for writes) and you capture these events with an extended events session. You will need to hook a script (or an executable) to that session using the streaming API and you can implement your own logic to filter, categorize and persist the events you're interested in.

Another possibility is capturing the "Audit Schema Object Access Event" events with a trace and streaming them to your own custom application. Here's a tutorial. In Extended Events the same event is not available, as it is reserved for the SQLAudit implementation.


postgres makes db dump without password if i write on console at home (new machine, random IP):

/usr/bin/pg_dump --host xxxxxx --port xxxxxx --username "yyyy"
--no-password  --format plain --create --clean --section pre-data --section data --section post-data --inserts --column-inserts --verbose --file "/POSTGRES/backup_2016-09-08" "bbbbb"

serwer dumps database without asking for password! how do i block it?

my pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    aaaa              all             0/0                     md5
host    bbbb      all             0/0                     md5

ops, how stupid I am...

i have used 1 time pgadmin and logged to my  external postgress server
it made in my home directory .pgpass file

if i delete it - pg_dump doesn't work without password

my error!


MySQL IS NULL / IS NOT NULL Misbehaving? Please have look at this table:

mysql> desc s_p;

+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| id                      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| s_pid                   | int(10) unsigned | YES  | MUL | NULL    |                |
| sm_id                   | int(10) unsigned | YES  | MUL | NULL    |                |
| m_id                    | int(10) unsigned | YES  |     | NULL    |                |
| created                 | datetime         | YES  |     | NULL    |                |
| s_date                  | datetime         | YES  |     | NULL    |                |
| estimated_date          | datetime         | YES  | MUL | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Now Have a look at these queries:

mysql> select count(*) from s_p where estimated_date is null;
+----------+
| count(*) |
+----------+
|   190580 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from s_p where estimated_date is not null;
+----------+
| count(*) |
+----------+
|    35640 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from s_p;
+----------+
| count(*) |
+----------+
|  1524785 |
+----------+

The counts above are not matching. While as per my understanding:

Count with IS NULL and Count with IS NOT NULL should be equal to count when queried
without where clause.

Any idea on whats happening here?

===================================================

Update on 17th Feb 2012

Since, I found that a lot of people are asking about the kind of values estimated_date currently has. Here is the answer:

mysql> select distinct date(estimated_date) from s_p;

+----------------------+
| date(estimated_date) |
+----------------------+
| NULL                 |
| 2012-02-17           |
| 2012-02-20           |
| 2012-02-21           |
| 2012-02-22           |
| 2012-02-23           |
| 2012-02-24           |
| 2012-02-27           |
| 2012-02-28           |
+----------------------+
9 rows in set (0.42 sec)

As you can see above estimated_date either has NULL or a valid datetime values. There are no zeros or empty strings "".

Can this(original issue) happen if the index on estimated_date has some problem/s?

===================================================

Update on 18th Feb 2012

Here is the show create table output:

 | s_p | CREATE TABLE `s_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned DEFAULT NULL,
  `sm_id` int(10) unsigned DEFAULT NULL,
  `m_id` int(10) unsigned DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `estimated_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `sm_id` (`sm_id`),
   KEY `estimated_date_index` (`estimated_date`) USING BTREE,
  ) ENGINE=InnoDB AUTO_INCREMENT=1602491 DEFAULT CHARSET=utf8 |

Again, I can only suspect index on estimated_date here.

Also, the mysql server version is 5.5.12.

Do you have some zero dates? Datetime values of 0000-00-00 00:00:00 are considered by MySQL to simultaneously satisfy is null and is not null:

steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)

steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

See: http://bugs.mysql.com/bug.php?id=940

This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.

Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null and is not null counts should exceed the unrestricted count)...


"Error creating foreign key (check data types)…" but they do match… I'm getting the following error trying to create a constraint:

  Error creating foreign key (check data types)

Problem is… those column data types do match.

The target is an int(10) unsigned NOT NULL and is the PRIMARY column.
The source, in the same table, is an int(10) unsigned DEFAULT NULL, and the constraint I'm trying to create is a simple ON DELETE RESTRICT, ON UPDATE CASCADE.

Now, of course the target is NOT NULL and the source is NULL, but that's entirely intentional and what I need, and I can't see how could this be a problem. I could understand if it was the other way around…

Is this really the problem? Why? How do I solve it, then? I don't want to be forced to insert a value there even when I don't need one, neither I want to enable NULL on the PRIMARY column, which doesn't make much sense.

What else could be the problem?

It was a bug with phpmyadmin.

When you create a new constraint you usually are allowed to leave its name field blank, and it fills it for you.

This time, for some still unknown reason, this didn't work and gave this unrelated error.

Filling the name field did the trick, and allowed me to create the constraint.


Is my Publisher database my actual source database or is it a new database? I am setting up transactional replication for the first time and am working through my backup policies I need to set.

Is my Publisher database my actual source database?  Or is it a new database that gets created when I create the new publication?

I know our production database is currently backed up regularly, Im not sure if I also need to back up the 'Publication` as well once it is created?

Yes, your publication database is your source database.

There are some considerations when backing up the publication database, as well as other replicated databases, such as the distribution and subscription databases.  This is covered in Strategies for Backing Up and Restoring Snapshot and Transactional Replication.


Check if column UPDATE occurred When we update a MySQL record with php, we can check if it has effect using:

$mysqli->affected_rows;

But how do I check which column has been modified?

Example, in my table have the columns: id / name / age

In a record we have the data: 1 / Woton / 18

If I send an: UPDATE mytable SET id = '1', name = 'Woton', age = '20' WHERE id = '1'

Only the age field has changed, but SET was for everyone, how do I get the columns back that the data actually changed?

There is no such functionality in the mysql. If you want to trace the changes made to the data you have to collect historical data on your own. Say you create the trigger BEFORE UPDATE that insert all the data from the updated rows into the historical table. Later you can compare the present state of data with the historical states and trace all the changes been made.


Finding changed column values Table 1

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  3 | 2  | 6  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

Problem

After updating table 1, I have a new row with changed values:

 ID |COL2|COL3|COL4|COL5|
ROW1|  1 |  1 | 1  | 1  |
ROW2|  1 |  0 | 4  | 5  |
ROW3|  1 |  1 | 1  | 1  |
ROW4|  1 |  1 | 1  | 1  |
ROW5|  1 |  1 | 1  | 1  |
ROW6|  1 |  1 | 1  | 1  |

I want to select the row from table 1 that was changed into multiple rows based on the column name.

Desired output

ID  |OLD|NEW|
ROW2| 3 | 0 |
ROW2| 2 | 4 |
ROW2| 6 | 5 |

My attempt

I have created a trigger like:

select d.col2 as old, i.col2 as new
from inserted i
inner join deleted d
    on i.id = d. id
where not exists
(
    select i.row2
    intersect
    select d.row2
)

...but it just returns one row.

How I can I achieve the output I want? I am using SQL Server 2008.

Sample table and data

CREATE TABLE dbo.Table1
(
    RowID integer IDENTITY PRIMARY KEY,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

INSERT dbo.Table1
    (Col2, Col3, Col4, Col5)
VALUES
    (1, 1, 1, 1),
    (1, 3, 2, 6),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1);

Trigger

CREATE TRIGGER Table1_AU
ON dbo.Table1
AFTER UPDATE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SELECT
        I.RowID,
        Changed.Name,
        Changed.Old,
        Changed.New
    FROM Inserted AS I
    JOIN Deleted AS D
        ON D.RowID = I.RowID
    CROSS APPLY
    (
        -- Unpivot only changed columns
        SELECT 'Col2', D.Col2, I.Col2
        WHERE NOT EXISTS (SELECT D.Col2 INTERSECT SELECT I.Col2)
        UNION ALL
        SELECT 'Col3', D.Col3, I.Col3
        WHERE NOT EXISTS (SELECT D.Col3 INTERSECT SELECT I.Col3)
        UNION ALL
        SELECT 'Col4', D.Col4, I.Col4
        WHERE NOT EXISTS (SELECT D.Col4 INTERSECT SELECT I.Col4)
        UNION ALL
        SELECT 'Col5', D.Col5, I.Col5
        WHERE NOT EXISTS (SELECT D.Col5 INTERSECT SELECT I.Col5)
    ) AS Changed (Name, Old, New)
    WHERE NOT EXISTS
    (
        -- Only changed rows
        SELECT I.Col2, I.Col3, I.Col4, I.Col5
        INTERSECT
        SELECT D.Col2, D.Col3, D.Col4, D.Col5
    );
END;

Update statement

UPDATE dbo.Table1
SET Col3 = 0,
    Col4 = 4,
    Col5 = 5
WHERE
    RowID = 2;

Output

Note the returning results from triggers is deprecated, but presumably the question is a simplified version of the actual requirement. This code assumes the RowID column will never change (and cannot, in this example due to the identity property).


Best way to prevent deadlocks and server locking for single row Insert/Update transaction? I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing some research seems that the best approach would be to run Insert and Update statements in separate procedures. I'm deciding which stored procedure should be executed and then call procedure with all required parameters. There is also way to catch errors like deadlock or timeout in SQL try/catch block. I'm wondering if this would be necessary in my current code or it's over complicated. Here is example of my current code:

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
GO
    DECLARE @Transaction varchar(20) = 'TransUpdate';
    DECLARE @RetryCount INT
    DECLARE @Success    BIT
    SELECT @RetryCount = 1, @Success = 0
    WHILE @RetryCount < =  3 AND @Success = 0

    BEGIN TRY
        BEGIN TRANSACTION @Transaction
        -- This line is to show you on which execution
        -- we successfully commit.
        SELECT CAST (@RetryCount AS VARCHAR(5)) +  'st. Attempt'

            BEGIN
                UPDATE dbo.Building
                SET Status = @Status,
                    Name = @Name,
                    Code = @Code,
                    ActionDt = CURRENT_TIMESTAMP,
                    ActionID = @ActionID
                OUTPUT INSERTED.Code
                WHERE Code = @Code;
            END
            COMMIT TRANSACTION @Transaction

            SELECT 'Success!'
            SELECT @Success = 1 -- To exit the loop
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION @Transaction

        SELECT  ERROR_NUMBER() AS [Error Number],
                ERROR_MESSAGE() AS [ErrorMessage];

        -- Now we check the error number to
        -- only use retry logic on the errors we
        -- are able to handle.
        -- You can set different handlers for different
        -- errors
        IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
                                1205, -- SqlDeadlockVictim
                                1222 -- SqlLockRequestTimeout
                            )
        BEGIN
            SET @RetryCount = @RetryCount + 1
            -- This delay is to give the blocking
            -- transaction time to finish.
            -- So you need to tune according to your
            -- environment
            WAITFOR DELAY '00:00:02'
        END
        ELSE
        BEGIN
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop
            THROW;
        END
    END CATCH
END

This solution might be to complex for what I actually deal with but at the same time I would like to catch any deadlocks or other problems that I might face with UPDATE/INSERT transactions. This stored procedure is used for single row insert/update (I only showed update in this question). If anyone have suggestion or better approach for this please let me know.

I suggest one handle only errors that are expected and unavoidable. It would be better to address the root cause of errors rather than introduce the complexity of a retry work-around.

In this case (singleton update by primary key), the 1205 deadlock error is unlikely because only a single row is involved. The 1222 error will occur only with a non-default LOCK_TIMEOUT setting, which doesn't appear to be the case here. The 1204 error should not happen on a healthy server.

Command timeout errors cannot be caught in T-SQL. Command timeouts occur on the client side when the client API cancels the query, waiting up to the specified CommandTimeout (30 seconds by default with most APIs). No subsequent statements in the T-SQL batch will execute after cancelation, including the CATCH block.
A consideration is that an explict transaction started in the proc will remain open after a timeout (until the application closes or reuses the pooled connection). For this reason, I strongly recommend one specify SET XACT_ABORT ON in stored procedures with explict an BEGIN TRANSACTION so that transactions are rolled back immediately following a client timeout. There is no need for an explict transaction for this singleton row update proc, though, since autocommit will guaranteed atomic all-or-none behavior.

You might want to add an optimistic concurrency check so that users don't accidentally overwrite one another's changes. Whether or not this is worth the effort depends on the business impact of such a scenario and the likelihood of it happening. An optimistic concurrency check can be accomplished by either comparing each column value with the value originally retrieved or by adding a rowversion column to the table to simplify the task. Here's an optimistic concurrency example using a rowversion column. The app code would retrieve the rowversion when presenting data to the user and provide that value when updating the row.

ALTER TABLE dbo.Building
    ADD RowVersion rowversion NOT NULL;
GO

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL,
    @OriginalRowVersion ROWVERSION
AS
UPDATE dbo.Building
SET Status = @Status,
    Name = @Name,
    Code = @Code,
    ActionDt = SYSDATETIME(),
    ActionID = @ActionID
WHERE
    Code = @Code
    AND RowVersion = @OriginalRowVersion;
IF @@ROWCOUNT = 0
BEGIN
    RAISERROR('Building with code %s was modified or deleted by another user.', 16, 1, @Code);
END;
GO

Regarding the INSERT proc, it seems primary key violations are unlikely given the 2 character Code primary key and presumably small user base. You could just use a single statement stored procedure (autocommit) and handle PK violations errors on the applciation side with a user-freindly message.


Does assigning variable in SELECT do RBAR operations? So, I discovered something interesting.  I was playing around with the TOP command and assigning variables in the select statement.  I issued the below query and found that the variable (@a) value was the last value returned from the Top Command.  It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.

declare @a nvarchar(max)

select top 10 DisplayName
from dbo.Users

select top 10 @a = DisplayName
from dbo.Users

select @a as Results

Results:

This made me curious.  I modified the query to test again and executed the following:

declare @a nvarchar(max)
declare @b int = 0

select TOP 10 @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number

Results:

This is really interesting.  From these two queries, it almost seems like the TOP command is doing some sort of loop because of the cumulative sum that is happening on @b, as well as the assignments happening above.  Can someone explain what is going on?  Does SQL do a RBAR operation on when using the TOP command?

EDIT:

I also did this without the TOP commands, and see the results, so it's not just the TOP command.  It almost seems like assigning a variable in the SELECT statement does a RBAR command?

declare @a nvarchar(max)
declare @b int = 0

select @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number

Results:

It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.

Yep.  That's what happens.  This is a side-effect of how variable assignment in SELECT queries were initially implemented.  But it was used enough that it can't really be changed, and even is documented:

  If a SELECT statement returns more than one row and the variable
  references a non-scalar expression, the variable is set to the value
  returned for the expression in the last row of the result set.

Variables (Transact-SQL)

and

  For backwards compatibility reasons, SQL Server provides support for
  assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most
  scope.

Ordering guarantees in SQL Server


ORA-02270: Error report: no matching unique or primary key for this column list I have created two tables:

CREATE TABLE team (
    teamname        VARCHAR2(30) NOT NULL,
    carndate        DATE NOT NULL,
    teamnomembers   NUMBER(2) NOT NULL,
    charname        VARCHAR2(30),
    entryno         NUMBER(4) NOT NULL
);

COMMENT ON COLUMN team.teamname IS
    'Team name';

COMMENT ON COLUMN team.carndate IS
    'Date of carnival';

COMMENT ON COLUMN team.teamnomembers IS
    'Number of team members';

COMMENT ON COLUMN team.charname IS
    'Approved charity name';

COMMENT ON COLUMN team.entryno IS
    'Team leader for this team in this carnival';

ALTER TABLE team ADD CONSTRAINT team_pk PRIMARY KEY ( teamname,
                                                      carndate );

and another:

CREATE TABLE entry (
    entryno           NUMBER(4) NOT NULL,
    carndate          DATE NOT NULL,
    entrystarttime    DATE NOT NULL,
    entryfinishtime   DATE NOT NULL,
    entryplace        NUMBER(4) NOT NULL,
    charname          VARCHAR2(30) NOT NULL,
    compno            NUMBER(4) NOT NULL,
    eventypecode      CHAR(3) NOT NULL,
    teamname          VARCHAR2(30) NOT NULL
);

ALTER TABLE entry ADD CONSTRAINT entry_pk PRIMARY KEY ( entryno,
                                                        carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_charity_fk FOREIGN KEY ( charname )
        REFERENCES charity ( charname );

ALTER TABLE entry
    ADD CONSTRAINT entry_carnival_fk FOREIGN KEY ( carndate )
        REFERENCES carnival ( carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname )
        REFERENCES team ( teamname );

When i run the script in Oracle, I'm getting an error report saying no matching unique or primary key for this column list.

*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
       gives a column-list for which there is no matching unique or primary
       key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
       catalog view

and the error is reported at the Entry table, where i set the foreign key "teamname" at the last line. Why am i getting this error?

Foreign keys must reference another table's primary key, or a unique column in the other table (that is, a column with a unique constraint or index of some sort).

teamname is not unique in team, so a foreign key cannot link to it.

you need to either make teamname unique in team, or use a composite foreign key to match `team's composite primary key:

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname, carndate )
        REFERENCES team ( teamname, carndate );

Note: carndate may not be appropriate for this foreign key - just using it as an example. You would know best if there's a date in entry that would match team.carndate.


Output count percentage in 2 decimal places I have the following sql query code and I can not get to execute the desired output.

SELECT Deal_type, Price_type,
cast(sum(Deal_type)/count(Deal_type)*1.0 as decimal(12,2)) as results
FROM [dbo].[all]
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
GROUP BY Deal_type,Price_type order by Deal_type

I am trying to display the percentage values in decimal and currently its displaying the error message -

  Operand data type nvarchar is invalid for sum operator.

Any help with the above query would be very much appreciated.

Are you looking for something like this..

SELECT
    Deal_type,
    Price_type,
    count(isnull(Deal_type, 'x')) * 100 / (
        SELECT count(1)
        FROM users
        WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
    ) as results
FROM [dbo].[all]
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
GROUP BY Deal_type,Price_type
ORDER BY Deal_type


SQL Server Database Mirroring is going to die. How can I guarantee high availability now? The Database Mirroring feature of SQL Server is going to die:

  This feature will be removed in a future version of Microsoft SQL
  Server. Avoid using this feature in new development work, and plan to
  modify applications that currently use this feature. Use AlwaysOn
  Availability Groups instead.

Anyone knows when? What are my options for an High Availability (and quick recovery) database?

As noted, mirroring is not dead.  (At least not yet.)  The general population doubts that Microsoft would totally abandon HA for Standard Edition.

However, there are other methods.  In the SQL Server 2012 documentation when referring to mirroring it says: "If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping."

http://technet.microsoft.com/en-us/library/ms143729.aspx

There is plenty of instruction on using log shipping.

EDIT: You might read Kin's links at: SQL Server Log Shipping: File copy options?


Galera MariaDB not joining with IST Until now, I have a single MariaDB 10.1 node running as Galera Cluster. So, now I want to join the next one.

systemctl start mysql

initiates the snapshot state transfer (which takes about 10 hours for 200 GBytes).

The config of donor and joiner are quite similar:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
secure_auth = off
skip-name-resolve
bind-address        = 0.0.0.0
max_connections     = 100
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size      = 32M
max_heap_table_size = 32M
myisam_recover_options = BACKUP
key_buffer_size     = 128M
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
query_cache_limit       = 1G
query_cache_size        = 200M
log_warnings        = 2
slow_query_log=1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 3
log_slow_verbosity  = query_plan
server-id       = 103
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
expire_logs_days    = 3
max_binlog_size         = 100M
binlog_format       = ROW
default_storage_engine  = InnoDB
relay_log       = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
log_slave_updates
default_storage_engine  = InnoDB
innodb_buffer_pool_size = 110G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_file_format  = Barracuda
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="dbcluster1"
wsrep_cluster_address="gcomm://"
wsrep_node_name="db1"
wsrep_node_address="db1"
wsrep_replicate_myisam=on
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:pa55w0rt
[sst]
streamfmt=tar
transferfmt=nc
rlimit=3000k
bind-address=0.0.0.0
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
[mysql]
[isamchk]
key_buffer      = 16M

On the second node, db1 is replaced by db2, it has another server-idand

wsrep_cluster_address="gcomm://db1,db2"

After doing the SST, the server shuts down cleanly, and the position is stored in /var/lib/mysql/xtrabackup_binlog_pos_innodb. However, when I start the server again,

systemctl start mysql

it does not perform an incremental state transfer, but again flushes the entire data directory /var/lib/mysql and starts the SST again.

What do I have to do to join the cluster correctly (with incremental state transfer)?

My own hint with the Write Set Cache Size (aka Galera Cache Size) nailed it (I should have asked earlier, it seems some supernatural voice told me the answer.)

However I do not quite understand why 15 GBytes were too few for 45 KBytes/sec for an 18 hours transfer after a next try, obviously it was only a bit too short... (In my question I wrote about 10 hours, which I found out was not true).

Changing the transfer method to rsync finally fixed it as the transfer took only 1 hour.


How to rearrange columns in Oracle SQL Developer script output maybe this is an already answered question, but I am relatively new to Oracle DB and couldn't find my answer around the Internet.
So I have created a table in Oracle and when running the SELECT statement the output shows me columns like this:

Please if anyone can explain how to rearrange the columns so, the values from CITY column go under the City column, and the Customer_Name goes under that output column. Thanks in advance!

This is not an Oracle or SQL Developer problem.

The problem is the font you use. It is variable-width and there is just no way to properly align output with that. Use a fixed-width (monospaced) font and alignment just works out of the box.

https://en.wikipedia.org/wiki/List_of_monospaced_typefaces


Index maintenance operations that are ONLINE in Standard Edition: Is table Read-Write or Read-Only? As we understand it, related to SQL Server 2008 and above:

ALTER INDEX ... REORGANIZE

Is on ON LINE operation in ALL flavors of SQL Server.

ALTER INDEX ... REBUILD

Is a BLOCKING operation, unless you have SQL Server Enterprise Edition.... (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

If the above is all correct, the question is:

During an ALTER INDEX ... REORGANIZE operation, is the table available for read and write, or are writes blocked?

During an ALTER INDEX ... REORGANIZE operation, is the table available for read and write, or are writes blocked?

REORGANIZE is always a online operation and single threaded operation but does not holds blocking locks. When you reorganize there is option to allow_page_locks =ON or OFF if you keep this value off you might get error like index cannot be reorganized as it cannot take page level lock so its better to reorganize index with option allow_page_locks =ON. So yes table is available for read write operation locks are taken as pointed in the option but as I said it does not holds blocking locks

As per Microsoft Books Online

Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

  ALTER INDEX ... REBUILD
  Is a BLOCKING operation, unless you have SQL Server Enterprise Edition.... (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

Even index rebuild in enterprise edition do takes short term locks as per Paul Randal Article Online index operations need to take two very short-term table locks. An S (Shared) table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification – think of it as an Exclusive) table lock at the end of operation to force all read and write plans that could touch the index to recompile.


SQL Azure Migration Wizard - Duplicate Primary Key Just did a test at migrating a 120gb database onto RDS using SQL Azure Migration Wizard
Because we can't afford any downtime for "just" a test we ran the tool on the live database (meaning data was still getting inserted/updated/deleted while the tool was running)
Scripts were created ok but during the insertion of data we got the error "BCP copy in failed" "Cannot insert duplicate key"
My intuition is because the data was live when the .dat file got created, it somehow read the same row twice, but I don't really want to make an assumption I might regret when we will do the real migration, when the database will obviously be offline.
Could there be any other reason why this has happened and can I assume I'm safe using the tool on the "big day" with a read-only database?
-- Update --
We have tried again to run exact same .dat file of the table that failed and it worked. Which seems to indicate the duplicated key is not in the script... I'm even more confused, could it be a bug in the tool?

I ran into the same problem when trying to use SQL Migration Wizard to create a local copy of my database.  I know my source data didn't have duplicate key values, so I came up with the following script.  It simply causes duplicate key entries to be ignored (and a warning is generated instead of error).  Please read the comments in the code block below for instructions.

-- For some reason, SQL Azure Migration Wizard tries to insert duplicate records and fails.
-- I've wasted a lot of time making many many many attempts at copying the database, failing repeatedly,

-- So this is the workaround:
-- 1.0 In SQL Azure Migration Wizard: After selecting the database to script, select the "Advanced" button.
-- 1.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Table Schema Only".
-- 1.2 Click "Next" and proceed until the "Schema Only" migrate script has been executed a new database.

-- 2. Run THIS Sql script with @IGNORE_DUP_KEY set to 'ON' against the TARGET database.

-- 3.0 Back in SQL Azure Migration Wizard, click "Back" until you see the advanced button again, and click it.
-- 3.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Data Only".
-- 3.2 Click "Next" and proceed until the script has been executed.  NOTE: Table Name may still appear red like it failed, but looking at the message will show you that it was just a warning.
--     TO VERIFY: "SELECT COUNT(*) FROM TableName" on any tables that had the warning show in the source and target databases, and compare the counts.

-- 4. Run THIS Sql script, but set @IGNORE_DUP_KEY to 'OFF' to revert back to original/expected behavior.
DECLARE @IGNORE_DUP_KEY VARCHAR(MAX) = 'ON'; -- Options: ON, OFF

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE ' + @TableName + ' REBUILD WITH (IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY + ');'
        --print @sql
        EXEC (@sql)

        FETCH NEXT FROM TableCursor INTO @TableName
    END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

I know this solution is being posted some time after the original question was asked, but hopefully this will help someone else out!


Insert query skip NOT NULL constraint Mysql Version: 5.5.62 and 5.6.10
lib version: 2.18.1
CREATE TABLE IF NOT EXISTS temp_user (
  email         varchar(255),
  name          varchar(255)  NOT NULL,
  surname   varchar(255)  NOT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB;

When I insert a new user with a wrong query:
insert into temp_user (email) VALUES ('email@example.com');

If I execute in Mysql IDE (DBeaver) it throw an error: Errore SQL [1364] [HY000]: Field 'name' doesn't have a default value
But the node library insert the record with null in name and surname.
await pool.query(`insert into temp_user (email) VALUES ('email@example.com')`);

That error is caused when you have the STRICT_TRANS_TABLES sql mode enabled. It sounds like that mode is not enabled on your server by default and your IDE is enabling the setting for your session. You can either just enable STRICT_TRANS_TABLES sql mode on your server so all connections will be that way by default or run a query to change the sql mode with this module's connection prior to running your insert query (which is what your IDE is doing under the covers).


Strange sort behavior on a partitioned table with a prefix index Edit: Filed bug report at MySQL: link

I'm getting strange ordering behavior from MySQL when trying to order a partitioned table with a prefix index.

Problem can be reduced to the following dataset:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL,
  `data` varchar(2) DEFAULT NULL,
  KEY `data_idx` (`data`(1),`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p10 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (20) ENGINE = InnoDB) */;

INSERT INTO `test` VALUES
    (6, 'ab'),
    (4, 'ab'),
    (5, 'ab'),
    (16, 'ab'),
    (14, 'ab'),
    (15, 'ab'),
/*The following values cause the weird behavior*/
    (5, 'ac'),
    (15, 'aa')
;

Trying to order on id with a search on data gives the following:

mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+
| id |
+----+
|  4 |
|  5 |
| 14 |
| 15 |
| 16 |
|  6 |
+----+

mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+
| id |
+----+
| 16 |
|  6 |
|  5 |
|  4 |
| 15 |
| 14 |
+----+

Tried on both 5.6 and 5.7, with the same results.

Funnily enough, removing the ORDER BY gets me a neatly ordered result set:

mysql> SELECT id FROM test WHERE data = 'ab';
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
| 14 |
| 15 |
| 16 |
+----+

Running an EXPLAIN doesn't appear to yield me anything interesting either:

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

Any ideas?

Do not use index prefixing; it is often more harmful than useful.  Especially for short string.

In a composite index, the Optimizer won't go beyond the prefix index.
Prefixing is somewhat useful when you can't make a full sized index.

Do not use PARTITION unless there is a good reason for it; your schema an queries do not show any use for partitioning.

This lists the only use cases I know of.

Do have INDEX(data, id).

Even without partitioning, this would be the best index for that SELECT.

Do have an explicit PRIMARY KEY on every InnoDB table.

(If this was a watered down version of the real schema, start a new Question without the simplifications.  There may be something useful to discuss.)


Performance difference between SQL Server Linked Server and direct query A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds.

The same Management Studio but now from a locally connected query pane, with the same query executed over a locally defined Linked Server (SQLNCLI10) now needs 7 seconds.

Comparing the client stats, while the number of server roundtrips is 1 in both cases, I do see that "client processing" in the first is just 1794ms, while over the linked server it is 5741ms. "Wait time on server replies" over the linked server is also higher 967ms vs 187ms.

This is SQL Server 2008 R2 64 at both ends and on the same domain.

Can anyone explain this?

A few quick ideas:

The local server initiates a connection to the remote server, runs a query and fetches appropriate rows from the remote server. This all takes time. Worse, if you trace such activity with SQL Profiler, you may find that rows are fetched from the remote server to the local server one at a time, or in very small batches. This is very inefficient, and the more rows it fetches the worse and worse it gets.

If all data is local, none of that happens and the data can be retrieved much more quickly, especially if it is sitting in the data cache.

Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

For very large remote result sets (in terms of data size), you may run into issues where SQL needs to grow data or log files to accommodate the data. 7 seconds probably doesn't qualify, since it probably isn't bringing back much data.

Lastly, if the remote query is to a production system and the local query is to a dev or test system, load on the production server (which does not exist on the dev server) may cause blocking of the remote query. This would also slow down the observed performance of the remote query.


Moving a SQL Server DB from Windows to Linux fails We are trying to move a SQL server DB from Windows to Linux. But we are facing few problems while migration.

We took a Backup of the DB server, from SSMS in Windows
We restored it in the linux running the following commands:

RESTORE FILELISTONLY FROM  DISK =N'/var/opt/mssql/data/backup_file_name.bak'
RESTORE DATABASE DB_NAME
    FROM  DISK = N'/var/opt/mssql/backup_file_name.bak' WITH  FILE = 1,
    MOVE N'DB_NAME' TO N'/var/opt/mssql/data/DB_NAME.mdf',
    MOVE N'DB_NAME_Log' TO N'/var/opt/mssql/data/DB_NAME.ldf',
    NOUNLOAD,  REPLACE,  STATS = 1
GO

Running this I get an error more or less lie this:

  file XX_FlatFline cannot be restored to PATH_IN_WINDOWS {.....mdf}.
  Use WITH MOVE to identify a valid location for the file.

I get error for both MDF and LDF files. The windows path shown above in the error is the location of the mdf and ldf files in the Windows machine.

Can someone help me understand what's going wrong here? Why does the Windows path still matter when I am restoring the dump into a new DB?

Warning: I am a novice in SQL Server.

In the first release of SQL Server on Linux, I saw that SQL Server translates paths to related terms in windows, for example, root '/' will be C:.
so if this logic exists, you may try to make it something like:

  FROM  DISK = N'C:\var\opt\mssql\backup_file_name.bak' WITH  FILE = 1

but I think it's different now as mentioned here
maybe you should first check the version of your SQL Server on Linux and then check the steps on that article.


DateTime Conversion works for string value but for calculated value throws "out-of-range" error So I keep getting an error and I have no clue what is prompting it. I have the SQL

STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' ')

Which produces on the problem line the value :

'20161102 00:50:00'

When I call the following SQL, the DateTime conversion works just fine.

CONVERT(DATETIME, '20161102 00:50:00')

But when I try to put both together with the following:

CONVERT(DATETIME, STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' '))

Then and only then, I get the error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Does anyone here have any clue as to why the my nvarchar to datetime conversion works when I manually supply a string but fails when I try to convert from math that generates the exact same string? Any insight anyone has would be great because as of now this makes absolutely no sense.

Are you able to use date based functions to build your datetime.

Select
Dateadd(hh,11,
Dateadd(mi,50,
Dateadd(ss,30,
Convert(datetime,eatdate))))

Sorry if I've made any mistakes, I'm typing on my phone.


Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2? We recently migrated our production instances from SQL 2008 R2 to brand new SQL 2014 servers. Here is an interesting scenario that we uncovered with our usage of Service Broker. Consider a database with Broker Enabled = true with MyService and MyQueue. Poison message handling is disabled on this queue. There are least 2 active conversations with messages in the queue.

In one process (SPID 100) execute:

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;

Note that we leave the transaction open. Imagine that it's a .NET program that's waiting a long time on some external resource. Via sys.dm_tran_locks we see that this SPID has been granted an IX lock on the queue.

| type   | resource_id | mode | status | spid |
| OBJECT | 277576027   | IX   | GRANT  | 100  |

In a separate process (SPID 101) execute five times:

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;
ROLLBACK TRANSACTION;

The key here is that we are rolling back the transaction five times. This triggers the built in Poison Message Handling background logic. While the queue does not get disabled (because it's configured to not disable), a background task is still trying to do work and fire a broker_queue_disabled event. So now if we query sys.dm_tran_locks again we'll see a different SPID (associated with BRKR TASK) waiting on an Sch-M lock.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |

So far, everything makes sense.

Finally, on a different process (SPID 102), attempt to SEND to a Service using that Queue:

BEGIN TRANSACTION;
DECLARE @ch uniqueidentifier;
BEGIN DIALOG @ch FROM SERVICE [MyService] TO SERVICE 'MyService';
SEND ON CONVERSATION @ch ('HELLO WORLD');

The SEND command is blocked. If we look again at sys.dm_tran_locks we see that this process is waiting on a Sch-S lock. Executing sp_who2 we find that SPID 102 is blocked by SPID 36.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |
| OBJECT | 277576027   | Sch-S | WAIT   | 102  |

Why does a Sch-S lock wait on a Sch-M lock that is also waiting?

This behavior is completely different in SQL 2008 R2! Using this exact same scenario, running on our yet-to-be-decommissioned 2008R2 instances, the final batch including the SEND command does not get blocked by the waiting Sch-M lock.

Has locking behavior changed in SQL 2012 or 2014? Is there perhaps some database or server setting which might affect this locking behavior?

The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics:

  Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking.New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests.A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted.

In 2008 R2, a new Sch-S lock request was granted despite it being incompatible with the union of granted and waiting requests, which might lead to lock starvation. In 2012, the Sch-S lock request is blocked.

The reproduction script below uses regular tables rather than a Service Broker queue:

-- Session 1
CREATE TABLE dbo.LockTest (col1 integer NULL);

INSERT dbo.LockTest (col1) VALUES (1);

BEGIN TRANSACTION;

-- Will hold row-X, Pag-IX, and Tab-IX
INSERT dbo.LockTest (col1) VALUES (2);

-- Session 2
-- Blocked waiting on Sch-M
TRUNCATE TABLE dbo.LockTest;

-- Session 3
-- Takes Sch-S only
-- Not blocked in 2008 R2
SELECT * FROM dbo.LockTest AS LT WITH (READUNCOMMITTED);

In summary, 2008 R2 did not behave as designed. The problem was fixed in SQL Server 2012.


Looking for ways to optimize sql query with inner join My join query seems to be using the right index but is still slow. I am not sure where the issue lies and would appreciate any tips. The query and its explain is as follows:

SELECT
  events.*,
  event_subscriptions.read_at AS read_at,
  event_subscriptions.created_at
FROM events
INNER JOIN event_subscriptions
  ON event_subscriptions.event_id = events.id
WHERE event_subscriptions.user_id = 456
  AND event_subscriptions.group_id IN (123, 321, 222, 333)
  AND event_subscriptions.valid IS true
  AND events.type != 1
ORDER BY date_trunc('second', event_subscriptions.created_at) DESC
LIMIT 50;

explain:

Limit  (cost=24.84..24.84 rows=4 width=215) (actual time=13409.821..13409.842 rows=50 loops=1)    ->  Sort  (cost=24.84..24.84 rows=4 width=215) (actual time=13409.819..13409.828 rows=50 loops=1)
         Sort Key: (date_trunc('second'::text, event_subscriptions.created_at)) DESC
         Sort Method: top-N heapsort  Memory: 76kB
         ->  Nested Loop  (cost=0.23..24.83 rows=4 width=215) (actual time=0.105..13399.879 rows=7259 loops=1)
               ->  Index Scan using index_event_subscriptions_on_user_id_group_id on event_subscriptions  (cost=0.11..8.35 rows=4 width=20) (actual time=0.066..4728.728 rows=7295 loops=1)
                     Index Cond: ((user_id = 263010) AND (valid = true) AND (group_id = ANY ('{95513,220377,215898}'::integer[])))
               ->  Index Scan using events_pkey on events  (cost=0.11..4.12 rows=1 width=199) (actual time=1.183..1.185 rows=1 loops=7295)
                     Index Cond: (id = event_subscriptions.event_id)
                     Filter: (type <> 1)
                     Rows Removed by Filter: 0  Planning time: 23.064 ms  Execution time: 13409.973 ms

events is the table with actual data and event_subscriptions is a denormalized version which contains event_id along with some user info. Events have close to ~60M rows with event_subscriptions having 220M rows.

indexes:

index_event_subscriptions_on_user_id_group_id btree (user_id, valid, group_id, read_at DESC) WHERE valid IS TRUE

Your statistics are way off.

In the scan on index_event_subscriptions_on_user_id_group_id it estimates 4 rows returned, yet 7295 are returned.

WHERE (event_subscriptions.user_id = 456)
AND (event_subscriptions.group_id IN (123, 321, 222, 333))
AND (event_subscriptions.valid IS true)

The additional AND (events.type != 1) is not removing anything.

I would try running ANALYZE event_subscriptions (or, even better, if you can afford it VACUUM FULL ANALYZE event_subscriptions see if that fixes it. If it doesn't I would try ALTER TABLE event_subscriptions SET STATISTICS 10000 and try ANALYZE event_subscriptions again to see if that fixes it.

If neither of these worked, I would try pushing events.type check down..

INNER JOIN event_subscriptions
  ON event_subscriptions.event_id = events.id
  AND events.type <> 1

At the point that you're talking 220 million rows, you may also want to consider partitioning.


What is considered as a document in mongoDB? I have been learning MongoDB for the past two days and I've been confused with documents and their limits, and how to overcome the limits.

What is a difference between documents and collection?

In the context of MongoDB, a document means any piece of valid BSON* (binary JSON). The word does not have the everyday meaning of, say, an MS Word file or PDF.

A collection is a container for zero or more documents. There is no requirement for all documents in a collection to have the same structure. Indeed, that is one of the drivers behind the NoSQL movement. So it would be possible to store a shopping cart, a birth certificate and a recipie for chocolate cake in the same collection. (Though possible it would be an exceedingly poor design.) A collection is also the boundary for some management tasks, like copying, and authorisation.

* BSON is actually an important distinction as there are more data types than JSON. For example, the default primary key (_id field) is an ObjectId, which doesn't exist as a standard JSON type.

For more context see:

Documents; and
MongoDB Extended JSON.

BSON and MongoDB Extended JSON are JSON-like in terms of document structure, but definitely not interchangeable.


Update values from one table to another where key values are not unique I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-)

Consider the following:

Table A consists of lastname (null), firstname(null), merchant, amount, transdate, reference; with only unique field value being reference. The merchant, amounts and transdate can have similar duplicated values.

example:

Row 1:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","12345"
Row 2:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","77777"
Row 3:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","99999"
Row 4:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","44444"
Row 5:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","66666"

The other table has the lastname and firstname values I want to assign to each line, similar merchant,amount and transdate values throughout, but no reference field.

example:

Row 1:  SMITH,JOHN,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 2:  JONES,JAMES,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 3:  MILLER,STEVE,"BLANK-ROSTER NUM","16.07","2018-12-24"
Row 4:  WILSON,JACK,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 5:  DAVIS,BEN,"BLANK-ROSTER NUM","16.07","2018-12-24"

Is there a way to get names updated into the similar rows in TABLE A? It doesn't matter which goes to which so long as one name doesn't populate all similar rows in TABLE A (which is what happens to me). Any row with same merchant, amount, and date can update to any row in Table A that has same merchant, amount, and date. Avoiding the duplication of data is my issue.

Is it possible to get in TABLE A the names to distinctly assign to a row?

UPDATED TABLE A after assigning names from rows with same merchant, amount, and date distinctly:

Row 1:  "SMITH", "JOHN","BLANK-ROSTER NUM","34.30","2018-01-01","12345"
Row 2:  "JONES","JAMES","BLANK-ROSTER NUM","34.30","2018-01-01","77777"
Row 3:  "MILLER","STEVE","BLANK-ROSTER NUM","16.07","2018-12-24","99999"
Row 4:  "DAVIS","BEN","BLANK-ROSTER NUM","16.07","2018-12-24","44444"
Row 5:  "WILSON","JACK","BLANK-ROSTER NUM","34.30","2018-01-01","66666"

//////////Edited to include Table Def using SQL Server 2012

Table A:

 INSERT INTO [Recon2018].[dbo].[RECONReport]
       ([Employee - Last]
       ,[Employee - First]
       ,[MI]
       ,[Posted Amount]
       ,[Merchant]
       ,[Reference]
       ,[transdate])
 VALUES
       (<Employee - Last, nvarchar(50),>
       ,<Employee - First, nvarchar(255),>
       ,<MI, nvarchar(255),>
       ,<Posted Amount, decimal(18,2),>
       ,<Merchant, nvarchar(50),>
       ,<Reference, nvarchar(50),>
       ,<transdate, datetime2(0),>)

TABLE B:

 INSERT INTO [Recon2018].[dbo].[Summary_Final]
       ([Employee Last Name]
       ,[Employee First Name]
       ,[Employee Middle Initial]
       ,[Merchant]
       ,[CLIN Rate (Fee)]
       ,[Fee Transaction Date])
 VALUES
       (<Employee Last Name, nvarchar(max),>
       ,<Employee First Name, nvarchar(max),>
       ,<Employee Middle Initial, nvarchar(255),>
       ,<Merchant, nvarchar(max),>
       ,<CLIN Rate (Fee), float,>
       ,<Fee Transaction Date, datetime2(0),>)


Make custom aggregate function easier to use (accept more input types without creating variants) Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column.

While the code in itself works great it is somewhat bothersome to create custom data type for every possible input combination that I might need.

Here is the code I use

CREATE TYPE agg_tuple_text AS
(
    exists boolean,
    value numeric,
    text text
);

--------------------------------------------------------------------------------

CREATE FUNCTION valued_min(old_tuple agg_tuple_text, new_value numeric, new_text text)
    RETURNS agg_tuple_text
    LANGUAGE plpgsql
AS $$
    BEGIN
        IF (old_tuple).exists = false THEN
            RETURN (true, new_value, new_text);
        ELSIF (old_tuple).value > new_value THEN
            RETURN (true, new_value, new_text);
        ELSE
            RETURN old_tuple;
        END IF;
    END;
$$;
--------------------------------------------------------------------------------

CREATE FUNCTION unpack_agg_tuple_text(value agg_tuple_text)
    RETURNS text
    LANGUAGE plpgsql
AS $$
BEGIN
    IF (value).exists = false THEN
        RETURN NULL;
    ELSE
        RETURN (value).text;
    END IF;
END
$$;

--------------------------------------------------------------------------------

CREATE AGGREGATE valued_min(numeric, text)
(
        INITCOND = '(false, 0, null)',
        STYPE = agg_tuple_text,
        SFUNC = valued_min,
        FINALFUNC = unpack_agg_tuple_text
);

--------------------------------------------------------------------------------

-- Example
SELECT min(value) as min_value, valued_min(value, name) as min_name, max..., avg... FROM kv;
-- Output:
-- min_value | min_name           | ...
-- ----------+--------------------+----
--     11.11 | this is the lowest | ...

EDIT: My goal is drawing a min/max/avg chart for a TSDB and displaying the name of the min and max entries each.

Is there a way to achieve this without creating all of these for every possible combination? (Maybe some kind of generic parameter that are present in Java or alike)

Value column types

Various Date/Time types
Numeric types
Maybe text
(any comparable type)

data column types

anytype

It would be sufficient if I only could use it for the data value since it isn't used in any calculation inside that code. Unfortunately the anyelement type isn't allowed in custom data types.

I already considered using the json type as input, but that feels somewhat wrong, because it looses the type information (especially for date/time types).

I use Postgres 10 without extensions, but if this is possible using postgres 1x or using a special extension I'm willing to try.

I also considered joining the values, but then I get isues with performance and potential duplicates/rows that have the same value.

Before going into details - are you sure you are not re-inventing a wheel? This might burn down to the very popular topic of greatest-n-per-group.

Your query:

SELECT min(value) as value, valued_min(value, name) as name FROM kv;

Can be rewritten with stock Postgres as:

SELECT value, name
FROM   kv
ORDER  BY value
LIMIT  1;

Which also can use a simple btree index on (value) or (value, name) for an index or index-only scan - much faster.

I am pretty sure any other example can be solved with built-in functionality as well. To get one row per group, your query would be:

SELECT grp_col, min(value) AS value, valued_min(value, name) AS name
FROM   kv
GROUP  BY grp_col;

Replace with:

SELECT DISTINCT ON (grp_col)
       grp_col, value, name
FROM   kv
ORDER  BY grp_col, value;

Again, faster. And much more versatile. Detailed explanation:

Select first row in each GROUP BY group?


What's the most efficient way to load a csv file into SQL Server 2016? Alright, so this question seems simple up-front. Well, there's a bit more to it than just loading the file.

The entire story goes like this:
For some reason, our client sends us what can only be described as a relational database, flattened, condensed into a single csv file (but the delimiter is a tilde instead of a comma). It's kinda terrible, actually; the same data gets repeated ad infinitum throughout the entire file. So, in order to bring some order back into this data, I load it into an actual relational database. Because of the volume of data, loading it into a database makes it easier to inspect the data for issues. And it also makes it much easier to export.

There are 53 rows per record and somewhere in the ballpark of 250,000 records per transmission. I want to split that into 6 normalized tables. I'm not sure whether to validate the data in the C# program or the SQL Server 2016 LocalDb instance I'm using.

I'm not an experienced DBA; I'm a C# programmer who has dabbled in SQL a little. I feel comfortable enough with the syntax, but I want to make sure I'm doing this right.

Also, everything has to be completely automated. The file comes in, the C# program starts when it receives a file, and loads it into the database.

Let me explain the layout more. The file is 53 fields, and each line contains a detail line for a statement (what they are being charged for, how frequently they were charged for the item, total cost or credit for that item, etc). The problem is that EVERY line has the information for an entire mailing, the payer, resident, property, and remittance. With that being known, let me explain how I'm doing this now:

Open the file
For each line of the file, retrieve the keys for the tables that describe the mailing, payer, resident, property and remittance destinations.
Compare that data with the cached ones. If the cached data is invalid, query the DB to see if that entity has already been added. If not, create it. Cache that.
Add the new detail line and relate it to the mailing, which has a one to many relationship with details. (The mailing itself has a many to one relation to the payer, property, and remittance.)
Close the file when finished.

It's not the slowest thing in the world, but it's all being done completely in RAM. Since the program comes dangerously close to running out of RAM in its current state, we've decided to load the data into a database instead of keeping it all in local RAM. Hopefully that sheds some more light on this for potential answerers. Thank you!

My preferred approach to pass in multiple rows of multiple fields (i.e. not a simple delimited list) is to use Table-Valued Parameters (TVPs). The idea is that you will read the file line by line in your .NET code but stream the data either all at once, or if that is too much for one transaction, broken up into batches, into SQL Server using a TVP. The TVP is essentially a table variable that is an input parameter to a stored procedure, hence this will be a set-based operation (as far as SQL Server is concerned), not row-by-row. Technically a stored procedure is not required since a TVP can be sent as a parameter to an ad hoc query, but working with a stored procedure is just a better approach anyway.

There are two main patterns for using a TVP while reading from a file (and each of them relies upon passing in a method that returns IEnumerable<SqlDataRecord> rather than a DataTable):

Execute the "import" Stored Procedure, which initiates the file open and reading. All rows are read (and can be validated at this point) and streamed into the Stored Procedure. In this approach, the Stored Procedure is executed only once, and all rows are sent in as a single set. This is the easier approach, but for larger data sets (i.e. millions of rows) it might not perform the best if the operation is to merge the data directly into a live table rather than simply loading into a staging table. The memory required for this approach is the size of 1 record.
Create a variable for int _BatchSize, open the file, and either:

Create a collection to hold the batch of records

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Store valid entry in the collection

Execute the Stored Procedure at the end of each loop, streaming in the collection.
The memory required for this approach is the size of 1 record * _BatchSize.
The benefit is that the transaction in the DB does not depend on any disk I/O latency or business logic latency.

Loop to execute the Stored Procedure until no more lines to read from the file

Execute the Stored Procedure

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Stream record into SQL Server

The memory required for this approach is the size of 1 record.
The downside is that the transaction in the DB depends on disk I/O latency and/or business logic latency, hence could be open longer, hence more potential for blocking.

I have a full example of pattern #1 over on StackOverflow in the following answer: How can I insert 10 million records in the shortest time possible?

For pattern #2.1 (a highly scalable approach), I have a partial example below:

Required database objects (using a contrived structure):

First, you need a User-Defined Table Type (UDTT).

Please note the use of UNIQUE, DEFAULT, and CHECK Constraints to enforce data integrity before the records even hit SQL Server. The Unique Constraint is also how you create an index on a Table Variable :).

CREATE TYPE [ImportStructure] AS TABLE
(
    BatchRecordID INT IDENTITY(1, 1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    SKU VARCHAR(50) NOT NULL UNIQUE,
    LaunchDate DATETIME NULL,
    Quantity INT NOT NULL DEFAULT (0),
    CHECK ([Quantity] >= 0)
);
GO

Next, use the UDTT as an input param to an import stored procedure (hence "Tabled-Valued Parameter").

CREATE PROCEDURE dbo.ImportData (
   @CustomerID     INT,
   @ImportTable    dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;

UPDATE prod
SET    prod.[Name] = imp.[Name],
       prod.[LaunchDate] = imp.[LaunchDate],
       prod.[Quantity] = imp.[Quantity]
FROM   [Inventory].[Products] prod
INNER JOIN @ImportTable imp
        ON imp.[SKU] = prod.[SKU]
WHERE  prod.CustomerID = @CustomerID;

INSERT INTO [Inventory].[Products] ([CustomerID], [SKU], [Name], [LaunchDate], [Quantity])
    SELECT  @CustomerID, [SKU], [Name], [LaunchDate], [Quantity]
    FROM    @ImportTable imp
    WHERE   NOT EXISTS (SELECT prod.[SKU]
                        FROM   [Inventory].[Products] prod
                        WHERE  prod.[SKU] = imp.[SKU]
                       );
GO

App Code:

First we will define the class that will be used to store the batch of records:

using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private class ImportBatch
{
  string Name;
  string SKU;
  DateTime LaunchDate;
  int Quantity;
}

Next we define the method used to stream the data from the collection into SQL Server. Please note:

The SqlMetaData entry for BatchRecordID is defined even though it is an IDENTITY field. However, it is defined in such a way as to indicate that the value is server-generated.
The yield return returns the record but then control comes right back to the next line (which just goes back to the top of the loop).

private static IEnumerable<SqlDataRecord> SendImportBatch(List<ImportBatch> RecordsToSend)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("BatchRecordID", SqlDbType.Int, true, false,
                      SortOrder.Unspecified, -1),
      new SqlMetaData("Name", SqlDbType.NVarChar, 200),
      new SqlMetaData("SKU", SqlDbType.VarChar, 50),
      new SqlMetaData("LaunchDate", SqlDbType.DateTime),
      new SqlMetaData("Quantity", SqlDbType.Int)
   };

   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   // Stream the collection into SQL Server without first
   // copying it into a DataTable.
   foreach (ImportBatch _RecordToSend in RecordsToSend)
   {
      // we don't set field 0 as that is the IDENTITY field
      _DataRecord.SetString(1, _RecordToSend.Name);
      _DataRecord.SetString(2, _RecordToSend.SKU);
      _DataRecord.SetDateTime(3, _RecordToSend.LaunchDate);
      _DataRecord.SetInt32(4, _RecordToSend.Quantity);

      yield return _DataRecord;
   }
}

Finally, we define the overall import processing operation. It opens the Connection to SQL Server and the File, then it cycles through the file reading and validating the _BatchSize number of records for each cycle. The stored procedure parameters are only defined once as they do not change: the CustomerID value doesn't change, and the TVP parameter value is merely a reference to a method -- SendImportBatch -- that only gets invoked when the stored procedure is executed via ExecuteNonQuery. The input parameter to the method passed in as the TVP value is a reference type so it should always reflect the current value of that variable / object.

public static void ProcessImport(int CustomerID)
{
   int _BatchSize = GetBatchSize();
   string _ImportFilePath = GetImportFileForCustomer(CustomerID);

   List<ImportBatch> _CurrentBatch = new List<ImportBatch>();
   ImportBatch _CurrentRecord;

   SqlConnection _Connection = new SqlConnection("{connection string}");
   SqlCommand _Command = new SqlCommand("ImportData", _Connection);
   _Command.CommandType = CommandType.StoredProcedure;

   // Parameters do not require leading "@" when using CommandType.StoredProcedure
   SqlParameter _ParamCustomerID = new SqlParameter("CustomerID", SqlDbType.Int);
   _ParamCustomerID.Value = CustomerID;
   _Command.Parameters.Add(_ParamCustomerID);

   SqlParameter _ParamImportTbl = new SqlParameter("ImportTable", SqlDbType.Structured);
   // TypeName is not needed when using CommandType.StoredProcedure
   //_ParamImportTbl.TypeName = "dbo.ImportStructure";
   // Parameter value is method that returns streamed data (IEnumerable)
   _ParamImportTbl.Value = SendImportBatch(_CurrentBatch);
   _Command.Parameters.Add(_ParamImportTbl);

   StreamReader _FileReader = null;

   try
   {
      int _RecordCount;
      string[] _InputLine = new string[4];

      _Connection.Open();

      _FileReader = new StreamReader(_ImportFilePath);

       // process the file
       while (!_FileReader.EndOfStream)
       {
          _RecordCount = 1;

          // process a batch
          while (_RecordCount <= _BatchSize
                  && !_FileReader.EndOfStream)
          {
             _CurrentRecord = new ImportBatch();

             _InputLine = _FileReader.ReadLine().Split(new char[]{','});

             _CurrentRecord.Name = _InputLine[0];
             _CurrentRecord.SKU = _InputLine[1];
             _CurrentRecord.LaunchDate = DateTime.Parse(_InputLine[2]);
             _CurrentRecord.Quantity = Int32.Parse(_InputLine[3]);

             // Do validations, transformations, etc
             if (record is not valid)
             {
                _CurrentRecord = null;
                continue; // skip to next line in the file
             }

             _CurrentBatch.Add(_CurrentRecord);
             _RecordCount++; // only increment for valid records
          }

          _Command.ExecuteNonQuery(); // send batch to SQL Server

          _CurrentBatch.Clear();
       }
   }
   finally
   {
      _FileReader.Close();

      _Connection.Close();
   }

   return;
}

If the READONLY nature of the TVP prohibits some validation and/or transformation that is needed before being merged into the destination table, then the data in the TVP can be easily transferred to a local temporary table at the beginning of the Stored Procedure.


SQL Database size doesn't match with the total table sizes in the database I have a database that is size about 104GB.

When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces.

Why the DB size is too large?

Depending on the RDBMS being used, it is possible that the large file size as compared to summed-up table sizes is caused by any or more than one of the following:

Last database cleanup (in terms of table deletion, record deletion, etc) did not affect any disk reclamation by the OS/RDBMS
Last database cleanup does not normally affect disk space reclamation by OS/RDBMS
Indexes/constraints are being stored in other files. You won't see these when checking just table sizes.
No shrinking has been performed, though that may also possibly end up as first and/or second in this list
A possible miscalculation of table size retrieval (I just need to add this because it happens to some) and so the numbers don't tally
And maybe a few others.. Hopefully the stackers can add

I'm not saying these are definite items that impact your situation. I just mean that these may occur, and not just in your situation..

One other thing. It can also depend on the database settings for new disk allocation sizes. Say for example one allocation for a certain DB is 1GB. Once the usage exceeds that, another 1GB is added, regardless of whether or not you consumed all of it -- at the moment more allocation is required, it gets set.


Moving a SQL Cluster to Another Data Center w/No Downtime We have a two node SQL 2008 Enterprise cluster in a data center in California, and are looking to move hosts from a leased environment to a colo environment much closer to our home office.  Our cluster is quite busy with around 600GB of live data, so we're looking for a way to migrate with as little downtime as possible.

This is the only thing that I believe will be the challenge in the entire environment.

My initial thought was to add a passive node to the SQL Cluster and allow it to slowly replicate over to the other data center.  Once caught up, it wouldn't be hard, in theory, to fail over to this node say overnight and be active, DNS propagated, and so on, by that morning.

Does this sound viable, and if not, are there some other suggestions?

Thanks in advance for your help.

Note that both environments are very beefy resource wise, and one has 100MBps unmetered, while the destination is 1GBps unmetered.  Bandwidth, space, and resources are not a concern.

In SQL 2008 you're looking for minimum downtime vs no downtime in these cases unless you're willing to shell out a lot of money/time for a few seconds.  This sounds like hopefully it's not too complex of an environment so you have several options.

How about SQL Mirroring?  If your data isn't coming in too fast, it'll sync up and you can 'failover mirror' option.  Also, it's a very easy rollback, you just hit failover mirror again and it'll fail right back to the old cluster.  If you go with High Safety mode, all writes have to be hardened on the other side before committing, thus you will have some transactional delay while the mirror is up.  The upside is that it's super fast to just fail over as the data is certainly up to date.  High performance mode is available in Enterprise.  Mirroring is much more simple than replication and requires a lot less configuration items/requirements.  For example, in Replication, every replicated table must have a PK, this isn't required in Mirroring.  You also have the option of a witness server which will help with it being transparent to the application.

Worst case, you can do log shipping with automated scripts to restore the tail of the log.  You can take a log backup, restore, put the DB in read only mode, take another log backup, and restore again.  Be sure to check your VLFS (DBCC LOGINFO;) because if you have hundreds upon hundreds, your log restores will be slow complicating things.

Ensure security matches on both sides.  Are they different domains?  Then you'll have different users you'll need to add to your DB as soon as they go live.  SQL user?  Copy the user and SID together.  There's scripts to do this.

Prior to the failover, run a trace or profiler to see what apps are connecting from where.  Make sure to account for them on the new cluster.  This includes user security.  Also run a trace or profiler looking for logins to the old server.  Let the app owner know they are connecting to the wrong DB.

Whatever you do, make sure to test and stage the process multiple times and automate it to where it's a couple of key strokes.  Test failure scenarios and go into this confident.


Oracle isn't restoring correctly a backup First of all I'll say I'm using:

Oracle Database 11g Express Edition
Oracle SQL developer

What I'm trying to achieve
Create a backup and restore it, so for example I can delete a table after a backup and then restore the backup to recover that table.
What I tried step by step
Step 1
First I entered to RMAN and used the following statements
connect target;
shutdown immediate;
startup mount;
configure controlfile autobackup on;
backup database;

So for now I'm connected to the database and it's only mounted, and the backup has finished correctly. You have above what I got after executing the backup.
Step 2
Now I execute startup dba; because I want to initiate the database, so I can connect with Oracle SQL Developer and for example delete a table.

As you can see I don't have now tables because I deleted the only I had (before the backup I had the table "regions" of the HR schema so that's inside the backup I guess).
After all of this I close the connections in sql developer, because I don't know if it's going to create errors, but I want to avoid them.
Step 3
Now I want to start trying recovering the backup and I execute this because I read that both for backing up and restoring, you must leave the database in the "mount state", so I go to RMAN and execute only this now:
shutdown immediate;
startup mount;

Step 4
And now in this step the real recovery, I execute this:
restore database;

recover database;

Now I should execute the following but it's returning this error:
alter database open;

So, I execute this instead:
alter database open resetlogs;

Step 5
Now that all is done and opened, I'll check if the table is recovered in sql developer.

And no, it's not there.
I hope all the info was clear

Based on the above output, you never had the table.

But let's assume you had the table, then created the backup, then dropped the table.

You restored the database with restore database.

Then you recovered the database to the most recent point with recover database.

So you restored the database, and it had the table. Then you recovered the database using the redo log, which contained the change that dropped the table, so the table was dropped again. It is no wonder you can not see the table after opening the database.

If you took the backup at 2019-05-27 22:00:00, then dropped the table at 2019-05-27 22:10:00, and you want to restore the database to a point of time when it still had the table, you need to perform an incomplete recovery.

So instead of recover database;, specify a point of time where you still had the table, for example: recover database until time "to_date('2019-05-27 22:05:00', 'YYYY-MM-DD HH24:MI:SS')";.

Oracle did exactly what you asked from it.


Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete I have the following table

[dbo].[myObj]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fk_ParentID] [int] NULL
)

and I have a relationship from fk_ParentID to ID.

I want to change the Delete Rule in SQL Server Management Studio under the Window "Foreign-Key Relationships"->Insert and Update Specifications->Delete Rule to ON Delete Cascade, but it is not possible.

Can you tell me why, and how I can make this possible?

To further clarify this question, I've created a sample schema as such:

USE TempDB;

CREATE TABLE [dbo].[myObj]
(
    [ID] [int] NOT NULL CONSTRAINT PK_myObj PRIMARY KEY IDENTITY(1,1),
    [fk_ParentID] [int] NULL CONSTRAINT FK_myObj_ID FOREIGN KEY
                    REFERENCES myObj (ID) ON DELETE CASCADE
);

When you attempt to execute this SQL, the following error is returned by SQL Server:

  Msg 1785, Level 16, State 0, Line 3
  Introducing FOREIGN KEY constraint 'FK_myObj_ID' on table 'myObj' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

  Msg 1750, Level 16, State 0, Line 3
  Could not create constraint. See previous errors.

The error message indicates that SQL Server is declining to create the foreign key cascade relationship since deleting a single row could result in all the rows being deleted (or perhaps just multiple rows, not all, depending on data in the table!).

To illustrate this, consider the following data:

INSERT INTO myObj (fk_ParentID) VALUES (NULL);
INSERT INTO myObj (fk_ParentID) VALUES (1);
INSERT INTO myObj (fk_ParentID) VALUES (2);
INSERT INTO myObj (fk_ParentID) VALUES (3);
INSERT INTO myObj (fk_ParentID) VALUES (4);
INSERT INTO myObj (fk_ParentID) VALUES (5);
INSERT INTO myObj (fk_ParentID) VALUES (6);
INSERT INTO myObj (fk_ParentID) VALUES (7);

Which looks like:

If I could define the ON DELETE CASCADE action on this table, executing the following code would delete all rows in the table:

DELETE FROM myObj WHERE ID = 1;

Deleting the row with ID=1 would cascade through the row with fk_ParentID=1 which would cascade delete through fk_ParentID 2, etc, etc.  Most likely this is not the effect you want.


For Postgres, efficiently get points within X distance of point Y? I have this table as below in Postgres:

create table locations
(
    id serial not null
        constraint games_pkey
            primary key,
    name varchar,
    location point,
)
;

I want to query based on another location point( x, y ) as latitude and longitude; I only need to return records within, say, 1 kilometre distance of that location. How can I efficiently select records meeting that criteria?

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

I found this question on stackoverflow which appears to do something similar, but the accepted answer has nothing but code and no indication of how it works:
https://stackoverflow.com/questions/37827468/find-the-nearest-location-by-latitude-and-longitude-in-postgresql

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you need GIS. If you're using PostgreSQL that means you need PostGIS. For a PostGIS query that returns all points in a table within a 1 km (1000 m) distance of a specific point, you can use ST_DWithin

SELECT *
FROM tbl
WHERE ST_DWithin( tbl.geog, ST_MakePoint(x,y)::geography, 1000 );

That will also use a GIST index (if any) on tbl.geog.


Optimize mysql query, on table with 32 million records I have a database that I have created from DISCOG, currently it takes between 32 and 40 seconds to query the database for one record, my query looks similar to;

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles
FROM track_master
WHERE (artist LIKE '%Lo%Fidelity%Allstars%')
  AND (track LIKE '%Battle Flag%')

year, genre, release_date, styles, artist and track are all indexed, when I ran the query with EXPLAIN I received a ALL under TYPE column, which as I've been reading is NOT good, however I do not know what else to do to optimize the query.

Besides the mentioned columns, there is also

id int(25)
release_id int(25)
album_title varchar(2250)
master_id int(25)
main_release int(5)
artist_id int(25)

You need to add full text index to your table as

ALTER TABLE track_master
ADD FULLTEXT INDEX `artist_key` (`artist`)
ADD FULLTEXT INDEX `track_key` (`track`);

Also you need to tweak mysql server settings to support 2 character words. Find the mysql config file, In case of UBUNTU based server it is /etc/my.cnf and then locate the statement innodb_ft_min_token_size=3 and change it to innodb_ft_min_token_size=2. Then restart mysql server.

After that rewrite your query and use it as

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles
FROM track_master
WHERE MATCH(artist) AGAINST ('Lo Fidelity Allstars')
AND MATCH(track) AGAINST ('"Battle Flag"')


Database Entity Design for Enumeration Table I have an enumeration of ~10 values - they are Char such as A through K. These are all unique. They are used for two purposes - an application is forced to select acceptable values and also there is a many to many relation between product and this enumeration table.

Is it bad practice to set the Primary Key as simply this unique value [A-K]?
So basically one column of char(1) as the Primary Key. Or should I use an ID field and then set these values in a column and force that column to unique?

The values in this enumeration will never change. They are fixed by the representation. For example, position 1 can only be position 1, like first base will always be first base and never will it become 3rd base (or at least in the last 150 years it has not - my app might not last that long). So that is why I was thinking to simply do it this way - because it truly is permanent, non-changing.

If there was any sort of choice to be made by your designers or users in the selection of these values I would go for the surrogate ID approach. What has been decided can be un-decided and the change will be easier with surrogates in place.

Since you are adamant the values are fixed by a Law Of Nature (or, at least, some external authority) I would be OK with using the single-column approach. In these circumstance the chances of change, and the wider impact, mean you'll likely have sufficient notice to effect the application update.


Duration of adding versus dropping a column I have a table containing more than 50 million rows.

When I add a column:

ALTER TABLE my_table ADD my_column NUMBER;

It's very fast.

Just after adding the column, I drop it.

ALTER TABLE my_table DROP COLUMN my_column;

It takes 3 minutes!

Why is adding a column so fast, when removing it without populating it is slow?

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered.

(This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ordinary data types.)

On the other hand, when you physically drop a column (rather than hide/set unused), all the rows need to be at least inspected to check if any data needs to be removed. So the theoretical best case cost is a full table scan. (There's some table locking involved too I believe.) Worst case all rows have to be rewritten. This can be quite expensive.


VACUUM ANALYSE not reducing index bloat, only VACUUM FULL We are experiencing many slowdowns due to index bloat. On trying to optimize the index, recreating it seems to generate a much smaller index:
reviews_hotel_id_connection_id_review_date_created_at_review_id: original index
reviews_hotel_id_connection_id_review_date_created_at_new: exact duplicate of the original index, recreated
 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 5296 kB
 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB

The new index is only 55% the size of the original index!
Running VACUUM ANALYSE reviews doesn't result in any index reduction!
Running VACUUM FULL reviews makes the original index have exactly the same size as the new index:
 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB
 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 2944 kB

Although VACUUM FULL locks the table even for reads! Is are an option for optimizing indexes without causing downtime?

VACUUM typically does not shrink the tables or indexes.
From v12 on, you can use REINDEX CONCURRENTLY for that.
Before, you would create a second index with CREATE INDEX CONCURRENTLY, then drop the old index. But that wouldn't work for a constraint index.
But 50% bloat like in this case is quite normal and healthy for a b-tree index, and I wouldn't worry about it.


InnoDB ibdata1 corrupted, innodb_force_recover not working I think I have checked every post in the internet.
Thing is my MySQL (MariaDB) won't come up and after much testing I have seen it is because of the InnoDB files.
I have tried with different innodb_force_recover and also with mysqld_safe, also with online tools and with every tut I have found.
I'm pretty desperate at this point, so any kind of help is welcome.
The log that matters is this one:

2018-11-27 15:00:16 140309240685120 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: The InnoDB memory heap is disabled
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using Linux native AIO
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using SSE crc32 instructions
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Completed initialization of buffer pool
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Highest supported file format is Barracuda.
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: The log sequence number 1154517884 in ibdata file do not match the log sequence number 1345427345 in the ib_logfiles!
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
InnoDB: Page directory corruption: infimum not pointed to

MDEV-13939 Page directory corruption: infimum not pointed appears to be a bug upstream that has some tips on recovery.


MongoDB cross datacenter replication without elections or high data availaility I want to replicate a MongoDB database to another node, located in another datacenter.
This is to help guard against data loss in the case of a hardware failure.
We don't want/need high availability or elections; just a 'close to live' read-only copy of the database, located in another DC.
Everything I read says that you need an odd number of nodes, due to elections, but this isn't something we need/want and I can't find anything related to just having one primary, and one secondary (I might be being blind).
Is this something we can achieve with MongoDB, and if so are there any 'gotchas' or serious downsides we should consider?

You could created a Replica Set with a Hidden Member. The hidden member will take a read/only copy of your data. However, if the hidden member goes down then the primary steps down to secondary, i.e. your MongoDB becomes read/only.
You can define an Arbiter. It is not necessary to create the Arbiter service on a dedicated host, define it on your primary node. It is just a small mongod service which consumes almost no CPU/memory resources or disc space.
If you have a Primary + Arbiter on one host and the Hidden Secondary on another host then the second may stop and your application continues running normal. Of course, is your primary goes down then the application is not available anymore.


MultiSubnetFailover=true for DBMSes besides SQL Server A connection string for SQL Server can contain MultiSubnetFailover=true, to handle multiple DNS A records, letting the client try to connect to every IP address of a domain (or subdomain) name, to access the current master (of Always On availability groups).
Does this work with other DBMSes, like MySQL?  If not, is there an equivalent?

MultiSubnetFailover can be used only for connecting to SQL Server part of the client connection string.
For MySQL, if you are using .NET, then you can use multiple hosts in connection string or you can even use single DNS that provide DNS Service (SRV) records to provide failover, load balancing, and replication services.