Restore a WordPress database from .frm and .idb only I had a WordPress website running a long while ago. I used WAMP server. Then, I uninstalled WAMP. Today I would like to work again on my website; all that remains of my database is this (I cannot find the backup I once made) the ibdata1 file is not the right one so the only thing I have to eventually restore the database are.frm and .idb (yes, I really, really screwed up) :

So, there seem to be some data left.

However, here is what I get in phpMyAdmin:

It says my tables don't exist (no table were found for wp_db). Is there any way I can restore the database?

In general, if you're using MyISAM tables, you might be okay. See https://stackoverflow.com/a/879268/2385479 for instance.

If your tables are InnoDB, then it's a bit more difficult. According to https://serverfault.com/a/487534/67211, it seems the ibdata1 file is rather important.

Based on the error message, what I believe is happening here is that your database structure is being read correctly from the files you were able to recover, but the underlying table structure and associated data itself is in the missing file.

If it's a MyISAM table type, you can attempt to recover it (in phpMyAdmin click the Operations tab and look for the Repair text; from the command line or SQL tab just type REPAIR TABLE wp_commentmeta;.

Sorry for the bad news, but it doesn't look good.


get parenrs and childs based on id my Data
    TradeID Commodity   GeneratedFrom
    32839   CPO             NULL
    32840   Hard Stearin    NULL
    32841   PFAD            32839
    32842   RBDPO           32839
    32843   CPO             NULL
    32844   Olein           32842
    32845   Stearin         32842
    32846   RBDPO           NULL
    32847   Hard Stearin    32845
    32848   PMF             32845

when give the trade id then return patents and childs like when i give Tradeid 32845
in above data Genrated from is patrent Id and which tradeid has Grerated from they are childs.
i want below result
     TradeID
      32845
      32847
      32848
      32842
      32839

You can use a recursive CTE similar to the following to get the whole ancestor list (parents and children) for a given TradeID:
WITH RecursiveCTE_Children AS
(
    SELECT GeneratedFrom, TradeID
    FROM MyData

    UNION ALL

    SELECT RC.TradeID AS GeneratedFrom, MD.TradeID
    FROM RecursiveCTE_Children AS RC
    INNER JOIN MyData AS MD
        ON RC.TradeID = MD.GeneratedFrom
),
RecursiveCTE_Ancestors AS
(
    SELECT GeneratedFrom, TradeID
    FROM MyData

    UNION ALL

    SELECT MD.GeneratedFrom, RC.GeneratedFrom AS TradeID
    FROM RecursiveCTE_Ancestors AS RC
    INNER JOIN MyData AS MD
        ON RC.GeneratedFrom = MD.TradeID
)

SELECT TradeID
FROM RecursiveCTE_Children
WHERE GeneratedFrom = 32845

UNION

SELECT GeneratedFrom
FROM RecursiveCTE_Ancestors
WHERE TradeId = 32845


Query Pivot Date column into Rows My problem is pivoting one single Date column into my row headings while all my current Column headings become rows.

My Column headings are [Date], which contains 50 years of days (from an awesome link provided by scsimon), and the other columns are vehicle license plates.

What I learned, from Mitch Wheat, is that using dates as individual columns, I am designing my table poorly. A google search result told me there is a hard limit on columns.

So I am back here now, after trial and error, left wanting.

My code...

SELECT [Date],[858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093],[133-1977],[133-1978],[133-1979],[133-9693],[113-9678],[133-1996],[115-2776],[115-2777],[128-5095]
FROM DateDimension
WHERE Date BETWEEN '1/19/2019' AND '1/25/2019'
pivot
(
FOR Date BETWEEN '1/19/2019' AND '1/25/2019';
)piv

My error message results in
"Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'pivot'."

I really hope my question is understandable. I found plenty of tutorials, and questions, on pivoting tables that contain only small amounts of data and targeting that. My table contains 50 years worth of dates.

Now for my question. What am I doing wrong when it comes to switching all my date column to the row headers and all my column headers to the first column?

Edit: All column titles: Date, 858-456, 128-5045 111-7090, 111-7091, 111-7092, 111-7093, 133-1977, 133-1978, 133-1979, 133-9693, 113-9678, 133-1996, 115-2776, 115-2777, 128-5095

The Between part is due to only wanting to view a specific range of dates for all of the vehicles (license plates).

Your PIVOT syntax is incorrect. You don't filter records in the PIVOT, you do that in the inner SELECT that represents your source for the PIVOT. I've got an example below, but I also should point out that it requires manually entering the dates you're pivoting as columns. If this date range will change or is to be parameterised then you need to look into dynamically generating this PIVOT statement. There are lots of questions and answers on DBA.SE, like this, that can help you.

Setup:

CREATE TABLE ##DateDimension (DateVal DATETIME, StrVal NVARCHAR(25))
GO
DECLARE @StartDate DATETIME,
    @Counter INT = 0

SET @StartDate = DATEADD(YEAR, -1, GETDATE())

WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
BEGIN
    INSERT INTO ##DateDimension (DateVal, StrVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate), 'Data')

    SET @Counter = @Counter + 1
END
GO

Query Example:

SELECT
    [01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019]
FROM
(
    SELECT CONVERT(VARCHAR(25), DateVal, 103) AS DateVal, StrVal
    FROM ##DateDimension
    WHERE DateVal BETWEEN '2019-02-01' AND '2019-02-08'
) src
PIVOT
(
    MAX(StrVal) FOR DateVal IN ([01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019])
) pvt

Results:

01/02/2019  02/02/2019  03/02/2019  04/02/2019  05/02/2019  06/02/2019  07/02/2019
----------------------------------------------------------------------------------
Data        Data        Data        Data        Data        Data        Data

EDIT: Based on comments, below is a new example pivoting the Licence Plates before left joining to the date dimension.

CREATE TABLE ##LicencePlates (LicPlate NVARCHAR(255),
    DateVal DATETIME,
    Department NVARCHAR(25))
GO
INSERT INTO ##LicencePlates (LicPlate, DateVal, Department)
VALUES ('858-456', '2019-02-01', 'ABC'),
    ('128-5045', '2019-02-03', 'DEF'),
    ('111-7090', '2019-02-15', 'ABC'),
    ('111-7091', '2019-02-27', 'DEF'),
    ('111-7092', '2019-02-08', 'ABC'),
    ('111-7093', '2019-02-21', 'XYZ')
GO

CREATE TABLE ##DateDimension (DateVal DATETIME)
GO
DECLARE @StartDate DATETIME = '2019-05-16 00:00:00.000',
    @Counter INT = 0

SET @StartDate = DATEADD(YEAR, -5, @StartDate)

WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
BEGIN
    INSERT INTO ##DateDimension (DateVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate))

    SET @Counter = @Counter + 1
END
GO

WITH LicencePlatesPivot AS
(
    SELECT DateVal,
        COALESCE([858-456], '') AS [858-456],
        COALESCE([128-5045], '') AS [128-5045],
        COALESCE([111-7090], '') AS [111-7090],
        COALESCE([111-7091], '') AS [111-7091],
        COALESCE([111-7092], '') AS [111-7092],
        COALESCE([111-7093], '') AS [111-7093]
    FROM
    (
        SELECT LicPlate, DateVal, Department FROM ##LicencePlates
    ) src
    PIVOT
    (
        MAX(Department) FOR LicPlate IN ([858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093])
    ) pvt
)

SELECT dd.DateVal,
    COALESCE(pvt.[858-456], '') AS [858-456],
    COALESCE(pvt.[128-5045], '') AS [128-5045],
    COALESCE(pvt.[111-7090], '') AS [111-7090],
    COALESCE(pvt.[111-7091], '') AS [111-7091],
    COALESCE(pvt.[111-7092], '') AS [111-7092],
    COALESCE(pvt.[111-7093], '') AS [111-7093]
FROM ##DateDimension dd
LEFT JOIN LicencePlatesPivot pvt ON pvt.DateVal = dd.DateVal
WHERE dd.DateVal BETWEEN '2019-02-01' AND '2019-02-28'

DROP TABLE ##DateDimension
DROP TABLE ##LicencePlates


Update MySQL Stored Procedure Definers on shared environment I am accessing a MYSQL database through PHPMyADMIN.

My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL Workbench. Routines created on Workbench are visible in PHPMyAdmin, but cannot be viewed or opened there or vice versa.

I believe the issue lies with the Definer that was generated upon the Routine's creation.

When using PHPMyAdmin, Routines are created with definer 'dbname@localhost'.

But when done through Workbench, this varies I assume based on the Network I am currently connected to. The format of the stored procedures created outside of PHPMyAdmin are 'dbname@ipaddress'.

I am trying to change all the Definers to 'dbname@localhost', in the hopes that I will be able to open and manipulate all Routines from PHPMyAdmin.

Running any queries on mysql.* is not an option because the database is hosted in a shared environment and I don't have root permissions on the database server.

I also tried updating INFORMATION_SCHEMA but this also proved fruitless.
I have contacted Support and was told I may be able to find more help here.

I hope this was descriptive enough.
Is there any other way I will be able to update these definers?

SUGGESTION #1

If you login as root@localhost, you can change the definer in mysql.proc

In your case, you would do

UPDATE mysql.proc SET DEFINER = 'dbname@localhost';
SELECT name,type,definer FROM information_schema.routines;

I am not sure if the stored procedures load automatically into INFORMATION_SCHEMA.ROUTINES

If not, run these

FLUSH PRIVILEGES;
FLUSH TABLES;
SELECT name,type,definer FROM information_schema.routines;

SUGGESTION #2

Based on my old post Modify DEFINER on Many Views, you can do the following

You may have to mysqldump the stored procedures into a test file
Edit the definer user to 'dbname@localhost' in the text file
Then, reload the stored procedures

