SQL Server FILELISTONLY command providing incorrect logical database name When using RESTORE FILELISTONLY to restore a database using code the logical_name for the files is coming back as 423.

However when I do a manual restore of the database the logical file name appears to be 3497.

I'm running SQL Server 2008 R2.  Has anyone run into anything like this before?  How do I repair this?

So what it sounds like is happening is:

You create a database called foo with a logical data file name of foo.
You back up that database.
You restore that database as a different database (say, bar).
You backup the bar database.
You try to restore the bar backup as yet another database name, and your code assumes that the logical file name for the data file for the database bar should be bar. But it's not, it's still foo!

You could also interject some random ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); in there to make it even more confusing for you (never mind your code). Particularly if - like it turns out is true in your case - you are storing multiple backups in a single backup file. This can be bad regardless of whether it is the same database but the logical file names change over time, or different databases being backed up to the same file. You need to determine which file number you're actually looking for, and specify that in FILELISTONLY's WITH FILE = option. Better yet, stop reusing the same file for multiple backups.

You should be sure that when you restore a new database based on the backup of another, you use WITH MOVE to give the logical file names meaningful physical paths, and then use MODIFY FILE immediately afterward to make the logical names themselves meaningful.

To help figure out where the actual discrepancy is coming for a specific case you are experiencing (I couldn't reproduce a case where RESTORE FILELISTONLY showed different values than the restore UI in SSMS), you could provide a .bak file on some file sharing service, and we could look ourselves. Your description of what the file names are and what they should be is extremely hard to follow.


Why will my ALTER and UPDATE not work in the same query in this specific instance? I have a problem with a query where i am trying to ALTER a table by adding 3 columns to it, then updating each column with 1 specific piece of data.

I am aware there are several posts like this but every answer for them has not helped my specific situation e.g. "IF NOT EXISTS" or "COL_LENGTH" or adding "GO" in between the ALTER and UPDATE etc.

I still keep getting "Invalid Column Name" errors. Please see my code below:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

ALTER TABLE [dbo].[app_table1] ADD [Bonus] int
ALTER TABLE [dbo].[app_table1] ADD [Allow] bit
ALTER TABLE [dbo].[app_table1] ADD [Discount] int

ALTER TABLE [dbo].[app_table2] ADD [Bonus] int
ALTER TABLE [dbo].[app_table2] ADD [Allow] bit
ALTER TABLE [dbo].[app_table2] ADD [Discount] int

Update [dbo].[app_table1]
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Update [dbo].[app_table2]
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Would anyone happen to have a great revelation to my increasingly frustrating problem? :)

Many thanks in advance,

Do you have the option to create the new columns outside of this stored procedure via change control?

Alternatively, you could use default constraints if your business requirements allow the columns to be not null:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table1_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NOT NULL CONSTRAINT df_app_table1_Discount DEFAULT (100);

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NOT NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999);
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NOT NULL CONSTRAINT df_app_table2_Allow DEFAULT (1);
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NOT NULL CONSTRAINT df_app_table2_Discount DEFAULT (100);
GO

If your columns must be nullable, then use the WITH VALUES clause:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

    ALTER TABLE [dbo].[app_table1] ADD [Bonus] int NULL CONSTRAINT df_app_table1_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Allow] bit NULL CONSTRAINT df_app_table1_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table1] ADD [Discount] int NULL CONSTRAINT df_app_table1_Discount DEFAULT (100) WITH VALUES;

    ALTER TABLE [dbo].[app_table2] ADD [Bonus] int NULL CONSTRAINT df_app_table2_Bonus DEFAULT (999) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Allow] bit NULL CONSTRAINT df_app_table2_Allow DEFAULT (1) WITH VALUES;
    ALTER TABLE [dbo].[app_table2] ADD [Discount] int NULL CONSTRAINT df_app_table2_Discount DEFAULT (100) WITH VALUES;
GO

Lastly, you could use sp_executesql to perform the updates. You are not introducing a SQL injection risk as the update values are not parameterised.


Search and Replace in multiple MySQL databases I have multiple MySQL databases(50+) and I want to replace a string in all of them, is there anyway to search and replace in all the databases?

Server is CentOS and I have SSH root access.

Not really.

Instead, you could do something like

SELECT CONCAT("UPDATE ", table_schema, ".", table_name,
                " SET foo = replace(foo, 'this', 'that');")
    FROM information_schema.tables
    WHERE ...

to generate the desired 50+ UPDATE statements.  Then copy and paste them into the mysql commandline tool.

(Or you could construct a shell script using that.  Or...)


Does Oracle cache queries when using a database link Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized.

I can't find any information confirming/infirming this.

Naively I would not see why Oracle would not be able to do query caching when using a db link.

There are some restrictions with database links. You can't execute DDL remotely for example.

Queries run against remote tables are not processed in exactly the same way as regular tables:

  The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

In most cases, the breaking process is adequate and you will just get the expected overhead of the database link (network delay between the databases, two query analyses/processing instead of one, etc..). The remote subqueries will be treated as normal queries by the remote database and the data will get into the remote cache as with regular queries.

You might get into performance problems if you attempt to join a local table to a remote table (or tables from two distinct db links) since the data has to be carried to a single database to perform the join. In that case, you might want to change the breaking up process (for example performing a complex join in a remote database instead of the local database).

The documentation details a number of ways you can tune distributed queries.


Different execution with same query In a migration plan I am taking a mysql 5.5 database with only MyISAM tables and converted it into mysql 5.7 with InnoDB tables.

The create table statements are here: http://sqlfiddle.com/#!9/bb815d.
They are exactly the same except for the engine:

CREATE TABLE `pageview` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `VisitId` int(11) DEFAULT '0',
  `PageId` int(11) DEFAULT '0',
  `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `VisitId` (`VisitId`),
  KEY `PageId` (`PageId`),
  KEY `All` (`PageId`,`ViewDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3245799556 DEFAULT CHARSET=utf8;

CREATE TABLE `page` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SiteId` int(11) DEFAULT '0',
  `Url` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `Name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `Rating` tinyint(1) NOT NULL DEFAULT '0',
  `RawUrl` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `Url` (`Url`,`SiteId`),
  KEY `SiteId` (`SiteId`)
) ENGINE=InnoDB AUTO_INCREMENT=105689724 DEFAULT CHARSET=utf8;

When running this query the results are very different and performance is much worse in 5.7 with InnoDB that in 5.5 with MyISAM:

SELECT VisitId,SUM(page.Rating) AS Rating
FROM pageview
INNER JOIN page ON page.Id = pageview.PageId
GROUP BY VisitId
ORDER BY VisitId DESC
LIMIT 500000;

The explain from 5.5 MyISAM is this:

The explain from 5.7 InnoDB is this:

I don't know what can I do to improve performance in 5.7, or why has performance degraded so much.

The first thing that I notice is that the order of the tables is backwards, but also for the page table its not using the PRIMARY index as it was in 5.5 and I think this is the key difference.

Can you please suggest ways to improve this situation?

Update

After following the suggestion by mendosi I still do not get any improvement, here is the updated SHOW CREATE TABLE:

CREATE TABLE `pageview` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `VisitId` int(11) DEFAULT '0',
  `PageId` int(11) DEFAULT '0',
  `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `PageId` (`PageId`),
  KEY `All` (`PageId`,`ViewDate`) USING BTREE,
  KEY `VisitId` (`VisitId`,`PageId`)
) ENGINE=InnoDB AUTO_INCREMENT=3268418928 DEFAULT CHARSET=utf8

And the explain:

It is difficult to say why MySQL Optimizer is choosing a different join order for 5.7 without seeing the optimizer trace for the query.  However, you can force the same join order as 5.5 by using STRAIGHT_JOIN instead of INNER JOIN.  That will tell MySQL to process the tables in the order they are listed.


MySQL - Ordering 'featured' items first I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table.

Currently the results list all of the events by date. There is a column in the eventlisting table that is called featured and has values 1 (featured) or 0 (non-featured). I want the exact same results except that all featured items for that day will list before the non-featured items, for each day. Thank you for any suggestions.

SELECT a.*, c.id as catid, c.cat_name FROM eventlisting a
LEFT JOIN categories c ON a.cat_num=c.id
WHERE a.startDate >= "2015-08-21"
AND a.endDate <= "2016-08-21"
AND a.status=1
GROUP BY a.id
ORDER BY a.endDate
ASC LIMIT 0, 50

If this is my table data:

id featured status  startDate    endDate
_________________________________________
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
3      1      1    2015-08-21  2015-08-25
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
6      0      1    2015-08-22  2015-08-31
7      1      1    2015-08-22  2015-08-31

Then I need the results to show this order:

id featured status  startDate    endDate
_________________________________________
3      1      1    2015-08-21  2015-08-25
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
7      1      1    2015-08-22  2015-08-31
6      0      1    2015-08-22  2015-08-31

All you need to do is ORDER BY startDate ASC first, then featured DESC:

SELECT
    a.* --,
    -- c.id as catid,
    -- c.cat_name
FROM eventlisting a
-- LEFT JOIN categories c ON a.cat_num=c.id
WHERE a.startDate >= '2015-08-21'
AND a.endDate <= '2016-08-21'
AND a.status=1
GROUP BY a.id
ORDER BY a.startDate ASC,
         a.featured DESC
LIMIT 0, 50;

Note: I've commented the categories table because you didn't specified which is its structure.

Test:

mysql> SELECT  a.*            FROM eventlisting a   WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21'  AND a.status=1  GROUP BY a.id  ORDER BY a.startDate ASC,  a.featured DESC LIMIT 0, 50;
+----+----------+--------+------------+------------+
| id | featured | status | startDate  | endDate    |
+----+----------+--------+------------+------------+
|  3 |        1 |      1 | 2015-08-21 | 2015-08-25 |
|  1 |        0 |      1 | 2015-08-21 | 2015-08-21 |
|  2 |        0 |      1 | 2015-08-21 | 2015-08-22 |
|  5 |        1 |      1 | 2015-08-22 | 2015-08-27 |
|  7 |        1 |      1 | 2015-08-22 | 2015-08-31 |
|  6 |        0 |      1 | 2015-08-22 | 2015-08-31 |
+----+----------+--------+------------+------------+
6 rows in set (0.00 sec)

mysql>

Test it in this Fiddle.


Is it possible to use MAX with a condition? I have a jobs table that has two relevant fields date_executed and user_id. I was wondering if there's a way I can avoid joins or inline sql and just calculate both the last executed time, and the last executed time for a specific user in one simple query. I want to bee able to use MAX on everything, and a MAX on a subset of the results.

Example mock-up query. Line 3 has made-up syntax that's hopefully self-explanatory for what I want to do:

SELECT
    MAX(date_executed) as last_executed,
    MAX(date_executed, user_id = 'current-user-id') as last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

(I'm a bit rusty with PostgreSQL, but I believe the query below is ISO-compliant and so should work):

SELECT
    MAX( date_executed) as last_executed,
    MAX( CASE WHEN user_id = $userId THEN date_executed END ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

$userId is a parameter you'll specify in your PostgreSQL client library.
A CASE WHEN x THEN y END expression without an ELSE case will evaluate to NULL when x is false or Unknown.
The MAX aggregate function only considers non-NULL values, therefore it only considers values in jobs.date_executed when jobs.user_id = $userId.

You can also use the FILTER clause to get the same result (thanks for the tip, ypercubeᵀᴹ!):

SELECT
    MAX( date_executed) AS last_executed,
    MAX( date_executed ) FILTER ( WHERE user_id = $userId ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

Both queries will return last_executed_by_user = NULL if there are no rows where user_id = $userId.


What is branching factor of mysql myisam's b-tree I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node).
I also would like to know how the char/varchar keys are stored - are they stored in full length or just first "meaningful" characters.

By "meaningful" characters I mean:
create table a(
   name varchar(1000) primary key
);

insert into a('london');

"london" key is 1000 characters long (assuming ASCII), but the key
itself is just 6 characters (7 with null character, if we represent it as C-string).

There is about 8 bytes of overhead per index entry.  So, if the key is typically 6 bytes, and the key_buffer block size is 1KB, and the BTree is 69% full:
1024 * .69 / (8+6)

says there will be about 50 rows.
So, 3 levels of BTree can handle up to 50^3 = 125K rows.
The .69 comes from assuming lots of random inserts -- leading to each block oscillating between 100% full just before a block split and 50% after.
There may be an obscure SELECT against the information_schema to get the exact value for the indexes.  (I know of one for InnoDB.)


How do I place leading zeros for numbers less than 10 without affecting those 10+? Probably a newb question, but...

I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original number if it is 10 or greater (i.e. 1=01, 10=10 and 300=300).

SELECT DISTINCT RIGHT('0'+CONVERT(VARCHAR,[FacilityCode]),3) FROM...

This returns 1=01, 10=010 and 300=300 (using the same examples as above)

EDIT: I'm trying to do this within a query so that I can do lookups without altering either table.

Can someone lend a brother a hand here? My brain just ain't working.

The safest way is probably to only add zeroes when the length of the column is 1 character:

UPDATE
   Table
SET
   MyCol = '0' + MyCol
WHERE
   LEN(MyCol) = 1;

This will cover all numbers under 10 and also ignore any that already have a leading 0.

EDIT

To just select the data try:

SELECT
   MyColPadded = CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
FROM
   MyTable;


How to prevent Performance issue & going for Denormalization We are planning to do an e-commerce shopping site with PHP-MYSQL combination.

I read in almost every link related to Normalization & Denormalization : Normalize until it hurts, denormalize until it works.

One thing I understood is Normalization is must for good performance , am i right ?

We have to do Denormalization only when we face problems of Performance [ the very low page speed ] , otherwise Denormalization is not required.

Is for 100% it will hurt - Normalization? can we do anything to prevent [not to hurt.] can we take some steps for performance boost other than Denormalizing & Refactoring complete database again.

Can we do Denormalization without doing Normalization before ?

credits : Vladimir Oselsky
Normalization is never for a performance of reads it is for data integrity and performance of writes. You normalize to avoid redundant data and creating problems by having same data exists in different locations. If you want most amazing performance, you denormalize by throwing everything into a single table and create a lot of indexes. That is why for reporting (DW) it is a practice to denormalize to increase the performance of reads. Save yourself a lot of headache and do a lot of reading on how other people solve same problem.
I strongly believe in 3NF, but going beyond that is absurd in my book, I have yet to see a valid business case for doing anything above that. Sooner or later you will find a need to denormalize for a specific process, usually it is done for reporting, data warehouse need. A good rule is OLTP = Normalize, OLAP = Denormalize.
It is a nature of database design and request from the business. Whenever you are designing a system, you are designing for a specific purpose, and later someone else comes along and request information that you might not have been prepared to give, in that case, you are required to denormalize to provide a best performance.
To increase the performance go for "Indexes, query tuning, hardware upgrades, server configuration and etc"....


How to convert timezone in MySQL using the timezone name? I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is

SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') AS test
FROM appointment

which is working properly. Now the issue I am having is that my database uses the timezone name as America/New_York.

Is there any way to convert timezone using timezone name not from timezone offset?

To answer the question:

  Is there any way to convert timezone using timezone name not from timezone offset

The function CONVERT_TZ has this example and warning:

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
    -> '2004-01-01 13:00:00'

  Note
  To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

And the section on Time Zone support has a 'Populating the Time Zone Tables' section which describes the process of setting up the mysql timezone tables.


Bridging Table Properties I have a question on bridging tables: if I have a bridging table linking two tables (standard) with the only values in the bridging table being the Primary Keys for both tables it is linking, how would I create new records in the bridging table if I wanted to, would this be through simply adding them in to the table directly as long as they existed in both the Parent tables?
Also, under what circumstances can something be deleted from a bridging table (or not be)?
If anyone has any references on where to find more info to read up on this as well, I would appreciate it.
Thanks

You add a row into the bridging table , when you need to connect two rows from the referenced tables.
you can always delete one row in the bridging table, when that connection isn't any longer present.
What you are searching for is Associative entity


xp_cmdshell bcp wont save file EXEC xp_cmdshell 'bcp "SELECT lastconnectip FROM RF_USER.dbo.tbl_UserAccount" queryout "C:\test.txt" -T -c'

The command above runs, the output:

NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
NULL
1158 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (1158000.00 rows per sec.)
NULL

But no file is actually written. what am i missing?  The user does have full rights (sysadmin) SQL 2008 R2.

Also alternative way to use NETSH in sql query/trigger would work aswell instead of the one  above.

For sysadmins, xp_cmdshell runs the command as the SQL database engine service account.

Make sure that account has been granted write permissions on the target location.


Replacing NULL values in LEFT JOIN with MAX function I have created a query that join two tables and returns a date of the last log in of a particular user. If a user logged in multiple times, the query returns the most recent date:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME varchar(255)
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','17.07.17'),
       ('1234','20.07.17'),
       ('1235', '27.08.17')

SELECT T1.NAME, MAX (T2.LOGIN_TIME)
CASE WHEN T2.LOGIN_TIME IS NULL THEN 5 ELSE T2.LOGIN_TIME
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.USERID='XYZ_' + T2.USERID
GROUP BY T1.NAME

http://www.sqlfiddle.com/#!18/c119d/2

What I'm struggling with is to replace "null" values that are returned every time when a user exist in table1 and does not exist in table2.
For that I was trying to write a "CASE" function (line 2 in the link above).

You can use ISNULL(MAX(T2.LOGIN_TIME), 'Default Value') to replace the NULL.

But first you should consider that LOGIN_TIME varchar(255) with values such as 17.07.17 that holds dates should be DATE data type. Always use DATE or DATETIME when using dates, as varchars become troublesome when comparing or ordering (and even understanding them depending on culture info).

For example, the MAX() on a varchar value of 17.07.18 against 27.08.17 will return 27.08.17 because 27 is higher as literal than 17, but the year of the latter (2018) is higher than the former, thus returning an incorrect result. This won't happen if the column is DATE or DATETIME type.

This is the fixed SQL:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME DATE -- It's date here!
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','2017-07-17'),
       ('1234','2017-07-20'),
       ('1235', '2017-08-27') -- ISO format

SELECT
    T1.NAME,
    ISNULL(CONVERT(VARCHAR(200), MAX(T2.LOGIN_TIME)), '5')
FROM
    Table1 T1
    LEFT JOIN Table2 T2 ON T1.USERID ='XYZ_' + T2.USERID
GROUP BY
    T1.NAME

If you are using a default value of a literal '5' then you have no other choice than converting the max login time to varchar so both types are compatible.

Maybe you wanted to display a particular date instead?


I think you should be doing something like this.

Maintain username,password in separate table like 'password_change_history'
Compare the separate table password with  rm_users password for each user

2.1 if the password is not same -  changed - perform the following task.

  2.1.1 - send sms
  2.1.2 - update password_change_history with latest password for that user.

You can even add date_time column for last password change, counter for how many times changed...extra , so the new table will act as extra meta information table.

You also need to check if any new user is added or any user is deleted.


Upgrade SQL Server 2000 database to 2008 R2 and enable new features I have recently upgraded SQL Server 2000 database to 2008 R2.

What I did was:

Shutdown SQL Server 2000 (express) service on old machine,
Move datafiles (mydatabase.mdf and mydatabase.ldf) to new machine,
Run SQL Server Management Studio 2008,
Connect to local database engine,
Attach datafiles to database.
Change the compatibility level of database to SQL 2008 (100).

Question: What else should I do to make migration complete?

I want to:

use new features like checksumming and full recovery model,
make this database to be exactly as it was created in SQL 2008 R2,
make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine.

In other words: I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up.

Sir, I'm really impressed with your answer, I wasn't expecting so much.

So some comments:

The database is now in production. As I said, it was upgraded using deattach-attach method as I desribed in first post and as described on MSDN: http://msdn.microsoft.com/en-us/library/ms189625.aspx
It had to be done quickly, so I was forced to do it that way. Let's forget about how inappropriate could it was and focus on current situation.
The users/persmission is not a problem here - there are only few and permissions are simple.
The application which use database is compatibile with SQL 2000 till 2012 so this is not a problem either.
The database file (MDF) isn't big - only about 1GB.

Few more questions:

You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem.
About checksumming and full recovery model: it wasn't available/enabled on SQL 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking.
I'm prepairing to migrate this database to SQL 2012 - so first it was from SQL 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of SQL 2000 databases in SQL 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right?
Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?
As we were using SQL 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL 2008, isn't this still better than using backup function in Management Studio?
Full recovery mode with frequent Transaction log backups - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly?

I know that my questions may sound silly, I'm not professional database admin, but I am the only person here who can do such "hard core" task like upgrading the database engine. I'm also sure that your knowleadge will help a lot to other people like me.

Thank you very much for your time and knowleadge, I really appreciate this.

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it.

As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a simple scan of the database, such as TSQL embedded in applications.  You can capture traces of TSQL using SQL Profiler on your SQL Server 2000 server during typical hours and analyze these traces using the Upgrade
Advisor.

So the rest of the steps would be :

On the day of migration :

script our logins on 2000 server using sp_help_revlogin.
Script out jobs and linked servers from sql 2000 server.
stop webservers connecting to 2000 server. Make sure that no applications are connecting to the 2000 server.
backup your databases and restore on destination sql 2008 R2 server.(note: Do not detach/attach as things could go wrong and you will end up with detached database and no backups !)
Once your backups are restored on the 2008 R2 server, run the output from sp_help_revlogin on 2008 R2 server to recreate logins.
Sync up orphan users (If any) and recreate sql agent jobs and linked servers on the new server.
change compatibility level on the restored databases to 100.
Dbcc checkdb with all_errormsgs  and data_purity options turned on : DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY
run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
Optional : Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's scripts.
Recompile all SP's using sp_recompile 'procedureName'
Refresh your views SP_REFRESHVIEW view_name
make sure to change the database option : page verify to CHECKSUM.
Change the recovery model (if different from sql 2000) to FULL. If you change to FULL recovery model, then MAKE SURE you do Transaction Log backups frequently. This will help you to recover point-in-time as well as not bloat your T-Log.
In SQL Server 2005 and up, Database Mail was introduced. So you have to migrate from SQLMail to Database Mail.

USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

Also, if you have any replication, then you have to reset it up. If any DR like logshipping or Mirroring (new in 2005 and up, but depreciated in 2012) then you have to reset it up as well.

Old DTS packages needs to be migrated to SSIS using C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSMigrationWizard.exe (command line) or using Package Migration Wizard.

Also, you can use my script found at https://dba.stackexchange.com/a/36701/8783. Though, it uses detach/attach method, I highly recommend you to use BACKUP/RESTORE method. Change the script accordingly.

As a side note:

turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
Enable Optimize for ad-hoc workloads and Default backup compression options.

Lets address your questions ...

  What else should I do to make migration complete?

Refer to my answer. It will help you properly come up with a migration plan. Always test your migration plan in a UAT (non production) along with proper application testing by business users.

  use new features like checksumming and full recovery model.

CHECKSUM is new in SQL Server 2005 and up. I have covered it as a part of migration steps described above.

full recovery model is not new. It depends on your business type and dictates on how much data you can lose in case of disaster.

Full recovery mode with frequent Transaction log backups will allow you to restore point-in-time and there by reducing the amount of data loss.

  make this database to be exactly as it was created in SQL Server 2008 R2.

  make this database to be fully compatible, correct and be perfect suited for new, SQL 2008 R2 database engine.

Don't fully understand this ! But above migration steps will help you. You just have to restore the database and change the compatibility level 10 100 along with above steps.

  I just want to know how to correctly and completely convert old SQL Server 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

You have to be careful with this, as this will require changes to your application code as well. If your application code is changed to use the new features in SQL Server 2008 R2, then you wont be encountering any issues - PROVIDED you have fully done a complete regression testing of your application in a UAT or DEV environment. This will give you best confidence when you do the actual migration in PROD.

Note: Above are steps that I could remember and I am pretty sure that nothing is left out. If I see that I have missed some stuff, then I will add it or other experts on this site -- feel free to add !

Everything that is outlined above needs to first replayed on a NON PRODUCTION environment to avoid any surprises during the actual migration.

----------

Few more questions:

  You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem.

If everything worked fine and you were able to attach the database, then NO you wont be having any problems. Detach/Attach vs Backup/Restore is just a method on how you move your database/s to a different place. Just FYI .. Backup/Restore is more secure and reliable as if anything goes wrong (in worst cases) then atleast you have a backup to restore and recover your database/s.

  About checksumming and full recovery model: it wasn't available/enabled on SQL Server 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking.

As I said, checksum is new in version 2005 and up. It is a mechanism by which SQL Server will detect page corruption especially due to I/O. Refer to my answer here for more details.

To enable CHECKSUM as well as change the recovery model to FULL, you can do it using below T-SQL code :

USE master;
GO
ALTER DATABASE [your_database_name] -- change this !!
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

Note : Once you set the database options, it will be persisted when you do migration from 2008R2 to 2012.

  I'm preparing to migrate this database to SQL Server 2012 - so first it was from 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of 2000 databases in SQL Server 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right?

Yes please. As I said, backup restore is the preferred method, unless you have a good reason not to do it.

  Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

Backup/restore is ... similar to dump and load used in Sybase, Oracle or probably MySQL as well. Its just SQL Server calls it .. backup/restore.

A must read : Understanding SQL Server Backups by Paul Randall.

Simple Syntax(for Full syntax refer BOL) :

backup database database_name
to disk = 'D:\backup\database_name_full.bak'
with init, stats =10

Then restore can be done on destination server as :

-- assuming the destination's disk layout doesn't match the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
move 'logical_data_fileName' to 'physical_path\database_name.mdf'
move 'logical_log_fileName' to 'physical_path\database_name_log.ldf'
with recovery, stats = 10

-- assuming the destination's disk layout matches the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
with recovery, stats = 10

  Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

backup/restore will not defragment your database. You have to use Alter Index Reorganize or Rebuild depending on your fragmentation level.

Since you are new to SQL Server I would highly recommend you to use Ola Hallengren's :

SQL Server Index and Statistics Maintenance - for defragmenting your Indexes
SQL Server Backup -- for taking care of your backups
SQL Server Integrity Check -- for checking database consistency of your databases

  As we were using SQL Server 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL Server 2008, isn't this still better than using backup function in Management Studio?

Stopping engine is the worse thing that you can do to do a backup !!

Read Paul's link about the backups that I mentioned and Use Ola's script. Microsoft has a KB article with the script to do automated backups - How to schedule and automate backups of SQL Server databases in SQL Server Express

  Full recovery mode with frequent Transaction log backups - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly?

Every SQL Server database has a log that records all the transactions and database modifications made by each transaction. The transaction log is a critical component of any database.

The usual naming convention extension for Transaction log is '.LDF', but it can be any.

I am not going to write more on this as this will make the answer very leanthy. Refer to
Transaction Log Management and my answer here has excellent links as well.

EDIT : 8/24/2016 .. This will help future readers :

If you are migrating your entire instance from one version to another version, I would highly recommend to use PowerShell based solution Start-SqlMigration


SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017] I have visual studio 2017.

I have a script transform that I can run locally just fine, however when I deploy to the  Integration Services Catalog on the dev server and run it, it fails with the following error:

  'package name':Error: Failed to compiled scripts contained in the
  package. Open the package in the SSIS Designer and resolve the
  following compilation errors.

So, when I remote into the dev and rebuild the package, it will work fine on the next execution.

I thought this might be a reference issue but after reinstalling Visual studio 2017 and SSDT the references are an exact match on both machines.

What could be the issue for this?

You need to make sure the package in Visual Studio has been created with the correct target SQL Version you are deploying to.
In Visual Studio right click on the project name and select properties.  Under the Configuration Properties -> General menu you will see the TargetServerVersion.  This must match the SQL version you are deploying to.
Once changed, build and deploy the packages again.


Reclaim disk space from large database's primary filegroup after truncating tables (other than shrink) We have an 8 TB SQL Server 2014 SP1 database, with one primary filegroup, spread across multiple files on different drives. This is part of an AlwaysOn AG with multiple secondaries (some async).

One old table in this db is 6TB, and we truncated it. Now to reclaim this disk space, we can either DBCC ShrinkDatabase or ShrinkFile.

We don't care about the usual problems with shrinking like fragmentation, or database growing again etc. because we are only focusing on regaining disk space now.

But these shrink operations are REALLY slow, and are looking for a faster way to do this.

First, are these slow because the changes have to sync to the synchronized-secondary?

Second, is there a way to make shrinks faster?

We thought about couple other options:

Creating another database and moving all (except the old) tables to it...but might need to setup AG all over.
Creating another filegroup and moving all (except the old) table to it...but the old table is on Primary filegroup, which cannot be dropped, right ?

Which method have you guys used in the industry to 'shrink' databases after dropping or truncating large tables?

I have read How do shrink *right* when required? but no answer satisfies my questions.. is there an option other than shrink? Can I move tables off the primary filegroup and delete it?

Community wiki answer:

It's going to take some time for the shrink operations on such large database files to run. Honestly, you should consider running the DBCC command and just letting it run as long as you can, on days and at times you can afford. Just let it do it's thing.

If the shrink operation is stopped before completion it does not roll back the changes so even if it runs for 2 hours on one day, another day for 4 hours, and so forth until a weekend or something where you can let it run a whole day until it's able to complete quickly.

Run scripts to check DB free space before and after each run too.

This way no exporting to new DB structure or whatever, and you can just let it do it's own thing. Do some more research, but in similar-sounding cases, I've been involved in the past (huge data warehouses where data loaders did things all wrong - using the actual DB for temp tables rather than tempdb), it happened and was what it was. I got the issues tracked down, retrained data loaders, and then did the cleanup. Afterwards I worked on taking care of fragmentation, etc. where space was higher priority than performance.

Because of the issues mentioned, we have always shrunk files in smaller chunks. In my experience, the shrinks are excruciatingly slow initially, i guess because SQL Server has to move the pages from the end of the file before shrinking. But as you progress it becomes faster and faster.


Database replication using wamp? I have created a POS system for our corporation, in the HQ we have a wamp server with the main database, we also have more than 25 branches across the country.

I will setup a wamp server on each branch, so I can access its database directly when putting wamp online.

I want to make MySQL replication with all branches, so every query on any branch will affect the main database on HQ.

I tried to test but found no one to explain how to do it using wamp on different PCs.

For something like this, you may want to think about a unique architecture known as a Star Topology.

Picture the following:

Server HQDB with 25 databases (1 for each branch)
Server BranchDB001 with database `branch001``
Server BranchDB002 with database branch002
...
Server BranchDB025 with database branch025

