Update table from a join which returns multiple rows for each result I am trying to merge information contained in 2 different archives but I will try to simplify my problem to its root cause.
I have 2 tables A and B and I want to update a column in A with values from a column of B if the value in B is not null, otherwise I have to preserve the value in A.
A and B are not direclty related but I have the information I need to identify which elements of A are also present in B.
To avoid perform all operations in a single and really complex query I've added a Foreign Key in table B where I store, if present, the key value of the corresponding element in A.
At this point my idea was to perform an update joining the tables:
UPDATE A
JOIN B ON B.A_FK = A.PK
SET A.INFO = IFNULL(B.INFO, A.INFO);

Unfortunately for me this doesn't work in all cases because in table B there can be more rows that refers to a single row of table A. For this scope lets assume that only one of them has a non null value.
Table A
--------------
| PK | value |
--------------
| 1  |   X   |
--------------

Table B
---------------------
| PK | value | A_FK |
---------------------
|  n |   Y   |  1   |
|  m |  null |  1   |
---------------------

In this case the value in table A is not modified and is still X.
I hoped that performing the update process it would have run the update 2 times for my row in A and that my result would have been the following but that's not the case.
Table A
--------------
| PK | value |
--------------
| 1  |   Y   |
--------------

I really would like to avoid resorting to a cursor to iterate over all rows, so can I modify my query to obtain this result?

Combine the rows of B (GROUP BY a_fk); in the process decide whether to keep Y or NULL.
Store the result of that into a temp table.
Then do the original UPDATE, but against the temp table.
So two statements, no looping:
CREATE TEMPORARY TABLE t
    SELECT ...
        GROUP BY a_fk;
UPDATE A JOIN t ON ...
    SET A... = t...;


Where can I get the master.dacpac file for SQL Server 2012? I am using Visual Studio 2012 Professional with SQL Server Data Tools with a SQL Server 2012 standard edition database instance.
I have been unable to locate a master.dacpac file on either my development machine or on the server hosting the SQL Server instance.

How can I obtain this file, so I can get rid of warnings like the following:

SQL71502: Procedure: <ProcedureName> has an unresolved reference to object [sys].[objects]

You should be able to add a Database Reference. Click Project-> Add Database Reference. Click the radio button for System and then master. Code with references to objects in the master db should now build correctly.


Invalid object name after aliasing a table? When I run the following query

select distinct A.movieID, A.avgStars from
(select mID as movieID, avg(stars) as avgStars
from Rating
group by mID) A
where  A.avgStars <= all(select A.avgStars from A);

I get the error "Invalid object name 'A'."

Do you know why I'm getting this?

Thanks!

You can't reference an alias from a subquery at the same scope. You can do this with a CTE, though; perhaps you meant:

;WITH Avgs AS
(
  select mID as movieID, avg(stars) as avgStars
  from Rating group by mID
)
SELECT movieID, avgStars FROM Avgs
WHERE avgStars <= ALL (SELECT AvgStars FROM Avgs);

You shouldn't need DISTINCT there, either.


MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data? I'm trying to estimate my db traffic not including backups (mysql 5.1.41).

I use show global status; to get the parameters bytes_sent and bytes_received.

Questions

Do these numbers include mysqldump (backup) data or not ?
Do they include the log file which is being read by slaves or not?

Thanks,
Niro

Do these numbers include mysqldump (backup) data or not ?

It would include mysqldump for one simple reason: It uses a DB Connection. That DB Connection, when you do SHOW PROCESSLIST;, has a SELECT SQL_NO_CACHE running on a table at any given moment during the mysqldump.

  Do they include the log file which is being read by slaves or not?

In MySQL Replication, you have two threads: IO Thread and SQL Thread. They are, for all intents and purposes, DB Connections as they are counted (included) under the global status variable Threads_created. DB Connections and Threads are really one and the same.

What then gets counted?

Bytes sent : IO Thread requesting binlog entries from the Master
Bytes recevied : IO Thread reading binlogs entries from its Master
Bytes recevied : SQL thread reading its own relay logs


converting varchar to number while passing to IN clause I am really stuck here..

Given below is the miniature of what I am trying to achieve.

set serveroutput on;
declare
rajesh varchar2(20);
x number(10);
begin
select wm_concat(stocks_id) into rajesh from users_stocks where users_id=28800;
select count(*) into x from users_stocks where stocks_id in(rajesh);
dbms_output.put_line(x);
end;
/

Here is the error report

Error report:
ORA-01722: invalid number
ORA-06512: at line 6
01722. 00000 -  "invalid number"
*Cause:
*Action:

The data type defined for stocks_id is number, and rajesh here is varchar2, that is why is this error is getting generated. I need some way by which rajesh comes as number to IN clause.
As I stated this is just miniature I can not do something like this

select count(*) into x from users_stocks where stocks_id in(select some column from someTable);

Please suggest. Thanks in advance.

If you want to go down this route (be aware there's a limit of 4000 values per IN, and all of the individual SQL statements are going to flood your shared pool, potentially causing performance problems), you can alter your code to use execute immediate and concatenate the IN list into the query.

You'd be better off combining the 2 queries in your code into a single query, and avoiding this completely.


Overlap in LSN's backed up My current backup strategy for our AlwaysOn clusters is a daily full backup and hourly transaction log backups via a third party backup program. We had a request to minimize the amount of log backups and I noticed that we had a cluster of large log backups at the same time during the day and we began a process to run a log backup to nul right before a full backup.

It seemed to have worked until I tried to do a point in time restore and then it wouldn't restore past the full backup. I investigated and noticed that there is an overlap in the LSN's being backed up. Here is an example for one of the databases.

Last real log backup (secondary) - 20165000011169700001 / 20168000018974100001

backup to nul (primary)          20168000018974100001 / 20168000019060800001

full backup   (primary         20168000019060800001 / 20168000019245900001

first log backup (secondary)      20168000019060800001 / 20174000018926600001

It looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster. I assumed that running backup log on any node will transfer to the other one since we normally run log backups on the secondary.

Is this how it is supposed to work?

it looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster.

Not to me.  The First Log Backup on the secondary (after the full) starts with the last LSN covered by the backup to 'nul' taken on the primary.

But stepping back to "We had a request to minimize the amount of log backups": why?  And why are you breaking the log chain with the backup to 'nul'?

  Is this how it is supposed to work?

You are supposed to maintain an unbroken chain of log backups in addition to occasional Full backups.  The log records included in a Full backup will also be present in the next Log backup, as Full backups never break the log chain.  This allows you to loose a Full backup, and restore from a previous Full plus all the log backups.


Use Where Clause With Merge In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table?  Something similar to my below DDL - but of course this throws an error and does not work.

What should I update in my syntax to only update rows that are different?  My desired update would be to not iterate each and every row that matches, but only update emp44 & emp55 since the address is different.

The error presented is

  Msg 156, Level 15, State 1, Line 24
  Incorrect syntax near the keyword 'WHERE'.

Syntax

  DECLARE @Emp Table (empid varchar(10), empaddress varchar(100))
  Insert Into @Emp Values
  ('emp11', '111 No Blue'),
  ('emp22', '222 No Blue'),
  ('emp33', '333 No Blue'),
  ('emp44', '444 No Blue'),
  ('emp55', '555 No Blue');

Declare @EmpUpdates TABLE (empid varchar(10), empaddress varchar(100))
Insert Into @EmpUpdates Values
('emp11', '111 No Blue'),
('emp22', '222 No Blue'),
('emp33', '333 No Blue'),
('emp44', '999 No Blue'),
('emp55', '888 No Blue'),
('emp66', '4141 No Blue');

MERGE @Emp emp
Using @EmpUpdates eup
ON emp.empid = eup.empid
 WHEN MATCHED THEN
UPDATE
SET emp.empaddress = eup.empaddress
WHERE emp.empaddress <> eup.empaddress
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid, empaddress)
VALUES(eup.empid, eup.empaddress)

There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help.

There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there:

MERGE @Emp emp
USING @EmpUpdates eup
ON emp.empid = eup.empid
WHEN MATCHED
     AND emp.empaddress <> eup.empaddress
  THEN
    UPDATE
    SET emp.empaddress = eup.empaddress
WHEN NOT MATCHED BY TARGET
  THEN
    INSERT (empid, empaddress)
    VALUES (eup.empid, eup.empaddress) ;

Be careful to handle nulls correctly there (if empaddress is nullable).

It is sometimes more explicit (and efficient) to perform the operations in two separate statements (UPDATE and INSERT in your case) in a transaction.

Also, be aware there have been (and still are) some issues with MERGE.


Cannot see other databases in SQL Server Managed Instances using Azure AD user we are running PoC on SQL Server Managed Instance but it doesn't seem to be walk in the park and we're hit some blockers. Hoping some of you may have resolved this already and can share some experiences :)

Our Business Acceptance Criteria:

Users must be able to run standard SQL queries- OK
Users must be able to correlate data between databases on cloud-based server- BLOCKED
Users must be able to access from and to on-prem linked servers- BLOCKED
Users must be able to access data with existing tools
SSMS - OK
Spotfire- OK
PowerBI - BLOCKED, AAD user doesn't work!
Excel - BLOCKED, AAD user doesn't work!
Tableau - Pending

Given I have SQL Server Managed Instance with these setup