GIVE IT A TRY !!!


How to monitor general performance and wellbeing of a database? I've read @Mike answer to How to tell if database tuning is fruitful (PostgreSQL), and that's the kind of approach I'm thinking about for monitoring the performance of my database (of a web application, many connections, heavy writes and reads all the time, very dynamic data).
The approach sums up like this: I want to know if my database can keep up with the workload, if it has the same readiness over time, and ultimately if my tuning and cleaning is useful. So for a general wellbeing indicator, monitoring the query time (for representative queries used by the web application and data flows) seams sensible and compact to me (query time increases without substantial changes => performance is degrading).
But my question is, is it?
Besides the question linked I've found no other source for such an approach, and I have still some holes on how to implement all this, so knowing more from other examples/experiences or manuals would be very useful. Even knowing that this approach is not worth doing would be interesting.
And FYI, I've read about pgbench, but it seams so aseptic to me, I mean, it surely gives a reference point, but what it says about my tables, my indexes, about the alive environment that is my database? (I hope it makes sense to you what I mean with that)
Maybe I'm looking at pgbench in the wrong way, but that's exactly what I want to find out!
Note: I work with Postgres, so I Postgres specific answer would be awesome... but not necessary!

In general I agree somewhat: if the customers are happy, don't start tuning.
But there are certainly things that can go wrong without you noticing before it is too late. Here is a random list of things that should cause an alert, without a claim to completeness:

Is there enough free disk space?
Is the CPU overloaded?
Is the I/O subsystem overloaded?
Does the database grow unreasonably?
Are there problems with WAL archiving?
Are there too many client connections?
Are there long running queries?
Are there transactions that remain open for a long time?
Are there databases that are in danger of suffering data loss from transaction wraparound?
Can you connect to the database and run a simple query?
Is replication delayed too much?
Are there any tables with too many dead tuples?
Did you get errors from data corruption?
Are there any stale prepared transactions?

pgbench is more useful than you think: you can use it with custom scripts that even support a simple scripting language to simulate different workloads.
But of course it is essentially a tool to generate artificial load to test the performance impact of certain changes and to make sure you didn't misconfigure PostgreSQL or the operating system.


Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7 I have started testing some MySQL 5.7 databases to replace our ageing 5.5 Databases, and there seems to be a significant change in the way the information_schema.tables Table records the data size.

I run the following query:

SELECT
    table_schema AS table_schema,
    table_name AS table_name,
    (data_length + index_length + data_free) AS table_size,
    ROUND((data_length + index_length + data_free)/POWER(1024,3),2) AS table_sizeGB,
    t.data_length,
    t.index_length,
    t.data_free
FROM information_schema.TABLES t
WHERE table_schema = 'myDb'
ORDER BY table_size DESC, table_name
LIMIT 10;

This gives me the 10 largest tables. I run this daily to record the growth over time. On MySQL 5.5 it takes apx 5 seconds, on MySQL 5.7 it is instant.

On MySQL 5.5 I get the following result for the largest table:

myDb | myTable | 319383535616 | 297.45 | 319116279808 | 262012928 | 5242880

On MySQL 5.7 I get:

myDB | myTable | 270051115008 | 251.50 | 269824819200 | 226295808 | 0

Both Databases were created a few days apart. The 5.7 Database is a Slave of the 5.5 Database. Neither are active yet (just sitting as a slave of the Active System).

If I look on the server, the tables .ibd file is 302GB on the 5.5 Database, and 292GB on the 5.7 Database.

I expected a small discrepancy (one is using Antelope, on is Barracuda File format - both are innodb). Data is being constantly added to the database, and very little (if any) is being removed.

On the MySQL 5.5 database, I can see the numbers increase each time I run the query, even just seconds apart. But on MySQL 5.7, the numbers haven't changed since I started looking at it, a week ago.

Is this correct behaviour? Is there a better way to monitor this in MySQL 5.7 that I have missed?

-- UDPATED 2018-10-16

After three weeks, the result of the query is still virtually the same on MySQL 5.7:

5.5 -- myDb | myTable | 327890632704 | 305.37 | 327616036864 | 268304384 |6291456
5.7 -- myDb | myTable | 270055309312 | 251.51 | 269824819200 | 226295808 | 4194304

The Physical size on the server is 310GB on MySQL 5.5, and 300GB on MySQL 5.7.

Both Tables are created identically.

Neither database is active yet, so the only queries running (besides my testing) is from replication.

Both databases uses innodb_file_per_table.

The MySQL 5.5 was copied from an active slave using mysqldump.

The 5.7 database was copied from the MySQL 5.5 database using mysqldump + mysql_upgrade.

Optimize table has never been run on these tables.

Short answer:  The numbers are close; don't worry.

Long answer:

There are a lot of explanations.

Well, I guess Barracuda is 15% smaller than Antelope.  (This may be the main reason for the difference.  Still, many other things can cause differences.)
Are they a different ROW_FORMAT.
SELECTs can cause locking that leads to extra fragmentation of inserts/updates/deletes.
Is innodb_file_per_table the same?
How was the Slave data cloned from the Master?
Have you done OPTIMIZE TABLE on either machine.  (There is virtually no reason to every do so.)
5.7 has a lot of code changes over 5.5, but I can't think of any other issues that might directly show up in those numbers.
It are not likely that there are any data differences.  Percona has a tool that will check.
Is there a "better way to monitor?" -- Monitor for what?

Also,

When an InnoDB block shrinks due to updating/deleting, there is some attempt to merge the block with an adjacent block.  It may be that 5.7 does a better job than 5.5, hence leading to slower growth of the table over time.  (This seemed to be more of an issue in index BTrees.)
The "Data_free" includes only a fraction of the reusable space.  It should not be trusted for much.


How can I obtain the last inserted row with INSERT ... SELECT in PostgreSQL? I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id).

In order to determine where to start for the next batch, I'd like to start off from the last inserted row (WHERE user_id >= last_user_id AND object_id > last_object_id).

Starting off with this:

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...)
FROM source_table
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... returns a table of 1000 tuples. I'd like to obtain the last inserted tuple from it.

I've tried to do a SELECT around it, like this:

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...)
    FROM source_table
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

But that returns a syntax error:

ERROR:  syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
               ^

I've also attempted RETURNING ... INTO variable as described here, but that fails too:

ERROR:  syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
                                      ^

Do I need to create a function for this (e.g. plpgsql) or am I missing something obvious in plain SQL that let me do this? That would be highly favorable.

It seems that you need only the maximum of the last inserted (user_id,object_id) as a couple. The first INSERT could be:

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
 ORDER BY 1 DESC LIMIT 1;

This form uses a row to refer to the (user_id,object_id) couple for convenience. Row elements are compared left-to-right as needed in your case to use this couple of values as a boundary. See
 Row Constructor Comparison in the documentation.

With this solution, the subsequent INSERTs would inject the value returned by the last preceding insert. This avoids looking up the last inserted row in dest_table.

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
  WHERE row(user_id,object_id) > row(...inject the result of the previous query here...)
ORDER BY user_id, colN, object_id
LIMIT 1000
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
ORDER BY 1 DESC LIMIT 1;

  Do I need to create a function for this (e.g. plpgsql) or am I missing
  something obvious in plain SQL that let me do this? That would be
  highly favorable.

In plain SQL, you would do a single INSERT in one step without any batching. Since you want to break it into batches, there has to be some loop driving these INSERTs with a stop condition, and this procedural logic goes beyond pure SQL.

If you don't feel like reinjecting the boundary through a variable, it could be persisted in a dedicated, single-row table.


Force number precision in a view with Union I am using Entity Framework and Oracle. Oracle's ODAC seems to have a known bug where data of type NUMBER is cast to Int64 by Entity Framework.  I thought a view would assist me but

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual
union
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

When you look at the view the data type is Number with no precision for all of them.  This appears to be a consequence of the UNION as just

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

yields NUMBER, NUMBER(1), NUMBER as the datatypes

Is there a workaround to force Oracle to derive a precision in a view with a UNION?

Edit: @Phil asks the desired result is. He is correct that Entity Framework has to have a precision to work with so the desired datatype from the sample above would be
NUMBER(1)

Edit: @Phil for this version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production on Windows 32 bit a view with a union gives a view that has NUMBER with no precision.  Entity Framework interprets this as int 64.

I am looking for a solution that shows the NUMBERS with a precision that Entity Framework can understand as a 32 bit integer.  Any precision from NUMBER(1) to NUMBER (9) does the job.

If you say

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

gives you what you want, why not use the same for union :

CREATE OR REPLACE VIEW test_v11 as
SELECT ONE, CAST(TWO AS NUMBER(1)) AS TWO, CAST(THREE AS INT) AS THREE
FROM
(
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
  UNION
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
 )a


Oracle Enterprise Manager Database Console - default login and password? I've just installed an Oracle 11g Standard Edition Database. It ran successfully and everything, however when going to Oracle Enterprise Manager Im not really sure what my "username" would be.

I entered a password during the installer, and I tried SYS and my password connecting as "Normal" and "SYSDBA". However, I'm not really sure what my username is meant to be. Is it just that I haven't set up a username/password for this yet?

Thanks

Here is the Oracle 11.2 installation document describing the administrator user accounts after installation.  Once logged in, you would change passwords with the syntax

alter user system identified by mynewpassword;

Note the SYS and SYSTEM accounts are assigned the DBA role. The DBA role does not have a password, the accounts do. The DBA role is created with every installation. You could grant the DBA role to another user if needed.


MongoDB denying API access, while console shell works I am a MongoDB newbie, with a MongoDB instance running on a Ubuntu machine.

If I login onto the machine's console and run mongo -u username -p password databasename I have permissions for the database databasename to create and drop collections, insert documents and remove them. I have successfully tested these operations.