Setup Data for HQDB

On HQDB, create the 25 databases branch001 through branch025
Load data for each branch into their respective databases

Setup each Branch

Load HQDB's branch001 into branch001 on Server BranchDB001

mysqldump branch001 database on HQDB into a script
Run that script in branch001 on Server BranchDB001

Repeat this for all Branch Databases

Setup Replication

Here is the tricky part. You want to make the HQDB the Slave of Every Branch. Unfortunately, MySQL Replication is inherently designed to be a Slave reading from one Master. (NOTE: MultiMaster (MultiSource) Replication is being implemented in the soon-to-be-released MariaDB 10.0. Since I am a MySQL DBA, I leave that to more adventurous DBAs. I need to more free time to learn MariaDB.)

Getting on with MySQL Replication, the idea is

RESET MASTER on BranchDB001 (Clears Binary Logs)
make HQDB a Slave of a Branch (say branch01)
Start Replication loading changes from BranchDB001's branch001 into HQDB's branch001
RESET MASTER on BranchDB002
make HQDB a Slave of a Branch (say branch02)
Start Replication loading changes from BranchDB002's branch002 into HQDB's branch002
...
RESET MASTER on BranchDB025
make HQDB a Slave of a Branch (say branch25)
Start Replication loading changes from BranchDB025's branch025 into HQDB's branch025