SQL Managed Instance, General Purpose
MI on private VNet, we have Azure AD synced with our ON-PREM AD
Lifted two databases DB1, DB2 from ON-PREM instances
MI Containment = Disabled, DB1 and DB2 Containment = None
Mapped existing user groups from Azure AD (AAD) DB_ADM_USERS
Granted DBO role on group DB_ADM_USERS on each database DB1, DB2
Granted DBO role on group DB_ADM_USERS on MASTER
Added linked servers to ON-PREM instances using option #4 Security Context with remote user and password
I have USER1 who is part of AAD Group DBM_ADM_USERS
USER1 connects via latest version of SSMS
USER1 connects using Active Directory - Integrated

Questions:
When user logs in Uses Active Directory - Integrated

If USER1 doest not specify databases in connection, we get connection error

  Cannot connect to xxxx.xxxx.database.windows.net. Login failed for
  user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error:
  18456)

If USER1 specifies the database to connect to such as DB1

User can successfully connects, DB1 objects are accessible
User CANNOT see DB2 is not accessible and all other objects including the Linked Servers

If USER1 specifies the database to connect to such as DB2

User can successfully connects, DB2 objects are accessible
User CANNOT see DB1 is not accessible and all other objects including the Linked Servers

Thanks in advanced - Ardi

What you are seeing here is by design and my guess is things will change with future releases. Behind the scene managed instance still uses same authentication model as Azure SQL Database.

Managed Instance supports Azure AD authentication as cloud alternative to Windows authentication. The link will take you to the page where it explains Use Azure Active Directory Authentication for authentication with SQL. Which is for Azure SQL Database and SQL Data Warehouse.

  If USER1 does not specify databases in connection, we get connection
  error

Azure Active Directory authentication requires database users to be created as contained database users. A contained database user based on an Azure AD identity, is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory that is associated with the database. The Azure AD identity can be either an individual user account or a group.

This article explains about Contained Database Users.

This section of Books Online clearly explains that you will need to mention database name (Because it is a contained user) in your connection string.

Azure AD limitations related to Managed Instance:

Only Azure AD admin can create databases, Azure AD users are scoped
to a single DB and do not have this permission
Database ownership:
Azure AD principal cannot change ownership of the database (ALTER
AUTHORIZATION ON DATABASE) and cannot be set as owner.
For databases created by Azure AD admin no ownership is set (owner_sid field in sys.sysdatabases is 0x1).
SQL Agent cannot be managed when logged in using Azure AD principals.
Azure AD admin cannot be impersonated using EXECUTE AS
DAC connection is not supported with Azure AD principals.
These system functions return NULL values when executed under Azure
AD principals:

SUSER_ID()
SUSER_NAME(<admin ID>)
SUSER_SNAME(<admin SID>)
SUSER_ID(<admin name>)
SUSER_SID(<admin name>)

For now as a workaround you will need add your user into an AD group and make that group an administrator. I agree that is not ideal as you are giving more privilege then you need. But there is no other way to solve your problem with current build.


Why is sp_update_alert failing? In SQL Server 2012, I have a Job Agent Alert set up as follows:

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0',
        @job_id=N'00000000-0000-0000-0000-000000000000'

I have a job scheduled to run every weekday at 5:30pm that executes this step:

EXEC msdb.dbo.sp_update_alert @name = N`enter code here`'Deadlock Alert', @enabled = 0;

I have another that re-enables the alert at 8:00am every weekday. Both jobs (and the alert) have been working fine for months in dozens of instances. But the job at 5:30pm on Friday failed on one instance with the following messages:

  The specified object_name ('Locks') does not exist. [SQLSTATE 42000]
  (Error 14262)  The specified object_name ('Databases') does not exist.
  [SQLSTATE 42000] (Error 14262).  The step failed.

If I execute the sp_update_alert command directly for that alert (with enabled set to either 0 or 1). I get this error:

  Msg 14262, Level 16, State 1, Procedure
  sp_verify_performance_condition, Line 50 The specified object_name
  ('Locks') does not exist.

I tried disabling the Alert using the SSMS UI and get:

  The specified object_name ('Locks') does not exist. (Microsoft SQL
  Server, Error: 14262)

This pair of jobs is still working without error in all of the other instances. What could be causing sp_update_alert to fail in this was on just one instance and only since Friday?

[Added:]

The enable and disable jobs also enable and disable another alert, which looks at the SQLServer:Databases|Data File(s) Size (KB)|tempdb counter and those commmmands throws the error "The specified object_name ('Databases') does not exist". Again, only in this one instance. The same jobs and alerts still work fine on another instance on the same server.

Verify that the SQL Server Agent account has the necessary permissions to query the performance counters DMV (sounds like your agent account has recently been changed). According to the documentation:

  Requires VIEW SERVER STATE permission on the server.

So:

GRANT VIEW SERVER STATE TO [SQL Agent account];

Also make sure that as that login (or any login with VIEW SERVER STATE, such as sa), you can see results come back from the DMV. I have heard of many cases where the performance counters simply come up empty in SQL Server, and this dates back to SQL Server 2000 with sysperfinfo. Several sources suggest the following as a solution from the command line (followed by a reboot):

lodctr "<path to binn>\binn\sqlctr.ini"

Of course Microsoft closes most of these reports as no repro (which doesn't surprise me, as I haven't seen this symptom directly, and would have no idea how to reproduce the issue).

Missing sys.dm_os_performance_counters
sys.dm_os_performance_counters only showing XTP-related data (fix was applying a CU)

