SQL Server database has very small backup I have a database of 216 GB. 75% is used by data, the rest is used by index and log. When I check free space in the database files I get values around 300 MB. The database is split over 3 data files. When I take a full backup without compression I get a backup of 38 GB.

Can anyone explain this? Can I recover any disk space from the database?

Update: If I try to restore the backup it requires over 200 GB of diskspace.

Update2: I got these values by running:

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles
order by fileproperty(name,'IsLogFile')

The result is:

QASDATA1    74124   73383,75    740,25
QASDATA2    69548   69114   434
QASDATA3    72288   71972,6875  315,3125
QASLOG1 374,8203125 112,1015625 262,71875

Update 3: Here is some more information about the backup itself, the query is below in the responses:

QAS x:\sql\SQL42\QAS\QAS_log_20121030.BAK   51 MB   160 MB  7 Seconds   2012-10-30 01:00:00.000 16269000022730200001    16271000002874600001    Transaction Log SQL42   FULL
QAS x:\sql\SQL42\QAS\QAS_data_20121025.BAK  38820 MB    224903 MB   3458 Seconds    2012-10-25 09:55:16.000 16265000012013400138    16265000012581700001    Full    SQL42   FULL

As Paul Randall stated in his MCM Internals on Backup/Restore for SQL Server, the backup file does not require the empty space in the database to be in the backup, but it DOES require it when restoring the backup.  The backup file basically puts pointers to where the empty space needs to repopulate when it rebuilds it during the restore process.

Database compression works differently than removing empty space from backups.  It uses something closer to a winzip algorithm.  If you were to winzip/winrar/7zip with maximum compression your backup, you would get very similar space usage as SQL Server native compression.  One of the benefits of using it native in SQL Server is it requires a lot less space to write to disk thus it's faster assuming your CPU isn't pegged.

Mr. Denny is also correct, so that could be part of it hence why I voted it back up.

Paul Randal's MCM video list including backup/recovery as mentioned:
http://www.sqlskills.com/T_MCMVideos.asp

EDIT:
After re-reading your answer there's some discrepancies I want to clarify, but the information stated above is still relevant.

You have a database that when you right click on the DB, properties, and space used, you see:
Free Space: 300MB
SP_HELPDB shows: 216GB

run:
dbcc sqlperf('logspace').  What do you get back for the log life related to this DB?  Please post your findings.

Please validate and get back to us.

/*
10/29/2012 Edit after posting findings & using block comment code to drive Buck Woody crazy:

Next, please run this, it will tell us everything about your backup right when SQL Server took it.  This modified script originally from Pinal will help us validate if something after the backup happened to the file, or if SQL Server really just spit out a small backup:
*/

USE [DatabaseName]

GO

SELECT TOP 100

s.database_name,

m.physical_device_name,

CAST(CAST(s.compressed_backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS CompBkSize,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY s.backup_start_date DESC, s.backup_finish_date

GO

Edit after final results were posted:
As suspected, your backups have compression enabled.  As you can see in the query, Compressed backup size shows 38GB where as just 'backup size' shows 228GB. Review your backup scripts, you will find 'WITH COMPRESSION' in it.

Remember the factors that affect backup size:
-Transaction Log files are backed up along the SQL Server backup file.  This is for redo/undo operations during the recovery portion.

-Data pages, GAM, SGAM, etc. are to the backup file. Empty space in the database is removed and only pointers are placed, thus if you have lots of free space your backup will remain small.  However it will be required when you restore.

-Compression plays a huge factory typically in SQL Server databases.  One of the biggest contributing factors.

-Breaking up a backup file into smaller multiple files obviously will also affect size.

Let us know if you have any questions but your original question regarding the small backup size is resolved.


Use SQL Script Parameter to Map Drive I am trying to map a drive to a UNC path where the UNC Path is a variable, but I keep getting an error saying:

  Incorrect syntax near '+'

I execute this script:

DECLARE @UNCSourcePath nvarchar(255) = '\\OTHER_SERVER\C$\Program Files'
EXEC xp_cmdshell 'NET USE S: ' + @UNCSourcePath

Any idea what the syntax should be?

It looks like it only accepts one argument. So try doing this...

DECLARE @UNCSourcePath nvarchar(255) = 'NET USE S: "\\OTHER_SERVER\C$\Program Files"'
EXEC xp_cmdshell @UNCSourcePath


Will this trigger significantly degrade performance? I have this trigger to (do my best) to prevent brute forcing logins. It has two seperate functions:

If a failed login has been attempted more than 4 times (so read that exactly 5 times) from the same IP address within the last three minutes, that IP_address is added to the blacklisted_ips table. The IP address then has a "cool down" time period specified by a column in the table.
If a failed login has been attempted more than 3 times (so read that exactly 4 times) from the same username within the last two minutes (so IP address agnostic), then the username is added to the blacklisted_usernames table. The username then has a "cool down" time period specified by a column in the table.

My question is, looking at this trigger, it looks performance inefficient to me. Am I correct in thinking that, or is the performance effect negligible?

CREATE TRIGGER prevent_brute_force
AFTER INSERT ON login_att
FOR EACH ROW
BEGIN
    SELECT ip_add, was_success INTO @ip, @result
    FROM login_att
    ORDER BY login_att_id DESC LIMIT 1;

    SELECT ip_add INTO @check_ip
    FROM login_att
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 3 MINUTE)
      AND ip_add = @ip
    HAVING COUNT(ip_add) > 4;

    IF @check_ip IS NOT NULL
    THEN
        IF @result = 0
        THEN
            INSERT INTO blacklisted_ips (ip_add) VALUES (@ip);
        END IF;
    END IF;

    SELECT username_att into @username_att
    FROM login_att
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 2 MINUTE)
      AND was_success=0 HAVING COUNT(username_att) > 3;

    IF @username_att IS NOT NULL
    THEN
        INSERT INTO blacklisted_users (username) VALUES (@username_att);
    END IF;
END$$

The best way to evaluate performance is by measuring it- you are the only person that can evaluate that on the real application, or, much better, on a staging environment with a copy of the traffic or an emulation of such.

Hardware, other code running, throughput, how slow are those new queries, amount of data you have, etc. will tell you what you want to know- to the point that the same code can make it or break it under different circumstances.

Having said that, if you just want a high level opinion of the idea will work, without knowing much about your system- I would personally would do this kind of analysis asynchronously based on external logs (or your login_att table), as normally you don't want to punish your normal users if the occurrences are not frequent enough. Also triggers are not my favorite way to handle application logic. However, if you cannot modify the source code, it doesn't add much to the total latency, and you test it and it seem acceptable, go on- but please test it doesn't break your application when there are thousands of parallel attempts per second due to the extra writes.


pg_dump inserting C source code in dump file Am backing up daily my DB with pg_dump. The strange issue is that when I try to restore it with psql, errors are thrown. After opening the dump file with vim and analyzing the line that is causing the error, it is clear that there is some C source code being written to the file. Not only one, but chunks of them.

How could this happen? Any way to avoid it?

The dump is done through the postgresql webmin module.

I'm using PostgreSQL 9.3.

Problem solved. Apparently there is some sort of issue with the PostgreSQL webmin module.

Dumping and restoring the DB with pg_dump in command line solved the issue. Will now backup with scripts rather that using webmin.


Query to categorize rows based on a "time" column without using a CASE expression There is a ProductTT table as you can see below:

[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)

...which contains the following rows:

1   XX   0030
2   UY   0354
3   YY   0517
4   ZZ   0712
5   WW   0415
6   GG   1112
7   MM   1030
8   HH   0913

Note: The format of the data in time column is hh:mm so 0030 is 00:30.

I want to write a query to categorize the rows based on their time value. I need to have 4 categories like this:

category1   00 to 03
category2   03 to 06
category3   06 to 09
category4   09 to 12

I need to see how many rows pertain to each category.

My attempt so far

What I've written so far is like this:

With CTE
AS (select ID,
       product,
       [time],
       Case
         When left(time,2)>=00 and left(time,2)< 03  then  'group1'
         when left(time,2)>=03 and left(time,2)< 06  then  'group2'
         when left(time,2)>=06 and left(time,2)< 09  then  'group3'
         when left(time,2)>=09 and left(time,2)<=12  then  'group4' End AS groupID
from [dbo].[ProductTT]

)
 select groupid,count(*) as recordcount
 from cte
 group by groupid

My question

That query works fine but I just want to know whether there are better ways to write this query and avoid using a CASE expression.

You stored Time as an int but then displayed it as a string (with leading zeros). Those don't get stored, so in order to perform calculations that need to handle the leading zeros, you need to convert to a string first (your current query doesn't do this, so either your query doesn't work, or that table structure is not accurate). Since this is a linear calculation (groups of 3), you can simplify away the CASE expression by simply dividing the first two digits in the time by 3 (and thanks to SQL Server's integer division, the remainder gets discarded, and we add 1 to go from 0-3 to 1-4). Of course, there is an exception, because you want 12 PM to be in group 4, not group 5. With a CASE expression this could just be left to the ELSE clause, but if you eliminate CASE, you will have to deal with that exception explicitly - that's all the COALESCE/NULLIF stuff at the end.

;WITH x AS
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4)
  FROM dbo.ProductTT
), y AS
(
  SELECT ID, Product, [Time], h = CONVERT(char(2),[Time])
  FROM x
)
SELECT ID, Product, [Time],
  [GroupID] = 'group' + CONVERT(char(1),h/3+1-COALESCE(NULLIF(h%11,1)-h%11,1))
FROM y;

Results:

ID  Product  Time   GroupID
--  -------  ----   -------
1   XX       0030   group1
2   UY       0354   group2
3   YY       0517   group2
4   ZZ       0712   group3
5   WW       0415   group2
6   GG       1112   group4
7   MM       1030   group4
8   HH       0913   group4