If you have common tables to ship to every branch, simply mysqldump each branch's copy of that table and load into each branch.

All these things could be done in a maintenance cycle to update HQDB's copy of every branch

I have discussed this wild idea before

Apr 25, 2011 : MySQL in star topology in StackOverflow (My 1st answer)
Apr 25, 2011 : MySQL in star topology in StackOverflow (My 2nd answer)
Apr 28, 2011 : MySQL in star topology in ServerFault

CAVEAT

Make sure your firewalls allow for DB Access in both directions

Branch to HQ
HQ to Branch

SSH Tunneling, please


SQL Server - Overlapping NC Indexes, Drop Question I'm doing some index cleanup/tuning on a SQL '14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following definitions:

IX_1 ON [Tbl1] ([field1])
IX_2 ON [Tbl1] ([field1], [field2]) INCLUDE ([field3], [field4])

The read/write data for the two indexes shows IX_1 currently with 5 million Seeks/Scans against it vs. 10k Seeks/Scans against IX_2, with equal updates for each.  My index tuning sense says to drop IX_1 without a second thought as it's fully covered by IX_2.  But the the drastic preference of the optimizer to use IX_1 spooks me a little.  Is there any reason to think twice about dropping IX_1 in a case like this?  Thanks.

When two indexes both satisfy a query, you'll find that the smaller of the two is selected. Often, this means the narrower index, but it also holds true with fully duplicated, identical indexes, with the physically smaller one being preferred.
Unless you're scanning the index and the full size matters, your index tuning Spidey Sense is right. Drop IX_1 and your queries will use IX_2. Those seeks will hardly notice.


Improve performance on concurrent UPDATEs for a timestamp column in Postgres I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here.

The queries all have the same format

UPDATE "managers" SET "updated_at" = '2014-07-25 15:00:24.212512' WHERE "managers"."id" = 1

The problem is that there is too much activity on this column, which leads to slow writes. I'm assuming the slowdowns are being caused by the locking mechanism.
There is no index on the column.

We have been trying to mitigate this by batching together operations that would cause multiple updates and this did help but the whole system is still suffering.

Would a different kind of column be faster? For example an integer.
Is there some way to not lock the column?
Any other tips to improve this?

We are actually thinking of moving this to redis and using INCR.

                                            Table "public.managers"
         Column          |            Type             |                          Modifiers
