A Customized Table with Chronological order Goal:
My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem:
When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8.

I don't know how to do it?

Information:
I'm using SQL server 2012.
The data in the UDT will be random for every database request.

Picture 1:

Picture 2:

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
end

GO

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

DECLARE @data tvf_id INSERT INTO @data([id])
VALUES (8), (2), (4), (1), (3);

exec sp_Test @pID = @data

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
    ORDER BY b.OrdCol ASC
end

GO

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

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol])
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

exec sp_Test @pID = @data


How can I tell if TempDb is too big? Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got some extremes where TempDb's log is filling up a 250GB drive while there are 8 equally-sized TempDb files filling up a 1TB drive.
Is there a way to tell from a query that the sizes are "too big" after the server's been running under "normal" load for say a week or month? I.e. how would one gather enough information to make a reasonable, educated guess that a 25GB log and total 120GB of TempDB files are sufficient?
Or is there no other way except to shrink them down to "almost certainly too small", turn on auto-growth, and hope performance doesn't suffer too much for a few weeks while they grow to "proper sizes"? ...'cause that sure would suck.

Could you not simply run sp_spaceused, or a cut down version as you probably don't need both datasets, on tempdb regularly and log the results?
Obviously this will likely result in an underestimate, because it is unlikely that peak space use will be exactly on one of your samples, so you will need to inflate the maximum figures. Perhaps you could use this method to decide a good starting point for the "shrink and watch if it grows back a bit" experiment that you describe.


Finding the application behind ".net sqlclient data provider" I'm trying to find which application is opening various connections on the SQL Server, all named ".net sqlclient data provider".

From system monitor I got the machine name;
On the client with netstat -a -b -o | Find "SQLServer", I have found 4 connections all with the PID 4 (ntoskrnl) ... yes this is windows and it is a MS SQL server.

"Trace process in SQL server profiler", crashes the Management Studio.

So, before I start killing applications, one by one, do you have an idea how to trace back this connection?

Best Regards;
Ezeq

sp_who2 (MSDN) is always a good start, and you can query the sys.processes table or sys.dm_exec_connections (MSDN) DMV.

Alternatively something like Adam Machanic's sp_WhoIsActive can help find problem processes and queries.

If you can't find what you're looking for directly in SQL Server, you'll be able to find things like the hostname/IP address and loginame that will help you track down the culprit.

Sample code for querying DMV/sys.processes (you'll have to chop it about if you want to do more):

Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype, loginame,cmd,spid,waittype,waittime,lastwaittype,cpu,physical_io,memusage,login_time,last_batch,open_tran,status,net_address, t.text
from sys.sysprocesses sp
--JOIN sys.dm_exec_connections con ON con.session_id = sp.sid
CROSS APPLY( select text from sys.dm_exec_sql_text(sp.sql_handle))t
--where hostname
order by sp.spid


mysql 8 - rds - 1022 Can't write; duplicate key in table Documenting this error and solution here in case it could help anyone.
We have a MYSQL 8 database on Amazon RDS.
The below group by used to work just fine on Mysql 5.7 but it started giving
 1022 Can't write; duplicate key in table  '/rdsdbdata/tmp/#sqlf80_1656bc_0'
 error once we upgraded to Mysql 8.

GROUP BY DATE_FORMAT(CONCAT(YEAR(performance_stats_date),'-',
MONTH(performance_stats_date),'-',DAY(performance_stats_date)),
 '%Y-%m-%d')

That's the wrong way to use DATE_FORMAT.  It is given a DATE or DATETIME and a string providing the 'formatting'.  Example:

mysql> SELECT DATE_FORMAT(NOW(), "%Y-%m-%d"), DATE(NOW());
+--------------------------------+-------------+
| DATE_FORMAT(NOW(), "%Y-%m-%d") | DATE(NOW()) |
+--------------------------------+-------------+
| 2019-01-30                     | 2019-01-30  |
+--------------------------------+-------------+

In your case, it would be something like

  GROUP BY DATE_FORMAT(performance_stats_date, "%Y-%m-%d")

But this is even simpler:

  GROUP BY DATE(performance_stats_date)

For further discussion, please provide

SHOW VARIABLES LIKE 'char%'
SHOW CREATE TABLE` for each table.
the entire query


Whitelist-based SQL Server schema setup My group has access to a single database on a SQL Server instance. We have a number of different people in our group, each working on separate projects. In trying to determine how to segregate projects within the database, I came across schemas, which seem to be a good way to define pseudo-databases within the single database instance, and segregate access as necessary at the schema level.

The problem I'm hitting is that a newly created schema seems to grant access to everybody, and I'm not sure how to enforce a whitelist approach. Do I have to manually deny access to all those not in a given group, or is there a mechanism to deny all access to a schema for all users by default and only grant access to those on a whitelist?

They can read/write in every schema because they have db_datareader and db_datawriter fixed roles assigned. These roles allow the user to read and write to any user table in the database ( http://msdn.microsoft.com/en-us/library/ms189121.aspx ).

(btw db_ddladmin also allows them to run ANY DDL commands in the database, which might cause you some problems if they start dropping/modifying each others objects)

If you revoke these role grants, you can then assign explicit schema permissions to a user, ie:

GRANT select, insert ON SCHEMA :: schema1 to user1;

More details at http://msdn.microsoft.com/en-us/library/ms187940.aspx

If you can't revoke these roles because they're in use elsewhere, then you'll need to explicitly deny permissions on the schema for the users you don't want to have access. (A deny at the schema level overrides the database level grant from the fixed role).


Instead of Delete trigger I want to pass one validation DML statement on the trigger. Condition is below DML statement

select distinct t1.cid
from table1 t1, table t2
where t1.cid=t2.cid
      and
      t1.status<>'5'

if table1.status is  not 5, then trigger will block delete transaction.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
INSTEAD OF DELETE
AS
Begin
    declare @caseid VARCHAR(10)
    select @caseid = deleted.cid from deleted
    If exists(select * from table1 t1,table2 t2 where t1.cid=t2.cid AND t1.STATUS<>'5' AND t2.cid=@caseid)
    begin
        Rollback Transaction
        RAISERROR ('Deletions not allowed after loaded ', 16, 1)
    end
end

Will the above trigger will give correct solution?

An INSTEAD OF trigger is not the best solution here. An INSTEAD OF trigger performs no actions against the database unless explicitly done so in the trigger code. Your example will never delete rows regardless of the STATUS value because it contains no DELETE statement. The ROLLBACK is superfluous since no modifications were made.

Consider an AFTER DELETE trigger for this requirement. As with any trigger, multiple rows may be affected so it is important to handle such a case. Avoid using the select @caseid as that will identify only a single cid value even if different ones were deleted.

Here's an example of an after trigger for your need.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
FOR DELETE
AS
IF EXISTS(SELECT * FROM deleted where deleted.STATUS <>'5')
BEGIN
        RAISERROR ('Deletions not allowed after loaded ', 16, 1);
        ROLLBACK TRANSACTION;
END;
GO


InnoDB buffer pool active pages I'd like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW INNODB ENGINE STATUS), so one can't find out how many distinct pages are actually active in the buffer pool.

I have Googled around and read various "how to set/find out optimal innodb buffer size" type Q&As, but they don't involve that question.

You can kind of do this from MySQL 5.6 and onwards using information_schema.  I have an example of finding the ideal buffer pool size on my blog here: http://www.tocker.ca/2013/05/31/estimating-mysqls-working-set-with-information_schema.html

The caveat is that you may need to either restart or lower the buffer pool size first.  Inactive pages will just stay in memory if there is no need to make free space - which could skew your result on a server that's been running for a while, yet has plenty of memory.


Mongodb: too many namespaces raise on setProfiling I have a mongodb database with a single (relatively big, around 130GB) collection, with 3 indexes, on which I'd like to do some profiling.
However when I run the db.setProfilinStatus(1, 1000) command, mongodb returns the following error:

{
    "was" : 0,
    "slowms" : 100,
    "errmsg" : "exception: too many namespaces/collections",
    "code" : 10081,
    "ok" : 0
}

I have verified the nssize setting (I have nssize = 2047 in mongod.conf and I have checked the result of db.adminCommand( "getCmdLineOpts" )

Any idea what the issue might be here, and how I could enable profiling?
Thanks!

You might have missed this little bit of the nsSize docs (highlighting mine):

  Use this setting to control size for newly created namespace files.
  This option has no impact on existing files

So, you can't just change the parameter and increase the number of collections for an existing database - that will still be at the old value.  The setting actually alters the size of the <dbname>.ns file when it is created - if that is already in existence (for an existing database), then it stays at its original size (default 16MiB) until you cause it to be recreated.

If you are running a replica set, then the best way to cause that recreation to happen is to shutdown a secondary, make sure it will restart with the new nsSize parameter, and then wipe its data files and allow it to sync from scratch.  Since that will include creating a new .ns file it will pick up the setting and create the larger one.  Then repeat for all secondaries before finally stepping down the primary and doing it there.  Once complete you will have the setting on all nodes.

If you don't have a replica set, then I think your only options are either:

Running a repair, which will mean down time and you will need 2x your data size free at least, probably more since you are now creating 2GiB namespace files that were previously 16MB
Copying all the data to a newly created database to take advantage of the new namespace sizing

Neither of those options are going to be particularly quick, so hopefully this is a replica set and not a standalone instance.

You could also look to clean up some of the collections in that database to give yourself some head room and time to sort it out.  Are you actually using all 24000 collections, can some be pruned?


How can I find out if a user's password has expired in MySQL? How can I find out if a user's password has expired in MySQL?

The MySQL version I'm using is 8.0.16.

This information, along with other user properties, is found in the user table in the mysql schema (database). The value 'Y' would indicate an expired password

select password_expired from mysql.user where user = 'whatever'


MySQL query slow even with indexes Structure of my table

mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE `t_group_tag_relation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) NOT NULL,
`group_tag_id` int(11) NOT NULL,
`resource_id` int(11) NOT NULL,
`resource_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `group_tag_id_resource_id` (`group_tag_id`,`resource_id`),
KEY `domain_id` (`domain_id`,`group_tag_id`,`resource_type`,`resource_id`),
KEY `domain_id_resource_type` (`domain_id`,`resource_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1613462 DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

