Why some rows are missing in my recursive CTE?

If we slice your query, the first cte (rt2) returns this:

id   hid     parent_hid  hierarchy
11    2         1            2
22    3         2            3
44    5         3            5
77    8         5            8
170  11         8           11
190  13         11          13
240  18         15          18
255  20         18          20

The first SELECT ([root]) in the second cte (rt1) returns this:

id   hid     parent_hid  hierarchy
11    2         1            2

This is indeed the only row with parent_hid = 1. The recursive CTE starts with this single value and goes all the way down the hierarchy until there is no match anymore.

From this, the second SELECT recursively joins rt2 with rt1 on rt1.hid = rt2.parent_id:

id   hid     parent_hid  hierarchy
11    2         1            2 -+                1->2                   => start
22    3         2            3 -+ -+             1->2->3                => previous + 3
44    5         3            5    -+ -+          1->2->3->5             => previous + 5
77    8         5            8       -+ -+       1->2->3->5->8          => previous + 8
170  11         8           11          -+ -+    1->2->3->5->8->11      => previous + 11
190  13         11          13             -+    1->2->3->5->8->11->13  => breaks here
240  18         15          18
255  20         18          20

It breaks on 190 because there is no match on parent_hid = 13 where hid = 13 (row for id = 190).

It breaks because your are only looking for hierarchy starting with a root at 1.

I am not sure what your expected output should be but if you are looking for hierarchies with no parent (i.e. parent_hid not in hid [the root]), you will also get the hierarchy 15->18->20.

Replace where rt2.parent_hid=1 by WHERE NOT EXISTS  (SELECT 1 FROM rt2 WHERE hid = r.parent_hid) and starting roots will be:

id  hid hierarchy
11  2   1->2
240 18  15->18

And output will be:

id  hid hierarchy
11  2   1->2
22  3   1->2->3
44  5   1->2->3->5
77  8   1->2->3->5->8
170 11  1->2->3->5->8->11
190 13  1->2->3->5->8->11->13
240 18  15->18
255 20  15->18->20

It seems that only hierarchy_link is needed to built up the hierarchy. Therefore rt2 should be remove:

;WITH rt1 AS (
    SELECT hid, parent_hid, CAST(parent_hid as varchar(50)) + '->' + CAST(hid as varchar(50)) as [str]
    FROM hierarchy_link r
    WHERE r.parent_hid = 1
    UNION ALL
    SELECT l.hid, l.parent_hid, CAST(r.[str] as varchar(50)) + '->' + CAST(l.hid as varchar(50)) as [str]
    FROM hierarchy_link l
    INNER JOIN rt1 r ON r.hid = l.parent_hid
)
SELECT h.id, r.hid, r.[str] as hierarchy
FROM rt1 r
INNER JOIN id_hierarchy h ON h.hierarchy = r.hid;

The id from id_hierarchy is only added at the end once the hierarchy from rt1 is ready:

id  hid hierarchy
11  2   1->2
22  3   1->2->3
44  5   1->2->3->5
77  8   1->2->3->5->8
170 11  1->2->3->5->8->11
190 13  1->2->3->5->8->11->13
240 18  1->2->3->5->8->11->13->15->18
255 20  1->2->3->5->8->11->13->15->18->20


I just exited SSMS while restoring a UAT database, am I screwed?

This is baked into sp_Whoisactive as well without needing an ad-hoc script.

It's the percent_complete column, over to the right (removed some superflous columns like sql_text and waitinfo):


How to prevent 'table is mark as and last (automatic) repair failed'?

"marked as crashed" -- Longterm solution:  Switch from MyISAM to InnoDB.

"errno 28" -- Check the setting for tmpdir and consider changing it to a bigger dis partition.

"Out of memory" -- What is running?  Is this server hosting MySQL only?  Or does it have other things, such as a web server and PHP?  If hosting multiple apps, let's see my.cnf so we can advise on what settings to shrink.

References:

http://mysql.rjweb.org/doc.php/memory
http://mysql.rjweb.org/doc.php/myisam2innodb


Well,

A view increases the overhead but if the view only contains a select with a different column ordering, the overhead will not be noticeable.
You can also rebuild the table with the column order you want (a rather expensive one time operation)
Most people agrees that views should follow the same naming standard as tables (i.e. no V_, or any other, special prefix)

Having said that, software should almost never do selects or inserts, without specifying the columns.


Best structure for user-level versioning of data in mysql

It is possible to use one-database-per-user or one-table-per-user models… But these will start to become unwieldy as the number of users in your application grows… MySQL (and, really, no RDBMS) handles hundreds of thousands of tables or databases very well.

Having an user_id (or similar) column on each table that contains user data is totally standard, and the "right" way to do what you're describing.


SQL Server: filegroup for system tables only?

The isn't a performance gain to this, there is a recoverabily gain to be made.  If file corruption happens in the system tables then the database is lost.  If you keep the user data in a separate file group (or groups) then you can restore just those files keeping the rest of the database online during the restore (assuming Enterprise Edition here).

If this is why they state this, I can't say, but this would be a benefit of having multiple file groups with just the system objects in the PRIMARY filegroup.

You should however kick then in the junk for saying that AutoShrink should be enabled.


How do I iterate over 1000 records in a particular order

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT *
    FROM (
      SELECT *
      FROM yourtable
      ORDER BY submitdate ASC
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.


Exactly the same way:  CONFIGURE option_name CLEAR;
There is no 'clear all'.

https://docs.oracle.com/database/121/RCMRF/rcmsynta009.htm#RCMRF113


Does SQL manage foreign key constraints in terms of insertion?

As discussed in the comments violation of a foreign key constraint will produce an error on most legitimate server products.  However, this is only when the foreign key has been defined physically.

A logical foreign key can exist without the enforcer that on most engines is called the  constraint.  I've seen a lot of schemas that show relations on paper but on close inspection revealed that logical relationship wasn't actually being enforced by a foreign key constraint.  This is important to check for data integrity purposes as well as query performance when relationships are properly indexed as well.

When the constraint has been defined then each of the following server products will generate an error if you violate that constraint.

MySQL:

violation of FOREIGN KEY constraint "<name of constraint>" on table "<your table>".

note MySQL currently only supports Foreign Key constraints natively with InnoDB & Falcon engines

SQL Server:

statement conflicted with the FOREIGN KEY constraint "<name of contraint>". Conflict occured in database <your database>

Postgres:

insert or update on table "<your table>" violates foreign key constraint
"<constraint name>"

Oracle:

ORA-02291: integrity constraint (<schema.constraint>) violated - parent key not found

Sybase's message is probably identical to SQL Server, I know DB2 enforces it.  SQLite does as well though the error message doesn't identify the constraint.

Note these engines will also raise an error if you try to remove the parent once children relying on the key exist.

Another tip:

When you look at these errors you also pick up on a good development strategy.  Name your constraints in a way that makes it easy to track back to the tables involved since almost all of them identify the constraint name in the error.


MySQL - automatically update all references to specific table to use on delete cascade?

WARNING !!!
Before you do anything, mysqldump the entire database or tarball /var/lib/mysql
If you are concerned about case sensitivity, you need to hunt down the usernames in a special way
I tried this little experiment
use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');

I loaded the sample data
mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE rolando
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO rolando (name) VALUES
    -> ('rolando'),('Rolando'),
    -> ('ROLANDO'),('ROLANDO'),
    -> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

I ran these queries (please note subtle differences)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name    | MD5(name)                        | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 |        2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f |        1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 |        3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name    | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando |                          1 |
| Rolando |                          0 |
| ROLANDO |                          0 |
| ROLANDO |                          0 |
| rolando |                          1 |
| rolando |                          1 |
+---------+----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,(name LIKE lower(name)) like1,
    -> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name    | like1 | like2 |
+---------+-------+-------+
| rolando |     1 |     1 |
| Rolando |     1 |     0 |
| ROLANDO |     1 |     0 |
| ROLANDO |     1 |     0 |
| rolando |     1 |     1 |
| rolando |     1 |     1 |
+---------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY operator with the comparison
I do not think BINARY can be applied to ON DELETE CASCADE.
However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:
DELETE FROM logins WHERE username = BINARY LOWER(username);

or
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

If you run this now, you might nix all usernames in the other tables. Run this instead
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);