-------------------------+-----------------------------+-------------------------------------------------------------
 id                      | integer                     | not null default nextval('leads_managers_id_seq'::regclass)
 account_id              | integer                     |
 created_at              | timestamp without time zone |
 updated_at              | timestamp without time zone |
 notification_recipients | text                        | default ''::character varying
 uuid                    | character varying(255)      |
Indexes:
    "leads_managers_pkey" PRIMARY KEY, btree (id)
    "index_leads_managers_on_uuid" UNIQUE, btree (uuid)
    "index_leads_managers_on_account_id" btree (account_id)

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

  Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

  Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.


Mysql master-master: single table out of sync I have a master-master MySQL 5.7, everything works fine except for one error which is rising from time to time.

If I run a show slave status\G on server2 I have an error on a table:

...
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table my_database_name.my_table_name; Can't find record in 'my_table_name', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000120, end_log_pos 83145706
...

I can temporarily skip the error by running:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

or by setting

slave-skip-errors = 1032
skip-slave-start

in server2's my.cnf and then restarting MySQL.

However, I would like to fix it permanently so I checked in server1's binary logs and I found this:

### UPDATE `my_database_name`.`my_table_name`
### WHERE
###   @1=140
###   @2='2014:02:02'
###   @3=2878
###   @4=3253
###   @5=''
###   @6=0.00
###   @7=0
###   @8=35.75
###   @9=0.00
###   @10=0
###   @11=0
### SET
###   @1=140
###   @2='2014:02:02'
###   @3=2878
###   @4=3254
###   @5=''
###   @6=0.00
###   @7=0
###   @8=35.75
###   @9=0.00
###   @10=0
###   @11=0
# at 83145706

I see it tries to update the table by setting the value 3254 in the 4th column where the 4th column itself has value 3253. Anyway, if I look at the table on  the servers, it has a different value on both:

server1:

mysql> select * from my_table_name where my_table_name.id = 140;
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| id  | col2       | col3     | col4     | col5    | col6      | col7    | col8    | col9      | col10               | col11               |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| 140 | 2014-02-02 |     2878 |     3254 |         |      0.00 |       0 |   35.75 |      0.00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+

server2:

 mysql> select * from my_table_name where my_table_name.id = 140;
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| id  | col2       | col3     | col4     | col5    | col6      | col7    | col8    | col9      | col10               | col11               |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| 140 | 2014-02-02 |     2878 |     3257 |         |      0.00 |       0 |   35.75 |      0.00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+

Considering it's master-master, how can I align the tables? I think I can't simply set the value 3253 hoping it's going to be updated by the binary log, can I?

If possible, I want to avoid re-syncing the whole database because it's really huge.

Thank you!

You could use pt-table-checksum and pt-table-sync to perform an optimized synchronisation while online and it will backoff to avoid replication lag.


Tools and best practice for reporting/presentation database Currently my employer has a team that creates tableau dashboards and another very large team that creates ad hoc and regular reporting for external clients.
Both of these teams pull data from many databases and other sources.  For each report they either create a script that lives on their machine, a stored proc that lives in a source database, sometimes a scheduled job on a jenkins instance, or manually copy and paste the results of a query.  It's a badly documented mess.

My solution, that will have the least impact on how they work, and cost to implement, is to create a database on which all of the extracted data will live, so the reports and dashboards only access this single source.  A task scheduler to run regular extraction jobs, and a fairly simple custom software application that tracks changes to the source databases and alerts the project owners (of the jobs) but can also be used by the database owners to determine which downstream jobs will be impacted by changes to their databases.

I was wondering if what I have described already exists as a concept (is a presentation database a thing?) or if there exists software that could do the bulk of the heavy lifting for me? I after any tools that I might look into as alternatives, or concepts that might help my better design this solution.

So data needs to be extracted mainly from mssql, jira, jenkins and few custom sources.  I'd be storing the extracts on RDS and running the jobs once migrated on Jenkins.  I'd be building a custom web based UI for some very specific interactions, but monitoring would all be tableau dashboards.

The RDS instance wouldn't be a true data warehouse or vault, more akin to a lake I guess, as the data stored there would be aggregated from the disparate systems for a very specific purpose per table and would not be linked to each other in anyway.  This is basically replacing a huge number of regular extracts living all over the various systems and disks and instead storing them in a single database.

So I'm wondering in particular if this setup is common? and if there are any tools I should be looking at that might do some of this already.

It seems you are referring to a concept called a data warehouse.  Data Warehousing is a huge topic, with a few main types of architecture: star schema, snowflake, and data vault. Googling these might give you some ideas on how to proceed.


How can Restore MS SQL 2000 database To Ms SQL 2012 Please suggest:
I have MS SQL 2000 database  backup i want to Restore in Ms SQL 2012 Please suggest.

You must first restore the 2000 database to a SQL Server 2005 instance, then take a backup from the 2005 instance to restore on the 2012 instance.


PostgreSQL count 0 using WHEN and GROUP BY with more than one column I am trying to make a new table that shows the number of Active Credit Cards each customer has in my dataset using:

SELECT
    sk_id_curr,
    credit_type,
    credit_active,
    COUNT(credit_type)
FROM
    BUREAU
WHERE
    credit_active='Active' AND credit_type='Credit card'
GROUP BY
    sk_id_curr,
    credit_type,
    credit_active

The problem is that it removes ID's where there are no Active Credit Cards. I want it to return ID's with no credit cards with count 0. How would I do this?

Ex:

Original Data

-------------------------------------------------
id              type                   activity
--------------------------------------------------
10001        Consumer Credit           Active
10002         Credit Card               Active
10002        Credit Card               Active
10003         Credit Card              Closed

Current result

-----------------------------------------------------------------------------
id,                type,              activity,                  count
------------------------------------------------------------------------------
10002         Credit Card               Active                2

desired result

-----------------------------------------------------------------------------
id                type                 activity            count
------------------------------------------------------------------------------
10001         Credit Card              Active               0
10002         Credit Card               Active              2
10003         Credit Card               Active               0

SELECT DISTINCT
    BUREAU.id,
    'Credit Card' as type,
    COALESCE(t1.count, 0) as count
FROM
    BUREAU
LEFT JOIN
    (SELECT
        id,
        COUNT(*) as count
     FROM
        BUREAU
     WHERE
        activity = 'Active'
        AND type = 'Credit Card'
     GROUP BY
        id) t1
    ON t1.id = BUREAU.id
ORDER BY
    id;

   id | type        | count
----: | :---------- | ----:
10001 | Credit Card |     0
10002 | Credit Card |     2
10003 | Credit Card |     0

db<>fiddle here

Or using the conditional aggregation pointed out by a_horse_with_no_name:

SELECT
    BUREAU.id,
    'Credit Card' as type,
    COUNT(type) FILTER (WHERE type = 'Credit Card' AND activity = 'Active')
FROM
    BUREAU
GROUP BY
    BUREAU.id
ORDER BY
    BUREAU.id;

db<>fiddle here