However, if I try to insert a document (into the same database using the same user credentials) using the MongoDB API from a Java program (running on the same server), I get error 13:

com.mongodb.MongoCommandException: Command failed with error 13:
'not authorized on databasename to execute command { insert: "collectionName" ...

My Java code (works well when running on my local machine for another MongoDB instance without authentication):

...
ServerAddress serverAddress = new ServerAddress(serverName, portNo);
MongoCredential mongoCredential =
     MongoCredential.createCredential(username, DB_NAME, password.toCharArray());
mongoClient = new MongoClient(...
DB db = mongoClient.getDB("databasename");
mongoCollection = db.getCollection("collectionName");
this.mongoCollection.insert(...                          <-- CAUSES EXCEPTION
....

What could be the cause of this error, and how can I solve it?

The cause of this problem was likely a MongoDB server configuration issue, relating to IP address binding. Changing 127.0.0.1 to 0.0.0.0 resolved the problem.


How to optimize a Full Text Search So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.

Doing this search slows the query down by about 300ms and since the query is run a few hundred times a day, this amounts to quite some time lost.

The statement looks something like this:

SELECT *
FROM table1 t1
WHERE t1.code LIKE '%so%'

Where 'so' is the start of the string 'something'.

I've optimized the rest of the query, so this is really the only part that is left unoptimized.

I've tried adding an index to the column, however, since the input string doesn't always match the start of the string, it results in a search that could be anywhere within the string, meaning that a B-tree index doesn't work.

I've also tried adding a 'FULLTEXT' index, but it didn't speed up my query, it only used more disk space.

Is there any way to speed up a full text search with variable length strings , where the searched string could be at any position within each row of the column?

The problem with the FULLTEXT index is that when using match against it has a 50% threshold, which I don't really understand. The effect it has is that it returns an empty result set, because it matches too much.

From Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search by Henning Koch:

  FULLTEXT performs better when your text has low redundancy
  FULLTEXT performance differs by a factor of 78 between a vocabulary of 1,000 words and 100,000 words. I guess that larger vocabularies result in a very wide but shallow inverted index that can quickly determine if a query has matches or not. An educated person has a passive vocabulary of 15,000 to 20,000 words, so FULLTEXT should work well for natural language texts.


SQL Server: Covering indexes including all columns? Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every column that isn't otherwise part of the key. These tables have on average anywhere from two to five indexes or unique constraints as well as foreign keys.

The intent looks to be to improve SELECT performance regardless of what query is thrown at the database, as access is via an ORM that by default (but not always) retrieves all columns. We expect that the side effects of this are increased storage requirements (possibly significantly so) and additional overhead time for INSERT/UPDATE/DELETE.

The question is, is this a sensible strategy? Our team has history with SQL Server but no members who would consider themselves experts on its internal behaviour (though the question has been raised that if this strategy was optimal, wouldn't it be the default by now?). What other side effects (database server CPU/memory/TempDB usage, etc) should we be expecting, or are some of our assumptions above incorrect?

Additionally, the application can be installed into both SQL Server on-premise (versions since 2012), as well as Azure SQL -- should we be prepared for any differences between the two, or additional side effects on Azure, as a result of this approach?

I've done this on specific indexes before now, to aid oft-run heavy queries. Effectively what they have done is create multiple clustered indexes: when any of those indexes is used to find rows no extra work is needed looking up the rest of the data in the real clustered index (or the heap if there is no real clustered index).

  is this a sensible strategy?

For some indexes where needed to support certain query patterns, certainly yes.

But to do this with all indexes, I would just as certainly say no.

It is going to be wasteful of space to do where not actually needed, and will slow inserts/updates significantly. It may slow down as many read queries as it helps too, because each index page holds less records so any query needing to reference a chunk of the index for filtering but not using all other columns will have to access more pages. This will make your database more memory-hungry: those pages will need to be loaded into the buffer pool, potentially ejecting other useful pages if memory is low. If compression is used on those indexes to try mitigate the effect on storage and memory requirements, then it will be pushing extra load to the CPUs instead.

  as access is via an ORM that by default (but not always) retrieves all columns

This is a common pattern with poorly optimised use of an ORM (or just naive ORMs) and in these cases I've seen SQL Server's index adviser (and similar 3rd party tools) suggest indexes with many INCLUDEd columns, so I would agree with your suggestion that this is why the indexes have been created this way.

But while it may make all such queries slightly faster and some of them significantly faster, I suspect that in many cases any benefit is so small as to not be worth the extra memory footprint required by your common working set, the space on disk, and the IO between disk and memory.

Also remember that the ORM might not be selecting out all columns of all tables tha a query touches, so that benefit might only hold for the main target of the current request, and the larger indexes may penalise the query when other objects are used for filtering but not returning data  (SELECT * FROM table1 WHERE id IN (SELECT someID FROM table2 WHERE someColumn='DesiredValue') perhaps).

Another consideration for the excess space used, particularly if the data is large, is that it will have an impact on your backup strategy: storage and transfer costs for those backups, potential restore times, and so forth.

  should we be prepared for any differences between the two [on-prem & AzureSQL]

Generally I think the considerations here are going to be the same in each case, though any excess memory/IO cost imposed by the large indexes may be more directly visible in Azure where you can tweak the service tier and therefore infrastructure cost more easily rather than having a relatively fixed set of hardware resource. If using standard/premium tiers instead of vcore based pricing then you'll be affected more by the IO cost in standard as premium includes significantly more IO per DTU. If you are using multi-region backups or redundancy or other non-local features in Azure than there might be a bandwidth cost associated with the extra space taken by uneccesarily wide indexes.


Equivalent idioms in Oracle? I was reading this article that is about Google BigQuery:

Exploring a powerful SQL pattern: ARRAY_AGG, STRUCT and UNNEST

They use a couple of functions that I am trying to figure out what the Oracle equivalents are, if they even exist.
I am digging through the Oracle documentation and I found something similar to ARRAY_AGG in Oracle called LISTAGG.
What I have not been able to find are the idiomatic equivalents of STRUCT and UNNEST.
Are there things that do the same thing as STRUCT and UNNEST or will I have to write my own?

Technique Translation

The technique for solving a problem in one RDBMS doesn't always translate well into another.

I've had performance problems with similar code to that which was in the article.  Thus, my comment "This technique will kill performance in Oracle".

I don't see CAST(MULTISET()) (Oracle's version of ARRAY_AGG) being used too much.  As such, the code might be unmaintainable by your replacement after you get a promotion.

Translating Terms

Translation goes like this

STRUCT in BigData is TYPE in Oracle
ARRAY_AGG in BigData is CAST(MULTISET()) in Oracle
UNNEST in BigData is TABLE() in Oracle
UNNEST of XML Data is XMLTABLE() in Oracle
UNNEST of JSON Data is JSON_TABLE() in Oracle (12c+)

You can't create the structure on the fly.  You have to define it ahead of time.

EXAMPLES

Creating a structure of a single row:

create type emp_t as object (
  EMPNO             NUMBER(4),
  ENAME             VARCHAR2(10) ,
  JOB               VARCHAR2(9)  ,
  MGR               NUMBER(4)    ,
  HIREDATE          DATE         ,
  SAL               NUMBER(7,2)  ,
  COMM              NUMBER(7,2)  ,
  DEPTNO            NUMBER(2)
);
/

Creating a structure of a set of rows based on above: ( nested table )

create type emp_tt as table of emp_t;
/

Using CAST(MULTISET())

select d.deptno
  ,cast(multiset(
           select * from scott.emp e where e.deptno=d.deptno
        ) as emp_tt) as emp_table
from dept d
;

Using TABLE()

with data as (
  select d.deptno
    ,cast(multiset(
             select * from scott.emp e where e.deptno=d.deptno
           ) as emp_tt) AS emp_table
  from dept d
)
select b.*
from data a, table( a.emp_table ) b
order by empno;

Oracle's Solution

Business Requirement:  I want the first time a hurricane reached its maximum category along with its position.

Other, inferred, identifiers to "GROUP BY" would be season, basin, subbasin in addition to hurricane name.

One method to solve this in Oracle is to use Analytics.

Since I don't have access to the huricane data, I'll have to improvise.

  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp

First thing we do is use the analytic function RANK() to rank all of the rows by category (descending) and time (ascending) but partition the rankings by season, basin, subbasin, and hurricane_name

  select h.*
    ,RANK() over (partition by season, basin, subbasin, huricane_name
                  order by category desc, time)
      as rank_score
  from huricane_data h

Finally, we'll pick only the best (rank_score=1)

select *
from analyized_data
where rank_score=1
order by season, basin, subbasin, hurricane_name, time

Putting it all together

with hurricane_data as (
  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp
), analyized_data as (
  select h.*
    ,RANK() over (partition by season, basin, subbasin, hurricane_name
                  order by category desc, time) rank_score
  from hurricane_data h
)
select *
from analyized_data
where rank_score=1
-- place season/basin/subbasin filters here
order by season, basin, subbasin, hurricane_name, time;


Reclaim unused space I have a DB 150 GB , contains 138 tables one of the tables is 120 GB. I need to archive around 80 GB from this table. Is there a way to reclaim this space without using DB shrink?

thanks

There is no other way than shrinking a db to claim back unused space.
No matter if you truncate or delete data or update with smallers length of data, the .mdf file will stay the same size. Which is good, because shrinking has negative side effects such as fragmentation of files and indexes.

If that table you are archiving later on grows again to the bigger size shrinking will not be a good option here. It's better to have that space already allocated rather than do heavy IO operations on the sub system in some days again. If you know that this table will not grow that big, because you put some maintenance in place, shrink that db once and be prepared to rebuild your indexes.


Transactional replication between SQL Server 2014 (Publisher) and SQL Server 2012 (subscriber) We need to set up transactional replication between two servers A and B, configs listed below:

Server A (Publisher)

  Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Enterprise Edition (64-bit)
  Single database of approx 1.5 TB( this would be the database to be replicated on server B
  CPU - 4 Intel Xeon CPU E7-8893 v2 @ 3.4GHZ

Server B (Subscriber & Distributor)

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64) Standard Edition (64-bit)
  CPU - 1 AMD Operon 6378 processor 64 bit 2.40 GHz (4 Core) (more RAM and better CPU will be added when moved to better Hyper V server)

I am new to the replication concept so would like you're expertise to confirm:

If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be  am looking somewhere wrong. Please suggest if it can be set up?
We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?

Thanks in advance

1) If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be am looking somewhere wrong. Please suggest if it can be set up?

For your replication to work, the PUBLISHER and DISTRIBUTOR should be on same version. The subscriber can be on lower version.

From BOL :

  For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)

  For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.

