Multiple Batches in SQL Server Migration Script I'm trying to come up with a database script that will be used to migrate an earlier version of an application database to a later version. The differences are quite extensive, and accomplishing the migration will involve creating new tables, renaming tables, adding functions, adding views that have calculated columns that depend on those functions, etc. After the script is done, I will hand it to somebody else to run on the target environment.

I'd like to be able to run the entire migration in a single transaction so that if something goes wrong it will be simple to roll back.

The problem I'm running into is that when when the script tries to CREATE FUNCTION, it complains that:

  SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.

I've done some research and found that SSMS allows the use of the GO keyword to separate batches, but every time I try to wrap the entire script in a BEGIN TRANSACTION and then include a GO to separate the create function call into its own batch, I get an error:

  Incorrect syntax near 'GO'.

I'm not sure why, since answers like this one seem to work with GO statements in there.

I'm also making an assumption (maybe incorrectly) that the person I give the script to will be using SSMS.

I also found a couple articles that mentioned using SET XACT_ABORT ON;, which I've tried outside of the transaction at the very beginning.

So my script looks something like this:

SET XACT_ABORT ON
GO
BEGIN TRY
BEGIN TRANSACTION
    -- Create some tables
    GO -- Error
    CREATE FUNCTION [...]
    GO -- Error
    -- Create some views that rely on the above function
    COMMIT TRANSACTION
END TRY -- With the GOs in there, this line also gives an error: Incorrect syntax near 'TRY'. Expecting CONVERSATION.
BEGIN CATCH
    -- Error handling, including transaction rollback
END CATCH

Any recommendations on how to migrate a SQL database using multiple batches but one transaction in a single script?

The problem is really just about error handling, as SSMS has no way to handle errors across batches when you run the script in an SSMS query window (ether normal or SQLCMD mode).  So your transaction might abort, and SSMS would run the next batch.

If you run the script with SQLCMD you can start a transaction in the first batch, and use the -b switch.  Any error will abort the script and roll back your transaction.

EG

use tempdb
go
begin transaction
-- be sure to run with sqlcmd -b
-- eg c:> sqlcmd -b -i c:\deploy\thisscript.sql
go

--. . . any number of batches here

go

commit transaction

go

Of course you must ensure that your script does not commit or rollback the transaction, catch errors, or contain any statements that can't be run in a transaction (like adding files to a database).

You can similarly burst the batches yourself in .NET or PowerShell, start a transaction, and run each batch separately, commiting only if all batches ran without error.

Another good way to handle this whole scenario (if you can do the upgrade during an outage) is with a database snapshot. In case of a failure, restoring from a database snapshot allows a quick rollback of all the upgrade actions without the need of a transaction.


"max_packet_size" variant in SQL Server I am wondering if there is a SQL Server "version" of the MySQL max_packet_size parameter. Is there a way to configure this setting in SQL Server?

See Configure the network packet size Server Configuration Option:

sp_configure 'network packet size', ...;

But this setting is not the same as MySQL's max_allowed_packet, is more similar to net_buffer_length. SQL Server's maximum request limit is 65,536 * Network Packet Size, which is closer to max_allowed_packet meaning.


Is it possible to bulk insert using CTE in MySQL? I created this small sample fiddle to play with the model: https://www.db-fiddle.com/f/b37ASVuSxna9wEoWn9hZDw/0

I have a table called folder, which represents a folder hierarchy with a parent_id column pointing to the parent folder. The root folder in a tree has NULL as its parent_id.

I have another table that specifies access restrictions to folders for users and user groups, it is called folder_access. It has a foreign key relation to the folder table, and then it has columns for user_id, user_group_id and a flag called access (which is an int column, but it is used as a bitwise flag that signifies access like read, write, delete (not important in relation to this question)).

I am implementing an API that gets a list of folder ids, a list of either user ids or user group ids and a single access value. The API should then insert or update rows in the folder_access table like this:

Insert a row for each combination of folder id and user id (or user group id)
Also insert a row for each descendant folder in the hierarchy
If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

I implemented a version without the second requirement above:

public async Task SaveFolderAccess(long areaId, List<long> folderIds, List<long> userIds, List<int> userGroupIds, Access access)
{
    var values = new List<string>();
    foreach (var folderId in folderIds)
    {
        foreach (var userId in userIds)
        {
            values.Add($"({areaId},{folderId},{userId},NULL,{(int)access})");
        }

        foreach (var userGroupId in userGroupIds)
        {
            values.Add($"({areaId},{folderId},NULL,{userGroupId},{(int)access})");
        }
    }

    await dbSession.ExecuteSqlRawAsync(
        @$"INSERT INTO folder_access (area_id, folder_id, user_id, user_group_id, access)
            VALUES {string.Join(",", values)}
        ON DUPLICATE KEY UPDATE access = {(int)access}");
}

I know I can create a common table expression to find all the descendant folder ids for a particular folder, like this (replacing {folderId} with the actual value):

WITH RECURSIVE CTE (id) AS
(
SELECT f.id
    FROM folder AS f
    WHERE id = {folderId}
UNION ALL
SELECT f.id
    FROM CTE JOIN folder AS f
    ON f.parent_id = CTE.id
)
SELECT id FROM CTE

But is it possible to combine this into one big command that leverages both the CTE and the "ON DUPLICATE KEY UPDATE"?

The task is not defined completely, but nevertheless:

-- Parameters
SET @folderId:=2;
SET @user_ids:='11,14';
SET @access_mask:=2;

-- Insert/update access masks for single users
INSERT INTO FOLDER_ACCESS (FOLDER_ID, USER_ID, ACCESS)
WITH RECURSIVE
folder_ids AS ( SELECT f.id
                FROM FOLDER AS f
                WHERE id = @folderId
              UNION ALL
                SELECT f.id
                FROM folder_ids
                JOIN FOLDER AS f ON f.parent_id = folder_ids.id ),
user_ids AS ( SELECT SUBSTRING_INDEX(@user_ids, ',', 1) user_id,
                     CONCAT(SUBSTRING(@user_ids FROM 1 + LOCATE(',', @user_ids)), ',') slack
            UNION ALL
              SELECT SUBSTRING_INDEX(slack, ',', 1),
                     SUBSTRING(slack FROM 2 + LENGTH(SUBSTRING_INDEX(slack, ',', 1)))
              FROM user_ids
              WHERE slack )
SELECT folder_ids.id, user_ids.user_id, @access_mask
FROM folder_ids, user_ids
ON DUPLICATE KEY
UPDATE ACCESS = @access_mask;

fiddle

The query for groups access masks update is absolutely similar.

  If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

"Update" is too vague term.

I think that the flag of access mask change type must exist. For example, @type, with the values:

unconditional replace
set bits
clear bits

And update clause will be

UPDATE ACCESS = CASE @type WHEN 1 THEN @access_mask
                           WHEN 2 THEN ACCESS | @access_mask
                           WHEN 3 THEN ACCESS & ~@access_mask
                           ELSE ACCESS
                           END


SQL Server tail log backup fails where it shouldn't I am trying to set up a demo of backing up the tail of the log in case of disk failure, but it doesn't work as expected.

The setup:

Set AdventureWorks backup model to "Full", create a full, a differential and a log backup (always with some writing in between)
Move the only primary data file to a USB drive:

ALTER DATABASE [AdventureWorks2012] SET OFFLINE

ALTER DATABASE [AdventureWorks2012] MODIFY FILE ( NAME = AdventureWorks2012_Data, FILENAME = 'E:\AdventureWorks2012_Data.mdf')

-- Copy file physically here

ALTER DATABASE [AdventureWorks2012] SET ONLINE
Check if file is opened on USB drive (rename fails because file is locked), then disconnect USB drive
INSERT some more data (to prove that it still works and gets restored with the log tail)
Try tail log backup:

BACKUP LOG [AdventureWorks2012] TO
 DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
 WITH  NAME = N'AW Tail Log Backup', NORECOVERY, CONTINUE_AFTER_ERROR

This last step fails with the following message:

  Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating
  abnormally.

  Msg 823, Level 24, State 3, Line 1 The operating system
  returned error 21(Das Gerät ist nicht bereit.) to SQL Server during a
  write at offset 0x00000002860000 in file
  'E:\AdventureWorks2012_Data.mdf'. Additional messages in the SQL
  Server error log and system event log may provide more detail. This is
  a severe system-level error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

So why does SQL Server insist on writing to the data file during a tail log backup where the whole point is that you only need the log file for this? Have I missed a backup parameter or some obscure trace flag?

BTW: I can still do a log backup if I use NO_TRUNCATE as the only parameter, but that's no real tail backup because it lacks the NORECOVERYportion and thus still allows users to change data after that. If I add NORECOVERY to that, it fails again with the same message as above. If I try to restore with the NO_TRUNCATE log it complains that I have to take a tail log backup first...

Paul Randal did a good piece on backing up the tail of the log that includes just this case.  In it he says that the method to use when there is no data file is using NO_TRUNCATE.  Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

BOL does say

  To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.


Stored procedure to handle empty sets How can I make a stored procedure to print a specific message if an empty set wa returned by the query?

If you are using MySQL 5.5 or 5.6, you can accomplish this with SIGNAL.

Simplified example, inside the body of the procedure...

IF NOT EXISTS (SELECT * FROM users WHERE username = input_username AND password = input_password) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'authentication failed.';
ELSE
  -- normal code to select and return the username other info to the caller, etc.
END IF;

This will throw an exception:

ERROR 1644 (45000): authentication failed.

If you wanted to be more specific, you'd need to declare a variable to use a the MESSAGE_TEXT and populate it accordingly before calling SIGNAL because SIGNAL only accepts a variable -- not an expression -- as an argument:

DECLARE custom_error TEXT DEFAULT NULL;
...
SET custom_error = LEFT(CONCAT('authentication failed for user ',IFNULL(CONCAT("'",input_username,"'"),'NULL')),128);
...
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = custom_error;

Your code would probably take a different approach than IF ... EXISTS such as selecting INTO a variable and then testing that variable for null and then either returning it to the called with an unbounded SELECT or throwing the exception.

The reason for the LEFT( ...,128) is that this is the maximum length for an argument to MESSAGE_TEXT.  Overrunning it won't hurt anything but you'll end up throwing an error about the invalid argument instead of the error you intended.

For simplicity, the example assumes that the password is in cleartext which obviously wouldn't be the case.


MySQL inserts/deletes slowing down after a while after Start/Reboot The Setup here is:
OS: Ubuntu 12.04 (1 Core and 1.75 GB of RAMO)
MySQL Version: 5.5

There are about 8 to 9 tables in the database. On initially starting mysql the queries are fast at around 0.05 - 0.10 seconds, however after sometime the INSERTS and DELETES slow  down to around 1 - 2 seconds, however the SELECTS are still around 0.05 - 0.10 seconds.

Then after rebooting the system the situation reverts back to how it was in the beginning of the previous paragraph. Everything is fast, but then INSERTS and DELETES slow down tremendously after a while.

Note: I'm just doing simple inserts and deletes on very few records, and the database starts with empty tables in the beginning.

Any insight?

Edit: Create Table of 1 of the Tables

| FeedUps |
CREATE TABLE `FeedUps` (
  `post_id` int(10) unsigned DEFAULT NULL,
  `liker` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `post_id` (`post_id`,`liker`),
  KEY `liker` (`liker`),
  CONSTRAINT `FeedUps_ibfk_1` FOREIGN KEY (`post_id`)
      REFERENCES `Feed` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FeedUps_ibfk_2` FOREIGN KEY (`liker`)
      REFERENCES `Users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I found the problem, in the my.cnf file I had turned on TABLE_CACHE.
The way I resolved it without having to restart it was by using the command FLUSH TABLES. Shortly after that the inserts/updates/deletes resumed working at normal speeds.


Case insensitive LIKE in DB2 Is there a way to do a LIKE operation in DB2 against a varchar column in a case insensitive way, without changing the underlying column definition?

Or is there a workaround like SELECT * FROM mytable WHERE mycolumn LIKE '%string%' OR mycolumn LIKE '%STRING%' that would work?

Try

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn,'string',1,'i')

Will work if your Db2 version supports regular expression functions
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061494.html
and if you escape any regex meta-characters in your string


How these conflicting scenarios will work in cassandra when one of the mode crashes? Say we have two nodes(simple replica no sharding) i.e. N1,N2  with replication factor as 2. I am using default Read and write consistency levels i.e. R and W are 1 which means it will read/write from/to single node

Scenario 1

Consider write request goes to node N1  to update record R1. Say name scott updated to scott_update1
Before it is replicated to N2, N1 crashed
Another write request goes to node N2  to update the same record R1. Now scott updated to scott_update2
Now when N1 comes back, will the name scott_update1 be updated to scott_update2 ?

Scenario 2

Consider write request goes to node N1  to update record R1. Say name scott updated to scott_update1
Before it is replicated to N2, N1 crashed
Read request  goes to node N2  to read the same record R1. It returns scott
Now when N1 comes back, will the name scott_update1 be updated to scott or it will stay scott_update1 ?

In both scenarios, everything depends on the how long the node N1 was offline, and where the hints enabled or not...

In scenario 1, if downtime was less than 3 hours (default window to store hints), then the changes done when N1 was offline will be replayed, and value will be updated to scott_update2 because this value has greater timestamp. If downtime was larger than this window - then you need to run repair.

For scenario 2, there are 2 things - it could be eventually repaired when read repair happens, or you can explicitly run repair.

But talking about replication - write usually happens to all replicas at the same time, and consistency level specifies how many nodes need to answer to consider request as successful, so N2 could be updated as well in most cases.


Minimum requirements to run Sql Server BCP.exe utility I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe utility run without a SQL Server install?

Thanks for any help.

You have to use one of the SQL Server distributable packages to stay within licensing terms. These are not full installs.

The one you want is "Microsoft® SQL Server® 2008 R2 Command Line Utilities"

Note: copying bcp.exe by itself will not work


Remove ORA-01100:DATABASE ALREADY MOUNTED I am using Oracle 11g and  there is a database create during installation name "xyz" and user "xyz".Now i created a user 'abc' and gave following permission to abc.

"CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK,
 CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM,
 CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE,
 CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE"

Now, I am trying to create database named 'abc'

At the time of creation of the database, I am get the following exception:

ORA-01501:CREATE DATABASE FAILED
ORA-01100:DATABASE ALREADY MOUNTED

Please tell me how to come out of this.
What standard procedure to be for creating database?

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace.

If you want to create tables, or other schema objects, you create them with a chosen user as their owner. Such an owner is a schema and there can exist multiple schemas in one database. Schemas can share tablespaces, if you want to. Often, tablespaces are created dedicated for a schema, giving a bit more oversight in who is using what resources.

Since Oracle 12c there also is the option to create a pluggable database inside a container database. Once that pluggable database is created, you can connect to it and use it in a similar way as the pre-12c database, where you again start creating users/owners/schemas and their objects.


commit or rollback on a statement based replication, effect on auto increment id How commit or rollback is safe on statement based replication in terms of auto_increment column?

If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment column in case of statement based replication.

CREATE TABLE t1 ( c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(1)) ENGINE = INNODB;

Session 1
begin;
INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d'); should increase c1 to 4 in master while no affect in slave as not yet commited, so not yet written to binlog

Session 2
begin;
INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d');
commit; should have c1 start from 5 in master and from 1 in slave

Session 1
rollback or commit doesn't matter

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

  Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
  Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

  In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back.

I think you will find an extra "statement" in the binlog that assigns the auto_inc value for the subsequent statement.  This guarantees that the Slave has the same ids for SBR.

For RBR (Row-Based-Replication), there is no issue, since the id is part of the INSERT.

In addition to the possibility of "burning" ("losing") ids, the ids can be replicated out of order.  This because ids are grabbed as needed, but replication is in COMMIT-order.  (This applies to SBR and RBR.)

For multi-master, auto_increment_increment > 1 -- another reason for gaps in the ids.


How restore from .gz backup with new database name using mongorestore r3.2.9? I'm trying to backup/restore mongodb database to/from .gz files as sample script here

#01 create .gz backup - ok for r3.2.9 and r3.4.10
mongodump --db ${DB_NAME} --gzip --archive=${BACKUP_FILE_GZ}

#02 restore from .gz file - NOT ok for r3.2.9
mongorestore --gzip --archive=${BACKUP_FILE_GZ} --nsFrom "${DB_NAME}.*" --nsTo "${DB_NAME_RESTORE}.*"

Step 01 i.e. back up is good for both mongodb version r3.2.9 and r3.4.10; though step 02 NOT works for r3.2.9

How can I get mongorestore version r3.2.9 to restore from .gz file and be able to rename the database?

p.s.

We have the solution here but that requires the backup to be a folder; my backup files are huge i.e. 1Gb-2Gb so the extraction is too much time-consuming.

Nothing works for me but this.

mongorestore --gzip --archive=/path/to/file.gz --db db_name


MySQL - Order by closest value on multiple columns I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's level and connection. However I want to order this so that a user's level is the most important and then it will look at the connection. So if there are two users with the same level, it will look at the connection instead. Maybe there will be more columns in future that can be used to further refine the search. I have seen that on single column selects like this, I could use abs() however I'm not sure if this is possible on this as well?

create table users (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  connection INT(11),
  level INT (11)
);

create table lobby (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11),
  CONSTRAINT user_id FOREIGN KEY (id)
  REFERENCES users(id)
);

insert into users values (null,5,6);
insert into users values (null,7,10);
insert into users values (null,10,9);
insert into users values (null,10,4);
insert into users values (null,1,8);

insert into lobby values (null,1);
insert into lobby values (null,2);
insert into lobby values (null,3);
insert into lobby values (null,4);
insert into lobby values (null,5);

So if I'm using user_id = 1 as my user I would have the following select statement:

select lobby.id, lobby.user_id, users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id
where user_id <> 1 and
users.connection between 0 and 10 and
users.level between 1 and 11
order by abs(connection-5);

This select statement comes back with the following table:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  3 |       3 |         10 |     9 |
|  4 |       4 |         10 |     4 |

However what I want is the following output:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  4 |       4 |         10 |     4 |
|  3 |       3 |         10 |     9 |

Thanks.

EDIT: Ok so I am currently using the abs function and it's working well, however it is working on multiple fields and because of this I feel that the statement is not very efficient. Is there a better way of doing this?

Simply add another column,
order by abs(connection-5), abs(level-4) is valid syntax:

select lobby.id, lobby.user_id,
   users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id
where lobby.user_id <> 1
  and users.connection between 0 and 10
  and users.level between 1 and 11
order by abs(connection-5), abs(level-4);


How can I create a stored procedure of inheritance tables in SQL Server I have three tables

create table CustomerTypes(
CustomerTypeID int primary key identity(1,1),
CustomerType varchar(11)
);

insert into CustomerTypes(CustomerTypeID,CustomerType)
select 1, 'Regular' union all
select 2, 'Business'

create table Customers(
ContactID int primary key identity(1,1),
CustomerTypeID int references CustomerTypes(CustomerTypeID),
FirstName varchar(25)not null,
LastName varchar(25)not null,
DOB date not null,
Telephone varchar(18),
Notes varchar(250),
AddDate date not null
constraint cust_AltPK unique (ContactID,CustomerTypeID)
);

create table Regular_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 1 persisted, --regular
Specification varchar(45),
Date_Joined date,
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);

create table Business_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 2 persisted, --Business
Business_Name varchar(30)not null,
Business_Type varchar(30),
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);

I want to create a stored procedure called CustRegistration to insert data into the various customer tables, by combining customerType table and Customer table.