issues updating records in ssis using lookup and oledb command im working on an ssis project loading data between two sql azure databases that is updating matching records and inserting new records between them using a lookup and ole db command but I'm getting an error on the ole db command as the following, any ideas what it may be? trying to update over 20,000 records
[OLE DB Command [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: The semaphore timeout period has expired.

It seems like this could be a bug in OLE DB for SSIS. Could this solution be the one you're looking for?


How to mark a table as pre-populated with postgresql logical replication I accidentally loaded data into a table before setting up a logical replication subscription for it and now I'm getting errors when I try to sync it (duplicate key already exists).

I can't delete the rows because "cannot truncate a table referenced in a foreign key constraint".

Is there a way to tell the subscription that the table was pre-synced or pre-populated?

We're getting lots of logs lines like:

ERROR: duplicate key value violates unique constraint

Thanks.

I was able to get this fixed using a bit of a hack. Basically, drop the table from the publication, refresh the publication from the subscriber, add the table to the publication again, then refresh the publication again, but with copy_data=false:

drop the table from the publication.

alter publication my-publication drop table only my-table ;

Refresh the subscription.

ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION;

The log lines seem to stop at this point. Good!
Add the table to the publication.

alter publication my-publication add table only my-table ;

Refresh the subscription with copy_data (false)

 ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION with (copy_data = false);

This seems to work, though it seems like it'd be problematic on a table that was changing rapidly.

It'd be nice to put this in a transaction somehow, but the commands are being run alternately on the publisher and the subscriber, so I don't know how that'd work.


'Incorrect datetime value: for column 'last_login' at row 1' i am getting below error:

Last_SQL_Errno: 1292

    Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. Query: 'INSERT INTO app_player (creation_date, last_login, removed_date, app_id, user_id) VALUES ('2013-03-10 02:00:00', '2013-03-10 02:00:00', null, 400, 77870956)'
1 row in set (0.00 sec)

Table structure:

mysql> show create table app_player\G
*************************** 1. row ***************************
       Table: app_player
Create Table: CREATE TABLE `app_player` (
  `app_id` int(10) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `creation_date` datetime NOT NULL,
  `removed_date` datetime DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`app_id`,`user_id`),
  KEY `idx_creation_date_app_id` (`creation_date`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql veriosn is : 5.1.52

mysql> show global variables like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| innodb_stats_auto_update                | 1                 |
| innodb_stats_update_need_lock           | 1                 |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_log_update                          | ON                |
| sql_low_priority_updates                | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+

Description:
The sql_mode changes behaviour from 5.6 to 5.7.
I notice a create table statement that replicates fine on a 5.6 master breaks on the 5.7 downstream slave.

Last_SQL_Error: Error 'Invalid default value for 'last_change'' on query. Default database: 'db1'. Query: 'CREATE TABLE mytable (
id int(10) unsigned NOT NULL,
field2 tinyint(3) unsigned NOT NULL,
last_change timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
....

sql_mode on the 5.6 master is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode on the 5.7 slave is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
(both the config in /etc/my.cnf)

Given the sql_mode change the value and this is being replicated the 5.7 stricter behaviour is being applied on the 5.7 slave by the SQL thread when in fact the 5.6 behaviour should be applied.

How to repeat:
see above.

Suggested fix:
Make the SQL thread aware of the master version being 5.6 and treat the sql_mode as if it were running on a 5.6 server to maintain the same behaviour.

https://bugs.mysql.com/bug.php?id=75439


Unable to deploy SSIS package to remote sql server I am a newbie to SSIS.
I have created Azure VM and installed SQL Server. I need to deploy the ssis package from visual studio in my local to SQL server on Azure VM. Using below command I can login to VM SQL server with windows authentication from SSMS in my local.

runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL
Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

Now, I'm trying to deploy package from Visual studio to VM SQL Server, In the deployment wizard it asks for destination server where not able to login to VM sql server using windows authentication, it throws below error:

And when using SQL server authentication in deployment wizard for destination server it throws below error:

How can I deploy packages to Azure VM SQL Server from local ?

Have you tried to lauch VS in the same way you are using SSMS?
runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\devenv.exe"

Eventually you can build your SSIS solution with VS, copy the ispac file to your VM filesystem and thne import the ispac file inside the ssis catalog with the proper wizard.


Granting access to all tables for a user I'm new to Postgres and trying to migrate our MySQL databases over.  In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database.  I must be missing something in Postgres because it looks like I have to grant those privileges for each table one at a time.  With many databases and hundreds of tables per database that seems like a daunting task just to get off the ground.  In addition, once a database is in operation, adding tables happens frequently enough that I wouldn't want to have to grant permissions each time unless absolutely necessary.

How is this best accomplished?

First, you have to be able to connect to the database in order to run queries.  This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name
TO user_name;

The REVOKE is necessary because

  The key word PUBLIC indicates that the privileges are to be granted to
  all roles, including those that might be created later. PUBLIC can be
  thought of as an implicitly defined group that always includes all
  roles. Any particular role will have the sum of privileges granted
  directly to it, privileges granted to any role it is presently a
  member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

As Jack Douglas pointed out, the above only gives the privileges for the already existing tables.  To achieve the same for future tables, you have to define default privileges:

ALTER DEFAULT PRIVILEGES
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Here, some_role is a role that creates the tables, while user_name is the one who gets the privileges.  Defining this, you have to be logged in as some_role or a member of it.

And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) - here it is the USAGE privilege that you need.


Should I use VARCHAR or DATETIME? I am making a blog cms system and the release date and time of the posts should be shown on my site.

Should I save the Datetime in the php code with a function and save it in a varchar in the database or should I use the default value of the database by using DATETIME.

Which is the more professional way to do this / The more efficient way

You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.

Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, what if one user uses en_US and one uses en_GB, the date will get stored differently within the same column of your database. Then when you query your data, how will you know whether '01/12/2020' is January 12th 2020 or December 1st 2020? There is a big difference.

Storing the date as a date allows you to apply different formatting (such as language differences, or precision differences) in the presentation layer where it belongs.


Indexed view referencing objects on two different schemas When I try to create/alter a view to create an index like this

CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation
   ON sfdc.vSalPopulation (ID);

I get the following error message

  Msg 1938, Level 16, State 1, Line 40 Index cannot be created on view
  'vSalPopulation' because the underlying object 'YR_TRM_SBTRM_TABLE'
  has a different owner.

When I check the tables I see that the tables are owned by different schemas

exec sp_tables 'dbo.YR_TRM_SBTRM_TABLE'
exec sp_tables 'vSalPopulation'

TABLE_QUALIFIER     TABLE_OWNER       TABLE_NAME              TABLE_TYPE    REMARKS
MyDB                dbo               YR_TRM_SBTRM_TABLE      TABLE         NULL
MyDB                sfdc              vSalPopulation          VIEW          NULL

The documentation on indexed views states that you cannot have an indexed view that references two different databases.

The view must be created by using the WITH SCHEMABINDING option.
The view must reference only base tables that are in the same database as
the view.
The view cannot reference other views. … etc

However, I have the same database but two different schemas. Maybe the problem is actually the third required. While I am not referencing other views, there are functions.  Maybe I misunderstand the error message. Permissions? So, generally, is it possible to have an indexed view that references objects from two different schemas?

A simplified definition of the view that gives me the same error looks like this

ALTER VIEW sfdc.vSalPopulation
   WITH SCHEMABINDING
AS
SELECT DISTINCT
    ID
FROM dbo.CAN
INNER JOIN dbo.YR_TRM_SBTRM_TABLE YTS ON CAN.YR_CDE = YTS.YR_CDE
WHERE YTS.SBTRM_END_DTE > GETDATE()

I think I found the answer here. Basically, grant authorization on my second schema to dbo:

ALTER AUTHORIZATION ON SCHEMA::sfdc TO dbo

So, it really has to do with ownership/authorization rather than the schema itself.
See: https://www.sqlteam.com/articles/understanding-the-difference-between-owners-and-schemas-in-sql-server


Building slowly changing dimension on a Fact/Dimension Star Schema I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as:

  The fact table should contain core information about an object and dimensions should contain information about the facts

(paraphrased)

However, practically in business, I have seen a star schema designed where the fact table contains a surrogate key, a business key, and all single-valued fields of an object, and each dimension stores all the multi-valued fields of an object (hence the word dimension). For example, a person may be the object represented in a fact table. A person has one name, one age, etc., which all make viable facts in a fact table. A person may own multiple cars, each with their own attributes, which would represent a person's car dimension, stored as a dimension table with several columns to describe each car's attribute. In this example, this dimension table also includes a foreign key representing the business key of the corresponding row from the fact table.

So, if we can agree that this may be a suitable design, the problem that I am trying to overcome is how to do SCD type 2 (historical) on a multivalued dimension table. For my fact table full of single facts, it is obvious. I include two extra columns, an effective date and expiration date, and I use the business key to link common records where the most recent record has a NULL expiration date, and all other historical records for the same business key have both an effective and expiration date indicating at what point in time they were the most current record.

How do I use this same concept on a dimension which represents a multivalued list? I essentially would like the same concept where I can (1) identify the current list (in this example, the cars a person owns) and (2) identify what the list was at any given moment in history. Can I just put an effective and expiration date on each dimension value? How then do I differentiate between values added after a certain time? Or deleted values?

But, if we do not agree on this design approach, please tell me what industry standard is so I can do this correctly.

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to mark currently valid records as this simplifies queries. An end date in the past would signify deletion or any other semantic you define (like person left country or is not employed anymore). Also add surrogate keys to your dimension tables to uniquely identify records.

Fact tables usually contain "measures" like sales or cost or signify events like a placed call or durations of these calls. One would usually use aggregates on these columns in reports.

A star-schema is a way to model a sparsely populated "cube", where each axis of the coordinate system is given by one of the dimension tables. "Slice and dice" operations and "drill up / drill down" operations in reports translate nicely into SQL using this model.

In your cars and people example, I would use two dimension tables, one for cars and one for people, each historized (according to SCD2), and a factless fact table comprising foreign keys to the dimension tables, referencing the respective identifier (entity identifier), and valid time columns (SCD2). You would not add a record according to SCD2 rules in the fact table, if one of the dimension tables changes, in this design.

This way you can model changes in each entity, like name changes in people, color changes in cars and the relationship between cars and people, for example ownership. Each table would use non-overlapping valid times (start and end values) for each business key, recording the history of these entities independently. The fact table would in this model basically be a m:n linkage table, for which a separate history of valid times is kept.

You would identify the current and past lists by using x between start and end on each table for as-of now (or past) queries (answers your (1) and (2) - ignoring if the intervall is right open or left open).

Summary statistics like how many cars do we have in some city with full history (assuming city is part of the people dimension table), can now be answered using temporal joins and "sequenced" queries, which are sometimes also called "coalesce" queries, see Snodgrass, chapter 6.3ff


Should I enable binlog binary logging on slave server? What are the benefits of enabling binary logging in MySQL slave server? I'm thinking that doing a switchover from master to slave and back to master would be easier in this way?

Enabling binary-logs on your current slave server will eliminate two steps of the failover process but there would still be many other steps involved.

In a true failover and depending on the scenario you'd probably go through the following steps.

Enable binary logs on the new master via my.cnf or equivalent.
Restart MySQL on the new master.
Grant the old master's replication user access to the
new master.
Run the FLUSH TABLES WITH READ LOCK command on the new master.
BACKUP the databases being replicated on the new master.
Run the UNLOCK TABLES command on the new master.
RESTORE those databases to the new slave.
Run the stop slave command on the current slave.
Run the CHANGE MASTER command on the old master to point to the new
master.
Run START SLAVE on the new slave.

Update after Karoly's comments

As a follow response up to Karoly's answer and comments. If you really wanted to save time on the fail-over process, using a master-master replication schema would be the best idea in my opinion.

You would essentially prepare the slave to take over as master at anytime, leaving you with just redirecting your applications to the new master.

Assuming you've already setup a master-slave replication schema.
The Basic steps to then setup a master-master replication, you would.

Turn on bin-logs on the slave
Turn on log-slave-updates on both the master and the slave
Configure the master to update from the slave with CHANGE MASTER
Grant access to the replication user on both master and slave
start slave on both servers
Eventually make the slave server read-only via the read-only my.cnf
option


Is there a performance gain in specifying the table name in joins and using explicit cast? Is there any performance gain by the interpreter of the query is I specify the table on a join and make the cast explicit?

Instead of

...
join table_join on field1_from = field1_join and field2_join = '2'
...

using

...
join table_join on table_from.field1_from = table_join.field1_join and table_join.field2_join::text = '2'::text
...

It seems that this should make the job of the parser a little easier

It is a good practice to use aliases to qualify tables etc

Select a.c1, b.c2
From table1 as a
 ....

However, that has very little to do with performance. Identifying each table etc is only a fraction of what the optimizer does and I would be surprised if it even is noticeable.


Access not properly translating TOP predicate to ODBC/Oracle SQL I have a MS Access query that is based on a linked ODBC table (Oracle).

The DDL for the underlying Oracle table is:

create table road_insp
(
    insp_id integer,
    road_id integer,
    insp_date date,
    length number(10,2)
);

INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (1, 100, to_date('1/1/2017','MM/DD/YY'), 20);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (2, 101, to_date('2/1/2017','MM/DD/YY'), 40);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (3, 101, to_date('3/1/2017','MM/DD/YY'), 60);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (4, 102, to_date('4/1/2018','MM/DD/YY'), 80);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (5, 102, to_date('5/1/2018','MM/DD/YY'), 100);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (6, 102, to_date('5/1/2018','MM/DD/YY'), 120);

select * from road_insp

   INSP_ID    ROAD_ID   INSP_DAT     LENGTH
---------- ----------   -------- ----------
         1        100   17-01-01         20
         2        101   17-02-01         40
         3        101   17-03-01         60
         4        102   18-04-01         80

         5        102   18-05-01        100
         6        102   18-05-01        120

And the local MS Access Query is:

SELECT ri.*
FROM user1_road_insp AS ri
WHERE ri.insp_id = (
                    select
                        top 1 ri2.insp_id
                    from
                        user1_road_insp ri2
                    where
                        ri2.road_id = ri.road_id
                        and year(insp_date) between  [Enter a START year:] and [Enter a END year:]
                    order by
                        ri2.insp_date desc,
                        ri2.length desc,
                        ri2.insp_id
                   );

The Problem:

The performance of the query is quite poor. Unlike the table in the sample DDL, the real table has about 10,000 records. Similar queries execute instantly, but this query takes several minutes to run.

I've checked the SQLOut.txt log, and the problem seems to be that it is executing way too many individual statements:

SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri"
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )

Question:

Unlike my other local MS Access queries, this query uses the TOP predicate. MS Access seems to be attempting to translate TOP to SQL that the ODBC driver and/or Oracle can understand. However, it seems to be doing a very poor job of it.

How can I improve the performance of this query?

I think this may be less an ODBC challenge and more an Access challenge you are facing.

The driver is more or less (with differences like when drivers like to introduce server-side cursors and break work up that way, etc) doing what the client tells it to.

Access is really well known for doing some interesting things to decent databases and somewhat well designed DBs. Can you at least put your code into Oracle in stored packages/procedures and then just call those from the forms app? Are you 100% married to Access?


Can a non-owner of a table be allowed to disable the table's triggers? I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?

As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:

CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;

Then grant the necessary privilege to your user to execute the function:

GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;

Now alice will be able to disable that one trigger.  You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.

The trick is in SECURITY DEFINER.  It makes the function running with the privileges of the user that defines (creates) the function.  As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.


Recursivly get a Tree Via self joined table Using other questions here & Postgresql documentation I've managed to build a many-to-many self joined table.

However adding a WHERE clause is giving me trouble.

Problem:

A Category can have many child categories, and many parent categories. Given a category.Id, I want to retrieve the category, the category children, childrens children and so on.

Example: given this structure:

child_1
    child_11
        child_111
        child_112
            child_1121
    child_21
child_2

Given: a clause of id = child_11

Expected results:
child_11, child_111, child_112, child_1121,

Actual results: child_11, child_111, child_112

Here is my attempt: http://sqlfiddle.com/#!17/3640f/2

In case Sqlfiddle is down: https://www.db-fiddle.com/#&togetherjs=LhDjxfPHo6

Note: I don't care about duplicating the where clause, my application can handle that

Table structure:

CREATE TABLE Category(id SERIAL PRIMARY KEY, name VARCHAR(255));
CREATE TABLE Categories(parent_id INTEGER, child_id INTEGER, PRIMARY KEY(parent_id, child_id));

ALTER TABLE Categories ADD FOREIGN KEY (parent_id) REFERENCES category (id);
ALTER TABLE Categories ADD FOREIGN KEY (child_id) REFERENCES category (id);

Table data:

INSERT INTO Category(id, name) VALUES (1, 'parent_1');
INSERT INTO Category(id, name) VALUES (2, 'child_1');
INSERT INTO Category(id, name) VALUES (3, 'child_2');
INSERT INTO Category(id, name) VALUES (4, 'child_3');
INSERT INTO Category(id, name) VALUES (5, 'child_1_1');
INSERT INTO Category(id, name) VALUES (6, 'child_1_2');
INSERT INTO Category(id, name) VALUES (7, 'child_1_1_1');
INSERT INTO Category(id, name) VALUES (10, 'child_of_many');
INSERT INTO Category(id, name) VALUES (11, 'parent_1');
INSERT INTO Category(id, name) VALUES (12, 'parent_2');

INSERT INTO Categories(parent_id, child_id) VALUES (1, 2);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 3);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 4);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 5);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 6);
INSERT INTO Categories(parent_id, child_id) VALUES (5, 7);