Explain plan of my query

mysql> explain select * from t_group_tag_relation WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2;
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| 1 | SIMPLE       | t_group_tag_relation | ref   | domain_id,domain_id_resource_type | domain_id | 4 | const | 28400 | Using where; Using index |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
1 row in set (0.57 sec)

It is using indexes already but performance is not good.
How to improve performance?

I think your explain plan is doing a heavy index scan. What you might need is an index with those exact three columns.

Please create this index

ALTER TABLE t_group_tag_relation ADD INDEX
resource_id_domain_id_resource_type_index (resource_id,domain_id,resource_type);

and try your query again.


What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode? Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance?

Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?

If you have synchronous replicas you typically have one with automatic failover.  The most common reason not to is when the primary is hosted on a Failover Cluster Instance, and it's a rule that only the FCI can failover automatically in that configuration.

But you could also have a scenario where you would prefer an outage over a failover, for instance if the secondary replica is not accessible from the application.


Lookup multiple ids in seperate table with 2 subquerys I have a query from a timesheets tables that I am using to select a submitter, approver, and a date.

SELECT PRTIMESHEET.POSTED_TIME,
  PRTIMESHEET.PRRESOURCEID as "SUBMITTER",
  PRTIMESHEET.PRAPPROVEDBY as "APPROVER"
FROM PRTIMESHEET
WHERE rownum < 10;

In 1 table I have a list of all employees.  The PRTIMESHEET.PRRESOURCEID and PRTIMESHEET.PRAPPROVEDBY both link to SRM_RESOURCES.ID.

That table has data like the following.

How can I run 1 query to combine the 2 tables and get the names instead of IDs?  something like this:

I have tried a subselect like this to do joins, but with no luck.

select prtimesheet.posted_time as "TIMESHEET DATE",
  (
  select prtimesheet.prresourceid as "SUBMITTER"
  from prtimesheet
  where prtimesheet.prresourceid = srm_resources.id
  ),
  (
  select prtimesheet.prapprovedby as "APPROVER"
  from prtimesheet
  where prtimesheet.prapprovedby = srm_resources.id
  )
FROM prtimesheet, srm_resources;

I am getting the following error: ORA-01427: single-row subquery returns more than one row

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases.

I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;)

Schema and Data

CREATE TABLE prtimesheet (
  id numeric,
  submitter_id numeric,
  approver_id numeric
);

 CREATE TABLE srm_resources (
   person_id numeric,
   name text
 );

 INSERT INTO prtimesheet VALUES(1, 1000, 1001), (2,1000,1002), (3,1002, 1001), (4,1001,1002);

 INSERT INTO srm_resources VALUES (1000,'Jack Ryan'), (1001, 'Bart Mancuso'), (1002, 'James Greer');

Multiple joins on same table using aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources AS sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources AS app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

This should fix you right up!

Here is a SQL Fiddle for you to play around with if you like.

Edit: And as suggested...

The Oracle version: Schema and Data

CREATE TABLE prtimesheet (
  id number,
  submitter_id number,
  approver_id numeric
 );

  CREATE TABLE srm_resources (
     person_id number,
     name varchar(25)
  );

  INSERT ALL
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(1, 1000, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(2,1000,1002)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(3,1002, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(4,1001,1002)
  SELECT * FROM DUAL;

 INSERT  ALL
 INTO srm_resources(person_id, name) VALUES(1000,'Jack Ryan')
 INTO srm_resources(person_id, name) VALUES(1001, 'Bart Mancuso')
 INTO srm_resources(person_id, name) VALUES(1002, 'James Greer')
 SELECT * FROM DUAL;

The Oracle version: Joins and aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

Here is the Oracle SQL Fiddle!


How does the SQL Spec specify literals with a decimal should be typed? Different databases implicitly type numeric literals differently,

MySQL types 1.0 as decimal(2,1)
PostgreSQL types 1.0 as numeric

Which one of these is correct?

Both are correct and compliant on storage from literals. There are two types of numeric literals in the spec's eyes,

<exact numeric literal>
<approximate numeric literals> floating point, triggered by the use of Scientific Notation (with E or e)

For exact numeric literals (ENL),

  22) The declared type of an  ENL is an implementation-defined exact numeric type whose scale is the number of <digit>s to the right of the <period>. There shall be an exact numeric type capable of representing the value of ENL exactly.

For approximate numeric literals (ANL),

  23) The declared type of an <approximate numeric literal> ANL is an implementation-defined approximate numeric type. The value of ANL shall not be greater than the maximum value nor less than the minimum value that can be represented by the approximate numeric types.

Meaning the type merely has to be able to hold the literal, the rest is left to the implementation.

Taken from the SQL 2011n spec

See also

Does MySQL or MariaDB support double literals (double precision)?


Does distributor_admin need sysadmin? I have a new install with push replication (20+ replications).  When it was set up the SQL account 'distributor_admin' was created with sysadmin.  I did not do the install but I am responsible for ongoing security of the databases. The install team says the account was auto created which is supported by the msdn article Secure the Distributor
According to the msdn article Replication Agent Security Model it looks like after install we can back the permission up to db_owner with a few other considerations.

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:
-At minimum be a member of the db_owner fixed database role in the distribution database.
-Be a member of the PAL.
-Have read permissions on the snapshot share.
-Have read permissions on the installation directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.
-When replicating LOB data, the distribution agent must have write permissions on the replication C:\Program Files\Microsoft SQL Server\XX\COMfolder where XX represents the instanceID.
Note that the account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database, or have equivalent permissions if the subscription is for a non-SQL Server Subscriber.
Also note that when using -subscriptionstreams >= 2 on the distribution agent you must also grant the View Server State permission on the subscribers to detect deadlocks.

Per security rules, if the account keeps sysadmin the password needs to be changed regularly.
In this environment we expect additional replications to be added periodically.
Will dialing back the sysadmin for distributor_admin as described by msdn lead to issues?

In theory no, in practice yes.

After testing for several months on an instance running SQL 2014 Enterprise edition, against 20+ replication instances running MySQL, I found:

distributor_admin does not need SA for day to day operations.

If there is a sync issue (like reboot during patching) if distributor_admin does not have SA it will not self recover.  When distributor_admin does have SA, replication has always self recovered after patching.


Group by month taking significant time I am trying to do a simple per month of year sum of values on a big (>10M) table.
However, it faces significant performance issues (7 sec with 4G innodb pool size, in local db with 1G innodb pool size it takes over a minute). The script seems fairly simple.

SELECT
  year(date_time),
  month(date_time),
  sum(value)
FROM measurements
WHERE
  source_id = 2
GROUP BY year(date_time), month(date_time)

I have tried using MySQL 5.7 and MariaDB 10.2. Using EXPLAIN in both cases provides:

SIMPLE  measurements    ref source_id,source_date   source_id   4   const   4418476 Using where; Using temporary; Using filesort

It seems I cannot avoid using temporary table. The table has 2 indexes: date_time and source_id, date_time.

INDEX(source_id, date_time, value) is optimal, partially because it is "covering".  "Covering" means that the query can be completely handled by the columns in the index.  I am assuming you did not water down the query?
The column tested by = must come first, regardless of cardinality.  Else, If date_time is first, it will have to read the entire index.
The EXPLAIN estimates that source_id = 2 44% (4418476/10M) of the time.  Reading 4M rows is a lot better than 10M.
Please provide SHOW CREATE TABLE; without it, I am making guesses in my Answer.
Both of your attempted indexes have to bounce between the index BTree and the Data BTree.
Probably between 1GB's and 4GB's worth of blocks (16KB - data or index) needed to be pulled into cache (buffer_pool) to satisfy your query.  Probably it was entirely in the 4GB buffer_pool when it took 7s.  And in the 1GB, there was not room, so it was at least partially I/O-bound.
The suggested index will easily fit in 1GB buffer_pool.  And only 44% of it is needed for this query.
When you add my composite query, get rid of the existing one that is a prefix of it; it will be redundant and unnecessary.
As your data grows, someday the index will become too big for 1GB.  That's a fact of life.
However, by building and maintaining a [Summary table(http://mysql.rjweb.org/doc.php/summarytables), you can make the equivalent query take less than 1 second on either server, even as the table grows.  (It would probably have 3 columns: source_id, date (no time), and sum(value).)


Putting a condition related to a table in JOIN is better? I have seen this a lot that people are putting table related condition on JOIN clause. See this example,

SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.index_id > 0 AND i.is_hypothetical = 0)
    AND (i.object_id = tbl.object_id)
  WHERE (i.is_unique = 1 AND i.is_disabled = 0)
    AND (tbl.Name = 'Warehouse')

SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.object_id = tbl.object_id)
  WHERE (i.index_id > 0 AND i.is_hypothetical = 0)
  AND (i.is_unique = 1 AND i.is_disabled = 0)
  AND (tbl.Name = 'Warehouse')