If you're trying to mimic inheritance in MS SQL Server, you'd technically create three procedures:

RegularCustRegistration: insert a regular customer
BusinessCustRegistration: insert a business customer
CustRegistration: insert common data to Customer

NOTE: Since the ContactID in Regular_Customer and Business_Customer must match the one in Customer, you need to remove the IDENTITY attribute on those two tables. All ContactID values must be generated from Customer.

The code for CustRegistration and RegularCustRegistration would look something like this:

CREATE PROCEDURE CustRegistration
      ( @ContactID int OUTPUT
       ,@CustomerTypeId int
       ,@FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18)
       ,@Notes varchar(250)
       ,@AddDate date
      )
AS
BEGIN
    DECLARE @CustOut TABLE (ContactID int);

    INSERT INTO Customers (CustomerTypeId, FirstName, LastName, DOB, Telephone, Notes, AddDate)
    OUTPUT INSERTED.ContactID INTO @CustOut
    VALUES (@CustomerTypeId, @FirstName, @LastName, @DOB, @Telephone, @Notes, @AddDate);

    SELECT TOP (1) @ContactID = ContactID
      FROM @CustOut;
END;

CREATE PROCEDURE RegularCustRegistration
      ( @FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18) = NULL
       ,@Notes varchar(250) = NULL
       ,@AddDate date
       ,@Specification varchar(45) = NULL
       ,@Date_Joined date = NULL
      )
AS
BEGIN
    DECLARE @ContactID int;
    DECLARE @CustomerTypeID int = 1; -- regular

    EXECUTE CustRegistration
            @ContactID OUTPUT
           ,@CustomerTypeID
           ,@FirstName
           ,@LastName
           ,@DOB
           ,@Telephone
           ,@Notes
           ,@AddDate
    ;

    INSERT INTO Regular_Customer (ContactID, Specification, Date_Joined)
    VALUES (@ContactID, @Specification, @Date_Joined);
END;

See this dbfiddle.uk link to see this work. It also has corrected versions of your table creation statements (IDENTITY removed from Regular_Customer and Business_Customer; PRIMARY KEY has to come after IDENTITY(1,1); need to use SET IDENTITY_INSERT <table> ON to insert explicit values to an IDENTITY column).

Since we need the identity value from the Customers table to create our row in Regular_Customer, we use the OUTPUT clause of the INSERT statement to capture the ID created by the INSERT, and mark the @ContactID parameter of the CustRegistration procedure as an OUTPUT parameter, so the value put in that parameter is passed back to the specified variable in the calling procedure.

You should be able to create BusinessCustRegistration based on this.


Why is Full Recovery model a requirement for an Availability Group? We have all our production databases in simple recovery model, and are very happy about it because it fully satisfies our RPOs and RTOs.

Now we want to implement AG DR solution for our databases and we found out that full recovery model is a requirement.

My question is WHY? Theoretically, is there any technical reason why AG or other SQL Server DR solutions (Log shipping, DB Mirroring) shouldn't work with simple recovery model?

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery either. In mirroring, a transaction log block change is sent to a mirror server; in log shipping, a transaction log backup is sent. So, you can see the very basic concept on which Mirroring/Logshipping/AlwayON works is a Transaction log.

The transaction log is the most important part of the database—it's the only place where all changes to the database are guaranteed to be described in the event of a crash. Log records for a single transaction are linked by their LSNs and are almost like a timestamp. This timestamp is internally used to link various transactions and replay it on DR servers.

The main aim of a DR technology is to provide as little data loss as possible, and this can only be achieved if the transaction log is maintained structurally consistent. In simple recovery, transaction logs are truncated the moment the transaction commits, which means the information is not required and the space occupied by the old transaction would now be used to write a new transaction. In that case the old information is lost. If you somehow want to get it, you cannot. But in full recovery you can do it.


Trigger to update a materialized view once after relevant transaction? I'm using PostgreSQL 9.6.

I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched.

I'm trying to find a good strategy to run REFRESH MATERIALIZED VIEW post_search.

The ideal solution would be a trigger like this: at the end of (or after) any transaction that modifies posts, tags and/or posts_tags (join table), refresh the materialized view exactly once.

How could I accomplish this?

An OK solution using triggers

This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.

(In the future, we're thinking of using an actual table for search instead of a materialized view, and updating individual relevant rows via triggers instead of refreshing an entire matview.)

Create a function to refresh the materialized view concurrently:

  CREATE OR REPLACE FUNCTION refresh_post_search()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
  BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY post_search;
  RETURN NULL;
  END $$;

And create a trigger for each of the underlying tables:

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts_tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();


Log Shipping Backup grey in Transaction Log Shipping Status I have slow network that makes configure log shipping via GUI always timeout, so I generate script and run it in both primary server and secondary server.

The scripts work fine but the primary database not show in secondary server log shipping report if I select [msdb].[dbo].log_shipping_monitor_primary in secondary server my primary database not insert at that table.

Is there possibly something wrong when I generate or run logshipping script that makes it not configure properly?
How to fix this, can I manually insert at [msdb].[dbo].log_shipping_monitor_primary or it not enough?

Currently the backup,copy and restore works fine in my server just backup status not show at report transaction log shipping.

Currently the backup,copy and restore works fine in my server just
  backup status not show at report transaction log shipping.

If backup, copy and restore is working fine then its objectivity is obtained and one site to another site is in sync however in case you are unable to see only the backup status, you may check this at individual job level by clicking on job history at primary server and see whether it is successful or has completed with error.

Alternatively, you may use script to check the current status of log-shipping at primary and secondary server:

--Primary

select * from msdb..log_shipping_monitor_primary

--Secondary

select * from msdb..log_shipping_monitor_secondary

Primary will give you information about backup whereas secondary will have details on both the jobs i.e. copy as well as restoration.

You may try below stored procedure also which will return result however un-formatted:

use master
go
exec sys.sp_help_log_shipping_monitor
go

  The scripts work fine but the primary database not show in secondary
  server log shipping report if I select
  [msdb].[dbo].log_shipping_monitor_primary in secondary server my
  primary database not insert at that table.

This is supposed to return rows at primary server and not at secondary server, it seems you are mistake about this table.

Procedure sp_help_log_shipping_monitor can be run on both the nodes i.e. primary and secondary and will return result for backup on primary whereas on secondary this would give details about copy and restore.

Hope above helps.


What is the purpose of this Uniq1002 column in this index scan? Take the following repro:

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        NONCLUSTERED
        IDENTITY(1,1)
    , col1 datetime NOT NULL
    , col2 varchar(800) NOT NULL
    , col3 tinyint NULL
    , col4 sysname NULL
);

INSERT INTO dbo.t (
      col1
    , col2
    , col3
    , col4
    )
SELECT TOP(100000)
      CONVERT(datetime,
         DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2000-01-01 00:00:00'))
    , replicate('A', 800)
    , sc2.bitpos
    , CONVERT(sysname, CHAR(65 + CRYPT_GEN_RANDOM(1) % 26)
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26)
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26))
FROM sys.syscolumns sc
    CROSS JOIN sys.syscolumns sc2;

Here I'm adding a clustered index onto a set of columns that are not unique, and typical single-column non-clustered index:

CREATE CLUSTERED INDEX t_cx
ON dbo.t (col1, col2, col3);

CREATE INDEX t_c1 ON dbo.t(col4);

This query forces SQL Server to do a lookup into the clustered index.  Please forgive the use of the index hint, it was the quickest way to get the repro:

SELECT id
    , col1
    , col2
    , col3
FROM dbo.t aad WITH (INDEX = t_c1)
WHERE col4 = N'JSB'
    AND col1 > N'2019-05-30 00:00:00';

The actual query plan shows a non-existent column in the Output List for the nonclustered index scan:

Ostensibly, this represents the uniqifier used in the non-unique clustered index.  Is that the case?  Is a column named like that always the clustered index uniqifier?

Ostensibly, this represents the uniqifier used in the non-unique clustered index. Is that the case?

Yes.

  What is the purpose of this Uniq1002 column in this index scan?

Each row in the nonclustered index must be associated with exactly one row in the base table so that Bookmark Lookups (RID or Key) work correctly. This mapping is provided by the "row locator".

For heap tables, the row locator is the RID. For clustered row store tables, it is the clustering key(including the uniquifier where necessary).

For the Key Lookup in your plan to work, it must have access to the row locator. This includes the uniquifier, so it must be emitted by the nonclustered index scan.

The uniquifier is stored in the variable-length portion of the row so it only takes up space when needed (i.e. when a duplicate key actually exists).

  Is a column named like that always the clustered index uniquifier?

Yes. The uniquifier column is always named UniqXXXX. The row locator associated with heap tables is named BmkXXXX. The row locator for a columnstore table is named ColStoreLocXXXX.

Observing the uniquifier

It is possible to directly observe the values of the uniquifier on SQL Server versions that contain a functional query_trace_column_values Extended Event.

This undocumented and unsupported event is in the Debug channel. It was introduced with SQL Server 2016, and stopped working around CU11 of SQL Server 2017.

For example:

CREATE TABLE #T (c1 integer NULL INDEX ic1 CLUSTERED, c2 integer NULL INDEX ic2 UNIQUE, c3 integer NULL);
GO
INSERT #T
    (c1, c2, c3)
VALUES
    (100, 101, 0),
    (100, 102, 1),
    (100, 103, 2);
GO
DBCC TRACEON (2486);
SET STATISTICS XML ON;
SELECT T.* FROM #T AS T WITH (INDEX(ic2));
SET STATISTICS XML OFF;
DBCC TRACEOFF (2486);
GO
DROP TABLE #T;

Has the plan:

It produces event output like the following on SQL Server 2016:


Removed privileges from root user and can't access database New to databases and accidentally removed privileges from root user; only privileges I currently have are USAGE and PROXY. This is a MySQL database that I access through HeidiSQL. Currently cannot access my data tables; is there a way to get these privileges restored? Did not create any other user accounts.