I strongly recommend you use the actual time data type, as that is what it was designed for. Then you can use DATEPART(HOUR( in your calculations instead of messy string manipulation, the query above is less complex and, as a bonus, you get built-in validation, to avoid invalid times like 1369 and 9997. Or if the leading zeros are important but you don't care about validation, use char(4) instead of int.

I also think you need to handle the case where an event happens in the afternoon.

And FWIW I am not sure why you don't want to use a CASE expression here. It's a few more characters, sure, but it's a lot more clear what the query is actually doing. Code that is self-documenting is much more valuable than code that is slightly shorter. This is simpler IMHO, and would be even simpler if you used the right data types:

;WITH x AS
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4)
  FROM dbo.ProductTT
)
SELECT ID, Product, [Time],
  GroupID = 'group' + CASE CONVERT(char(2),[Time])/3
    WHEN 0 THEN '1'
    WHEN 1 THEN '2'
    WHEN 2 THEN '3'
    ELSE '4' END
FROM x;


"No database selected" or "Table 'DB.table' doesn't exist" failures in pt-upgrade run where queries from multiple DBs were collected with tcpdump I collected queries on our Master using the following tcpdump command:

tcpdump -i any -s 65535 -x -n -nn -q -tttt 'port 3306' > tcpdump.tcp

The queries are executed amongst several databases.

I then ran this file through pt-query-digest using the following command:

pt-query-digest --output=slowlog --no-report --sample 100 --type tcpdump tcpdump.tcp > slow.log

Then I ran pt-upgrade against two Slaves like this:

pt-upgrade --user user --ask-pass --run-time=1h --upgrade-table percona.pt_upgrade h=10.1.1.1 h=10.1.1.2 slow.log

But I got a bunch of failures since it does not appear to specify which database the query should be executed against.

How is one supposed to use pt-upgrade when queries are collected amongst multiple DBs? AFAICT this isn't specified in the documentation anywhere.

Are you supposed to use --filter with pt-query-digest to just output queries for a particular database and then specify --database with pt-upgrade? Rinse and repeat per database.

It takes several hours to parse my gigantic tcpdump capture so any guidance here is appreciated.

Thanks!

P.S.

I used this article as a starting point but it is outdated. e.g. pt-query-digest doesn't have a --print option anymore.

Does it still fail to work if you omit --sample?
(from manual)

  Also note that pt-query-digest may fail to report the database for
  queries when parsing tcpdump output. The database is discovered only
  in the initial connect events for a new client or when  is
  executed. If the tcpdump output contains neither of these, then
  pt-query-digest cannot discover the database.

Consider using the general log instead of tcpdump?


will subscriber be available, if publisher or distributor is down? We have to set up replication between two SQL Server databases residing on two different servers connected to the same domain. Our concern is that if the publisher is down, or distribution database is down, will it by any way affect the availability of subscription database? Here I mean to say if server on which distribution resides goes off while it is updating the tables on subscriber will subscription database tables be accessible? Does distributor acquire a lock on subscriber for updating the rows? We are planning to set up transactional replication.

There are locks taken on the subscriber during data transfer which can cause blocking on the subscriber - so you can definitely encounter issues there (and there are options to modify this behaviour at the risk of not being able to rollback failed transactions).

However there aren't any other magical locks or connections between the servers.

But also when the publisher/distributor go down I've never seen the insert also fail to go down and not release the locks (aside from standard rollback time). I'd hesitate to say it wasn't possible but it would be abnormal and I haven't seen it.

The much more common scenario is that the subscriber goes down, log records accumulate in the publisher, fill the disk and your publisher goes down. A large log file and monitoring of backups, space, and the replication itself are essential.


Return Bit Value based on previous records by Category How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID?  So there has to be 4 failures (or FALSE records) in the last six

MAKEID       FAILURES    CREATEDATE
101          TRUE        12/1/2015
101          FALSE       12/5/2015
101          TRUE        12/6/2015
101          FALSE       12/8/2015
101          TRUE        12/10/2015
101          FALSE       12/15/2015
101          FALSE       1/1/2016
101          FALSE       1/2/2016
102          TRUE        12/2/2015
102          TRUE        12/4/2015
102          FALSE       12/5/2015
102          TRUE        12/6/2015
102          FALSE       12/7/2015
102          TRUE        1/1/2016

Query would return the following:

MAKEID    FOUROFLASTSIXFAILED
101       TRUE
102       FALSE

Requirement is there needs to be 4 or more failures in last six records.  So if there were 4, 5, or 6 failures in the last six records FOUROFLASTSIXFAILED would return TRUE.  If there were only 1, 2, 3 records for a given MAKEID that would return FALSE because there at least needs to be 4 failures in previous six records.

Here is one approach (but please, stop changing your requirements):

DECLARE @x TABLE(MAKEID INT, FAILURES varchar(5), CREATEDATE date);

INSERT @x VALUES
(101,'TRUE ','20151201'),    (101,'FALSE','20151205'),    (101,'TRUE ','20151206'),
(101,'FALSE','20151208'),    (101,'TRUE ','20151210'),    (101,'FALSE','20151215'),
(101,'FALSE','20160101'),    (101,'FALSE','20160102'),    (102,'TRUE ','20151202'),
(102,'TRUE ','20151204'),    (102,'FALSE','20151205'),    (102,'TRUE ','20151206'),
(102,'FALSE','20151207'),    (102,'TRUE ','20160101');

;WITH x AS
(
  SELECT MAKEID, FAILURES,
    -- allow us to identify the last 6 rows:
    rn = ROW_NUMBER() OVER (PARTITION BY MAKEID ORDER BY CREATEDATE DESC)
  FROM @x
)
SELECT MAKEID, FOUROFLASTSIXFAILED = CASE
  -- make sure there have been at least 6 tests - though this may change:
    WHEN COUNT(*) >= 6
  -- make sure *at least* 4 of those have failed *within the last 6*
  -- though this may change (again), too:
    AND COUNT(CASE WHEN rn <= 6 AND FAILURES = 'TRUE' THEN 1 END) >= 4
  THEN 'TRUE' ELSE 'FALSE' END
FROM x
GROUP BY MAKEID
ORDER BY MAKEID;

Results (I think these are right, as opposed to the desired output stated in your question, which seem to conflict with your stated requirements):

MAKEID    FOUROFLASTSIXFAILED
------    -------------------
101       FALSE
102       TRUE

It would seem to me that MAKEID = 102 is the only one that has failed exactly four times out of its last six entries, unless the FAILURES column in the original table really represents NOT_FAILURES.


SQL Server AlwaysOn Availability Groups Cluster File Share Permissions Just need to double confirm, when setting up the file share witness to act as a quorum vote in the HA cluster - does each server node within the cluster need access to that file share or is it just the cluster name that needs write access to the file share?

Pretty sure its just the cluster object name that needs access to the file share but want to be doubly sure

HA cluster - does each server node within the cluster need access to that file share or is it just the cluster name that needs write access to the file share?

Each node participating in WSFC should have access to the FS witness and the WSFC name or cluster name object( CNO) should have read write permission on fileshare folder and also at NTFS level.
A FS witness should not be part of WSFC and can reside on "any" windows machine which is accessible to both the nodes. Please note that the file share witness should reside on one of the machines which is part of domain in which WSFC is created.


Index max row size error Is there a upper bound for an array column?

I am getting this error when inserting into the array field -

PG::Error: ERROR:  index row size 3480 exceeds maximum 2712 for index "ix_data"

Here's my table definition -

create table test_array(id varchar(50), data text[]);

ALTER TABLE test_array ADD PRIMARY KEY (id);

CREATE INDEX ix_data ON test_array USING GIN (data);

I need an index on array field, since I am doing some lookups on it.

The problem
Here is a very similar case discussed on pgsql.general. It's about the limitation in a b-tree index, but it's all the same because a GIN index uses a b-tree index for keys internally and therefore runs into the same limitation for key size (instead of item size in a plain b-tree index).
I quote the manual about GIN index implementation:

Internally, a GIN index contains a B-tree index constructed over keys,
where each key is an element of one or more indexed items

Either way, at least one array element in your column data is too big to be indexed. If this is just a singular freak value or some kind of accident you may be able to truncate the value and be done with it.
For the purpose of the following demo I'll assume otherwise: lots of long text values in the array.
Simple solution
You could replace elements in your array data with according hash values. And send look-up values through the same hash function. Of course, you probably want to store your originals in addition somewhere. With that, we almost arrive at my second variant ...
Advanced solution
You could create a look-up table for array elements with a serial column as surrogate primary key (effectively a radical kind of hash value) - which is all the more interesting if involved element values are not unique:
CREATE TABLE elem (
  elem_id serial NOT NULL PRIMARY KEY
, elem    text UNIQUE NOT NULL
);

Since we want to look up elem, we add an index - but an index on an expression this time, with only the first 10 characters of the long text. That's should be enough in most cases to narrow a search down to one or a few hits. Adapt the size to your data distribution. Or use a more sophisticated hash function.
CREATE INDEX elem_elem_left10_idx ON elem(left(elem,10));

Your column data would then be of type int[]. I renamed the table to data and got rid of the ominous varchar(50) you had in your example:
CREATE TEMP TABLE data(
  data_id serial PRIMARY KEY
, data int[]
);

Each array element in data refers to a elem.elem_id. At this point, you may consider to replace the array column with an n:m table, thereby normalizing your schema and allowing Postgres to enforce referential integrity. Indexing and general handling becomes easier ...
However, for performance reasons, the int[] column in combination with a GIN index may be superior. Storage size is a lot smaller. In this case we need the GIN index:
CREATE INDEX data_data_gin_idx ON data USING GIN (data);