The query is same but the difference is that,
First one is using (i.index_id > 0 AND i.is_hypothetical = 0) in JOIN clause where second one using the same in WHERE clause. Is this impact on query or performance?

As JNK said, for an INNER JOIN, these are going to be the same. You can prove it by comparing actual (don't bother with estimated) execution plans. In this case, they are exactly the same (click to enlarge):

Personally, I like to keep the join conditions and the filter conditions separate. Join conditions go in the ON clause, and filter conditions go in the WHERE clause. This is one of the primary benefits of having explicit INNER JOIN syntax in the first place, which helps to reduce the risk of returning too many rows because of not enough (or even no) join criteria inherent in old-style joins (where filter and join criteria are thrown together) - see this blog post for more details.

JNK is also right though that you need to be careful when you're talking about OUTER JOIN. A simple example:

CREATE TABLE dbo.a(id INT);

CREATE TABLE dbo.b(id INT, name SYSNAME);

INSERT dbo.a(id) VALUES(1),(2),(3);

INSERT dbo.b(id) VALUES(2,N'a'),(3,N'b');

SELECT a.id, b.name
  FROM dbo.a
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id
  AND b.name LIKE N'b%';

SELECT a.id, b.name
  FROM dbo.a
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id
  WHERE b.name LIKE N'b%';
--^^^^^ only difference

DROP TABLE dbo.a, dbo.b;

Results:

id    name
----  ----
1     NULL
2     NULL
3     b

id    name
----  ----
3     b

As you can see from the results, the first query still returns all three rows from a (as you would expect), however the second query turns the OUTER JOIN into an INNER JOIN, and only returns the rows from a with a match from b on all conditions. You may want one behavior or the other, so neither of these is "worse" or "wrong," it's just important to understand the different functionality so you know to write the query to get the results you are after.


SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION I seem to be getting a lot of deadlocks when doing select count(*) on a particular table.
I have already changed all the required parameters and made them into row only locking.

I've also changed the database to use READ_COMMITTED_SNAPSHOT isolation,

however, it seems that using a select count(*)  where column = ? on the table triggers deadlocks or locks on the table..

Am I correct that the select count(*) should only be accessing intermediate rows?, however, it doesn't seem that way and I'm still encountering deadlocks. Proper indexing would probably help,

The question is: Does SQL server 2008 R2 place shared lock on table during select count(*) even when read_committed_snapshot is set to on?

Thanks

Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.

Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.

I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your select. That way you will be sure your select never embraces in deadlocks, and you do not break any other code, like READ_COMMITTED_SNAPSHOT might.


SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary We have one way transactional replication enabled on SQL Server 2008 databases. Due to some issues we had to run DBCC checkDB, bringing the database to single user mode. When we complete the DBCC activity and enable replication again, replication won't work. We had to initialize snapshot again for replication to work.

Can you please help me understand why replication is breaking in this scenario? I understand the DBCC drops some rows and indexes in a table, but as per my understanding when we enable replication these changes should be propagated to subscriber without any issues.

When I checked with my senior team member, I was told this is due to LSN mismatch, but I am not able to understand logically.

Any help is much appreciated.

3 important pieces of information are missing to pinpoint exactly what went wrong in your particular scenario:

What where the exact steps you took, from the moment you found a problem until the moment you discovered that replication didn't work?
What was broken, what was repaired, what was lost? All of this information is available in the output of CHECKDB.
What exactly do you mean by: "Replication don't work?" Did you get any errors, or are tables not the same? Is data missing?

If you face database corruption in a replication environment and you don't know exactly how to interpret the repair results OR if your publication is not very big, my advise would be to always reinitialize (if possible) or drop/recreate the publication en subscription.

The reason is that most repair operations performed by DBCC CHECKDB are not replicated to the subscribers.(see demo later on) If you have very large publications  AND you know exactly how to read the DBCC CHECKDB output you might be able to manually fix things on a table or row level.

Main reasons for replication failure after publication db corruption

Transaction log corruption
Metadata corruption
User data corruption

General setup

I'm using the following script to setup a publisher, distributor, subscriber on the same server, create a source and destination database and a table with 10k rows and a publication and subscription for this table. I'll use this setup for the next tests:

Version:

        SELECT @@VERSION

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SETUP SCRIPT:

--Create a source database
CREATE DATABASE [RepSource]
GO
--Create a destination database
CREATE DATABASE [RepDest]
GO

USE [RepSource]
GO

--Create a table that we are going to publish
CREATE TABLE [dbo].[RepUserTable]
(
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TestString] [varchar](4000) NOT NULL DEFAULT (replicate('A',(4000))),
    CONSTRAINT [PK_RepUserTable] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

--fill the table with 10k rows
SET NOCOUNT ON
INSERT INTO dbo.RepUserTable DEFAULT VALUES
GO 10000

--configure distribution
USE master
GO
-- declare local server as distributor
exec sp_adddistributor @distributor = @@SERVERNAME,  @heartbeat_interval= 10, @password = N'Dis@Password'
GO

--install local distribution db
exec sp_adddistributiondb   @database = N'distribution'
GO

--find the default backup directory to use as a working directory
DECLARE @path NVARCHAR(4000)
EXEC master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            @path OUTPUT,
            'no_output'

--Make this server a publisher
exec sp_adddistpublisher    @publisher = @@SERVERNAME,
                            @distribution_db = N'distribution',
                            @security_mode = 1,
                            @working_directory = @path, --Make sure the sql agent account has enough permission in this dir.
                            @trusted = N'false',
                            @thirdparty_flag = 0,
                            @publisher_type = N'MSSQLSERVER'

GO

--Make this server the subscriber as well
exec sp_addsubscriber       @subscriber = @@SERVERNAME,
                            @type = 0,
                            @description = N'Subscriber',
                            @security_mode=1

GO

--Adding a publication
use [RepSource]
exec sp_replicationdboption @dbname = N'RepSource', @optname = N'publish', @value = N'true'
GO

exec sp_addpublication @publication = N'TestPub2', @description = N'Transactional publication of database.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TestPub2', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec sp_addarticle @publication = N'TestPub2', @article = N'RepUserTable', @source_owner = N'dbo', @source_object = N'RepUserTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'RepUserTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboRepUserTable', @del_cmd = N'CALL sp_MSdel_dboRepUserTable', @upd_cmd = N'SCALL sp_MSupd_dboRepUserTable'
GO

--Add a subscription
use [RepSource]
exec sp_addsubscription @publication = N'TestPub2', @subscriber = @@servername, @destination_db = N'RepDest', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'TestPub2', @subscriber = @@servername, @subscriber_db = N'RepDest', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20140107, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

After running the script, start the snapshot agent and you should be done.

Transaction Log corruption

When you hit transaction log corruption and you rebuild the log file by using DBCC CHECKDB (RepSource,REPAIR_ALLOW_DATA_LOSS) in Emergency mode you'll see that the publication and subscription are gone. You have to recreate these.

Stop SQL Server
rename the transaction log file
start SQL Server and SQL Agent Service

Confirm that your database is now corrupt:

SELECT state_desc
FROM sys.databases
WHERE name='RepSource'

OUTPUT:
state_desc
------------------------------------------------------------
RECOVERY_PENDING

--try getting the db online
ALTER DATABASE [RepSource] SET ONLINE

OUTPUT:
File activation failure. The physical file name "e:\SQLServer\Log\RepSource_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

To fix this problem (without backup/restore) you would have to perform a emergency repair:

ALTER DATABASE [RepSource] SET EMERGENCY
ALTER DATABASE [RepSource] SET SINGLE_USER

DBCC CHECKDB (RepSource,repair_allow_data_loss)
ALTER DATABASE [RepSource] SET MULTI_USER

OUTPUT:
    File activation failure. The physical file name "e:\SQLServer\Log\RepSource_log.ldf" may be incorrect.
    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
    Warning: The log for database 'RepSource' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

Now, if you look at the output you can see why transactional replication would no longer work:

  Warning: The log for database 'RepSource' has been rebuilt.
  Transactional consistency has been lost. The RESTORE chain was broken,
  and the server no longer has context on the previous log files, so you
  will need to know what they were.

Transactional Replication is based on the transaction log and breaking the LSN Chain breaks replication.

If you look at your publication and subscription you'll see that they are both gone. You have to recreate them from scratch.

NOTE: Deleting the log file is quite big step. I don't have any tools to only corrupt a single log record in the transaction log file. I'm not sure if the publication and subscription would be dropped too in that scenario.

Metadata corruption

When you configure replication, there are several system tables created that hold data that is needed for replication to work. So called metadata. There are tables created in master, MSDB, distribution db, publishing db and subcribing db.

Personally I don't have experience with this kind of corruption but supposedly you would have to re-setup replication if any of these system tables in your publishing db gets corrupted. I just tested it strangely I was able to corrupt syspublications, sysarticles, sysarticleupdates and replication kept on going. ??

User data corruption

Now this is where it gets interesting. Because you could have inconsistencies between publisher and subscriber without you knowing.

The reason is, that if you "fix" stuff with DBCC REPAIR_ALLOW_DATA_LOSS it's fixed on a physical level. Transactional replication works with a logreader that reads all transactions in the publishing database log file. If the transaction is marked for replication, it is read and translation in a logically replication statement that is executed on the subcriber. It can deal with Statements like INSERT, DELETE, etc. But it can't deal with repair statements where for example datapages are deallocated. It's not that the repair is not logged. it is! It's just that the log reader doesn't know how to handle them.

Here is an example:

Let's find out which pages are used for the ReUserTable that we created in the RepSource db with the previous script:

--create user data corruption of usertable
use [RepSource]
Go

--find the data pages of RepUserTable
DBCC IND ('RepSource','RepUserTable',1)

OUTPUT:

We'll take page 296 for this example. It's a data page (Pagetype=1).
Let's look at which 2 records are on this page. (Each record is 4015 Bytes so 2 records per page.)

DBCC TRACEON(3604)
DBCC PAGE('RepSource',1,296,3)

OUTPUT

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

ID = 3

.......

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

ID = 4

So on this page we have the records with ID 3 and ID 4.

If I try and select the rows AFTER I have corrupted page 296 I get a checksum error:

SELECT * FROM RepUserTable WHERE ID=3

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb5aca98c; actual: 0xb58ea98c). It occurred during a read of page (1:296) in database ID 18 at offset 0x00000000250000 in file 'e:\SQLServer\Data\RepSource.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.