You can use following steps to regrant root priviledge.

Shutdown mysqld.
Start mysqld with --skip-grant-tables.
Use mysql to connect mysqld. (Type mysql only, No need to input password -p, maybe No need to input username root, either)
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;
Restart  mysqld normally.


how is synchronization maintained between different write and read database One of my friends was asked this question in interview. Given two different databases one on which only write is done and the other on which only read is done. How does one maintain synchronization between the two given a specified time lag. I would like to add that these databases have same schema.

Eg: R is my Read database and W is my Write database. I want to ensure that read database should be the replica of Write database say a minute earlier.

What I am keen here is on the techniques(concepts) this is done rather than the implementation. Lets say the RDBMS is SQL Server.

In SQL Server 2012 you can have read-only secondaries, which allow you to use Availability Groups to mirror a database or set of databases to another server, and perform read-only queries against them even as they are being mirrored.

The downside: this requires Enterprise Edition on both nodes, which can only be licensed per core (not CAL), and the underlying OS needs to be WSFC, so the OS needs to be Enterprise Edition as well (this is less of a big deal since the additional cost of Enterprise at the OS level is laughable compared to the jump in SQL Server license costs).

In previous versions you could do this with log shipping (subject to the annoying limitations @datagod already mentioned) or with mirroring + snapshots (which also needs Enterprise Edition). I haven't seen any of our customers using replication for this specific requirement but I suppose that is possible as well.


Hide Select Output from T-SQL I'm trying to get query execution time, but I also want to hide the query output. I just want the elapsed time - no output.
Example
DECLARE @Start datetime
DECLARE @End datetime

SELECT @StartTimeWA=GETDATE()

SELECT
       [id]
      ,[database_id]
      ,[proc_name]
      ,[exec_t] from
  [DB].[dbo].[STAT]

SELECT @End=GETDATE()

SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

At the moment, I get query output and at the bottom my duration, which is the only thing I want as far as the output goes. I haven't been able to do this and wondering whether anyone else has come across similar problems? This is something I want to do in T-SQL not Management Studio or anything like that.
I'm trying to monitor the time it takes to run the select statement and report back to a server. I have an external monitor server which will run that every minute and get the time back (duration it took) which I will use over time to trend / baseline. As the current query spits out the select results and my duration it skews it, and my monitor server gets confused. I just wanted the duration column. I will also be doing this for inserts, which will be straightforward as it won't need to perform a select.
I'm trying to do this purely in T-SQL. I don't want to use DMVs as I want to get the time it takes (snapshot) when I run a query and check whether this changes over time when the server goes through the various levels of load as this will give me a good idea as to whether query execution time changes.

There are a lot of different ways to do this.

I don't usually recommend inserting into a #temp table, since any tempdb load or autogrowth may impact the results, and I definitely don't recommend using a @table variable, since modifications to those are forced serial (no parallel plan can be used), which may change actual query times.

Variable Assignment

You can declare a variable and assign your columns to it, like this:

DECLARE @Start datetime
DECLARE @End datetime
DECLARE @blob_eater SQL_VARIANT;

SELECT @StartTimeWA=GETDATE()

SELECT
       @blob_eater = [id]
      ,@blob_eater = [database_id]
      ,@blob_eater = [proc_name]
      ,@blob_eater = [exec_t] from
  [DB].[dbo].[STAT]

SELECT @End=GETDATE()

SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

Though doing this may prevent some parameter embedding optimizations. See Parameter Sniffing, Embedding, and the RECOMPILE Options under "An Embedding Restriction".

Note that this method may trigger plan warnings about implicit conversions, but they're not the kind you have to worry about. See this Q&A for background: What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice.

In SSMS

You can change the settings to discard query results.

SQL Query Stress

SQL Query Stress is an open source tool that allows you to run queries against a SQL Server to simulate load. No query results are returned to the application when they're run.

You can read some instructions on it here.

ostress (RML Utilities)

ostress is a similar tool, published by Microsoft, which also doesn't return results to the client, unless you choose to do it.

I've written some about it here.

Plan Explorer

SentryOne's Plan Explorer is a free alternative to view SQL Server execution plans and deadlocks with.

You can also use it as a client to query SQL Serve to some degree:

This will also discard results.

Hope this helps!


Struggling with left join - products I got a quite easy question for you.

I have a table

 products [ID,Name,Category,Price]

and a table

product_client[ID,product_ID,client_ID]

The products table contains all the products of course, but what the product_client does is, it is storing some products that are exclusively for a certain client.

So I wrote a small query for this one

SELECT *
FROM product
LEFT JOIN product_client ON product.ID = product_client.product_id
WHERE product_client.client_id =1

Everything works fine, returns me the products exclusively for this client. But what I want is more - the products table is like a dictionary where on top of that client has his own products. So I need to get the whole products table, plus the query output above.

Any tips? Is it a good idea to have this set up this way?

Consider a case where:

In the database we have ids 1.2.3.4.5
Product ids 1.2 are assigned to client 1
The product ids 3.5 are assigned to client 2.

We want to query for client 1. So we expect to get the following ids: 1.2.5,  since 3 belong to clients 2, and number 4 is a global product (unassigned). Any unassigned is a global product.

Products Table

+-----+-------------+-----------+-------+
| ID  |    Name     | Category  | Price |
+-----+-------------+-----------+-------+
|  1  | Paper       | Material  |    10 |
|  2  | Wool        | Material  |     4 |
|  3  | Cleaning    | Service   |    10 |
|  4  | Something   | Service   |     1 |
|  5  | Something2  | Service   |     2 |
+-----+-------------+-----------+-------+

Client Table

+-----+---------+-------------------+
| ID  |  Name   | {anyextra fields} |
+-----+---------+-------------------+
|  1  | Client1 |                   |
|  2  | Client2 |                   |
+-----+---------+-------------------+

product_client Table

+-----+-----------------+-----------+
| ID  | product_client  | client_ID |
+-----+-----------------+-----------+
| 1   |              1  |         1 |
| 2   |              2  |         1 |
| 3   |              3  |         2 |
| 4   |              5  |         1 |
+-----+-----------------+-----------+

Desired output for client_ID = 1

      Query return table
+-----------+----------+-------+
|  Product  | Category | Price |
+-----------+----------+-------+
| Paper     | Material |    10 |
| Something | Service  |     1 |
| Wool      | Material |     4 |
+-----------+----------+-------+

Your "where" clause restricts it to products where it is matching the client products (As you may know, left join returns everything from the left side, with matching entries from the right side or NULL otherwise. But then you filter it for a specific client value) - I would amend your WHERE clause to:

WHERE
    product_client.client_id = 1
    OR product_client.client_id IS NULL

My logic is - you are matching either the specific client, or the values that are "unmatched" to another (e.g. client_id = 2) client. client_id is null where the product doesn't match a specific client.

To determine which one is a global product and which one is a client's only, add to the select statement:

CASE
    WHEN product_client.client_id IS NULL
        THEN 'Global'
    ELSE 'Client'
END

For MySQL/PHP add ( ) around the statement.

You could also ISNULL(client_id,"Global") if wanting to take the specific client if it's specified, or otherwise 'global'.


Connecting to Active Active Cluster Services I have a two node active active cluster setup as below:

NODE 1
  - SQLINST1 ( with DTC1 )

NODE 2
  - SQLINST2 ( with DTC2 )

I wish to now have the application connect to SQLINST2 whenever SQLINST1 fails. Is it possible in SQL server 2012.

Once the SQLINST1 comes backup the connections should reconnect back to SQLINST1

First off, there is no such thing as an "active/active" cluster.  That is a huge misnomer, and can lead to a large amount of confusion.  What you have is two failover cluster instances in a Windows failover cluster with at least two nodes.

Secondly, this will not be possible if you are dealing two failover cluster instances.

  I wish to now have the application connect to SQLINST2 whenever SQLINST1 fails

Which nodes are the possible owners of the cluster group SQLINST1?  I'm guessing both nodes would be able to own the FCI (failover cluster instance)?  If so, it'd be a rather corner-case where both either node is still up, with SQLINST1 down but SQLINST2 is still alive and well (it could happen, just would be a typical failure).

Something you could consider, seeing as you are using SQL Server 2012 is AlwaysOn Availability Groups.  What you could use here (provided that the rest of the functional capabilities meet your implementation requirements:  AGs are a very robust technology) is an availability group listener to act as the client abstraction (it's just a virtual network name within the AG cluster resource group of the WSFC).

It does sound as though you want/need automatic failover in this case.  Unfortunately, automatic failover between a FCI replica and another node isn't currently supported, do you'd need to revert to standalone instances for your AG replicas.  Not necessarily a bad thing, but surely worth thinking about in the initial stages of infrastructure design.


SQL Server 2016 SP2 - ONLINE REBUILD DEADLOCKed Itself - is this a bug? ALTER INDEX [PK_Order_Line_Inventory]
ON [OrderManager_C].[Order_Line_Inventory] REBUILD
WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=SELF) )

This was satisfied by 3 threads within SPID 58.  However, arguing over the same data page, one of the threads became the DEADLOCK victim.  Process IDs are different, as are the Execution Context IDs and the Txn properties.  There are actually two DEADLOCKs for this same command, one for the 3 threads, the 2nd for the surviving 2 that then became one.

The SQL 2017 Guidelines for Online Index Operations has this paragraph, "Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.", but as the deadlock involved "itself", my root cause wasn't found.

The table has just a single-column, bigint, Clustered PK, and contains 2.7M rows in 520MB; one FK to its parent in a 1::0/1 relationship.

Questions arise:

Is this a bug?  I've not seen this behavior in over a decade with SQL Server.
Did any of the REBUILD survive?
Is this likely to recur?
I guess I could enhance our Defrag Tool to allow an option to override MAXDOP=4 to =1 for specific tables, but is that really necessary?

