How to tell if a backup log chain is broken? I had a situation where the Native Backups were being made on a Server.
I happened to see in msdb that there was a third party backup tool (AppAssure) that was also taking VSS (kind-of) backup to virtual device.

At some interval, the AppAssure (backup being made to VIRTUAL DEVICE) was doing a COPY_ONLY backup and at some other interval it was doing a FULL backup breaking the log chain.

Is there any way(T-SQL query) to know when a backup log chain is broken?

Here is a screenshot of the situation from February.

Reference Reading / Similar Q&As

You might want to check out my answer that I posted in response to the question:  Will VSS backups break logchain? (dba.stackexchange.com)

The explanation in my answer also links to the question How can I backup an SQL Server database using Windows Server Backup? (serverfault.com) which was also answered by myself.

Transaction Log Chain

When a Transaction Log (TLOG) backup is performed, the backup information is stored in the msdb database in various tables. The information stored will contain information like backup_type, logical_device_name, physical_device_name, is_copy_only, is_snapshot, and various ..._lsn columns (lsn = log sequence number).

You can retrieve the transaction log backup chain information from your SQL Server instance via the msdb database with the following script:

/* ==================================================================
 Author......:  hot2use
 Date........:  25.04.2018
 Version.....:  0.1
 Server......:  localhost (first created for)
 Database....:  msdb
 Owner.......:  -
 Table.......:  various
 Type........:  Script
 Name........:  ADMIN_Retrieve_Backup_History_Information.sql
 Description.:  Retrieve backup history information from msdb database
 ............
 ............
 ............
 History.....:   0.1    h2u First created
 ............
 ............
================================================================== */
SELECT /* Columns for retrieving information */
       -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_start_date,
       msdb.dbo.backupset.backup_finish_date,
       -- msdb.dbo.backupset.expiration_date,

       CASE msdb.dbo.backupset.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Diff'
            WHEN 'L' THEN 'Log'
       END  AS backup_type,
       -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
       msdb.dbo.backupmediafamily.logical_device_name,
       msdb.dbo.backupmediafamily.physical_device_name,
       -- msdb.dbo.backupset.name AS backupset_name,
       -- msdb.dbo.backupset.description,
       msdb.dbo.backupset.is_copy_only,
       msdb.dbo.backupset.is_snapshot,
       msdb.dbo.backupset.checkpoint_lsn,
       msdb.dbo.backupset.database_backup_lsn,
       msdb.dbo.backupset.differential_base_lsn,
       msdb.dbo.backupset.first_lsn,
       msdb.dbo.backupset.fork_point_lsn,
       msdb.dbo.backupset.last_lsn
FROM   msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
            ON  msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

        /* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
        -------------------------------------------------------------------------------*/
WHERE  1 = 1

       /* ----------------------------------------------------------------------------
       WHERE statement to find Device Backups with '{' and date n days back
       ------------------------------------------------------------------------------- */
       -- AND     physical_device_name LIKE '{%'

       /* -------------------------------------------------------------------------------
       WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b
       ---------------------------------------------------------------------------------- */
       -- AND     physical_device_name  LIKE '[fF]:%'                          -- STANDARD F: Backup Directory
       -- AND     physical_device_name  NOT LIKE '[nN]:%'                      -- STANDARD N: Backup Directory

       -- AND     physical_device_name  NOT LIKE '{%'                          -- Outstanding Analysis
       -- AND     physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
       -- AND     backupset_name NOT LIKE '%Galaxy%'                           -- CommVault Sympana Backup

       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b
       ----------------------------------------------------------------------------------
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- 7 days old or younger
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE())  -- n days old or older

       */

       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for (a) given database(s)
       ---------------------------------------------------------------------------------- */
       AND database_name IN ('AdventureWorks2012') -- database names
       -- AND     database_name IN ('rtc')  -- database names

        /* -------------------------------------------------------------------------------
        ORDER Clause for other statements
        ---------------------------------------------------------------------------------- */
        --ORDER BY        msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause

        ---WHERE msdb..backupset.type = 'I' OR  msdb..backupset.type = 'D'
ORDER BY
       --2,

       2       DESC,
       3       DESC

Caution: The where clause currently select the AdventureWorks2012 database

Broken Transaction Log Chain

The (transaction) log chain is never broken unless one of the following conditions is met:

a Transaction Log backup file was deleted
a Transaction Log backup file is not accessible (somewhere in a backup device; 3rd-party backup solution)
the database is in SIMPLE recovery model
a Transaction Log backup was performed with the option TRUNCATE_ONLY
a FULL Database backup was taken without the COPY_ONLY option and was then deleted from disk because the developers only needed a quick backup to analyse a situation in the database and your FULL backup before that was deleted by (a) backup procedure.

Your Situation

In the screenshot you provided you have a FULL backup of the database that is is_copy_only and shortly after a FULL backup that is not is_copy_only. Now what you don't know:

Is the second FULL, non-is_copy_only backup a snapshot or not?

If you use my script from above and change the WHERE clause to match your database name, you might find out that that FULL backup that is not is_copy_only might just be a is_snapshot.

And that might just open up a new question:

Will the FULL, is_snapshot database backup of my database break the log backup chain?

But...

....whichever way this goes, as long as you have an unbroken chain of FULL and TLOG backups you can access, you can still restore your database to any point in time, even if you have another FULL backup in-between.

You can verify this with the output of my script for your database, by looking at the first_lsn and last_lsn numbers. They should match up, even when bypassing a FULL backup.

Better Be Safe Than Sorry

I have an AdminDB2 database on one of my instances. I created a TLOG backup, modified data, performed a FULL backup, modified data, performed a TLOG backup, ....

Lets have a look at my backup history of my AdminDB2:

dbname    backup_start_date       backup_finish_date            type    Log   physical_device_name                                          C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-25 17:29:08.000 2018-04-25 17:29:08.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172908.trn   0   0   36000002022400042   36000002022400042   NULL    36000002021900001   NULL    36000002025100001
AdminDB2    2018-04-25 17:28:48.000 2018-04-25 17:28:48.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172848.bak   0   0   36000002022400042   36000002018900037   NULL    36000002022400042   NULL    36000002024200001
AdminDB2    2018-04-25 17:28:23.000 2018-04-25 17:28:23.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172823.trn   0   0   36000002018900037   36000002018900037   NULL    36000002021500001   NULL    36000002021900001
AdminDB2    2018-04-25 17:28:07.000 2018-04-25 17:28:07.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172807.trn   0   0   36000002018900037   36000002018900037   NULL    36000002018400001   NULL    36000002021500001
AdminDB2    2018-04-25 17:27:32.000 2018-04-25 17:27:32.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak   0   0   36000002018900037   36000001990800037   NULL    36000002018900037   NULL    36000002020600001
AdminDB2    2018-04-25 17:15:00.000 2018-04-25 17:15:00.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_171500.trn   0   0   36000002016000003   36000001990800037   NULL    36000002018100001   NULL    36000002018400001

The order is date descending

You can see the last TLOG backup at the top, the previous FULL (in-between) backup at 2018-04-25 17:28:48.000, the previous TLOG backup at 2018-04-25 17:28:23.000,  and so on.

To restore the AdminDB2 database to the current point-in-time I would have to use the first FULL backup from 2018-04-25 17:27:32.000 (because I deleted the in-between FULL backup) together with all the TLOG backups.

Let's give that a try.

Delete the FULL backup file AdminDB2_FULL_20180425_172848.bak on my disk (or rename it), just because it is the one in-between.
Open up the Restore GUI in SSMS and add ..

the FULL backup AdminDB2_FULL_20180425_172732.bak
all the TLOG backup files

AdminDB2_TLOG_20180425_172807.trn
AdminDB2_TLOG_20180425_172823.trn
AdminDB2_TLOG_20180425_172908.trn

Make sure i set the option Overwrite the existing database (WITH REPLACE)
Perform the restore (or script the statement out into a query window)

Script