So, essentially, your SERVER A should be PUBLISHER and DISTRIBUTOR and your SERVER B should be SUBSCRIBER.

  2) We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?

For your scenario, you should enable TDE on PUBLISHER database ONLY.

From BOL :

  Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files.  During transactional or merge replication, encryption can be enabled to protect the communication channel.


SQL job ignoring IF condition I have a job with a few steps(that run reports information) and i added a query i wrote to the last step.
Sometimes the job fails because of factors i can not control (application issues)
and my query basically automatically executes jobs (the commands are running with a cursor in a table i made)when the current time is after 8:45 am, so the BI team won't need to do it themselves.
I checked it a few times and it seems to work when i execute it on ssms, but for some reason when it runs as a job it ignores the time condition and just execute the jobs anyway.

The query:

DECLARE @CodeID INT
declare @codecommand nvarchar(100)
DECLARE crsr CURSOR FAST_FORWARD
    FOR
        SELECT CodeID, CodeCommand
        FROM exec_jobs_on_error
        ORDER BY codeid asc;

    OPEN crsr
    FETCH NEXT FROM crsr
    INTO @CodeID, @CodeCommand

    WHILE @@FETCH_STATUS = 0
    begin

BEGIN TRY
if exists (select 'true' where  LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) > '08:45AM')
begin
EXEC sp_executesql  @codeCommand

INSERT INTO the_daily_BI_error_log
            VALUES(@CodeID, 'Completed', GETDATE())

            end

        END TRY
        BEGIN CATCH
        declare @CodeError nvarchar(100)
            SET @CodeError = (SELECT ERROR_MESSAGE())
            insert into the_daily_BI_error_log
            values(@codeid, @CodeError, GETDATE())
            END CATCH

        FETCH NEXT FROM crsr
        INTO @CodeID, @CodeCommand
        end
    CLOSE crsr
    DEALLOCATE crsr

Am i missing something in the condition writing?thanks.

You are doing a string comparison, not a time comparison. Moreover, the string you return does not include the leading 0.

You might use something like below:

IF CONVERT(TIME,GETDATE()) > '08:45:00'

Here's further proof your expression returns a string:

sp_describe_first_result_set  N'SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))'

For me this shows an output of varchar(7).


Levenshtein function returning different result based on limit value The Levenshtein function is not working as I would have expect. Is there anything that I minsunderstood?
Here is the query:
SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, c0.vin, c0.inserted_at, c0.updated_at
FROM cars AS c0 ORDER BY LEAST(levenshtein(c0.model, 'Camry'), levenshtein(c0.make, 'Toyota'))
LIMIT 5

Executing this query will return me the following data:
5   "electric"      257 32288   "Toyota"    "Camry" "SW081452D50423138" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
20  "gasoline"  83      68851   "Toyota"    "Camry" "643VN327D4ZH04928" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
4   "gasoline"  74      74482   "Toyota"    "Corolla"   "1K48R780410S27945" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
10  "gasoline"  73      87040   "Dodge" "Ram"   "J22782VG240639409" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
3   "electric"      116 66560   "Audi"  "A5"    "94V5772ZB4BJ23179" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"

As you can see above, the first two matches are the Toyota Camrys; which I would expect from the query.
However, when I change the LIMIT attribute to 10 for instance,
SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, c0.vin, c0.inserted_at, c0.updated_at
FROM cars AS c0 ORDER BY LEAST(levenshtein(c0.model, 'Camry'), levenshtein(c0.make, 'Toyota'))
LIMIT 10

I get a different result:
4   "gasoline"  74      74482   "Toyota"    "Corolla"   "1K48R780410S27945" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
5   "electric"      257 32288   "Toyota"    "Camry" "SW081452D50423138" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
20  "gasoline"  83      68851   "Toyota"    "Camry" "643VN327D4ZH04928" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
10  "gasoline"  73      87040   "Dodge" "Ram"   "J22782VG240639409" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
7   "electric"      274 41661   "Dodge" "Charger"   "FDND794KFW0179068" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
8   "gasoline"  57      42369   "BMW"   "M3"    "NS7V3N1VW5J508253" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
9   "electric"      214 15710   "BMW"   "X5"    "3VUFCG07ATW125829" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
11  "electric"      417 63167   "Nissan"    "Juke"  "6800ULHC7H0857158" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
12  "gasoline"  78      21059   "Lincoln"   "MKX"   "AFUCF3SUG6W287040" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"
13  "electric"      348 93954   "Lincoln"   "MKS"   "2L64A6Z18XR348145" "2020-10-06 14:48:27"   "2020-10-06 14:48:27"

Here above, for a reason beyond my understanding, the Toyota Corolla comes before the two Camrys. How come the Levenshtein distance is smaller for "Toyota Corolla" than "Toyota Camry" when I'm explictely searching for "Toyota Camry" and I limit the rows returned by the query to 10?
Any idea why?

The least() function returns the lesser of it's two arguments; both sets of rows have a perfect match for 'Toyota', which will have a distance of 0. So the ultimate evaluated ORDER BY value for each of the 3 rows is 0.
Adding additional ORDER BY expressions of each of the arguments to least() should do what you want: ORDER BY LEAST( levenshtein(c0.model, 'Camry'), levenshtein(c0.make, 'Toyota')), levenshtein(c0.model, 'Camry'),  levenshtein(c0.model, 'Toyota') will return all Camrys first, then any non-Camry Toyotas, and then rows whose least() expression evaluated to something higher than 0.


How to retain backups if automatic backup is enabled Is there a way to retain the backups or configure the interval when they are deleted? According the manuals it's not possible:

  "If backup to disk is selected, the automatic backup feature will
  regularly delete backup images from the directory specified in the
  automatic database backup configuration. Only the most recent backup
  image will be available at any given time, regardless of the number of
  full backups that are specified in the automatic backup policy file."

It seems strange to me that it's not possible to retain backups.

Generally it is desirable to have old backups pruned at some point otherwise your backup file system is liable to run out of space at some point causing further backups to fail (this assumes that more recent backups are more valuable than aged backups, which is generally the case).

In DB2, there are several database-level configuration parameters which dictate when automatic backups are retained or removed which include:

logarchmeth1 - the primary method for archiving logs
auto_del_rec_obj - automatically remove log files, backup images, etc. when the associated recovery history entry is pruned
rec_his_retentn - the number of days that history entries are retained for
num_db_backups - the number of full database backups to retain for a given database

A typical setup might be:

logarchmeth1 set to DISK:somepath to ensure that transaction logs are saved somewhere for recovery
auto_del_rec_obj set to ON to ensure that old backup objects (log files and full backups) are removed when their associated history entries are purged
rec_his_retentn set to 30 to prune history entries after 30 days
num_db_backups set to 4 so that up to 4 full database backups are available at any given time

You might then set up a weekly cronjob to perform a full online backup of the database (presumably during some reasonably low-usage hours; a more nuanced strategy might involve incremental backups). As you'd be performing weekly backups you'd take about 4 full backups per month which pairs nicely with the 30 days of history pruning.

If something went horribly wrong and you needed to restore, you'd do so presumably from the latest weekly backup then roll forward through the retained logs to bring your database back to the point you wanted. And in case something was wrong with that backup you've got three more full backups before it to try as well.

Your backup file-system should largely look after itself with reasonable settings; just keep an eye on the amount of used storage (as your database grows over time so will your full backups, and if you experience a growth in transaction level, the average amount of log storage may go up too) and expand it as necessary to keep it within sensible limits.

There are various caveats to be aware of. Read up on the effects of the PRUNE HISTORY and ARCHIVE LOG commands particularly, in addition to the obvious BACKUP DATABASE and RESTORE DATABASE.


Retrieving database dump after copying from remote server I used command

pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser

to copy database on remote server to my local machine.

Can I somehow retreive the dump obtained after the first part of the command, i.e.

pg_dump -C -h remotehost -U remoteuser db_name

is executed? Can it be stored in some temporary location?

The first command does not store the created file anywhere, so the answer is: no, you can't retrieve that file because there is none.

If you need such a file, you can either re-run the first statement and provide a filename using the -f switch.

Or, if f you have not yet changed your local database,  then you can simply dump that.


MySQL DELETE statement doesn't use index although the same SELECT query does I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following:

id,
cookie_id VARCHAR(45),
country VARCHAR(45),
category VARCHAR(45),
other_non_relevant_columns

Indexes look like this:

SHOW INDEX FROM data;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data  |          0 | PRIMARY                |            1 | id          | A         |    24767570 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | cookie_index           |            1 | cookie_id   | A         |    14440214 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            1 | country     | A         |         498 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            2 | category    | A         |         997 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

So there's a non-unique index on cookie_id, and non-unique index on country+category columns. Now the case is, every week I should run query to