Now, each key of the GIN index (= array element) is an integer instead of a longish text. The index will be smaller by several orders of magnitude, searches will consequently be much faster.
The downside: before you can actually perform a search you have to look up the elem_id from the table elem. Using my newly introduced functional index elem_elem_left10_idx, this, too, will be much faster.
You can do it all in one simple query:
SELECT d.*, e.*
FROM   elem e
JOIN   data d ON ARRAY[e.elem_id] <@ d.data
WHERE  left(e.elem, 10) = left('word1234word', 10) -- match index condition
AND    e.elem = 'word1234word';  -- need to recheck, functional index is lossy

You may be interested in the extension intarray, that supplies additional operators and operator classes.
Fully functional live demo on sqlfiddle.


update stored proc ony fields that are passed to the proc and leaving others alone i have a  stored proc  that does a simple update (see below).

i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want to update the table data to null, but rather just leave what the current value is.

can this be done in a sql update statement such as this?

ALTER PROCEDURE [dbo].[upd_MessageDetail]
    @MessageId INT,
    @IsDraft INT,
    @IsPreviewed INT,
    @IsRead INT,
    @IsFlagged INT,
    @IsDeleted INT,
    @IsArchived INT
AS
BEGIN

        UPDATE MessageDetail
        SET
            IsDraft = @IsDraft,
            IsPreviewed = @IsPreviewed,
            IsRead = @IsRead,
            IsFlagged = @IsFlagged,
            IsDeleted = @IsDeleted,
            IsArchived = @IsArchived
        WHERE MessageId = @MessageId

Something like below if I understand you correctly.

UPDATE MessageDetail
        SET
            IsDraft = ISNULL (@IsDraft, IsDraft),
            IsPreviewed = ISNULL (@IsPreviewed,  IsPreviewed),
            IsRead = ISNULL (@IsRead, IsRead),
            IsFlagged = ISNULL (@IsFlagged, IsFlagged),
            IsDeleted = ISNULL(@IsDeleted, IsDeleted)
            IsArchived = ISNULL(@IsArchived, IsArchived)
        WHERE MessageId = @MessageId


How to optimize selection of pairs from one column of the table (self-join)? I'm using PostgreSQL 9.5.19, DBeaver 6.3.4

I have a table where one row is - user's name, place he attended, time when he was there

I need to select all pairs of places where any user was (if user was at place a and place b i need row like this: user, place a, place b, time at place a, time at place b)

The ponds table:

CREATE TABLE example.example (
    tm timestamp NOT NULL,
    place_name varchar NOT NULL,
    user_name varchar NOT NULL
);

Some sample data:

INSERT INTO example.example (tm, place_name, user_name)
values
('2020-02-25 00:00:19.000', 'place_1', 'user_1'),
('2020-03-25 00:00:19.000', 'place_2', 'user_1'),
('2020-02-25 00:00:19.000', 'place_1', 'user_2'),
('2020-03-25 00:00:19.000', 'place_1', 'user_3'),
('2020-02-25 00:00:19.000', 'place_2', 'user_3');

I'm trying this script:

select
   t.user_name
  ,t.place_name as r1_place
  ,max(t.tm) as r1_tm
  ,t2.place_name as r2_place
  ,min(t2.tm) as r2_tm
from example.example as t
join example.example as t2 on t.user_name = t2.user_name
                       and t.tm < t2.tm
                       and t.place_name <> t2.place_name
where t.tm between '2020-02-25 00:00:00' and '2020-03-25 15:00:00'
  and t2.tm between '2020-02-25 00:00:00' and '2020-03-25 15:00:00'
    group by t.user_name
       , t.place_name
       , t2.place_name

Seems like it gives me the right result, but it works really slow.
Can I optimize it somehow?

Postgresql 9.5.19 has windowing functions that prove helpful in such situation. The lead() function give you access to the next row in a "partition".

You could try something like that :

SELECT
  user_name,
  place_name AS r1_place,
  tm AS r1_tm,
  lead(place_name) OVER (PARTITION BY user_name ORDER BY tm) AS r2_place,
  lead(tm) OVER (PARTITION BY user_name ORDER BY tm) AS r2_tm
FROM example
ORDER BY 1, 3;

resulting in :

user_name|r1_place|r1_tm              |r2_place|r2_tm              |
---------|--------|-------------------|--------|-------------------|
user_1   |place_1 |2020-02-25 00:00:19|place_2 |2020-03-25 00:00:19|
user_1   |place_2 |2020-03-25 00:00:19|        |                   |
user_2   |place_1 |2020-02-25 00:00:19|        |                   |
user_3   |place_2 |2020-02-25 00:00:19|place_1 |2020-03-25 00:00:19|
user_3   |place_1 |2020-03-25 00:00:19|        |                   |

Not sure about the performance part however... you should make some tests.

Of course, you can filter out null results:

SELECT * FROM (
  SELECT
    user_name,
    place_name AS r1_place,
    tm AS r1_tm,
    lead(place_name) OVER (PARTITION BY user_name ORDER BY tm) AS r2_place,
    lead(tm) OVER (PARTITION BY user_name ORDER BY tm) AS r2_tm
  FROM example
  ORDER BY 1, 3) req
WHERE r2_place IS NOT null


Will using bigint vs mediumint have a performance impact? I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I can also technically split up the numbers, and that way use a mediumint. I do not care about storage, but I do care about speed/performance. Is it better to use mediumint than bigint?

This is on an indexed, auto-increment ID column which will be used a lot for SELECT queries.

Why does the user know the value of the ID column? This should be abstracted from them. They should be able to identify their data by some other natural key (even if this is only implemented as a unique constraint and you use the surrogate ID as a primary key for performance reasons). If you take away the ID column and you can no longer uniquely identify a row, there is likely an issue with the design.

While you can save some space and improve performance in small ways by choosing a different data type, you need to balance performance with actual business requirements. If you're going to exceed the bound of a smallint or mediumint then you shouldn't use either. Don't make your design complex for the exception (e.g. "splitting up the numbers") to try and optimize for the general rule. This complexity is unnecessary IMHO and will only lead to problems.

In SQL Server we can take advantage of data compression, which essentially treats a bigint column in the following way:

if the number fits in a tinyint (0-255), it takes 1 byte
if the number fits in a smallint (-32K -> 32K), it takes 2 bytes
if the number fits in an int (-2Bn -> 2Bn), it takes 4 bytes
only if the number exceeds the bounds of an int does it take 8 bytes