I've hunted for similar occurrences but what looked a promising link from SQLCoffee to MS KB978250 met a 404 error and no hits at all from searching MS (Original thread).  That KB spoke of a fix to SQL2008 - if relevant, one would assume it should still be a fix in the 2016 bits...

Can anyone shed any light on this, please?

Graph: On OneDrive

Sounds like an intra-query parallelism deadlock.

They're generally considered bugs because there's not much you can do about it, other than to rerun the query or drop the maxdop of the statement down (if its happening often for the same query).

Here is some information to help troubleshoot them, hope this helps.

https://support.microsoft.com/en-us/help/4089473/better-intra-query-parallelism-deadlock-troubleshooting-in-sql-server2


Pulling from Two Tables with Different Number of Records I have three tables, one for members with 8 records, one for tasks with 1 record, and one for events with 3 records. The tasks table associates the events with members using one-to-many primary to foreign keys, creating a many-to-many relationship (so the columns are TaskID, MemberID, and EventID in the Tasks table).

I want to pull member data from the members table and the tasks data from the tasks table.  Instead of showing the one Task record with the member name in the adjacent column, MySQL is listing ALL 8 members adjactent to the same Task, but only one of the members is actually associated with that task.  This is what I have tried:

SELECT EventID, CONCAT(Title, " ", LastName) AS Member FROM Events, Members

How do I get MySQL to list only the tasks in the tasks table with the associated member CONCAT from the members table?

Sounds like you just need to do an INNER JOIN. And it also sounds like you don't know what one is? Recommend you read up: http://www.mysqltutorial.org/mysql-inner-join.aspx

In your case it would be a simple:

SELECT EventID, CONCAT(m.Title, " ", m.LastName) AS Member
FROM Events e
INNER JOIN Tasks t ON e.EventID = t.EventID
INNER JOIN Members m ON t.MemberID = m.MemberID
WHERE EventID = <event ID>

Here, I am assuming you wish to get the information for a particular event? If not, remove the WHERE statement.


How does shrinking a SQL Server log file affect performance? I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is no longer available with 2008 and later databases.

I do have a script that truncates the log file:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This truncates the log file completely, but my question is: Does this affect performance?

I perform two Full backups daily so the log should not really be necessary as far as data roll-forward is concerned.

I really recommend reading Importance of proper transaction log size management by Paul S. Randal.

The quintessence is that there are only two really good ways to do transaction log handling:

Either go with regular LOG file backups and the LOG-file will reuse it's space after each LOG backup and won't grow indefinitely, or
Use the SIMPLE recovery model and you don't have to care about your LOG-file size, as you do regular full backups.

What concerns LOG-file truncation and performance is that you will always get a performance hit when the LOG file is to be increased (a quote from the above-linked blog post):

  If you shrink the log, then it's going to grow again - possibly causing VLF fragmentation, and definitely causing your workload to pause while the log grows, as the log can't use instant initialization [...]

Update: Don't mistake LOG file truncation for DATA file shrinking. DATA file shrinking is really bad. See Why you should not shrink your data files for details.


To Normalize or Not to Normalize In general, I understand that normalization is usually beneficial even with the join costs. However, I came up with an interesting dilemma recently.

What if the data is duplicated but unlikely to ever change. It's possible, but I would not anticipate it.

I have a nutrients table with a unit column and the units would be g, kg, ug, etc.

I can't see these values every changing.

I'm tempted to just put them as a column in the table rather than normalizing and having a units table and using a foreign key and having to join whenever fetching a row from the nutrients table. At the same time, I know in general, even with the join coins, we should normalize.

What should I do (and why)?

There is a difference between redundant data (bad) and coincidentally repeated data (not bad).  Normalization is a technique which is used to avoid insert, update and delete anomalies.  It is not meant to eliminate every repetition of a piece of data.  Data which is static doesn't benefit from normalization.

A unit of measure, stated as a standardized abbreviation is not the kind of data that you need to normalize out.

Think of it this way:  If you were to normalize out your unit of measure into a separate table, you'd need a foreign key from your nutrients table to your units table.  Is the unit of measure code going to be unique (probably, yes).  Therefore it's a candidate key for your units table.  If it's a candidate key in units you could use it as a foreign key in nutrients.

The end result is that you have your unit of measure code in your nutrients table anyway even if you've normalized out the units.

Here's when you would want to create a units table.  If you have other predicates (columns) that are dependent on the unit code, but not on the nutrient.  For example, a unit_type or a conversion factor to a base unit of the same type (grams for weight, etc.)  This would be a transitive functional dependency in your nutrients table and doesn't belong there for that reason.


incompatibility of max_allowed_packet in mysql I have a client and I want to dump a row such as :

$ mysqldump -uroot -p pahlevan_azad mp_posts --where="id='655'"  --max-allowed-packet=1024 > dump.sql
Warning: option 'max_allowed_packet': unsigned value 1024 adjusted to 4096
Enter password:

Because I have the following value in server :

SHOW VARIABLES LIKE 'max_allowed_packet';
max_allowed_packet  1024

And I don't have any permission to change it.I only have a phpmyadmin.

What do you recommend?
How can I set max_allowed_packet such as server?

Update:
even I tested with 1K, But I got the above error.

16777216 is a reasonable value; 1024 is not.  The error message is just a warning, saying that 1024 is below the minimum allowed value.

Try the mysqldump with 16777216.


What is the point of encryption at rest? I am wondering what is the point of encryption at rest?
It is said that a hacker cannot steal the database and look through it. I'm afraid that it does not protect the database while the key is stored on the same machine.
Let's have a look at my MariaDB settings:
[mysqld]
# at-rest encryption
plugin_load_add              = file_key_management
file_key_management_filename = /etc/mysql/rest/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC

If keyfile.passwd and keyfile.enc are on the same machine, the hackers will steal all of them together and decrypt keyfile.enc by keyfile.passwd and then they will obtain the key in a plain text and via the key, they decrypt the encrypted database.
Where will be the protection?

The file_key_management is largely a testing plugin. Its there to show the general operation and be there as self tests. Its also there as a simple reference implementation if you want to write your own.
You are right about every weakness described.
There may be container based applications where the storage of keys used by the file_key_management plugin are only brought together when the MariaDB pod is instigated. With this there may be some risk vectors mitigated.
There are a number of other encryption implementations using the same server API that offer greater protection.


SQLite disk I/O error (3850) I can't seem to figure out what is causing the I/O Error:

$sqlite3 db.sqlite < data.sql
(3850) disk I/O error
Error: near line 2: disk I/O error
(3850) disk I/O error
Error: near line 3: disk I/O error
(3850) disk I/O error
Error: near line 5: disk I/O error
(3850) disk I/O error
Error: near line 8: disk I/O error
(3850) disk I/O error
Error: near line 9: disk I/O error

See the contents of data.sql below

$ less data.sql
.log stderr
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" (
  "product_id" varchar(36) NOT NULL
);
INSERT INTO "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" VALUES ('C6E07AA2-5B01-11E4-9116-00166E0083A1');
INSERT INTO "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" VALUES ('C6E07AA2-5B01-11E4-9116-00166E0083AA');
END TRANSACTION;

I already saw this issue: disk I/O error in SQLite
but my issue generates a different error code after I inserted .log stderr at the top of the data.sql file and I am not using Virtualbox - I am not sure if the host is using another form of virtualisation.

So our sysadmin just told me:
"we use VMWare virtualization, and the system is fully optimized as a VM (virtio, etc.).You are trying to do the read and the write to an NFS mounted disk (my home directory)"
I tried the command in /tmp and it worked there.


Proper storage size estimation technique for PostgreSQL We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we are doing research, reading manuals and using our general information technology knowledge to achieve this.

We have actual data to migrate to this database and have some rough estimations of growth. For the sake of the example, let's say we have a estimation of growth of 50% per year.

The point is: what's the general proper technique for doing a good size estimation?

We are estimating the storage usage by the following rules. Topics where we need advice are marked with bold text. Feedback on the whole process is more than welcome:

Estimate the size of each table

Discover the actual size of each row.

For fields with a fixed size (like bigint, char, etc) we used the sizes described in the documentation
For fields with a dynamic size (like text) we estimated the string length and used the function select pg_column_size('expected text here'::text)
We added 4 more bytes for the OID that PostgreSQL uses internally

Multiply the size of each row by the number of estimated rows
Do I need to consider any overhead here, like row or table metadata?

Estimate the size of each table index

Don't know how to estimate this, need advice here

Estimate the size of the transaction log

Don't know how to estimate this, need advice here

Estimate the size of the backups (full and incremental)

Don't know how to estimate this, need advice here

Sum all the estimates for the actual minimum size
Apply a factor of 1.5x (the 50% growth) to the sum of the estimates 1, 2 and 4 for the minimum size after 1 year
Apply an overall factor of 1.2 ~ 1.4 (20% to 40% more) to estimates 5 and 6 for a good safety margin

I know the rules got pretty extensive. Let me know if examples are necessary for a better understanding.

For point 1), you need to read the Storage Page Layout chapter of the documentation and in particular consider the HeapTupleHeaderData Layout table for the metadata at the row level.

The 4-bytes per-row OID is obsolete for user tables. PostgreSQL no longer have them by default since 8.1. This is now controlled by the default_with_oids config parameter or the WITH(OIDS) clause of CREATE TABLE.

When a significant part of the data is live (gets frequently updated), estimating the disk size is harder because UPDATEs are equivalent to INSERT new version followed by DELETE old version, followed hopefully by reuse of the unused space whenever possible. There is also the fillfactor storage option for tables that come into play here (see the storage parameters section in CREATE TABLE)

And there is bloat at the index level, too. For write-heavy tables, it's not uncommon for an index to be several times bigger than its optimal size.

You may need to learn a bit about VACUUM to figure out how much you may be affected by table and index bloat in your specific usage.