My query which is giving me the children, but not the childrens children etc.
If I remove the WHERE clause's I can get all rows:

WITH RECURSIVE categories_category AS (
  SELECT id, 'Category' AS COLUMN_TYPE, c1.name
  FROM Category c1
  WHERE c1.id=2
UNION
  SELECT c2.id, 'Category' AS COLUMN_TYPE, c2.name
   FROM Category c1
   INNER JOIN categories cs1 ON c1.id = cs1.parent_id
   INNER JOIN Category c2 ON c2.id = cs1.child_id
   WHERE cs1.parent_id = 2
) SELECT * FROM categories_category

Edit: More Detailed example:

Given the following category row's, I'd like to be able to run a query given a WHERE clause the matches the id of warmStoreAlcohol and get a result of:

+---+------------------+
|id |name              |
+---+------------------+
|2  |warmStoredAlcohol |
|3  |vodka             |
|4  |beer              |
|5  |frozenBeer        |
+---+------------------+

coldStoredAlcohol would give a result of:

+---+------------------+
|id |name              |
+---+------------------+
|6  |coldStoredAlcohol |
|5  |frozenBeer        |
|7  |cooler            |
+---+------------------+

The database structure will not change often. In this example 'frozenBeer' has two parents, and should return for querying both warmStoredAlcohol and coldStoredAlcohol.

I am open to changing the table structure, adding new tables and even upgrading postgres version etc. The database will hold ~2,000 rows, therefore I value an easy-to-understand table structure over a super complicated optimal one. (But any solution is better than my broken one)