Delete all data belonging to country='Y' AND category='X' (5 to 20 million rows)
Import fresh data (similar amount)

The problem is, deleting the data takes crazy amount of time - that's why I've set up an index on country+category columns. However, 'DELETE' statement still doesn't use the index and instead tries to scan the whole table:

mysql> EXPLAIN DELETE FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys          | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
|  1 | DELETE      | data  | NULL       | ALL  | country_category_index | NULL | NULL    | NULL | 24767570 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+

Select works fine:

mysql> EXPLAIN SELECT id, cookie_id FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys          | key                    | key_len | ref         | rows     | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
|  1 | SIMPLE      | data  | NULL       | ref  | country_category_index | country_category_index | 365     | const,const | 10130630 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+

Is there any way to optimize DELETE query?

By deleting the data first, you are essentially making the data inaccessible.  Don't you want to avoid this "downtime"?

Consider loading the replacement data into a temp table, then doing IODKU to update the main data:

INSERT INTO main (...)
        ON DUPLICATE KEY UPDATE
             col1 = VALUES(col1),
             ...
    SELECT ... FROM temp;

If there could be rows to remove; IODKU won't provide that feature.  However, you could precede it with something like

ALTER TABLE temp ADD INDEX (...);  -- to speed up the LEFT JOIN below

DELETE FROM main
       USING main
        LEFT JOIN temp ON ...
       WHERE temp... IS NULL;


Tuning Clustered Indexes in case of Soft / Logical delete I have a table (say dbo.AppUsers) with a clustered index on the always increasing, application generated column AppUserId.
I have also a Bit column IsDisabled, that is used to logically delete/disable users.
Now, the majority of the queries on the table will include a Where IsDisabled = 0 clause, which is fine.
I'm wondering if I should include the IsDisabled column in the clustered index or if there is a best practice in this scenario.
My thoughts on this:
I should have IsDisabled in the clustered index, because list queries should not read the logically deleted rows from disk.
or...
I should not have IsDisabled in the clustered index, because it could mess up with table fragmentation.
Is there some sort of trade off?
Thanks!

First, testing is your buddy. Run tests on this.
Let's avoid talking too much opinion. This question could be taken as a purely opinion driven one. Instead, let's talk about some of the mechanics of choosing a clustered index.
You get one clustered index on a table because the clustered index defines data storage. Since the clustered index is where the data gets stored, the best rule I've found is to make the clustered index the most frequently used path to the data. For many tables, this is simple, the primary key. It will absolutely be the most commonly used path to the data. However, that's not always the case.
The question here is, how is your data being accessed? Is it through the primary key? If so, adding the IsDisabled shouldn't be necessary. If most queries have the ID value and you're going directly to one row, that's it. Adding IsDisabled won't help. In fact, having a filter that has the ID and IsDisabled will work fine (although, test it, test it, test it) because the principal filter is on a unique value that can be found first and then additional columns will be used to refine that result.
On the other hand, if the primary key is not the most common access path to the data. Rather, you're seeing filtering on date, some foreign key, a set of columns, and then the IsDisabled column, then changing the clustered index may make some sense. Instead of having it on the default of the primary key, put it on the data, fk, or set of columns, along with IsDisabled. Although, again, test it.
Picking the keys for an index, clustered or not, is all about balancing selectivity, so that the statistics which are always built off the first column look as attractive to the optimizer as possible, with the need to define the key so it functions to properly refine the data. The IsDisabled column, at least early in the databases life, is likely going to have only a single value. However, as the database ages, it's going to have two. But only, ever two. So, regardless of whether or not you put that column in the index, it's unlikely to be useful as the leading edge, or first column, of that index. It's always going to be either a second, or last, column, or an INCLUDE column.
I hope all that helps.
Be sure and test it.


How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY? I can make a certificate in the windows certificate store for the master column key easily enough with:

$cert = New-SelfSignedCertificate `
    -Subject "Always Encrypted Sample Cert" `
    -CertStoreLocation Cert:\CurrentUser\My `
    -Type Custom
$thumbprint = $cert.ThumbPrint
Write-Verbose "Cert thumbprint $thumbprint"

$smoDatabase.ExecuteNonQuery(@"
CREATE COLUMN MASTER KEY [$($MasterKeyName)]
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/$($thumbprint)'
);
"@);

The problem come with the CREATE COLUMN ENCRYPTION KEY

CREATE COLUMN ENCRYPTION KEY key_name
WITH VALUES (
    COLUMN_MASTER_KEY = [$($MasterKeyName)],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = $EncryptedValue
);

I don't know how to get the calue for ENCRYPTED_VALUE. I try the following:

C:\Users\zippy> $certProvider = new-object 'System.Data.SqlCLient.SqlColumnEncryptionCertificateStoreProvider'
C:\Users\zippy> $byte = [byte[]]@() # I don't know what to put here
C:\Users\zippy> $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7fe3f88e91e37e69f08c8fa1f4cc48', 'RSA_OAEP', $byte)

And then I get

  Exception calling "EncryptColumnEncryptionKey" with "3" argument(s): "Empty column encryption key specified.
  Parameter name: columnEncryptionKey"
  At line:1 char:1
  + $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7 ...
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : ArgumentException

So how do I get the value to put in EncryptColumnEncryptionKey()?

Since your thumbprint is being returned as a System.String you actually cannot easily convert it to a System.Byte. I have not gotten a full example that you are doing to work completely but will show you how I convert the thumbprint, and it seems to work:

$encode = [System.Text.Encoding]::UTF8
$r1 = $encode.GetBytes($thumbprint)

$sha = New-Object System.Security.Cryptography.SHA256CryptoServiceProvider
$r2 = $sha.ComputeHash($r1)
[System.Convert]::ToBase64String($r2)

$certPath = 'CurrentUser/My/$($cert.Thumbprint)"
$certProvider = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
$certProvider.EncryptColumnEncryptionKey($certPath,'RSA_OAEP',$r2)

The only thing I am getting right now to figure out is an error on Invalid provider type specified. Which I do have the March Preview of SSMS 2016 on my laptop, not sure if that has something to do with this or not.


Case statements for multiple fields when only certain cases exist for each field We have an inventory feature where we generate Bills. There is an Edit Bill API call. We have implemented it as PATCH call.

A Bill with id = 1 has 2 LineItems :

|  Stock Id |   Qty        |  Rate       |
|    10     |      2       |    10       |
|    11     |      3       |    20       |

Now lets say I want to change the quantity for stock Id : 10 to 5 and I want to change the rate for stock Id : 11 to 40

We have represented it as PATCH Call :

bill : {
    id : 1

    lineItems : [
    {
        stockId : 10,
        qty : 5,
     },

     {
        stockId : 11,
        rate : 40
     }
    ]
    }

In the backend we run following query :

 UPDATE `billlineitem`
SET    `rate` = ( CASE
                    WHEN stockid = 11 THEN '40'
                    ELSE rate
                  END ),
       `qty` = ( CASE
                   WHEN stockid = 10 THEN 5
                   ELSE qty
                 END ),
       `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
WHERE  `bill_id` = '1'
       AND `stockid` IN ( 10, 11 )

Is it ok, in the above case when there is no change for an attribute then the else clause will take the value from the database for that attribute. The above update statement is run in a transaction.

Is this a correct approach? Will this do an update for every attribute for every stock Id. Is there a better approach?

We are using MySQL DB.

Apart from qty and rate we also have discountType, discountValue and there are other 4 more attributes.

The single-UPDATE you have seems reasonable.  However, depending on statistics and how well the Optimizer handles IN, this 2-UPDATE approach may be faster.

Either will benefit from

INDEX(bill_id, stockid)

The order of the columns in the index matters.  bill_id needs to be first for the single-update with the CASE statement.  Doing multiple UPDATEs, as follows, would not care which order the INDEX is written.

UPDATE `billlineitem`
    SET    `rate` =  '40',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
    WHERE bill_id = 1
      AND stockid = 11;
UPDATE `billlineitem`
    SET    `rate` =  '5',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
    WHERE bill_id = 1
      AND stockid = 10;

(For updating 15 rows, there would need to be 15 UPDATE statements; this may be impractical.)


Automatic recovery of the failed postgresql master node is not working with pgpool II I am new to Postgresql and Pgpool II setup. I have configured the Postgresql HA/Load balancing using Pgpool II and Repmgr.
I have followed the link to do the setup.

The setup consist of 3 nodes and verison of Application and OS is as mentioned below:
OS version => CentOS 6.8 (On all the 3 nodes)

Pgpool node => 192.168.0.4
Postgresql Nodes:
node1 (Master in read-write mode) => 192.168.0.6
node2 (Standby node in read only mode) => 192.168.0.7

Pgpool II version =>  pgpool-II version 3.5.0 (ekieboshi).
Postgresql Version => PostgreSQL 9.4.8
Repmgr Version => repmgr 3.1.3 (PostgreSQL 9.4.8)

I have configured the Pgpool in Master-Slave mode using Streaming replication.

The setup is as shown in the below image:

When I bring down the Master node(192.168.0.6), the automatic failover happens successfully and the Slave node(192.168.0.7) becomes the new Master node.

After failover, I have to recover the failed node(192.168.0.6) manually and sync it with the new Master node. Then register the node(192.168.0.6) as a new Standby node.

I want to automate the Recovery process of the failed node and add it to the cluster back.

The pgpool.conf file on the Pgpool node(192.168.0.4) contains parameter recovery_1st_stage_command. I have set the parameter recovery_1st_stage_command = 'basebackup.sh'. I have placed the script 'basebackup.sh' file on both the Postgresql nodes(192.168.0.6, 192.168.0.7) under the data directory '/var/lib/pgsql/9.4/data'. Also I have placed the script 'pgpool_remote_start' on both the Postgresql nodes(192.168.0.6, 192.168.0.7) under the directory '/var/lib/pgsql/9.4/data'.