Since this is corruption on the clustered index I can only fix this with REPAIR_ALLOW_DATA_LOSS

--RUN DBCC CHECKDB
DBCC CHECKDB('RepSource')

OUTPUT:
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 389576426, index ID 1, partition ID 72057594039435264, alloc unit ID 72057594043760640 (type In-row data), page (1:296). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 389576426, index ID 1, partition ID 72057594039435264, alloc unit ID 72057594043760640 (type In-row data): Page (1:296) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 389576426, index ID 1, partition ID 72057594039435264, alloc unit ID 72057594043760640 (type In-row data). Page (1:296) was not seen in the scan although its parent (1:295) and previous (1:293) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 389576426, index ID 1, partition ID 72057594039435264, alloc unit ID 72057594043760640 (type In-row data). Page (1:297) is missing a reference from previous page (1:296). Possible chain linkage problem.

.......
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (RepSource).

Let's do that:

    ALTER DATABASE [RepSource] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DBCC CHECKDB (RepSource,repair_allow_data_loss)
    ALTER DATABASE [RepSource] SET MULTI_USER
OUTPUT
Repair: The Clustered index successfully rebuilt for the object "dbo.RepUserTable" in database "RepSource".
    Repair: The page (1:296) has been deallocated from object ID 389576426, index ID 1, partition ID 72057594042056704, alloc unit ID 72057594046840832 (type In-row data).
    The error has been repaired.
    There are 9998 rows in 4999 pages for object "RepUserTable".

Now here is the important info:
Repair: The page (1:296) has been deallocated from object ID 389576426

The repair works by rebuilding the clustered index on a new set of pages and it simply skips the corrupted page. It fixes all references in allocation pages and linked pages. After the rebuild, it is as if the page was never there. (This is all logged.) The repair can't work ona row level since I corrupted the header and DBCC CHECKDB simply doesn't know what is on the page.

the result:
There are 9998 rows in 4999 pages for object "RepUserTable".

1 page gone and 2 records gone.
The logreader agent has no clue on how many rows where on that page. so it has no idea on how to replicate this to the subscriber.

If you now restart the log agent. You'll see that Replication doesn't fail.. However:

On the source we don't have record 3 and 4 but they are still present on the subscriber:

Select * from RepUserTable where ID IN (3,4)
/*
ID          TestString
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)
*/

--INSERT record into source table to show that replication is still working
INSERT INTO RepUserTable DEFAULT VALUES

Select MAX(ID) from RepUserTable
/*
ID
-----------
10000
*/
Select MAX(ID) as ID from RepDest.dbo.RepuserTable

/*
ID
-----------
10001
*/

Select * from RepDest.dbo.RepuserTable WHERE ID=3
/*
ID          TestString
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
*/

--You now have Inconsistent data

If you would now re insert record 3 and 4 on the source database you would get a PK violation error when these rows are replicated to the subscriber. That won't work. And I'm assuming that this is what you mean by "Replication don't work"

But more important. If you wouldn't do that, you wouldn't notice anything. But you would have more records on the subscriber.


Find last (max) value according to TimeStamp using update method I have task to find  values of integer according to last (max) of timestamp (datetime) value.
Because it is so complicated query I am consindering this way to find last value something like this

Pseudo code:

update my_table
    set value=last_value
from my_table
    inner join (
                 select *
                    from (
                    select top(100) percent
                        from
                            ( select ts,last_value,pk_v from tb1
                                    union all
                              select ts,last_value,pk_v from tb2
                              ..
                              ) as temp
                              order by ts
                              ) as temp_order
                            ) as temp on my_table.pk_v=temp.pk_v

The idea is to make sub-query which is ordered by timestamp and then to update table.
In this case, sometime is going to be more values in subquery for one pk_val (primary_key value).

For me now looks like that it is impossible and I am considering to do updates in CURSOR. But before I go on, I would love to hear you option this.

Cleaner question would be: How updates working when in one transaction (select) SQL need to update same row two times ?

*EDIT:1 *
Here is sample with data

create table #Table_To_update
(pk int not null,last_value int)

insert into #Table_To_update
select 1,null
union all
select 2,null
union all
select 3,null

create table #table_as_sub_query
(fk int, value int ,ts datetime)

insert into #table_as_sub_query
select 1,5,'2012-01-01'
union all
select 1,6,'2012-03-01'
union all
select 1,2,'2012-04-01'
union all
select 2,7,'2012-02-01'
union all
select 2,8,'2012-02-05'
union all
select 2,6,'2012-04-01'
union all
select 3,0,'2012-01-01'
union all
select 3,9,'2012-05-05'
union all
select 3,12,'2012-01-01'

/*--This Way I want to update new table with last values --*/
update #Table_To_update
set last_value=table2.value
from #Table_To_update table1
inner join (select
                top(100) percent
                *
            from #table_as_sub_query
                order by ts desc
                ) as table2 on table1.pk=table2.fk

Your question is not clear. You are trying to update the value of some table based on a value of another? This can be solved using something like the query below:

In SQL Server 2005 or newer:

with my_data_cte (
  select row_number() over (order by timestamp desc) as sequence,
  pk_vv
from (
  select timestamp, pk_v from tb1
  union all
  select timestamp, pk_v from tb2
 )
)
update my_table
set value = pk_v
from my_table mt
  inner join my_data_cte ct on mt.pk_v = mt.pk_v
where sequence = 1


Most suitable database design for multi store inventory? I am trying to understand how to manage multiple stores in a inventory management system (Each store has large number of data). There are 123 stores in this shop. Some stores have sub stores.

So I planned to create one master database (only super admin can access this) and one separate database for each store and database user for each database.

I have some doubts about this approach.

1). Is this design is correct? If not what are the suitable ways?

2). What is the best DBMS for this kind of projects?

3). How to update master database to get daily sales, monthly sales, employee details etc real time?

4). How many databases I can create using this

This is not a challenge for one, modern RDBMS. You will realize you do not want to have one database per store the first time executive management asks for "How many iPhone 7s do we have on hand across all stores ahead of the new Apple launch?"
I have seen retail systems managing several hundred thousand SKUs across several hundred stores. The master inventory was one ~10 million row table with columns like:

Store ID
SKU
Units on hand

If you use partitioning on your table, queries can be sub-second.
Edit:
There is an open retail data model that handles multiple stores that you can see here:
http://www.omg.org/retail-depository/arts-odm-73/


Oracle 12 Startup upgrade throws error 443 Please follow the logic of my attempts at upgrade before suggesting a fix.  The error 443 is not the main problem here.

we are upgrading from oracle 11.2.0.4 to 12.2.0.1 on a Solaris 11.3 server 'test'.
all upgrades are successful except the one database called VIT which throws the error on startup upgrade (after logging in as oracle12): terminating the instance due to error 443
we cloned VIT and mounted it on another server.  It upgrades successfully.  We then mounted the same clone on 'test' and it does not startup, throwing the same error 443.
we then renamed VIT to VITNEW on 'test' and the upgrade works!  We then renamed it back to VIT and it throws the same error 443 on startup.