The size of the transaction logs entirely depends on the amount of writes to the database.


PostgreSQL - duplicate data show and count This is table.

CREATE TABLE cameras
(
  id integer NOT NULL DEFAULT nextval('sq_streams'::regclass),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  exid text NOT NULL,
  owner_id integer NOT NULL,
  is_public boolean NOT NULL,
  config json NOT NULL,
  name text NOT NULL,
  last_polled_at timestamp with time zone DEFAULT now(),
  is_online boolean,
  timezone text,
  last_online_at timestamp with time zone DEFAULT now(),
  location geography(Point,4326),
  mac_address macaddr,
  model_id integer,
  discoverable boolean NOT NULL DEFAULT false,
  preview bytea,
  thumbnail_url text,
  CONSTRAINT pk_streams PRIMARY KEY (id)
)

that config object is of type json and containing json data inside it as well.

i have 5000 records in this. What i am trying to get is ,
There are 57 Cameras and they have everything different but 3 things are same. which are

'external_http_port', config->> 'external_host', config-> 'snapshots'->> 'jpg'

I want to create a query which will show me results as

external_http_port | external_host | config-> 'snapshots'->> 'jpg' | Count
"93.87.73.118"     | "1024"        |      "/oneshotimage.jpg"      | 57
"180.180.180.48"   |  "81"         |"/onvif/media_service/snapshot"|20

I think you just want a simple group by query, using the 3 expressions that get data out of the json column:

select
    config->>'external_http_port' as external_http_port,
    config->>'external_host'      as external_host,
    config->'snapshots'->>'jpg'   as jpg,
    count(*)                      as count
from
    cameras
group by
    config->>'external_http_port',
    config->>'external_host',
    config->'snapshots'->>'jpg'
-- having                              -- to show only those
--     count(*) > 1                    -- with count > 1
  ;

Tested in SQLfiddle.


SQL Server 2008 encrypted connection using multiple server names I have a SQL Server that must be accessed using various DNS names (SQL1, sql1.contoso.local, sql1.contoso.com, etc).

I'm struggling with creating a correct SSL certificate that can be used to secure all these names. Applications using Windows Integrated security are connecting to the server using it's local address, because from the Internet only the SQL Logins are enabled.

I have created SSL certificate with Subject (CN) containing sql1.contoso.com and with several SAN entries: DNS=SQL1, DNS=sql1.contoso.local, etc.

Using this certificate I can connect using encrypted connection only when I use the server sql1.contoso.com. Using other names will result in this error message in SQL Server Management Studio:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)

I'm connecting to the server from a computer that trusts the CA that issued the SSL certificate used on the SQL Server and all the firewalls are correctly opened to allow connections using the 1433 port. The client also trusts the CA. The certificate was generated using Web Server template.

Is it possible to configure by SQL Server so I can connect to it using several domain names of the server using an encrypted connection?

I can only assign one certificate to a single SQL Server Instance, so I have to use one certificate for each accessible server name. If I could assign different certificates for the local connections I can use proper certificate signed by trusted CA instead of self-signed certificate.

I tried to generate a new certificate with multiple CN entries but I wasn't able to connect to the server using any of the defined names in CN entries.

I'm pretty sure that you'll need a certificate which has multiple CNs not one with DNS entries.

  The certificate was generated using Web Server template.

I don't think that's the correct template.  I don't have access to a CA at the moment, but I don't think that template uses the correct server authentication certificate. The note in my book says:

    The certificate must be a server authentication certificate that requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1). The certificate must also be created using the KeySpec option of AT_KEYEXCHANGE; optionally the key usage property will include key encipherment.


mysql trigger to insert/update date column from datetime column probably simple question but can't get it to work:

I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them.

Now, i would like to, whenever a row is inserted or updated, to extract the date part from the datetime columns, and insert or update the date columns.

Not very well versed with triggers, so pointers would be much appreciated.

server version: 5.1.49-3

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key,
     `dt1` datetime,
     `dt2` datetime,
     `d1`  date,
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1),
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1),
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo


Oracle using sub-optimal index on bind variables Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields).  And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third index on ORDER_DATE.

The following query is intended to select all orders that are older than the current order, from the same customer:

select ORDER_NUMBER from ORDERS
    where CUSTOMER_ID = :BIND_VAIABLE_1
    and ORDER_DATE <= :BIND_VARIABLE_2
    and ORDER_NUMBER != :BIND_VARIABLE_3

In this case, :BIND_VARIABLE_2 would be a recent date, usually today's date, from a current order.  The problem is, that Oracle seems to want to use the index on ORDER_DATE, which selects almost the entire table, then filters on CUSTOMER_ID,  whereas it is much more efficient to use the index on CUSTOMER_ID instead (since there are only a few orders per customer).

The thing is, if you do a query without bind variables, it will always use CUSTOMER_ID like it should.  But with bind variables, Oracle creates a query plan assuming that ORDER_DATE will be some random value, so ends up using the less efficient index.

How can I get Oracle to use the correct index in the presence of bind variables, without using index hints (since I can't modify the app)?  I've already tried re-computing statistics, using histograms, etc. but without much luck.  Ideally I'd like to turn off bind-variable optimization altogether, and have Oracle re-formulate a query plan based on the actual contents of the bind variables.

Edit: the purpose of this query is to find all previous orders by this particular customer, so BIND_VARIALBE_1 is the current customer, BIND_VARIABLE_2 would be the date of the current order, and BIND_VARIABLE_3 is the current order number.

I would create a composite index on (CUSTOMER_ID, ORDER_DATE) and that should fix all your issues, assuming ORDER_DATE is a DATE data type.  And assuming ORDER_DATE should not be null, ensure the table definition says NOT NULL.  I am certain Oracle is not using "some random value" if you gathered statistics correctly.  You tagged your question with both 11g and 12c; you running different versions on different instances?

Is your bind variable the correct data type for the ORDER_DATE?  If this is a vendor application, you may need to create a function-based index on the ORDER_DATE column to produce a reasonable date that matches the format of the bind variable.


Are implicit joins as efficient as explicit joins in Postgres? It's often convenient to write:

SELECT *
FROM t1   # ... +many more tables
INNER JOIN t2 ON (t1.id = t2.col)
INNER JOIN t3 ON (t1.id = t3.col)
INNER JOIN t4 ON (t1.id = t4.col)
...

as a cross join with conditionals:

SELECT *
FROM t1, t2, t3, t4   # ... +many more tables
WHERE
       t1.id = t2.col
   AND t1.id = t3.col
   AND t1.id = t4.col
   # +include matches on columns of other tables

However, a naive implementation of a cross join would have a higher time complexity than the inner join. Does Postgres optimize the second query into one with the same time complexity as the first?

I would use explicit JOIN syntax, but reduce the noise:
SELECT *  -- really? *all* columns?
FROM   t1
JOIN   t2 ON t1.id = t2.col
JOIN   t3 ON t1.id = t3.col
JOIN   t4 ON t1.id = t4.col
-- ... more tables
WHERE ...

Place conditions linking two tables in the JOIN clause.
Place other conditions in the WHERE clause.
INNER is a noise word. Parentheses are not required around join conditions.
To answer your question:

Are implicit joins as efficient as explicit joins in Postgres?

Yes. There is no difference in efficiency. Postgres is typically free to rearrange the order of join operations and apply JOIN and WHERE conditions in any order it sees fit. The manual:

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN)
is semantically the same as listing the input relations in FROM, so it
does not constrain the join order.

There are special considerations for OUTER joins that do not apply here.
And be aware of some limitations:

Explicit joins bind stronger than implicit joins. This can have side effects when mixing both - which you should avoid unless you understand the implications. See:

Why does this implicit join get planned differently than an explicit join?

What does [FROM x, y] mean in Postgres?

With more than join_collapse_limit items in the FROM list (default is currently 8), Postgres switches from an exhaustive search for the best query plan to generic methods. Hence the order of explicit join items becomes yet more important - or even instructive.

Since you mentioned:

+many more tables

... you probably need to consider how to optimize planning time and query execution time by carefully choosing the order of explicit joins and placement of conditions. You may want to use subqueries, CTEs, mix explicit and implicit joins, use parentheses among groups of FROM clause items or play with configuration settings to get best results.
Related:

Postgres JOIN conditions vs WHERE conditions
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT


Effectively searching through entire 1 level nested JSONB Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level).

How does one effectively optimize a query to search entire JSONB column for each value?

Is there some good alternative to doing ILIKE %val% on jsonb datatype casted to text?

jsonb_each_text(jsonb_column) ILIKE '%val%'

As an example consider this data:

SELECT '{"col1": "somevalue", "col2": 5.5, "col3": 2016-01-01, "col4": "othervalue", "col5": "yet_another_value"}'::JSONB

How would you go about optimizing a query like that when in need to search for pattern %val% in records containing different keys configuration in a column? Is there a better alternative to extracting each key-value pair to text and performing an ILIKE/POSIX search?

Mainly, I'm looking for a different alternative to unpacking a whole jsonb field into separate key rows with their values as text.

For pattern-matching, there is currently (pg 10) no better way with standard operators and indexes than to unnest values and test each individually. At least, use an efficient EXISTS query:

SELECT *
FROM   tbl
WHERE  EXISTS (SELECT FROM jsonb_each_text(jsonb_column) WHERE value ~* 'val');

A specialized solution for this particular use case: concatenate all string values per row with an IMMUTABLE function and create a trigram GIN index on the expression. You need the additional module pg_trgm installed. Read this first if you are not familiar:

How is LIKE implemented?

Function:

CREATE OR REPLACE FUNCTION f_all_value_string(jsonb)
  RETURNS text AS
$func$
SELECT string_agg(value, '|') FROM jsonb_each_text($1)
$func$  LANGUAGE sql IMMUTABLE;

Chose a separator that never interferes with your patterns - '|' in the example.

Index:

CREATE INDEX tbl_all_value_string_trigram_idx ON tbl
USING GIN (f_all_value_string(jsonb_column) gin_trgm_ops);

Query (matching the index):

SELECT * FROM tbl WHERE f_all_value_string(jsonb_column) ~* 'val';

Related:

Search for nested values in jsonb array with greater operator
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server? i have a bach file it contain:

@echo off
sqlplus x/y@z @run.sql

for execute this code correctly i have to execute this file from administrator user from my client, for some reason i need to run this file from a standard user without administrator privilege and without any password request.

when i research about my problem, i understood sqlplus can't be run without administrator privilege.

How to connect to SQL*plus from standard user in Windows Vista to Oracle10g on my server?

I didn't find how to run sqlplus from standard user but JJ. answer in this question help me to find alternative way.

I just use PsExec to run sqlplus in my server.


How can I check if a user have a quota on tablespace My objective is to check users quotas on tablespace:

Unfortunately the table dba_ts_quotas gives the details of already used space on tablespaces.

I want to see all users who have quotas on tbs even they don't use any space on the tablespace.

is there solution to check if a user has quota on a tbs?

DBA_TS_QUOTAS also contains information on the assigned quota, even if the user did not create any tables.

From the manual

  MAX_BYTES - User's quota in bytes, or -1 if no limit

Example:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 10:35:29 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

no rows selected

SQL> create user foobar
  2    identified by welcome
  3    default tablespace users
  4    quota 10M on users;

User created.

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

TABLE USERNA      BYTES  MAX_BYTES
----- ------ ---------- ----------
USERS FOOBAR          0   10485760

1 row selected.

SQL>


MariaDB 10.0.25 installs without configuration While trying to install MariaDB on Ubuntu 15.10, I issue command

$ sudo apt-get install mariadb-server

and it outputs

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libdbd-mysql-perl libmysqlclient18 libterm-readkey-perl mariadb-client-10.0
  mariadb-client-core-10.0 mariadb-common mariadb-server-10.0
  mariadb-server-core-10.0 mysql-common
Suggested packages:
  mailx mariadb-test tinyca
The following NEW packages will be installed:
  libdbd-mysql-perl libmysqlclient18 libterm-readkey-perl mariadb-client-10.0
  mariadb-client-core-10.0 mariadb-common mariadb-server mariadb-server-10.0
  mariadb-server-core-10.0 mysql-common
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/11.6 MB of archives.
After this operation, 122 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

At the end the installation of packages are completed and the service mysql get installed. But, I never get a prompt/wizard for setting root credentials and other configuration. And later I'm not able to connect to the service

$ mysql -u -root -p
Enter password:
ERROR 1698 (28000): Access denied for user '-root'@'localhost'

Not able to understand what is going wrong.

UPDATE
What's more intriguing is the following

$ sudo mysqladmin -u root password <myPassword>
$ mysql -u root -p
Enter password: <myPassword>
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I'm completely puzzled.

It appears You had some MySQL/MariaDB/Percona instance previously installed, and some of data and config files were left in Your OS.

The way that worked for me on that case (Ubuntu Xenial 16.04 LTS with MariaDB 10.0 installed), with forgotten Maria's root password, was:

purging everything: sudo apt-get purge mariadb*
reboot; it didn't work without it, probably some related service running in background, locking some files from being purged: sudo reboot -h now
installing database once again: sudo apt-get install mariadb-server

Of course this was possible only on private dev machine, without any important data.


How to move 200 materialized views without having to re-create in the new home I am moving a database with 200 materialized views into a new server.  All of them are 'fast refresh' materialized views , means they have corresponding materialized view logs, in this case sitting on another database.

I have spent countless hours tuning my 200 materialized views and would hate to re-create them in the new server (database).

Is there a way to move them without having to re-create them ?

If you are moving the database (backup on old server and restore on new server) the MVs will come along.

Are you creating an empty database on the new server? If so you could use Data Pump to export the MVs from the old and import them into the new.

I would opt for using a full RMAN (or filesystem) backup and restore over Data Pump.


Multiple BIT datatype columns in the same table Consider the following information coming from Microsoft SQL docs pages:

  The SQL Server Database Engine optimizes storage of bit columns. If
  there are 8 or fewer bit columns in a table, the columns are stored as
  1 byte. If there are from 9 up to 16 bit columns, the columns are
  stored as 2 bytes, and so on.

See: https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017

So, if I create a new table like this:

CREATE TABLE MyTable (
    Id int PRIMARY KEY,
    Value1 bit,
    Value2 bit,
    Value3 bit,
    Value4 bit,
    Value5 bit,
    Value6 bit,
    Value7 bit,
    Value8 bit)

...then all the bit columns (Value1, Value2, ... Value8) in one row should take up one byte. Or?

When we take a closer look at it, Value1 field of a row can have values 0, 1 or NULL inside. This means that field is actually of a 3-state value, or a ternary value. 2 such values can represent dictionary of 3*3=9 values. 8 such values are a dictionary of 6561 values.

If we're thinking about the memory footprint of such a row, 8 bits should take up one byte. This means that this one byte encodes 6561 values instead of 256 values. This is obviously wrong.

So, either MS SQL docs are misleading or we're talking about some quantum computing here. Of course it's the former, but what I'd really want to know is the answer to this question: how are nullable bits (and of course that SQL Server bits are nullable by default) really stored in SQL Server underlying structures?

For a fixed length field (such as a bit value), the field will always take up the same amount of space - in your case one bit.
The NULL bitmap is in the data row itself - this stored whether a particular field is considered NULL or not. As such the field itself only has to store two values - 1 or 0 - the NULL bitmap handles whether the field is actually set to NULL or not.
The NULL bitmap has a bit for each column in the table regardless of whether the column is nullable, and this contains  the "third value" you mention. Again the space for the null bitmap is rounded up to an integer number of bytes.
The following code demonstrates how SQL Server stores the NULL bitmap - you'll need to replace the page numbers if you run this yourself.
CREATE TABLE TestNullBitmap (
    TestText CHAR(10) NULL
    )
GO

INSERT INTO [dbo].[TestNullBitmap] ([TestText])
VALUES
('hello'),
('hello2'),
(NULL);

--Grab the page number
DBCC IND ([TestDb],TestNullBitmap,-1);
GO

--Use the page number from DBCC IND
DBCC PAGE ([TestDb],1,631032,3) WITH TABLERESULTS;
GO

For me I get the following results for the three data rows:

0000000000000000:   10000e00 68656c6c 6f202020 20200100 00        ....hello     ...
0000000000000000:   10000e00 68656c6c 6f322020 20200100 00        ....hello2    ...
0000000000000000:   10000e00 68656c6c 6f322020 20200100 01        ....hello2    ...

The NULL bitmap is the change to that very last byte value - read in Little Endian the binary representation is 0000 0001 (IE The first column is NULL)
Rather interestingly you can also see that the system has stored the value from the previous INSERT as the actual data for the fixed length field - rather than 0'ing it out.


MariaDB writes at 500kb/s how to get it to write at normal speeds? My ubuntu 18.04 has mariadb. I've tried mariadb 10.1, 10.2 and 10.3 all with the same symptons
When I time the harddrive, it has reasonable speeds

$ sudo hdparm -Tt /dev/sda
/dev/sda:
Timing cached reads:   33470 MB in  1.99 seconds = 16840.64 MB/sec
Timing buffered disk reads: 354 MB in  3.03 seconds = 116.92 MB/sec

write:

$ dd if=/dev/zero of=/tmp/output conv=fdatasync bs=384k count=1k; rm -f /tmp/output
1024+0 records in
1024+0 records out
402653184 bytes (403 MB, 384 MiB) copied, 2.4399 s, 165 MB/s

But when I check the actual thouroughput of mariadb it only has 500kb/s which is ridiculously slow. On my windows box it runs at 60mb/s and it both has mariadb 10.2
 10012 be/4 mysql       0.00 B/s  502.34 K/s  0.00 %  2.50 % mysqld
   563 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.44 % [ext4lazyinit]
 10009 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.09 % mysqld
     1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
     2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
     4 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0H]
     6 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [mm_percpu_wq]

I've tried various iterations of these settings, but all end up with really slow throughput/no noticable change
innodb_file_per_table=1
innodb_buffer_pool_size=8G
thread_cache_size=10
skip-name-resolve
query_cache_type=1
query_cache_limit=256k
query_cache_min_res_unit=2k
query_cache_size=80M
tmp_table_size=256M
max_heap_table_size=256M

I set the sysctl -w vm.swappiness=0 without any effect.
I'm doing transactions of 5000 rows that get inserted. When I run the exact same code at my workstation it pops each query off per second. ON the remote system it takes roughly 10 minutes for one batch to complete.
I've tried every trick I could find, tried various versions, and nothing really helped.
Does anyone have an explanation why mariadb only runs at 500kb/s?
It's a fresh system, bought today, freshly installed with ubuntu today.
I had a similar issue with an SQLITE database, where the database would wait for the disk to spin around, release, wait, etc... I could disable the waiting on disk there.
Is there a similar setting in MariaDB? to write to disk buffer, where I would accept the risk of powerloss, isntead of waiting on a hardware lock?

query_cache_type=1
query_cache_size=80M

For every write, it must scan the 80M for any references to the table being written to, and purge those entries.  This is an example of where the Query cache hurts performance, and should be completely disabled for production servers.

innodb_flush_log_at_trx_commit=1

cause a disk flush at every "transaction".  If you are using autocommit=ON and writing one row per SQL statement, then this is a huge overhead.  Some workarounds:

BEGIN;
write lots of rows
COMMIT;

and/or

INSERT ... VALUES (...), (...), ... -- Writing lots of rows in a single SQL.

and/or

Switch to SSDs

500KB/s is not a useful metric -- Are they tiny rows or huge rows?  100 rows per second is a typical speed for HDD drives with all the worst settings (innodb_flatc=1, single-row transactions, etc).  Adding any one of the above tips, you might get to 1000 rows/sec.  Going beyond that is possible, but more challenging.