Also created the pgpool extension "pgpool_recovery and pgpool_adm" on both the database node.
After the failover is completed, the 'basebackup.sh' is not executed automatically. I have to run the command 'pcp_recovey_node' manually on the Pgpool node(192.168.0.4) to recover the failed node(192.168.0.6).

How can I automate the execution of pcp_recovery_node command on the Pgpool node with out any manual intervention.

Scripts used by me as follows:

basebackup.sh script

#!/bin/bash
# first stage recovery
# $1 datadir
# $2 desthost
# $3 destdir

#as I'm using repmgr it's not necessary for me to know datadir(master) $1
RECOVERY_NODE=$2
CLUSTER_PATH=$3
#repmgr needs to know the master's ip
MASTERNODE=`/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'`

cmd1=`ssh postgres@$RECOVERY_NODE "repmgr -D $CLUSTER_PATH --force standby clone $MASTERNODE"`
echo $cmd1

pgpool_remote_start script

#! /bin/sh

if [ $# -ne 2 ]
then
    echo "pgpool_remote_start remote_host remote_datadir"
    exit 1
fi

DEST=$1
DESTDIR=$2
PGCTL=/usr/pgsql-9.4/bin/pg_ctl

ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &

failover.sh script

#!/bin/sh
failed_node=$1
new_master=$2
(
date
echo "Failed node: $failed_node"
set -x

# Promote standby/slave to be a new master (old master failed)
/usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

Help me with the procedure to automate the recovery of the failed node.
Also let me know, for failover is it compulsory to use repmgr or we can do it without repmgr. Also specify any other method for failover without using Repmgr, its advantages and disadvantages over Repmgr.

It is as designed: when pgpool does the failover, the failed master is ejected from the pool so that there is a protection against a split-brain. If the failed master comes back again (degenrated master), pgpool will not automatically perform the recovery operation.  You have to manually execute the command pcp_recovery_node command.

If you want to automate this, then you can have a cron job running on each database, this cron would check the status of the database and when it detects a degenerated master it would execute the pcp_recovery_node command. This script can also take care of re-attaching failed standby databases (when a standby fails, pgpool ejects it from the pool and it does not re-attach it when the standby comes back).

I did something like that, it is a bit tricky, you must make sure that the script is never executed twice, for example if the cron interval is shorted than the time to perform the recovery (have a mechanism of a pid file to protect agains that). Otherwise it works.

As for repmgr, I use their scripts to perform failover, recovery, etc. but I don't use repmgrd to automate the failover, I found it safer to have the automatic failover done by pgpool.


Connect to oracle 11 on a corporate network via oracle developer 17 Sorry for this absolute oracle noob question:

I am in an environment that uses integrated / active directory authentication, for developer to access the oracle databases via an old version 9 PL/SQL Developer:

Clicking OK gets me into the dababase

I am trying to connect to the same database via Oracle SQL Developer 17 and the login screen is more complicated:

What do I enter here? I've searched far and wide but there are few resources for an oracle noob like me.

EDBDEV is a TNS alias.

If you search for a file called tnsnames.ora, that should contain an entry similar to below:

EDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EDBDEV)
    )
  )

Based on that, you can fill in the required connection details.


German umlaute represented by questionmarks when latin1 is used I'm almost certain this has come up before but I couldn't find anything regarding it.

My problem is that I've a database and a database server (and every table and column inside) set to latin1/latin1_general_ci.

Curiously when I connect with a client (for example HeidiSQL) client specific variables regarding character sets are set to utf8mb4, even more curious: äüöß are shown correctly.

When I execute SET NAMES latin1; and check again, the variables are set to latin1, as they should be, but the Umlaute are now displayed as question marks. This is also true if they where inserted AFTER SET NAMES latin1; was executed.

Another database server is completely set to utf8, except the database my program works with, which is again set to latin1. If I execute SET NAMES latin1; there it works as expected.

Could someone explain/tell me what is going on here? What am I doing wrong?

The variables I'm talking about are:

character_set_client [Global/Default: latin1]
character_set_connection [Global/Default: latin1]
character_set_database [Global/Default: latin1]
character_set_results [Global/Default: latin1]
character_set_server [Global/Default: latin1]
collation_connection [Global/Default: latin1_general_ci]

After some toying around it seems like character_set_results is the problematic one here. Setting it to latin1, while leaving the other ones at utf8mb4, results in question marks being displayed. Still no idea why this happens.

edit

Result of SHOW CREATE TABLE adressen:

CREATE TABLE `adressen` (
  `ID` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `Strasse` longtext,
  `Strasse2` longtext,
  `Hausnummer` longtext,
  `Postleitzahl` longtext,
  `Ort` longtext NOT NULL,
  `Postfach` longtext,
  `IDLand` int(9) unsigned NOT NULL,
  `Laengengrad` float NOT NULL,
  `Breitengrad` float NOT NULL,
  `Adresszusatz` longtext,
  PRIMARY KEY (`ID`),
  KEY `FK_Adressen_Land` (`IDLand`),
  CONSTRAINT `FK_Adressen_Land` FOREIGN KEY (`IDLand`) REFERENCES `laender` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=137205 DEFAULT CHARSET=latin1

SELECT STRASSE, HEX(STRASSE) FROM adressen WHERE STRASSE LIKE 'ä%':
    Ä[...];C4[...]

According to Wikipedia's article about ISO8859 that's latin1 alright.

edit2

After some further reading I found that both, HeidiSQL and .NET handle text in utf8 / unicode [at least by default]. The solution to this question is thus to convert everything to utf8mb4_unicode_ci as per this answer.

See "Question Marks" in here .  In particular,

The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
The column in the database is CHARACTER SET utf8 (or utf8mb4). Fix this.
Also, check that the connection during reading is UTF-8.

Since you seem to be aiming for latin1, not utf8, see "Test the data" in that link.  äüöß will be hex E4 FC F6 DF in latin1, or C3A4 C3BC C3B6 C39F in utf8/utf8mb4.

SET NAMES establishes the encoding in the client, not the server.  The table's encoding is based on SHOW CREATE TABLE; let's see that.

OR...

It is OK if you want latin1 used in the columns/tables, but your client wants utf8.  Then the connection setting (to say utf8 / UTF-8) or the use of SET NAMES utf8 (after connecting) will alert MySQL to convert as data is transferred between the client and the server and database.

(With latin1, you are limited to Western European characters.)


Normalization for three somehow identical tables These three tables are nearly identical. But, there are some exceptions.

But, I can't find best normalized structure. Could you please help me?

Here is the informations must be hold in database:

 1) APARTMENTS
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      APARTMENT
      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

 2) GARDENES
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING

      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

      TOTAL_GARDEN_SPACE

 3) PLOT_OF_LAND
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      LAND_SPACE

What is the best structure for this tables?

Yet, I have made two structures:

FIRST ONE:

But, it seems not the best to me. So, i have made second choice:
SECOND ONE:
May be I must make on table with the name POSSESSİONS? And inside this:

POSSESSIONS
   ID
   TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)
   SUBSCRIBER_ID
   ADDRESS_ID

   LIVING_SPACE
   TOTAL_GARDEN_SPACE
   LAND_SPACE

What is your recomendations?

The question one has to ask is: Will I ever need the address id and the subscriber id more than once? If the answere to this is yes, go with the second approach. If the answere is no you could still use the second approach, but most of the time it is more convenient to use the first one.

If the triple ELECTRIC_SUBSCRIBER_NUMBER, GAS_SUBSCRIBER_NUMBER, WATER_SUBSCRIBER_NUMBER will always be the same for an owner, then I would make a separate table out of it, because it may happen, that one owner has multiple possessions.

Since the address of a posession and the space does uniquily belong to one possession there is no reason to split it out.

My approach would look like this:

POSSESSIONS
  ID
  [TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)]
  CITY_ID
  VILLAGE_ID
  STREET_NAME
  BUILDING
  APARTMENT (nullable)
  SPACE
  SUBSCRIBER_ID (nullable)

SUBSCRIBERS
  ID
  ELECTRIC_SUBSCRIBER_NUMBER
  GAS_SUBSCRIBER_NUMBER
  WATER_SUBSCRIBER_NUMBER

You may skip the TYPE field, because the information is already encoded in the Nulls of APARTMENT and SUBSCRIBER_ID.

Normalization is all about eliminating redundacies. A good approach to work this out is, to take your scheme with good sample data and then try to change some information by hand. If you have to change data in multiple places for one bit of "atomic information", you should probabbly rethink your scheme. If you need to think about where this information was, because you have multiple tables with similar information schemes, then again, you should probabbly rethink your scheme.


History of User privileges held In Oracle 12c or 11g, is it possible to fetch the history of user privileges held by a schema? Say from last two months, I might have granted different roles and privileges. I can get the current privileges the schema holds, but is it possible to know the privileges I previously granted?

Edit:
I have standard auditing enabled 'DB'. FGA is not enabled.

Well there is no official method of getting the list of previous privileges list of a user or a role. But my suggestion would be doing a Fine-grained auditing on the role granted to the user. Or you can do standard auditing on the role by issuing audit <role_name>; . Here please note that if you are going to audit DBA or SYSDBA roles then set audit_sys_operations parameter to TRUE. Thank you.


Adding a Clustered index, should I drop the nonclustered indexes I am adding a clustered index as an online operation to a table that has 3 billion rows. It has taken over 17 hours so far. If I do this again on another table with just as many rows should I first drop the nonclustered indexes?

Dropping the nonclustered indexes will make converting the heap to a clustered index faster since those nonclustered indexes will not need to be updated.

However, you probably will want to recreate them after the conversion process is complete, so make sure you script the CREATE INDEX scripts prior to dropping them.