So after all these attempts it's obvious to me that there is either something with the name or the server, but bear in mind other databases work on that same server, and it's ONLY that name of VIT that does not work on 'test'.

Weird problem.  We have opened a SR for it, but Oracle have no answers yet.  Hoping someone might have an idea.

We got to the bottom of this eventually!

It was an oracle group write (g+w) permission missing in the parent folder of the diagnostic_dest structure.  Why it gives an error 443 and not a simple write permission error, only the oracle engineers who created this enigmatic diagnostic_dest structure will know!


How can I get a list of tables contained in a database diagram? I need to get a list of the tables contained in a diagram in SQL Server. How can I do it using an SQL script?

I don't need a list of tables in the database, but the list of tables contained in a diagram of the database in SQL Server 2008r2/2012.

Although this question is a year old, if anyone stumble over this, I've posted a solution to make that possible here:

http://www.codeproject.com/Messages/4914888/Search-through-the-binary-for-all-contained-texts.aspx

The trick is to extract all ASCII characters in the diagram binary, you'll see that all table names are in there as simple text. That seems to be the reason why a diagram is also destroyed if you rename a table with a script - because it doesn't use a table ID to identify the table but the table name. If it's no longer there, the diagram is not usable anymore.

With this method you can identify which diagrams contains a specific table.


MSSQL - Should I mention column max length? I am a bit confused from what I read so far regarding to this issue.
In my database I usually write the max length of a column when I create a table or add a column.
Because I need to support other languages than English, some of my columns are nvarchar type,
the problem occurs when a programmer is not aware he can insert to a nvarchar column half of the length mentioned and this is causes sometimes errors.
My question is what would happen if I will stop writing the max length of the columns, what would be the actual size of the content, any performance affect?
We are validating the content is the client and server side so if I will have a column without max length it won't be more than what we defined in the server side so I am not worry about this.

This is 100% an education problem for your developers and that is where this needs to be addressed.
Not specifying variable column length is a bad idea that will probably break almost immediately for you. Spoiler: it gives severely small lengths to the column.
Similarly, using MAX as the length by default is also a bad idea and will set you up for performance and architectural challenges down the line.
You should stick to using the smallest variable length fields that fit your business requirements and do some sessions with your developers to ensure they understand how SQL Server handles variable length data types, or check their work more closely in pull requests.
In the case where you have already tried this (which I hope is not the case) I have written a script sp_sizeoptimiser that flags occurrences of this problem to help you root out and remove them.


The value for the parameter @MirrorDirectory is not supported error with Ola Hallengren Backup script I am using Ola Hallengren's Maintenance Solution, and it's been working great for our environment.

Quick question regarding the User Database backups s proc. Currently, when I'm taking backups, the backup files are getting saved to one of the servers local drive (Z: drive). But if I wanted to also get another copy of those same backup sets to a different drive or folder, I was thinking the @MirrorDirectory is where we can enter a different drive path for the backup files. (Please correct me if I'm wrong).

I wanted to test this, so I have duplicated the original the User Backup Database sqlserver job for my main backup job and renamed this new sqlserver job a "DatabaseBackup - USER_DATABASES - FULL_COPYONLY". Here's what I have on the first and only job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\COPYONLY_BACKUPS',@MirrorDirectory =N'C:\MIRRORBAKS', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @MirrorCleanupTime= 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I'm currently testing this in the hope that it will place the main backup files on "Z:\COPYONLY_BACKUPS" folder, and place another backup set on "C:\MIRRORBAKS" foder. (I'm place them in C drive just for testing).

When I run the job, it is failing and the message is:
 Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 436  The value for the parameter @MirrorDirectory is not supported.     Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 740  The documentation is available at https://ola.hallengren.com/sql-server-backup.html.     Date and time: 2018-11-19 11:05:58.  Process Exit Code 1.  The step failed.

Could anyone shed some light on why it is saying the parameter for @MirrorDirectory is not supported? Thank you very much.

The backup procedure does a number of validation checks against the path to ensure it is valid. One of them checks the edition of SQL Server to ensure it can perform mirrored backups.

Run the below code and if it does not return 3 then you don't have Enterprise Edition and cannot use Mirrored Backups:

SELECT SERVERPROPERTY('EngineEdition')

See: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/mirrored-backup-media-sets-sql-server?view=sql-server-2017


Delete backup directories using RMAN Is there any way RMAN can delete empty backup directories?

No. rman does not delete directories that it did not create by itself.


Trigger doesn't run when value of a column changes from null to 1 I have this trigger:

delimiter $$

create trigger tr
after update on t1
for each row
begin

  if new.col1 !=old.col1
    then update t2 set col2 =1 where t2.col3=t1.col3;
  end if;

end

$$

This trigger works when col1's value changes from 0 to 1 but
when its changes from null to 1, the trigger doesn't make any changes in t2.

I just wanted to know why it is so. I know null is different, but I'm just comparing the values.

I have solved my problem by doing new.col1 =1.

Change the if condition to:

if (new.col1 != old.col1) or ( new.col1 is not null and old.col1 is null )
                          or ( old.col1 is not null and new.col1 is null )

That should solve your problem. The original code didn't work correctly as you can't  test for equality with a NULL value - you have to use IS NULL or IS NOT NULL.

MySQL has also a "null-safe equals" operator: <=> which can be used here to make the above condition simpler (and still equivalent):

if not (new.col1 <=> old.col1)


Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not? What are the drawbacks and downsides?

We want to schedule automatic nightly refreshes of 20 databases, approximately 1GB each, from the PRODUCTION server to the TEST server.  We're setting up SQL Server snapshot replication to publish snapshot updates from the PRODUCTION databases to TEST.  Are there gotchas we should know about?

Your scenario is interesting :

Small databases approx 1GB in size.
Number of databases = 20
Refresh of the data occurs on nightly basis, so the data can be stale by one day.

Initial thought reveals that snapshot replication should be OK as you thought, but I would highly recommend to go for backup/restore method - safe, reliable and less overhead of maintaining.

Note that I am not saying that snapshot replication is a bad choice, but why not use a proven and reliable method - backup restore ? You can/should automate it (backup/restore) using either Powershell or T-SQL or SSIS.

Drawbacks of Snapshot replication :

You cannot drop/create tables, sp's, functions, that are marked for replication. Also, you cannot directly alter a table, you have to use system stored procedures sp_repldropcolumn and sp_repladdcolumn.
A standard snapshot will lock the tables during the BCP step to make sure you get a valid copy.
Any schema changes requires a new snapshot to be generated which is quiet resource intensive on the publisher side.
Additional overhead on the server occurs if you have a local distributor.
End up monitoring multiple services running on the server.
Dependency on SQL Server Agent.
Windows NT permissions on the snapshot folder.
Network bandwidth.


Why shouldn't user-defined database roles be members of fixed roles? I'm redesigning security setup in our database. I was in process of defining certain user-defined roles and - as I find it handy - on the other monitor I was browsing documentation to make sure I don't miss anything.

And in this resource from the Microsoft Docs I noticed the following:

  Do not add user-defined database roles as members of fixed roles. This could enable unintended privilege escalation.

Now - either I'm tired or there is no good explanation for this tip.

In fact I find it common to define specific role and ensure they have proper permission through fixed role membership.

Simplified examples:

"power user" = db_datareader + db_datawriter
"developer" = db_datareader + db_datawriter + ddladmin
"function designer" = db_datareader + GRANT CREATE FUNCTION on database:xyz

etc.

It's just handy.

I'm putting this security setup on hold - hopefully someone can give me some good pointers here.

The author is recommending that you practice the Principle of Least Privilege.

Basically, the author is saying that if you create a Role, it should do exactly what you define that role to do and nothing else at all. If you then assign it to a built-in role, you're now greatly expanding the role's permissions and losing some control over exactly what those permissions might be.


Database Restore Failed Due to Incompatible Version of SQL Server I am working on different two SQL Server, I need to transfer database from one server to another but problem is the servers have different versions.

Below is image of error message.

Using SELECT @@Version I have checked database version like below,

First Database Version (Source Database):

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64)
Jul 11 2012 15:47:13   Copyright (c) Microsoft Corporation  Web Edition (64-bit)
on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Second Database Version (Destination Database):

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit)
on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

How can I do this?

+1 to Doug L., I'd recommend the same (export all data and structure into SQL script and data dumps, then import) but on top of that: if you can connect the two DBs you can cross-server SELECT/INSERT INTO the tables and data from old DB to the new one; you can also try upgrading the RTM server (if you are allowed to do that) - I'm unsure on this but I think if you install SP on the RTM it'll be considered as same version as the other guy.


Can't take backup to the shared folder Need your help. My sql job is not able to take backup to the shared folder although the domain account, under which the sql agent service account is running, has full permission on that shared folder.
In addition to that, my sql service account uses default virtual login. So when I change that account to the domain account that I also used for sql agent service account, then the problem is fixed.
Why can't I take backup to shared folder using sql agent service account?