(This is a simplification. And this is the worst case where it treats each value independently only if values on a page are unique and non-sequential, which wouldn't be the case for an auto-increment column. In addition page compression uses other methods like dictionary/prefix, which can reduce storage even further.)

Of course compression trades CPU for I/O, so while you save raw storage and reduce I/O, you pay in CPU. Even today most systems are I/O-bound, so this is a benefit, but shouldn't be considered if you're CPU-bound.

I don't believe there is similar functionality in MySQL, but I am not certain. If there is you should look into it.

And one final note. You say "I do not care about storage, but I do care about speed/performance." If you care about speed/performance, then you should care about storage. The more pages it takes to store your data, the harder the engine is going to have to work to read/write those pages, perform seeks, etc. But again, there needs to be balance. You shouldn't take an extra four weeks over-engineering a solution that is going to save you 10 seconds a week.


Auto-incremented ID with sequence and trigger: How to handle manual overrides (causing non-unique error) I have a sequence and trigger that provide auto-incrementing IDs to a PROJECT_ID column:

PROJECTS
+------------+-----+
| PROJECT_ID | ... |
+------------+-----+
| LC000001   |     |
| LC000002   |     |
| LC000003   |     |
| LC000004   |     |
| LC000005   |     |
+------------+-----+

CREATE SEQUENCE SEQ_PROJECT START WITH 1 INCREMENT BY 1

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
    WHEN (NEW.PROJECT_ID IS NULL)
      BEGIN
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');
      END;

Source: Trigger to get next prefixed ID: Is there a better way?

The sequence and trigger work just fine under normal circumstances.

However,  the editing environment in my application is far from normal. Users edit the data by accessing the entire table; it's almost as if the table is treated like big spreadsheet or something (which is kind of frightening when I think about it). Anyway, the scenario is called "editing in the attribute table window". Users can manually edit any column in any row, which means they can override the IDs that are generated by the sequence and trigger.

For example:

A user creates a new row.  The sequence and trigger provide an ID of LC000006.
A user then goes and manually changes LC000001 to LC000007. (I can't think of a good reason why a user would do this, but they have the ability, which means it will probably happen.)
A user creates another new row.  The sequence and trigger attempt to provide an ID of LC000007, but it throws an error:

Database Row Change: An unexpected failure occured. Underlying DBMS error [ORA-00001: unique constraint (USER1.PROJECT_ID_IDX) violated][USER1.PROJECTS]

The error is caused by the unique index on the field.
There are can't be duplicate values.

Practically speaking, I could live with the fact that my users would get an error like this (if someone on the team was unwise enough to manually override one or two PROJECT_IDs). All users need to do is try again, and the sequence/trigger just magically skips to the next number.

But of course, there's the possibility that there are 10's or 100's of IDs that are out-of-sequence (not just one or two). Believe it or not, this is a real possibility, due to the existence of a tool called the field calculator. With this in mind, getting an error message for 10's or 100's of out-of-sequence IDs doesn't seem so practical.

So, I'm curious, can I avoid this problem somehow?

Instead of trying to address what happens after someone updates the PROJECT_ID column, have you tried denying updates of the PROJECT_ID column in the first place?

Perhaps revoking update privileges on the PROJECT_ID column? Or perhaps ... yuck ... a trigger to rollback (and generate an error) any attempts to update the PROJECT_ID column?

Since you're ok with them getting an error when they attempt to generate a duplicate PROJECT_ID, I'm assuming you'd be ok with them getting an error when attempting to update PROJECT_ID ... and after a couple such attempts ... and perhaps a "WTF!?!?!" email to the DBA (you?) ... they'll get used to the fact that they can't update the PROJECT_ID column.

Or do you really want to allow them to update the PROJECT_ID column?


PG_DUMP on Replica DB server Error I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it.

I need to run pg_dump from replica although I face with error.
What do I do:

SELECT pg_xlog_replay_pause()
Run pg_dump on replica server
SELECT pg_xlog_replay_resume()

Immediately I receive after pg_xlog_replay_pause()

ERROR:  recovery is not in progress

And when the command of pg_dump starts I see:

pg_dump: [archiver (db)] query failed: ERROR:  cannot assign TransactionIds during recovery
pg_dump: [archiver (db)] query was: SELECT pg_export_snapshot()

Questions:

Does it mean I can't do pg_dump from replica? I have a feeling that in case of wal streaming replication it would be possible. Correct me if I'm wrong.
I can't find any information for SELECT pg_xlog_replay_pause() / SELECT pg_xlog_replay_resume() works only with wal streaming setup.
Can someone tell if this true?

Thanks in advance.

Customer's setup is:
They copy xlog to network share and  replica recovery.conf consist of

standby_mode = 'on'
restore_command = 'if exist A:\\Logs\\From_Master_DB\\%f (copy A:\\Logs\\From_master_DB\\%f  %p) else (exit /b 1)'
archive_cleanup_command = '"C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  A:\\Logs\\From_Master_DB %r && "C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  D:\\PG-SQL\\data\\pg_xlog %r'
recovery_target_timeline = 'latest'

Parallel pg_dump against a replica is not supported cleanly until v10.

You can either drop the -j, run the dump against the master rather than the replica, or you can use --no-synchronized-snapshots which will make your backup be potentially inconsistent.

Pausing the recovery might cure the consistency problem with use of --no-synchronized-snapshots, but the pause must be run on the replica, not on the master, which is what you seem to doing.


SQL Server 2012 Always On AG - DBEngine Service account locking I am having a strange issue and am hoping you guys might be able to help!

Problem: I have a 2 node SQL Server 2012 Availability Group Cluster utilising a FSW. Both nodes are using the same DBEngine Service account (as is normal in these setups).

There are currently 3 Availability Groups on this cluster, and it's been working fine for quite some time.

Today I restarted the passive node DBEngine account (as I was adding an SSL Certificate for a new application, new availability group). When the node came back up, it was no longer synchronising with node 1. The state of the replica was disconnected, and I could see lots of login failures on Node 1 (active node) SQL Logs.

I found that the DBEngine service account had locked. I had it unlocked, but it soon locked again.

I removed the SSL Certificate and restarted the passive node again (so it was in the same state I started with) and it started up fine, but within a minute, the service account had locked out again.

Steps I tried:

created a new service account to rule out the account being used elsewhere, started both nodes under the new account.... account locked out when node 2 started
unlocked the account, stopped node 2. restarted node 1. Account fine... waited.. account still fine. Started node 2 service... account locked out.
recreated mirroring endpoints on both nodes and reapplied connect permissions to the dbengine service account. - this didn't fix it.
restarted both VM's
removed the node 2 replica from the availability group, removed all databases (from node 2) and dropped the mirroring endpoint on node 2. restarted node 2 service. - at this point both nodes were happily running under the same service account.
tried re-adding node 2 as a replica using the wizard. It added it, backed up the database, restored to node 2, and got to the very last step where it connects it, and the password locked out again!

Has anyone got any ideas? Any input would be greatly received!

Sam

So just in case someone wants to know what caused this, It was a group policy!

Some time ago, unbeknown to me, the domain controllers for the domain in question had been upgraded to Server 2012. Along with this came a whole bunch of Windows Server 2012 group policies. Additional policies had been added to one of the parent server OU's with a filter applied.

Unfortunately the filter had a typo, and so it was being applied to all of my database servers on this domain.

I had almost ruled out it being a GP issue, as I seemed to have all the permissions I needed, and I could see connections coming in on the correct ports between each node. The servers were happily running as single nodes!

I asked the server team to move the servers (just to see) to the 'computers' OU, and after a forced gpupdate, bingo!

Unfortunately, I am unsure exactly which policy caused the problem as there were a lot! There was a number to do with NTLM usage and authentication, and I'm convinced the issue was related to this. I will at some point set up a test lab to try and replicate the issue.

So there you go! Always check group policies, even if you believe (like I did) that nothing had changed!


Postgres scalability - What is the impact of connection pooling? We had a question over on Server Fault that raised an interesting question:
In light of the scalability improvements in Postgres 9.2 scalability improvements, is it better to use connection pooling mechanisms to avoid the overhead of making extra connections to the database, or is the connection overhead worth the improved read performance?

Relating it specifically to my environment: We have a web application that is database backed and read-centric, and we're currently running on Postgres 8.4.

Our re-implementation will be launching next year, along with an upgrade to 9.2, and gives each Apache worker process its own connection to the database (and thus its own Postgres backend which is preserved for the life of the Apache worker).
Based on what we've seen this seems to be a good balance between the overhead of connecting to the database and having more workers handling the read load, though we haven't yet done any substantial benchmarking of our own to confirm this.

Does that implementation seem like a reasonable one, and are there other options / connection pooling mechanisms which we should be considering in light of the recent scalability improvements?

I think you see a false dichotomy that does not exist.

It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful for other reasons:

Block until a connection is available rather than return an immediate max_connections exceeded error;
You can switch the pool target server if you fail-over to a standby without having to reconfigure applications;
You can limit application database workers to lower than max_connections, so you can still make reporting and maintenance connections as a non-superuser.

Additionally, if suitable for your application you can use transaction-level pooling to greatly increase the number of clients that can be served by your server.

I would not try to keep a strict 1:1 mapping from Apache workers to PostgreSQL workers, personally. If you've got (say) 16 cores and good I/O on your PostgreSQL box you might want something like 16-20 active PostgreSQL workers for optimal performance. You're almost certain to want more Apache workers than that, since they'll be kept busy by things like:

persistent HTTP connections from idle clients;
Unresponsive or very slow clients;
Intentional DoS connections;
Network interruptions between client and server; etc

If possible, consider a transaction-pooling design with short-lived transactions instead.


Update rows in a table with an index column starting from 0 with consecutive numbers I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a script to identify the rows who do not follow this rule but I don't know how to modify those rows (ids). The script to identify the rows is:

select ID,NRCRT,H_ID,INDEX_COLUMN from ANEXE_H_ROWS where H_ID in (
select anexa.id from (
    select ANEXA_ROWS.H_ID as id, ANEXA_ROWS.INDEX_COLUMN as idx
        from chest CH
        inner join investig INV on INV.FK_ID_CHEST = CH.ID
        inner join tip_investig TIP_INV on TIP_INV.ID = INV.FK_TIP_INVESTIG
        inner join ANEXE_H ANEXA on ANEXA.FK_ID_INVESTIG = INV.ID
        inner join ANEXE_H_ROWS ANEXA_ROWS on ANEXA_ROWS.H_ID = ANEXA.ID
    ) anexa
group by anexa.id having max(anexa.idx)+1<>count(*) or min(anexa.idx)<>0
)
order by H_ID,INDEX_COLUMN

and let's say it returns:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    8       1136    7
2518    9       1136    8
2519    10      1136    9
2520    11      1136    10
2521    12      1136    11
2522    13      1136    12
2523    14      1136    13

3000    2       1137    1
3001    3       1137    2
3002    4       1137    3

How can I modify the script to alter the table to return:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    1       1136    0
2518    2       1136    1
2519    3       1136    2
2520    4       1136    3
2521    5       1136    4
2522    6       1136    5
2523    7       1136    6

3000    1       1137    0
3001    2       1137    1
3002    3       1137    2

To update the values in the table, you can use a MERGE statement (alter is for changing the structure of a table, not for changing values. That is done with UPDATE or MERGE)

merge into anexe_h_rows
using (
  select id,
         row_number() over (partition by h_id order by nrcrt) as rn
  from anexe_h_rows
) t ON (t.id = anexe_h_rows.id)
when matched then
  update set nrcrt = rn, index_column = rn - 1;

This assumes that anexe_h_rows.id is the primary key of that table.

It also assumes that value for the column nrcrt is always one bigger than the one for index_column (which raises the question why you have both - because one can easily be derived from the other).


MongoDB scaling when working with files I have read through the MongoDB manual about sharding and replicasets.

However, I would like to know if the following can be achieved with sufficient performance (read/write):

Saving 10,000,000 files via GridFS on MongoDB instances
Total file size of about 2TB without indexes and journals
10,000 writes / day
10,000 reads / day
Querying for a document does not need respond instantly, 2 seconds is still acceptable

Setup in mind:

1 Replicaset of 3 nodes
Each node with 32GB Ram, 2TB SSD
1 Mongos instance
1 Config server

In the case the proposed setup would not suffice to obtain the goal of storing 10,000,000 documents and writing/reading 10,000 a day with fairly good performance, what would be the next step to take?

Adding more RAM to each node
Adding more disk capacity to each node
Adding Another replica set with the same configuration
Sharding

I would like to avoid sharding as much as possible since I consider it best practice to avoid sharding whenever possible.
I feel this would only complicate the topology unnecessarily and this would most likely be overkill in this case

Some advice would be much welcome.

Cheers

My answer is : It depends.
If you are accessing files by _id field, which is already indexed then you don't need to add more memory soon.

The _id field which is type of ObjectID is 12 byte in size. That means it can hold up to 2^(12*8) files. 3 byte is for machine ID which is a hash value and has a fixed vale on the machine can be subtracted which gives you approx 2^72 files. For the reference, 2^20 is 1,048,576.

In terms of the memory, the index on the _id field needs 10,000,000 x 12 byte = 114 MiBytes. To be honest, I don't now how much overhead there will be for an index which holds 10 millions value but I don't think that it will need more than 1 Gigabyte.

Now, if your _id field is not a type of ObjectID than do the math.

In the gridfs, filename value of the files collection is also indexed. If you are not accessing files using filename, then you may leave it blank and drop the index for the filename.

On the other side, if you will add some metadata to the files you added and want to query the files according to those metadata, then you should have indexes for those metadata and do the math again.

I have a production environment which has over 3,000,000 pdf files (takes 180 Gig space on the disk). My server is a virtual server which has 4 vCPU and 4 Gig RAM, still no problem. The specs you provided is way too high for your needs. You can save billions of files with those servers. Especially if you have SSD. Because even if your indexes do not fit into the memory, swapping will be very fast, you won't even notice a slowdown.


Delete an object from within an array using JSONB in PostgreSQL I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table called site_content. I am trying to remove the object based on it's ID.

Here is the sample JSON

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick",
                "teamName": "Shire Soldiers",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            },
}