Also some advice in these threads, which you are welcome to try (these are quite long-standing and I'm not going to try and summarize and repeat the info here):

What should we do when the sql server performance counters are missing
How to revive SQL Server 2008 R2 performance counters?
sys.dm_os_performance_counters empty
Empty Sysperfinfo table and blank sql performance monitor counters


How to lower HEAP Fragmentation in SQL Server? i recently found out that one heap table had more than 70% fragmentation.
So i decided to do a

ALTER TABLE dbo.myTable REBUILD

Funny enough, afterwards i had 20% fragmentation. There was no write on that table ever since. So i decided to do the rebuild one more time.

After the 2nd time the table hat 50% fragmentation so even more!
I really don't understand how this can happen...

What does Fragmentation Means in a Heap

The fragmentation value in Heap which you get from column avg_fragmentation_in_percent by querying sys.dm_db_index_physical_stats DMV states that

  Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

Further the same BOL says that

  This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

So you can see it is not the free space present in pages allocated to Heap but the varying sequence of pages that creates the fragmentation.

This can be demonstrated by small test. Let us create a Heap Table and insert some records in it and then check the fragmentation.

create table dbo.HeapTest
(
Id INT not NULL Default (1),
Col1   char(5000) Not null Default ('Heaps Are Cool')
)

SET NOCOUNT ON

Insert into dbo.Heaptest default values
go 50

select index_type_desc,avg_fragmentation_in_percent,fragment_count,
avg_page_space_used_in_percent,record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.HeapTest','U'),0,default,'detailed')

So Heap table is created with 50 records in it. Below is what fragmentation looks like after query DMV sys.dm_db_index_physical stats

You can see avg_fragmentation_in_percent column value is 33 %. Now let us see how are pages arranged. This can be done by using undocumented query %%lockres%%. The query would be

SELECT  %%lockres%%, * FROM dbo.HeapTest;

And below is what output looks like. Attaching only relevant portion of it. The query produced 50 rows since we inserted 50 rows in our dbo.HeapTest table.

What it says is the first page has ID 197 the next page has ID 242 subsequent pages has continuous ID till we reach page ID 264 because after that we get page ID 280. So this jump in page ID numbers is what actually causing fragmentation.

Now lest rebuild the heap and run The command again to see the fragmentation and how pages are arranged. We get fragmentation like

You can see fragmentation is now 14%.

Let us see page numbers allocated

We only have one jump rest all pages are allocated page ID serially. Since just one jump fragmentation decreased considerably.

I rebuild the Heap again and now when I checked fragmentation it was completely gone. And page ID allocation is like

Why Fragmentation Increased

Now regarding what could have caused fragmentation to rise we can corroborate it to fact that when pages were getting allocated to the heap they would not be continuous, as you saw above what caused fragmentation value to increase was jump in the PAGE ID's allocated to pages.

At the back of head you should also keep in mind that the word fragmentation for HEAP does not have any meaning, how would you define fragmentation for bunch of un-ordered pages.

Really Worried about Fragmentation

If you really face a scenario where heap table is fragmented and slowing queries it would be better creating a clustered index on table than rebuilding it. The reason is when you rebuild heap all underlying Non Clustered indexes are also rebuilt causing the rebuild process to take much longer time, utilizing lot of resources and bloating transaction log. On a production system one would always try to avoid this. Paul covered this in his Myth Section about heap.

PS: Please don't use undocumented command on production system. This was just for demonstration.


Concatenate a string value from table A to a text field in table B, in a scalable way On a daily basis, I am adding values from the following table (table1):

Id    Name      Value
1     'Name 1'  789
2     'Name 2'  789
3     'Name 3'  789

To the text field (Values) in the following table (table2), with a comma:

Id    Name      Values
1     'Name 1'  '123,456,789'
2     'Name 2'  '123,456,789'
3     'Name 3'  '123,456,789'

(So on day one, '123' was added. On day two, '456' was added et.c.)

This is to have a daily history of values, over the last year. The idea of having a "flat" history field (rather than a table with an entry per day) is to have fast reads:

I'm reading a lot from that table
I need all 360 days for every read
I'm writing once a day
I don't need to query the values

I currently have ~32 million rows in each of the two tables above.

Question #1: is this a good way at all of solving my problem of having to display daily values over a year, with fast reads and slower writes?

Question #2: assuming this is an acceptable way of solving the problem - what is the most efficient way of executing the above concatenation? My current solution is to do a MERGE that selects all entries from table1 as a source:

MERGE table2 AS [Target]
USING(SELECT Name, Value FROM table1) AS [Source] ON [Target].Name = [Source].Name
WHEN MATCHED THEN UPDATE SET [Target].Values = [Target].Values + CONVERT(varchar(12), [Source].Value) + ',' + ','
WHEN NOT MATCHED THEN INSERT(Name, Value) VALUES([Source].Name, CONVERT(varchar(12), [Source].Value) + ',');

The reason I am asking these questions is that this takes several hours to run and I'm hitting cannot obtain a LOCK resource at this time errors.

UPDATE #1:
With the very useful comments I got on this question, I learned that the "flat" model I'm using will probably not have any effect on performance as SQL Server reads per page and not per row. Based on that, I'll re-model everything into a more traditional relational structure: one row per entry.

I had to get things working ASAP though, so as a band aid I wrote a stored procedure that updates the flat model without locking. In stead of using a MERGE, it iterates over each row in table1, and for each row, upserts (UPDATE followed by a IF (@@ROWCOUNT = 0) with an INSERT) the corresponding table2 entry. It takes the same amount of time to run, but uses half as much CPU (interesting) and doesn't lock.

Thanks everyone for your inputs.

The stored procedure (Name, Value and Values will have to be renamed):

-- row ID for iteration
declare @id int
select @id = min(id) from table1

-- field variables
declare @name varchar(128),
        @value int

-- iterate through all table1 entries
while @id is not null
begin

    -- select next table1 entry
    select @name = Name, @value = Value
    from table1
    where id = @id;

    -- update corresponding table2 entry
    update table2
    set Updated = getdate(),
        Values = Values + CONVERT(varchar(12), @value) + ','
    where Name = @name

    -- if no rows were updated, there is entry for this name; insert it instead
    if (@@rowcount = 0)
        begin
            insert into table2 (Created, Updated, Name, Values)
             values (
                getdate(),
                getdate(),
                @name,
                CONVERT(varchar(12), @value) + ',')
        end

    -- update @id to select next row
    select @id = min(id) from table1 where id > @id
end

The suggestion to have one row per value is good advice. It should be the starting point for any relational database design. To ensure all rows are close together (on the same or at least adjacent pages) you will have to have the clustered index on (ID, Date) (or on (Name, Date) depending on which you've chosen for the PK). Next you'll need to make sure there's room for newly-arriving rows beside their siblings. You can do this in one of two ways. One - by setting PAD_INDEX and FILLFACTOR to reserve that space when the index is created. Two - define the index with little or no free space reserved and rebuild it regularly.

That said, what you're proposing is not silly. As long as VALUES is treated as a single attribute and no query tries to dissect it the design is first normal form.

Why would you write to table1 then try to re-write into table2? I'd upsert (i.e. MERGE) into table2 directly, as each value comes into the system.

It seems your primary concern is that the target table is being locked, preventing concurrent work from proceeding. This is caused by lock escalation. The solution is to process the writes in smaller batches. The optimal batch size can only be found by experimentation, but usually a few thousand at a time is about right. I've an answer on this here.

The MERGE statement has had some problems. I haven't checked if they've been fixed recently (& I'm not sure what version you're running) so be aware and read up on the work-around. I propose the following alternative using newer functions which may simplify syntax:

declare @Target table (ID int, x varchar(99));
declare @Source table (ID int, x varchar(99));

insert @Target values (1, 'a'), (2, 'b,c'), (3, 'd');
insert @Source values (1, 'w'), (2, 'x'), (4, 'y');

-- UPDATE @Target with these rows:
select
    t.ID,
    CONCAT_WS (',', t.x, s.x)
from @Target as t
inner join @Source as s    -- INNER JOIN ensures there's a row in both tables
    on s.ID = t.ID

-- INSERT @Target with these rows:
select
    s.*
from @Source as s
where not exists     -- ensures only new rows are attempted
(
    select 1
    from @Target as t
    where t.ID = s.ID
);

Wrap this in the batching technique I suggested above. The downside is a second scan of the data. As the INSERT and UPDATE branches are disjoint sets they could be run in separate, concurrent jobs.


Can You Drop Consistency from ACID? In his book "An Introduction to Database Systems" C.J.Date wrote a chapter about the ACID principles called "Dropping ACID". In this chapter he calls Correctness(Consistency) "essentially meaningless" and the other principles at most "a desideratum".

This book is from 2004 and everywhere i go on the internet i see ACID principles as the guidline for RDBMS application programming. Does that mean that Date's ideas to ACID were not accepted? Or is ACID from a theoretical point of view not as mandatory as it seems when reading internet documentation about database programming?

I hope my question makse sense and is in the right forum.

Content of the book

The author analyzes the ACID concept from a very theoretical point of view.

The essential part might be the first sentence of the last paragraph of the chapter:

  Overall, then, we conclude that the transaction concept is important
  more from a pragmatic point of view than it is from a theoretical one.

Another important part is his introduction of the "multiple assignment" operator (p. 487), which he uses as a solution for some problems which are addressed by practical DBMS by supporting transactions. This operation does not seem to work in MySQL, Oracle nor MSSQL. (Make it possible to update several tables with one query.)

His statement is: If we had this multiple assignment operator, deferred checks of constraints are not needed and should be applied immediately and not at the end of a transaction. The latter one results in making the C of ACID useless, as the database is then always consistent (if the C stands for consistency) or it's correctness is unenforceable (if the C stands for correctness).

Concerning Isolation he claims that this should mean all transactions should have absolutely no knowledge of other transactions might be running. This is likely not doable. He further tells that most DBMS provide "isolation level" settings, that undermine this concept in it's base.

Durability he writes could only apply to the most outer transaction, if nested transactions are supported.

Atomicity according to him is only needed because there is need for transactions. If the multiple assignment operator is available as atomic, one would not need atomic transactions.

What to take away

This is a very fundamental discussion about the ACID concept. Without judging if his critics is correct or not, there is a living landscape of DBMS, which is based on transactions.

C.J. Date himself writes that "we now have a better understanding of some of the assumptions on which that research was based". This means it's one of the fundamental aspects, which one cannot change easily in living DBMS. Remove transactions and push in atomic multiple assignment operators and no enterprise will want to update it's architecture, because this is a lot of change.

Thus you have read a theoretical chapter proclaiming how one could improve the properties of DBMS conceptually, and which would erase some problems fundamentally. But only because something might be superior, it will not be used in practice immediately.

You have basically reached the border between someone with a mere theoretical point of view and the practical world, in which there is more than theory, but an existing world, which has to wrap around.


Help with DB2 Procedure I have the following query:

CREATE PROCEDURE MYPROC(
IN @TABLES VARCHAR(500))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MYPROC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN

 --declare variables

DECLARE @TABLE VARCHAR ( 100 ) ;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT * FROM TABLE(split(@TABLES, ';')) AS FUNC;

 -- create temp table
DECLARE GLOBAL TEMPORARY TABLE SESSION . KEYS
(
    INMATEID INTEGER NOT NULL ,
    ROWID INT GENERATED ALWAYS AS IDENTITY ( START WITH 1 , INCREMENT BY 1 )
)

WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ;

IF ( @INMATEID IS NOT NULL ) THEN
    INSERT INTO SESSION . KEYS ( INMATEID ) VALUES ( @INMATEID ) ;
ELSE

    INSERT INTO SESSION . KEYS ( INMATEID )
    SELECT MyTable.ID
    FROM MYTABLE;
END IF ;

 -- select tables

OPEN C1;
FETCH C1 INTO @TABLE;

WHILE (SQLCODE = 0) DO
BEGIN

If (@TABLE = 'INMATES') THEN
   SELECT INMATES.* FROM ATABLE;  <-- THIS IS THE PROBLEM SPOT!
END IF;

FETCH C1 INTO @TABLE;
END;

END WHILE;
CLOSE C1;
END;

The problem is marked in the above procedure.  I want to loop through the passed in 'tables' and SELECT data from the tables that are passed in.  The problem is that I cannot do a SELECT statement within my while loop and if statement.  Are there any syntax problems that you see or is it something else entirely?

When I try to CREATE the procedure I get this error message:

SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger STSSP_JAILIF_GETDATA in PJGVDGD1 not created.

I've narrowed it down to that it's definitely related to my 'SELECT INMATES.*' line in the IF Statement.

The DB2 Docs suggest to me that returning a dynamic result set is done via a cursor with a WITH RETURN clause rather than a simple select statement.


Postgres Problems with Phusion Deployment (Rails) FATAL:  Ident authentication failed for user "test-postgres"

So I have a postgres server hosting a side of my database access for my Rails application and I have a database for production set up and a user for production set up but I keep getting the previous error whenever I try to view my Rails application from a browser. Can somebody help me out? I have the postgres user created and the database created already...

Read the Ubuntu documentation on their PostgreSQL packages, which you'll find in /usr/share/doc . Alternately, there's a nice wiki page on it here: https://help.ubuntu.com/community/PostgreSQL (first hit for "ubuntu postgresql").

You have a pg_hba.conf file that's set to try to use ident authentication for host (TCP/IP) connections, but you don't have an identd running or you're trying to connect as a different PostgreSQL user to your unix username.

You need to set a paassword for the user and change the authentication method to md5. See the client authentication chapter of the documentation.

(A truly definitive answer would be easier to make if you'd given the command you ran to get that error).


What is the relation between MySQL pages and Operating system pages? By MySQL pages i mean this :

https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

and operating system pages :

https://en.wikipedia.org/wiki/Page_(computer_memory)

I understand both of them, but i don't understand their relation, so DBMS is basically a program that runs on top of our O.S correct? so why are DBMSes like MySQL don't just use the page size of the operating system and instead let the DBA decide what should the page size be?

what happens when the O.S page size is 8KB but DBMS is 16KB? and what if its vice versa?

Does a difference between these two affect performance or anything else?

The OS decided that XKB (often 4KB today) was a good size.  (In the old days, it was 1KB, or even less.  Note that disks are frozen in 512-byte units!)

InnoDB decided that 16KB was a good size.  InnoDB also implements tablespaces (eg ibdata1 or .ibd files) as files, not as raw disk partitions.  So, InnoDB is at the mercy of what the OS is going to do when asked to "in file y.ibd, seek to offset z, and read 16KB bytes.  (Side note:  InnoDB can be configured to use 4/8/16/32/64KB, but there are disadvantages, and I have yet to hear of anyone actually using such.)

One can hope, but not necessarily control, that a tablespace assigned at some disk address that is a multiple of 16KB, or even that it is aligned with the pages that the OS sees.  But, the OS will deliver the 16KB, regardless of the details of where the piece(s) are.

InnoDB takes pains to avoid the "torn page" problem.  This is where the system crashes in the middle of writing, say, the four 4KB blocks for the one InnoDB block.  Such a half-written block would have the effect of destroying the data integrity for that table.  To compensate, InnoDB has a "double-write" buffer in which it effectively writes some things twice.  Upon recovery, it will be able to discover and fix the "torn page" if necessary.

Since the OS might choose to scatter its pieces of InnoDB's 16KB, there is a potential performance problem with extra head seeks (on HDD).  For SSD, this does not matter much, if at all.

Hardware RAID controllers with "Battery Backed Write Cache" are an excellent way to both speed up write (making them virtually instantaneous without risk of loss) and avoid the scattering problem, even for HDDs.

Now enter some disk manufacturers.  To be "the best" in the MySQL market, their disk (SSDs) can be configured such that 16KB is the "atomic" write size.  With that kind of disk, InnoDB can be configured to turn off the double-write, hence run faster.

O_DIRECT is usually optimal for InnoDB because it avoids a copy of the 16KB to/from an OS buffer.  But this depends on the details of the disk driver.

innodb_flush_neighbors/innodb_flush_neighbor_pages is a setting to flush adjacent pages at the same time, thereby avoiding a disk seek; it has some performance benefit for HDDs.  It may have a negative impact on SDDs.

(Sorry for the TMI, sometimes my fingers just keep going.)


Central stored procedure to execute in calling database context I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on one of my databases, it does what I want it to do and pulls down a listing of all records regarding any database. When I place it on a different database it pulls down a listing of all records relating to only that DB.

For example (code at bottom):

Query run against Database 6 shows [requested] information for databases 1-10.
Query run against Database 3 shows [requested] information for only database 3.

The reason I want this procedure specifically on database three is because I'd prefer to keep all maintenance objects within the same database. I'd like to have this job sit in the maintenance database and work as if it were in that application database.

Code:

ALTER PROCEDURE [dbo].[GetFragStats]
    @databaseName   NVARCHAR(64) = NULL
    ,@tableName     NVARCHAR(64) = NULL
    ,@indexID       INT          = NULL
    ,@partNumber    INT          = NULL
    ,@Mode          NVARCHAR(64) = 'DETAILED'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @databaseID INT, @tableID INT

    IF @databaseName IS NOT NULL
        AND @databaseName NOT IN ('tempdb','ReportServerTempDB')
    BEGIN
        SET @databaseID = DB_ID(@databaseName)
    END

    IF @tableName IS NOT NULL
    BEGIN
        SET @tableID = OBJECT_ID(@tableName)
    END

    SELECT D.name AS DatabaseName,
      T.name AS TableName,
      I.name AS IndexName,
      S.index_id AS IndexID,
      S.avg_fragmentation_in_percent AS PercentFragment,
      S.fragment_count AS TotalFrags,
      S.avg_fragment_size_in_pages AS PagesPerFrag,
      S.page_count AS NumPages,
      S.index_type_desc AS IndexType
    FROM sys.dm_db_index_physical_stats(@databaseID, @tableID,
           @indexID, @partNumber, @Mode) AS S
    JOIN
       sys.databases AS D ON S.database_id = D.database_id
    JOIN
       sys.tables AS T ON S.object_id = T.object_id
    JOIN
       sys.indexes AS I ON S.object_id = I.object_id
                        AND S.index_id = I.index_id
    WHERE
        S.avg_fragmentation_in_percent > 10
    ORDER BY
        DatabaseName, TableName, IndexName, PercentFragment DESC
END
GO

One way would be to make a system procedure in master and then create a wrapper in your maintenance database. Note that this will only work for one database at a time.

First, in master:

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(),
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
    -- shouldn't s.partition_number be part of the output as well?
  FROM sys.tables AS t
  INNER JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(), t.[object_id],
      i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  -- probably also want to filter on minimum page count too
  -- do you really care about a table that has 100 pages?
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

Now, in your maintenance database, create a wrapper that uses dynamic SQL to set the context correctly:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,      -- can't really be NULL, right?
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
    EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';

  EXEC sp_executesql
    @sql,
    N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
    @tableName, @indexID, @partNumber, @Mode;
END
GO

(The reason the database name can't really be NULL is because you can't join to things like sys.objects and sys.indexes since they exist independently in each database. So perhaps have a different procedure if you want instance-wide information.)

Now you can call this for any other database, e.g.

EXEC YourMaintenanceDatabase.dbo.GetFragStats
  @DatabaseName = N'AdventureWorks2012',
  @TableName    = N'SalesOrderHeader';

And you can always create a synonym in each database so you don't even have to reference the name of the maintenance database:

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

Another way would be to use dynamic SQL, however this, too, will only work for one database at a time:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT
    DatabaseName    = @DatabaseName,
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
        DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;';

  EXEC sp_executesql @sql,
    N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
      @partNumber INT, @Mode NVARCHAR(20)',
    @DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO

Yet another way would be to create a view (or table-valued function) to union the table and index names of all your databases, however you'd have to hard-code the database names into the view, and maintain them as you add/remove databases that you want to allow to be included in this query. This would, unlike the others, allow you to retrieve stats for multiple databases at once.

First, the view:

CREATE VIEW dbo.CertainTablesAndIndexes
AS
  SELECT
    db = N'AdventureWorks2012',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM AdventureWorks2012.sys.tables AS t
  INNER JOIN AdventureWorks2012.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  UNION ALL

  SELECT
    db = N'database2',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM database2.sys.tables AS t
  INNER JOIN database2.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  -- ... UNION ALL ...
  ;
GO

Then the procedure:

CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName NVARCHAR(128) = NULL,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(s.database_id),
    TableName       = v.[table],
    IndexName       = v.[index],
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM dbo.CertainTablesAndIndexes AS v
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
    AND v.index_id = COALESCE(@indexID, v.index_id)
    AND v.[table] = COALESCE(@tableName, v.[table])
    AND v.db = COALESCE(@DatabaseName, v.db)
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO


Running Check Check Constraint and still have untrusted foreign keys (not a replication issue) Anyone any ideas on this one?  As per Brent Ozar's recommendation (thanks Brent and team!), I've ran the query below which shows FK's that are untrusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND  i.is_disabled = 0;
GO

It displays two keys.  I then run WITH CHECK CHECK CONSTRAINT against these two foreign keys (they are on massive tables), which takes some time but eventually completes.  I run the checking query above again, and they are still there!

I've done some research online and found that "is_not_for_replication" can be the common cause for this, but when I run:
    SELECT name, is_not_trusted, is_not_for_replication FROM sys.foreign_keys   WHERE is_not_trusted = 1; it is showing is_not_for_replication = 0.

Anyone any ideas why these foreign keys aren't trusted despite running CHECK CHECK CONSTRAINT?  It should be noted that I am able to run CHECK CHECK CONSTRAINT against other foreign keys successfully.

Thanks in advance!

So, turns out the Application Code uses BULK INSERTS which makes the FK's become un-trusted.  To resolve this, I need to get the Developers to add in WITH ( CHECK_CONSTRAINTS ) to the BULK INSERT to resolve the issue and then re-run the WITH CHECK CHECK CONSTRAINTS.  Thanks to Dan Guzman for the prompt.


Meaning of 'SET' in error message 'Null value is eliminated by an aggregate or other SET operation' I saw the above 'ANSI warning' message today when running a colleague's script (and I don't know which of the many statements caused the warning to be shown).

In the past I've ignored it: I avoid nulls myself and so anything that would eliminate them is a good thing in my book! However, today the word 'SET' literally shouted out at me and I realised I don't know what the meaning of the word is supposed to be in this context.

My first thought, based on the fact it is upper case, is that it is referring to the SET keyword and means 'assignment', as in

UPDATE <table> SET ...

...ON DELETE SET NULL...

SET IDENTITY_INSERT <table> ON

According to the SQL Server Help, the 'ANSI warnings' feature is based on ISO/ANSI SQL-92, the spec for which makes just one use of the term 'Set operation' in a subsection title, hence in title case, in the data assignment section. However, after a quick Googling of the error message I see examples that are SELECT queries with seemingly no assignment involved.

My second thought, based on the wording of the SQL Server warning, was that the mathematical meaning of set is implied. However, I don't think that aggregation in SQL is strictly speaking a set operation. Even if the SQL Server team consider it to be a set operation, what is the purpose of putting the word 'set' in capitals?

While Googling I noticed a SQL Server error message:

Table 'T' does not have the identity property. Cannot perform SET operation.

The same words 'SET operation' in the same case here can only refer to the assignment of the IDENTITY_INSERT property, which brings me back to my first thought.

Can anyone shed any light on the matter?

I was just looking through the SQL-92 Specification and saw a passage that reminded me of this question.

There is in fact a prescribed warning for this situation as indicated below

  b) Otherwise, let TX be the single-column table that is the
                result of applying the <value expression> to each row of T
                and eliminating null values. If one or more null values are
                eliminated, then a completion condition is raised: warning-
                null value eliminated in set function.

I assume that the SET in the SQL Server Error Message is a reference to the set function of that error message although I'm not sure why it would make a distinction between aggregates and other set functions, as far as I can see they are synonymous. The relevant bit of the grammar is below.

6.5  <set function specification>

         Function

         Specify a value derived by the application of a function to an
         argument.

         Format

         <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>

         <set function type> ::=
              AVG | MAX | MIN | SUM | COUNT

         <set quantifier> ::= DISTINCT | ALL


Handling 'Variable not found' in a tokenised job step I have an Agent job which is usually triggered by a WMI event.  The job has a token within it, which automatically takes a value from the WMI event and substitutes it into the step's Powershell code, e.g.:

$alert_wmi_state = '$(ESCAPE_SQUOTE(WMI(State)))';

The SQL Agent is configured with the Replace tokens for all job responses to alerts setting checked so, when the WMI event triggers, the substitution happens fine, and the code that is run is:

$alert_wmi_state = '7';

However, there is the possibility that this job could be run manually (i.e. right-click, Start Job at step...) and I would like my step code to handle this gracefully, so I have this logic further in my step:

if ($alert_wmi_state -eq ('$' + '(ESCAPE_SQUOTE(WMI(State)))')) {
    ...
}

...the theory being that, splitting $ from (ESCAPE_SQUOTE... would reasonably mean that this is not recognised as a token, and so does not get substituted, so the test would read literally:

if ($alert_wmi_state -eq '$(ESCAPE_SQUOTE(WMI(State)))') {
    ...
}

However, when running the job manually, the SQL Agent always errors out at the step (regardless if it's configured as a CmdExec job step type or a PowerShell job step type) before any of my code is run, with the error Variable WMI(State) not found.

My guess is that this is because the substitution of ESCAPE_SQUOTE happens before the step code is actually parsed/loaded, so the Agent never runs the step.

I expect I could have two steps, one with the token and one without, and have the failure action of the tokenised one start the non-tokenised one, but that duplication of step code is not desirable.

So, my question is, is there any way to gracefully handle the non-existence of a step token within the step code?

Would using a PowerShell here-string in your script prevent the need for ESCAPE_SQUOTE?

$alert_wmi_state = @'
$(WMI(State))
'@

That should at least let you determine if the problem is with ESCAPE_SQUOTE throwing an error or if it's SQL Agent's token parser itself that's complaining.

As long a the SQL Agent doesn't literally begin with '@ you should be fine.  If it does begin with '@, you can fix that easily:

$alert_wmi_state = @'
_$(WMI(State))
'@.Substring(1)

Note that you typically want to use @'...'@ and not @"..."@ since the latter is still evaluated by PowerShell like a double quoted string.


MariaDB keeps crashing after a few hours with ridiculous memory expectations Mariadb keeps crashing with typical errors from /var/log/mysql/error.log being:

... terribly wrong ...
stack_bottom = 0x0 thread_stack 0x20000
(my_addr_resolve failure: fork)
...
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 130760704 bytes)
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 98058240 bytes)
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 73531392 bytes)
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-01-27  9:04:30 3069758672 [Note] InnoDB: The InnoDB memory heap is disabled
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-01-27  9:04:30 3069758672 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using Linux native AIO
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using generic crc32 instructions
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(138543104 bytes) failed; errno 12
2019-01-27  9:04:30 3069758672 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-01-27  9:04:30 3069758672 [ERROR] Plugin 'InnoDB' init function returned error.
2019-01-27  9:04:30 3069758672 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-01-27  9:04:30 3069758672 [Note] Plugin 'FEEDBACK' is disabled.
2019-01-27  9:04:30 3069758672 [ERROR] Unknown/unsupported storage engine: InnoDB
2019-01-27  9:04:30 3069758672 [ERROR] Aborting