The service account for Agent is irrelevant. All Agent does it to tell SQL Server to produce a backup file. It is SQL Server that produces the backup file, hence it is the service account for the SQL server service that matters. You can't do anything about that.
So, either run SQL Server using a domain account; or use the virtual service account and assign privileges on the share to the domain computer account.


create one private database link for two users oracle I am trying to create one private database link to be accessed by only two users but it gives me syntax error.

My code:

CREATE DATABASE LINK "mylink"
 CONNECT TO USER1,USER2
 IDENTIFIED BY password123
 USING 'mydb';

When you select from a table with dblink first time in current session, then oracle needs to connect from one server to another.

So your current oracle instance is SRV1, and other is MYDB. When you issue first time in current session

select * from t@mylink

then oracle create session from SRV1 instance to MYDB instance. How oracle do it? Sends the connect command with username, password and TNS name (MYDB here). This information we provide when create dblink - user, password, TNS name (db name).

You need TWO private database links, first for one user (A), and the second for another (B).

SQL> connect a/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...

SQL> connect b/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...


Too many Partitions, removing older years This is a follow-on to this question a few years ago Splitting Existing Partitions in SQL 2014 where now there are partition years for 2011 all the way to 2021.
I need to archive partitions 2011 to 2015 by moving the data from these partitions to a new table/database, and then archive this database, and remove the partitions for 2011 to 2015 from the original Log table.
I'm successfully moving the data out to a Log_Archive table with the following statement.
ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20120101')
    TO dbo.Log_Archive PARTITION $PARTITION.pfYearlyPartition_Log_Archive('20120101');

What I'm not sure of is how I now remove that partition from the Log table, as it's still there but with 0 rows within it. Can someone advise? You can see below I've successfully moved the data from Log to Log_Archive but I'm left with 0 rows in the original Log table that I want to remove.

I'm hoping I don't need to re-partition the whole table as it's very large.

Once the partition is empty and no other partitioned table on a partition scheme using the same partition function has data in the corresponding partition, just use ALTER PARTITION FUNCTION to MERGE away boundary point in the empty partition.


Our replica set config is invalid or we are not a member of it - MongoDB I have started the mongodb server with replica set name 'rs01' enabled on port 27001.

Then started another command window to start the mongo db on the same port.

The status says the 'rs0' is not a member of the replica set.

rs0:OTHER> rs.status()
{
        "operationTime" : Timestamp(1552575009, 1),
        "ok" : 0,
        "errmsg" : "Our replica set config is invalid or we are not a member of it",
        "code" : 93,
        "codeName" : "InvalidReplicaSetConfig",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1552575009, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

But, if I try to register the member, below error message happens. Any help would be appreciated.

rs0:OTHER> rs.initiate({_id:"rs0",members[{_id:0,host:"localhost:27001"}]})
2019-03-14T22:14:39.840+0530 E QUERY    [js] SyntaxError: missing : after property id @(shell):1:30
rs0:OTHER> rsconf={ _id:"rs0", members: [ { _id: 0,host:"localhost:27001"}]}
{
        "_id" : "rs0",
        "members" : [
                {
                        "_id" : 0,
                        "host" : "localhost:27001"
                }
        ]
}
rs0:OTHER> rs.initiate(rsconf)
{
        "operationTime" : Timestamp(1552575009, 1),
        "ok" : 0,
        "errmsg" : "already initialized",
        "code" : 23,
        "codeName" : "AlreadyInitialized",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1552575009, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

mongodb

As per error it seems like you have not configured properly the replica set. How to setup MongoDB Replica Set , you find here.

For example I am writing the steps here to setup MongoDB Replica Set

1) Start a mongod instance.

2) Start another mongod instance.

3) Start Replication.

After launcing the all mongod node instances, connect to any one node and start the mongo shell then type in command prompt

  rs.initiate()

For example

Now the mongo shell prompt would be changed to yournodeinstancename:PRIMARY>

You may also check the status using rs.status() method.

  rs.status()

for more info about rs.status() see the MongoDB documentation here and here

4) Add a MongoDB instance to the Replica Set.

  rs.add();

5) Check the Status.

You may check the status of the Replica Set by running the following command

  rs.status();

6) Getting an overview of the replica set and if current node is master

  rs.isMaster()

For further your ref here , here , here


Going out of disk space in the course of querying a postgres database Do SELECT queries (w/ table JOINs) on a database affect disk space whilst they're running?

Background: I have a Django app with a Postgresql backend (9.3.10). My DB resides in a VM that was running critically low on disk space (around ~400MB left).

I queried a few tables to assess which data to deprecate in order to release disk space (these included joins across tables). These analytical-type queries were bundled behind a single url, and run in unison. When I hit the url, the VM containing my DB went out of space after around half a minute.

I'm an accidental DBA and still learning the ropes. Can anyone explain why I went out of space in this scenario? Are some kind of temporary files created in such operations? I'll share my config details in case they're needed.

Of course, there are actually many ways for a SELECT to take disk space. Just look at the docs for work_mem

  work_mem (integer)
  Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

Even with a high work_mem a SELECT can chew away disk space. You can for instance, have a function required that writes to disk directly.

You may be interested in mitigating this by using temp_file_limit

  temp_file_limit (integer)
  Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. The value is specified in kilobytes, and -1 (the default) means no limit. Only superusers can change this setting.


What is the best way to cross check the record in multiple collections I am developing the application in which system will show the campaign content to users which he/s never seen. To keep the logs either the specific user seen the specific content or not, i am maintaining logs of user activity in MongoDB collection. Here's what a campaign record in the database looks like.

campaigns

{
    "campaign_id": "123",
    "campaign_image": "<image path>",
    "min_user_age": 10
}

Here is how the user's activity log collection like

user_activity

{
    "i123i12391i2391i23kjnsa": {
        "user_id": 12,
        "campaign_ad_id": 34234,
    },
    "xajeou424u2934y293y4293": {
        "user_id": 32423,
        "campaign_ad_id": 34234,
    },
    ...
}

The user_activity collection will have approx 7 million records and campaigns will have more than 10 thousand records. I will always have user's id to get the campaign to show him. Here i want to get only those campaigns from campaigns collection which are not seen by the specific user.

I can do like fetch records from campaigns and then check that their records do not exist in user_activity collection based on specific user_id.

$campaigns = db.getCollection('campaigns').find({});

Loop though $campaigns and make sure activity does not exist for particular campaign_id and user_id.

$isExists = db.getCollection('user_activity').find({"campaign_id": 34234, "user_id": 12});

What is the best way to get the campaigns that are not seen by the user?

I am using mongodb 3.4.4.

Thanks

You could do an aggregation pipeline using the lookup operation, where you only need to specify the user ID.

Would be something like this:

var my_user_id = "1234";
db.getCollection('campaigns').aggregate([
{
    $lookup:
    {
        from: "user_activity",
        let: { campaign_id_var: "$campaign_id" },
        pipeline: [
        { $match:
            { $expr:
                { $and:
                    [
                    { $eq: [ "$user_id",  my_user_id ] },
                    { $eq: [ "$campaign_ad_id", "$$campaign_id_var" ] }
                    ]
                }
            }
        },
        { $project: { user_id: 1, campaign_ad_id: 0 } }
        ],
        as: "someactivity"
    }
},
{
    $match: { "someactivity": { $eq: [] } }
}
]);


PostgreSQL UNIQUE constraint error message On Appendix A: PostgreSQL error codes, it says:

  For some types of errors, the server reports the name of a database object (a table, table column, data type, or constraint) associated with the error; for example, the name of the unique constraint that caused a unique_violation error. Such names are supplied in separate fields of the error report message so that applications need not try to extract them from the possibly-localized human-readable text of the message.

Bold emphasis mine. I have the following table:

CREATE TABLE recipes (
    id SERIAL,
    title TEXT UNIQUE NOT NULL,
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

When I try to insert a new row with a duplicate title, I get the following error message in pgAdmin3:

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(mytitle) already exists.

Or, using PHP:

["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "23505"
    [1]=>
    int(7)
    [2]=>
    string(117) "ERROR:  duplicate key value violates unique constraint "title_unique"
                 DETAIL:  Key (title)=(mytitle) already exists."
  }

According to the paragraph from the PostgreSQL documentation, shouldn't the constraint name title_unique be found in a separate field of the error info?

I'm using PostgreSQL 9.4.5.

For instance, you can read these separate fields mentioned in your quote inside exception handlers in PL/pgSQL (as instructed in the manual, like @dezso commented):

DO
$$
DECLARE
   err_constraint text;

BEGIN
   INSERT INTO recipes (title) VALUES ('foo'), ('foo');  -- provoke unique violation

EXCEPTION
   WHEN SQLSTATE '23000' THEN  -- Class 23 — Integrity Constraint Violation
      GET STACKED DIAGNOSTICS err_constraint = CONSTRAINT_NAME;
      -- do something with it, for instance:
      RAISE NOTICE '%', err_constraint;
      RAISE;  -- raise original error

END;
$$

Result:

NOTICE:  title_unique

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(foo) already exists.
CONTEXT:  SQL statement "INSERT INTO recipes (title) VALUES ('foo')"
PL/pgSQL function inline_code_block line 5 at SQL statement

Aside: you declare the unique constraints twice in your DDL command. (To my surprise, this results in a single constraint in my test on pg 9.5). Either way, do it only once.

CREATE TABLE recipes (
    id SERIAL,
    title TEXT NOT NULL,  -- remove redundant clause
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);


A good use case for Postgres JSONB? I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My issue is, each type needs to track different attributes. We would track a different set of properties / attributes for computers than say a monitor. I've read about EAV pattern (or anti pattern from what most people say) and it seems like that should be avoided by the plague. Creating a single table with all the columns just seems ridiculous, and creating a new table for each new type that comes along doesn't seem optimal. I recently read about JSONB in Postgres 9.4. Storing a JSON object that can be handled by the application seems like a decent compromise.

Is this a good use case for JSONB? Or does EAV make sense? Or making a giant table, or creating a table per type?

This is indeed a reasonable case for storing object-like or key/value data, and representing it as JSON in jsonb fields in PostgreSQL is a reasonable way to do that.

In general it's time to consider hstore, xml, jsonb, etc when you're starting to look at alternatives like EAV or wide tables where the app adds columns dynamically. jsonb is basically the new hstore with a more standard way of representing its data and with nesting capabilities, so it's preferable most of the time.

Static tables for different kinds of thing, with parent tables for more general categories of thing, still make sense if the things being tracked are (a) fairly static and not user-defined, and (b) not excessively numerous. That does not sound like the case here.

I still recommend keeping attributes as regular columns where:

They form part of a referential relationship (is-a, has-a, contains, is-contained-by, owns, is-owned-by, etc);
They are a natural key where a UNIQUE constraint makes sense;
They are filtered on or otherwise queried in the large majority of queries

You can define a unique constraint - or rather, unique index - over an expression that queries the json field. It's just often cleaner to have them separate. Similarly expression indexes can help with oft-queried fields you want to keep part of the json object. It's really the first point that's the most important, as RI constraints cannot reference the value of a json object key.


MySQL InnoDB + high table creation-destruction rate We are transferring a microsoft SQL based website (+app + plugins) structure to MySQL.

The website has a high "Content + Comments + Activity Tracking" behaviour.

Mainly because of the activity tracking, InnoDB structure looks like a must (high read, insert, update and truncate rates). All the activity is written intensively in a table for each content unity and it's periodically "compressed" to shrink the table.

To reduce the amount of tables in the DB, only the tables related to often accessed content are kept in the DB. Others are "packed" and deleted.

If any content becomes "active" again then we "unpack" and recreate the tables.

Our total amount of tables is about 16 million a year but, daily, we normally create and delete about 5k of those tables and about 50k remain always "unpacked" in the DB.

What kind of memory and HD space leak problems can this structure give us in MySQL-InnoDB? Is this a good practice or should we consider a structure change?

What kind of memory and HD space leak problems can this structure give
  us in MySQL-InnoDB?

Given your description it's impossible to give any meaningful value to memory or storage requirements.  You say "16 million per year" but what is that?  16 millions rows/tables?  With no schema example it's still impossible to estimate.   For tables you do have you can run

select table_name, data_length, index_length, data_free from information_schema.tables where table_schema='mydatabase';

To get a feel for current storage requirements.   What you'll be more interested in is growth rate over time though to ensure you're not going to run out of space in 3months down the road.  MySQL doesn't give a built in growth rate metric so that's something you'll need to periodically poll and record yourself.

For memory requirements, in a perfect world your innodb_buffer_pool would be big enough to accommodate all your data and indexes in memory.  It's never a perfect world though and can become impractical or cost prohibitive to have such a machine.  At the same time, strive to make the buffer pool as large as you can to keep most frequently accessed data in memory.

If you are straight up dropping tables the storage space will be reclaimed to the file system if and only if you are configured with innodb_file_per_table = true.  Other wise all your data is stored along with metadata and transaction history in ibdata1.

  Is this a good practice or should we consider a structure change?

It's not unheard of to offline old historical data.  However your description makes it seems like this becoming active is some what common.  I can't imagine any on demand process where you have to rebuild a table before being able to service the request.

Rebuilding a table and it's indexes is much slower then a raw copy of a file of similar size to the final table.

You say it's being "packed" and "unpacked" indicating it's still being stored somewhere.  If it's going to be needed to be accessible by an application just keep it in the database and let old pages fall out of the buffer pool.  When requested again the information needs to be read from disk versus having a table completely rebuilt.


Each node holds 50% of data on 2 nodes with replication_factor=1 Description: Linux OS, Cassandra 3.9, Using CCM to create 2 Nodes, Consistent Level = One

replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}

Using writing script (* ) as below (Cassandra-Python-Driver) to insert data into cluster.

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement
cluster = Cluster(['127.0.0.1','127.0.0.2'])
session=cluster.connect('luan')
count = 1
while(count < 500000):
 a=count
 b='luan'
 c='2016-01-01 01:01:01'
 query = SimpleStatement(
    "INSERT INTO t1 (a,b,c) VALUES (%s, %s, %s)", consistency_level=ConsistencyLevel.ONE)
 session.execute(query, (a,b,c))
 session.execute("INSERT INTO t2 (a,b) VALUES (%s,%s)", (1,a))
 count = count + 1

When running (* ) , getting problem when I stopped node1 OR node2 and then (* ) stopped.

  cassandra.cluster.NoHostAvailable: ('Unable to complete the operation
  against any hosts', {: Unavailable('Error
  from server: code=1000 [Unavailable exception] message="Cannot achieve
  consistency level ONE" info={'required_replicas': 1,
  'alive_replicas': 0, 'consistency': 'ONE'}',)})

I read on Cassandra Calculator, with those configurations, it said that Each node holds 50% of your data. (cluster size = 2, replication factor = 1, write level = one)

Case: replication_factor = 2, (* ) OK when I stopped node1 or node2

Question :

1/ Why replication_factor = 1, (* ) stopped when I stopped 1 in 2 nodes ?

2/ Why "Each node holds 50% of your data" as those configurations?

Replication Factor 1. Assuming that the cluster is balanced. There is 1 copy of your data (1 replica) in the cluster. Since you have 2 nodes and one replica, then each node will have 1/2 of the data of half a replica each. When you shut down 1 node, you lose access to 1/2 of the data.

Replication factor 2. There are 2 replicas of the data in the cluster. 2 nodes and 2 replicas -> each node can have 1 full replica. When you shut down 1 node, the other node still has a complete replica.


date not inserting - shows 0000-00-00 after insert I am trying to insert date to a mysql table. The SQL query I used to create the table is as follows:

CREATE TABLE IF NOT EXISTS DDD(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    datecolumn DATE NOT NULL ,
    open FLOAT,
    high FLOAT,
    low FLOAT,
    close FLOAT,
    volume INT)

I am inserting the data with the following SQL statement:

INSERT INTO DDD(datecolumn, open, high, low, close, volume)
VALUES(2011-05-26, 12.09, 13.31, 12.05, 13.09, 1293441)

After that when I select and view the data the entries in the datecolumn is

  0000-00-00

I have tried the solution from

importing CSVs into MySQL table leaves date fields as 0000-00-00

...but it resulted in the following error:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'datecolumn DATE' at line 1

after the following query,

ALTER TABLE db.DDD MODIFY datecolumn datecolumn DATE;

I am a novice in SQL and learning now. Could anyone shed some light, what it is that I am doing wrong?

Put the date into quotation marks:

INSERT INTO DDD(datecolumn, open, high, low, close, volume)
VALUES('2011-05-26', 12.09, 13.31, 12.05, 13.09, 1293441)

MySQL will have silently done some mathematics with 2011-05-26 and got its knickers in a twist.

It does warn you though:

mysql> insert into d VALUES(2011-05-26);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'datecolumn' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Without using quotes, MySQL is essentially inserting the integer 1980 ( which is 2011 minus 05 minus 26) as a date, as it treats it as mathematics:

mysql> select 2011-05-26;
+------------+
| 2011-05-26 |
+------------+
|       1980 |
+------------+
1 row in set (0.00 sec)

mysql>


MySQL hangs in killed state I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.

Scenario to reproduce:

OS: CentOS 6.5
MySQL: 5.6
Engine: MyISAM (with at least one BLOB/LONGTEXT column)
mysql config 'myisam_repair_threads' = 2

I have a table with 40MB of data and 1500 records. With myisam_repair_threads set to 1, OPTIMIZE completes within a second. Settings myisam_repair_threads to 2 takes forever to complete. After killing the query, it remains in the 'killed' state (at moment of writing already 3 hours). Seems like this is a bug in mysql.

However, the big question: How can I force to kill this query? It currently holds a lock on the meta data of the table, so some processes can't execute because they are waiting for this lock.

Any advice is appreciated!