Below is the query I am attempting to use to delete the object within the array

UPDATE site_content
SET content = jsonb_set(content, '{playersContainer,players}'::text[], content->'playersContainer'->'id'->'players' #- '{1}'::jsonb

However this seems to delete all the data from my content row in my table and not just the array with an ID of 1 in my players array.

To remove key (warning - json/jsonb arrays index starts from 0 unlike pure SQL arrays):

UPDATE site_content
   SET content #- '{playersContainer,players,0}'::text[];

To remove array element with specific id:

UPDATE site_content
   SET content = content #- coalesce(('{playersContainer,players,' || (
            SELECT i
              FROM generate_series(0, jsonb_array_length(content->'playersContainer'->'players') - 1) AS i
             WHERE (content->'playersContainer'->'players'->i->'id' = '"2"')
         ) || '}')::text[], '{}');


Database Modelling of Type, Subtype Relation I'm trying to model the type and subtype relationship described here.

The best approach I could come up with would look like this:

CREATE TABLE Card_Types (
       card_type varchar(12) PRIMARY KEY
);

CREATE TABLE Card_Subtypes (
       card_subtype varchar(15) PRIMARY KEY
       card_type varchar(12) REFERENCES Card_Types
       PRIMARY KEY (card_subtype, card_type)
);

CREATE TABLE Card_Characteristics (
       card_name varchar(141) PRIMARY KEY,
       -- more to come?
);

CREATE TABLE Card_Characteristics_Types (
       card_name varchar(141) REFERENCES Card_Characteristics,
       card_type varchar(12) REFERENCES Card_Types,
       PRIMARY KEY (card_name, card_type)
);

CREATE TABLE Card_Characteristics_Subtypes (
       card_name varchar(141) REFERENCES Card_Characteristics,
       card_subtype varchar(15) REFERENCES Card_Subtypes,
       PRIMARY KEY (card_name, card_subtype)
);

Sample data for Card_Subtypes:

| card_subtype | card_type |
|--------------+-----------|
| Human        | Creature  |
| Rogue        | Creature  |
| Werewolf     | Creature  |
| Thopter      | Creature  |

Sample data for Card_Characteristics_Types:

| card_name                     | card_type |
|-------------------------------+-----------|
| Kruin Outlaw                  | Creature  |
| Ornithopter                   | Artifact  |
| Ornithopter                   | Creature  |
| Akroma, Angel of Wrath Avatar | Vanguard  |

Sample data for Card_Characteristics_Subtypes:

| card_name    | card_subtype |
|--------------+--------------|
| Kruin Outlaw | Human        |
| Kruin Outlaw | Rogue        |
| Kruin Outlaw | Werewolf     |
| Ornithopter  | Thopter      |

What I don't like about this model is that sometimes you're encoding the type information in two places. If a card has the subtype Human, then it is clearly a creature, so there is no need to store that information in the Card_Characteristics_Types table.

So I guess I could only write to the types table if the card didn't also have a subtype of that type. But that's a constraint that the user would have to uphold.

I know I could accomplish some of this w/ triggers, but I'm trying to stay as close as possible to the relational model. Is there a better way to model this situation?

Important Caveat:  You need to look at the link OP provided!  It uses the words type and sub-type as jargon in a way which is similar to, but different than what programmers or even data modellers would do.

The issue is that the rules state that if a card has a sub-type, that sub-type must be valid for the given (super)type.

Since not all types have sub-types in this model, you need to have a many to many association between the list of types and individual cards.  At the same time, you need to have a many to many association between the list of possible sub-types and individual cards.

You are in a bit of a relational model rock and hard place.  It depends on how you want to optimize your model.  You can optimize for storage or you can optimize for retrieval.

Optimizing for Storage: You can eliminate the redundancy by linking types without sub-types directly to cards.  When you have a type that has sub-types, don't link the type, link the sub-type instead.  This avoids redundancy and the potential inconsistency of parents and children contradicting each other.  It sucks for data retrieval, however.

Optimizing for Retrieval: You could instead link all types and sub-types (when applicable) to cards and then you have redundancy to manage - either with triggers or some other kind of application logic.

There is one more option you could try, which is a kind of combination approach and which manages to push the complexity into a more rarely used corner of your application.  In the third way, you collapse type and sub-type into a single table with an involuted relationship (self-reference).  Now when you assign a type or sub-type it automatically implies any parents of that thing.

Given the rules requirement for enforcing membership in the type before allowing membership in a sub-type, I would be inclined to just live with the redundancy.  You need a business rule that says you can't have the sub-type if you don't have the type.  That's the kind of business rule that needs to be in code anyway.


Running total until specific condition is met I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way.

So I have orders, each order has its unique id and quantity. Quantity can go up (buying) and down (selling). Ids go in order but are not 'dense', so there might be a gap between two subsequent orders. I need to select all orders that represent the oldest position. Position is a continuous range of orders, overall sum of those is 0, example (id, qty): (1, 1) (2,2) (3,-3) (4,1) (5,-1) - there are two positions, the oldest position consists of orders with ids 1,2,3.

My current approach:

Schema

CREATE TABLE [dbo].[orders](
    [id] [int] NULL,
    [qty] [int] NULL
) ON [PRIMARY]

Solution

create table #or (id int, qty int, rn int)
create clustered index ix_orid on #or(rn)
insert into #or
select *, ROW_NUMBER() over(order by id) as rn from dbo.orders;

with position as (
select o1.*, o1.qty as s from #or o1 where rn = 1
union all
select o1.id, o1.qty, o1.rn, o1.qty + position.s
from #or o1
join position
on o1.rn = position.rn + 1
where position.s > 0
)
select * from position
option (maxrecursion 0)

drop table #or

My concern is that I create big temporary table, for all orders. And all time goes into creating this table. I have about ~3.000.000 rows in dbo.orders and ~15.000 is the result set. My current idea is to limit insert into #or using top hint with reasonable amount, and run it several times until we are done. But I wonder if it can be done easier?

As suggested in comments, I can get the latest id of the order in a single position by watching running total goes to zero. Then I can just select orders in range

declare @latestId int;
WITH T1
     AS (SELECT *,
                SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS total
         FROM   [dbo].[orders])
SELECT top (1) @latestId = id
FROM   T1
WHERE  total <= 0
order by id

select * from [dbo].[orders] where 1 <= id and id <= @latestId


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed As the title states, I'm getting the following error when attempting to use a ASP.NET webapp:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.x.x]

This is from the "Application" log on the SQL server. I currently have a clustered setup (using Microsoft Failover Cluster) although the second node has not been configured yet. I've tried configuring SPNs:

setspn -L myserviceaccount