USE [master]
RESTORE DATABASE [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5, REPLACE
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172807.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172823.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172908.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Output

15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
100 percent processed.
Processed 848 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE DATABASE successfully processed 850 pages in 0.134 seconds (49.502 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.027 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.005 seconds (0.390 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.125 MB/sec).

...and the database is back ONLINE.

Summary

The backup chain only breaks when you lose the TLOG backups in-between, other than that you can restore a database from a FULL backup a long time ago and just add all the TLOG backups.

However...

...it is faster to have a more recent FULL, DIFF and TLOG backups handy.

Additional information in response to comment: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query

Backing Up Transaction Log With Truncate_only

In previous versions of SQL Server prior to SQL Server 2008 you could use the following statement:

 BACKUP LOG [AdminDB2] WITH TRUNCATE_ONLY

This has been deprecated and is no longer supported. You will receive an error message like the following:

Msg 155, Level 15, State 1, Line 10
'TRUNCATE_ONLY' is not a recognized BACKUP option.

The new method is to backup to disk NUL and is performed with the following command:

BACKUP LOG [AdminDB2] TO DISK='NUL'

This will return the following information:

Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.001 seconds (1.464 MB/sec).

  Advisory
  Do NOT use this in production. You will lose the ability to restore to a point-in-time during that TLOG backup.

Reference: BACKUP (Transact-SQL) (Microsoft Docs)

Your backup history will show this as:

dbname      backup_start_date       backup_finish_date      type ldev  pdev C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-26 09:35:05.000 2018-04-26 09:35:05.000 Log NULL    NUL 0   0   36000002030100002   36000002022400042   NULL    36000002033400001   NULL    36000002033700001

The information for the logical_device_name (ldev) and physical_device_name (pdev) will both contain the value NULL. This is a sign that a BACKUP LOG ... was performed with a TRUNCATE_ONLY (new: TO DISK='NUL'). You will have lost the ability to restore past this point using Transaction Log backups.

Additional information in response to comment: Yes - this was a is_snapshot = 1 [backup]

is_snapshot

Please read the section is_snapshot in my answer to the question Use of third-party VSS backup plus native SQL backup

From my original answer:

  If the database backup history has the flag is_snapshot set to 1 then you know that this backup was performed using a 3rd-party software that triggered the SQL Server Writer (VSS Service for SQL Server) which allowed the 3rd-party software to backup the database almost instantaneously.

  From the official documentation on what Snapshot Backups are:

    SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

  Reference: Snapshot Backups (Microsoft Technet)

  A backup created using this feature can also be restored almost instantaneously.

  Summary

  The 3rd-party backups should be marked as is_snapshot = 1 and is_copy_only = 1. These backups will not conflict with additional backup steps/procedures performed using native SQL Server BACKUP DATABASE..., BACKUP DATABASE ... WITH DIFFERENTIAL.... and BACKUP LOG... statements. The 3rd-party database backups are not part of an existing backup set.

I hope this information is sufficient.


Migration of table from MySQL to Oracle using GoldenGate I have a table of size 120 GB in MySQL 5.7 which I need to migrate to Oracle 12c. I am using Oracle GoldenGate for initial load of the table.

I tried with initial load of full table but it consumed complete RAM and I had to cancel it.

Even trying to run with primary key range initial load for 1/20th of table at a time, but it abends after certain time with:

  ERROR   OGG-01233  Send TCP params error: TCP/IP error 110 (Connection timed out)

Is there any better approach to get this completed?

From my experience with GoldenGate, it's better to do initial loads with database native tools.  In this case, you would use:

SELECT * FROM TABLENAME INTO OUTFILE 'filename';

https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Then load into Oracle with SQL*Loader.

Example control file for SQL Loader:

$ cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Example taken from:
https://www.thegeekstuff.com/2012/06/oracle-sqlldr/

If you cannot stop data changes to perform the load, you need to:

Start your extract
Export the data
Load the data
start your replicat with HANDLECOLLISIONS enabled

After your replicat has caught up, remove HANDLECOLLISIONS.

For some good Tutorials, I've used https://gavinsoorma.com/category/goldengate/

Also the Oracle TechNet forums were responsive for GoldenGate questions - even for some really obscure scenarios.


MariaDb: right syntax of PREPARE statement for CONNECT type tables when passing string variable I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am fighting with the right syntax of PREPARE statement for CONNECT type tables.

Having a table named fff which is ENGINE=CONNECT TABLE_TYPE=ODBC , I try to run prepared statements on it.
While I have no trouble to run statement with integer type parameter:

set @createStr = ("select * from fff where reltpfak = ? ");
prepare theSQL from @createStr ;
set @i = 1;
execute theSQL using @i;
-- works!

I can not find a right syntax to pass string variable. My trials lead to an error:

set @createStr = ("select reltpfak from fa where Firma = ? ;");
prepare theSQL from @createStr ;
set @i = "NameOfCompany";
execute theSQL using @i;

returns Incorrect arguments to EXECUTE .

The same one just with added ' around '?'

set @createStr = ("select reltpfak from fa where Firma = '?' ;")
prepare theSQL from @createStr ;
set @i = "NameOfCompany";
execute theSQL using @i

returns Error: Got error 174 'SQLExecDirect: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Firma= NameOfCompany'.' from CONNECT .

What is the right syntax of PREPARE statement for CONNECT type tables when passing string variable?

This looks like a bug as you can run this code just fine against an InnoDB table. It should work the same way against a Connect table, but apparently it doesn't. So I'd suggest you file a bug report in the MariaDB Server Jira project.


What is the most efficient way to merge topics of a forum or blog? Lets say we have a topic table, and we want the ability to merge topics, such that all posts from x topics about the same thing will appear in one single topic( the oldest one). So, when someone clicks on a redundant topic that was merged, they'll be brought to the oldest one, with all the posts from the newer one included.

My idea was to create a merge table, that contained the following attributes: merge_id(which is a primary key, serial type, automatically incremented), topic_id, and date. So if five posts were merged, they'd have one record each in the merge table, all associated by the merge_id. Each topic would have a merge_id in its table as well, set to null if its not merged with another post.

However, lets say I need to merge another one with those five, I'd have to delete those five and create a new record for each, including the sixth one so that they'd all have the same merge_id, then update the merge_id on all the merged topics; all of this within a transaction.

This feels a bit cumbersome to me, and I wanted to know if someone knew of a much better way of implementing merging forum topics/threads.

Instead of storing the merge_id in the topic table store the id of the base topic, the one this topic's posts now appear under. A null would show the current topic has not been merged, or is the base topic into which others have been merged. To merge a new topic into an existing set would only require setting that one row's base_topic_id.


sp_addserver - Where does it add in which system table sp_addserver - Where does it add in which system table. if I use this in my current server configuration will it change the name of my server and how will it affect ?

sp_addserver is for adding servers - for anything other than the local server, it is adding a linked server. It puts server information in the catalog view (not system table) sys.servers. It does not affect the name of your current server unless you are actually using it to run sp_dropserver / sp_addserver after you have already renamed the underlying server (which you do to ensure functions like @@SERVERNAME return the right value) - this should be a rare incident.


Database tables design If I have, in my Java code, the following classes:

Patient :contains instance of anther classes (mentioned bellow)
PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age.
PatientImageFinidgns: contains Date, comments, lesionSize, site, procedure.
PatientLabFindings: contains Date, t1,t2,t3,t4.
PatientTreatment: contains visitNumber, Date, some other data.

Id is the primary key for Patient and Date is very important key in both PatientImageFindings and PatientLabFindings. visitNumber is my primary key in PatientTreatment.

How could I design these classes in a database. Should I create a table Patient, which holds only foreign keys of the other classes (like ID, Date, VisitNumber) or should I put PatientId in the table Patient as a primary key and as a foreign key in the PatientBasicInformation table. If so, what would be the primary key in the PatientBasicInformation table?

I want appreciate a simple and clear answer as I'm a new to databases and I have read a lot of articles, but I still don't get the whole picture.

Put simply you should design your database so that all information is stored with a its natural level.

In your case, I would create a table Patients. Each row in that table should store all the pieces of information that are relevant to that patient and not dependent on anything else. The other tables then follow on from this. For instance, PatientVisits, the unique key would be the identifier of the visit; but you need to know what patient this visit relates to so you should also have a foreign key back into Patients. You schema then looks something like this:

Patients

Id - primary key, auto-increment as there's no natural key
Phone number, name, gender, date of birth.

Never store age, you have to re-calculate it each and every day. Store the date of birth instead so that you can easily work out the current age.
ImageFindings

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
created, comments, lesion size, site, procedure

I've renamed date to created as it's a reserved word and really quite confusing to use in a database. I doubt this should be the primary key as it's possible to insert two simultaneously
LabFindings

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
test - 1, 2, 3 etc -- foreign key to the primary key of Tests
test result
created

If a Image is dependent on a LabFinding then you should change the foreign key in ImageFindings to be the primary key of LabFindings. As you might have more than 1 test these should be split out into rows rather than columns as it might be possible for you to add more tests.
Treatment

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
more columns

Tests

Id - primary key, auto-increment as there's no natural key
Description
More data related to the test

You note that the primary key of every table is a surrogate key. This is because there is no natural key, i.e. one that should obviously be the primary key of the table. You ask what the primary key of PatientBasicInformation (Patients) should be; it can't be anything but a surrogate key as you can't rely that you will never have a patient with the same name as another, or the same name and date of birth etc. The naming conventions of people are inherently chaotic and so you can't rely that people will nicely fit into your database.

I would strongly suggest reading up about the Third Normal Form and trying to understand it.


Does 'Cities' deserve a separate table? I have the following Customers table:

customer_id - int
company_name - nvarchar
street - nvarchar
city - nvarchar
comments - nvarchar

The app will only be used in part of one small country (something like 30 cities). A friend told me I should seperate 'city' into a different table 'Cities' and use only city_id in customers table.

Personally I didn't see much of a benefit from it (except for saving some space on Customers table which seems insignificant to me in this case for the cost of creating another table).

He also mentioned that because I have duplicate columns - city: foo, city: bar, city: foo. (few customers in the same city) this is not considered normalized, is this true?

Who's right? Any enlightenment on the issue?

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally.

I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store.

The biggest piece of the puzzle here is to move all address-related fields out of the Customers table -- addresses are entities unto themselves.

The space cost may not be relevant in a very small system (maybe), but this is more about a technical debt issue. If you need to start adding more address-related fields, you would have to keep adding more and more of them to the Customers table. Sooner or later, you'll realize that this is an inflexible design -- if you need to use multiple addresses for a given customer (billing & shipping addresses being the classic example), now you're in a world of hurt without normalizing, as you can't reuse the existing structure to store the required data.

At an absolute minimum, create a new table Addresses, and then reference address_id from Customers. If you want to go the multiple address route eventually, even doing just this step (as opposed to sticking with the current design) will save a massive headache later.

The address line could go in either the Addresses table directly for simplicity, or in a separate Address_Lines table to handle multiple lines. (The latter is usually preferred.)

After that, a general minimum for being able to slice and dice your data in a meaningful way is to construct normalized Countries, Regions (aka provinces/territories/etc.), and Cities tables, with only the latter appearing as a field in the Addresses table. This lets you ask business questions like "how many products did we sell in city X?" and "how many products did we sell in region Y?". (Note: depending on where you operate, what data you have, and how the data will be sliced, you may require a 4th table in there between Regions and Cities.)

If you need to get more granular ("how many products did we sell to customers on street X?"), then you'll have to start componentizing the address lines themselves, which is the really difficult part. Usually, though, a business won't ask this kind of question. Given that I don't even see a postal code field, I'm guessing this is not something you care about.


Join two tables in MYSQL and avoid duplicate values I created two tables using MYSQL, tblorder and tblitem.
CREATE TABLE `tblorder` (
`orderId` int NOT NULL,
`orderName` varchar(45) NOT NULL,
PRIMARY KEY (`orderId`)

CREATE TABLE `tblitem` (
`itemId` int NOT NULL,
`itemName` varchar(45) NOT NULL,
`itemUnit` varchar(5) NOT NULL,
`itemRate` double NOT NULL,
`orderRef` int NOT NULL,
PRIMARY KEY (`itemId`),
KEY `fk1_idx` (`orderRef`),
CONSTRAINT `fk1` FOREIGN KEY (`orderRef`) REFERENCES `tblorder` (`orderId`)

I tried to join the two tables using query below
SELECT orderId,orderName, itemName, itemUnit,itemRate
FROM tblitem
INNER JOIN  tblorder on tblorder.orderId = tblitem.orderRef

Now result show like Image-01

How to remove duplicate values in orderId ,OrderName columns in result table? Thanks to help me to solve this problem.
After joining tables is it possible to get output like below?

Output from new Query

Do this "deduplication" on the application side. It will be much easier to clean up repetitive values with PHP or JS. It is possible to make what you want by UDVs but SQL wasn't intended for it.


How to import a single table in Windows environment I am running Windows 7 with xampp. I can easily import a database with the following command:

mysql -u user -p database < backup.sql

The problem is that I have a database around 8.5 GB, which take a long long time to import. Mostly I just need to restore a single table from it.

How can i restore single table in this environment?

This issue has been dealt with here. The approach described therein assumes that you have already separated your schema dump by filename.

However, if I've understood you correctly, you have a schema dump from which you wish to extract a single file? I have written a small tool which extracts individual table data separately from a schema dump file.

It's called MySQLDumpSplitter. If it doesn't suit your needs, there are two others with the same name also on Github. A Google of "mysqldump file split" or similar will lead you to other tools.

Once you have your Table_name.sql file, just follow the steps outlined in the first link.

DISCLAIMER: I wrote the linked tool. Its limitations are described on the page - you may find others which work better for you.


InnoDB: Error: Table “mysql”.“innodb_table_stats” not found I've got the same error, although I'm running galera cluster with 3 nodes.
I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 but when I get to the step of running the create statements, I get the following error:

Error Code: 1813. Tablespace for table '`mysql`.`innodb_index_stats`' exists.
Please DISCARD the tablespace before IMPORT.

This made no sense to me, and when I tried to use

drop tablespace `innodb_index_stats`;

I got an SQL sysntax error.

The problem is actually very simple. Here is what happened

When you installed MySQL, the 5 InnoDB systems tables exist in two places

inside /var/lib/mysql/mysql as 5 .frm and 5 .ibd files
inside the data dictionary within ibdata1 (InnoDB System Tablespace)

At some point in your installation, you must have deleted ibdata1. This left the 10 InnoDB system table files inside /var/lib/mysql/mysql with no data dictionary entry.

SOLUTION

cd /var/lib/mysql/mysql
rm -f innodb_index_stats.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.frm
rm -f innodb_table_stats.ibd
rm -f slave_master_info.frm
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.frm
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.frm
rm -f slave_worker_info.ibd

Then, login to MySQL and run the steps from my post : InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

GIVE IT A TRY !!!


how to find oracle calculated/computed column list using data dictionary views I would like to find computed column list in oracle database using Oracle Data Dictionary Views.

I would like to add more information. Suppose I have following computed/calculated column in database.

ALTER TABLE HR.EMPLOYEES
 ADD FULL_NAME AS ( FIRST_NAME || ' ' || LAST_NAME);

I can select this column value in Select Statements.

SELECT EMPLOYEE_ID,FULL_NAME FROM HR.EMPLOYEES;

Results

  ...
   174 Ellen Abel
  ...

I can see this column in TAB_COLUMNS view.

 select * from all_tab_columns   C
 WHERE
 1 = 1
 AND  C.table_name = 'EMPLOYEES'
 AND C.OWNER = 'HR'
 AND COLUMN_NAME = 'FULL_NAME'
 ;

I would like to find given schema, table and column find that if this column is calculated/computed?

Wrongly I thought that INDEXES view gives me this information. But following select returns no rows. This gives only functional indexes.

SELECT index_name,index_Type,I.*
FROM ALL_indexes I
WHERE
1 = 1
AND INDEX_TYPE LIKE 'FUNCTION-BASED%'
AND I.OWNER = 'HR'

The column USER_TAB_COLS.virtual_column = 'YES' indicates if it's a virtual column.

You can get the expressions for each column with:

SELECT *
FROM user_tab_columns
WHERE data_default IS NOT NULL;


Empty string values - PostgreSQL 9.2 I've got some empty string values being putting into my DB.
Code I've used:

CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$
      SELECT
        CASE
          WHEN length($1::VARCHAR) = 0 THEN NULL
          ELSE
            (SELECT array_agg(get_byte($1::bytea, a)) FROM generate_series(0, length($1::bytea) - 1) a)
        END;
    $$ LANGUAGE SQL;

    SELECT get_as_byte_array(name_first), * FROM ja_customers WHERE id = 8154501;

Question:
How can I find the responsible statement that is causing this issue?
Would be possible to create a trigger to do that? If so, how?

UPDATE 1: According to some users, I can create a CONSTRAINT. This is even better indeed...

Could be something like:

ALTER TABLE public.ja_customers
      ADD CONSTRAINT cc_check_empty_strings CHECK (("name_last" != '',"company" != '',"address" != '');

?

This is related to my previous question: Blank (but not null) columns - PostgreSQL 9.2

A constraint has been made to solve this issue:

 ALTER TABLE public.ja_customers
      ADD CONSTRAINT cc_check_empty_strings CHECK (("name_last" != '' AND "company" != '' AND "address" != ''));


Review database schema / thought process Please review my thoughts below. It is written in an authoritative style because it is the most succinct, not because I am competent. Please poke as many holes as possible.

I am trying to create a database to store graphs. Each graph is represented as a set of Nodes and Relations:

Node (Table)
id (PK)

Relation (Table)
id (PK)
from (FK Node)
to (FK Node)

It should be possible to create multiple graphs, and no information is shared between the graphs. This gives me the following options:

Create a new database for each graph

Simple: Pick the right database at the start of the request, and we are done.
Scalable: Possible to split the graphs easily across different servers.

Create a new set of tables for each graph, i.e., Node_{graphid} and Relations_{graphid}

Simple: Need only a single database.
Complex: Must always transform the table names.

Modify the schemes above to have a graphid

Simple: No need to transform table names.
Complex: Queries are more complex, since we must always account for the graphid.
Expensive: Need to index on graphid, or do some other tricks?

A new database for each graph seems the best choice, and is assumed in the discussion below.

Users will create and share graphs, which leads to two levels of access:

Access (Table)
id (PK)
graph (database id for graph)
edit (FK Group)         # allows editing of graph
view (FK Group)         # allows viewing of graph

A user can be part of many groups:

User (Table)
id (PK)

Group (Table)
id (PK)

User_Group (Table)
id (PK, is this really needed?)
user (FK User)
group (FK Group)

Some graphs will be public. Can use either:

NULL means public.
Add every user to public group by default.

Trade offs (approached from NULL means public):

Saves storage: but a laughable amount (assuming 1 million users: 1e6 * (8 + 8) / (1024 * 1024) = 15 MB)
Complex: Some queries need to check for NULL explicitly (while the same code would handle both public and restricted group otherwise)
Simple: Easier to deal with users that do not have an account (only access to graphs with NULL group).
Faster: To answer the query: ''Can the user view this particular graph?'', we will not always have to check the User_Group table.
Implicit information: NULL has a special meaning that I will need to document somewhere.

Gracefully handling users without account (otherwise I have to always keep in mind what particular ID belongs to the public group), is enough to sway this in favor of 'NULL means public'.

The backend will be written in Python, with either PostgreSQL or MySQL as database management system. Ideally users will be able to edit graphs concurrently.

Recommendations are as follows:

For access control use the groups instead of individual users. Create a group called public to which all users belong therefore any graphs assigned to public will be visible. Look to Role Based Access Control (RBAC) for more on this
Each graph can be accessed by one or more groups, which allows you to have graphs that are visible to different cross sections of users (through groups). This means your access table will be graphid, groupid, edit, view with a unique key constraint on graphid and groupid.
Answering the question can a user edit this graph will require a join from User_Group to Access table
There is no special meaning for nulls, as none exist
Handling users with no account is easier since no authentication is provided
For the graphs, you need a graph table, and also a graphid added to the node and relation tables to simplify queries.


ORA-12154: TNS:could not resolve the connect identifier specified I've initially posted this question on serverfault.com, but because lack of answers decided to post it here...

I'm trying to use bat file/CLI to run SQL script against certain Oracle database with following syntax:

sqlplus -L user_name/database_name@Password @d:\my_bat_file\sql_script.sql

If I run this following error is returned:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0751: Unable to connect to Oracle. Exitig SQL*Plus

At the same time tnsping database_name works fine & corresponding connection configuration strings are present in tnsnames.ora

Moreover if I type commands manually in a following way:

sqlplus /nolog
connect user_name@databasename
Passowrd

I can connect to the database too. Any idea why I'm getting error when trying to use command I mentioned in beginning & how to fix it?

P.S. I'm doing this from Windows Server 2003 SP2 box with Oracle client v10 installed.

As Munchi pointed out the issue was caused by wrong syntax. I used:

sqlplus -L user_name/tns_alias@Password @d:\my_bat_file\sql_script.sql

Instead of:

sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql


How to automate creating credentials in a secure way We have created an automated process to stand up a new SQL Server using TFS and powershell.  As part of a new server build, we deploy a database and a set of jobs for dba use.  As part of the install we need to create a credential for a sql agent proxy for some of our jobs.  However, we don't want to store in passwords in code.

Is there a solution to storing passwords (or generating a password) where we aren't storing it in plain text or in line in the code?

Our servers are on-prim, not in Azure.  We have access to Keepass, but that is being deprecated for Thycotic.

You can leverage PowerShell and the Thycotic REST API to fetch your credentials from Secret Server programmatically. Use "Windows Authentication" to avoid having to store credentials in the PowerShell scripts, or enter them during runtime.

If your deployments run under a build agent service account context, then you need to ensure that service account has access in Secret Server to the credentials you want to access.

Leveraging the API as a step in your release prior to deploying the SQL credential, you can fetch the credential and pass it to the PowerShell script that deploys the credential.


How to prevent different connections treating bytea in same query differently? I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.
A query that demonstrated the behaviour is this:
INSERT INTO "TestTable" ("Input") VALUES (decode('74657374', 'hex'))
74657374 is hexadecimal for 'test'. In one client, 'test' is inserted into the "Input" field, whether that field is text/varchar or bytea. That is the behaviour I desire. In another client, '\x74657374' is inserted into the "Input" field, whether it is text/varchar or bytea. This string is the postgresql literal representation of the bytea bytes of ASCII 'test'. But the sql literal syntax itself is inserted. I do not desire this behaviour.
I use a single piece of hand-written SQL, the bytea value only occurs "within" the query (if column "Input" has type Text then none of literals nor the receiving column have bytea type), and it seems unlikely to me that either client is parsing then rebuilding the query. Therefore it seems that the difference must be happening on the server where the query is executed. That means there must be some connection specific configuration setting which is altering the server behaviour.
Can anyone please tell me what connection specific settings could be altering this behaviour?
I know the queries are really behaving differently and it is not a display issue etc., because I can see the rows in the same table having different values ('test' and '\x74657374'). I have tried various alternative bytea handling methods, but they are all affected by this problem. For those who are interested, the "good" client is pgAdminIII and the "bad" client is Ruby PG gem. Though for the reason I gave above I believe there must be some built-in feature of postgresql supporting this behaviour.

I suspect that the difference is the appearance of a bytea depending on the current setting of bytea_output:
CREATE TABLE test (id integer PRIMARY KEY, t text, b bytea);

SHOW bytea_output;

 bytea_output
--------------
 hex
(1 row)

INSERT INTO test VALUES (1, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;

 id |     t      |     b
----+------------+------------
  1 | \x74657374 | \x74657374
(1 row)

SET bytea_output = 'escape';

TABLE test;

 id |     t      |  b
----+------------+------
  1 | \x74657374 | test
(1 row)

INSERT INTO test VALUES (2, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;
 id |     t      |  b
----+------------+------
  1 | \x74657374 | test
  2 | test       | test
(2 rows)

RESET bytea_output;

TABLE test;

 id |     t      |     b
----+------------+------------
  1 | \x74657374 | \x74657374
  2 | test       | \x74657374
(2 rows)

The text column actually contains the result of the type cast from bytea to text, which depends on the current setting of bytea_output.
The bytea column contains the correct four bytes, but is displayed differently, depending on the current setting of bytea_output.


Duplicate tables and procedures in master database I've just discovered, that master database contains duplicates of some other databases' tables and stored procedures.

For example, I have a database SomeDB with table justTable in it. For some reason, a copy of this table is in master as well, but with no data.

How is it possible? I'm certain that when I was creating those tables and procedures I was using corresponding databases and not master. They are all present in those databases (as they should be), just with a copy in master as well.

I use Microsoft SQL Server Management Studio 2014 with Redgate additions (source control and stuff).

Did you create the table with a script run through SSMS?

Is it possible that if you did you didn't include USE [SomeDB] at the top of the script and instead had the current DB set to master?

So doing this:

Instead of this:

You can see when a specific table was created by looking at the system views, as in:

SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , o.create_date
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type_desc = 'USER_TABLE';

This may help you understand where/when those tables were created.

Results from the query:

If you really need to prove where these are coming from, you probably want to create a DDL trigger at the server level to capture events such as CREATE TABLE etc, however that is beyond the scope of this answer.  A Server-level DDL Trigger would allow you to capture all data-definition events for future events; i.e. it wouldn't help solve where these existing tables came from.


Help with reading xml I am struggling to extract data from an XML file. The file for all intense and purposes looks like this:

<Message>
  Hello
  bye
</Message>

At the moment I am using the Value function.

However the data I get returned is only ever "Hello". How do I return all data in a tabular format, like so:

If your question is about MS-Sql you can use this syntax:

DECLARE @data xml;

SET @data =
'<Message>
  Hello
  bye
</Message>';

SELECT d.lin.value('.', 'varchar(100)') as [Code]
FROM   @data.nodes('Message/Code') d(lin)
GO

| Code   |
| :----- |
| Hello  |
| bye    |

dbfiddle here


SQL Server TDE Vs SQL Server Database Backups Can anyone please help me understand below case:

Kindly advise on below case:

SQL Server is on 2014 SP2 GDR version

I read Compression and TDE does not go hand in hand .

I have 2 PRD Environments:

The table structures between server 1 and Server 2 are same.

With DB Size around 400 GB in Server 1 with FileStream Enabled.
FileStream content sized around 300 GB.
With DB Size around 687 GB in Server 2 with FileStream Enabled.
FileStream content sized around 600 GB

Currently the above Databases are with TDE Enabled.

When I backed up below are the details when they are NOT on TDE:

With DB Size around 400 GB in Server 1 took around 51 Minutes

With DB Size around 687 GB in Server 2 took around 108 Minutes

When I backed up below are the details when they are WITH TDE:

With DB Size around 400 GB in Server 1 took around 57 Minutes

With DB Size around 687 GB in Server 2 took around 207 Minutes

My Observations

In Server 1 with same specifications as Server 2 , time taken is 51 Minutes (Pre TDE) --> 57 Minutes after TDE Enabled

In Server 2 with same specifications as Server 1 , time taken is 108 Minutes (Pre TDE )--> 207 Minutes after TDE Enabled

My Question
Why is that difference when compared to time taken is almost doubled in Server 2 but Server 1 it is only 7 Minutes .

NOTE: Also , my Databases are with TDE + EFS (Encrypting File System) when backed up.

Kindly advise , as Compression and TDE does not go hand in hand . What is the reason why in Server 2 the time taken is DOUBLE , where as in Server 1 it is only 7 Minutes after TDE enabled.

Regards

Based on the comments so far -

SQL server has no knowledge of how EFS does encryption. Turning off EFS - the timing is reduced almost similar to pre-TDE timing (confirmed by OP).

This means that EFS is slowing down the backups.

Remember that both EFS and TDE will protect data at rest. So, for sql servers, you should have a global policy to exclude sql server filesystem from EFS.


SQL Formatting: Add Space within query result I need to insert a space into result set from a query, I tried using the space() function and right but I have confused myself.

The Column result from the query is:

1234567891234567891

I need this:

1234 567891234567891 (With a space after the 4th number)

You can do this several ways. I'm assuming this is stored as a number and not a string, and I'm using RTRIM to prevent invalid datatype errors.

DECLARE @i BIGINT = 1234567891234567891

SELECT LEFT(@i, 4) + ' ' + RIGHT(@i, LEN(@i) -4) AS lefty_righty

SELECT SUBSTRING(RTRIM(@i), 0, 5) + ' ' + SUBSTRING(RTRIM(@i), 5, LEN(@i)) AS substrings

SELECT STUFF(RTRIM(@i), 5, 0, ' ') AS stuffy


Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)? Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart?

Query plans? Yes.
Table/index data? Yes.
Statistics? No.

(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created unless there was also some event or condition to trigger that (there are several).)

If you stop the service while a transaction is rolling back, you likely haven't accomplished anything, since rollback may just start again from where it left off (or it may have to start over completely, depending on the type of activity that is getting rolled back).

If you restarted the service because you got impatient waiting for a rollback to complete, that's probably an expensive lesson you'll only have to do one or two times before you stop trying. See:

Why did a SQL Server Restart roll-back seemingly committed transactions?
KILLED/ROLLBACK query doesn't stop running
Rollback taking forever
SQL Server 2008 R2 - Service shutdown - what with running stored procedures?
https://dba.stackexchange.com/a/160293/1186


How do I get article properties from query in a merge replication I would like to know if there are any queries to get article properties in a SQL Server merge replication. I'm able to get properties only by GUI. I select a publication properties then I get the page properties, then I select an article (a table) then if I want to check the Article Properties, I select Set Properties of Highlighted Table Article I get the four group of properties like as Copy Objects and Settings to Subscriber, Destination Object, Identification and Merging Changes.
Anybody knows if there are any queries to get that properties or to get a script that create the article with of all of these properties?

This is covered in View and Modify Article Properties, in the section To view the properties of an article belonging to a merge publication:

Execute sp_helpmergearticle, specifying the name of the
publication for the @publication parameter and the name of the
article for the @article parameter. If you do not specify these
parameters, information will be returned for all articles in a
publication or at the publisher.

If you want to generate scripts to create replication components, this is covered in Scripting Replication.


Can sql server logins stored in the master db/msdb be used for migration? In sql server when we expand security, expand logins, there is list of logins.
Are all these stored in the master db?
If so, then when migrating to another server, if we backup this master db and restore to the new server, then will the logins get migrated to the new server?
Similarly SQL server jobs, schedules, etc are stored in the msdb? Can this be backup/restored to migrate the jobs to the new server?
If not, then what is the purpose of doing a backup of these system dbs?

Simply answering your questions is not the solution to your questions.

Are all these stored in the master db?

Yes. (Well sort of)

If so, then when migrating to another server, if we backup this master db and restore to the new server, then will the logins get migrated to the new server?

No. It is recommended to transfer the SQL Server Logins using the procedure documented by Microsoft. See also the Q&A Transfer logins to another server which includes various links and where they discuss in the comments, that restoring the master database can break things.

Similarly SQL server jobs, schedules, etc are stored in the msdb? Can this be backup/restored to migrate the jobs to the new server?

I would also recommend to right-click the jobs and script them out to ensure they are created correctly on the new server.
Even though the msdb is slightly less relevant for a functioning SQL Server instance, the newer versioned msdb could still contain elements (columns, tables, ...) that are not available in older versions.

If not, then what is the purpose of doing a backup of these system dbs?

When your server system crashes and you want to restore the whole SQL Server instance, then you would require the master database to restore all the databases, logins, etc. and the msdb database to have all the jobs back.


Database Offset & Limit without order by? To my understanding, in MySQL/Postgres database there is no guarantee for the order of values with a select statement without an order-by clause, thus one should be getting rows arbitrarily. If so, then why are there usually no duplicates when I perform such query?

SELECT * FROM `table` LIMIT 0,5
SELECT * FROM `table` LIMIT 5,5
SELECT * FROM `table` LIMIT 10,5

Aren't these queries safe to use, considering that one only cares about not fetching duplicates and does not mind about the ordering? If so, how then?

Your syntax won't work with PostgreSQL, but I can answer your question for PostgreSQL anyway:

Your query will often work as you intend because the three statements will often use the same execution plan. There is no guarantee for that though.

One possibility where even a sequential scan in PostgreSQL will return a different order is if there is already a sequential scan of the table in progress: Then PostgreSQL will start scanning where the other statement is currently scanning the table, i.e. somewhere in the middle, so that both scan can execute somewhat synchronized to reduce the I/O operations necessary.


Extracting date from file group name and converting to date fails when filtering In sys.sysfielgroups I have a list of filegroups with dates in them, and I'm trying to extract these dates.  Here's an example list of the file groups:

  Event_Header_Daily_LeftNull_FG

  Event_Header_Daily_2018_08_07_FG

  Event_Header_Daily_2018_08_08_FG

  Event_Header_Daily_2018_08_09_FG

  Event_Header_Daily_2018_08_10_FG  2018-08-10

This is the query that I'm trying to run.  As you can see, I'm trying to get any file group older than today's date:

SELECT *
FROM
(

    SELECT
        groupname,
        CAST(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) AS dt
    FROM sys.sysfilegroups
    WHERE groupname LIKE 'Event_Header_Daily_%'
        AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
) a
WHERE dt < GETDATE()

If i run the subselect without filtering the results, the query runs just fine.  However, when running the whole query I get the 7th, 8th, and 9th and the query errors out, stating:

  Conversion failed when converting date and/or time from character
  string.

As far as I can tell this should just work, but it doesn't and I'm at a loss as to why.

The problem is that for some values the resulting trim isn't a date so it can't be converted to one.

This is your current REPLACE function:

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_2018_08_09_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: 2018-08-09

This is the value in which it won't work (you filtered one but there might be others similar to this one):

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_LeftNull_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: LeftNull

SELECT CAST('LeftNull' AS DATE)
-- Conversion failed when converting date and/or time from character string.

TRY_PARSE() function won't throw an error whenever the cast can't be done, it will return NULL.

SELECT TRY_CAST('LeftNull' AS DATE)
-- Result: NULL

You should switch your explicit CAST with a TRY_PARSE:

SELECT *
FROM
(

    SELECT
        groupname,
        TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) AS dt
    FROM sys.sysfilegroups
    WHERE groupname LIKE 'Event_Header_Daily_%'
        AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
) a
WHERE dt < GETDATE()

If you want to know which values are giving casting problems, you can check where the TRY_PARSE IS NULL.

SELECT
    groupname,
    REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS dt
FROM sys.sysfilegroups
WHERE groupname LIKE 'Event_Header_Daily_%'
    AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
    AND TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) IS NULL


Cannot revoke default privileges from postgresql user I am using the psql tool:

Command:

my_db=# \ddp

Result:

                         Default access privileges
     Owner      | Schema |   Type   |          Access privileges
----------------+--------+----------+--------------------------------------
 postgres       | kpi    | function | kpi_updater=X/postgres              +
                |        |          | intranet2=X/postgres
 postgres       | kpi    | sequence | kpi_updater=rwU/postgres            +
                |        |          | intranet2=rwU/postgres
 postgres       | kpi    | table    | kpi_updater=arwdDxt/postgres        +
                |        |          | intranet2=arwdDxt/postgres
 postgres       | kpi    | type     | kpi_updater=U/postgres              +
                |        |          | intranet2=U/postgres

Next I use the following command, that returns without error:

ALTER DEFAULT PRIVILEGES IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

I run \ddp one more time and... I get exactly the same result: default privileges were not modified.

What am I missing here ?

Thanks !

Edited to answer the question related to the \ddp command not the \dp command as @personne3000 pointed out in the comment below.

You probably want to use ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

This is because postgres is the user that was granted the default privilege of execute on the functions in the schema kpi and is granting it to intranet2, as noted by the permissions of intranet2=X/postgres

I created a small example to illustrate what's going on.

pgsql@[local]:5432:pgsql:=# CREATE ROLE bob;
CREATE ROLE
Time: 0.526 ms
pgsql@[local]:5432:pgsql:=# CREATE SCHEMA we_like_bob;
CREATE SCHEMA
Time: 0.608 ms

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob GRANT EXECUTE ON FUNCTIONS TO pgsql;
ALTER DEFAULT PRIVILEGES
Time: 1.480 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

User pgsql has the execute privilege, X, granted by bob.

Try to revoke the execute privilege for the role pgsql, as pgsql (a superuser).

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE pgsql IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.176 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

Didn't work because the command is not removing the bob role's default ability to grant execute on functions to role pgsql, it is removing the pgsql role's ability to revoke execute on functions from pgsql.

If we change it to remove the privilege of execute from the bob role for the role pgsql, then it works.

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.644 ms
pgsql@[local]:5432:pgsql:=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

pgsql@[local]:5432:pgsql:=#


Source control makes sure my code is never lost, how do I make sure my users/orders database is never lost? Everyone is recommending the use of git, automated tests, continuous integration, automated deployment and so on, to reduce human error during all stages of web development.
I have yet to come across a clean, automated way of doing this for databases.
Currently, I'm using the Symfony2 framework for PHP and doctrine to create/manage my users database in MySQL. How do I make sure that my users & orders databases will always remain safely backed up without manual intervention?

You can dump databases with a cron job, but your database schema should be under version control just like all the other code.

For MySQL, see mysqldump.

Copy or move the backup files to a safe place. (Or to two different safe places.) The database server is not a safe place.


Well, MySQL isn't a specialty of mine, so there may be a better way of doing this. However, after checking to see how/if MySQL implemented various concepts (like SQL Server's ROW_NUMBER or LEAD functions), this appears to work.

This uses a trick, where the value of a variable is updated with each row, in sequence, and both the old and new values of the variable are included as columns. This lets us pick up the location from the previous row in the current row. (Note that I am not familiar enough with MySQL to know if this is a deliberate feature; in SQL Server, something similar works - but is not documented as a feature, and is generally thought of as something that could stop working with the next version.)

With that trick, we:

Limit our records to those that have one of the two locations we're interested in;
add the previous row's location to the current row; and
select the desired fields from the rows where the previous row's location is different from that of the current row.

Here's the code:

CREATE TABLE WhenWhatWhere (`when` datetime, `code` int, `location` char(1), CONSTRAINT PRIMARY KEY (`code`,`when`));

INSERT INTO WhenWhatWhere
VALUES ('2017-01-05 12:54', 102263, 'X')
      ,('2017-02-02 8:42', 102263, 'Y')
      ,('2017-03-02 12:04', 102263, 'Z')
      ,('2017-03-06 13:34', 102263, 'Y')
      ,('2017-03-07 7:57', 102263, 'Z')
      ,('2017-03-07 18:37', 102263, 'Y')
      ,('2017-03-09 17:47', 102263, 'Z')
      ,('2017-03-13 7:56', 102263, 'Y')
      ,('2017-03-21 11:44', 102263, 'Z')
      ,('2017-03-22 20:28', 102263, 'Y')
      ,('2017-03-24 13:07', 102263, 'X')
      ,('2017-03-25 14:01', 102263, 'Y')
      ,('2017-03-26 8:14', 102263, 'Z')
      ,('2017-03-27 9:52', 102263, 'Y')
      ,('2017-04-03 10:02', 102263, 'X')
;

SET @rownum = 0;
SET @loc1 = 'X';
SET @loc2 = 'Y';
SET @lastloc = '';

--
-- Select only the rows where the location changed from the previous row.
SELECT `when`, `code`, `location`
  FROM ( -- Add the location from the previous row; must be sorted to work
        SELECT `when`, `code`, @lastloc `lastloc`, (@lastloc := `location`) `location`
          FROM ( -- Select just the rows that deal with our two locations
                SELECT `when`, `code`, `location`
                  FROM WhenWhatWhere
                 WHERE `location` = @loc1 OR `location` = @loc2
                  -- add additional restrictions (like date range or specific code) here
               ) loc
         ORDER BY `when`
       ) comploc
 WHERE `lastloc` <> `location`
 ORDER BY `when`
;

It worked when I ran it at sqlfiddle.com.


How to update pg_opclass as non-postgres superuser I'm using poorly documented third-party software that does esoteric things with PostgreSQL 9.3.  At some point during its setup, it attempts the following:

UPDATE pg_opclass SET opckeytype=xxxx WHERE ...;

The statement is running as a database superuser (though not postgres), and fails with this error:

ERROR:  permission denied for relation pg_opclass

What permissions do I need to grant to allow this statement to work?

Note #1:  I tried granting all permissions on that table to no avail.
 \z from psql reports the following (zzz is my superuser's name):

mydb=# \z pg_catalog.pg_opclass
                                 Access privileges
   Schema   |    Name    | Type  |  Access privileges   | Column access privileges
------------+------------+-------+----------------------+--------------------------
 pg_catalog | pg_opclass | table | =r/postgres         +|
            |            |       | zzz=arwdDxt/postgres |
(1 row)

mydb=#

Note #2: The exact same statement, when run as the postgres user, works just fine.

It turns out there's a (somewhat hidden) permission for users, namely whether they have permission to "update system catalogs directly," which is specified by the rolcatupdate column in the pg_authid system table:

https://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html

This other answer talks about how to set it:

https://serverfault.com/a/300421

Anyhow, once I set it to TRUE for my user, the UPDATE was allowed.

That column in pg_authid appears to have gone away as of PostgreSQL 9.5.


Update all data in a table without affecting ID(PK) I'm using SQL Server 2005.

I have a table with 4846 customer records. The PK is called ID and spans from record 40003 to 79870.
This numbers are used a other tables to hold info about user orders, for example.
Within the table there's a decimal field that stores the amount of credit each user is entitled (CustomerCredit).

Now, the problem is I cant manage to come a up to a way of updating all of the CustomerCredit records without affecting the ID field... The only information that needs to change is the credit amount per each user.

Any clue will be more than appreciated.

I am not fully clear regarding where you get the new CustomerCredit value but an SQL update should never change ID unless you explicitly tell it to.

UPDATE [table] SET CustomerCredit = 100.0 WHERE ID = [customer-id-to-update]

or setting all records to the same value, just omit the WHERE

UPDATE [table] SET CustomerCredit = 100.0


How can I get SQL Server restore start and end time information? SQL Server error log reports only the end of the restore (ah doc restore)
How can I get the start time? Is it stored somewhere?

1.The restore history information is readily available inside the msdb i.e. msdb.dbo.restorehistory

You can use below T-SQL code to find the start time and restore information of a database over a required period.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -1 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
 WHEN rsh.restore_type = 'F' THEN 'File'
 WHEN rsh.restore_type = 'G' THEN 'Filegroup'
 WHEN rsh.restore_type = 'I' THEN 'Differential'
 WHEN rsh.restore_type = 'L' THEN 'Log'
 WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
 WHEN rsh.restore_type = 'R' THEN 'Revert'
 ELSE rsh.restore_type
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To]
 FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
 WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
 AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
 ORDER BY rsh.restore_history_id DESC

Alternatively you can use the approach of default trace as mentioned by Chris as well.


Oracle trigger to update columns daily I'm trying to create a trigger that would run twice daily and do updates based on a specific condition.

To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

We have Table A and Table B with columns

Primary Key
Date 1
Date 2
Date 3
Status

For a specified set of Primary Keys in Table A, until Table A.Status==Completed, I want the Date fields to be copied over from Table B to Table A twice daily. The same primary keys will be already setup in Table B. The number of records to update will decrease over time as more records in the primary keys list reach completed status.

The purpose is to maintain certain static values on the Date fields till status==completed. We are unable to control an external system that keeps over-writing them. As a stopgap we want to rewrite them at frequent intervals to have the notion of maintaining static values. Once all PKs in our list reach completed status, we want to turn off the trigger.

This is not what triggers do -- they act on an event, such as a change to data.

Use DBMS_Scheduler to set up a repeating event.

Consider applying a function-based index to include only those rows that need updating, and make sure the query is constructed to be able to use it.


mongo: how should I layout my data I have 2TB of data currently spread across many CSV files that looks like:

{
 'rec_date': "2010-01-29",
 'rec_time': "09:15:00",
 'site_no': '46',
 'data_owner': '1',
 'flow_period': '5',
 'vehicle_count': '60',
 'detector': '8'
}

I would like to insert this into mongodb so that I can quickly do queries that typically get the vehicle volume for a site and its surrounding sites for the past n Mondays of the past m years. (I wish to analyse the history of a site to determine if there's abnormal traffic flow for this time of day at this time of year).

Initially I wanted to store a document for each site, that contained its entire history, like so:

{
    site_no: 46,
    history: [{
            $date: 1264756500000,
            readings: [{sensor:1,vehicle_count:60},
                       {sensor:2,vehicle_count:32},
                       ...
                      ]
            },{
            $date: 1264756800000,
            readings: [....]
            }
    ]
}

But this would make each document very large, as the entire history for each site will be more than 16MB. More traffic volume data is coming in every 5 minutes from every site and I would need to be appending to the history array very often.

So my question is, how should I format my data so that I can perform the queries I want but minimising data redundancy? Should I just make a new record for every sensor reading?

Yes, create a new record for every reading.

MongoDB and other document stores are designed for efficient retrieval of de-normalized or irregularly structured data. A MongoDB collection is essentially a set of key-value pairs with a rich value type. Updating the value is an atomic operation, which means that constantly appending to a list within the value is not going to be efficient and, as you mentioned, you will eventually hit the document size limit.


Making Session Keys of SQL instances Identical in SQL Availability Group We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.

I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.

I am not sure of what to make of that:

Is there anything such as session keys in HA group?
If so, can I make them identical for each node (SQL instances) in the
cluster?

When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy.  The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:

OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY

There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout.  Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events.  The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.

This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.


Oracle Standby Database - Archivelog GAP Good Afternoon!

I've done some research around fixing archivelog gap between Primary and Standby databases, and a few links provide tips on the querying V$archived_log, v$archive_gap and v$log_history. They are really great source of information, however, I have a scenario in which both servers need to have archivelog retention due storage limits, so standby database let's say, has archivelog sequences from D to P, and from P to R, being R today's.

What would be the implications, apart from having missing data, in a case of need to put standby temporarily as production db in case of failure? Would it work, or the best practice is to recreate stdb database and apply only continuous archives from now on?

Thanks.

Assuming you mean that the database is "missing" logs from P->R, and that your primary database no longer has those logs either, then the answer is that you'll have a functional database (upon standby activation as a primary) but you will be missing data from P-> the Activation point.

The thing to remember here is that your standby is only as good as your last archive log that was fully applied. Without intervention on your part, your standby database is essentially frozen in time, and will never advance.

If you're missing archive logs, and they are truly unrecoverable (did you check storage backups? Backups to tape?), then your standby, IMO, is worthless as a production level standby. You, and your business, may have a different opinion based on your usage of the database in question.

But if this was truly a production level standby database that is meant to take over for production in a disaster scenario, personally, I would rebuild the standby immediately.


logon denied for oracle wallet user Following a video tutorial on oracle wallets: https://youtu.be/LLZzUNb9ALU
The db server is run from oracle's official docker container.
The steps I have done are as follows:

On server shell, ran sql plus as sysdba:

$ sqlplus / as sysdba

Created a user:

SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> CREATE USER fred identified by flintstone;
SQL> GRANT CONNECT, RESOURCE, DBA to fred;

Tested logon:

$ sqlplus fred/flintstone

SQL> show user;
User is "fred"

Created a wallet at default location: $ORACLE_BASE/admin/$ORACLE_SID/wallet

$ orapki wallet create -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -auto_login -pwd MyWalletPass

Created a wallet profile for user fred:

$ mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -createCredential $ORACLE_SID fred flintstone

Tried to login sqlplus:

$ sqlplus /@$ORACLE_SID

This fails. Got the error ORA-01017: invalid username/password; logon denied
Not able to understand whats wrong. The final goal is to use this wallet for a nodejs application with external auth (node-oracledb). But I think if I can fix step 6 I should be able to use from nodejs.

As suggested by Andrew
The 1st part, I explicitly mentioned wallet location in sqlnet.ora file, and also included the following line:SQLNET.WALLET_OVERRIDE=TRUE. Also verified the correct tns entry in tnsnames.ora. (This is already taken care of during container start, so I didn't have to do anything).
For the 2nd part, I had to use the same sqlnet.ora with the exception that the WALLET_LOCATION parameter was updated with the location to the wallet folder on the client machine. And for the client program, I had to set the TNS_ADMIN environment variable; I had to point it to the folder where the former file would reside. Additionally added a tnsnames.ora file and added a tns name (similar to the oracle service name on the db server).
Note: On the client machine, we have to be careful of the syntax in the sqlnet.ora and the tnsnames.ora file. Different clients have various ways to connect. I have just noted for the case of a nodejs program which used orcaledb as the oracle client. These specific type of clients make use of the environment variable TNS_ADMIN.


left outer join - sort operations in the query plan - any ways of tuning this simple query? while working on the query below in order to answer this question:

How to query chart data in a database agnostic way?

Having the following tables:

CREATE TABLE [dbo].[#foo] (
[creation]  DATETIME                         NOT NULL,
[value]     MONEY                                NULL,
[DT]        AS (CONVERT([date],[CREATION])) PERSISTED)

-- add a clustered index on the dt column
CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
GO

and this other table for joining:

create table #bar (dt date primary key clustered)
go

the loading of data into these tables can be found here.

But when running the following query:

WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt,
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

--get rid of the duplicates and present the result
SELECT
THE_DATE,
THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
THE_TOTAL_VALUE_FOR_THE_DAY
FROM RADHE
WHERE THE_ROW = 1

I get something like this picture below, which is exactly what I was looking for.

But the execution plan generated has several Sort and Nested Loops Operations, as you can see on the picture below.

The full query plan can be found here.

this is a very simple operation, a left outer join between 2 tables, the indexes are already ordered, and therefore I was wondering if I could simplify the query plan.

alternatively, I could change the query code.

why exactly do we need nested loops 2 times and sort 2 times in the query plan?

You have an index that provides ordering by B.DT but

the plan first evaluates THE_ROW using this order
then the right hand sort orders by F.DT to evaluate THE_NUMBER_OF_RECORDS_ON_THIS_DAY
and finally the left hand sort puts things back into B.DT order for the THE_TOTAL_VALUE_FOR_THE_DAY.

You can get rid of one of the sorts by simply changing the order of the columns in the CTE so the F.DT one appears last (The connect item for this Unnecessary Sort is here)

WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0),
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

But you can get rid of both by changing the definition of THE_NUMBER_OF_RECORDS_ON_THIS_DAY to

CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY B.DT ) END

So it uses the same partitioning definition as the rest of the functions.

This shouldn't change anything in your example as your CASE expression will just assign 0 to any non matched rows anyway.

As for the rest of the plan see Partitioning and the Common Subexpression Spool

(Plan afterwards with no sorts)


Maximum server memory We have a Windows 2008 r2 server (on hyper v) and on this we are running a SQL server. This database is approx 300 gb big. The server has 32 GB ram. In the SQL Server properties I saw the following:

I just wondered is this OK or is this too much memory. According to www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/
I read that a server with 32 GB needs max  29000

Can someone advise. Thanks

Generally in our setup guide, I recommend that you leave 4GB or 10% free for the operating system, whichever is larger. In this case, 4GB is larger, so you'd set max server memory to 28000.

The free memory is for:

Other services like SSIS, SSAS, SSRS (and if you use those extensively, you'll want to leave more memory aside)
Remote desktopping in (I wish people didn't, but so many folks RDP in and run SSMS or other tools)
Systems management apps (Dell OpenManage, HP SIM, etc that fire up and gobble memory)
Backup tools

If you monitor the Perfmon counter for Available Memory over time, and you consistently notice that most of the memory is going unused, then you can inch up max server memory to a higher number.


MySQL (mariaDB) trigger syntax always wrong I'm making a small game and I need to have a trigger on my members table, which will check the experience column, and anytime it gets bigger than certain values, the level will become level+1. This is the trigger:

DELIMITER $$
CREATE TRIGGER update_level AFTER UPDATE ON members
FOR EACH ROW BEGIN
    IF experience < 1000 THEN
        SET members.level = 1
    ELSE IF experience < 2400 THEN
        SET level = 2
    ELSE IF (experience < 4000) THEN
        SET level=3
    ELSE IF (experience < 8000) THEN
        SET level=4
    ELSE IF (experience < 15000) THEN
        SET level=5
    ELSE IF (experience < 24000) THEN
        SET level=6
    ELSE IF (experience < 35000) THEN
        SET level=7
    ELSE IF (experience < 50000) THEN
        SET level=8
    END IF
END
$$
DELIMITER ;

phpMyAdmin always responds with this error code:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

I spent about 2 hours trying to figure it out, but it looks like I can't do it by myself. What I have tried is to change the IF statement to a CASE statement (simple switch). I have tried it with WHEN xxx THEN SET. Also with UPDATE. Another thing  I have tried was to add ' ' and " " everywhere around the column names and values - didn't help at all.

Can you please advise me about what's the best way to accomplish this? I'm pretty sure there is some small mistake there, and if not that, then my approach must be wrong.

When trying to compile the original trigger code, we encounter quite a few issues.  As some colleagues have pointed out, the timing ("after") should be adjusted.  There are some syntactical and logical problems, too (eg < instead of >).  Maybe it would be better to use BETWEEN ... AND ... for defining the "levels".

--  test data

create table members (member_id int, experience int, level_ int);

insert into members values (1,2200,1),(2,3800,2),(3,7800,3);

Trigger (MySQL 5.7)

DELIMITER $$
create trigger update_level
  before update on members
  for each row
begin
  if new.experience between 0 and 999 then
    set new.level_ = 1;
  elseif new.experience between 1000 and 2399 then
    set new.level_ = 2;
  elseif new.experience between 2400 and 3999 then
    set new.level_ = 3 ;
  elseif new.experience between 4000 and 7999 then
    set new.level_ = 4 ;
  elseif new.experience between 8000 and 14999 then
    set new.level_ = 5 ;
  elseif new.experience between 15000 and 23999 then
    set new.level_ = 6 ;
  elseif new.experience between 24000 and 34999 then
    set new.level_ = 7 ;
  elseif new.experience between 35000 and 49999 then
    set new.level_ = 8 ;
  elseif new.experience >= 50000 then
    set new.level_ = 9 ;
  end if;
end;
$$
DELIMITER ;

Testing

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       2200 |      1 |
|         2 |       3800 |      2 |
|         3 |       7800 |      3 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 8500 where member_id = 1 ;
update members set experience = 16000 where member_id = 2 ;
update members set experience = 25000 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       8500 |      5 |
|         2 |      16000 |      6 |
|         3 |      25000 |      7 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 36000 where member_id = 1 ;
update members set experience = 55000 where member_id = 2 ;
update members set experience = 10 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |      36000 |      8 |
|         2 |      55000 |      9 |
|         3 |         10 |      1 |
+-----------+------------+--------+
3 rows in set (0.01 sec)

ALTERNATIVE

A VIEW may be a better solution for this scenario.  We could use a different table layout for MEMBERS, and avoid storing levels ("level" depends on "experience", not on member_id ...), like so (MariaDB 10.2):

-- test data
create table members (member_id int, experience int);

insert into members values (1,2200),(2,3800),(3,7800);

Now we can find the current level associated with a member_id by SELECTing from the VIEW.  No trigger required.

create or replace view member_level
as
select
  member_id
, experience
, case
    when experience between 0 and 999 then 'level 1'
    when experience between 1000 and  2399 then 'level 2'
    when experience between 2400 and  3999 then 'level 3'
    when experience between 4000 and  7999 then 'level 4'
    when experience between 8000 and 14999 then 'level 5'
    when experience between 15000 and 23999 then 'level 6'
    when experience between 24000 and  34999 then 'level 7'
    when experience between 35000 and  49999 then 'level 8'
    when experience >= 50000 THEN 'level 9'
  end as level_
from members ;

Testing

select * from members;

member_id  experience
1          2200
2          3800
3          7800

update members
set experience = 27500
where member_id = 1 ;

select * from member_level;

member_id  experience  level_
1          27500       level 7
2           3800       level 3
3           7800       level 4

See dbfiddle here.

Another alternative (MariaDB version 5.2+, MySQL 5.7)

Use a virtual column, let the DDL do the work.

create table members (
  member_id int
, experience int
, level_ varchar(32) as (
    case
      when experience between 0 and 999 then 'level 1'
      when experience between 1000 and  2399 then 'level 2'
      when experience between 2400 and  3999 then 'level 3'
      when experience between 4000 and  7999 then 'level 4'
      when experience between 8000 and 14999 then 'level 5'
      when experience between 15000 and 23999 then 'level 6'
      when experience between 24000 and  34999 then 'level 7'
      when experience between 35000 and  49999 then 'level 8'
      when experience >= 50000 THEN 'level 9'
    end ) virtual
);

insert into members (member_id, experience) values (1,2200),(2,3800),(3,7800);

select * from members;

member_id  experience  level_
1          2200        level 2
2          3800        level 3
3          7800        level 4

Dbfiddle here.


Selecting from a function that returns a sys refcursor or an alternative I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can:

Create a view and then query it.
Create a function and return resultset

If I go the first way a simple query like the following takes more than 20 seconds:

SELECT PAID_YEAR,PAID_TOTAL FROM VIEW_1 WHERE SSN=12345678912882;

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

If I go the second way I can send a parameter and make the engine look only for those rows that match the condition and return the recordset. But I do not know how to then SELECT from that returned resultset. I took a look at pipelined tables but didn't quite get how to benefit them. So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:

SELECT * FROM FUNCTION_1(12132323232).

If yes, then how, if no, what would be an alternative way?

EDIT:Here's the explain plan:

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

This might be true, but is unlikely to be so.

When you apply a condition to a view, the condition is usually merged into the view query. This is called predicate pushing, and ought to allow the optimiser to apply the filter as early as possible in the execution plan.

There are some cases where this cannot happen, for example if your view summed up all of the sales orders by client and then you applied a condition to require that total sales order were greater then $1,000,000.

The explain plan wil tell you whether this has happened, but it's best to extract it using DBMS_Xplan.Display. the resulting text-based plan will show you the lines of the plan at which conditions are applied.


Replication Server version 15.7.1 - Sybase ASE 15.5, how to change primary data server in RSSD? Recently, I am requested to migrate the current Sybase data server to a new Sybase data server (let's say, from pub1 to pub2). To do this, I need to rebuild all the RepAgents which have been set up previously in pub1. Is there any primary data server I would need to set in the RSSD? Because I could see when I run the command 'connect' without any option, it will connect to (pub1.repserver) instead of (pub2.repserver). Is there any configuration required on the RSSD to make it (pub2.repserver)? Also, I actually have setup one RepAgent (pub2.db_lotte), the setup process using rs_init was successful, but seems the RepAgent connection is down after the setup process (admin who_is_down), the connection couldn't bring up and no error is captured in the replication log.

Appreciate if anyone could help.

Thanks.

You're going to need to provide more details on what you mean by 'migrate the current Sybase data server', eg, are you moving the dataserver? are you copying the dataserver? something else?

Also, there's generally nothing to do directly in the RSSD.  All commands should be issued either in the PDB, the PRS/RRS, and/or the RDB.

Generally speaking ...

If a database has been copied from elsewhere (eg, dump and load), you'll want to make sure the old repagent info is discarded and the old secondary trunc pt is removed, eg:

use new_db
go
-- in case repagent is running:
exec sp_stop_rep_agent 'new_db'
go
-- discard repagent config settings
exec sp_config_rep_agent 'new_db','disable'
go
-- in case sp_config_rep_agent didn't remove 2nd tp:
dbcc settrunc('ltm','ignore')
go

At this point you then use rs_init to add 'new_db' as a a new database/connection to the desired PRS.

NOTE: If 'new_db' is to be the active db in a WS setup, make sure the logical connection has been created before running rs_init.

Once you've added 'new_db' to the replication domain you'll need to continue with normal replication setup, eg:

make sure RDB(s) is added to the replication domain (if not already a member)
add route(s) to RRS (as needed)
setup repdefs/subs
assign any custom/modified classes to the DSI connections


change optimizer's plan on using indexes When I run a query as follows, the run time is less than 1 sec.

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
    FROM source_price_curve spc
    WHERE spc.as_of_date >= @as_of_date_from

It utilizes the non clustered index based on maturity date column in the table.
I then added a temp table in join as follows

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
FROM source_price_curve spc
INNER JOIN #source_price_curve_list spcl
    ON spc.source_curve_def_id  = spcl.price_curve_id
WHERE spc.as_of_date >= @as_of_date_from

In this case also same index is seen utilized(unless recompiled or forced to use new index by hint),however, performance was decreased dramatically(to 12 secs),which was solved by using cross apply condition(as suggested by Paul).

My concern is, is there any ways to change optimizer's plan or let the optimizer  automatically choose a new index as used in join or filter condition by new query ?
(As far as I know, the optimizer uses the same index unless recompiled or forced to use a new index as a hint.)

Thanks in advance.

Your two queries are different, and have different execution plans. While they may both use a particular index, that index was chosen independently. The fundamental tasks the queries are doing are different (one has one table, one uses two), and thus they cannot possibly use the same plan.

Once the optimizer has identified a plan for a given query, it will reuse that plan as you indicate. However, the queries have to be the same. Even changing a hard-coded value will often cause a new plan to be generated (see Parameters and Execution Plan Reuse in the product documentation).

As a general rule, plans that are good sometimes but bad others are normally due to queries with variables in them, or queries in procedures and functions that use passed-in parameters in queries.

The best way to help the optimizer make the best choices is to ensure that your statistics are up-to-date. When the optimizer makes good estimates as to how many rows will be found, its plans are more likely to be good.

There are relatively rare cases where the optimizer will refuse to pick the best possible query; in those rare instances, you can use various query hints to (for example) force the use of a particular index.

That said, hints should generally be used very sparingly, especially in recurring code (scripts run regularly, applications, etc.). Using a query hint like this will prevent the optimizer from considering certain options, and the odds are good that what's the best option today may not be the best in a month or a year, as your data changes. Your "perfectly planned" query today may become a crippling bottleneck next month, next week, or even tomorrow. Instead of query hints, you can also use plan guides (I know no more about those than that they exist).

Most often, the best solution is to tune the query so a better index will be chosen, or so less work needs to be done. This may sometimes feel like finding a way to "trick" the optimizer into doing what you want. However, it's really a matter of explaining to the optimizer what you want to do better. If you're speaking a foreign language, and you're asking to get to a bathroom and use the toilet, but what you actually say is "I need to use your bathtub," whose fault is it if the person you're talking to goes to get you a towel instead of taking you straight to the nearest toilet?


Count instances of value in multiple tables I'm attempting to write a query that returns the count of a given value across multiple tables.

Right now, I'm using the following query

SELECT COUNT(*) AS SL,
(
  SELECT COUNT(*)
  FROM [Sales Line Archive]
  WHERE [Document No_] = 123
) AS SLA
FROM [Sales Line]
WHERE [Document No_] = 123

Which returns a single result with the count of lines in Sales Line in the first column and the count from Sales Line Archive in the second.

However, I'd like to request multiple records using an IN clause, so that I could get results like the following

Document No_, SL, SLA
1, 3, 0
2, 0, 4
3, 2, 1

One way would be to pivot

WITH CTE AS
(
SELECT [Document No_], 'SL' AS Source
FROM [Sales Line]
UNION ALL
SELECT  [Document No_], 'SLA' AS Source
FROM [Sales Line Archive]
)
SELECT P.[Document No_],
       P.SL,
       P.SLA
FROM CTE
PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P
WHERE P.[Document No_] IN (1,2,3);

I'd hope that the predicate on [Document No_] gets pushed up into the selects from the source tables. Check the execution plans to be sure.


Will Installation of Service Pack 3 on SQL Server 2012 Web Edition (existing production) require database restore activity? We are currently running our Database Server on a RTM version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

We learnt that we need to install the latest Service Pack for SQL Server 2012, which is SP3

I had the following doubts based on this subject:

Will installation of SQL Server 2012 Web Edition SP3 affect any of our production databases?
Do we need to restore the databases, after installation of SP3?
Are there any particular prerequisites we need to follow before the installation of SP3?
Do we need to perform a database backup and backup of the data files before the installation of SP3?

Please advise. Appreciate your help on this. Thanks.

Installing service packs usually goes like this: detailed step by step guide

It shouldn't affect databases, but you may want to test installing SP3 on the test server first. It will help you get an idea how long it takes (management will want to know that beforehand). It will also give you a chance to find out if any of your applications (or anything else) got affected. Safety always comes first so production is never the best place to patch first.
No, you don't need to restore anything. Existing databses will be in the same state as they were before the installation. Settings (collation etc) will remail unchanged as well.
You may want to read about bugs in SP3 and potential problems that can occur during instalation. There is plenty of examples on Google. You may also want to kave a look at the release notes to understand better what fixes are contained in SP3.
I always do (just in case), but you don't have to. It is really about how concerned your managers are about data integrity, safety etc. It is usually recommended to backup all databases before any maintenance.