From http://www.dbasquare.com/2012/05/15/why-do-threads-sometimes-stay-in-killed-state-in-mysql/:

  What actually happens when you run KILL?

  The command sytnax is KILL [QUERY | CONNECTION] , where
  thread_id is the value from the first column of the process list
  output. The optional argument determines whether only running query or
  should the entire session be terminated. It defaults to the latter, so
  specifying CONNECTION is not required.

  Running the command doesn’t actually do anything except for setting a special flag inside the selected thread. Therefore the kill
  operation doesn’t happen synchronously with the corresponding request.
  In many cases it takes some time for a thread or a query to stop. The
  flag is checked at various stages of statement execution. When it
  happens exactly, or how frequently, depends on the work a thread is
  actually doing.

...

  No matter what the real cause is, it is impossible to get rid of
  such hanging threads without a database restart. They should,
  however, simply be allowed to go away on their own.

  There is also no way of forcing a kill that would execute instantly like kill -9  in Unix systems.


Using Powershell Variables in a File-Stored Query with SQL Server I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell script, which would make what I'm trying to do trivial. What I need is to be able to have SQL Server do is to access the Powershell variable and use it in the query I'm sending. xp_cmdshell is coming up as a potential option to do this with, but I'd sooner not expose myself to the security vulnerabilities there if I don't have to.

Here's some pseudocode to get my idea across easier:

PSScript.ps1:

$var = 1
$query = query.sql
$con = connection(server_connection_params)
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
$command.ExecuteScalar()

Query.sql:

SELECT * FROM table WHERE field = $var;

Is there any way I could access $var in this fashion given how I'm calling the SQL in Powershell?

I expect the sample query you gave is basic, so my offer is basic but I think should work for you even with complex script files that require multiple variables to be passed/replaced.

The key here is you need to have a place holder in your script files that you can easily search for in PowerShell, then just replace it with that value.

An example query:

SELECT * FROM sys.databases WHERE name = 'var1'

In this example I am going to pull that query in from a file and then search for the var1 value, replacing it with the database name I want to query for...

$var = 'MyDatabase'
$query = Get-Content .\Sample.sql | foreach {$_.replace("var1",$var)}

Example usage:


You are hitting the limit on the GROUP_CONCAT() results length - group_concat_max_len as can be seen in http://sqlfiddle.com/#!9/bb7a66/16

You can raise it temporarily for your query:

SET SESSION group_concat_max_len = 10240;

http://sqlfiddle.com/#!9/bb7a66/19


Constrain a postgres timestamp to "End of day"? Given a table named bananas
and a timestamp without time zone column named end_time
and some psql clients have set timezone to 'UTC'
and other psql clients have set timezone to 'US/Eastern'
and the server config has timezone = 'UTC' in postgresql.conf

How would one write a check constraint on bananas.end_time to ensure that end_time is always the end of the day, defined as the 23rd hour and 59th minute and 59th second of a "US/Eastern" day?

I tried:

alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  23 = date_part('hour', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = date_part('minute', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = floor(date_part('second', end_time at time zone 'UTC' at time zone 'US/Eastern'))
)
;

This seems to correctly constrain the column, but it seems horrendously inefficient, and quite unreadable.  Is there a more efficient and/or more readable implementation?

For now
While stuck with your unfortunate solution:
CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time)

That's right, AT TIME ZONE two times:

The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times.

The second instance converts the timestamptz back to timestamp at your given time zone. Now you can just check that the time component is whatever you wish.

Cast to time, instead of converting to a string, that's cheaper and more robust.
To get rid of fractional digits you could cast to time(0) instead, but that rounds the values instead of floor in your example. Instead, truncate  with date_trunc(), which is the cheaper method for floor() with positive numbers:
CHECK ((date_trunc('sec', end_time) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time
        = '23:59:59'::time)
Proper solution
timestamp values have fractional digits and using the time component '23:59:59' as upper limit is an unfortunate decision. Instead, use 00:00 of the next day as exclusive upper border. It is trivial to enforce that with a check constraint.
Next, since you are dealing with multiple time zones, I would suggest to use timestamptz instead of timestamp. Internal storage is the same as with timestamp in the given time zone 'UTC', but input / output handling is different.

The timestamp is shifted to the current time zone automatically on output.
Input timestamps with time zon offset so the values are saved as according UTC times automatically.

Related answer on SO with a lot more details.


Why can't I create a index using column definition in MySQL? Consider the following create table syntax

CREATE TABLE test1 (f1 int primary key, f2 int, unique key(f2));

I can also write the above as follows

CREATE TABLE test1 (f1 int primary key, f2 int unique key);

Why can't I do the same for indexes?

CREATE TABLE test1 (f1 int primary key, f2 int, index(f2)); -- is valid
CREATE TABLE test1 (f1 int primary key, f2 int index); -- is invalid

I know that the later one is wrong as per syntax but can you detail the reasons on why MySQL chose to not allow index in column definition? While it's perfectly fine for unique keys what would be the problem for indexes?

Why can't I do the same for indexes?

They just haven't got around to implementing and testing that syntax extension yet, would be my guess. Support for such definitions may be added in a later version.

IIRC inline index definitions are not part of any of the SQL standards but are something that some DB engines have added for convenience, so if you wish to retain as much compatibility with a range of database back-ends this may be a consideration that stops you using inline index definitions. Other DBs do implement that syntax of course (SQL Server added both forms, the one mySQL supports and the one you are looking for, in the 2014 release with a few enhancements in 2016) but perhaps not in a directly compatible way.


Will performance be better if I specify a filter multiple times? Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN?

To clarify using code:

select *
from #main_select
left outer join
(
  select
    main_id
   ,sum(aggregated) as agg
  from #left_table
  group by main_id
) as grouped
on #main_select.id=grouped.main_id
where #main_select.id = 1

--same reuslts

select *
from #main_select
left outer join
(
  select
    main_id
   ,sum(aggregated) as agg
  from #left_table
  where main_id=1 -- in this case I added where same as Left outer join
  group by main_id
) as grouped
on #main_select.id=grouped.main_id
where #main_select.id = 1

In general there will be no difference. SQL is a declarative language that gets translated into an "execution plan" by the query optimizer. The goal of the optimizer is to provide the best execution plan the produces the results that were asked for in the query. That means that two logically equivalent queries should always produce the same execution plan.

So far the theory. In praxis however, there are way to many ways to build an execution plan for an even moderately complex query. (See for example my answer here: https://stackoverflow.com/questions/16974241/sql-server-join-selects-slower-than-join-select-with-local-table/16974641#16974641) That means that instead of aiming for the best plan, the optimizer can only aim for a good enough plan. The plans the optimizer is even considering are selected based on a set of rules and heuristics and are certainly influenced by the original query.

In SQL Server 2000 a lot of the performance optimization strategies were build around helping the optimizer to start with a better plan set by rewriting the query into another logically equivalent form. Since SQL 2005 the optimizer rarely needs that form of help anymore, but there are still a few queries out there that could benefit from a rewrite.

So to answer your question, if you observed that a query got faster by supplying that additional "hint" in the join condition, the above should give you an explanation. In general this is not the first thing to look at when trying to optimize a query.

Instead you should first make sure that your tables are properly indexed and all statistics are up to date.


Is binary logs required on the slave in order to do replication? I have a MySQL database setup with a master and a slave. As far as I understand, the master writes transaction events to a binary log, which is read and executed by the slave, thus making the slave a continuous copy of the master.

I was under the impression that I needed to make sure that the slave had consumed a specific binary log file before I could safely purge it. But now I have read documents indicating that the binary log isn't really used by the slave in order to facilitate replication. It is instead the relay log which is of interest on the slave. The binary log is only interesting to keep if I need to do point-in-time restores. Is this correct?

If correct, does this mean that I can either turn off binary logs on the slave or purge them all at any time?

Other than doing point-in-time restores, there are two additional reasons a Slave could have binary logging enabled.
REASON #1
If a Slave is also a Master

binary logging must be enabled on Slave
log-slave-updates must be enabled on the Slave

This would allow another Slave to connect to a Slave with binary logging enabled as a Master
REASON #2
If a Slave is not a Master, but you want to failover to the Slave to become a new Master

binary logging must be enabled on Slave
log-slave-updates must not be enabled on the Slave (this prevents binary logs from growing on a Slave as long as the DB Server has the role of a Slave)

That way, after failing over your application to the Slave to become the Master, you do not have to restart mysql to enable it later. You could then setup replication to the new Master.


How to show all users of all databases in mongodb? I know show users or db.getUsers() can show all users in current database in mongodb, but how to list all users in all databses for ease of management as an administrator?

You can find information about all users in all databases in system.users collection of admin database. See MongoDB documentation for system.users collection.


SQL Server 2008 log directory has too much dump file My SQL Server log directory has too many dump files. I have 3 databases on the server and the number of the transactions on them is very small, why would there be so many large files??

Start checking the log - the dump files are that - dump files. Logs should indicate why the server crashs so often. Check hardware issues. Are you properly patched up? Do that - install the last CU from SQL Server Release Services.

If nothing helps, then a MS PSS ticket needs to be opened, they can analyze the dump files. This likely leads to some hotfix, in which case it is a free ticket.

But first check logs and patch up the server.