Registered ServicePrincipalNames for <DN>:
        MSSQLSvc/*SQL.corp.*.local:1433
        MSSQLSvc/*SQL.corp.*.local

And indeed, SQL Server does register it correctly as per its logs:

Message
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/my.sql.fqdn ] for the SQL Server service.

However, the query SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; returns NTLM and not Kerberos. I've restarted the server a few times to no avail. SQL Server runs under a domain user account with the appropriate delegation privileges.

Am I missing something?

UPDATE:

I was successfully able to get the SQL Server to use "Kerberos" as per the above query, but I'm still having no luck getting the IIS level to work.

This is known as the "double hop" issue between SQL Server and IIS. It looks like you may not have trusted the IIS server for delegation.

Here's a good checklist of things to do when setting up SQL Server with IIS using Kerberos.

Here's a great primer on the condition. The anonymous login is a give-away of the problem.


Table Value Constructor as Procedure Parameter I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined.

Suppose I have a procedure defined something like:

create type info as table(stuff int, more text);

create procedure test(@id int,@data info) as
begin
    -- etc
end;

I would like to execute the procedure using a table literal instead of assigning values into a variable. I would like to be able to do something like this:

execute test 42,(values((1,'one'),(2,'deux'),(3,'drei')) as (stuff,more));

It doesn’t seem to work.

I know I can create a variable and do it that way, and that is the method generally used in examples.

Is there a way to use a a syntax similar to the above? If so, how?

I want to this partly for the exercise, but more importantly, it seems to me that since table literals are part of the language, it should be possible to use them wherever tables are required.

In this regard I think there are gaps in the implementation. Finally, I think that using a literal is sometimes simpler and more intuitive, so creating a variable just to pass information through is a kludge.

No, you must create a table variable, insert the records into that table variable, then pass the table variable to the procedure.

The way that people used to do what you are describing is with pipe delimited text strings. For example, passing '1|Tyler|Smith|2|Jack|Blade|3|Someother|Guy' and then having the stored procedure unpack it. But that methodology has completely fallen out of favor compared to using table valued parameters.


Postgresql script doesn't work as I expect DO $$
DECLARE companyNrs text[] = ARRAY(SELECT LPAD(companynr::text, 5, '0') FROM s_users);
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    raise notice 'notice message %', companyNr;
  END LOOP;
END; $$

The postgresql script above works perfectly on DataGrip. But if I add a DECLARE statement into the loop(as below), it gives multiple syntax errors. What is the rule here ?

Where did you find such syntax in the documentation?
You need declare variables in the same block.
DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
        v_sql text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    v_sql := format('foo bar %L', companyNr);
    raise notice 'notice message %', v_sql;
  END LOOP;
END; $$;

Or it is possible to declare another sub-block:
DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    --
    -- Create a subblock
    --
    DECLARE
        v_sql text;
    BEGIN
        v_sql := format('foo bar %L', companyNr);
        raise notice 'notice message %', v_sql;
    END;
  END LOOP;
END; $$;

Subblocks with EXCEPTION clause are implicit savepoints and have a performance impact. Not sure about subblocks without EXCEPTION clause. They may not be cheap syntactic sugar either.


SSRS : Bug using SSL (https://) We have an SSRS 2014 already in production and we want now use a secure site by linking it to a certificate (URL like "https://..."). So now the URL is fully qualified domain name(FQDN).

We can access correctly to the report manager with this https URL. We have to register to access with the Windows account.

The bug we have is that when we navigate, sometimes, we pass to an http page. Each time that we pass from an https to an http page, the browser (IE) ask to register again.

For example, here we are on a https page. When I click right on a report -> manage:

I arrive on this page:

If I clic on a tab on the left pane (for example in Security), SSRS ask me to reconnect and jump to an http page. This is a real problem for us.

Any suggestions?

Well, it was difficult to solve, but with the help of our sysadmin and reading through lot of places, we managed to get it working. It was a mix of things, but the 2 main ones were changing some parameters on SSRS config file:

Change <Add Key="SecureConnectionLevel" Value="0"/> to <Add Key="SecureConnectionLevel" Value="3"/>
Make sure that <RSWindowsNTLM/> is the only authentication type specified
on <AuthenticationTypes>
make sure all url's are absolute, without wildcards

And second part was to modify a windows registry and restart SSRS afterwards:

Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

Create a new DWORD registry key DisableLoopbackCheck and set value to 1.

In case you are wondering, here are all the references consulted:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2d216af4-6586-4619-b8aa-56f05cbd8239/the-request-failed-with-http-status-401-unauthorized-in-ssrs?forum=sqlreportingservices
https://connect.microsoft.com/SQLServer/feedback/details/323214/the-request-failed-with-http-status-401-unauthorized
https://social.technet.microsoft.com/Forums/en-US/27ecdb2d-d6be-4e10-a7e2-7fedba1266e2/url-problems-with-sql-server-reporting-services-2012-with-wildcard-ssl-certificate?forum=sqlreportingservices
https://www.sqlservercentral.com/Forums/Topic908027-146-1.aspx
http://sqlmag.com/sql-server-reporting-services/implement-kerberos-delegation-ssrs
https://msdn.microsoft.com/en-us/library/cc281382.aspx


Allow access for localhost and named server I'm trying to set up a user to allow them access from localhost and from a specific named server. Is this possible in MySQL and if it is how do I do it?

I know I can use % under the host to allow access from any server, but I'm looking to limit the access to let's say localhost and testserver1.

You could use this :

GRANT ALL PRIVILEGES ON *.* TO 'username'@'ip_testserver1', 'username'@'localhost' IDENTIFIED BY 'password';

Of course, feel free to replace the ALL PRIVILEGES by the rights you want to allow to your user, and the *.* with the database name & tables name.


How to manually invalidate a pull merge replication snapshot from the publisher XY Problem background info:

I have a pull replication publisher in which I want to add or alter a index and have those changes be applied to the subscriber.

The solution to that problem I would like to do is to generate a new snapshot and re-init the subscription.

My Question:

How do I, from the publisher, mark a pull merge replication publication as having a invalid snapshot such that if I did sp_helpmergepublication the snapshot_ready column would return 0.

Doing exec sp_reinitmergesubscription @upload_first = 'true' will cause the subscriber to re-initialize but it does not mark the snapshot as invalid.

I know I could change a publication or article property then change it back and cause to invalidation to happen that way but I would really like to invalidate as the "primary action" instead of having the invalidation be a side effect of some other action.

I am looking for something similar to transactional replication's sp_reinitsubscription procedure which has a @invalidate_snapshot parameter, but I could not find the equivalent for merge replication.

Is there any way to invalidate a merge replication snapshot only without making some other kind of change that has snapshot invalidation as a side effect?

I looked in to the declaration of sp_changemergepublication and found out the way it invalidates the snapshot is a simple query to update the table sysmergepublications

if (@property like 'ftp%' OR @property in ('sync_mode', 'snapshot_in_defaultfolder',
        'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot','keep_partition_changes',
        'use_partition_groups', 'publication_compatibility_level'))
and @snapshot_ready = 1
begin
    if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
    begin
        raiserror(20607, 16, -1)
        goto UNDO
    end
    update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
    if @@ERROR<>0   GOTO UNDO
end

So if you want to invalidate all subscriptions and snapshots just run these two lines.

update dbo.sysmergepublications set snapshot_ready = 2 where snapshot_ready = 1
exec sp_reinitmergesubscription @upload_first = 'true'


How to store data in look-up tables when some of it may be semantically different, but logically the same? I am working on a system that receives communications from various laboratories. Each laboratory has two separate, independently operated data services which means that Laboratory A and Laboratory B could send the same data to the system, but they may send it in different formats (if they use different systems). I'm trying to figure out the best way to handle this. Here's an example:

The system I am working on receives patient appointment status information, specimen collection information and final laboratory test results.

When Laboratory A sends appointment status information or specimen collection information, it comes from one system. Part of the information they send might be XML such as:

<CollectionStatus>INPRC</CollectionStatus>
<ReasonForTest>PRE</ReasonForTest>
<IdSampleType>UR</IdSampleType>

This indicates to our system that the Urine (UR) specimen collection is in process (INPRC) and the reason for that test is "Pre-Employment" (PRE).

Later on when Laboratory A sends the final laboratory test results for the same test they may send XML such as:

<CollectionStatus>OK</CollectionStatus>
<ReasonForTest>PRE-EMPLOYMENT</ReasonForTest>
<IdSampleType>R</IdSampleType>

This indicates to our system that the Urine (R) specimen was collected successfully (OK) and that the reason for the test is "Pre-Employment" (PRE-EMPLOYMENT).

The same lab may send the same information multiple times, but because they come from different systems at the same laboratory, the data sent may be different.

The same applies to different laboratories. Laboratory B may send:

<CollectionStatus>2</CollectionStatus>
<ReasonForTest>PE</ReasonForTest>
<IdSampleType>U</IdSampleType>

and

<CollectionStatus>1</CollectionStatus>
<ReasonForTest>1</ReasonForTest>
<IdSampleType>UR</IdSampleType>

To handle this, right now I have three tables for each differing variable. Using ReasonForTest as an example I would have these tables:

The table "TestReasons" holds data that is standard for our system. Such as (for example):

| TestReasonID | Reason                         |
-------------------------------------------------
|            1 | Pre-Employment                 |
|            2 | Post-Accident                  |
|            3 | Return to Duty                 |
|            4 | Promotion                      |

The other tables, "Results.TestReasons" and "Orders.TestReasons" are simply there to allow the system to convert the data it receives from laboratories into the data our system understands and needs to operate and if necessary, convert it back. The "Name" field holds text that is "UX friendly" and is typically shown on reports or our system's front facing UX. The "Value" field holds the data that the laboratory identified in the "LaboratoryID" field would send (such as "PRE" or "PRE-EMPLOYMENT"). Since not all laboratories may test for the same reasons (the above examples are some typical drug testing reasons), it's possible that the "Results.TestReasons" and "Orders.TestReasons" may not have a record that matches every entry in the "TestReasons" table.

Since there are (at the moment at least) 13 different data types that may be sent at any given time, that ends up being 39 different tables to simply convert from one data type to another. I don't know why, but this just sits funny with me and I feel like there's probably a better way to do this.

Am I missing something or does this seem like the best way to achieve this?

Am I missing something or does this seem like the best way to achieve
  this?

Item 1

You seem to be missing the part of the contract with the laboratories that state "you will send us data in this format.".  That document should clearly list the valid values for TestReason (even if it is their list of values).

Design Idea 1

  receives patient appointment status information, specimen collection
  information and final laboratory test results.

I'm pretty sure there are ISO/Government standards for accomplishing this.  CDISC comes to mind.

I would look at various Government web sites to see if they have a standardized lists of Sample Types.  The Open Data StackExchange might be able to help you out (or even state that this does not exist).

Design Idea 2

Natural Keys.

In both your tables (Order.TestReasons and Results.TestReasons) should have a Unique Constraint on (LaboratoryId, Value).  These can be your PK.  You may want to investigate why they are two separate set of values.

Design Idea 3

You should already have a TestReasons dimension table (and a table for each of the other 12 dimensions).  That would narrow your requirement down to 26 tables.  Then, you just need to reduce the two tables into one.  (eg add a "Domain" column that indicates "Orders" vs "Results")


Move transactional replication subscriber to another server without re-snapshotting I have publisher and subscriber at same server. I need to move subscriber's DB to another server but without creating new snapshot. I've tried this steps:

Stop all agents
Backup subscriber's DB
Restore subscriber's DB on
another server with KEEP_REPLICATION
Changed server name in
syssubscriptions, MSsubscriber_info, MSsubscriber_schedule
Changed
server name in scheduled task for replication

Then I've started agents and got error 20053 (Server not registered). I fixed it with adding linked server to new server

After that I've got an error 20044 (Subscription is outdated or not present), but subscription is present.

What did I do wrong or maybe I have missed any step(s)?

I can't use initialization from backup, because subscriber has some additional tables, views, routines.
Thanks

My answer will assume you are doing this during downtime.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

Continue running the distribution agent jobs till undelivered
command count is zero (This is key that you do not have any undelivered command).
Stop the distribution agent job/jobs.
Script out replication.
Remove replication (publisher and subscriber only) but keep the
distributor as is.  That is my preference but you can also decide to remove subscriber only.
Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


Convert or open Oracle 7.3.x database in Oracle 11 I have a very old Oracle 7.x database that I need to open or convert to a new Oracle 11g.
Here's the content of the old database:

control01.ctl
control02.ctl
control03.ctl
findata01.ora
finind01.ora
gstdata01.ora
rbs01.dbf
system01.dbf
system02.dbf
system03.dbf
temp01.dbf
temp02.dbf
tools01.dbf
users.dbf

Note: the .ora files are binary (spfile).
When mounting the old database the following error appears:
ORA-00205: error in identifying control file

Ok, first I've tried to create a pfile from spfile:

create pfile='c:\ora\pfile.ora' from spfile='c:\ora...\findata01.ora'

I get "File created" but the pfile is empty.

I've tried to change the control files to:

alter system set control_files='c:\ora...\control01.ctl,c:\ora...\control02.ctl,c:\ora...\control03.ctl' scope=spfile;

But then I get the error message:

ORA-32001: write to SPFILE requested but no SPFILE is in use

Anyone know what else to try? I'm running out of ideas.

Maybe the database format has changed and it's not possible to open a 7.x database from inside 11.x? Or maybe there's a third-party tool to convert the database format?

Thanks in advance!

You should open your 7.3 database with Oracle server version 7.3. Than you need to export data using legacy exp command and import generated file into Oracle 11g using imp.
That would be safest path.

If I remember correctly you can not migrate database directly from 7.3 to 11g version.

EDIT:
As mentioned in comment, there are more ways to to get to 11g version. Here is upgrade guide http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm


SQL Server Merge Replication Change Partition Definition I am using Merge Replication on a few SQL Server 2008 R2 servers.  There is one server, and the rest are clients.

I have each of the clients on a different partition using row filters with the HOST_NAME() function.

This works as expected, but I can't seem to figure out how to change this value after creating the subscription.

I need to change this value for all of the subscriptions and I would like to avoid dropping and recreating them.

How can I change the HOST_NAME() value for a subscriber without dropping/recreating?

I found a solution!

Go to the merge agent that handles the subscriber that you would like to change.
Right click -> Properties
Go to the Steps page and edit the Run agent step
Add or Alter the HostName parameter like so:  -HostName [<Your Value Here>]
Stop & Start the agent job
Reinitialize the subscription.

That should do it!


Columnstore index for Key/Value Pair table on SQL Server I need to build a database for storing assessments.

Each assessment can have an indefinite number of questions, and an indefinite number of responses.

Responses for each assessment can grow up to 500k.

Questions for each assessment can be from 10 to 200.

While the Assessments and AssessmentResponses tables will be designed with the "normal" relational table, Questions and Answers should be stored as Key/Value pair.

Assessments
|AssessmentID|Name|JsonSchema|

Questions
|QuestionID|AssessmentID|QuestionValue|QuestionType|

AssessmentResponses
|ResponseID|AssessmentID|RespondentName|Date|JsonResponse|

Answers
|ResponseID|QuestionID|AnswerValueText|AnswerValueDecimal|

As you can see I store also the assessment schema and the responses in JSON format as I found it useful to quickly visualize on a web UI. JsonResponse, in particular, contains all the answers given in the related response, like the following:

{
  "interviewDate":"2001/12/28",
  "city":"Mombasa",
  "phone":"123456789",
  "name":"Marco",
  "age":16
}

Typical queries can be:

Extract all the answers (paged) of a particular AssesmentID.
Calculate the average on all the answers of a particular
    AssessmentID of the age key.
Extract all the responses where the key "age" has a value greater
than 25 and the key "city" is equal to "New York"

Please note that for query No.2, the age values will be stored in the column AnswerValueDecimal, where I will store all the numeric values.

I'm wondering if the column store index will increase the performance in this structure?

Please note that I know that an ElasticSearch instance will help me a lot but for a budget issue we can't implement at this stage.

Some more details about data

Answers to the questions in the assessments are generally chosen from a limited number of choices. In example:

Are you married? > [YES,NO]
Type of building? > [Concrete,Skeleton,Eternite Sheets,Iron Sheets,Other]
City Area? > [North,South,East,West,center]

The queries can be built by the end-user depending on the information they need from a particular assessment. Giving the above example, they want to know how many Eternite buildings are in the North and West area of the city, a count in the end.

But for example there is also a question "How old are you?", they want to filter the previous query to know how many Eternite buildings are in the North and West area where there's a person with an age lower than 25 years...

USING JSON FIELD TO STORE DATA

I've tried using the new feature in SQL Server 2016/2017 to store JSON document inside an NVARCHAR(MAX) field. It seems to work pretty well but maybe only once the whole table is loaded in memory.

Here is the dbfiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2ce135dc37d72f9db951bbb4e2708baa

What better way than to test?

TL:DR up front. The way you structure your data makes it awkward when there are multiple conditions, as you have to check different rows for the same response. Columnstore works better simply for using batch mode in the self join of my test query.

I would expect you to gain far more from structuring your data in a different way than by looking at storage options.

Here's a setup with 5m rows, and an example query at the end looking for average age of people in the city center for a range of responses (assuming one particular survey). Dbfiddle link as well

DROP TABLE IF EXISTS dbo.AnswersRow

CREATE TABLE AnswersRow (
ResponseID INT NOT NULL,
QuestionID INT NOT NULL,
AnswerValueText VARCHAR(20) NULL,
AnswerValueDecimal DECIMAL(15,2) NULL,
CONSTRAINT PK_R_Q PRIMARY KEY CLUSTERED (ResponseID,QuestionID)
)

INSERT dbo.AnswersRow
SELECT TOP 5000000
FLOOR((-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))/5+1) AS ResponseID,
(-1+ROW_NUMBER() OVER(ORDER BY(SELECT 'Joe')))%5+1 AS QuestionID,
NULL AS AnswerValueText,
NULL AS AnswerValueDecimal
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

--5 questions
--1) Married 0 or 1
--2) BuildingType
--3) CityArea
--4) Age
--5) Income

--randomize answers
UPDATE ar
SET AnswerValueText = CASE
WHEN QuestionID = 2 THEN (CASE x2.seed
                            WHEN 0 THEN 'Concrete'
                            WHEN 1 THEN 'Skeleton'
                            WHEN 2 THEN 'Eternite Sheets'
                            WHEN 3 THEN 'Iron Sheets'
                            WHEN 4 THEN 'Other'
                            END)
WHEN QuestionID = 3 THEN (CASE x3.seed
                            WHEN 0 THEN 'North'
                            WHEN 1 THEN 'South'
                            WHEN 2 THEN 'East'
                            WHEN 3 THEN 'West'
                            WHEN 4 THEN 'Center'
                            END)
ELSE AnswerValueText END,
ar.AnswerValueDecimal = CASE
WHEN QuestionID = 1 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*2))
WHEN QuestionID = 4 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*60+17))
WHEN QuestionID = 5 THEN (FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*200000))
ELSE AnswerValueDecimal END
FROM dbo.AnswersRow ar
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x2
CROSS APPLY (SELECT FLOOR(RAND(CONVERT(BINARY(8),NEWID()))*5) AS seed) x3

SELECT *
INTO dbo.AnswersCol
FROM dbo.AnswersRow

CREATE CLUSTERED COLUMNSTORE INDEX CX_Answers ON dbo.AnswersCol
CREATE NONCLUSTERED INDEX IX_Answer_text ON dbo.AnswersRow(AnswerValueText)
CREATE NONCLUSTERED INDEX IX_Answer_decimal ON dbo.AnswersRow(AnswerValueDecimal)
CREATE NONCLUSTERED INDEX IX_Answer_question ON dbo.AnswersRow(QuestionID) INCLUDE(AnswerValueText,AnswerValueDecimal)

--average age of particular survey, city center
SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersRow ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
            FROM dbo.AnswersRow ar2
            WHERE ar1.ResponseID = ar2.ResponseID
            AND ar2.QuestionID = 3
            AND ar2.AnswerValueText = 'Center'
            )

SELECT AVG(AnswerValueDecimal), COUNT(*)
FROM dbo.AnswersCol ar1
WHERE 1=1
AND ar1.ResponseID >= 100000 AND ar1.ResponseID < 200000
AND ar1.QuestionID = 4
AND EXISTS (SELECT 1
            FROM dbo.AnswersCol ar2
            WHERE ar1.ResponseID = ar2.ResponseID
            AND ar2.QuestionID = 3
            AND ar2.AnswerValueText = 'Center'
            )


Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility? We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is really no one who knows what's going on inside the applications. We've been treating them as black boxes.

As a strategy to migrate and test the applications compatibility with postgres 9.3, I'm considering:

Do a pg_dump to have a starting point.
Log all SQL statements.
Replay all the logged SQL statements to the new server from the point were I did the
pg_dump. This will take of synchronization, and...
Check the response of the new server to the statements fed on (3), to see if It can
digest all the SQL statements that will be sent to it. This will take care of "input"
compatibility. Once we're satisfied with the "input" compatibility, and the new
server is up to date, then...
Pick a good sample of the SELECT commands from the logs, replay them to both servers,
capture the output on files, compare the files. This would take care of "output"
compatibility.

This would run over 6 months, to make sure there will be no surprises. If you see any holes in my plan, feel free to point them out :)

The only doubtI have is about the order in which the statements appear in the log file:

Do the statements in the log file appear in the same order as they do in the WAL file?

Thanks

WAL vs statement log ordering

  Do the statements in the log file appear in the same order as they do in the WAL file?

That doesn't really make sense.

Statements do not appear in the WAL at all. Their effects do, in terms of transaction begins/commits/rollbacks, changes to table heap and index blocks, etc, but the actual statement is never recorded in the WAL at all.

Many queries run concurrently on most systems. The write-ahead log contains the effects of these queries mixed together in the order in which they did the work. So the queries are effectively all in the WAL at the same time.

PostgreSQL logs queries in the order they're received for parsing and execution. Because the operating system scheduler controls when each PostgreSQL backend runs this isn't necessarily the exact same order each query begins executing, and it's certainly not the same order each query finishes execution.

About all you can do is set a log_line_prefix that records the transaction IDs associated with statements, then look for those transaction IDs in the corresponding WAL entries. To do that you have to parse the disk block structure to find the xmin or xmax fields on changed tuples; a tool like pg_xlogdump will help you with this.

All in all I don't expect you to gain much from trying to match the WAL against the query log.

Replaying statements to compare results

  Pick a good sample of the SELECT commands from the logs, replay them to both servers, capture the output on files, compare the files. This would take care of "output" compatibility.

This would work if your application only ever uses runs one query at a time, i.e. it only uses one connection.

Almost all real world applications do many concurrent writes. You can't simply replay the query log to replicate the effect of concurrent write operations - especially in read committed transaction isolation, where one transaction's commit can affect the results of other concurrent transactions.

If you're logging transaction boundaries and transaction IDs with an appropriate log_line_prefix you can probably reassemble an interleaved ordering of all individual statements that get run by each session and use a custom tool to replay them in that exact order. It won't be perfect though, as individual statements can be subject to race conditions with each other.

Take the simple example:

INSERT INTO mytable (id, somevalue)
SELECT 1, 'blah'
WHERE NOT EXISTS (SELECT id FROM mytable WHERE id = 1);

If you run this twice at the same time from two different connections the effects can vary. They'll change based on details of exactly when each started, and on operating system scheduler timing decisions. Possible outcomes include:

The first inserts a row and returns INSERT 1, the second inserts no row and returns INSERT 0. Or vice versa.
The first inserts a row and returns INSERT 1. The second attempts to insert the same row, because the SELECT did not find any row with id=1, and fails with a duplicate key error.

So as you can see, it's just not possible to replay the exact state of one database onto another using just the query log. That's before considering things like the effect of rollbacks on sequences, volatile functions, functions using timestamps, etc, too.

Limitations of comparing input and output

Some app bugs won't be detected by the approach you describe.

For example, it's entirely normal for rows to be inserted in different orders into a table based on small details of timing, differences in plan choices, etc. A correct application won't mind this, because it either doesn't care about the row order, or or specifies a specific order by clause when it does care. However, in reality some apps try to treat tables as if they have some kind of innate order and suddenly get very upset if the table is not "in order". To PostgreSQL the table is just a heap with no innate order at all, it just happened to be returning the rows in the order the app was expecting because that was fastest. So if your app is relying on behaviour like this you won't notice the problem with either of your proposed tests.

If you attempt to compare results of replay without sorting the results then everything will appear to mismatch because of those minor timing and plan differences. If you do sort the results though, you might not notice cases where the app's making unsafe assumptions about ordering that it gets away with on 8.1 and not on 9.3.

Another case is the change of bytea_output default format. Your app's inserts will produce the same results, but selecting the data will return it in a different format now. If your app doesn't understand \xDEADBEEF hex format bytea it might try to interpret that as an octal bytea . You'd only notice the problem if you went to save it back to the DB and it got mangled, or if you got an error in the app as a result of the misinterpreted bytea data.

So how do I check compatibility?

If your application doesn't have a good unit and integration testing framework, now is the time to add one.

Read the release notes for each major PostgreSQL release and look for specific compatibility challenges in your queries, like missing from clauses, implicit casts to/from text, etc.

Manually test every part of the app you can against a dump of the old DB (taken with 9.3's pg_dump) that's applied to a test copy of the new DB.

You should also simply review your queries to look for likely issues. Some things, like a LIMIT clause without a corresponding ORDER BY, are pre-existing bugs in the app code that it might've got away with before, and might still get away with in 9.3 ... or might not. PostgreSQL tries to detect obvious bugs, but there are legitimate cases where you might use a LIMIT without an ORDER BY, so in this case and many related ones it can't warn you in case that's really what you intended.

The biggest problems you're going to face are going to be:

Switch to standard_conforming_strings = on changing handling of escapes in strings
Removal of implicit from clause causing previously valid-looking but nonstandard and potentially unsafe queries to fail;
Removal of implicit casts to text causing previously valid-looking (but probably buggy) queries to fail;
Change of default bytea_output (if you use bytea fields)


Load data - fields terminated by character appearing within a field statement I have an issue with loading data from a csv file. The question is, without preprocessing, is it possible to have an exception in the following code

load data local infile 'Program.csv' into table program_table
    fields terminated by ','
    LINES TERMINATED BY '\r\n'
    ignore 1 lines
    (@program_code, @field_of_study, @area_of_study, @degree_level)
    SET
        program_code = nullif(@program_code,''),
        field_of_study = nullif(@field_of_study,''),
        area_of_study = nullif(@area_of_study,''),
        degree_level = nullif(@degree_level,'');

that ignores the "terminate by" character when it's followed by a (space). The problem I've encountered is within the csv file there are lines like the following

ZLD6,Administration/Business,BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING,Doctorate.

The BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING should be one field, but the field termination character ','  separates int into two. That is the fields would be ZLD6, Administration/Business, BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING, and Doctorate (5 fields instead of 4).

Any suggestions on possible solutions would be great.

To answer your question yes I'm sure it is possible to employ a scheme like jkavalik suggested where you:

Import the entire file into a single column/cell in a temp table
Parse the malformed csv string using increasingly obtuse trickery as each edge case is discovered.

I'm sure a smarter person than me could write an event driven, real-time, web-enabled, protocol buffer communicating, graphical user interface, autonomous aircraft landing system for use in hurricanes using only SQL, but why would they?  You should use the best tool for the job.  If you can't correct the csv exporter to properly escape the data then you should properly escape the data manually or use a programming language that will make this task easier.

In my opinion, SQL is only going to make your life more difficult, and as such you should choose a different tool.


Efficient way to move rows across the tables? This is somewhat long question as I would like to explain all details of the problem.

System Description

We have a queue of incoming messages from external system(s).
Messages are immediately stored in the e.g. INBOX table. Few thread workers
fetch the job chunk from the table (first mark some messages with UPDATE,
then SELECT marked messages). Workers do not process the messages,
they dispatch them to different internal components (called 'processors'),
depending on message command.

Each message contains several text fields (longest is like 200 varchars),
few ids and some timestamp(s) etc; 10-15 columns total.

Each internal component (i.e. processor) that process messages works differently.
Some process the message immediately, others triggers some long operation,
even communicating via HTTP with other parts of the system. In other
words, we can not just process message from the INBOX and then remove it.
We must work with that message for a while (async task).

Still, there are not too many processors in the system, up to 10.

Messages are all internal, i.e. it is not important for user to browse
them, paginate etc. User may require list of processed relevant messages,
but that's not mission-critical feature, so it does not have to be fast.
Some invalid message may be deleted sometimes.

Its important to emphasize that expected traffic might be quite high - and we don't
want bottlenecks because of bad database design. Database is MySql.

Decision

The one of the decisions is not to have one big table for all messages, with some flags column that will indicate various messages states. Idea is to have tables per
processors; and to move messages around. For example, received messages will be stored in INBOX, then moved by dispatcher to some e.g. PROCESSOR_1 table, and finally moved to ARCHIVE table. There should not be more then 2 such movements. W

While in processing state, we do allow to use flags for indicating processing-specific states, if any. In other words, PROCESSOR_X table may track the state of the messages; since the number of currently processing messages will be significantly smaller.

The reason for this is not to use one BIG table for everything.

Question

Since we are moving messages around, I wonder how expensive this is with high volumes. Which of the following scenarios is better:

(A) to have all separate similar tables, like explained, and move complete messages rows, e.g. read complete row from INBOX, write to PROCESSOR table (with some additional columns), delete from INBOX.

or

(B) to prevent physical movement of the content, how about to have one big MESSAGES table that just stores the content (and still not the state). We would still have other tables, as explained above, but they would contain just IDs to messages and additional columns. So now, when message is about to move, we physically move much less data - just IDs. The rest of the message remains in the MESSAGE table unmodified all the time.

In other words, is there a penalty in sql join between one smaller and one huge table?

Thank you for your patience, hope I was clear enough.

I think its faster (feel free to test) to keep the record in the same table while processing, but be sure to set an index on the column(s) that track which processor is using them.

Also use MyISAM tables if you don't require InnoDB's features. The ARCHIVE table can best be set to the archive type, so it does not make space for updates (if you don't need to write to it)


Mysql locking - How to acquire a lock in the DB for each userId Hi I have a J2EE Server(clustered) that executes requests from multiple users.

I would like that each request of a given user will be handled like so:

{
  acquireLockForUser(userId); // i.e. insert a lock row for a dedicated table
  doSomeStuffInDbForUser(user);
  releaseLock(userId);  // i.e. delete a lock row from the dedicated table
}

This will make sure that each user request will be handled by a single thread of my servers.

Whats the way to achieve that? (Is it possible to insert into a table 'userLock' by id and lock it? how to insert\fetch from this "lock" table?)

GET_LOCK('rick');
...
RELEASE_LOCK('rick');

During ..., any other thread trying to do GET_LOCK('rick') would be blocked.

Will that work for your application?

Be aware of the caveats:  A disconnect (eg, network glitch) will release the lock.  Multiple locks need some care.  Etc.