Recursive CTEs are notoriously hard to learn.  Take a look at this fiddle to play with a working model.

The 2nd query in the CTE, or Common Table Expression, needs to reference the first query.  This is where the "recursive" name comes from in CTE.

My fiddle has a single table, where each cat can only have a single parent, which is arguably a more common tree scenario.  This makes it slightly easier to understand, in my opinion.

Anyway, the DDL consists of:

CREATE TABLE cats
(
    cat_id SERIAL
      PRIMARY KEY
  , cat_name VARCHAR(20) NOT NULL
  , parent_cat_id int NULL
);

ALTER TABLE cats ADD FOREIGN KEY (parent_cat_id) REFERENCES cats (cat_id);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('garfield', NULL);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('bertina', 1);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('oletha', 2);

And the recursive CTE looks like:

;WITH RECURSIVE cats_hierarchy AS
(
  SELECT c1.cat_id
      , c1.cat_name
      , cast('' as varchar(20)) as parent_cat_name
      , c1.parent_cat_id
      , 1 as level
  FROM cats c1
  WHERE c1.parent_cat_id IS NULL

  UNION ALL

  SELECT c2.cat_id
      , c2.cat_name
      , c1.cat_name AS parent_cat_name
      , c2.parent_cat_id
      , c1.level + 1 AS level
  FROM cats c2
      INNER JOIN cats_hierarchy c1 ON c2.parent_cat_id = c1.cat_id
)
SELECT *
FROM cats_hierarchy;

Results look like:

╔═════════╦═══════════╦══════════════════╦════════════════╦═══════╗
║ cat_id  ║ cat_name  ║ parent_cat_name  ║ parent_cat_id  ║ level ║
╠═════════╬═══════════╬══════════════════╬════════════════╬═══════╣
║      1  ║ garfield  ║                  ║ (null)         ║     1 ║
║      2  ║ bertina   ║ garfield         ║ 1              ║     2 ║
║      3  ║ oletha    ║ bertina          ║ 2              ║     3 ║
╚═════════╩═══════════╩══════════════════╩════════════════╩═══════╝

In the CTE the first query (before the UNION ALL) shows cats that are at the root.  The 2nd part of the CTE (after the UNION ALL) gets cats related to the parent cat.  Those returned results are then queried again by the CTE, generating a tree.

Let me know if that helps, or if you need further clarification.


What configuration to use for PGbouncer over a WAN link We have two datacenters, one in America and one in Europe. For various reasons, the Europe datacenter has the database server, while the workers and web apps are in America.

PGbouncer is in the American datacenter, in transaction pooling mode, helping reduce connection latency to the european datacenter. Unfortunately, I often have transactions in the "<IDLE> in transaction" state, which are holding locks and preventing the rest of the machines from making useful progress.

America
-------

worker      webapp
   |           |
    \         /
     \       /
      +--+--+
         |
     pgbouncer
         |
         :

      public
      Internet

         :
         |
      PostgreSQL

------
Europe

What pgbouncer parameters should be set/reset/changed to prevent those idle in transactions?

Well, applications staying '<IDLE> in transaction' is really an application-level problem, not something pgBouncer can do much about.

First, you should figure out how long your applications are remaining '<IDLE> in transaction' for, and go after the egregious offenders. Since your clients are far away on a WAN link, it is perfectly normal for clients to be '<IDLE> in transaction' for, say, a few hundred milliseconds between short queries (i.e. network latency plus some processing time on the client side between subsequent queries). Check CURRENT_TIMESTAMP - query_start in pg_stat_activity, and sort by the largest differences to find the real offenders. A minute or two should cause some concern, hours-long you should get rid of immediately.

If you have a large codebase and many clients, it can be tricky to nail down exactly where in your code such '<IDLE> in transaction' connections are originating (and may even be a framework problem instead of from your own codebase -- Django had a bad history of causing mysterious '<IDLE> in transaction' connections). I recommend setting a different application_name for separate applications within your codebase, to better help you track where these connections are coming from.

Finally, there is a pending patch to allow Postgres to kill off '<IDLE> in transaction' connections you might be interested in:
http://www.postgresql.org/message-id/538DC843.2070608@dalibo.com

If we're lucky, we might have that idle_in_transaction_timeout (or whatever name it ends up with) in Postgres 9.5.


How to get minimum value in pyMongo I have a collection with several fields, and I'd like to get the minimum value from one. I know a command for getting the max value:

collection.find_one(sort=[("myfield", -1)])["myfield"]

but not one for getting the min. Is there a way to do this?

You can reverse the sort direction to get the minimum instead of the maximum value:

 # Sort by myfield (ascending value) and return first document
 collection.find_one(sort=[("myfield", 1)])["myfield"]

This example assumes that:

myfield is a numeric value (so the sort order makes sense to determine a minimum or maximum)
myfield exists in the matching document returned (otherwise Python will report a KeyError when trying to reference a non-existent field).
all documents in the collection have a myfield value (documents that have do not have a myfield value will sorted before the minimum numeric values)

To ensure your sort is based on documents that actually have a myfield value you can add $exists to the query criteria:

 collection.find_one({"myfield": {"$exists": True}}, sort=[("myfield", 1)])["myfield"]

For more information on sorting see:

Use Indexes to Sort Query Results
What is the compare order for BSON types?


SQL Server Log shipping standby db stuck restoring I have 2 servers on SQL Server 2005 SP4. When the secondary server catches up with the log files it is switches into standby mode, but after a while it seems to be 'stuck' in restoring.

There were some open connections & queries on the database, but these have now been closed. The copy and restore jobs are running fine, but its not being switched into standby. Can't see any transactions in the dmvs.

Can I attempt to manually restore the database with STANDBY option to switch it into standby? or will this break the log shipping (need to be careful as snapshot 300GB from a live website)

or is there anything else I need to check for task, etc blocking the switch to Standby..

Cheers

Turns out the log shipping configuration has reverted to NORECOVERY instead of STANDBY.

I think the is that there must have been an open session on the database, SSMS or alike and after a set period the log shipping is configured to revert back to NORECOVERY. The original configuration was did not have the 'disconnect users currently connected' option enabled.

To resolve the issue I simply went back into the original database and selected ship transaction logs and then selected the radio button for STANDBY instead of NORECOVERY


Selecting records where date range is current year minus #years I want to select and eventually delete records from a point in time earlier than current year minus "x" years.

I'm unsure of the most efficient way to do this.

This will delete anything three years older than January 1st of this year.

DELETE table_name
WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);


Can MariaDB or MySQL cast as bigint, getting an error: ... right syntax to use near 'bigint)'? If I want to take a string and create a table (this fails in strict mode),

CREATE TABLE g
  AS SELECT CAST('2147483699' AS int);

How do I tell it that I want a bigint type? I'll also get an int(10) type. SELECTing from it is humorous too,

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483647 |
+---------------------------+

The data is actually wrong, and it silently failed. I know that's a known issue with this database having really poor defaults, but I how can I create the table as a bigint?

CREATE TABLE g
  AS SELECT CAST('2147483699' AS bigint);

Doing that I get this error,

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

How do I cast an interger to a bigint?

PostgreSQL

With PostgreSQL, it's pretty simple. In addition to throwing an exception if you cast to an int, you cast to a bigint using

SELECT CAST('2147483699' AS bigint);
    int8
------------
 2147483699

Silently? Let's try:

CREATE TABLE g
   AS SELECT CAST('2147483699' AS int);

MariaDB 10.1.32, default sql_mode (non-strict)

  Query OK, 1 row affected, 1 warning (0.19 sec) Records: 1  Duplicates:
  0  Warnings: 1

And the warning is:

  | Warning | 1264 | Out of range value for column 'CAST('2147483699' AS int)' at row 1 |

Yes, selecting from the table is interesting, but not surprising given the warning.

(I don't have a MariaDB 10.2 instance at the moment, but testing on dbfiddle.uk indicates it too fails, though again not silently.)

MariaDB 10.3.6, default sql_mode (strict)

No warnings.

select * from g;

Gives:

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483699 |
+---------------------------+
1 row in set (0.000 sec)

And the column is indeed created as bigint(10).

MySQL 8.0, both the default strict and non-strict sql_mode

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

Computer says no.

Conclusion

Can MariaDB or MySQL cast as bigint? As we have seen, yes, MariaDB 10.3 can (though it's not GA yet ... but surely any day now it will be). However, it can only cast as bigint implicitly. You can't explicitly cast as bigint, just like you can't cast as varchar (but see MDEV-11283), text, float, tinyint etcetera. There are only a limited number of datatypes you can cast to. This is not well documented, but CAST appears to support only the same datatypes as listed for the CONVERT function.