My platform is a Raspberry Pi Zero with 512M of RAM and running 2018-10-09-raspbian-stretch.  I’ve installed Mariadb version
mysql  Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

From reading various forums I’ve changed the following:

/etc/mysql/mariadb.conf.d/50-server.cfg:
change key_buffer_size to 64K
change max_connections to 10
change query_cache_limit to 16K
change query_cache_size to 128K

I tried to add

performance_schema=0

to /etc/mysql/my.cnf but Mariadb didn’t recognize the variable.

There are only two clients to the database – an Apache2 server on the same device which connects every 5 seconds and a daemon I wrote in C which connects every second, both for a brief time.  I’ve checked my code and each of them closes the connection to Mariadb correctly.

Any help would be very much appreciated.

EDIT: After enabling Slow Query Logging, I find that most queries take around 1ms-3ms to run, but in the lead-up to the crash, this time began to ramp up to 1.8 seconds, before the final log event of:

SET timestamp=1548626761;
SELECT * FROM timers;
# Time: 190128  6:06:03
# User@Host: pi[pi] @ localhost []
# Thread_id: 4  Schema: hptimer  QC_hit: No
# Query_time: 1.865374  Lock_time: 0.801896  Rows_sent: 0  Rows_examined: 8
# Rows_affected: 1
SET timestamp=1548626763;
UPDATE relays SET state=0 WHERE id=3;
/usr/sbin/mysqld, Version: 10.1.23-MariaDB-9+deb9u1 (Raspbian 9.0). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