or
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

You will have to go to all tables that have a username in it and execute
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';

Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:
DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}

If the file is correct to you, run it in the mysql client like this
mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}

Give it a Try !!!


MYSQL How do I query to show data in separate columns from one field?

ORACLE, NOT MYSQL

CREATE TABLE MYTABLE ( item_id int PRIMARY KEY, item_name VARCHAR(20) );

INSERT INTO MYTABLE (item_id,item_name) VALUES (1,'Fred');

INSERT INTO MYTABLE (item_id,item_name) VALUES (2,'Arthur');

INSERT INTO MYTABLE (item_id,item_name) VALUES (3,'Jane');

select * from mytable;

**ITEM_ID ITEM_NAME**
---------- --------------------
         1 Fred
         2 Arthur
         3 Jane

3 rows selected.

select
  item_id,
  (select unique item_name
     from MYTABLE
     where item_name='Fred') as fred,
  (select unique item_name
     from MYTABLE
     where item_name='Arthur') as Arthur,
  (select unique item_name
     from MYTABLE
     where item_name='Jane') as Jane
from MYTABLE;
/

   ITEM_ID FRED                 ARTHUR               JANE
---------- -------------------- -------------------- --------------------
         1 Fred                 Arthur               Jane
         2 Fred                 Arthur               Jane
         3 Fred                 Arthur               Jane

3 rows selected.

I am only learning but this works, remember subquery must return only one row, otherwise there will be error.

One more, this should work faster:

select
 item_id,
 decode(item_name,'Fred',1,0) as Fred,
 decode(item_name,'Arthur',1,0) as arthur,
 decode(item_name,'Jane',1,0) as jane
from MYTABLE;

  ITEM_ID       FRED      ARTHUR        JANE
---------- ---------- ---------- ----------
         1          1          0          0
         2          0          1          0
         3          0          0          1

3 rows selected.

Hope this helps.


How to deny writes in all SQL Server databases (including new ones & restored ones)

If you could live with a finite time (minimum 10-seconds) between executions to catch new/restored databases, you could create a scheduled SQL Server Agent Job and put something like this in a T-SQL Job Step:-
SET NOCOUNT ON

DECLARE @command nvarchar(max) ;

SET @command =
N'SET NOCOUNT ON

DECLARE @UserName sysname ;

SELECT @UserName = USRS.[name]
FROM sys.database_principals AS USRS
INNER JOIN sys.server_principals AS LGNS ON LGNS.[sid] = USRS.[sid]
WHERE LGNS.[name] = ''LoginName'' ;

IF IS_ROLEMEMBER(''db_denydatawriter'', @UserName) = 0
   BEGIN
      EXEC sp_addrolemember ''db_denydatawriter'', @UserName ;
   END ;' ;

EXEC sp_ineachdb
   @command      = @command,
   @state_desc   = N'ONLINE',
   @is_read_only = 0 ;


Your management configuration seems fine to me.  It sounds like ndb_mgmd didn't quite die or there is a problem with the contents in the /var/lib/mysql-cluster directory on the management server.

You might also want to make sure that ndb_mgmd isn't starting automatically, like through some startup script in /etc/init.d/.

To try and resolve the issue, I'd start with these steps first.

Since you are just getting started and don't have any real data yet.