Be aware, dropping the nonclustered indexes means any queries that run during the conversion may run very slowly since they may be expecting indexes that are no longer there.


Kill connection in pgadmin4 Postgresql Can you tell me how to kill connection in pgadmin 4 web postgresql without executing any scripts?

How can I limit the number of connection to database in order to kill the active connection (My case I want to rename database already used !)

pgAdmin III can show information about all connections by using the Tools > Server Status. This option shows a window with four panes:

Acitivity (showing all connections, with PID, application name, database, user, etc). In this pane, you can select one connection and either Cancel the query or Stop the backend by means of two little orange and red buttons. These correspond to pg_cancel_backend and pg_terminate_backend PostgreSQL function calls.
Locks
Prepared transactions
LogFile

pgAdmin III, when choosing a Database Cluster from the Object Navigator, will show, under the Statistics tab, all connections with their running queries.

pgAdmin 4, on the other hand, does not have a Tools > Server Status menu. Instead, it's got a Dashboard tab, which has the same functionality, except for three things:

The Sessions tab (equivalent to the Sessions pane in pgAdmin III) does not have any buttons. So, the functionality to cancel a backend is by means of the GUI is not there.
THe LogFile functionality is not present. I assume this is due to the new architecture of pgAdmin 4, that works as if you were remotely connecting through a web server, which is supposed to not have access to local files, but only to the database. The database itself writes on the log files, but it does not read from them.
There is also a Configuration tab, shown when selecting the database cluster, showing all values from postgresql.conf (through database calls, I assume).

The statistics tab is also present, with euivalent functionality.

NOTES: Checked on pgAdmin III v 1.22.2  and pgAdmin 4 v 1.4


you can use the WITH ROLLUP in the group by to achieve multi level aggregation. try something like this SQL Fiddle

create table table1
(
register_time date,registered int
);

insert into table1 values('2015/03/31',10);
insert into table1 values('2015/03/31',20);
insert into table1 values('2015/03/21',30);
insert into table1 values('2015/02/01',15);
insert into table1 values('2015/02/01',1);
insert into table1 values('2015/02/02',25);
insert into table1 values('2015/01/31',11);

SELECT MONTH(ds.register_time) Month,DAY(ds.register_time) day,SUM(registered) AS totalOftheDay
  FROM table1 ds
  GROUP BY MONTH(ds.register_time),DAY(ds.register_time) WITH ROLLUP


How to Capture Job Exit Code? I'm essentially trying to find a better way to determine if my job has completed successfully before moving on to another call of the same job. I have multiple jobs which run through a parsing process before calling this one job in particular, but I don't want all the job calls to clash and ruin everything. What I have in place now to account for this looks like:

This basically just checks to see if the job_ID persists in the sys_jobs_view and sysjobactivity. If so, wait. If not, break and execute job again.

WHILE EXISTS (SELECT
            job.Name, job.job_ID
            ,job.Originating_Server
            ,activity.run_requested_Date
            ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
        FROM
            msdb.dbo.sysjobs_view job
                INNER JOIN msdb.dbo.sysjobactivity activity
                ON (job.job_id = activity.job_id)
        WHERE
            run_Requested_date is not null
            AND stop_execution_date is null
            AND job.name like 'myJob%')
        BEGIN
            WAITFOR DELAY '00:00:05.000'
        END
EXEC msdb.dbo.sp_start_job 'myJob'

Is there a way to just capture the exit code and have something that looks like:

pesudo-code:

WHILE (*Capture Exit code for 'myJob'* = In_Progress)
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (*Capture Exit code for 'myJob'* = Fail)
    BEGIN
        BREAK
    END
END

IF (*Capture Exit code for 'myJob'* = Success)
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END

If you create a FUNCTION and query the sysjobhistory table in MSDB you can get a simple response on the most recent job execution.

Create the function:

CREATE FUNCTION [dbo].[fnGetLatestAgentJobExitCode]
(
    @JobName VARCHAR(100)
)
RETURNS VARCHAR(25)
AS
BEGIN
    RETURN(
        SELECT TOP (1)
            CASE jh.run_status
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'In Progress'
            END AS [Status]
        FROM [msdb].[dbo].[sysjobactivity] ja
        INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = ja.job_id
        INNER JOIN [msdb].[dbo].[sysjobhistory] jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id
        WHERE j.[name] = @JobName
        ORDER BY ja.start_execution_date DESC)
END

Then query it like so:

SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob')

This will give one of the following results:

Failed
Succeeded
Retry
Cancelled
In Progress

Or will return no result if there is no job history. To use this in your specific use case would be simple:

WHILE (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'In Progress')
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Failed')
    BEGIN
        BREAK
    END
END

IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Succeeded')
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END


How to consolidate script duplication? I just want to know how you deal with long scripts and redundant information. Here is an example:

Suppose you have three tables: table1, table2, and sometable.

And you have

SELECT
 id  = 1
 org = 'company'
 name = 'name'
 addr = 'arr'
 lang = 1
cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table1
FROM   sometable

SELECT
 id  = 2
 org = 'company2'
 name = 'name'
 addr = 'arr'
 lang = 1
 cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table2
FROM   sometable

So the difference is that the starting stuff (not the cnt* fields) are changing. But as you can see there is a lot of overlap.

Also if I want to change the logic on the counts (which are common to both), then i have to go through both select statements and change them manually. this is error prone.

I was thinking something along this pseudocode:

   SELECT
 id  = 1
 org = @variable
 name = @variable
 addr = 'arr'
 lang = 1

  INCLUDE other columns/logic from somewhere else

INTO  table2
FROM   sometable

that way, if my logic changes for the cnt fields, I can just change it in one spot and it would change everywhere.

I am open to any suggestions. SMSS addin, stored procs, or some other ugly or beautiful method people know.

If you have something THAT similar and you need to call it more than once or twice, I would put it into another stored proc that you can call:

CREATE PROCEDURE dbo.InsertStuff (@id int, @org varchar(100), @name varchar(100), @lang int)
AS

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT id = ' + @id + ' ' +
           'org = ''' + @org + ''' ' +
           'name = ''' + @name + ''' ' +
           'lang = ''' + @lang + ''' ' +
           'cnt 1 = CASE WHEN Col1 = 1 THEN 1 ELSE 2'
           -- All your other common stuff here

EXEC (@SQL)


Postgres table partitioning "no partition of relation "parsel_part" found for row" error? I'm trying to use new partitioning method of PostgreSQL 10. I have a parent table that contains 1.5 million rows. I want to create partitioning on this already populated table.

I've created a  new master table which has identical columns with real master table from the CREATE SCRIPT;

CREATE TABLE master_part (objectid integer,poly geometry(Geometry,2321), parcel character varying(255), m_date(date)) PARTITION BY RANGE (m_date);

Then I've created child tables, partitioned by the m_date column;

CREATE TABLE parsel_2014_04
  PARTITION OF parsel_part FOR VALUES FROM ('2014-04-01') TO ('2014-04-30');

CREATE TABLE parsel_2014_05
      PARTITION OF parsel_part FOR VALUES FROM ('2014-05-01') TO ('2014-05-31');

CREATE TABLE parsel_2014_06
      PARTITION OF parsel_part FOR VALUES FROM ('2014-06-01') TO ('2014-06-30');

CREATE TABLE parsel_2014_07
      PARTITION OF parsel_part FOR VALUES FROM ('2014-07-01') TO ('2014-07-31');

CREATE TABLE parsel_2014_08
      PARTITION OF parsel_part FOR VALUES FROM ('2014-08-01') TO ('2014-08-31');

CREATE TABLE parsel_2014_09
      PARTITION OF parsel_part FOR VALUES FROM ('2014-09-01') TO ('2014-09-30');

CREATE TABLE parsel_2014_10
      PARTITION OF parsel_part FOR VALUES FROM ('2014-10-01') TO ('2014-10-30');

CREATE TABLE parsel_2014_11
      PARTITION OF parsel_part FOR VALUES FROM ('2014-11-01') TO ('2014-11-30');

CREATE TABLE parsel_2014_12
      PARTITION OF parsel_part FOR VALUES FROM ('2014-12-01') TO ('2014-12-31');

CREATE TABLE parsel_2015_01
      PARTITION OF parsel_part FOR VALUES FROM ('2015-01-01') TO ('2015-01-31');

CREATE TABLE parsel_2015_02
      PARTITION OF parsel_part FOR VALUES FROM ('2015-02-01') TO ('2015-02-28');

CREATE TABLE parsel_2015_03
      PARTITION OF parsel_part FOR VALUES FROM ('2015-03-01') TO ('2015-03-31');

CREATE TABLE parsel_2015_04
      PARTITION OF parsel_part FOR VALUES FROM ('2015-04-01') TO ('2015-04-30');

When I run the script above to create child tables I'm getting the error below;

  ERROR:  no partition of relation "parsel_part" found for row
  DETAIL:  Partition key of the failing row contains (m_date) = (2014-10-31).
  SQL state: 23514

This error seems to be very rare because I couldn't find anything about it.

Maybe some one have seen it before ?

Your problem is related to this point in the documentation:

  When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

(emphasis added)

So in fact in

CREATE TABLE parsel_2014_10 PARTITION OF parsel_part
     FOR VALUES FROM ('2014-10-01') TO ('2014-10-31');

the date 2014-10-31 is not included in this partition, and hence nowhere in all your partition tables (and same for all ending dates)

See the example at: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

The to value of one partition must be the same as the from value of the next one (because the to part is exclusive and the from is inclusive).


How to use Common Table Expression properly - Example where it seems to be very slow in TSQL I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp table. I will put the two examples below, the first one using common table expressions takes a very long time to run (3min 27sec) and the second one using temp tables which only takes 0:50. Is this an inappropriate way to use CTE or am I implementing it poorly?

;WITH tmpRiskFactor AS  --Super slow. 3:27
(SELECT col1,col2,col3,col4,col5 FROM [tablevaluedfunction]() )
(SELECT DISTINCT col1 AS [RiskFactors] FROM tmpRiskFactor
UNION
SELECT DISTINCT col2 FROM tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM tmpRiskFactor
) AS tmp;

Using temp:

SELECT col1,col2,col3,col4,col5 INTO #tmpRiskFactor FROM [tablevaluedfunction]() --A lot faster. 0:50
SELECT * INTO #RiskFactors FROM
(SELECT DISTINCT col1 AS [RiskFactors] FROM #tmpRiskFactor
UNION
SELECT DISTINCT col2  FROM #tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM #tmpRiskFactor
) AS tmp;

To expand a bit more on Jonathan's comment, each time you reference a CTE, the query within the CTE will be called.

Each UNION you are using is creating a separate execution of the CTE, thereby executing the table value function each time as well. By putting the results into a temp table, you are only then reading the static data in the temp table for each UNION and avoid recalculating the data.

If you check the execution plan for each of the queries, you'll be able to see a visual confirmation of this fact.

In your case, it makes much more sense to use a temp table as illustrated by the vastly different execution times. I personally find non-recursive CTEs rarely offer much by way of performance and are usually more helpful for readability  and logical separation of query contents.


Replacing last 3 characters of values I presumably appended .png to all entries in the column image like this:

update genres set image=image||'.png'.

I then realised that the images I'm referencing will be jpegs; and I'm too lazy to go and convert them; so I'd rather spend 10 minutes trying to form an SQL query and then result to serverfault; so I want to change them again.

I've tried:

update genres set image = image.slice(0,-4)||'.png';

and then I remember it's SQL, everything is as confusing as its string-concatenation operator.

So then I looked up a few string functions and came up with this:

UPDATE genres SET image = right(overlay(right(image) placing right('.png') FROM 0));

But it just doesn't work. There's always someone who's been working with SQL for 20 years that can write this paragraph in 10 characters of functional code.

I'm postgresql, it's basically the same thing.

There are several ways to do that.

The first one that Rick already mentioned will work on Postgres as well:

update genres
   set image = replace(image, '.png', '.jpg');

But that will not work if the file names contain .png before the end. To be sure to only replace that value at the end you can use a regular expression:

update genres
   set image = regexp_replace(image, '(.*)\.png$', '\1.jpg');

or you can simply use the first "length - 3" characters and append the new extension to that.

update genres
   set image = left(image, -3)||'png';

Alternatively as you seem to not like the || operator that has been defined for SQL 30 years ago:

update genres
   set image = concat(left(image, -3), 'png');


Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a function which deletes 100,000 records from a main table and all of its associated data from ancillary tables, the initial delete takes about an hour.

If I wait 10 minutes before running the function, it runs in about 16 - 20 seconds.  I run this function 120 times in separate transactions in this script, then rebuild my constraints and indexes at the end (postgres was hanging in the middle if I try to keep them all in the same transaction and even at 500000 per transaction sometimes resulted in a performance hit).

Is there anything which postgres does following index/constraint changes that could account for the need to wait on running my deletes?

First, sorry if I have a bad English.

Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of the deletion that you're trying to do.

Create indexes on the foreign keys of the child columns that are pointed to the main table you are trying to delete, this will solve the problem and improve performance for inner join also. Remember PostgreSQL will not create indexes for the foreign keys automatically.


Script Contents of SQL Server Database From a Batch File I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio programmatically.

I know this is possible using .NET and SMO, but is there something built-in to do this?

Background to this question: This is meant as a simple auditing instrument. We want to capture the schema every night. Any primitive, low-tech solution works for us.

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir


MySQL dead-locking with UPDATE JOIN and INSERT SELECT statements We've run into a considerable number of dead-locks recently and they seem to be happening at this query:

  INSERT
    INTO link_click (link_id, created_at, weight)
  SELECT :link_id, :created_at, lw.weight
    FROM link_weight lw
   WHERE lw.link_id = :link_id AND lw.start_at < :created_at
ORDER BY lw.start_at DESC
   LIMIT 1

Where link_id and created_at are passed in as PDO parameters
This is part of a larger transaction.

The dead-locks only appear to occur when the following separate query is running on a cron every 30 minutes:

UPDATE owner o
  JOIN (
    SELECT l.owner_id,
           SUM((lc.state = 1) * lc.weight) total_pending,
           SUM((lc.state = 2) * lc.weight) total_confirmed,
           SUM((lc.state = 3) * lc.weight) total_awaiting_processing,
           SUM((lc.state = 4) * lc.weight) total_processed,
           SUM((lc.state = 5) * lc.weight) total_invalid
      FROM link_click lc
      JOIN link l
        ON l.id = lc.link_id
     WHERE (:owner_id IS NULL OR l.owner_id = :owner_id)
       AND lc.state != 0
  GROUP BY l.owner_id
       ) raw
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid

Where owner_id is passed in. This is a one shot query not in a transaction but takes roughly 30s to complete.

I wasn't aware that INSERT SELECT or UPDATE JOIN would lock any rows beyond themselves and cause such a dead-lock.

I guess my question is whether separating out the SELECTs (I don't need a FOR UPDATE lock) from each query would help this situation?

Or is it more likely that the dead-lock is coming from the first query's transaction and the second query is just slowing down the database and that's making the dead-locks more frequent?

Add these indexes:

link_weight:  (link_id, start_at, weight) -- in this order
link:         (owner_id, id)              -- in this order
link_click:   (state, owner_id)           -- in this order
owner:        (owner_id, total_processed) -- in either order

If owner has PRIMARY KEY(owner_id) then don't add the owner index above.

The goal is to speed up processing to minimize deadlocks.  Since deadlocks probably cannot be completely eliminated, be read to rerun transactions when they do occur.

If that does not suffice, break up the UPDATE into two steps:

CREATE TEMPORARY TABLE raw
      ( INDEX(owner_id, total_processed) )
AS
    SELECT ...;   -- from your existing code
UPDATE owner o
  JOIN raw
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid;

If you need further help, please provide

EXPLAIN SELECT ...
EXPLAIN UPDATE ...
SHOW CREATE TABLE ...


How would I maintain referential integrity across a database where I use versioned records while keeping data in sync? I am building a database that consists of a few tables that are used as data sources for a form our application generates.

[form]     [question]       [responses]
id         id               id
name       form_id          question_id
desc       question         response
created    created          created
modified   modified         modified

This is a loose example, but a web form is generated using [form] and then questions generated using [question] table. The responses are captured in [responses] table. The problem I see with this, is if a response is captured, and then the question is modified, the historical data will be out of sync (meaning that the response would be directed at the original pre-modified question). This would be an auditors nightmare.

What is the best way to approach this? I feel I need to keep some sort of version information or shadow records of some kind, but I am not sure the best way to go about it.

The three ways I can see this being handled is to either:

1) Have an audit table that tracks any changes to the questions\forms tables. This would store the records being updated before they are changed, along with a date.

2) If you foresee the questions\forms being changed often, it might be better to store the actual questions in the response table, only use the question table to generate the forms.

3) Don't allow questions to be changed at all. Any modifications to a question would generate a brand new question_id. This would ensure data integrity.


Replicate single table from Postgres 9.3 replica I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication.

I need to replicate a single table from a database on that server to another physical server.

I've tried using bucardo but it doesn't like talking to read-only databases.

Is there a way to trigger an update on the remote server from the replica?

You will need to replicate the table from the read/write master.

PostgreSQL's built-in physical streaming replication can only replicate the whole database server instance.

The replication systems that support single-table replication require the ability to use a trigger to write a record of changes. This can only be done on the upstream read/write master.

The BDR project plans to deliver a unidirectional version that can stream changes using only the transaction logs, with individual table selection. That would meet your needs, but it won't be ready for a few months at best, possibly longer. Keep an eye out on the PostgreSQL mailing lists and the above wiki for discussion of "UDR" if you're interested.


How to set all auto increments to 1 in a Schema Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)

If the tables already have rows, you can't do it.

If you want to throw away all the data, then you could do TRUNCATE TABLE for each table.

Or you could get all the table definitions via mysqldump --no-data and make sure there are DROP TABLE for each.  Then have a script that runs the output.


InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT I am working with mysql version 5.7.14 (homebrew installation on OS X El Capitan). My system did not shutdown properly while the mysql was running and after rebooting when i try starting the mysql daemon i am getting the below errors.

2017-02-07T10:11:42.224506Z 0 [Note] mysqld (mysqld 5.7.14) starting as process 18234 ...
2017-02-07T10:11:42.227375Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2017-02-07T10:11:42.228809Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-07T10:11:42.228821Z 0 [Note] InnoDB: Uses event mutexes
2017-02-07T10:11:42.228826Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-02-07T10:11:42.228830Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-07T10:11:42.229114Z 0 [Note] InnoDB: Number of pools: 1
2017-02-07T10:11:42.229227Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-02-07T10:11:42.230512Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-02-07T10:11:42.239632Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-07T10:11:42.270103Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-07T10:11:42.270567Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 44002250712 and the end 44002250240.
2017-02-07T10:11:42.270606Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-02-07T10:11:42.577436Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-02-07T10:11:42.577470Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-02-07T10:11:42.577484Z 0 [ERROR] Failed to initialize plugins.
2017-02-07T10:11:42.577488Z 0 [ERROR] Aborting

I tried restarting mysqld with innodb-force-recovery=1 but no success. Can anyone guide me as to how to recover from this state.

Try the following:

Set innodb_log_checksums = ON on master and slave.
Remove rm /var/lib/mysql/ib_logfile*.
Restart.