then no further information.

EDIT#2: Thank you for the suggestions to monitor memory usage/other processes.  I found my C daemon process was consuming an additional 100KiB per call using "top", hence the OOM.  I shall pull out my K&R and take a closer look at my code before blaming other applications again :)

Best regards,
Peter

EDIT#3: You must always use

mysql_free_result( res );

after a query in C after your query has been processed.

https://dev.mysql.com/doc/refman/8.0/en/mysql-free-result.html

You must always use

mysql_free_result( res );
after a query in C after your query has been processed.

https://dev.mysql.com/doc/refman/8.0/en/mysql-free-result.html

This solved my OOM and memory leak from my C daemon, which was the cause of the problem.


Improving accurate count mechanism in PostgreSQL I have a problem I have been facing for some time and was ignoring for a while. The problem of counting rows. I have read that it is slow as the PostgreSQL has to validate each row thus have to do sequential scan. I am currently using PostgreSQL 13.
Potential solutions I came across:

Using Estimate.
Although the estimation was closer when querying a single table, the result was far off when using joins. Also I want exact count as I need to show the exact number to the user like how many transactions he did.

Using an external table to maintain the count.
The idea here was to use a rule or after insert trigger to update the count using count = count + 1 The problem here is the user can add multiple filters (like date, userid etc), which will change the count but the external table is only storing the count.

How would I solve this? I am trying to reduce the time to get the count to less than 3-4 seconds. Currently, there are almost 1 billion records in a table, although this table is split into 12 partitions for each month. Is this even possible?
Eleven partitions won't have inserts but could have delete operations. The user can apply filters which will change the count for any partition.
I am using a b-tree index on the date column. In explain analyze, the index is being used but since the records are huge, the b-tree index cannot fit in RAM as it's size is also big.
The tables are partitioned by each month and each partition has an index on the date, which means there is one index for each month.

With a large table, you won't be able to avoid I/O, and the query will never be fast. Very fast aggregates over huge amounts of data are not PostgreSQL's strong point. You want a column store for that (PostgreSQL has a column store extension).
If you VACUUM the table, PostgreSQL can avoid looking up the row by consulting the visibility map.
If 11 partitions are static, with only one active, it might be possible to store the total for the static partitions and add to that the count of rows for the active partition. If the user can apply filters, there is no way to get out of actually counting the rows that match. You might need a data warehouse of some sort that summarises the data.


What are these files in MongoDB? I was looking into my server and I found this:

[root@host ~]# cd /var/lib/mongo/journal/
[root@host journal]# ls -lh
total 3.1G
-rw-------. 1 mongod mongod 1.0G Apr  7 01:18 j._0
-rw-------. 1 mongod mongod   88 Apr  7 01:18 lsn
-rw-------. 1 mongod mongod 1.0G Dec 19 23:03 prealloc.1
-rw-------. 1 mongod mongod 1.0G Dec 19 23:06 prealloc.2

but in my db, db.stats() shows:

> db.stats()
{
    "db" : "gpstracker",
    "collections" : 5,
    "objects" : 59127,
    "avgObjSize" : 139.84318500854093,
    "dataSize" : 8268508,
    "storageSize" : 11198464,
    "numExtents" : 10,
    "indexes" : 3,
    "indexSize" : 1937712,
    "fileSize" : 201326592,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 5
    },
    "ok" : 1
}

And I have only one database, with 3 collections in it.

Maybe it is something that I don't know, yet.

I searched a bit and found things about compacting a database, can this be of help for this? Or these files are completely normal and they should be this way?

What if my database grows, say from ~200MB (which is now) to ~1G. How much does these files grow? Can these files (journals) be optimized somehow?

Sorry if my questions seem obvious. I'm a beginner in database department.

Thanks in advance

