How to model Parent -> Child -> Grandchild if the Child can be removed I'm dealing with a set of relationships where the child can be removed, but I obviously don't want to lose the connection between the grandchild and the parent. I did think about marking the child as "deceased" (to use relevant terminology for this post), but then I would be stuck with a bunch of deceased children in my DB eventually and who wants that (just for the purpose of preserving the relationship)?

If a parent is deleted, all its descendants are deleted. Furthermore, it works like a "normal" relationship, where the grandchild and child always have the same top level parent. The hierarchy is fixed in 3 levels (as shown above). Finally, the Parent, Child, and Grandchild are all of different types (e.g. we're not talking about 3 "humans", they don't have the same base).

However, it feels a bit odd to have the grandchild track the parent since that relationship can usually be derived from the parent-child relationship. Though, I cannot think of another way of doing it.

Is this model valid? Or is there a different way of doing it?

This answer is based upon your question as it stood before the clarification about each level being a different type.   Since you have identified the need for different types, I agree with my answer as it originally appeared, and your self-answer documenting how you've approached this problem.  Adding a single column to the grandchild table, referencing the top-most table, seems the simplest approach.

I'm leaving the below details in-place in case it helps a future visitor.

I would implement this with a cross reference table.

Below is a SQL Server-specific example; this table contains columns about the entities, including name, etc:

CREATE TABLE dbo.Entities
(
    EntityID int NOT NULL
        CONSTRAINT PK_Entities
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EntityName varchar(30) NOT NULL
);

This table describes their relationships:

CREATE TABLE dbo.EntityRelationships
(
      EntityIDParent int NOT NULL
        CONSTRAINT FK_EntityRelationships_Parent
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , EntityIDChild int NOT NULL
        CONSTRAINT FK_EntityRelationships_Child
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , CONSTRAINT PK_EntityRelationships
        PRIMARY KEY CLUSTERED (EntityIDParent, EntityIDChild)
    , CONSTRAINT CK_EntitytRelationships
        CHECK ((EntityIDParent <> EntityIDChild))
);

Each relationship must be unique, that is any given parent can only be related to any given child once.

Next, we create an INSTEAD OF DELETE trigger on the Entities table that will handle deletes properly, by reparenting any relationships necessary, prior to removing the deleted Entity:

CREATE TRIGGER EntityRelationshipDelete
ON dbo.Entities
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
    SELECT erp.EntityIDParent
        , erc.EntityIDChild
    FROM deleted d
        INNER JOIN dbo.EntityRelationships erp ON d.EntityID = erp.EntityIDChild
        INNER JOIN dbo.EntityRelationships erc ON d.EntityID = erc.EntityIDParent
    EXCEPT --don't create duplicate entries
    SELECT er.EntityIDParent, er.EntityIDChild
    FROM dbo.EntityRelationships er;

    DELETE
    FROM dbo.EntityRelationships
    FROM dbo.EntityRelationships er
        INNER JOIN deleted d ON er.EntityIDChild = d.EntityID OR er.EntityIDParent = d.EntityID;

    DELETE
    FROM dbo.Entities
    FROM dbo.Entities e
        INNER JOIN deleted d ON e.EntityID = d.EntityID;
END;
GO

Here we'll test that setup:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent')
    , ('Parent')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 2)
    , (2, 3);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

The results of the select above:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Parent     ║
║ Parent      ║ Child      ║
╚═════════════╩════════════╝

Here, we'll delete the "Parent" entity, and re-query the relationships:

DELETE
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent';

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

The result:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Child      ║
╚═════════════╩════════════╝

Note that running DELETE FROM dbo.Entities (with no WHERE clause) will delete all rows from both tables.

To show a slight more complex example; imagine you have 2 grandparents, 2 parents, and single child:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent 1')
    , ('Grandparent 2')
    , ('Parent 1')
    , ('Parent 2')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 3)
    , (2, 3)
    , (1, 4)
    , (3, 5)
    , (4, 5);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 1   ║
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 2 ║ Parent 1   ║
║ Parent 1      ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

If we remove Parent 1 from the Entities table:

DELETE
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent 1';

we see this:

╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 1 ║ Child      ║
║ Grandparent 2 ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

This performs a cleanup of our test data:

IF OBJECT_ID(N'dbo.EntityRelationships', N'U') IS NOT NULL
DROP TABLE dbo.EntityRelationships;

IF OBJECT_ID(N'dbo.Entities', N'U') IS NOT NULL
DROP TABLE dbo.Entities;
GO


Selecting the first purchase item of each customer I have a table like this:

CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);

INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (1111,'17-JAN-2011',4002,'DRY FRUIT');
INSERT INTO SALES VALUES (1111,'20-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'03-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'04-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (2222,'05-FEB-2011',4005,'APPLE');
INSERT INTO SALES VALUES (2222,'06-FEB-2011',4001,'LUX');
INSERT INTO SALES VALUES (2222,'09-FEB-2011',4006,'ORS');

INSERT INTO SALES VALUES (3333,'03-FEB-2009',4007,'CHOCOLATE');
INSERT INTO SALES VALUES (3333,'09-MAR-2011',4001,'LUX');
INSERT INTO SALES VALUES (3333,'15-APR-2011',4008,'MILK');
INSERT INTO SALES VALUES (3333,'03-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (4444,'11-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (4444,'19-JAN-2011',4010,'SUGAR');
INSERT INTO SALES VALUES (4444,'21-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (4444,'05-JAN-2011',4005,'APPLE');
INSERT INTO SALES VALUES (4444,'15-JAN-2011',4001,'LUX');

INSERT INTO SALES VALUES (5555,'01-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (5555,'03-JAN-2011',4010,'TELENOR CARD');
INSERT INTO SALES VALUES (5555,'22-JAN-2011',4005,'APPLE');

I need to fetch the first purchased item name by all the id individually.

How can I do this?

select cid,
       pdate,
       itemcode,
       itemname
from (
  select sales.*,
         rank() over (partition by cid order by pdate) as rnk
  from sales
) t
where rnk = 1
order by cid;

Alternatively

select s1.cid,
       s1.pdate,
       s1.itemcode,
       s1.itemname
from sales s1
where pdate = (select min(s2.pdate)
               from sales s2
               where s1.cid = s2.cid)
order by cid;


Restoring only differences with SQL differential backups? I find something similar here but it was asked almost 10 years ago and by that time there was no option. I wonder if there is any now.
I am doing some actions on application and getting back ups before and after but when I want to restore the db to roll back to previous version, it cost a lot of time and distraction. I wonder if there is any quick way to get differential back ups like myDB_v1, myDB_v2, v3 etc.. and restore between them quickly.
Thanks in advance.

The question you asked
No. It is not an option to apply just a differential backup, without the full backup (aka the differential base backup).
You want to essentially undo changes made after the backup, and differentials cannot do this. They are designed to work in the other direction--they "fast forward" changes made since the prior full backup, allowing you to get to the end state quicker, without having to redo the actual changes along the way.
A differential backup contains only the data pages that have changed since the last full backup. Your scenario would require undoing pages that are changed after the differential backup is taken--the differential has no idea what will change in the future, so it likely doesn't have the data to undo those changes. In order to do what you suggest, you need a backup with every data page, so that you can undo any possible changes--the mechanism for this is a Full backup.
The question you should have asked
It sounds like you want to look at database snapshots.
Snapshots are essentially that inverse of differentials that you're looking for. They keep track of changes since the snapshot creation, so that the snapshot shows the original version, regardless of what happens to the real database. Snapshots can also be used to roll back to the point in time when they were created.
From the docs:

In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward

It's important to note that snapshots are not backups. They can be used to revert data changes, but they are not helpful if the original/real database becomes corrupt, or goes offline.
You'll want to read up on reverting to a snapshot to ensure that it does meet your needs, and that the various limitations work for your scenario. In particular, in order to revert to a snapshot, you'll need to drop other snapshots first.
You mention "...myDB_v1, myDB_v2, v3 etc.. and restore between them quickly." Snapshots would allow you to actively query all versions simultaneously, but when you want to revert your "real" database to one of those versions, that process would involve dropping all other snapshot versions.


Is it okay to have a fill factor of 100 on the clustered index that is on an ID column? So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server.

Is it okay to have a fill factor of 100 on this since the data will always get written with the next latest ID? Would it matter if the other columns on this table get updated a lot for the existing IDs?

Also, how would deletes impact the fragmentation of the table?

In most cases you do want a FILLFACTOR of 100. Only in special cases would you want to lower that value. Updates only cause page-splits if they increase the size of the row such that it won't still fit on the same data page. Deletes here and there shouldn't matter in either case. In all cases, you need to do regular index maintenance and REBUILD the clustered index when fragmentation is over a certain limit. Rebuild will re-sort all of the data to be in the ideal order, so it will fill in voids left by DELETE operations and UPDATE operations that caused page splits where there is still available space on the old and/or new data pages.

A lot of deletes can leave empty pages and hence some fragmentation, but using a lower FILLFACTOR isn't going to help there. And in fact, depending on what rows are being deleted, you could be increasing empty page fragmentation since fewer rows will be on each data page in the first place.

But keep in mind:

Using a FILLFACTOR of 100 doesn't mean that 100% of the space is used. Rows are fit into the data page as long as there is space for the entire row. If you have 100 bytes left over and have a 105 byte row to add, it can't fit there. But there is still space for an update of a variable length column to increase by 50 bytes.
When you set FILLFACTOR to less than 100, you are reserving space on all data pages, not just the ones that will be getting updated. Unless you have a usage pattern that evenly distributes updates across the entire range of the table/index, why reserve space on data pages that contain "older" rows that aren't being updated? That just slows down queries across the entire index / table in the hopes that some of the newer data pages (i.e. rows more likely to be updated) won't page split as early as they would when using a FILLFACTOR of 100.
Consider the size of your rows and how many will fit onto a data page when FILLFACTOR is 100, and how many will fit onto a data page with a lower FILLFACTOR such as 90, or even 80. If you have wide rows of 500 - 1000 bytes, the difference between various FILLFACTOR values might be just one or a few rows. What types of updates are happening? If updates are to fixed-length columns, this isn't even an issue. For variable-length columns, can they be increasing in size by 100 - 500 bytes?

There are several factors to consider here to be truly pragmatic: if, in the course of a day or week (how long between REBUILD operations?) successive UPDATE operations on rows that sort close together are going to expand in size and cause multiple page splits, then is postponing that page split by several hours or a day really worth the impact of reserving that space across the entire table / index such that it takes up more disk space, space in memory (i.e. Buffer Pool), makes backups larger, takes longer to read from disk into the Buffer Pool, take longer to do the index maintenance, etc?
Fragmentation adversely affects range queries, not singleton queries. If you are fetching individual rows (or finding rows to be updated or deleted), you likely won't see the impact of fragmentation, at least not until it is really bad, but maybe not at all.

As with many / most technical decisions, there are various technicalities that influence what is the "best" or "optimal" approach. So you really need to consider the max size of the row, if the updates will happen to variable-length columns, if the update activity is going to be mostly spread across the table / index or confined to a more "recent" segment of it, etc. I have worked in places where the policy was for all tables to have a FILLFACTOR of 80 and it was an insane (and irresponsible) practice given that we were using IDENTITY columns for the clustered indexes (usually PKs) and "older" data rarely ever got updated. So we wasted tons of expensive SAN space on many tables -- often containing a million rows -- that then bloated the backups (and made both backup operations and restore operations take longer!) and chewed up more memory, which either force cached plans to get evicted or cached data pages (i.e. Page Life Expectancy was quite low).

ERGO: Start with 100 and experiment with lowering in small amounts IF / WHEN you think an optimization might be warranted (i.e. if you find that fragmentation on a particular table is growing to higher levels between rebuilds AND that is also causing performance degradation!).


Alter multiple Logins through SQL I am trying to change the following properties for SQL users to disable password complexity and expiration.

Using:

ALTER LOGIN xx WITH
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Where 'xx' is an exact username works perfectly.

However, I would like to update an entire group of users that match a string such as:

SELECT FROM sys.sql_logins where is_policy_checked = '1' and name like '%cpsuser%'

So I attempted:

ALTER LOGIN FROM sys.sql_logins where is_policy_checked = '1' and name like '%cpsuser%' WITH
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

But get an error that SQL doesn't recognize 'FROM'.

Is my Logic incorrect?
This is MySQL 2008.

The following query will provide a list of statements you can copy-and-paste into a new query window if you need to perform this task once:

SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + '
    WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;'
    , *
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
    AND sp.is_disabled = 0
ORDER BY sp.name;

If you need to do this a lot, you could use dynamic SQL like this:

DECLARE @cmd NVARCHAR(MAX);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + '
    WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;'
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
    AND sp.is_disabled = 0
ORDER BY sp.name;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);
    --EXEC sp_executesql @cmd; /* uncomment this to actually modify logins */
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The standard warnings apply about doing things like this in an automated fashion.  Test it in a non-production environment first, and roll this out to production with extreme caution.


Oracle 11g - PL/SQL block - Unable to use a newly added column in subsequent DML begin
   execute immedate 'alter table my_table add new_column varchar2(30)';
   update my_table set new_column=old_column;
end;

Here the update statement fails stating that the new_column is not available in the table. I knew DDLs are implicity committed but just for thoroughness sake, I added an explicit commit statement before the update statement, but the error still exists. Any inputs is much appreciated.

The column does not exist at compile time. Try this:

begin
   execute immediate 'alter table my_table add new_column varchar2(30)';
   execute immediate 'update my_table set new_column=old_column';
end;


How much time will a vacuum/autovacuum operation take? I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other few inserts and large number of updates.

Database runs on PostgreSQL 8.4 on a Debian 6.0 amd64 system with 16 gigabytes of RAM.

The question is sometimes autovacuum process on a table, takes a very long time (days) to complete. I want to be able to roughly tell how much time a particular vacuum command will take, to be able to decide whether to cancel it or not. Also if there were a progress indicator for postgres vacuum operations, it would be really helpful.

Edit:

I'm not looking for a bullet-proof solution. Just a rough hint on the number of dead tuples or necessary I/O bytes is enough to decide. It is really annoying to have no clue when VACUUM will finish, whatsoever.

I've seen that pg_catalog.pg_stat_all_tables has a column for number of dead tuples. So it is possible to have an estimation, even if it means one has to ANALYZE the table before. On the other hand, autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor settings alone prove that postgres itself knows something about the amount of change on the tables and probably puts it in the hands of the DBA too.

I'm not sure what query to run, because when I run VACUUM VERBOSE, I see that not only tables, but indexes on them are being processed too.

On my PostgreSQL (8.3) I use this trick:

I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read.
I run VACUUM on the table.
I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity).
In Linux shell I run while true; do cat /proc/123/io | grep read_bytes; sleep 60; done (where 123 is the pid) - this shows me bytes read by the process from the disk so far.

This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM. I presume that the VACUUM must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.

I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes field is good enough to be used as a progress counter.

Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.


foreign key violation within trigger on insert of 'new' id in other table as foreign key Within a trigger I am inserting the id of the table the trigger is assigned to as a foreign key in another table, but the 'new' id doesn't exist yet, so I get a foreign key constraint violation.

Here is a simplified version:

  CREATE or replace FUNCTION trg_func()
            RETURNS trigger
            LANGUAGE 'plpgsql'
        AS $BODY$
        DECLARE

        BEGIN

        INSERT INTO other_tbl (
        my_tbl_fk
        ,my_calc
        )
    SELECT new.id -- <- this one is the issue. Is fk in other_tbl & pk in my_tbl
        ,a.some_val / b.some_val calc
    FROM (...subquery here...) a
        ,(...subquery here...) b
    WHERE a.some_id = b.some_id;

        RETURN new ;
        END ;
        $BODY$ ;

        CREATE TRIGGER my_tbl_ins_upd_trg
            BEFORE INSERT OR UPDATE
            ON my_tbl
        FOR EACH ROW
        EXECUTE PROCEDURE trg_func();

I tried temporarily removing the fk before the insert then adding it back, but it doesn't seem to help.

I can remove the fk, trigger the trigger, then add the fk back again, so I know my insert values don't violate the constraint if applied generally.

I am guessing there is a method or strategy for doing this because it seems like something that would be done a lot for calculated fields via trigger.

I am trying to do the 'Discarding 3NF With Triggers' section of this link:
http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

PostgreSQL 9.3

That should be simply achieved by setting the foreign key constraints to be checked at the end of the transaction. This is achieved by means of a SET CONSTRAINTS ALL DEFERRED (or, at least, SET CONSTRAINTS name_of_fk_constraint DEFERRED.

As per PostgreSQL (9.3) documentation about SET CONSTRAINTS:

  SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

If you want to use this feature, you need to create your constraints with the DEFERRABLE option. If you intend to use them mostly in deferred mode, it is a good idea to make them INTIALLY DEFERRED. By default, constraints are NOT DEFERRABLE.

It is advisable to make constraint names unique (PostgreSQL doesn't force you to; they only need to be unique per table), and to create the names explictily. This way, you can control its deferrability with guarantee that you always specify the right one.

If you want a specific FK constraint to behave already as DEFERRED, you can declare it with:

ALTER TABLE table_a
    ADD CONSTRAINT table_a_col_name_table_b_fk FOREIGN KEY (col_name)
    REFERENCES table_b (col_name)
    ON UPDATE NO ACTION   /* or whichever action is deemed appropriate */
    ON DELETE NO ACTION   /* or whichever action is deemed appropriate */
    DEFERRABLE INITIALLY DEFERRED;

This way, the FK constraint is checked, by default, at the end of the transaction, and the SET CONSTRAINT isn't needed.


Does MariaDB 5.5.56 have INFORMATION_SCHEMA.INNODB_METRICS? I'm trying to debug why I don't have INFORMATION_SCHEMA.INNODB_METRICS. I'm uncertain because I see it in the MySQL 5.5 reference glossary: https://dev.mysql.com/doc/search/?d=10&p=1&q=innodb_metrics.

mysql --version
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

When I try to configure it through my.cnf I get:

mysqld: unknown variable "innodb_monitor_enable"

When I try to configure it through the MySQL CLI I get:

MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_rw_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_nl_ro_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_ro_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_rollbacks;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'

My guess is that it was introduced in 5.6 because
https://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-metrics-table.html redirects to 8.0 and 5.6 works.

Additional info

Turns out that, as shown below

SELECT VERSION()
    -> ;
+----------------+
| VERSION()      |
+----------------+
| 5.5.56-MariaDB |
+----------------+

...I was looking in the wrong place. I actually am using MariaDB as the database management system, and @danblack pointed out it was released in 10.

Your version string indicate your client version is MariaDB. Making the assumption that your server is too and looking at the MariaDB kb INFORMATION_SCHEMA.INNODB_METRICS was added in 10.0.


Sequence according to value from table in postgresql I have dictionary_type and dictionary_value tables. Value is connected with type. I would like to create sequence which will set the id of values according to type id.

Expected dictionary_type:

id       type
----     ----
1        type1
2        type2

Expected dictionary_value:

id       type_id     value
-------  -------  -------
100      1         value1
200      2         value2
101      1         value3
102      1         value4
201      2         value5

So baiscly I would like to make it like:
nextval(sequence(type_id * 100)). How can I do it in postgresql?

Have a nice day!

If I understand correctly you want to have ids that depend on another column. I'm sorry not to answer your question, because I don't understand that need. Why would you need that ?

The only purpose of a sequence is giving unique values. What's wrong with that kind of data?

id       type_id     value
-------  -------  -------
1        1         value1
2        2         value2
3        1         value3
4        1         value4
5        2         value5

If your problem is that you want to be able to sort your data by type_id and then by id, just try that query :

select id,
  type_id,
  value
from my_table
order by type_id, id


Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server? I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com.
RPE uses a SQL Server database.

Their old POS server had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data.
However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.

I was able to copy the C:\Program Files\Microsoft SQL Server\ folder which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1.

I installed the RPE software on the new computer and a SQL Server instance called MSSQL10_50.PCAMERICA.
The new computer is using SQL Server 2008 R2, while I believe the old computer would have been using SQL Server 2005.

I am not a DBA, especially when it comes to SQL Server.

Is is possible to 'recover' the database from the 'raw' .mdf and .ldf files from the old computer to the new computer / SQL Server instance?
If so, how should I proceed?

Please pay attention. The answer from Julien is correct. BUT if you attach the database to an SQL Server 2008 instance it will be upgraded! If the application can't handle SQL Server 2008 and needs SQL Server 2005 you're in trouble. Take an extra step and make a backup.

Furthermore you have to create the needed logins on the new server. You might want to attach the old master under a new name for looking up the details.


Postgres advisory lock when key doesn't fit I've identified that the Postgres advisory lock would be a good match for my use-case (being bound to the current session/transaction) and thus I don't want to use e.g. Redis.

The advisory lock functions are documented at https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS having two variants: (bigint) or (int, int).

I've a resource which is 512 bit (a VARHCHAR(64) column, actually). The column is part of a table where I cannot just directly lock the row. The resource being part of that row is just one aspect.

One of the goals here is that I can detect the lock from without other parts of the application: i.e. the application must not hang/wait for the lock but give me back whether the resource is currently locked or not.

Is there a way to express an advisory lock on a resource requiring more storage?  This is a problem because I don't have any unique key that I could use for this purpose.

I'm using Postgres 9.6.3.

The usual way of solving such issues is to use a hash function that would produce a (nearly-)unique value that fits your purposes - in this case a pair of integers or a single bigint.

Unfortunately, to my knowledge there is no such built-in hash function in PostgreSQL.  You can adopt different solutions though:

use the built-in md5() and truncate it to bigint
find a 3rd party solution, like hash64 (warning: I haven't tried this yet, so no idea if it is a good hash function or not)


Return ONE Row For EmpName My tables may or may not have the same employee Names listed in them.  The problem is I have no way of knowing if Table1 will hold more names or if Table2 will hold more names so I was thinking a union would solve this issue.  However as you see in the syntax below it produces to lines for "C" since it exists in both tables.  This is my desired output

empName | TotalSW | TotalSNT
--------+---------+---------
A       |       1 |       0
B       |       1 |       0
C       |       1 |       1
x       |       0 |       1
z       |       0 |       1

With the DDL Below how can a query be written to produce this output?

Create Table #Test1
(empName varchar(100), swas varchar(100))

Create Table #Test2
(empName varchar(100), swont varchar(100))

Insert Into #Test1 (empName, swas) VALUES
('A', 'res1'), ('B', 'tim1'), ('C', 'run34')

Insert Into #Test2 (empName, swont) VALUES
('C', 'er12'), ('z', 'nn12'), ('x', '23rw')

Select empName, TotalSW = COUNT(swas), TotalSNT = ''
FROM #Test1
GROUP BY empName
Union
Select empName, TotalSW = '', TotalSNT = Count(swont)
FROM #Test2
GROUP BY empName

There are a couple of ways you could do this. You could use a FULL OUTER JOIN which will pull data:

SELECT ISNULL(#Test1.empName, #Test2.empName)
     , TotalSW  = COUNT(#Test1.empName)
     , TotalSNT = COUNT(#Test2.empName)
FROM #Test1
FULL OUTER JOIN #Test2 ON #Test2.empName = #Test1.EmpName
GROUP BY ISNULL(#Test1.empName, #Test2.empName)

(NOTE: the previous example that used SUM over a CASE statement isn't needed as COUNT does not count NULL values which is what the extra jiggery-pokery was trying to do)

or to keep the UNION method (which may be more or less efficient in more complex examples depending on the rest of the query):

SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        ) AS sq
GROUP BY empName

Note the use of UNION ALL not just UNION - otherwise it will filter out duplicates if an employee exists more than once in one table. UNION ALL is also often more efficient because it possibly avoids extra sort operations so unless you need duplicate removal you should use it instead of plain UNION.

Or if you find the CTE syntax more readable than sub-queries (which it often is in more complex examples, though it makes no difference here IMO):

WITH Emps AS (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        )
SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   Emps
GROUP BY empName


Postgresql synchronous_commit off and select queries If data is inserted into a postgresql database using asynchronous commit, is it possible for a subsequent select query to guarantee that the data it reads has been written to disk?

I am storing generated data in postgresql and since the data can be regenerated, I would like to take advantage of asynchronous commit to increase throughout. But the generated data is periodically synchronised with an external system and it is important that data that has been sent to the external system is not lost.

Have the sync program update a counter or timestamp in some dummy table and commit the update:

begin;
select * from async_inserted;
-- maybe do something in the external system here
update dummy set synctime=now();
commit;
-- or maybe do something external here instead

Of course if the update or commit fails, that could still leave you not knowing what state the external system should be in.  You might need to use prepared transactions (aka "2 phase commit") to bond the two systems together atomically.


Event 4104, MSDTC Client 2 I am using Idera SQL Diagnostic Manager to monitor a Windows Server Failover Cluster with two SQL Server 2008 R2 clusters configured to run with one SQL Server cluster on each node. It just started reporting that one of the instances does not have DTC or SQL Server Agent installed.

I've RDP'd to the cluster, and both nodes. In the cluster manager I see the DTC node running and is owned by the "A" node. On the "A" node, there are two services called "Distributed Transaction Coordinator." On the "B" node (which is the node on which the instance throwing the error on SQL Diagnostic Manager) there is only one service (in services.msc) called "Distributed Transaction Coordinator."  There is an error in the event log that says:

  Failed trying to get the state of the cluster node: SERVER-DTC.The
  error code returned: 0x80070005

This cluster node is on the "A" node along with a SQL Server instance. The "B" node has this error. I can list and enumerate jobs in SSMS in the instance that is on the "B" node, but the SQL Server Agent does not have the little green triangle in it (just the empty circle).

So, I don't know exactly what to do. It seems like things are working OK, but this message from Idera SQL Diagnostic Manager has me perplexed since it is new (the error).

Running:

EXEC master..xp_servicecontrol N'QUERYSTATE', 'MSDTC'

on both nodes results in "Running".

Typically, 0x80070005 is an Access Denied error.  Perhaps the account Idera SQL Diagnostic Manager is using no longer has access to the Service Control Manager on the Windows nodes where the errors are occurring.


How to force SQL server to use row locking for specific update/delete statements I read this great response to a similar question How to force the use of row locks but I was looking for a per update/delete statement instead of permanently putting the rowlock restriction on an index.

My problem is that I have multiple sessions running the same scripts and updating the same tables giving us some deadlocks...  And we know for a fact that there will never be any conflicting rows between different sessions. The problem seems to be caused by page locking (when a page contains data that confilcts between 2 sessions) I've tried using my update and delete statements WITH (ROWLOCK) hints but when analyzing the locks (starting a transaction and statement and then verifying sp_lock in another session), I see absolutely no difference between specifying and not specifying the WITH (ROWLOCK) hint.

any ideas to resolve this issue are welcome.

Edited for more info:
I ran different scripts testing different scenarios with and without the "With(ROWLOCK)"... the test scenarios I've tested are:

-- deleting 9 rows of 4600 - no difference between with or without rowlock hint
-- 9 row locks and 3 page locks
-- COL1 is part of the combined PK
-- DELETE FROM RPT_CHK WHERE COL1='RPT_LAST'
-- DELETE FROM RPT_CHK with(rowlock) WHERE COL1='RPT_LAST'

-- updating 16000 rows - no difference between with or without rowlock hint
-- table lock in both cases (which is understandable)
-- update price with (rowlock) set cost = cost + 0.05
-- update price set cost = cost + 0.05

-- no difference between with or without rowlock hint
-- 3833 rows -- 4028 locks (row and page Locks)
-- update client set COL1 = 'a' where ClientId <= 3833
-- update client with (rowlock) set COL1 = 'a'  where ClientId <= 3833

I know you said with (rowlock) did not appear to be working
Are you including a <> for the set as then it will avoid an update lock

update table with (rowlock)
set col1 = 1
where col1 <> 1

with (tablock holdlock) might fix deadlock
as then you only have one update at a time
it is worth a try

Are your transactions updating tables in the same order?


MySQL: IF statement check is a table exists I run in the following issue and can't figure out how to resolve it.

delimiter #
create procedure pr1()
    begin

        declare v1 double default 0;

        start transaction;
            IF "table1" in (Select `information_schema`.`TABLES`.`TABLE_NAME` FROM `information_schema`.`TABLES`) THEN
                set v1 = (select count(value) from `table1`); -- error here

            ELSE
                -- create table by calling a script

            END IF;

        END #

    delimiter ;

It doesn't go in that branch of the if statement in case the table doesn't exist. Is there any suggestions how to solve this issue or any potential work-around?

You probably want to use this syntax:

CREATE TABLE IF NOT EXISTS `table1` ( -- create table );
-- returns only a warning if the table already exists

Unless you want to check that the table name does not exist instance-wide, in which the way you "program with SQL" should be a bit different, for several reasons:

SET @check_table_name := false;

SELECT true INTO @check_table_name
FROM information_schema.TABLES
WHERE TABLE_NAME = 'table1'
LIMIT 1;


Unique over 2 columns I have 2 columns col1 and col2

INITIAL QUESTION:
is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes?

The Question has been edited to:
"Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?"

But it is my fault, I did not give enough details.
When I have a unique column. I can not repeat a entry in this column.
What I need is to expand this "unique" to a second column, so that any entry made in the first column, should not be possible entered to the second column at all.

I can do it using a external program by consequently adding the entries for this two columns in a array and always checking the array before entering something to this two columns. But the solution a joke, it is sub-optimal, because by extrapolation we do not need databases any more we can use arrays.

Is this possible using just mysql, or do I need "my array" ?

I guess you can use CHECK constraint

ALTER TABLE foo
  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;


Directory Verification with For Each Loop in SSIS How can we check if a directory is valid or not inside for each loop . I am having multiple files at some location and i want to load that file but before this i
 want to check if the directory is correct or not .
If the directory is not correct it will throw error for wrong directory.

I am passing Directory path in config file.

to avoid this situation do i need to write script task for this

Unexpected behavior of Foreach Loop in SSIS 2012

if you have the right amount of permissions you can use something like the function below to check if a file exists.

I have not tried to check if a folder exists though.

USE MASTER
GO

create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

this is an example as how to call the above function:

--=============================================
-- get the latest Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [LATEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MAX(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)


Creating Temporal table without adding DateTime columns I'm an SQL server DBA and new to Temporal tables. We have the following issue:
As part of implementing temporal tables mechanism , there's a need to add to datetime columns (so DWH will have the ability to investigate only last modified columns).

1st problem is that our R&D are trying to avoid from altering table structure (adding columns) as it is a sensitive and big table.

2nd problem is that the original table contains MODIF DATE column.
This column is being updated at the datetime value of the update transaction beginning time warriors I need the COMMIT time.(gaps are critical)

My question:
Is there a possibility to turn the original table to Temporal one , without adding columns and only by using the CREATION date and MODIF date built-in columns?
Will appreciate any support here!

If you're using System-Versioned Temporal Tables then I believe you need not change your table schema. Rather separate tables are created to automatically manage history on your existing tables.
There's also alternatives in SQL Server that shouldn't require you to change your existing table schema either such as Change Tracking or Change Data Capture (CDC).


How could you implement a custom sort that groups matching records from other source This is obviously simplified.  I have 2 applications:  Driver and Match.  I'm presenting data to users in this order and asking them to select which of the people in the Match application is truly a match for the person in the Driver application.
Data needs to be ordered as you see below:  Driver rows need to be in alpha order by PersonName.  Below each Driver record, list the Match records that have the same PersonMatchID, in PersonName alpha order.  Some Driver records will have no corresponding Match records.

If you started with the rows below in random order, how would you write a SQL query to return them in this order?  I am dealing with about 3000 rows at most, so I don't have to worry overmuch about optimization.

Source-system    PersonName    PersonPossibleMatchID
-------------    ----------    ---------------------
Driver           AA            77777
  Match          AA            77777
Driver           AB            11111
  Match          AA            11111
Driver           BB            33333
  Match          BA            33333
  Match          BB            33333
Driver           CC            99999
  Match          CB            99999
  Match          CC            99999
  Match          CD            99999
Driver           DD            44444
Driver           EE            22222

I've tried

  SELECT * ORDER BY LEFT(PersonName,1), PersonPossibleMatchId,
  Source-system

but this doesn't return Driver records in strictly alpha order - you get all the A's first, but within that the order on second letter is randomized.  For example, in the above list, I might have Driver AB listed prior to Driver AA.

Added:  I'm using MS SQL (2012)

Here's another couple of possibilities

SELECT [Source-System],
       PersonName,
       PersonPossibleMatchID
FROM   #src
ORDER  BY MAX(CASE
                WHEN [Source-system] = 'Driver'
                  THEN PersonName
              END) OVER (PARTITION BY PersonPossibleMatchId) ASC,
          [Source-System] DESC,
          PersonName ASC

(online demo)

- Or -

WITH DriverPersonNames
     AS (SELECT PersonPossibleMatchID,
                PersonName
         FROM   #src
         WHERE  [Source-System] = 'Driver')
SELECT s.*
FROM   #src s
       JOIN DriverPersonNames d
         ON d.PersonPossibleMatchID = s.PersonPossibleMatchID
ORDER  BY d.PersonName ASC,
          [Source-System] DESC,
          s.PersonName ASC

(online demo)

Both will return the same results assuming that there is exactly one Driver record per PersonPossibleMatchID and PersonPossibleMatchID is not nullable.

(DDL for online demos borrowed from jyao's answer)


Service mongod fails after adding security.keyFile in config file My mongo.conf:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: [127.0.0.1, 172.24.16.25]  # Listen to local interface only, comment to listen on all interfaces.

security:
  authorization: enabled
  keyFile: '~/rsKeyFile'

Issue:
When i perform service mongod restart after adding keyFile in mongo.conf as shown above

keyFile: '~/rsKeyFile'

My service fails when i check service mongod status it says:

"/etc/mongod.conf" 47L, 851C written
[root@788978-APP06 ~]# service mongod restart
Redirecting to /bin/systemctl restart  mongod.service
[root@788978-APP06 ~]# service mongod status
Redirecting to /bin/systemctl status  mongod.service
â mongod.service - High-performance, schema-free document-oriented database
   Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2017-05-18 06:19:43 PDT; 2s ago
     Docs: https://docs.mongodb.org/manual
  Process: 24066 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=1/FAILURE)
  Process: 24064 ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb (code=exited, status=0/SUCCESS)
  Process: 24060 ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb (code=exited, status=0/SUCCESS)
  Process: 24056 ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb (code=exited, status=0/SUCCESS)
 Main PID: 24066 (code=exited, status=1/FAILURE)

May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Starting High-performance, schema-free document-oriented database...
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Started High-performance, schema-free document-oriented database.
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: about to fork child process, waiting until server is ready for connections.
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: forked process: 24069
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: ERROR: child process failed, exited with error number 1
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: mongod.service: main process exited, code=exited, status=1/FAILURE
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Unit mongod.service entered failed state.
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: mongod.service failed.

I have 777 permissions on my rsKeyFile but still its not working, when i comment key fine in mongo.conf like given below and try restarting mongod service it works perfectly fine and show this Active: active (running) since Thu 2017-05-18 06:23:51 PDT; 2s ago status.

#keyFile: '~/rsKeyFile'

I am following this https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

Please help, thanks in advance!!

Use full path for keyfile. Don't use ~ -character there. Your mongod is started (probably) with user 'mongod' and that user don't have home directory.

Additionally keyfile must be have chmod 0400 and ownership same than user who run mongod process (mongod).


Hash aggregate bailout A question that arose in a chat discussion:

I know hash join bailout switches internally to a sort of nested loops thing.

What does SQL Server do for a hash aggregate bailout (if it can happen at all)?

Hash join and hash aggregate both use the same operator code internally, though a hash aggregate uses only a single (build) input. The basic operation of hash aggregate is described by Craig Freedman:

As with hash join, the hash aggregate requires memory.  Before executing a query with a hash aggregate, SQL Server uses cardinality estimates to estimate how much memory we need to execute the query.  With a hash join, we store each build row, so the total memory requirement is proportional to the number and size of the build rows.  The number of rows that join and the output cardinality of the join have no effect on the memory requirement of the join.  With a hash aggregate, we store one row for each group, so the total memory requirement is actually proportional to the number and size of the output groups or rows.  If we have fewer unique values of the group by column(s) and fewer groups, we need less memory.  If we have more unique values of the group by column(s) and more groups, we need more memory.

He goes on to talk about hash recursion:

So, what happens if we run out of memory?  Again, like hash join, if we run out of memory, we must begin spilling rows to tempdb.  We spill one or more buckets or partitions including any partially aggregated results along with any additional new rows that hash to the spilled buckets or partitions.  Although we do not attempt to aggregate the spilled new rows, we do hash them and divide them up into several buckets or partitions.  Once we’ve finished processing all input groups, we output the completed in-memory groups and repeat the algorithm by reading back and aggregating one spilled partition at a time.  By dividing the spilled rows into multiple partitions, we reduce the size of each partition and, thus, reduce the risk that the algorithm will need to repeat many times.

Bailout
Hash bailout is lightly documented, but mentioned by Nacho Alonso Portillo in What’s the maximum level of recursion for the hash iterator before forcing bail-out?

The value is a constant, hard coded in the product, and its value is five (5). This means that before the hash scan operator resorts to a sort based algorithm for any given subpartition that doesn’t fit into the granted memory from the workspace, five previous attempts to subdivide the original partition into smaller partitions must have happened.

The "hash scan operator" mentioned there is a reference to the internal class CQScanHash in sqlmin.dll. This class heads the implementation of the hash operator (in all its forms, including partial aggregates and flow distinct) we see in execution plans.
Bailout algorithm
This brings us to the heart of your questions - what exactly does the bailout algorithm do? Is it "sort based" or based on "a sort of nested loops thing"?
It is arguably both, depending on your point of view. When hash recursion reaches level 5, the in-memory hash partition changes from being a hash table to an initially empty b-tree index on the hash values. Each row from a single previously-spilled hash partition is looked up in the b-tree index and inserted (new group) or updated (maintaining aggregates) as appropriate.
This series of unordered inserts to a b-tree can equally well be seen as an insertion sort or as an indexed nested loops lookup.
In any case, this fallback algorithm is guaranteed to complete eventually without allocating more memory. It may require multiple passes if the space available for the b-tree is not sufficient to hold all the grouping keys and aggregates from the overflow partition.
Once the memory available to hold the b-tree index is exhausted, any further rows (from the current spilled partition) are sent to a single new tempdb partition (which is guaranteed to be smaller) and the process repeats as necessary. The spill level remains at 5 because hash recursion has ended. Some processing details can be observed with undocumented trace flag 7357.


Binding specific ips with Mongodb I am using Mongodb 3.6.3 on Centos 7.
I am running the below command:
sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all  --logpath /var/log/mongodb/mongod.log --fork
and everything will work just fine. Except, binding all is not secure enough.
I used to received a logging error when I tried --bind_ip 127.0.0.1,privateIP but discovered that the error would disappear when I did bind_ip [127.0.0.1,privateIP].
However, when I tried to connect to mongo locally it would fail and through a my site, it would sit there until it timed out. I checked sudo netstat -plnt | egrep mongodand then nothing showed up, which although it successfully forked the mongod process, it did not bind to any of the specified ips.
For reference, when I tried the same command on on the --bind_ip_all version it returned tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      1645/mongod.
My question is, why did --bind_ip ips fail to bind to the specified ips?
Side Note - when binding ips, should I bind my elastic ip or my private one?

I am running the below command:

  sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all
  --logpath /var/log/mongodb/mongod.log --fork

  and everything will work just fine. Except, binding all is not secure
  enough.

As per MongoDB BOL here
 there is Warning: Before you bind to other ip addresses, consider enabling access control and other security measures listed in Security Checklist to prevent unauthorized access.

  I used to received a logging error when I tried --bind_ip
  127.0.0.1,privateIP but discovered that the error would disappear when I did bind_ip [127.0.0.1,privateIP].

As here i would like to say that if you are going to bind multiple ips then as per MongoDB documentation here To bind to multiple addresses, enter a list of comma-separated values.

EXAMPLE

localhost,/tmp/mongod.sock

Or

localhost,192.168.103.100

In the above example , let's say i have bind multiple ips. One external ip (192.168.103.100) and another localhost or (in place of localhost, that is 127.0.0.1).

net.bindIp

Type : string

Default: localhost

Starting in MongoDB 3.6, mongos and mongod bind to localhost (127.0.0.1) by default.

  However, when I tried to connect to mongo locally it would fail and
  through a my site, it would sit there until it timed out. I checked
  sudo netstat -plnt | egrep mongodand there nothing showed up, which
  although it successfully fork the mongod process, it did not bind to
  any of the specified ips.

The "--fork" is a flag to tell mongod to run as a background process, rather than as an active process which blocks the shell.
The "--fork" is a flag to tell mongod to run as a background process, rather than as an active process which blocks the shell.

Most of time when we shall not configure mongd.conf properly ( i mean to say that bindIp, port, and authorization restriction) then get the timed out error.

  For reference, when I tried the same command on on the --bind_ip_all
  version it returned tcp        0      0 0.0.0.0:27017
  0.0.0.0:*               LISTEN      1645/mongod.

net.bindIpAll

Type: boolean

Default: False

New in version 3.6.

If true, the mongos and mongod instance binds to all ip addresses. When attaching mongos and mongod to a publicly accessible interface, ensure that you have implemented proper authentication and firewall restrictions to protect the integrity of your database.

set the net.bindIp setting to 0.0.0.0,:: to bind to all IP addresses.

As here in the above mongo shell ouput the mongod server returns the pid of localhost with by default port number 27017.

Note: net.bindIp and net.bindIpAll are mutually exclusive. That is, you can specify one or the other, but not both.

  My question is, why did --bind_ip ips fail to bind to the specified
  ips?

  Side Note - when binding ips, should I bind my elastic ip or my
  private one?

May be your mongod.conf configuration file is not configure properly.  That's why you are unable to start mongod server file to that specific ip.

As MongoDB configuration files use the YAML format.

The sample configuration file of mongod settings , you can find here

For example I am also write down the mongod.conf sample configuration file in "YAML" format.

storage:
  dbPath: /var/mongodb/db/node1   -- this is the dbPath location
net:
  bindIp: 192.168.103.100,localhost  -- here there is multiple ip binding
  port: 27011
security:
  authorization: enabled    -- here Authorization is enabled
systemLog:
  destination: file
  path: /var/mongodb/db/node1/mongod.log  -- this is log path
  logAppend: true
processManagement:
  fork: true    -- here fork is true

Untill unless you are not able to run mongod server through your mongod.conf file , That means there is some error in your YAML configuration file.

Note: YAML does not support tab characters for indentation: use spaces instead.

Hope this will be help out to you.


How to change the display of an empty string in mysql command line tool? I'm maintaining a database with InnoDB tables.
These tables have some columns of type (from show create table):

`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
etc...

From the mysql command line I am searching for NULL entries in these tables.

Now I SELECTed some rows where val0 IS NULL AND val1 = '' (empty string), but the mysql command line shows both values as

+------+------+
| val0 | val1 |
+------+------+
| NULL | NULL |
+------+------+

Apparently this is confusing me. Is this behavior intentional? Is there a method to define how empty strings are displayed?

EDIT :

I should mention that STRICT MODE (see the docs) seems to be enabled.

The query

SELECT * FROM table WHERE val0 IS NULL AND val1 = '';

finds the row above. The query

SELECT * FROM table WHERE val0 IS NULL AND val1 IS NULL;

doesn't find that row. Still the '' is displayed as 'NULL'. How do I change that?

Server version is "5.0.22-log".

EDIT 2 :

Here is the full (anonymized) SHOW CREATE TABLE mytable:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `time` int(20) NOT NULL,
  `val0` varchar(30) default NULL,
  `val1` varchar(30) default NULL,
  `val2` varchar(30) default NULL,
  `val3` varchar(30) default NULL,
  `val4` varchar(30) default NULL,
  `val5` varchar(30) default NULL,
  `val6` varchar(30) default NULL,
  `val7` varchar(30) default NULL,
  `val8` varchar(30) default NULL,
  `val9` varchar(30) default NULL,
  `status` tinyint(4) default '0',
  UNIQUE KEY `idtime` (`id`,`time`),
  KEY `status` (`status`),
  KEY `id` (`id`),
  KEY `time` (`time`),
  CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`id`) REFERENCES `idtable` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here are my queries:

select * from mytable where val1 is null limit 1;

-> no result

select * from mytable where val1 = '' limit 1;

result:

+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| id  | time       | val0 | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8 | val9 | status |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| 300 | 1346855716 | 0    | NULL | 42   | 4711 | blib | blub | NULL | NULL | NULL | NULL |      5 |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+

fun fact: These last four NULLs are actually the string "NULL". (I'm only maintaining this system, I didn't build it...)

Bug #20067 empty string returns NULL as dispalyed value

This bug is possibly fixed 5.0.23. Look at the document referenced by the above page.


Oracle ASM: Freeing up space Oracle RAC 11.2 with asm storage, all type = EXTERN redundancy.

I've been cleaning up some tablespaces and shrinking datafiles back down to sizes that they're not likely to grow much larger than (processes no longer used had blown them up).

The sum of the bytes of datafiles from dba_data_files reflects this shrink, however, USED_MB in v$asm_diskgroup has not fallen and it doesn't match to what adding up the datafiles bytes is?

What would account for this difference?  We typically use v$asm_diskgroup to monitor if we're running low on space but this doesn't appear to be correct.

  SELECT name group_name ,
  type type ,
  total_mb total_mb ,
  (total_mb - free_mb) used_mb ,
  ROUND(
  (SELECT SUM(bytes)/1024/1024
  FROM dba_data_files
  WHERE file_name LIKE '+DATA02%'
  ),0) AS datafile_used
FROM v$asm_diskgroup w
WHERE name = 'DATA02'

GROUP_NAME  TYPE  TOTAL_MB  USED_MB  DATAFILE_USED
______________________________________
DATA02  EXTERN  542712  516902  506447

dba_data_files does not include:

tempfiles
controlfiles
spfiles
redo logs
archived logs
backups/copies
ADVM volumes (ACFS)
OCR
reserved space for ASM metadata

Include v$asm_alias and v$asm_file in your queries, or just simply list the files in the diskgroup (asmcmd, ls).


How can I set the auto_increment value of a column in an existing InnoDB table, without incurring downtime? I have an InnoDB table whose primary key column is defined as follows:

id INT NOT NULL AUTO_INCREMENT

This column's auto_increment value has reached INT_MAX, so all inserts are failing with a duplicate key error. However, this table has unused ID values from 0 to 1.8 million, so I would like to reset the auto_increment value to 0 to buy time for a long-term fix to be implemented.

I cannot find any way to accomplish this without incurring significant downtime. My application servers' core functionality is broken if they can't read from this table (but not being able to write, like it is now, is okay).

I've considered the following two solutions, with no success:

Setting the auto_increment value with ALTER TABLE. This isn't possible because it rebuilds the table, which takes too long.

Creating a new table with identical schema and using RENAME TABLE to swap in the new table with the column starting at 0, then back-filling it with data from the old table. This also isn't possible, because the process of backfilling with clobber the auto_increment value (and I believe it would block inserts from the application while backfilling).

Is there any other approach I can take here, or am I completely out of luck?

Bite the bullet and take the downtime.

Setting the auto-increment value for the table is useless since it will be reset to the MAX(id)+1 anyway.

ALTER TABLE -- I don't think INPLACE will work in this case, so it will be a full table copy.  (Especially if the id is the PRIMARY KEY.)

pt-online-schema-change -- full copy.  Its benefit is that the table continues to be usable.  But your table is not usable for another reason, namely the inability to add another id.

What to alter?  If you have INT, you have a 2 billion limit; changing to INT UNSIGNED would give you a 4 billion limit.  The table size won't change (aside from the shuffling of BTrees).  BIGINT is 8 bytes and gives you an unreachable limit.  Caution:  All tables referencing this one need to change the datatype at the same time.  This involves more ALTER TABLEs.

Reuse 0..1.8M?  You can explicitly set ids as you do INSERTs, but that probably requires code changes (and risk).

This may be the fastest fix:  Move the top 1.8M rows down by doing something like

UPDATE tbl
    SET id = id - (some constant: about 2B -1.8M)
    WHERE id > (the same constant);

and restart the server so that it can discover the new MAX(id).  Caution:  This technique will not work for version 8.0.
CAUTION:  If other tables use this id (as a FK or for JOINing, you must also do a similar UPDATE on their table to adjust the foo_id in it.

Caution:  Don't set any id to 0 or less than 0.  (I think that UPDATE is correct in that respect.)
You will have room for only 1.8M rows before you are in trouble again.

Gaps?  Do you have lots of gaps in ids?  They are caused by INSERT IGNORE, REPLACE, and several other types of INSERT, plus (of course) DELETE.  Many of the 'insert' methods that 'burn' ids can be recoded to eliminate the burning and usually not be any slower.

Going forward...  It is not always 'right' to have an AUTO_INCREMENT PRIMARY KEY on every table.  I estimate that 2/3 of tables are better off with a 'natural PK' -- that is some column (or combination of columns) that is UNIQUE and not to bulky.


Almost unique constraint in MySQL Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure

user | car | seat
1    | 1   | -1
2    | 1   | -1
3    | 1   |  2

That will allow

INSERT INTO table VALUES (4, 1,  3)
INSERT INTO table VALUES (5, 1, -1)

and fail

INSERT INTO table VALUES (4, 1, 2)

Same applies for updates.

Yes, you can (ab)use NULL for this. Unique indexes allow multiple rows where the unique field is NULL. You just have to make sure you make the field nullable.

Note that this only applies to unique keys, not primary keys - primary keys cannot be nullable.


synchronizing schemas in server In production server there's a database per company, with each produced from a single template.

CREATE DATABASE company_x WITH TEMPLATE company_template;

Let's say I have updated the schema, is there a way to apply those changes to both existing databases and the template, or somehow synchronize between schema versions?

CREATE  DATABASE .. WITH TEMPLATE just copies from the template - like the term implies. Later changes to the template database are completely unrelated to databases created before that.

You have to deal with it in some other way. Like:

Create a backup of the schema.
Prepend the generated SQL script with DROP SCHEMA ... CASCADE;
Create a backup of just the schema in each target DB. Compare the dump with the template dump.
Be very sure, that it's safe to replace the whole schema. Did you make any updates to objects in the schema? Any at all? That includes FK or other references to any object in the schema from anywhere in your DB. Or even references from outside.
Unless you know what you are doing, create a backup for each DB you are going to update.
Run the script against each affected DB after that.


Problem creating subscriptions on Replication Server version 15.7.1 if anyone knows what is the error? Can not use active or standby connection names for subscriptions
I got this error when I'm trying to create my subscriptions. I have already created a repdef WITH PRIMARY AT pb1.customer and when I tried to run my subscription, WITH REPLICATE AT pb2.customer. It is showing the error. I have also created a logical connection pb1.customer for dump/load db.

pb1 and pb2 are both ASE, ASE15.5 and ASE15.7 respectively.

Please advise.

The error message indicates you're using a WarmStandby (WS) setup, in which case there are no subscriptions required (nor supported), period.

If you're trying to use the WS pair as a source for replicating into a 3rd db, then the repdef will need to reference the WS logical pair as the primary db.

If you're trying to replicate into the WS pair from a 3rd db, then the subscription will reference the WS logical pair as the replicate db.


How to insert '1001-01-01 00:00:00' into MariaDB when altering a table? I created a table in HeidiSQL:
CREATE TABLE `publish_dt` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `TS` DATETIME NULL DEFAULT NULL,
    `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

Then I insert some data into the table, e.g. with
INSERT INTO publish_dt (`Text`) VALUES ('Hello')

Then I want to change TS column to non-NULL with a default of '1001-01-01 00:00:00'. I tried to do this in HeidiSQL, it produces the following ALTER TABLE query:
ALTER TABLE `publish_dt`
    CHANGE COLUMN `TS` `TS` DATETIME NOT NULL DEFAULT '1001-01-01 00:00:00' AFTER `ID`;

However, when I try to save changes in HeidiSQL, I receive the following error from the DB:
SQL Error (1265): Data truncated for column 'TS' at row 1

Could you, please, clarify why this error happens (I couldn't find the same case with DATETIME on the internet) and how to work it around?
Reason for asking: MariaDB documentation says

MariaDB stores values that use the DATETIME data type in a format that
supports values between 1000-01-01 00:00:00.000000 and 9999-12-31
23:59:59.999999.

The alter is failing because the row has a value of null for the TS column. If you remove the 'NOT NULL` from your alter statement it will work correctly though you will still end up with the row you first inserted with a value of null for TS.
You will get the same error if you do the alter without trying to set a default. The alter you are trying to use will not replace the null value and, as a result, you are getting the error.
You may change the value of the row before the alter but, as you have it written, the alter will not work as you've seen.


Linked-server query against remote DB does NOT fail when DB is in SINGLE_USER mode This is really strange to me.  I have a primary server Foo2005 and it has a linked-server entry pointing to Bar2008R2; say the name of the linked-server is BarLINK.  I write a query such as SELECT * FROM BarLINK.MyDB.dbo.Stuff.  The catch is, over on Bar, MyDB is in SINGLE_USER mode.  Why would this query still succeed?  I would expect to get the error like "Could not open the specified database since it's in SINGLE_USER mode and is already open by a user" (forgive me for not remembering the exact phraseology).

Notes:

Linked-server is created using a SQL login/pwd, say readonly, which, as the name implies, has READ permissions on the target server & DB.
Testing the query when MyDB is in MULTI_USER mode seems "faster" than when it runs & MyDB is in SINGLE_USER mode, but I don't have any concrete metric of that, just the observation/feels.
As the names indicate, server Foo2005 (the "local" server where the query lives) is SQL 2005, while server Bar2008R2 (the "remote" server where the query is being sent / where MyDB lives) is 2008R2.

Any ideas?

Upon re-trying and attempting to reproduce this problem, it seems I cannot.  I think Nic said it best so far: "being in single-user mode doesn't implicitly prevent connections".  At the time, the linked-server query must have been the first and only thing connecting to that DB.

Thanks also to Aaron Bertrand for suggesting the restricted_user mode for future cases.


MySQL: Keep the 10 most recent rows for each user I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way):

userid   : the id of the user that got the score
timestamp: the time the score was gotten
score    : the score itself

The goal of the query is to delete rows so we have at most 10 scores per user.We also want to keep the most recent rows. So we need to delete the older ones until we get to 10.Note that some players may have less than 10 scores.

How would I manage to do this?

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score.

The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned by the inner query.

DELETE FROM score
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT *
    FROM score s1
    WHERE (
        SELECT COUNT(*)
        FROM score s2
        WHERE s1.userid = s2.userid
            AND s1.timestamp <= s2.timestamp
    ) <= 10 --Keep this many records
  ) foo
);


DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?

Here's some details:

DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise

Data drive: 800GB

Database to snapshot: 430GB

I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:

The operating system returned:

  error 665(The requested operation could not be completed due to a file
  system limitation) to SQL Server during a write at offset
  0x00002dc1520000 in file 'E:\SQL Data 1\devDB_seq1501_rba63692865.ss'.
  Additional messages in the SQL Server error log and system event log
  may provide more detail. This is a severe system-level error condition
  that threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

  E:\SQL Data 1\devDB_seq1501_rba63692865.ss: Operating system error
  665(The requested operation could not be completed due to a file
  system limitation) encountered.

  Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
  operation and is marked suspect. It must be dropped and recreated.

This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.

Any insight into what might be happening here would be greatly appreciated.

Did the drive the snapshot is on or the drive that the source DB is on run out of space?

There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.


Getting conversion error, for value that doesnt exist in database I am running this query:

SELECT
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
        THEN 1
        ELSE 0
        END) AS 'Official Holidays'
FROM
    EmployeeAttendance_Backup ea
    , Customer em
    , Employee emp
WHERE
    ea.markedFor >= '2012-08-26'
    AND ea.markedFor <= '2012-09-25'
    AND em.RSCode = ea.DistID
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY
    em.CustomerName;

I get an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value
    'EmployeeName A B' to data type int.

I've tried running the query with an explicit JOIN such as:

SELECT
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
        THEN 1
        ELSE 0
        END) AS 'Official Holidays'
FROM
    EmployeeAttendance_Backup ea
    LEFT OUTER JOIN Customer em ON em.RSCode = CAST(ea.DistID AS INT)
    LEFT OUTER JOIN Employee emp ON emp.EmployeeMasterID=ea.Empid
WHERE
    ea.markedFor >= '2012-08-26'
    AND ea.markedFor <= '2012-09-25'
    AND em.RSCode = ea.DistID
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY
    em.CustomerName;

However, I get the same error as above.

If I remove all the SUM statements from above, I get zero records. However, if I enable even one, I get the same error.

The strange part about this error is, there is no such Employee with the name EmployeeName in any of the tables used in this query.

I am stumped as to what is happening. Can anyone shed some light?

I was able to solve the problem by thinking about the error message for a minute. The error message says something about not being able to convert from varchar to int. So I looked for columns that should store a number, but were actually saving it as a varchar. The necessity of a cast was an indicator.

So, I looked through each column in the EmployeeAttendance table, and found the offending record.

Empname A B was stored in a column which should have either 1 or 0 values. This column is not a BIT, but a varchar(50)

dies in shame

This table was a hand-me-down, and I think I need to get my janitors uniform out.


Move data from one table to another How do I retrieve data from one MySQL table and store the data into another database table on the same server. I have tried this code but keep getting errors:

CREATE PROCEDURE `ProfusionTest` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `Profusion`.`source_cdr`;

INSERT INTO
    `dest_table`.`create_info`
    SELECT * FROM `Profusion`.`source_cdr`;

END

In same server you just need to use alter table to do that.

ALTER TABLE `old_db.foo` RENAME `new_db.foo`;


Delete row if table exists I want to delete a row from a table if that table exists.

Below is the delete statement

DELETE
FROM UM_CLAIM_BEHAVIOR
WHERE UM_TENANT_ID = ?

Can I extend this query to check the existence of the table or is there any other alternative to check existence of table before executing delete query?

According to comments it is not possible to give a platform-independent solution for this.

In SQL Server you could do something like this

IF EXISTS (
        SELECT 1
        FROM sys.objects AS o
        INNER JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
        WHERE CONCAT (s.NAME, '.', o.NAME) = 'dbo.UM_CLAIM_BEHAVIOR'
            AND o.type_desc = 'USER_TABLE'
        )
BEGIN
    DELETE T
    FROM UM_CLAIM_BEHAVIOR AS T
    WHERE UM_TENANT_ID = ?
END


How does one create a notification upon corruption being found during Automatic Page Repair under database mirroring? I read:  https://msdn.microsoft.com/en-us/library/bb677167(v=sql.120).aspx#UnrepairablePageTypes

But, I am unclear how I can set up a notification so I am aware when this occurs.

Any advice?

Assuming you are on any edition besides express AND that you have SQL Agent enabled/running, the quickest route is to utilize Glenn Berry's alerts setup script, to configure all of the necessary parts for alerting on most of the IO related alerts (823, 824 and 825). 823 and 824 are specific to an actual IO corruption, while 825 is a lesser used warning sign of a potential storage problem (retry).

In addition to Glenn's base alerts, add an alert for error 829 (Page marked RestorePending) and error 1481 (Database mirroring could not repair physical page).  These will likely fire with corresponding 823 or 824 errors), but are mirroring/AG specific in context.

Also note, that you can historically view pages that were marked suspect/corrupt, by querying the suspect_pages table in msdb, as opposed to scanning the ERRORLOG for errors.


Can 'clustering' and 'alwayson failover clustering' be used interchangeably? I am reading this summary from the book 'Pro SQL Server 2019 Administration' from Carter.
In 'hot', when citing the examples the author writes 'Clustering'.

But when each technology is explained it is listed as 'AlwaysOn failover clustering'... My question is about naming: can the terms 'clustering' and 'alwayson failover clustering' be used interchangeably? or do they  mean different things?

"Clustering" is definitely used here to refer to SQL Server's Failover Cluster Instances (FCIs). However, the terms are not interchangeable.
Both FCIs and Availability Groups (AGs) are built on top of Windows Server Failover Clusters (WSFCs). I've seen FCIs, AGs, and WDFCs referred to as "Clusters" or "Clustering."  The correctness of referring to any of these simply as "Clustering" is debatable.
What isn't debatable is that referring to any of those technologies simply as "Clustering" is ambiguous and can be confusing without proper context. Using the more verbose terminology, or even in abbreviations will always be unambiguous.


Old ODBC app throws "cannot generate sspi context" on Windows 10 An old Access 2003 .adp project is currently running on Windows 7 machines, while SQL Server 2008 is on the backend.
Since the app is moved to a Windows 10 machine, the app will trigger "Cannot generate SSPI context" error.

There are a lot of messages over the Internet to explain possible reasons and solutions.

In our case, we may overcome the problem running Msaccess.exe from commandline, using:

runas /netonly /user:userdomain\useralias "C:\Program Files\<path-to-office-folder>Msaccess.exe"

The downside is that the user is prompted for password each time the shortcut is being used.

EDIT:

The weird fact is that the context under which the program is running is apparently the same as the Windows 10 user has the right credentials to access domain resources (can access server side networks shares correctly), and also has credentials within SQL server configuration. As a proof, under Windows 7, the same credentials are being used without errors.

It looks like the app in Windows 10 is incorrectly passing computername\useralias credentials to SQL Server in place of userdomain\useralias.....

Is there a way to fix this so that the app (or Windows 10) are addressing the domain user name correctly, avoiding the error?

When you launch your app using "runas" command, you use some domain user, and that user has Windows login at your SQL server. Or that domain user is part of some AD group that has Windows login at your SQL Server

When you launch app without "runas" command, the app is running, but under different security context (under different windows user), right ?
In this case you have to create Windows login for this domain user at your SQL Server and grant it some permissions, so that different windows user can connect to SQL Server. That would resolve "Cannot generate SSPI context" error

Edit 1: after re-reading your question - its also possible that your windows 7 machine has login at your SQL Server (example "yourdomain/win7machine$") so it can connect, and when you move your app to windows 10 machine, that machine's computer account ("yourdomain/win10machine$") does not have login at your SQL Server... you just have to create this login

Edit 2: to find out what Windows login app is using to connect to SQL Server, while app on Win 7 machine is connected to SQL Server, run below command at SQL Server:

select * from sys.dm_exec_sessions

Look at columns host_name (it will be your Win 7 machine) and login_name (it will be the actual login that application is using to connect to SQL Server)

Then try to launch the app on Windows 10 machine. Make it throw "cannot generate SSPI context" error. Go to SQL Server machine, launch Event Viewer, go to Application logs. Is there a new Logon event from MSSQLSERVER ? if so, does it say "login failed for user...." ? if yes, it will show you what login app on Win 10 machine is using to connect to SQL Server


SQL Server 2012 - Disable Job On Linked Server I have a job on server B, which is linked to server A. Is it possible to disable a job that exists on server B in the context of server A? I have tried the following command from server A and receive an error message.

EXEC SERVER_B.msdb.dbo.sp_update_job @job_name = N'test_job', @enabled = 0

Error message:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('test_job') does not exist.

I also tried creating a stored procedure on server B that executes the above command. The procedure works when executing it from the context of server B, but not from server A. Is it possible to disable a job on a linked server?

I added the linked server user to the SQL Agent Operator role and the issue has been resolved.

EXECUTE sys.sp_addrolemember @rolename = 'SQLAgentOperatorRole',
                             @membername = 'linked_server_user'


Merge statement deadlocking itself I have the following procedure (SQL Server 2008 R2):

create procedure usp_SaveCompanyUserData
    @companyId bigint,
    @userId bigint,
    @dataTable tt_CoUserdata readonly
as
begin

    set nocount, xact_abort on;

    merge CompanyUser with (holdlock) as r
    using (
        select
            @companyId as CompanyId,
            @userId as UserId,
            MyKey,
            MyValue
        from @dataTable) as newData
    on r.CompanyId = newData.CompanyId
        and r.UserId = newData.UserId
        and r.MyKey = newData.MyKey
    when not matched then
        insert (CompanyId, UserId, MyKey, MyValue) values
        (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId, UserId, MyKey form the composite key for the target table. CompanyId is a foreign key to a parent table.  Also, there is a non-clustered index on CompanyId asc, UserId asc.

It is called from many different threads, and I am consistently getting deadlocks between different processes calling this same statement. My understanding was that the "with (holdlock)" was necessary to prevent insert/update race condition errors.

I assume that two different threads are locking rows (or pages) in different orders when they are validating the constraints, and thus are deadlocking.

Is this a correct assumption?

What is the best way to resolve this situation (i.e. no deadlocks, minimum impact on multi-threaded performance)?

(If you view the image in a new tab, it is readable.  Sorry for the small size.)

There are at most 28 rows are in the @datatable.
I have traced back through the code, and I cannot see anywhere that we start a transaction here.
The foreign key is set up to cascade only on delete, and there were no deletions from the parent table.

There would not be a problem if the table variable only ever held one value.  With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company.

Process A reads the destination, finds no row, and inserts the value '1'.  It holds an exclusive row lock on value '1'.  Process B reads the destination, finds no row, and inserts the value '2'.  It holds an exclusive row lock on value '2'.

Now process A needs to process row 2, and process B needs to process row 1.  Neither process can make progress because it requires a lock that is incompatible with the exclusive lock held by the other process.

To avoid deadlocks with multiple rows, the rows need to be processed (and tables accessed) in the same order every time.  The table variable in the execution plan shown in the question is a heap, so the rows have no intrinsic order (they are quite likely to be read in insertion order, though this is not guaranteed):

The lack of consistent row processing order leads directly to the deadlock opportunity.  A second consideration is that the lack of a key uniqueness guarantee means that a Table Spool is necessary to provide correct Halloween Protection.  The spool is an eager spool, meaning all rows are written to a tempdb worktable before being read back and replayed for the Insert operator.

Redefining the TYPE of the table variable to include a clustered PRIMARY KEY:

DROP TYPE dbo.CoUserData;

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL PRIMARY KEY CLUSTERED,
    MyValue integer NOT NULL
);

The execution plan now shows a scan of the clustered index and the uniqueness guarantee means the optimizer is able to safely remove the Table Spool:

In tests with 5000 iterations of the MERGE statement on 128 threads, no deadlocks occurred with the clustered table variable.  I should stress that this is only on the basis of observation; the clustered table variable could also (technically) produce its rows in a variety of orders, but the chances of a consistent order are very greatly enhanced.  The observed behaviour would need to be retested for every new cumulative update, service pack, or new version of SQL Server, of course.

In case the table variable definition cannot be changed, there is another alternative:

MERGE dbo.CompanyUser AS R
USING
    (SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
    R.CompanyId = @CompanyID
    AND R.UserID = @UserID
    AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN
    INSERT
        (CompanyID, UserID, MyKey, MyValue)
    VALUES
        (@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);

This also achieves the elimination of the spool (and row-order consistency) at the cost of introducing an explicit sort:

This plan also produced no deadlocks using the same test.  Reproduction script below:

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL /* PRIMARY KEY */,
    MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
    CompanyID   integer NOT NULL

    CONSTRAINT PK_Company
        PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
    CompanyID   integer NOT NULL,
    UserID      integer NOT NULL,
    MyKey       integer NOT NULL,
    MyValue     integer NOT NULL

    CONSTRAINT PK_CompanyUser
        PRIMARY KEY CLUSTERED
            (CompanyID, UserID, MyKey),

    FOREIGN KEY (CompanyID)
        REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE
    @DataTable AS dbo.CoUserData,
    @CompanyID integer = 1,
    @UserID integer = 1;

INSERT @DataTable
SELECT TOP (10)
    V.MyKey,
    V.MyValue
FROM
(
    VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
        (6, 6),
        (7, 7),
        (8, 8),
        (9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();

BEGIN TRANSACTION;

    -- Test MERGE statement here

ROLLBACK TRANSACTION;


After "ALTER TABLE MOVE PARTITION" new partitions are still created in the original tablespace I moved all objects from one tablespace (hereinafter A) to another (B) .. but when a new partition is created it resises in the original one ..

How can I fix it? I have no idea what to do because everything is moved already

DDL:

CREATE TABLE OWNER_PUBLIC.T_NO_CONNECT_CONTRACTS
   (
      CALL_DATE DATE,
        CONTRACT_ID VARCHAR2(10),
        FLAG_WF VARCHAR2(3 CHAR)
   )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE OWNER_PUBLIC
  PARTITION BY RANGE (CALL_DATE) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
    (PARTITION "P0"  VALUES LESS THAN (TO_DATE(' 2018-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE OWNER_PUBLIC

Moving existing partitions to a new tablespace does not change the default tablespace at the table level.

To do that:

alter table OWNER_PUBLIC.T_NO_CONNECT_CONTRACTS modify default attributes tablespace tablespaceB;

ALTER TABLE


Simple XML .query SELECT statement times out on 1 SQL SERVER instance I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line.   Using profiler I have reduced a recreateable scenario on this SQL Server instance to the following code:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]') -- comment out any of these lines so only one or two items are returned and the query works fine!

This statement hangs.   If you comment out one of the returned columns in the SELECT statement, then the statement runs fine.  So one of two returned columns is fine, but anything over is bad.  If you take out the first node selector (i.e. the [1]) it also runs fine.

Here's the rub though:

the statement fails on any DB in this SQL Server 2012 instance (SERVER1/INSTANCE1).
the statement succeeds on any other SQL Server instance I have tried, including  other instances running from the same SQL Server machine as the failing instance (e.g. SERVER1/INSTANCE2, SERVER1/INSTANCE3 etc.) - all of which are 2012 instances.

It seems to me that SERVER1/INSTANCE1 has borked the way it runs this statement.  I have heard that SQL Server does things with optimising execution of statements, but my knowledge stops there.  I'm sure there must be some way to get it to behave again, but how?

UPDATE

The following adjustment (getting the singleton of the Xpath result-set rather than potentially multiple first elements) solves the issue above for the SERVER1/INSTANCE1 issue.  I would put this as the answer, except that I don't believe this is identifying the underyling problem (please correct me if I'm wrong!).  Given that our codebase deployed on other servers implements the above statement (or derivatives of it) I don't want to update our entire shredding XML approach without good reason (or at least I would have to justify it to my peers).

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('(/Foo/Node1)[1]')
    ,@RequestXML.query('(/Foo/Node2)[1]')
    ,@RequestXML.query('(/Foo/Node3)[1]')

Any help gratefully received.

Thanks,

Ali

I'm not sure if it answers your question, but I had a similar problem. The resulution was limiting SQL Server to execute the query using one processor only.

If you have the same problem, this should work:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]')
OPTION(MAXDOP 1)

The thing is that SQL Server Query Optimizer decides that the query is costly enough to split its execution and merge those results. And sometimes this creates problems, so lowering degree of parallelism (MAXDOP) can solve it.

EDIT

Looking at the execution plan for this query, I find it hard to believe that somewhere in the middle of execution of each branch for @RequestXML.query('...'), estimated number of rows was 180,000. However, when I told it that I promise I will need only the first Foo, he reduced it to 900. So, try this...

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
    <Node4>Tomorrow</Node4>
</Foo>'

SELECT
    @RequestXML.query('/Foo[1]/Node1[1]')
    ,@RequestXML.query('/Foo[1]/Node2[1]')
    ,@RequestXML.query('/Foo[1]/Node3[1]')
    ,@RequestXML.query('/Foo[1]/Node4[1]')