Kill any left over ndb_mgmd processes with:  killall ndb_mgmd or by rebooting if killall doesn't help.
Completely delete everything within /var/lib/mysql-cluster: rm -Rf /var/lib/mysql-cluster/*
Start the cluster process again: ndb_mgmd -f /etc/config.ini --initial
Verify that ndb_mgmd is listening with netstat

$> netstat -tlpn

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 0.0.0.0:1186                0.0.0.0:*                   LISTEN      3251/ndb_mgmd

Connect to management node

$> ndb_mgm
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.9.26  (mysql-5.1.61 ndb-7.1.22)

Update

Here is a Quick Start guide from Oracle with all nodes, management, mysql and data running on one server.

http://downloads.mysql.com/tutorials/cluster/GetMySQLClusterRunning-LINUX.pdf


Sybase's SQL Anywhere has startup parameters to control the min/max cache settings.

  -c  for the initial cache memory setting
  -ch for the maximum cache memory setting
  -ci for the minimum cache memory setting

If you set these appropriately it should constain the memory usage from the cache.

Here is a SQL Anywhere 12.0 page with those settings:
http://dcx.sybase.com/1200/en/dbusage/limiting-using-perform.html


Dividing comma separated strings into columns

Is this what you're looking for? I'm using regexp_substr

with test as
(select '1,2,3' col1, 'Ab,cd,ef' col2 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1, regexp_substr(col2, '[^,]+', 1, rownum) result2
  from test
  connect by level <= length(regexp_replace(col1, '[^,]+')) + 1;

This is the SQL Fiddle.


The Principle of Least Privilege demands two things: the runtime user (or the human user logins) should not own the objects, because you cannot stop them from modifying or dropping them (on Oracle).

The same Principle also suggests you should not need to have extensive permissions for the user who wants to update application objects.

Both combined suggests: have a system user owning the application objects and another system user with access to those objects for runtime login. Use the DBA only to set up those users.

It is a bit different on MSSQL but basically you want to avoid having a runtime user with dbo role.


IMPDP Failing with ORA-00001: unique constraint in oracle

impdp ... data_options=skip_constraint_errors

Above works only for non-deferred constraints though.

link


how to use trigger to set value based on query result

That looks like it should work. I would run one SELECT to retrieve both values

SUGGESTION #1

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    select value,value2
    INTO NEW.value,NEW.value2
    from table2 where id=NEW.id;
  END;
|

delimiter ;

SUGGESTION #2

If the value and value2 are of datatype INT, do the following

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    DECLARE v1,v2 INT;

    select value,value2 INTO v1,v2
    from table2 where id=NEW.id;

    NEW.value  = v1;
    NEW.value2 = v2;
  END;
|

delimiter ;

or do DECLARE v1,v2 VARCHAR(45); or whatever datatype is appropriate


How to grant permissions to database but restrict to certain schemas?

A combination of DENY and a GRANT works for me.

For example:

GRANT SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
DENY SELECT ON schema::[schema_1] TO [user_name]
GRANT SELECT ON schema::[schema_Safe] TO [user_name]

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;


As you noted, you can do this with tablespaces to split the data over multiple drives.  But using tablespaces makes things like restoration and migration more difficult.  Also, you have to actually move some objects to this space, or it won't accomplish anything.  You set it as the default, but that only applies to new objects, it won't automatically spill storage for existing objects to the new tablespace.  Any single object can not be split.

Is it really necessary?  Of the 63GB main drive, how much of that is used by the database itself versus other things?  Rather than using a tablespace, it would be cleaner just to move the entire database to the new drive, assuming it has enough space to hold it all on its own.  If most of that 61GB is for this database, then of course that wouldn't work.
In that case, to make things easier to manage, I'd probably drop the tablespace, and ask them to create for you a single new drive large enough to hold the entire database single-handedly, then move the whole thing to it.


Benchmarking procedures or Upgrade tests to be done after upgrading to SQL Server 2016 from SQL Server 2012

When I did SQL 2005 to 2012 I used the Distributed Replay Controller feature. You run a profiler trace with the DRC template. Then run the command on a few servers and it runs those trace files against a target server.

During the process you check whatever performance monitoring app you use and make a note of the times


MySQL Trigger Query Not Deleting As Expected

What you want is (something like):

DELIMITER $$
CREATE TRIGGER `Before_Insert_tbl1` BEFORE INSERT ON `tbl1`
FOR EACH ROW
BEGIN
  DELETE FROM tbl2
  WHERE (NEW.host = tbl2.host2);  -- note not tbl1.host - out of scope!
  -- AND date = NEW.date)         -- not sure what the date logic is here.  AFAICS
                                  -- it's superfluous. Could be NEW.date = DATE(NOW());?
                                  -- i.e. delete it if it's today's record, otherwise don't?
END $$
DELIMITER ;

Your problem was having tbl1.host inside the WHERE clause where it's out of scope. The NEW refers to the new tbl1 record - no need for a table reference.

Also, as I note, the 'date' is confusing - it  does nothing - tbl1.date (of the record being inserted) will automatically equal NEW.date. Finally, you appear to have made an error in your TRIGGER - there's no tbl2.host (should be "host2"?). I've edited it to make it easier for anyone else should my logic not be flawless :-).


mariadb datadir change location raspberry pi

Be aware that although mariadDB loads configuration details from various my.cnf files it can also load them from other files with different names.

That means that if you make a change in one of the my.cnf files, it may get overwritten by another file of a different name. To make the change stick, you need to change it in the right (last loaded) config file - or, maybe, change it in all of them.

So how do you find all the config files that might be loaded? Instead of looking for my.cnf files, try running:

grep -r datadir /etc/mysql/

This will find all the places in which datadir is mentioned. In my case, it produces this answer:

/etc/mysql/mariadb.conf.d/50-server.cnf:datadir     = /var/lib/mysql

When I edit that file (/etc/mysql/mariadb.conf.d/50-server.cnf) to change the value for datadir (in my case to /media/USBHDD2/shared/mysql ), it works, whereas changing it in my.cnf does not.

However, you then have to make sure that the hard drive is mounted before mariadDB starts up, otherwise the startup will fail. And also make sure that the ownership of the directory and files on your mounted drive is mysql:mysql - a problem if the filestructure is ntfs rather than EXT4. see my own problem and solution here


How to get the nTH row in ordered result set?

To get that every row need to know the value of the next one, but the LEAD function is not in SQLServer 2008, so an auto-join is used in his stead

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID = Row_Number() OVER (PARTITION BY EMPLID
                                 ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
)
Select a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
FROM   I a
       LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID = a.ID - 1
WHERE  a.DeviceType <> b.DeviceType
   OR  a.ID = 1
ORDER BY a.EMPLID, a.PUNCHDATE, a.PUNCHTIME

SQLFiddle demo

The Row_Number is used to simplify the JOIN condition.


Database Design: Can only sell to X customers per given timeframe (ex: 11am to noon)

First, you have to figure out how you want to define the system: is it the order date that is constrained by your timeframes, or is it the delivery date?

The design seems overly complicated to me. You could just store the relevant timestamp with the orders and error out if there are more than five in one hour.


How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

EDIT after clarification:

I'm afraid you can't deal with it the way you intend.
You have to find another way, like for example defaulting to NULL and selecting via

SELECT COALESCE(ts, CONVERT_TZ( '1970-01-01 00:00:01', '+00.00', @@session.time_zone)) AS ts, ...

Or just use datetime and store the time_zone in a separate column. It depends on your requirements. But there's no way you can make it work like you want to, not even with triggers.

Original answer:

You can't use any functions in a table definition.

What you're trying to do is unnecessary anyway. May I quote the manual:

  MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

If you are querying a database across different timezones, and need the date/time to reflect individual users settings, use timestamp. If you need consistency regardless of timezone, use datetime.


How exactly does the one-byte "char" type work in PostgreSQL?

1. chr(10)

... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there.

2. & 3. ascii() produces 128 or 192?

It starts with a mistake I made. I carelessly assumed "char" would cover the range of an unsigned 1-byte integer (0 to 255) in the referenced answer (now fixed), but it's actually the range of a signed 1-byte integer (-128 to 127) internally.

ascii() takes a text parameter, the implicit cast from "char" to text produces a multibyte-encoded character in unicode, and the function returns (per documentation on ascii()):

  ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.

So we get a lot of truncated values. 128 and 192 are byte values for the leading byte of multibyte characters.

4. The null byte

The inability to store null bytes only affects regular character types (text, char, varchar), not "char". It applies to my buggy example, because I cast to text as stepping stone. While casting between "char" and integer directly, the limitation does not apply. The manual on chr():

  The NULL (0) character is not allowed because text data types cannot
  store such bytes.

Not so for "char", where 0 is mapped to the empty string '':

SELECT ''::"char"::int  -- 0
     , 0::"char" = '';  -- t

Remember: "char" is still an "internal" type intended for simple and cheap enumeration. Not officially designed for what we are doing here, and not portable to other RDBMS. There are no guarantees by the Postgres project for this.


Why do I receive the error message "'' must appear in the GROUP BY clause or be used in an aggregate function"?

I assume you are using PostgreSQL.

I suppose -I can't reproduce your whole query-, you are getting this error because you added an aggregate to it:

MAX(sr.solution_id)

I've set up a minimal example to reproduce this error:

CREATE TABLE t(id INT, foo int, bar int);
INSERT INTO t VALUES
(1, 10, 20),
(1, 11, 21),
(1, 12, 22),
(2, 30, 40),
(2, 31, 41),
(2, 32, 42);

Using a similar syntax:

SELECT
    DISTINCT ON (id) id,
    foo,
    MAX(bar) bar
FROM
    t
ORDER BY
    id, foo;

It returns the next error:

  ERROR:  column "t.id" must appear in the GROUP BY clause or be used in
  an aggregate function LINE 2:     DISTINCT ON (id) id,

One idea to solve it is by using a sub-query to calculate the aggregated value (but this doesn't work as one would expect):

SELECT
    DISTINCT ON (id) id,
    foo,
    (SELECT
        MAX(bar)
     FROM
       t t1
     WHERE
       t1.id = t.id) max_bar
FROM
    t
ORDER BY
    id, max_bar;

id | foo | max_bar
-: | --: | ------:
 1 |  10 |      22
 2 |  30 |      42

Assuming that you want one row per id, th eone that has the maximum bar, then this is how to use DISTINCT ON:

SELECT
    DISTINCT ON (id) id,
    foo,
    bar
FROM
    t
ORDER BY
    id, bar DESC;

id | foo | max_bar
-: | --: | ------:
 1 |  12 |      22
 2 |  32 |      42

db<>fiddle here

Using DISTINCT ON and after the comments that clarified a bit the requirements, that you want a row for each vulnerability - identified by dv.nexpose_id:

  The query has a row per vulnerability, however, some vulnerabilities have more than 1 solution which causes that row to be duplicated but with a different solution ID. I just need 1 solution ID per row.

  Nexpose ID is what identifies the vulnerability.

your query should be rewritten:

WITH
   references_cve AS (
      SELECT vulnerability_id, reference
      FROM dim_vulnerability_reference
      WHERE dim_vulnerability_reference.source = 'NVD'
   )
SELECT DISTINCT ON (
      dv.nexpose_id    -- one result for each vulnearibility
   )
   da.ip_address AS IPAddress,
      -- ...
   dos.description as DeviceOperatingSystem,

   sr.solution_id,               --<-- This is changed

   davbs.vulnerability_id,
   dv.vulnerability_id

FROM fact_asset_vulnerability_instance favi
   LEFT JOIN dim_asset da USING (asset_id)

      -- ...

ORDER BY
      dv.nexpose_id,        -- same as the DISTNCT ON
      sr.solution_id DESC   -- plus the ordering we want
  ;


Delete index while query is running, is it possible?

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course.

No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index.

No, I don't think your query should take more than three days, even if you import every link that every Wikipedia site on earth ever had to offer. There is probably something less than ideal in your command or setup or hardware or how you read / write the data.
For starters, an operation of that magnitude would rather use COPY:

Increased performance from single statement, multiple row inserts?


I found the information in this post about SQL Server function to check dynamic SQL syntax quite helpful.

The post author created a UDF that accepts a dynamic SQL statement and the UDF uses sys.dm_exec_describe_first_result_set (available in SQL Server 2012) to validate the SQL.

From that post:

-- =============================================
-- Author:  Eli Leiba
-- Create date: 06-2017
-- Description: Check Dynamic SQL Statement Syntax
-- =============================================
CREATE FUNCTION dbo.CheckDynaSQL (@p1 VARCHAR (2000))
RETURNS VARCHAR (1000)
AS
BEGIN
   DECLARE @Result VARCHAR (1000)

   IF EXISTS (
         SELECT 1
         FROM sys.dm_exec_describe_first_result_set (@p1, NULL, 0)
         WHERE [error_message] IS NOT NULL
            AND [error_number] IS NOT NULL
            AND [error_severity] IS NOT NULL
            AND [error_state] IS NOT NULL
            AND [error_type] IS NOT NULL
            AND [error_type_desc] IS NOT NULL
         )
   BEGIN
      SELECT @Result = [error_message]
      FROM sys.dm_exec_describe_first_result_set(@p1, NULL, 0)
      WHERE column_ordinal = 0
   END
   ELSE
   BEGIN
      SET @Result = 'OK'
   END

   RETURN (@Result)
END
GO

Declare @cmd varchar(1000)
set @cmd = 'select * from sys.databases'
Select dbo.CheckDynaSQL (@cmd)
---
OK

Declare @cmd varchar(1000)
set @cmd = 'select * from sys.databas'
Select dbo.CheckDynaSQL (@cmd)
---
Invalid object name 'sys.databas'.


How to check the timezone for a given datetime?

This is neither technically nor even conceptually possible, due to the nature of what timezones are.

It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since:

several timezones can share the same offset
timezones can come and go and can even shift their offset
different regions can change their timezones

Timezones are determined by governments. For example, in the U.S.A., there are primarily 4 timezones for the contiguous 48 states. But those same offsets are also used by other regions that probably have different laws regarding when Daylight Savings starts and ends, or if they even have a Daylight Savings period. And in fact, there are two extra timezones for the contiguous 48 states to handle differences for 2 states: one for Indiana and one for Arizona.

Daylight Savings start and end times change over time. The Indiana and Arizona links above discuss this for those two states. In the United States, the DST start and end dates changed in 2007, but this didn't affect other regions using those same offsets. And the DST offset isn't always 1 hour (mentioned in the prior link).

So, with:

a DATETIME value alone you have no basis for determining either the Offset or the Timezone (even if you had a Timezone database)
a DATETIME value and an OFFSET--whether stored separately or combined in a DATETIMEOFFSET datatype--you have the OFFSET (obviously) but cannot determine the exact Timezone, though it could be narrowed down to a few, but only if you had a table of which Timezones participated in that Offset at that particular moment in time (see below for sources of such data). With only the Offset, the DATETIME value can be converted to UTC, but not to another Offset without first having a Timezone database and knowing which specific Timezone you are converting it to, which will indicate the Offset for that particular point in time. (By saying it cannot be converted to another Offset, I mean in any meaningful way; clearly the Offset value can be adjusted, but that doesn't help much if you don't know what to adjust it to)
a DATETIME value and a Timezone (name) then you can determine the Offset by looking up what the Offset was at that point in time (see below for sources of such data)

Unfortunately this Timezone data is not built into SQL Server. But you can get it from the following sources (and possibly others):

Internet Assigned Numbers Authority (IANA)
TimeZoneDB


SSIS doesn't support nullable types for it's built in variables.  So - as you've experienced - a null integer in SQL will become zero when assigned as an SSIS variable.  Those are the breaks, but there ARE workarounds.

In order to keep our source table NULLs, we will need to skip over the whole  "assigning them to SSIS variables" completely.  Have a look at the OLE DB Command.  It's similar to the Execute SQL command, except it operates on the dataflow level.  It operates one SQL Command per row running through it. The advantage of this is that you never have to map a SQL result set to SSIS variables.  Working inside a dataflow is SQL out and SQL in.  We can avoid the whole "non nullable" headache altogether by passing a SELECT TOP 1 * FROM MyConfigTable into an OLE DB Command.  Inside the OLE DB Command compoenent  you can assign the ResultSet columns to SP parameters.

I know it's a change, but I do think that moving away from two Execute SQL Tasks in succession and into a Dataflow containing an OLE DB Command component is what you want.  Anything else would involve a script task.  Good luck!


Optimization: Increase Innodb_pool_size useful?

It's good you don't see a lot of reads, ideally it should be zero if available memory permits.

When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk.

If you already get no reads further buffer pool increase won't increase performance.

The storage drop - I don't know RDS is a black box, who know what's going on there.


CASE expression returns wrong value when using CEILING

To reproduce the problem:

SELECT *, (CASE
    WHEN IsGun=1 THEN CEILING(Price1Avg)
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;

What happens here is that CEILING(PQ.Price1Avg) produces a numeric(38, 0).

According to the documentation, the output type of CEILING() is of the same base datatype as the input, although the scale (the number of decimals) may change, which is what happens here.

The AVG() function, in my tests, returns numeric(38, 6).
The CEILING() function on that column, however, outputs numeric(38, 0):

To verify:

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))

As a workaround, you could explicitly convert the output of the CEILING() function, which should give you the correct results:

SELECT *, (CASE
    WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;


How to determine the name of a process that caused a trigger to fire

SET CONTEXT_INFO exists for SQL Server 2000 too.

However, you have to query sysprocesses.context_info to read it back. CONTEXT_INFO() was new for SQL Server 2005.


SQL Server: sp_describe_first_result_set with more than one select in a stored procedure

That behavior is expected and is described on the sp_describe_first_result_set doc under the Remarks section:

For each control flow path, the first statement (if any) that returns
a result set is determined by sp_describe_first_result_set.
When multiple possible first statements are found in a batch, their
results can differ in number of columns, column name, nullability, and
data type. How these differences are handled is described in more
detail here:
[...]

If the data type differs, an error will be thrown and no result is    returned except for the following cases:

varchar(a) to varchar(a') where a' > a.

varchar(a) to varchar(max)

nvarchar(a) to nvarchar(a') where a' > a.

nvarchar(a) to nvarchar(max)

varbinary(a) to varbinary(a') where a' > a.

varbinary(a) to varbinary(max)

The doc has an example where an Error because the data types differ happens, like in your case.


MySQL 5.5 Replication to Galera/Percona XtraDB Cluster

Yes, it is very possible.

As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes.

Make sure all nodes in the PXC have unique server_ids.
Make sure the Master you are replicating from has a unique server_id from the PXC nodes
Make sure the master and every PXC node is configured with the following

binlog_format=ROW
log-slave-updates

Make sure the master and every PXC node is using binlog_format=ROW

Pick one node and set it up as you would a DB Server designated as a Slave via CHANGE MASTER TO.

If you are going to set up any replication filters, restrict it to just the PXC node that is your Slave. Personally, I would stay away from using replication filters altogether in this setup since one of Percona's Galera Instructors does not feel good about it.

Please be aware that data takes time to be on all nodes at the same instant of time (See Investigating MySQL Replication Latency in Percona XtraDB Cluster)

By the way, you can use any PXC as a Master to StandAlone Slaves as well

Jun 07, 2013 : Percona XtraDB Cluster and MySQL Replication
Jan 29, 2014 : Native replication from Percona XtraDB Cluster to a standard stand-alone slave
Nov 24, 2014 : What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

HEADS UP #1

Don't be surprised by replication lag in the PXC node acting as the MySQL Slave. After all, three nodes must write data and become eventually consistent before all committing changes together. See Follow these basics when migrating to Percona XtraDB Cluster for MySQL for more info.

HEADS UP #2

Since you are using MySQL 5.5, do not use PXC 5.6. Use PXC 5.5. Why ? Row-based binlogging in MySQL 5.5 can sometimes not be properly interpreted by a MySQL 5.6 mysqld process. This can break replication from MySQL 5.5 to MySQL 5.6. I wrote a post about this two months ago. The work around was to switch to binlog_format=STATEMENT, but you cannot do that for Percona XtraDB Cluster as it requires binlog_format=ROW.


Convert query in words to relational algebra

I think the key point here is the fact that relational algebra eliminates duplicates from its result. So if you use a Project operator to get only iid and price, it will give you the list of bids excluding the duplicate bids. If you subtract this list from the whole bids you will get the bids those are duplicates..

I am not sure though.. Would you please comment on how you feel about this approach?


Why are queries getting dropped from query store?

After setting up an extended events session looking at query store cleanup I saw my query_id in an event: query_store_size_retention_query_deleted

So my queries are getting dropped to avoid reacing size limit.
flush_interval_settings was set to 1 min, which probably contributed to this.


To have PostgreSQL-like Partial Index in MySQL 5.5

Neither MySQL nor the siblings (MariaDB, Drizzle, etc) have implemented partial indexes.

What you can do, with this restriction in mind:

a) make a simple (not partial) index on (is_active, measurement_id). It will be used in queries where the partial index would. Of course if the is_active column is 3% True and 97% false, this index will be much bigger (than a partial index). But still smaller than the table and useful for these queries.
Another limitation is the index cannot be UNIQUE with this solution so the constraint is not enforced. If the index is created with UNIQUE, the uniqueness will be enforced for rows with is_active = FALSE as well. I assume you don't want that:

CREATE INDEX dir_events
    ON events (is_active, measurement_id)
    USING btree ;

b1) (the simple variation of b):  add another table in your design, with only the primary key columns of events and a foreign key to events. This table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures). Queries with is_active = TRUE would be changed to join to that table (instead of the WHERE condition.)
The UNIQUE is not enforced either with this solution but the queries would only do a simple join (to a much smaller index) and should be quite efficient:

CREATE TABLE events_active
( event_id INT NOT NULL,         -- assuming an INT primary key on events
  PRIMARY KEY (event_id),
  FOREIGN KEY (event_id)
    REFERENCES events (event_id)
) ;

INSERT INTO events_active
  (event_id)
SELECT event_id
FROM events
WHERE is_active = TRUE ;

b2) a more complex solution: add another table in your design, with only the primary key columns of the table and measurement_id. As in previous suggestion, this table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures, too). Then use this table only instead for queries that have WHERE is_active = TRUE and need only the measurement_id column. If more columns are needed from events, you'll have to join, as before.
The UNIQUE constraint can be enforced with this solution. The duplication of measurement_id column can also be secured to be consistent (with an extra unique constraint on events and a composite foreign key):

ALTER TABLE events
  ADD UNIQUE (event_id, measurement_id) ;

CREATE TABLE events_active
( event_id INT NOT NULL,
  measurement_id INT NOT NULL.
  PRIMARY KEY (event_id, measurement_id),
  UNIQUE (measurement_id),
  FOREIGN KEY (event_id, measurement_id)
    REFERENCES events (event_id, measurement_id)
) ;

INSERT INTO events_active
  (event_id, measurement_id)
SELECT event_id, measurement_id
FROM events
WHERE is_active = TRUE ;

c) maybe the simplest of all: use PostgreSQL. I'm sure there are packages for your Linux distribution. They may not be the latest version of Postgres but partial indexes were added in 7.0 (or earlier?) so you shouldn't have a problem. Plus, I'm confident that you could install the latest version in almost any Linux distribution - even with a little hassle. You only need to install it once.


Does any DBMS have a collation that is both case-sensitive and accent-insensitive?

TL;DR

There is no such thing as a "vendor-agnostic" view of Collations, nor even "version-agnostic", since their implementations -- including which aspects can be made insensitive and their naming conventions -- are vendor-specific and change over time.

Here is a summary of what I have found, and the details are in the longer section below the line:

RDBMS        Naming-             Combinations    Case-Sensitive and
             convention          of options?     Accent-Insensitive support?
-------      ------------        -------------   -----
SQL Server   _CS, _AI, etc       Yes             Latin1_General_100_CS_AI

DB2          _E{x}, _S{y}, etc   Yes             CLDR181_EO_S1

PostgreSQL   locale: en_US       N/A             unaccent(), not via Collation

MySQL        _cs, maybe _ai      No              No: _cs implies _as & _ci implies _ai
                                                 Yes? Create your own Collation :-)

Oracle       only _CI & _AI      No              No: _AI always implies _CI

SAP ASE      arbitrary: turdict  N/A             No: "AI" always implies "CI"

Informix     locale.codepage     N/A             No: no "AI" via Collations

As you can see in the chart, two of the seven RDBMSs do natively support "Case-sensitive and Accent-insensitive" operations via Collations, though they have different naming conventions (and several other functional differences).

One RDBMS -- PostgreSQL -- doesn't natively support this combination, but you can still achieve it by stripping off the accents with the unaccent() add-on function.

The last four RDBMSs, two of which have a similar naming convention for the options, neither natively support this combination nor does there appear to be a means of accomplishing this without writing your own function to remove the accents / diacritical marks. MySQL allows for creating your own Collations, but that requires that you then add it to source control and incorporate it into your testing & deployment process so that it can be applied to all servers in all environments (but still a very cool and flexible option). SAP ASE mentions that SAP can supply additional Unicode sort orders, but no mention of what they might be willing to supply.

With regards to:

  Is there a good reason for this or is mine merely a rare use case?

I can say that in doing the research for this answer I came across a lot of instances of people wanting case-insensitive and accent-sensitive for MySQL, but few, if any, asking for your desired combination.

  I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one.
  ...
  this question is vendor/version agnostic

You were unsuccessful in your search because it doesn't really make sense to look for an RDBMS based on a Collation specification. That's just not how Collations work. And while you are wanting to approach this as vendor-agnostic, reality is that Collations -- at least the part that we interact with -- are very much vendor-specific, and don't always fit into the scheme that you were searching on.

String comparison and sorting is highly complex, and there are different ways of performing these rules. One method is to have mappings that take into account one or more rules. Hence the four combinations of Sensitive and Insensitive for Case and Accents would equate to four separate mappings. For example, you saw this on that MSDN page for SQL Server Collation Name. If you scroll down, you will see that the left column of the chart is the Sort Order ID. Each Collation has a different ID: SQL_Latin1_General_Cp1_CI_AS = 52 while SQL_Latin1_General_Cp1_CS_AS = 51, even though the only difference is in the case-sensitivity.

Or, it can be rule-based, such as what Unicode offers through the Unicode Collation Algorithm (UCA). In this approach, every character is given, by default, one or more weights. Then, each culture / locale has the option to override any of those weights, or remove rules, or add rules. The algorithm takes into account any locale-specific rules, and then potentially manipulates those weights based on any options chosen (sensitivity, which case comes first when doing case-sensitive sorts, etc). This is one reason why doing Unicode sorting is a bit slower than non-Unicode sorting.

To get a sense of how many options there really are (i.e. the actual complexity), check out this demo from the ICU (International Components for Unicode) project:

ICU Collation Demo

There are 8 separate options to specify, and some of them get represented in multiple elements of the Collation name specification that you are thinking of (e.g. CS, CI, AS, AI, etc). Given how many variations there are, using the mapping file approach where each combination has its own ID would result in many thousands of files. Many of those files would need to get updated whenever there are changes in those particular languages, or when bugs are found. This is probably why there are only 75 of those type of Collations in SQL Server 2012 (i.e. those with names starting with SQL_). Hence no combination for _CS_AI.

And the reason why you couldn't find that combination for the UCA-based Collations? Well, there are 3810 Collations in SQL Server 2012 that do not begin with SQL_, so 3885 Collations total. That list seems to be too long to be enumerated fully on a web page. But this doesn't fully explain why you couldn't find this combination for other vendors.

Beyond what has already been mentioned (i.e. too many combinations to implement, and too many implementations to list), you still need to contend with vendor-specific implementations. Meaning: not all vendors allow for tailoring all of those options, and there is no standard naming convention for Collations in the first place. Also, not all vendors view the sorting options as being a part of the Collation: PostgreSQL Collations are default ordering for the chosen locale, and you need to use ILIKE to get a case-insensitive comparison. See below for vendor-specific info.

SQL Server (Microsoft)

The distinction between what you are seeing on those two MSDN documentation pages and the query provided by @MartinSmith in a comment on the question (slightly revised below):

SELECT *
FROM   sys.fn_helpcollations()
WHERE  [name] LIKE '%[_]CS[_]AI%';

is that those two MSDN pages are referring specifically to the very deprecated SQL Server Collations, whereas the collations that show up as a result of that query (888 of them as of SQL Server 2012, SP3) are Windows Collations.

Starting in SQL Server 2000, the older SQL Server Collations (created prior to SQL Server being able to tap into the Windows Collations) are deprecated and are not being updated with new rules or functionality. For example, starting in SQL Server 2012, a set of Collations was added that support the proper handling of the built-in functions for Supplementary Characters (i.e. the remaining UTF-16 characters beyond the "base" 65,536 characters initially defined in UCS-2). These newer Collations end in _SC (as in Supplementary Characters).

It is best to not use the SQL Server Collations -- those with names starting with SQL_. Hence you do have access to plenty of Collations that support the combination of options that you are looking for (i.e. Case-Sensitive and Accent-Insensitive). Whenever available, it is also best to use the one end in _SC as long as it has all of the other options you are wanting.

While SQL Server does use the _CS_AI naming convention, there is no list of all 3810 (as of SQL Server 2012) Windows Collations. There is just the Windows Collation Name page that lists all of the locales and versions, and how the naming convention works, but that's it.

SQL Server also supports toggling both Width and Kana sensitivity.

MySQL (bought by Oracle)

The MySQL version 5.7, documentation states that it does support the _ai, _as, _ci, and _cs suffixes (and _bin for completeness), but also states:

  For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. That is, if a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as.

  For example, latin1_general_ci is case insensitive (and accent insensitive, implicitly), latin1_general_cs is case sensitive (and accent sensitive, implicitly)

This certainly implies that it is possible to have a latin1_general_cs_ai Collation. However, the MySQL 5.5.50 server that I have access to does not have any collations with more than one suffix, and the only suffixes I see are: _cs, _ci, and _bin across 198 total Collations. I used the SHOW COLLATION command to list them.

So, while it sounds like MySQL uses a similar naming convention (at least as far as those two options go), I cannot find a Collation matching what you are looking for. However, it might be possible to strip off the accents (and other diacritical marks) and use a _cs collation to get what you want (similar to how you would do it in PostgreSQL -- see below). But I am not sure of this option and do not have time at the moment to research further.

OR, you might could create your very own Collation to do exactly what you want. Unlike the other RDBMSs, MySQL appears to make it rather simple to add your own Collations, in which case you are in full control over the weighting of each character. Please see Adding a Simple Collation to an 8-Bit Character Set and Adding a UCA Collation to a Unicode Character Set for more details.

For more info on how MySQL handles different types of Collations, please see their Collation Implementation Types page.

PostgreSQL

Collations in PostgreSQL seem to be far less flexible. You specify only the culture / locale: en_US, de_DE, etc. Please see their documentation page for Collation Support for details. Hence, by default you get the culture-specific overrides, but the Collations are otherwise everything-sensitive (which, by the way, is not the same as a "binary" collation).

You can use ILIKE (section 9.7.1) to get case insensitivity, but they do not have a similar operator for accent sensitivity. However, I found that they do have an unaccent function that can be used to strip off accents and other diacritical marks. Please note that this function is an Additional Supplied Module and hence is not necessarily present in any particular PostgreSQL server to use. That most recently linked documentation states:

  When building from the source distribution, these components are not built automatically, unless you build the "world" target
  ...
  If you are using a pre-packaged version of PostgreSQL, these modules are typically made available as a separate subpackage, such as postgresql-contrib.

Please see that documentation for instructions on how to get that function if you don't have it and want it.

More information can also be found in the following Stack Overflow answer:

Does PostgreSQL support “accent insensitive” collations?

DB2 (IBM)

Similar to Microsoft SQL Server, DB2 has two types of Collations:

"SYSTEM" Collations, which are specified using the following format: SYSTEM_{codepage}_[optional-territory]. These are not very flexible, and do not appear to support tailoring sensitivity to case, accents, or anything. You can find the list of supported Collations here: Supported territory codes and code pages
Unicode Collation Algorithm (UCA)-based Collations. These do support quite a bit of tailoring. Please see their Unicode Collation Algorithm based collations page for details about how to configure the behavior, the naming convention, and the list of valid locales. Please note that in Table 1, the example in the third row ("Case Level") starts with:

  Setting the Case Level attribute to on and the Strength attribute to primary level will ignore accent but not case.

That is exactly what you were looking for. But, the syntax for that is:
CLDR181_EO_S1. And this is why your searching did not find anything related to DB2.

Oracle

Oracle 10g added support for doing accent insensitive comparisons and sorting. However:

they only have the options to denote "insensitive" operations: _CI and _AI
you can only specify one of those options at a time
the case-insensitive option -- _CI -- is still accent sensitive
the accent-insensitive option -- _AI -- "is always case-insensitive as well." (quoted from their documentation which is linked below)

Please see their Linguistic Sorting and String Searching documentation page for more details and examples.

SAP ASE (formerly Sybase ASE, a.k.a. Sybase)

ASE supports one or more of the following combinations of sensitivities per each locale / character set:

case-sensitive, accent-sensitive
case-insensitive, accent-sensitive
case-insensitive, accent-sensitive, order with preference
case-insensitive, accent-insensitive

You can see the relationship between locale, character set, and available sort orders on their Selecting the Default Sort Order page. And you can see the full list of Collations on their Collation Names and IDs page.

Their Collation naming convention is arbitrary in that they are all 4 - 8 characters and try to capture the locale name or code page and and some sense of the sorting. For example:

altnoacc == "CP 850 Alternative – no accent"
rusdict == "Russian dictionary ordering"
dynix == "Chinese phonetic ordering"

There is a note on their Selecting the Default Unicode Sort Order page stating:

  You can add sort orders using external files in the $/collate/Unicode directory. The names and collation IDs are stored in syscharsets. The names of external Unicode sort orders do not have to be in syscharsets before you can set the default Unicode sort order.
  ...
  External Unicode sort orders are provided by SAP. Do not attempt to create external Unicode sort orders.

It is unclear whether or not SAP would supply an external sort order to allow for Case-Sensitive and Accent-Insensitive. Maybe someday I will email them and ask if one could be requested.

In order to get the desired combination of sensitivities, you should be able to create a scalar user-defined function to strip off accents and other diacritical marks.

Informix (bought by IBM)

Informix appears to mostly just support the default sorting and comparison behavior of a Collation. Hence Collations are just the locale and character set. Case-sensitivity is handled at the database-level, and by default they are case-sensitive. You can set a database (not a table, or a column, or a query, or even a predicate) to be case-insensitive by specifying NLSCASE INSENSITIVE in the CREATE DATABASE statement.

While the database Collation -- locale and character set -- can be overridden per client connection, there does not seem to be a way to override the case-sensitivity setting. AND, the NLSCASE option has "NLS" in the name for a reason: it only affects NCHAR and NVARCHAR data; CHAR and VARCHAR are always case-sensitive.

Accent-sensitivity is not addressed, nor is there a built-in function to strip of the accents / diacritical marks.

The Informix Collation naming convention is:

<lang>_<country>.<code set>

where:

<lang> = a 2-letter or 3-letter language code
<country> = a 2-letter country or region code
<code set> = the code page specified in one of the 3 following equivalent ways:

name: 8859-1
decimal value of IBM CCSID number: 819
hexadecimal value of IBM CCSID number: 0333

Hence, the following three locale specifications all refer to the exact same locale:

fr_fr.8859-1
fr_fr.819
fr_fr.0333

For more information, please see:

User Guide for IBM Informix International Language Supplement
The IBM Informix registry file (Windows)


1 out of 3 Transactions in DB2 fail with SQL 998, State 58005, error code 8, sub code 2

Apparently, this was the switch module.
The RM OPEN STRING definition included: rmopenstring=db=mydatabase,uid=uuuuuu,pwd=pppp,,axlib=somelib
Once the value for axlib was supplemented with an extention, .so, the error has gone away.


How do I add extra columns to crosstab()?

What you want is something like this,

SELECT *
FROM crosstab(
        $$
                SELECT sessionid, studentid, activity, created
                FROM activitylog
                WHERE activity IN ('logged in', 'logged out')
                ORDER BY 1, 2
        $$,
        $$
                VALUES ('logged in'), ('logged out')
        $$
) AS sessioninfo (
        sessionid text,
        studentid int,
        loggedin timestamp,
        loggedout timestamp
)
CROSS JOIN LATERAL age(loggedout, loggedin);

Output:

 sessionid | studentid |          loggedin          |         loggedout          |   age
-----------+-----------+----------------------------+----------------------------+----------
 abc       |         1 | 2017-08-12 02:39:05.753088 | 2017-08-12 02:59:05.753088 | 00:20:00
 def       |         1 | 2017-08-12 03:09:05.753088 | 2017-08-12 03:29:05.753088 | 00:20:00
 ghi       |         2 | 2017-08-12 03:39:05.753088 | 2017-08-12 03:59:05.753088 | 00:20:00
(3 rows)

  What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.

It specifies the categories. It can also be a query but here there is no need for that and being explicit was even more terse. You're interested in two categories those are loggedin, and loggedout. Age being a function of those two categories is calculated with CROSS JOIN LATERAL.

VALUES itself is a SQL expression. We could have written SELECT * FROM VALUES () AS t(category) but that's just more work.

  How do I add studentid to result?

Just like in my demonstration.

  How do I calculate interval between two columns that are result of crosstab()?

We used age. However one caveat about this we should hit right now..

Your schema is specified as timestamp without time zone. That's certainly a mistake. When that crosses DST, if ever, you're going to be in a world of pain. It'll show the time elapsed in the rollover.


How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff.

CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix!
  @scope VARCHAR(5) -- why an int?
AS
BEGIN
  SET NOCOUNT ON; -- always use this!

  DECLARE @d DATETIME, @cutoff DATETIME;
  SET @d = DATEDIFF(DAY, 0, GETDATE());

  SET @cutoff = DATEADD(DAY, 1-CASE UPPER (@scope)
    WHEN 'MONTH' THEN DAY(@d)
    WHEN 'WEEK'  THEN DATEPART(WEEKDAY, @d)
    WHEN 'YEAR'  THEN DATEPART(DAYOFYEAR, @d)
    WHEN 'DAY'   THEN 1 END, @d);

  SELECT columns -- never use SELECT * in production code!
    FROM Honor.Scores
    WHERE ScoredAt >= @cutoff; -- now you have a chance to use an index
END
GO

Some further reading you may find useful:

Bad habits to kick : avoiding the schema prefix
My stored procedure "best practices" checklist
Bad habits to kick : mis-handling date / range queries
Bad habits to kick : using SELECT * / omitting the column list


SQL query to update value based on email address in different table joined by id

I'm not sure if this solution covers your desired result:

CREATE TABLE table1 (id int, bla1 varchar(10), bla2 varchar(10), ucr varchar(10));
INSERT INTO table1 VALUES (10001, 'ABC', 33, 'VALUE1');
INSERT INTO table1 VALUES (10002, 'DEF', 44, 'VALUE2');
CREATE TABLE table2 (id int, bla1 varchar(10), bla2 varchar(10), email varchar(100));
INSERT INTO table2 VALUES (10001, 'TYU', 33, '1@1.com');
INSERT INTO table2 VALUES (10002, 'FGT', 44, '2@2.com');
CREATE TABLE dummy (ucr int, name varchar(100), email varchar(100));
INSERT INTO dummy VALUES (10001, 'ABC', '1@1.com');
INSERT INTO dummy VALUES (10002, 'DEF', '2@2.com');

UPDATE table1
JOIN   table2
ON     table1.id = table2.id
JOIN   dummy
ON     dummy.email = table2.email
SET    table1.ucr = dummy.ucr;

SELECT *
FROM   table1;

   id | bla1 | bla2 | ucr
----: | :--- | :--- | :----
10001 | ABC  | 33   | 10001
10002 | DEF  | 44   | 10002

dbfiddle here


What is the best way to keep track of all changes implemented on a database?

Do you have budget for a third party monitoring tool such as SQL Sentry?

This will monitor your SQL Server instance for you, far easier than attempting to create your own solution. You can also create additional Advisory Conditions to alert you for any changes you choose.


xb_stream_read_chunk(): wrong chunk magic at offset 0x0

Would have preferred to answer using a comment, but I haven't enough points yet.
Disclosure: I work for Percona. I have put your question to our tech team, and they are not sure that you have created a xstream archive.

There are examples here

For xstream the backup would be something like this:

innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbstream

and then the extract from backup.xtream would be something like this:

xbstream -x <  backup.xbstream -C /root/backup/

If you need more discussion, that might be easier via the free Percona forum .


Have you tried executing sqlpackage.exe with /p:BlockOnPossibleDataLoss=false in the command line?

I just used SQLPackage.exe to create a .dacpac file of my test database, then I pubished it with the /p:BlockOnPossibleDataLoss=false option, and it overwrote my database.


how service sid operate under the covers?

Service SIDs aren't a SQL Server thing. It's a technic inside windows to isolate different services from another. In the basic example where sql server is using NetworkService as account, many other services could have access to the SQL Server or its files.

Service SIDs and domain accounts have another importang aspect: if you change the domain account, all ACL secured objects are still valid and don't have to be updated.

See also http://support.microsoft.com/kb/2620201


Diffrence between relational and unrelational database

A relational database (the concept) is a data structure that allows you to link information from different 'tables', or different types of data buckets. A data bucket must contain what is called a key or index (that allows to uniquely identify any atomic chunk of data within the bucket). Other data buckets may refer to that key so as to create a link between their data atoms and the atom pointed to by the key.

A non-relational database just stores data without explicit and structured mechanisms to link data from different buckets to one another.

Check this link to learn more about MongoDb Link 1

Also check this link for more details  Link 2

When should you use MySQL?

If your data structure fits nicely into tables and rows, MySQL will offer you robust and easy interaction with your data. If it's performance that is your concern, there is a good chance you don't really need MongoDB. Most likely, you just need to index your data properly. If you require SQL or transactions, you'll have to stick with MySQL.

When should you use MongoDB?

If your data seems complex to model in a relational database system, or if you find yourself de-normalizing your database schema or coding around performance issues you should consider using MongoDB. If you find yourself trying to store serialized arrays or JSON objects, that's a good sign that you are better off MongoDB. If you can't pre-define your schema or you want to store records in the same collection that have different fields, that's another good reason.


For every insert statement I see sp_executesql in SQL Server Profiler

I wouldn't worry too much.

The idea is to parameterise statements for re-use. That is, avoid compiling

Consider you have 3 different clients run 3 separate statements:

SELECT col1, col2 FROM SomeTable WHERE col3 = 1

SELECT col1, col2 FROM SomeTable WHERE col3 = 15

SELECT col1, col2 FROM SomeTable WHERE col3 = 42

Each of these has different text therefore will be compiled to a different plan. That is, 3 compiles and 3 plans in cached.

Now, these 3

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '1'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '15'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '42'

The re-usable plan here is for the statement SELECT col1, col2 FROM SomeTable WHERE col3 = @p0 so you have one plan is cache.

In busy systems, this compile time matters. And most queries are more complex then this of course: you can often see this for 1st run vs subsequent run times (see Testing Query Speed)

Also in complex and busy systems then plan cache size may be limited or takes RAM away from data cache (buffer pool) which can hinder things too. The extreme could be a 10000 plans or one for a busy web app. (The effect depends on 32 vs 64 bit subtleties)

Some ORMs and drivers allow you to switch this off, usually "prepared statements" or some option: but be warned it can slow things down. You said "this seems to be a big hit" which implies you are concerned about load...


<> is not defined in relational calculus (or anywhere in math). It however is often used in programming languages (like SQL) to express "not equal". The correct sign would be the equal sign with a vertical line through its center. (= and | on top of each other).


When you add a clustered index to an existing table, SQL Server needs space to build the new index structure, in essence the entire table.

If space is an issue, you could create a new table with the clustered index, then migrate the data in chunks, drop the original table and rename the new one. It goes without saying that moving 1.4 TB if data like this is really only realistic if you're in SIMPLE recovery model, since you'd fill the log with that data otherwise.

You could probably use partition switching to avoid renaming, as renames carry some risks. That would involve truncating the original table, adding the clustered index to it, and finally using ALTER TABLE ... SWITCH.

Note that this operation will interrupt users, and you'll also have to be mindful of foreign key constraints and other schemabound objects. However, creating a clustered index on a TB+ table will also block the table for a long time, so it's your call.

I would probably just ask my SAN admin for more disk space if that's an option.


You ran sudo mkdir -p /data/db as not yourself.  Now that directory is owned by root.

You need to chown the directory back to yourself before starting mongod otherwise mongod cannot access the directory write the files into it that are needed.

Please also find a 64-bit platform to run mongo - it's not really a very good idea to run it on 32-bit system unless this is just for playing around with.


The question of when does forwarded records hurt a particular query's performance is too broad to answer, and will vary depending on what the query is actually doing.
Nowhere in the DBA.StackExchange post you linked recommends using a Heap table, rather it shows how to make use of an indexed table by forcing alternative execution plans with proper query hinting to solve the performance problem, when applicable. If you were previously experiencing performance issues that matched the question of that post, I recommend taking up Paul White's advice, but in either case the recommendation would be to index your table, especially if you're joining to it frequently.
Alternatively, Tara Kizer has a great article on How To Fix Forwarded Records which essentially ends with the suggestions of either a temporary fix by rebuilding the table, or a permanent fix by adding a clustered index to it.
And if you're still deadset on keeping it as a Heap then to directly answer your question of "when" to rebuild the table, it generally is recommended to do so based on the Heap fragmentation. That's a more easily measurable number to use to determine when's the right time to rebuild your table. Some people choose to rebuild after 20% fragmentation, others choose 50%, some wait until the table is mostly fragmented, it just depends on your situation and the particular queries you're running against the Heap table. But you can gather more information about Heap fragmentation and rebuilding the table in SQL Server Heaps, and Their Fragmentation.


You can start the SQL Server service from SSMS Registered Servers list (View-->Registered Servers). You might need to first register the desired server (local or remote).

Right-click on the registered name and select Service control-->Start.


Get Multiple Records Within Date Range Between Two Dates

You can use the  overlaps operator:

select *
from the_table
where (startdate, enddate) overlaps (date '2019-01-01', date '2019-01-30');

The overlaps operator works as the name suggest. It would e.g. include a start/end like 2019-01-20/2019-02-05 (because that range "overlaps" with the one specified)

If your input needs to be contained completely in the range defined by startdate/enddate, a query using a date range and the "contains" operator would be better.

select *
from the_table
where daterange(startdate, enddate, '()') <@ daterange(date '2019-01-01', date '2019-01-30', '()');

Or you can use a simple lower than bigger than/lower than condition:

select *
from the_table
where startdate >= date '2019-01-01'
  and enddate <= date '2019-01-30';

Online example (with more data): https://rextester.com/ALJSJE59375


Can I migrate 2 tables (with one table foreign key referencing the other) to in-memory OLTP

Seems like I need to do this to both tables at the same time

Correct.  If you try to recreate the FK after migrating one table you will get:

Msg 10778, Level 16, State 0, Line 20
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.

  is this possible without recreating both tables from scratch?

Migrating to in-memory tables always requires recreating the tables from scratch.  SSMS will rename your old tables, create the new in-memory tables, load them from your old tables, and drop your old tables.


I was able to achieve a sub-optimal solution with the help of Craig and Alexandros in the above comments.

Identify which polygons are children, and flag them as such:

UPDATE zones a SET parent = b.id FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;
Perform two queries in TileMill for both parental, and children geometries

Parents:    (SELECT * from zones WHERE parent IS NULL)
Children:(SELECT * from zones WHERE parent IS NOT NULL)
Apply a comp-op to the parental layer only and ensure that the children geometry layer is "above" the parental one in the layers list so that it is draw on top.

Unfortunately, this is still not perfect. Children geometries still cannot have an opacity value, otherwise the parent color will blend, and cause it to take on another color.


With MyISAM, you can get an AUTO_INCREMENT that starts over with each new value.

CREATE TABLE foo (
    C INT UNSIGNED NOT NULL,
    N INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(C,N)
  ) ENGINE=MyISAM;
INSERT INTO foo (C)
    SELECT C FROM your_table;

Then...

SELECT 100*C + N FROM foo

gives you the desired numbers.  (If you need U and R, add them to foo, etc.)

If this is a 1-time task, then give that a try.  If you need the numbering to be on-going, then it gets messier.


Unable to send WHERE parameters to subquery

I'm not able to change the structure of this query. The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).

PostGIS supports converting results to a different format. In your case, you want,

ST_AsGeoJSON

Moreover, it supports a ton of other Geometry Exports (current as of 2.5) too,

ST_AsBinary — Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
ST_AsEncodedPolyline — Returns an Encoded Polyline from a LineString geometry.
ST_AsEWKB — Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
ST_AsEWKT — Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
ST_AsGML — Return the geometry as a GML version 2 or 3 element.
ST_AsHEXEWKB — Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15
ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point.
ST_AsSVG — Returns a Geometry in SVG path data given a geometry or geography object.
ST_AsText — Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
ST_AsTWKB — Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
ST_GeoHash — Return a GeoHash representation of the geometry.
ST_AsGeobuf — Return a Geobuf representation of a set of rows.
ST_AsMVTGeom — Transform a geometry into the coordinate space of a Mapbox Vector Tile.
ST_AsMVT — Return a Mapbox Vector Tile representation of a set of rows.


MySQL only parses the text of a CHECK constraint, it does not actually enforce them.

From the documentation:

  The CHECK clause is parsed but ignored by all storage engines.

A workaround is to use a trigger:

DELIMITER //
create trigger student_info_bitrig before insert on student_info
for each row
begin
    declare errormessage varchar(100);
    if new.stu_code not like 's%' then
        set errormessage = concat('Error - Value not allowed: ', new.stu_code);
        signal sqlstate '45000' set message_text = errormessage;
    end if;
end
//

DELIMITER ;

Test:

mysql> INSERT INTO student_info VALUES (1,'k001');
ERROR 1644 (45000): Error - Value not allowed: k001
mysql>


SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary

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.


How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock?

Deadlocks will always happen at some point under the default locking strategy. However, it is unlikely in your given scenario because of the straight table scan.

However, it's more likely if you have several concurrent INSERTs and SELECTs.

Using NOLOCK means dirty reads and isn't best practice. Everyone seems to suggest them though...

The alternative is to use snapshot isolation modes: the SELECT will read the last committed data, rather than being blocked by the INSERT.


PL/SQL + after login seems Not see tnsnames.ora file on my PC

What do you mean "log in by PL/SQL"? Do you mean SQL*Plus? PL/SQL is a language used to write functions and procedures - you have to be logged in already before you can call them.

The message is fairly self-explanatory: if the client and database are using different character sets, results need to be converted from one to the other before being displayed at the client, which may cause "unexpected results" (like strange characters for example).

As the database is using UTF8 it makes sense to set the client side to use the same - how to do this depends on what platform your client is on.

On Windows, you can either set an environment variable or the NLS_LANG registry setting


Can I have different publishers and one subscriber in replication?

Yes it is possible to have many publishers and only one subscriber. You can also have one publisher and many subscribers. So in your case you will be able to publish from City1 and City2 and subscribe only City1 on those publishers. You can use this link to have an overview of the replication engine and how to setup it. http://technet.microsoft.com/en-us/library/ms151198.aspx

Hope this was helpful


MySQL Cluster FAQ states that it is possible to have a database bigger than available memory, but the documentation referenced from the FAQ entry states that only non-indexed columns of NDB tables can be stored on disk.