Those files belong to the journal, which is essentially what is going to guarantee consistency of your data as you write it to the database.  It is pre-allocated at 3GB (unless you start with --smallfiles) and will not be included in the size of your database.  This will not grow with your database but rather it will be based on how much you are writing to the database.  Unless you are doing a lot of writes the journal size will be 3GB.  If you are curious about journaling and how it works, there is a great write up here.

As for the database itself, the disk space usage question has been asked and answered many times - yes a regular compact (and repair or resync) can be needed, but it very much depends on what you are doing with the data.


Infrastructure for Highly Concurrent, High Write DB My requirements are:

3000 Connections
70-85% Write vs Read

Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of concurrent connections as the memory is fine. The write itself is very simple (validations slow things). To scale to 3000, we need to go to multiple servers, current options:

MySQL Sharding
MongoDB Cluster
Cassandra
Hadoop & MySQL (Hadoop caches, single dump to MySQL)
MongoDB & MySQL (instead of Hadoop, we use mongo for cache)

To handle this number of connections, a number of questions:

Can MySQL Sharding handle the concurrent connections?
Can any single master handle these concurrent connections, or is a multi-head like Mongo a better option?

I apologize if I'm not describing my problem well. Please ask questions.

If you are using MySQL as the main database, you may want to consider using a Star Topology via MySQL Replication.

Now, before you say UGHHH, ROFL and OMG to MySQL Replication, hear me out.

A star topology allows you to write to one DB server (called a Distribution Mster [DM]) and send the SQL commands to several DB servers. How do you setup such a DB infrastructure?

Here is the Description

You have 5 DB servers (server A,B,C,D,E)

Server A

In MySQL Replication setup, it will be the Master
Plays special role as the DM
Master of servers B,C,D,E
All tables use the storage engine BLACKHOLE (/dev/null)
Only stores binary logs
Bare Metal Machine
Benefits

Very fast writes since all tables on the DM use BLACKHOLE
Network Latency is less of a issue since reads are 15-30% of DB Activity
All slaves are updated strictly from the DM

Servers B,C,D,E

Slave of A
Server a base for heavy SELECTs
Server Can Be Virtual or Bare Metal
For all servers whose user tables use the storage engine InnoDB

It can server as a warm standby DB Server
Nonintrusive backups can be run against it

For all servers whose user tables use the storage engine MyISAM

Set up with read-only oprion
Tables can have their row formats redone to accelerrate reads

I have written posts on this before

https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

To keep MySQL Replication in tip top shape

Use MySQL 5.5
Use Semisynchronous Replication
Tune InnoDB to engage more CPUs


Error When Restoring Backed Up Tables I followed directions from this page: https://serverfault.com/questions/271367/importing-data-from-myd-back-up-file

I dropped the 3 files ( t2010_08.frm, t2010_08.myd, t2010_08.myi ) into my /var/lib/mysql directory.

Then I changed permission with the "chown mysql:mysql t2010_08.* command". Now, when I go to look at my table ( in HeidiSQL ), it tells me that it is type "View" and I get "Can't find file: 't2010_08' (errno:2) message.

What do I still need to do to gain access to this table?

I think I got it.

Here is the table as it appears in Linux from the Chat Session

-rw-rw----. 1 mysql mysql 9478 Jul 30 07:18 /var/lib/mysql/bsgtran/t2010_08.frm
-rw-rw----. 1 mysql mysql 1882892084 Jul 30 07:21 /var/lib/mysql/bsgtran/t2010_08.myd
-rw-rw----. 1 mysql mysql 74588160 Jul 30 07:22 /var/lib/mysql/bsgtran/t2010_08.myi

Since the database is on a Linux OS, filenames are case-sensitive.

Here is the solution

mv /var/lib/mysql/bsgtran/t2010_08.myd /var/lib/mysql/bsgtran/t2010_08.MYD
mv /var/lib/mysql/bsgtran/t2010_08.myi /var/lib/mysql/bsgtran/t2010_08.MYI

File extensions must be uppercase for MyISAM tables in Linux

Give it a Try !!!


Raster files into DB2 Team,

We have been loading shapefiles into DB2 using db2se import utilities.

I have been trying to find help to load Raster files and i wasnt able to find any help on this. it would be really helpful if I can get a guide on loading raster files into DB2.

Once you enable DB2 as an Enterprise GEoDB, it should work.
The steps to enable using Arcgis desktop tool

http://resources.arcgis.com/en/help/main/10.1/index.html#//0029000000sn000000


Update all geometry points by adding/subtracting from XY I want to update the POINT geometry column for all rows in a table.

For example, I want to change POINT(1 1) to POINT(1.1 .9)

UPDATE myTable SET geoPoint = asText(geoPointX+1 geoPoint Y-1)

The query is example of what I want to achieve, but I'm unsure how to split the text and then handle the values.

This makes absolutely no sense, but you would want to do something like this

UPDATE myTable
  SET geoPoint = Point( ST_X(geoPoint)+0.1, ST_Y(geoPoint)-0.1 );

This will move absolutely in the base unit of the SRS.


Does a full transaction log cause a rollback? Will a transaction that is trying to write something into the transaction log be rolled back if the transaction log is full?

Yes but it will be the rollback which actually causes the log to become physically full.

As a transaction generates log records, it will also reserve space in the log for any records required required to rollback. With 20MB of log space remaining and a hypothetical transaction that generates 10MB of log records, you would receive the log full error with 10MB still free and the rollback would then consume that 10MB.


Best way to design this mileage table? I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.

It's currently set up as so:

  id               (primary_key)
, truck_num
, start_date
, end_date
, start_miles
, end_miles
, start_fuel
, end_fuel
, miles
, gals
, mpg

There seems to be some redundancy. The miles is  (end_miles - start_miles), ditto for gals.

Should we have those miles and gals columns precalculated and stored in the database? It would definitely make querying easier, but at the expense of space. Same question for having the mpg calculated. A computed column would slow things down, no?

What indexes would work best? There's about 3,000 trucks (records) inserted in a batch every week.

I'm using SQL Server 2008 R2.

Edit: A sample query that would I'd be using

-- find average mpg for since ytd
select m.truck_num, avg(mpg)
from mpg m
join truck t on t.truck_num = m.truck_num
where start_date >= @begin_of_year and end_date <= @today
group by truck_num

Computed columns are your friend.  Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme.  You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

Create a test table
Pump it full of test data
Create the indexes
Update statistics
Run your most common queries
Check their plans and run times


Does it make sense to use SQL Server's bracket notation in hand written code? Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.

When I first saw it, I though wow a reincarnation of the somewhat banned quoted identifier notation.