As for local versus remote...  There could be differences in the settings.  There could be significant latency.  A distance of 120ms (a few thousand miles or kilometers) would explain the 10 minutes.

My high speed ingestion blog may be useful.


View SQL's errorlog in Amazon RDS Is there a way to view the SQL Server errorlogs in Amazon's RDS?  I usually use sp_ReadErrorlog, but in RDS we need to be a member of securityadmin role.

Per the documentation, there's a custom stored procedure that does just that:

EXEC rdsadmin.dbo.rds_read_error_log [index]


mongodb inMemory engine available in the community edition? I'm currently working with MongoDB 3.2.10 and I recently heard of the new "inMemory" engine. After some research I found that it was available in the Enterprise Edition since version 3.2.6, but I couldn't find any information on an eventual release in the community edition.

So the question is: do they plan to release the inMemory engine for the Community Edition? If they do, do we know when?

As at MongoDB 3.4, no plans have been announced to make the In Memory Storage Engine available in the MongoDB Community Edition. I suspect this is likely to remain as an Enterprise-only feature for the foreseeable future given the more niche use cases for in-memory storage, however the definitive source will be the mongodb.com website and any future major release announcements.

FYI, you can subscribe to driver/server release announcements via the mongodb-announce group.


Domain group hierarchical group membership and SQL Logins mapped to AD groups SQL Version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
    Nov 24 2008 16:17:31
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

This is the AD group structure:

Group A

User 4
Group B

User 1
User 2
User 3

I granted Group A login access to my SQL instance (connect and dataReader/writer to Northwind db).

I'd expect to be able to login to sql with User 1-4 but in fact only User 4 is allowed to login. If i grant Group B access then all users can login as expected.

So I'd venture to answer my own question and say "NO - sql only respects the 1st-level members of an AD sec group" but I wanted confirmation from someone else.

Logs show state 11 login failure which indicates "Valid login but server access failure."

This answer may not be of any real value since now the question becomes WHY is there a lag...

but the AD changes were done 3 days ago and in my particular case, it took 3 days before SQL Server started recognizing the new members/groups added.

As of last night, when i executed EXEC xp_logininfo 'DOMAIN\User1','all' it returned nothing.. today (4th day since change) it shows the permission path as 'DOMAIN\GroupA'.


Error running DB2MOVE command I'm trying to execute the DB2MOVE command at a db2 prompt console in a windows 7 machine (I have db2 client installed, with proper catalogs and nodes created and tested.).

The steps I made was:

Open the console (cmd)
Run the db2cmd command (it is on path) and it opens a new console window
Run the db2 tool which puts me into the db2 console window
Connect to a existent database: connect to mydb user x using x
Try to run the db2move command

db2move MYDB COPY -sn MYSCHEMA -tf C:/Users/MYUSER/Desktop/exp.lst -co
  TARGET_DB TESTDB USER user USING pass SCHEMA_MAP ((MYSCHEMA, MYSCHEMA_TEST)) PARALLEL 2

ps.: there is no enter on the command line

The error I'm getting is:

  DB21034E  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command.  During SQL processing it returned:
  SQL0104N  An unexpected token "db2move MYDB COPY -sn" was found following
  "BEGIN-OF-STATEMENT".  Expected tokens may include:  "".
  SQLSTATE=42601
  db2 =>

I'm strictly following the rules on the docs what am I missing?

In order to get the DB2 command window (DB2 CLP) in Windows, you can either Windows key + R and type db2cmd or Start > All Programs > IBM DB2 DB2COPY1 > DB2 Command Line Processor and then you can execute different db2 commands, like.

db2ilist    --> List instances
db2level    --> DB2 Version
db2licm -A  --> DB2 License

etc.

Regards,


How to get the top 20 percent records with a single query? SELECT start_coins
FROM   coins
ORDER BY  start_coins DESC LIMIT 10; /* till here the query works*/
CAST((COUNT(start_coins) * 0.2) AS INT);

I want the number returning from the count to be used as the LIMIT instead of 10. My database version is 10.1.32-MariaDB.

The LIMIT clause parameters are always literals. So You cannot use the value calculated by some statement in it.

The solution can be in prepared statement use.

Variant 1:

SET @sql := CONCAT( 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT '
                  , (SELECT ROUND(COUNT(*) * 0.2) FROM coins));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

Variant 2:

SELECT COUNT(*) * 0.2 INTO @cnt FROM coins;
PREPARE stmt FROM 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT ?;';
EXECUTE stmt USING @cnt;
DROP PREPARE stmt;

In both variants You MUST to execute all 4 statements in the same connection to database. The best vairant is the connector You use allows multi-statements (sending all statements as 1 command).


how to set a row's value from a certain row's value? I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values.

id   tstamp                   amount   balance
----------------------------------------------
 1   2013-03-18 00:00:10       57.00      0.00
 2   2013-03-18 00:00:11       13.05      0.00
 3   2013-03-18 00:00:12      110.00      0.00
 4   2013-03-18 00:00:13       23.50      0.00
 5   2013-03-18 00:00:14       35.44      0.00
 6   2013-03-18 00:00:15       76.00      0.00
 7   2013-03-18 00:00:16       34.74      0.00
 8   2013-03-18 00:00:17      120.47      0.00
 9   2013-03-18 00:00:18       35.00      0.00
10   2013-03-18 00:00:09       46.00      0.00

so balance fields' values must be like that:
current row's balance = CHRONOLOGICALLY previous row's balance + current row's amount.

Notice last row's tstamp value is smaller than first row's tstamp value. so when I say previous row I do not mean current id minus 1. So highest balance value must be at row #9.

And the problem is how to update all balances with chronogically previous row's balance value + current row's amount value?

Assuming that the tstamp has a UNIQUE constraint:

UPDATE activities AS a
  JOIN
  ( SELECT cur.tstamp,
           SUM(prev.amount) AS balance
    FROM activities AS cur
      JOIN activities AS prev
        ON prev.tstamp <= cur.tstamp
    GROUP BY cur.tstamp
  ) AS p
  ON p.tstamp = a.tstamp
SET a.balance = p.balance ;

Tested: SQL-Fiddle

MySQL has also a feature to use ORDER BY with an UPDATE, which you can combine with the use of variables:

SET @b := 0 ;
UPDATE activities
SET balance = (@b := amount + @b)
ORDER BY tstamp ;

Tested: SQL-Fiddle


mysql - merge two large instances to one server (40GB of binaries) I'm need to merge two mysql instances (both mysql V5.6) onto one server.

The binaries of 12 databases are approx. 40GB.

I read other posts about mysqldump but as I've previously had many issues dumping and importing multi GByte dumps, I don't dare try this method again.

Can anyone offer some starting points or from experience working with this size of data, some instructions.

Many internet posts I read are on <2GB website databases and point to mysqldump.

Many thanks.

Chris (DBA in training)

The right answer depend from - what is Your full task description?

is it 12 different databases, which just need to have on same
server?
is it the versions of same databases and You need merge data    in
each table?

in first - You still can use mysqldump, and it will be fasted method without additional steps and/or packages

if size of single dump is big - make dump by single database, table

user#> mysqldump db_name table_name > table_name.sql

Single huge file now must not be a problem with most of OS, but could be difficult for edit.

In second case, need analyse merging logic
simples way - mysqldump with --no-create-info (not CREATE TABLE), but could be problems with duplicate keys.

If some duplicate keys (indexes):

or edit dump (for big files - use sed) and change insert to INSERT IGNORE or UPDATE ON DUPLICATE

or create ETL Job with any of available package and manage merge logic.


Two arbiters in mongodb architecture I am distributing several instances of mongoDB between two datacenters (DC). I want to have high availability (HA), which means that three instances must always be active if one of DC goes down.

My decision has been:

DC1:

Primary
Secondary
Arbiter

DC2:

Secondary
Hiden
Arbiter

Can I choose 2 arbiters for HA? Or it is a mistake?

Your suggested configuration still allows a split-brain situation to occur. If the link between the two datacentres goes down, both arbiters will nominate their local nodes as the primary. The two DCs will continue independently, and will not be able to reform the cluster when the connection is re-established.

The ideal configuration has a single arbiter in a 3rd location. We put ours in the cloud. It's a really good use-case for that.


How the get the number bigger than the average number in Postgresql? I have three tables in my db which were created via the following declarations:

create table employee (
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date);

create table task (
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer references employee (id) on cascade update,
cat_id integer references category (id) on cascade update);

create table category (
id serial primary key,
name varchar (30) not null );

How ca I get the employee.id of employees who have the amount of tasks assigned to them more than the average amount of tasks assigned to employees?

First, I've reduced your tables by deleting FOREIGN KEYS just for the sake of the question.

create table task
(
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer,
cat_id integer
);

create table employee
(
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date
);

insert into employee values
(1, 'Emp1', 'SEmp1', now()),
(2, 'Emp2', 'SEmp2', now()),
(3, 'Emp3', 'SEmp3', now());

insert into task values
(1, 'Task1', now(), 1, 0),
(2, 'Task2', now(), 1, 0),
(3, 'Task3', now(), 1, 0),
(4, 'Task4', now(), 2, 0),
(5, 'Task5', now(), 2, 0),
(6, 'Task6', now(), 3, 0);

I've used this query to calculate AVG():

select avg(tasks) as average
from   (select empl_id, count(*) as tasks from task group by empl_id) t1;

|            average |
| -----------------: |
| 2.0000000000000000 |

Then I've used it to filter employees > AVG().

select   empl_id, count(*) as tasks
from     task
group by empl_id
having   count(*) > (select avg(tasks) as average
                     from   (select   empl_id, count(*) as tasks
                             from     task
                             group by empl_id) t1);

empl_id | tasks
------: | ----:
      1 |     3

dbfiddle here