As far as I know it is a proprietary extension from Microsoft (meaning Oracle doesn't support it).

Looking at SQL Server there is no difference if you define a table like

CREATE TABLE [dbo].[Table_2] ([col1] [int], [col2] [int]);

or

CREATE TABLE dbo.Table_2 (col1 int, col2 int);

Its a matter of personal or corporate style. Be consistent.

Now if you want to migrate your database to Oracle, brackets are no option.

You can use the old quoted identifiers, but these are case sensitive which causes a lot of trouble.

Is it a good idea to remove all brackets from generated code, avoid using blanks, other special characters and reserved keywords for names and just code in a way that most DBMS understand?

Standard SQL uses the double quote " for quoted identifiers. SQL Server supports this using the QUOTED_IDENTIFIER option (ANSI_QUOTES in mySQL). Standard SQL improves portability in general  and in this case would port to Oracle. Similarly I'd change SQL keywords to upper case (Intermediate SQL-92 requirement) and expand int to INTEGER (entry level SQL-92 requirement).

Needlessly using quoted identifiers, whatever the flavour, should be avoided, IMO.


missing files, installing Oracle DB 12c on Oracle Linux 7.2 when I try to Install the Database I get this error message:

Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives.

I did try this twice with the same error message with the main installation packages on Oracle website and from the support page (latest Patchset)

The files I tried are:

unzip linuxamd64_12102_database_1of2.zip
unzip linuxamd64_12102_database_2of2.zip

and for the patchset:

unzip p21419221_121020_Linux-x86-64_1of10.zip
unzip p21419221_121020_Linux-x86-64_2of10.zip

and when I create the ISO on my Mac

hdiutil makehybrid -o OracleDatabase12cEE.iso database/ -iso -joliet

all seems to be OK

I mount the ISO as a virtual CD/DVD in VMware then mount it in linux

tried to run the installation from the drive and after copying  everything locally!
and it's the same error message!

BTW I did run:

# yum install oracle-rdbms-server-12cR1-preinstall -y

# yum update -y

before trying to install!

also I login with ssh from my Mac

also I checked that everyone is the same as this article!

am I missing something?

The following is the excerpt of runInstaller.sh file

# Abort the install, if install/.oui file is not found in the stage area
#
if [ ! -f $CMDDIR/install/.oui ]
then
  echo "Source location is incomplete. Make sure you have downloaded and extracted
all the relevant archives."
        exit 1
fi

#

Which clearly states that if you are missing the .oui file it returns the given message.

Generally, after extracting the downloaded zip files manually we get two directories like database and database(0). The .oui file is hidden so we don't see it using general ls command and File Explorer. You can see it using ls -a command in Linux.

Solution:

Goto the second directory database(0)/install/ and copy the .oui file to the database/install directory.

[oracle@testsrv Desktop]$ cp 'database(0)'/install/.oui database/install


Standard ways to return SQL Server database to a specific state What are the most efficient ways to save the exact state of a SQL server database and then be able to bring it back to that state at will?

I am testing code performance against a database and each test alters it so I would like to be able to (as quickly and easily as possible) return it back to its beginning state each time I want to test performance again. I expect to be doing a lot of this.

To be honest, a full backup will be the easiest and quickest way to do that.  Simply backup your database (a full backup) to the point you want to revert to, and then when you need to get back to that point, do a full restore.

Also, depending on your version and edition of SQL Server (currently not specified in your question), you could work with Database Snapshots, and use a snapshot to revert back to.

The above two solutions are quick and easy if you're wanting to revert the entire database back to a certain point (either at the full backup, or the snapshot).  But if you're only looking to revert a subset of your database back to a certain point in time (think: certain types of development, benchmarking, or demo'ing) then the easiest thing would probably be a reversion script to target your specified affected objects.

With a tiny database that you want to revert the entire thing back, the first two options would be quickest.  But if you are dealing with a relatively large database, where a restore is just not working for you timewise, then you could also consider a script to do the revert.  It all depends on what you're trying to do, how big the database is, and what you're willing to handle based on the clock vs. trouble of reverting.


One generic mapping table VS many specific mapping tables I'm trying to design database tables to track files uploaded by end users.
Files can be uploaded under different contexts.
Each context is a different table.

As an example:

An employee can upload multiple "receipt" files against a "weekly expense" entry.
An employee can upload multiple "photo" files against a "pets" entry.

The entities / tables in this (contrived) scenario are:

Employees (Id, Name)
Expenses (Id, Date, RequestedRefund, RequestedByEmployeeId)
Pets (Id, Name, Type, BelongsToEmployeeId)

I already have a table called Files which tracks information about the file itself:
Files (Id, Name, Size, Extension, Folder)

My question is around how to map a expense receipt upload  and pet photo upload against the file record. I know I can do this in 2 ways:

1st Option:

Have one generic mapping table: GenericFileMap (FileId, ContextId, Type)
where

FileId is the id of the file record
ContextId is the id of the context record that i'm trying to retrieve
Type is the field to describe the context itself

In this scenario, I'd fetch all expense receipts for an employee like so:

SELECT *
FROM Employee e
INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId
INNER JOIN GenericFileMap g ON g.ContextId = ex.Id AND g.Type = 'expense'
INNER JOIN Files f ON g.FileId = f.Id

2nd Option:

Have separate mapping table for each entity / context table:

ExpenseFiles (ExpenseId, FileId)
PetFiles (PetId, FileId)

The query for receipts would be:

SELECT *
FROM Employee e
INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId
INNER JOIN ExpenseFiles ef ON ef.ExpenseId = ex.Id
INNER JOIN Files f ON ef.FileId = f.Id

I believe the 1st option is not the "normalized" way to do things in a relational database. But the only reason i'm considering this is, in my particular situation, there are at least 15 different contexts from which a user can upload / attach files.

The only benefit i see from the 1st option is the time I'd save creating and duplicating tables and stored procedures (one each for CRUD) for each and every context.

However, it does seem that the 2nd option is the "right" way to do things.

My questions:

Which of the two options is the smarter approach?
Which of the two is better for performance in the long run (I'll definitely put in the extra effort needed if the second option will be more performant) ?

You don't want option # 1 because:

You might as well just place ContextId and Type into the Files table.
You would be better off with a TypeID TINYINT field (and the associated Type lookup table) so that you didn't need to do an inefficient string comparison each time.

What is probably best is a modified option # 2. Yes, start with the context-specific file map tables (e.g. ExpenseFiles, PetFiles, etc) but also add a ContextTypeID TINYINT column in the existing Files table. There should also be a new ContextType lookup table with ContextTypeID TINYINT NOT NULL as the Primary Key (but do not make it an IDENTITY column). You can add a Name column to the lookup table, and a Foreign Key from Files to ContextType on ContextTypeID. Having this new column in the Files table will make it much easier to determine which context-specific file map / property table the associated record is in.

The reason for going with option # 2, even if there are 15 different contexts, is that, inevitably, at least some of them will evolve and diverge over time. So you might have:

ExpenseFiles (ExpenseId, FileId, Date, Total)
PetFiles (PetId, FileId, Name, Age, AnimalType)


When to use multiple tables in DynamoDB? The DyanmoDB best practices make it clear that:

  You should maintain as few tables as possible in a DynamoDB application. Most well designed applications require only one table.

I find it amusing then that just about every single tutorial I've seen dealing with DyanmoDB has a multi-table design.

But what does this mean in practice?

Let's consider a simple application with three main entities: Users, Projects, and Documents. A User owns multiple projects, and a Project can have multiple Documents. We typically have to query on the Projects for a User, and on the Documents for a Project. Reads outnumber writes by a significant margin.

A naive tutorial's table design would use three tables:

Users
Hash key
user-id

Projects
Hash key       Global Index
project-id     user-id

Documents
Hash key       Global Index
document-id    project-id

We could pretty easily collapse Project and Document into one Documents table:

Documents
Hash key    Sort key        Global Index
project-id  document-id     user-id

But why stop there? Why not one table to rule them all? Since the User is the root of everything...

Users
Hash key    Sort key
user-id     aspect
---------   ---------
foo         user                   email: foo@bar.com ...
foo         project:1              title: "The Foo Project"
foo         project:1:document:2   document-id: 2     ...

Then we would have a Global Index on, say, the email field for user record lookups, and another on the document-id field for direct document lookups.

Is that how it's supposed to work? Is it legit to throw such wildly-divergent kinds of data into the same table? Or is the second, two-table design a better approach?

At what point would it be correct to add a second table?

Yes, it is legit to do what you are saying. Both are actually. There are some variables that you do not have in here and can help guide how the data model should be done.

What kind of scale are you looking to get to with this application and data model?
Of the application's access patterns, what is the ratio of reads between those patterns. Meaning which one is hit most over the others.
Of the access patterns you list, how many times per second are they performed?

For example, if 80% of all reads are to find the users on a project and that needs to happen 30,000/sec, but in your application not as many people will go that step further and find out the documents for the projects, then it is 20% of the overall reads and may only be 2000 reads/sec. That first one is the "hot path" of your application and should be optimized for.

Also think of it this way, with a non-relational database like DynamoDB, you can optimize for how your application uses and accesses the data and not like relational database where you have to worry a lot about how it is stored in the database.


T-SQL QUERY to Grant Access to Database How do I build a t-sql query to grant access to users on multiple databases in multiple instances?

eg:

Instance 1 - Database A
Instance 2 - Database B

How do I grant access to both databases to a single windows user?

My query should also check the SQL Instance if the database is present in that "Instance" before granting access.

You can use SQLCMD mode in SSMS (or SQLCMD itself) to do this.

The following query would connect to an instance, check for the database in question, create the login if it does not exist, and then connect to the database and add a user for that login.

:CONNECT InstanceName
GO
IF EXISTS (SELECT Name FROM sys.databases WHERE name = 'DBName')
    EXEC sp_executeSQL N'   IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = ''LOGIN_NAME'')
        CREATE LOGIN LOGIN_NAME FROM WINDOWS;

    USE DBName;
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''LOGIN_NAME'')
        CREATE USER LOGIN_NAME;'
GO

You can do other kinds of variable substitution to make things a little quicker, it's worth reading up at https://docs.microsoft.com/en-us/sql/relational-databases/scripting/edit-sqlcmd-scripts-with-query-editor on the things that you can do.


What happens when you change a heap into a clustered table in SQL Server? As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough space to accommodate this record and insert into it.
When you create a clustered index on it, it becomes a clustered table and the clustered index itself becomes the table. But as the clustered index and the original heap are two different structures, does SQL Server create a new structure (the clustered index) and moves everything from the help to the new structure and then drops the heap?
There are a lot of stuffs that can be defined on a table, like triggers, constrains, permissions etc. If my assumption is true, that means SQL Server also moves all these stuff to the new structure. I didn't find any related information in the documentation. Is my understanding correct?

Yes, when you create a clustered index on a heap the rows are all sorted moved to the new clustered index.  Any non-clustered indexes are rebuilt with the new clustered index key as the row locator.
It's the same table, though, so triggers, constraints, etc don't have to change.
The reverse is not true, however.  When you drop a clustered index on a table, the leaf-level pages of the clustered index are left in-place and they become the new heap.  Nonclustered indexes are still rebuilt as the row locator switches from the CI key to the rowid (file:page:slot).


Write latecy increased when changed SQL Server to Enterprise on AWS We have been using AWS - Rds SQL Server 2016 Standard Edition with db.m4.4xlarge db instance (16 CPU, 64GB mem). Today, we needed to upgrade db instance to a large capacity machine, (db.r3.8xlarge - 32 CPU, 244GB mem).

To do so:

we had to take snapshot of the db instance
restored the snapshot as a new db instance with that large instance.

We have done everything perfectly. After we upgrade the instance, we realised that sql queries are really really slow. I know its because the buffer cache and plan cache cleared bcs of this upgrade, but this is really a bad situation for a production db. Every single query, when first executed, reads from disk, not from buffer. We have thousands of queries coming from tens of applications, so should I need to wait for our latency goes down?

I know its because the buffer cache and plan cache cleared bcs of this upgrade

SQL Server is not my specialty, so I'll assume those things are true, but there's probably an even more significant issue involved, any time you create an RDS instance from a snapshot.

RDS is backed by EBS,¹ and EBS snapshots are backed by S3.  Instances spin up quickly because they don't wait for the entire snapshot to be loaded into S3 onto the volume.  All of the data is logically on the volume but not yet physically on the volume -- accessible, but not instantaneously accessible. It's lazily loaded in the background.  Reads of data that have not yet been loaded onto the volume have to first be read from S3 -- so you have a first-touch penalty when reading from the disk, any time the disk block being read is read for the first time.  It is not well-documented whether EBS is only reactive with the background loading, or would also go ahead and restore everything from S3 even for a volume not being accessed.

Either way, as you can imagine, this is suboptimal for a busy database.

The volume should eventually warm up and performance should rise to the level you expect, but the timing of this warmup is not documented.

Anecdotally, I have observed it to be in the 5 to 10 MiB/s range... or 18 to 36 GiB/hour... however, these results may have been subject to the observer effect, since I was deliberately trying to stimulate the recovery with sequential reads, not random reads.

A solution I have created for MySQL, which I recognize is not directly useful to you but may give you an idea, iterates all the tables and indexes, writing and running queries that attempt to scan each table and each index (writing queries that trigger the optimizer to use the index as a covering index), thus forcing them all to be read from disk, warming it up as quickly as possible.  The generated queries look similar to this:

SELECT DISTINCT LEFT(MD5(CONCAT(c1,c2,c3, ... cn)),1) FROM t1;

A query in this form will read all the rows from the table, but returns a result set of 16 (0-9, a-f) or 17 (+null) rows.  The resultset is unimportant, but passing the concatenated data to MD5() ensures no shortcuts for the optimizer -- a full table scan is forced.

See Initializing EBS Volumes for a description of the snapshot problem, but note that the techniques for warmup here won't help you since RDS does not provide access to the command line of the server.

See also this reddit discussion quoting AWS support which appears to confirm that RDS snapshots are indeed EBS snapshots, thus prone to this condition.

It'a also possible that you could hasten this warmup process by making a database backup by some native mechanism -- not an RDS shapshot.

¹ RDS is backed by EBS -- The probable exception to this is Aurora, which uses a proprietary storage system to create the replicated "Aurora cluster volume," which apparently doesn't use EBS.


How to give title to the result of a select query in postgresql? For example I want to do like this:

select id,name from first;

It should look like this:

The Query Result // this should be the title of the query
id name
1  Ali
2  Sami

If you use psql, you can change the field separator using \f and \a meta commands. I show an example below:

testdb=# SELECT * FROM first;
 id | name
----+------
  1 | Ali
  2 | Sami
(2 rows)

testdb=# \f ','
Field separator is ",".

testdb=# \a
Output format is unaligned.

testdb=# SELECT * FROM first;
id,name
1,Ali
2,Sami
(2 rows)

And, \C meta command can be added any titles.

testdb=# \C 'The Query Result'
Title is "The Query Result".

testdb=# SELECT * FROM first;
The Query Result
id,name
1,Ali
2,Sami
(2 rows)


Ignoring specified tables on replication primary I need to make a primary-secondary replication for some joomla portals. However, high traffic means that a lot of inserts, deletes and updates happen on the %_session tables, something that isn't really necessary to replicate.
So, I've added the following line to my primary's my.cnf file:
replicate-wild-ignore-table=%.%\_session

I've stopped and started the primary server, but when I check the bin.logs, I can see that all operations with _session tables are still recorded and prepared to be sent to the secondary.
So my question is, how do I force the primary to ignore any and all operations on tables that end in _session?

first of all, the replication variables have effect on the slave only when activated on a replicated "slave" server. you need to understand that the filtering rules on master differ from the ones on slave. on master you can choose only to log a whole db or not. on slave you have more options. here is described http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
and here: http://dev.mysql.com/doc/refman/5.5/en/replication-rules-table-options.html
I think, you want to skip replicating a set of tables with a given pattern on slave. So, the variables must be configured on the slave. Change the configuration file on the salve and add the db_name instead of % for db part.

  replicate-wild-ignore-table=db_name.%_session line

After that, validate that the variable is activated (is shown in the output) by running:

  show slave status \G

once the filter is activated, slave server will skip all the statements which meet the given pattern. but before doing any change read how MySQL handles replication rules because you can skip important tables if you don't understand the rules.


ORA-00905: missing keyword I am trying to run the below Pl/SQL statements on my Oracle DB 11g on Linux box, but getting the error "missing keyword". Please let me know if I miss anything.

BEGIN

FOR X in (select * from all_tables where owner in ('owner1', 'owner2')) LOOP

EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to myuser';

END LOOP;

end;
/

Error starting at line : 1 in command -
BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL')) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to ARCTOOLS212';
END LOOP;

end;

Error report

  ORA-00905: missing keyword ORA-06512: at line 3

  00000 -  "missing keyword"

  *Cause:

  *Action:

there's a space missing before to in 'to myuser'.

BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL'))
LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
        ||X.owner||'.'||X.table_name||
        ' to TESTUSER';
END LOOP;
END;
/


find if the column of the PostgreSQL table is empty with minimum cost I need to find if there are any values written to the column but with minimum cost.
There is a table with ~1B rows and about 100 columns with data. I need to find columns that have no data at all (all nulls) and delete them. If I query like "SELECT * FROM my_table WHERE my_column IS NOT NULL LIMIT 1" then it costs me 1-2 minutes and I'm looking for the faster solution.
As far as I know (but not sure), if there are no data at all for the column, there is some property in the database saying that the chunk or the table has no data for this column, at least this happens when adding a new column to the existing table, so the database doesn't update all existing rows if the default value of a new column is NULL. I wonder if there is a fast way to get a result based on this info.
P.S. I use timescaleDB extension and the PK is a timestamp, if this changes anything

As far as I know (but not sure), if there are no data at all for the column, there is some property in the database saying that the chunk or the table has no data for this column, at least this happens when adding a new column to the existing table, so the database doesn't update all existing rows if the default value of a new column is NULL

If the row data ends early, it assumes all column after that for that particular row are NULL, or in newer versions, are whatever DEFAULT was at the time the column was added.  But this says nothing at all about what might be in other rows.
You can't avoid scanning the table unless you a had a very strange constraint which forces the values to all be NULL.  But you should be able to scan the table just once, not once for every column.
select count(col1), count(co2), count(col3)...count(col100) from the_table;


Add Constraint to check value exists in a non-unique composite key column I have the following table, which has a composite primary key:

CREATE TABLE dbo.Seqs(
    SequenceKey nvarchar(10) NOT NULL,
    UsageSequence smallint NOT NULL,
    col1 bit NOT NULL,
    col2 nvarchar(30) NULL,
    CONSTRAINT PK_Seqs PRIMARY KEY CLUSTERED
    (
        SequenceKey ASC,
        UsageSequence ASC
    )
)

I'm looking to create the following table, with a 'SequenceKey' column that must only accept values that are present in the 'Seqs' table:

CREATE TABLE dbo.Sometbl (
    SomeID int IDENTITY(1,1) NOT NULL,
    Col1 int NOT NULL,
    Col2 nvarchar(100) NOT NULL,
    Col3 nvarchar(30) NULL,
    SequenceKey nvarchar(10) NOT NULL,
    CONSTRAINT PK_Sometbl PRIMARY KEY CLUSTERED (
        SomeID ASC
        )
    )

Given that the 'SequenceKey' in the 'Seqs' table is not unique (the primary key uniqueness is reliant on the 'UsageSequence' column), what is the best way to ensure data cannot be added or updated in 'Sometbl' with a 'SequenceKey' value that doesn't exist in the 'Seqs' table?

As a possible answer to my own question, this could be achieved by adding a Check constraint with a function such as the following:

CREATE FUNCTION dbo.CheckSequenceKey (@SequenceKey nvarchar(10))
RETURNS bit
AS
BEGIN
  DECLARE @retval bit

  IF EXISTS (SELECT 1 FROM dbo.Seqs S WHERE S.SequenceKey = @SequenceKey)
      SET @retval = 1
  ELSE
      SET @retval = 0

  RETURN @retval
END;
GO

ALTER TABLE dbo.Sometbl
  ADD CONSTRAINT chkSequenceKey
  CHECK (dbo.CheckSequenceKey(SequenceKey) = 1);

EDIT:

The Check constraint prevents inserts and updates to the 'Sometbl' table where the new value for 'SequenceKey' is not found within the 'Seqs' table and this is my preference over the use of a trigger.

However, the check constraint doesn't restrict updates to values in the 'SequenceKey' column in the 'Seqs' table or the deletion of entire rows from this table, which could lead to orphaned records in the 'Sometbl' table. Therefore I've taken the below from EzLo's answer to be applied in addition to the check contraint and function for a more complete solution:

Then for dbo.Seqs you can rollback operation that
attempts to update or delete if the SequenceKey is being
referenced:

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnDelete ON dbo.Seqs
    AFTER DELETE
AS
BEGIN

    IF EXISTS (
        SELECT
            'deleted records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t delete SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END

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

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnUpdate ON dbo.Seqs
    AFTER UPDATE
AS
BEGIN

    IF UPDATE(SequenceKey) AND EXISTS (
        SELECT
            'updated records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t update SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END


Will Postgres Deadlock on Single Update If the two updates given in https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-DEADLOCKS happen in one UPDATE query will it still result in deadlock?

Transaction 1 executes the following statement:

-- transaction 1
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum IN (11111, 22222);

Transaction 2 executes the following statement:

-- transaction 2; note the acctnum order is different
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum IN (22222, 11111);

Please note that acctnum order provided in queries is different. Please ignore the fact that account balance will be incorrect. I am interested in the behavior as I have a similar situation.

The order of the values in the in clause is not significant.

the rows may be visited in any order, this order can change at any time.

however with such a small set of targets and in the presence of an ordered index (not a hash index) the records will probably be visited in index order.