switching from normal list partitioning to reference partitioning in oracle 11g I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle 11g). My child tables currently have 2 partitions each, both of which I need to drop and replace with reference partition.

Currently I am facing issues with dropping the last partition and adding the new reference partition using ALTER commands.

Is there a way to do this as I cannot drop the tables and lose the data
There are indexes created on the partition column.

The queries I am using:

ALTER TABLE CHILD_TABLE_1
    DROP PARTITIONS part1, part2
    UPDATE INDEXES; --- here only one partition was dropped

ALTER TABLE CHILD_TABLE_1 ADD PARTITION BY REFERENCE (foreign_key_with_parent);

this gives error

  Error: ORA-00902: invalid datatype SQLState:  42000 ErrorCode: 902

A much faster, way cooler, but also much more complex way would be to use partition exchange.

I would use it only if

there is enough data (say > 1 million rows, or partitions > 1 GB)
you have a test database/schema with identical structure and sufficient amounts of data, which can be easily recreated (in my experience, the scripts usually work only correct after the second try)
you have the time to develope and test the migration script

Given a mockup with minimal tables, data and columns:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, t VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

INSERT INTO parent(id,par,c)
SELECT object_id, mod(object_id,10) as par, object_name FROM all_objects;

INSERT INTO child(id,par,t)
SELECT object_id, mod(object_id,10) as par, object_type FROM all_objects;

First, you create holding tables to park the data:

CREATE TABLE temp_parent_p1 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_parent_p2 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_child_p1  AS SELECT * FROM child  WHERE 1=0;
CREATE TABLE temp_child_p2  AS SELECT * FROM child  WHERE 1=0;

Next, you'll swap out the data, children first, then parents:

ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;
DROP  TABLE child;
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;
DROP  TABLE parent;

Then you'll recreate the new table structure:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9),
  PARTITION p2 VALUES(0,2,4,6,8));

Now the parked partitions need to be indexed. The columns and constraints need to have exactly the same structure as the new tables (but don't worry about the names). Then you can swap the data back in:

ALTER TABLE temp_parent_p1 ADD PRIMARY KEY (id);
ALTER TABLE temp_parent_p2 ADD PRIMARY KEY (id);
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;

After the parent is done, the children can be swapped back in:

ALTER TABLE temp_child_p1 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE temp_child_p2 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;

A little bit of clean up afterwards:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'parent');
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'child');
END;
/
DROP TABLE temp_parent_p1;
DROP TABLE temp_parent_p2;
DROP TABLE temp_child_p1;
DROP TABLE temp_child_p2;


Extract date from partition high_value In the USER_TAB_PARITIONS, there is the attribute HIGH_VALUE, formatted TIMESTAMP' 2017-01-01 00:00:00'.

My problem:
I want to convert HIGH_VALUE into a date with a simple query (without procedure, and HIGH_VALUE is as long stored).

So far I could transform it with that procedure:

set serveroutput on size 30000;
DECLARE
   CURSOR c1 IS
   SELECT HIGH_VALUE, PARTITION_NAME
   FROM USER_TAB_PARTITIONS
   WHERE TABLE_NAME = 'MY_TABLE';

   partition_date   VARCHAR2(100);
   partition_name   VARCHAR2(1000);
   date_val         DATE;
   name_val         VARCHAR2(100);
BEGIN
   FOR c1rec IN c1 LOOP
      partition_date := 'SELECT ' || c1rec.HIGH_VALUE || ' FROM DUAL';
      partition_name := 'SELECT ' || c1rec.PARTITION_NAME || ' FROM DUAL';
      EXECUTE IMMEDIATE partition_date INTO date_val;
      DBMS_OUTPUT.PUT_LINE(date_val);
      DBMS_OUTPUT.PUT_LINE(c1rec.PARTITION_NAME);
   END LOOP;
END;
/

My question:
Is there any possibility to do it without a procedure? For example like:

SELECT HIGH_VALUE, PARTITION_NAME
FROM USER_TAB_PARTITIONS
WHERE 1=1
  AND TABLE_NAME = 'MY_TABLE'
  AND HIGH_VALUE < sysdate-1;

Something like this?  This will help you extract the LONG into text without a procedure, but I'm not sure about what SYSDATE logic you want to limit your result set to.  You would have to play with that final WHERE clause to get the right values.

SELECT * from (
  SELECT PARTITION_NAME,
    extractvalue
      ( dbms_xmlgen.getxmltype
        ( 'select high_value
           from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
        '//text()' ) as high_value
  FROM DBA_TAB_PARTITIONS t
  WHERE TABLE_NAME = 'MY_TABLE')
WHERE to_char(add_months(sysdate,-1),'SYYYYMMDD') < high_value;


Select one column by two different names in the same query I have a table named Main that contains two fields id,name.

I want to select the name column by two different names. For example:

Table Main

id:int, name:varchar(20)

I have inserted the following records:

id  | name
----+-------
  1 | Ahmad
  2 | Sami

I want to select the data like this:

select Main.name as n1,Main.name as n2 from Main
where n1='Ahmad' and n2='Sami'

When I execute this query, it gives the following error:

  ERROR:  column "n1" does not exist
  LINE 1: select Main.name n1,Main.name n2 from Main where n1='Ahmad' ...`

I use PostgreSQL as my DBMS.

select n1.name as "n1", n2.name as "n2"
 from main n1, main n2
 where n1.name = 'Ahmad'
     and n2.name = 'Sami';


MySQL - Many UPDATEs to run after INSERT I have a basic CRUD application where people are adding records. When they INSERT someone new, there are many updates (about 100) that need to be done to the record after it's been added.

I have tried to put the UPDATE statements in a TRIGGER but it would either lock the table --OR-- give me the "can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger" error.

Then I tried to put them all in a PROCEDURE -- which took about 30 seconds to run. Unacceptable.

I don't think I'm doing anything difficult here. Mostly, the updates are updating one field based on another.

Example

INSERT INTO data_table (field1, field2, ...) VALUES ('$field1', '$field2', ...);

Then the updates:

UPDATE data_table SET field2 = 'New York City' WHERE field1 = '212';
UPDATE data_table SET field2 = 'Los Angeles' WHERE field1 = '213';

etc.

Would it be faster/more efficient to do a bunch of IF...THEN statements in PHP to change the variable before inserting?

I'm just trying to speed up the process of inserting the record and processing the updates. Any help?

Let us suppose that you have built a reference data set that has two (or more) fields.  For the sake of argument lets call the table geography

Area_Code
City

The MySQL update syntax would be

UPDATE data_table, geography
SET data_table.field2 = geography.City
WHERE data_table.field1 = geography.Area_Code

Potential pitfalls to watch out for are the data types, size and collation discrepancies between data_table.field1 and geography.Area_Code.

If geography.Area_Code is unique and must always be present make sure you stick a primary key on it.

It is probably worth  adding an index to data_table.field1.  Again, if it is mandatory and unique make it a primary key.


Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update? CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        'Column has been altered.' -- Want this to be custom.
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO

This works but I want that indicated line to be dynamic based on 3 situations.

IF i.ColumnName is not null and d.ColumnName is not null THEN 'Column value has been altered'

IF i.ColumnName is null and d.ColumnName is not null THEN 'Column value has been deleted.'

IF i.ColumnName is not null and d.ColumnName is null THEN 'Column value has been added.'

Is it possible to this?

Use a CASE to evaluate it value:

CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        CASE
            WHEN i.ColumnName is not null and d.ColumnName is not null
                 THEN 'Column value has been altered'
            WHEN i.ColumnName is null and d.ColumnName is not null
                 THEN 'Column value has been deleted.'
            WHEN i.ColumnName is not null and d.ColumnName is null
                 THEN 'Column value has been added.'
        END
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO


When to use CDC to track history? SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table.

Through the use of special table value functions (TVF) it then allows the user to query this data, making it either possible to get all the changes on a specific table or only the net changes that resulted from the changes within a specific time.

CDC has certain advantages

It can be configured to only track certain tables or columns.
It is able to handle model changes to a certain degree.
It does not affect performance as heavily as triggers because it works with the transaction logs.
It is easily enabled/disabled and does not require additional columns on the table that should be tracked.

It also has some disadvantages:

The amount of history data can become huge fast.
You are not able to track who made the changes (at least not for deletes).
The history data takes some time to catch up, because it is based on the transaction logs.
It depends on the SQL Server Agent. If the Agent is not running or crashes, no history is being tracked.

I have read quite a lot about CDC and while I know now how to use it, I am still not sure if it is the right tool for me.

For which tasks/scenarios is CDC the right tool? (e.g. Allowing users to restore a data object to a certain point in time? Auditing? Showing the complete history of data?)
When should you rather not use CDC, but resort to a custom trigger-based solution?
Is it ok to use CDC in an operational database and make use of the CDC data within an operational application? (e.g. showing it to the end user) Or is this clearly a misuse of this feature?

I commonly hear that CDC is an audit tool, but isnt that what SQL Server Audit is for? Are they both different tools for the same task? Or can CDC used for other things?

My current scenario is that I am asked to build a reliable data framework which is supposed to be the basis for multiple future applications. The exact requirements are blurry, but one is that it should be able to track data history and restore older entries together with all related data from other tables. I am evaluating CDC right now as an option, but am uncertain if this is the way to go, because I can't really find any recommended use cases.

While I appreciate advice for my specific scenario, answers should give general advice about when or when not to use Change Data Capture.

Firstly,

  Change data capture is available only on the Enterprise, Developer,
  and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions.  (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage.    Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.


Optimize complicated sql query by either changing query or indexing I'm not great with SQL and I started working at a place and they have a query that is running slow.  This SQL query is taking about 4 seconds to load and is coming from a table with about 160,000 rows.  It's a MyISAM table and we're using MySQL.  Here is the query:

$driver = 28;          //for some reason they're using a magic number here
$date = $theDate;      //the date is current date in datetime format

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
AND truck_id in
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%'
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' ))
and time like '$date%' ORDER BY customer, truck_id ASC;

Here is the jobs table structure:

id              int(11)
driver_id       int(11)
truck_id        int(11)
job_id          int(11)
mileage_start   int(11)
mileage_end     int(11)
mileage_total   int(11)
time            datetime
customer        int(11)
address         text
city            text
state           varchar(255)
zip             varchar(255)
phone           varchar(255)
...

I'm trying to get the query time to be much less than 4 seconds.  I just started at this place and changing the whole table structure might break a bunch of other stuff I'm not aware of yet.  Every column is indexed as BTREE (which I guess is the default?) so I'm wondering if what I need to do is either redo the query or index the used columns differently?  I'm not experienced enough with SQL to really know what to do.  Thank you for the input.

I dont have the reputation to add comments so I will take a stab at an answer. My experience is with MSSQL but I think it is transferable here.

It seems like this query is doing a bunch of redundant checks.

Do you get different results from this:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
AND truck_id in
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%'
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' ))
and time like '$date%' ORDER BY customer, truck_id ASC;

than you do from this?:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
and time like '$date%' ORDER BY customer, truck_id ASC;

The check for customer and truck in the table with the same conditions as the main query should not be necessary.


Python version issue on Cassandra 3.11.2 Searching around I found a lot of thread about this problem (coming from 2012) but I can't find an official solution: running cqlsh.bat

  File "C:\apache-cassandra-3.11.2\bin\\cqlsh.py", line 146
    except ImportError, e:

The error suggests the script is for python 2.
In system environment I have python3, there is a Cassandra version compatible or there is an official fix for this?

I had the same problem as you.
I had installed python 2.7 version as mentioned at Prerequisites and the error no longer appears


How deterministic is execution plan creation in SQL Server? Given the following constants:

The same database with the same structure (tables, indexes, etc)
The same data
The same SQL Server and hardware configuration
The same statistics
The same SET options in the client
The same SQL Server versions
The same trace flags

Given these constants, will SQL Server always produce the same plan for a given query?

If not, are there other considerations? Is there also an element of nondeterminism to consider as well?

Given these constants, will SQL Server always produce the same plan for a given query? If not, are there other considerations? Is there also an element of nondeterminism to consider as well?

Query compilation is deterministic as far as I am aware. One of the original QO design goals was that it should be possible to reproduce execution plans on a different system using a statistics-only copy of the database. There are a couple of subtleties to that, around configuration parameters like the amount of memory available, and the number of logical processors, but these are covered by your list of things to synchronize.

Caveat: That is true provided the word 'same' in your list is taken to mean exactly the same in all respects. For example, the 'same' statistics might exist on the two systems, but they are only exactly the same if the histogram steps and density information are identical.

That said, the optimization process is also extremely complex, meaning it can be difficult to ensure that all inputs to this deterministic process are identical, and that all internal states are similar enough to ensure the same code path is taken through the optimizer for a particular compilation. If the query contains access outside the database (to another database or instance), those environments must be identical too.

One thing I would add to your list is to check that any plan guides also exist in the second database.

The use of non-deterministic functions like GETDATE() in queries might mean you get a different plan too. While the main optimizer does not use the value directly, cardinality estimation can (see Constant Folding and Expression Evaluation During Cardinality Estimation). I am unsure if this class of difference falls within the scope of the question though, because both systems would produce the same plan if executed at the same time (or, more generally, with the same input variables, parameters, and function values).


PostgreSQL WAL recovery not working due to missing WAL I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable Barman.

I ran the following command:

$ barman recover --target-time "2015-09-03 10:00:00.000" ke 20150501T200001 /tmp/ke-recovery

Leaving us with /tmp/ke-recovery, a folder we can start once we change the path names in postgresql.conf.

It then starts with the recovery of the WAL files from barman_xlog/ but then fails in about half an hour due to one of the WAL files are missing.

We assume this is because of an rsync "gone bad" from the master to the Barman box, and it was not noticed.  It means we have almost all WAL files, but at least one is missing.

How can we "force" PostgreSQL to continue with the recovery (even at the potential loss of some data)?  Perhaps by using pg_resetxlog ?

I've been kicking PostgreSQL backup around the last couple of weeks, because of a new job, and I find the implementation of PostgreSQL's backup quite hard to grasp.

What I have learned however, is that the a missing WAL file is the worst situation you can have. A missing WAL file will not allow you to continue your restore past the missing file, because the PostgreSQL database relies on the WAL file to be ACID.

You could try to retrieve some of the information from the non-restored WAL files, but you wouldn't have any guarantee, that the resulting database/instance is consistent.


MONGODB primary became secondary after servers restart I had this config before restart:

{
    "_id" : "rs0",
    "version" : 5,
    "members" : [
        {
            "_id" : 0,
            "host" : "Ubuntu12041vanilla:27017"
        },
        {
            "_id" : 1,
            "host" : "slave-ubumaster-9mnltsm2.srv.ravcloud.com:27017"
        },
        {
            "_id" : 2,
            "host" : "slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017"
        }
    ]
}

first node was primary...
After the restart of all servers, all of them became secondaries.
So I am trying to push the first one back to be primary, but it is not working.

cfg = rs.conf()
cfg.members[0].priority = 0.5
cfg.members[1].priority = 2
cfg.members[2].priority = 2
rs.reconfig(cfg, {force:true})// the force is because I am secondary

So I am getting this error:

{
    "assertion" : "need most members up to reconfigure, not ok : slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017",
    "assertionCode" : 13144,
    "errmsg" : "db assertion failure",
    "ok" : 0
}

And I understand why: this host (slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017) is not functioning anymore because of a network problem.
So I tried to change the hosts: cfg.members[1].host = "slave:27017"
but I get this error:

{
    "assertion" : "assertion db/repl/rs_initiate.cpp:119",
    "errmsg" : "db assertion failure",
    "ok" : 0
}

So I am stuck in a loop where I cant bring up the primary and I cant change the hosts to fix the network problems.

Do you see any chance to get out of that loop before giving up the replica set?

rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2014-02-14T16:34:00Z"),
    "myState" : 2,
    "members" : [
        {
            "_id" : 0,
            "name" : "Ubuntu12041vanilla:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "optime" : {
                "t" : 1392323075000,
                "i" : 1
            },
            "optimeDate" : ISODate("2014-02-13T20:24:35Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "slave-ubumaster-9mnltsm2.srv.ravcloud.com:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 0,
            "errmsg" : "socket exception"
        },
        {
            "_id" : 2,
            "name" : "slave2-ubumaster-ndww0zab.srv.ravcloud.com:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 0,
            "errmsg" : "socket exception"
        }
    ],
    "ok" : 1
}

Thanks

You need to change the config such that there is only one member remaining, and you have to do it on that host, i.e. Ubuntu12041vanilla:27017

The others are unreachable from its perspective, so even if you go with 2 nodes, 1 out of 2 down means you cannot form a majority and you will still only have secondary state.

Given your current rs.status() output, try this when connected via the mongo shell to Ubuntu12041vanilla:27017:

cfg = rs.conf()
cfg.members = [cfg.members[0]]
rs.reconfig(cfg, {force : true})

That will give you a config only one node and should mean that Ubuntu12041vanilla:27017 will be the only remaining member and primary.


What is a mysqld process in a MySQL cluster? As I know, a mysqld process is a kind of a router of a MySQl cluster. It directs requests to the right ndbd node.

Assuming I have 3 ndbd nodes on 3 different machines and I don't use replication. How many mysqld proccesses do I need? I would say one? For me its like a master-slave architecture (comparable with mongodb) But I saw configurations where each ndbd node also has one mysqld proccess.
Why?

When it comes to a DB Server running mysqld in a MySQL Cluster environment, the DB Server's role is known as a SQL Node.
I once described the difference between a Storage Node and a SQL Node : Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?. In the comment section of the question, Mat Keep commented that you should look at MySQL Cluster -- Storing table data on data nodes.
In a small Cluster, I do not see any significant impact of running mysqld on a Storage Node. In a larger Cluster, I would expect SQL Nodes to be setup apart from the Data Node unless you intend to store data in physical files.
You can find additional info from the three MySQL Cluster gurus in the DBA StackExchange

Frazer Clement
Mat Keep
Andrew Morgan

Based on your comment

To be more precise, I have 3 machines which run a Data Node. Each machine has 8Gb of RAM. In total the the db should store ~50 million ~1kb records. How would you do it here?

DISCLAIMER : Not SCMCDBA Certified
Personally, I would get a fourth server and run it as the SQL Node and leave the others as Storage (Data) Nodes. Make sure the SQL node and the Data nodes have the same hardware and OS settings.
If it is a low-write DB Cluster, then you could chance it and run mysqld --ndbcluster on each Data Node (no need for a fourth server).


MySQL - LEFT JOIN with ORDER BY is slow I have three tables in MySQL - gallery, gallery_likes and gallery_info.

I want to obtain photos from gallery based on the folowing query:

SELECT g.photo_id, g.name
FROM gallery g
LEFT JOIN gallery_likes AS gl ON gl.info_id = g.info_id
LEFT JOIN gallery_info AS gi ON gi.id = g.info_id
WHERE gl.user_id = 456 AND gl.photo_id < 113535
ORDER BY gi.insert_date DESC
LIMIT 10

However, ORDER BY in this setup is very slow. Query takes 5s. If I use ORDER BY g.info_id instead, it takes 200ms.

I have index on id, info_id, photo_id and insert_date columns

You need the next indexes:

gallery:       info_id
gallery_likes: (info_id, user_id, photo_id)
gallery_info:  (id, insert_date)


SQL Server Snapshot Isolation Level: How to create an Update Conflict? Does anyone have an example of how to create an "Update Conflict" using Transaction Level Snapshot  (Not ReadCommmittedSnapshotIsolation)?

Error message example:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Food’ directly or indirectly in database 'FoodDatabase’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

The following is not working:

create database FoodDatabase;
alter database FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

create table dbo.food
(
    FoodId int primary key identity(1,1),
    FoodDesc varchar(255) null,
)

insert into dbo.food
values ('broccoli'), ('strawberry')

Transaction Session 1:
set transaction isolation level snapshot
begin transaction -- do not commit transaction yet
update Food
set FoodDesc = 'Grape' where FoodId = 1

Transaction Session 2:
set transaction isolation level snapshot
begin transaction -- do not commit transaction yet
update food
update Food
set FoodDesc = 'Lettuce' where FoodId = 1

I am using SQL Server version:

Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)

Run your scripts in this order and you will be able to simulate a Update Conflict.

Connection 1

CREATE DATABASE FoodDatabase;
GO
ALTER DATABASE FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE [FoodDatabase];
GO

CREATE TABLE dbo.food
(
    FoodId int PRIMARY KEY IDENTITY(1,1),
    FoodDesc varchar(255) null,
);
GO

INSERT INTO dbo.food
VALUES ('broccoli'), ('strawberry');
GO

--Transaction Session 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Grape' WHERE FoodId = 1;

Output:

  (2 rows affected)

  (1 row affected)

Connection 2

--Transaction Session 2:
USE [FoodDatabase];
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Lettuce' WHERE FoodId = 1;

Session from connection 2 is blocked by connection 1. You can see details about the lock by using sp_whoIsActive written by Adam Machanic.

<lock_type>keylock</lock_type>
    <database_name>FoodDatabase</database_name>
    <hobt_id>72057594043105280</hobt_id>
    <schema_name>dbo</schema_name>
    <object_name>food</object_name>

Go back to Connection 1 window and run a COMMIT statement. You will see following error message in Connection 2 messages tab.

  Msg 3960, Level 16, State 3, Line 6 Snapshot isolation transaction
  aborted due to update conflict. You cannot use snapshot isolation to
  access table 'dbo.food' directly or indirectly in database
  'FoodDatabase' to update, delete, or insert the row that has been
  modified or deleted by another transaction. Retry the transaction or
  change the isolation level for the update/delete statement.

Helpful resource:

Understanding the Available Transaction Isolation Levels


Setting up SQL server multiple Names but within the same Database Can we create different SQL Name (so will not change the previous connection strings) in a single SQL Server with the same content of the Database and log-in credentials?

Yes you can use a CNAME record in DNS
there are a few gotchas though so worth reading through the article
https://www.mssqltips.com/sqlservertip/2663/using-friendly-names-for-sql-servers-via-dns/
if you only need to "rename " the server from a single client there are other alternatives


Configuring SSIS,SSRS, and SQL Server - three server model I have a requirement for a third party vendor application which requires SSIS and SSRS. The vendor had suggested this 3 server model, and my management is going ahead with the same. Three VMs are being provisioned, one each for SQL Server, SSIS, and SSRS.

I want to know in what ways I can configure SSIS and SSRS to work with the SQL Server. Also, what would be the best way to create SSIS packages? Because, if the packages have to be created on the dedicated instance, I still need to create a local instance of the database engine on the SSIS server.

I need some guidance as to how I can configure SSIS and SSRS in conjunction with the SQL Server instance in the third VM. I am fairly new to these two areas. I am going through these two links:

Configuring SSIS
Configuring SSRS

Please let me know if another URL would provide detailed step by step information.

Also, would the three servers have to be in the same cluster?

The system is spread across continents hence we expect a lot of users in the long term. The way it was planned was to have separate instances of SSIS and SSRS, and they point to a database in an Always On setup which stores the reporting information.

What you are asking about is broad architecture rather than specific issues. I dont believe there will be a single document that gives you a step by step guide.

If your vendor is making this reccomendation you could ask them to explain thier design with at least some basic documentation and diagrams that help you understand the layout.

If you are unfamiliar with either of these products you have a big learning curve ahead of you. MSDN is probably the best place to start. If you have specific issues then you should google those.

Assuming your main database is already running, i would start by installing an instance for SSRS. Then work through designing and deploying a report that connects to your source db.

Then repeat for SSIS. Set up the server and design and deploy a basic package.

A key part of learning about both SSRS & SSIS is learning about datasources,  connection managers and connection strings.

If you are unfamiliar with these concepts and your company is expecting you to install configure and develop with these products they might be expecting too much of you and I suggest that you ask for assistance.


RDS Postgres - pg_stat_statements is missing queryid column I upgraded my RDS postgres instances from 9.3 -> 9.4 -> 9.5 several months ago.  When installing the pganalyzer-collector tool, I got a funny error that the "queryid" column does not exist in the pg_stat_statements table. It looks like that column was added in version 9.4. I'm now running 9.5 and expect to see this column in that table.

How could this have happened?  And how can I make this column appear in the pg_stat_statements table?

Update:  Lukas from pganalyze helped me.  How nice!
The answer was as follows:

DROP FUNCTION pganalyze.get_stat_statements();
ALTER EXTENSION pg_stat_statements UPDATE;
CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
/* pganalyze-collector */ SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;


What kind of database should I use? I'm designing a new system and I'm wondering what kind of DB should I use for my needs.

I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 values:

The node's own value at that historic moment
The sum of all values of his children

Aside from that, each node could be defined as part of some categories - so I should be able to query the DB to get all the nodes of some given category (Example: Give me all nodes, under category CAT1, at a specific point in time). Each node could be part of several categories.

Further more, the historic data could have multiple versions at each data point - so if I have versions A,B,C - For each data point, I should be able to get the data for version A, B or C.

This of course can be achieved using a traditional SQL database (which I kind of created using PostgreSQL) - but that would require recursive queries to build each point in time (to compute values of all children). Also, though this data is relational - it feels that it can be defined better using non-relational tools. Just a feeling :)

Another thought was using NoSQL (MongoDB), where each node would contain its children - but then you have trouble getting to specific inner nodes without traversing all its parents and trouble using effectively the references of containing categories. I might be wrong about those points, though.

I would love to hear some pointers to what might be a good solution.

Thanks!

It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least.

For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL).

For historical data, the History Table pattern has proven to be effective. For table foo, make a table foo_history and copy-on-write from foo to foo_history. An example.

IBM's DB2 supports SQL:2011 Temporal, which lets you have foreign keys based on time, and is the only db that supports it that I know of.


Use of hash functions in group by? I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that sometimes a customer can have too many components and exceed the maximum output of listagg(). I was wondering if there could be a way to generate a hash from a group of ordered rows, in a way I could use to make the same validation. Sort of:

with my_tabe as
(
    select 'user1' as usrid, '1' as prodcode from dual union
    select 'user1' as usrid, '2' as prodcode from dual union
    select 'user1' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '2' as prodcode from dual union
    select 'user2' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '4' as prodcode from dual
)
select
    userid,
    ORA_HASH(prodcode) WITHIN GROUP (ORDER BY prodcode)
from
    my_table
group by userid

Is there any function that could be used to that end?


Database design - repeating values I have an application that deals with businesses and their physical locations. So most tables have a location_id field that logically partitions the data.

One table deals with marketing referrals and each location has a set of sources that they can define in the application. However, ALL locations have an "Unknown" source that is basically a catch-all when the source isn't known. The marketing referral id is the primary auto-incrementing key on the table, and the location_id is a foreign key to the locations table.

As its set up right now, there is an "Unknown" record for each location in the table, each with its corresponding location_id. This means there are a bunch of duplicative records (aside from the location_id). Is this the best possible design for this scenario? I don't really want to remove the foreign key constraint, but in a perfect world I'd see there being a record with an ID of 0 that would serve as the "unknown" record for all the various locations.

Am I overthinking it?

Having a "dummy" location with id=0 to represent the unknown location is possible.

My personal solution would however to just use NULL for that purpose, i.e. set the location_id to NULL to reflect unknown locations. That will be simpler since you will not need the dummy location "0". Also it should be more efficient, as the foreign key constraint will not be checked when you insert or update a row with an unknown location. And if you have an index on location_id, then that index will not contain any key for the rows with NULL location ids. This is more efficient than having a large set of duplicates for location id "0".

After all, that is what NULLs are for: to represent the absence of information (whether that information is unknown or just does not apply).

The flip side is that your application must be ready to handle nulls. Looking up all records for a given location id remains the same. Looking up records with an unknown location will need changing: from WHERE LOCATION_ID=0 to WHERE LOCATION_ID IS NULL.


500 databases or 1 database with 500 tables or just 1 table with all the records? I currently have an application that is used by a single end customer. For ease of discussion assume the application only needs a single database table for all records. I now need to support multi-tenancy and so in the future I will have about 500 customers using the same database server.

1) A possible solution is to have a separate database for each customer. As there will be around 500 customers that means having MySQL/SQL Server/Oracle/etc... host 500 separate databases. This sounds like overkill for a database server running on an average cloud based server.

2) Another solution is to stick with the single database I already have but to have 500 different tables, one for each end customer. Each customer only averages around 2000 records in their own table. This is easy to implement and I would guess makes it easy to migrate the customer to another server, just move the entire table data over.

3) Lastly I could stick to using a single table in the single database. Instead I add an additional column that identifies the customer the record belongs to. But then the table ends up with about 1,000,000 records which is the aggregate of all 500 customers that average 2,000 each.

I do not know enough about performance and scaling to know 1, 2, 3 is going to give the best performance. Any ideas?

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why:

!This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs!

Standards - each database is setup the same way with the same standard names and tables.
Flexibility - a new customer needs their language to be finnish and a different collation to be used, easy enough since it's in it's own database. Try dealing with that with a single table and everyone's information in it. Making a change for a request or feature but only for a single customer, you'll start to like a database for each customer.
Security - depending on needs, a different login and user for each database to keep security tight.
Automation - since each database is an exact copy (or base tables are exact copies at least), this makes it extremely easy to automate tasks.
Upgrades/Changes - changes and updates can be rolled out for each customer on their specific schedules and needs. If a single upgrade or schema change goes wrong, only a single customer is down and not all customers.
Granularity - ever need to restore older data that may have been accidentally deleted? Yep, much easier to do this when segregated by database rather tan all jumbled together in the same table or restoring a huge database for a few records in a different schema.
Locking and Blocking - if all of the records are in the same table, some type of optimistic locking will need to be used if you don't want too many performance problems of all of the customers attempting to read/write their data at the same time. This is going to lead to more tempdb utilization and make a process much heavier in terms of resources than it should be.

Some of the downsides:

Admin Overhead - if automation is not used and scripts created for re-use... it's going to make completing tasks manually a large effort. If automated or scripted properly, there should be little administrative overhead.
Backups - now there are more databases and your restore strategy has to take that into account. The backup routine that is being used needs to automatically understand that new databases should be backed up.
Disk Space - sure, using individual databases could lead to lower space utilization efficiency. I find this to be a very small downside, almost non-existent but depending on implementation could represent an impact, especially if in the full recovery model.

The pros (IMHO) extremely out-weigh the cons. We don't know your specifics so some of these items might be moot, but there are many different things to think about and I hope I captured most of the big ones with what you'll be needing to use these for.


mysql: Is mysqladmin -p create equivalent to create database? I am new to mysql administration and I need to setup certain program. At some point, the installation guide states to execute this command:

mysqladmin -p create somedatabasename

Is this equivalent to create database somedatabasename ?

So far I have checked the documentation and I think both commands are equivalent but I don't want to screw my existing databases. Can someone confirm?

https://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html
https://dev.mysql.com/doc/refman/5.7/en/database-use.html

Yes and with the  -p argument it should prompt you for a password to authenticate to perform the correlated command operation.

  mysqladmin
  supports the following commands. Some of the commands take an argument
  following the command name.

  create db_name

  Create a new database named db_name.

  --password[=password], -p[password]

  The password to use when connecting to the server. If you use the
  short option form (-p), you cannot have a space between the option
  and the password. If you omit the password value following the
  --password or -p option on the command line,
  mysqladmin
  prompts for one.

  source


Return the id after insert or select I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this?
Also how can I return the id if the email was not inserted and it already exist in the DB? Do I need to perform another SELECT?

BEGIN;
  LOCK TABLE mailing_list IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO mailing_list (email)
  SELECT 'email'
   WHERE NOT EXISTS (
     SELECT * FROM mailing_list WHERE email='email'
   );
COMMIT;

I have tried adding the returning id but it doesn't work. I got:

  query has no destination for result data

Sqlfiddle

You need to store the value that you want to return somewhere:

CREATE OR REPLACE FUNCTION f_get(ikey text)
  returns integer
  AS
$func$
DECLARE
  l_id integer;
BEGIN
  LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO foo (type)
  SELECT ikey
   WHERE NOT EXISTS (
     SELECT * FROM foo WHERE type=ikey
   )
   returning id into l_id; --< store the returned ID in local variable
   return l_id; --< return this variable
END
$func$ LANGUAGE plpgsql;

You could have done that with the original function and an OUT parameter as well. In that case you just need to use returning id into out_key;


Does PostgreSQL optimize queries in transaction? In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, amount of records. And there is a question, will DB engine optimize it for me that rows won't be updated twice, or should I do it by myself?

There are a couple ways to do this without too much trouble but PostgreSQL will not leave rows intact when old = new.  So you will need to do it yourself.  The simplest way to do this would be to enter the new data set into a temp table, perhaps manipulate it in your transaction and then upsert the rows using a writable CTE (if on Pg 9.1 or higher).  Something like:

DELETE FROM temp_foo
 WHERE id IN (select foo.id from foo join temp_foo t on foo.id = t.id
               where foo = temp_foo);

WITH upsert as (
     UPDATE foo SET bar = t.bar,
                    baz = t.baz
       FROM temp_foo t WHERE foo.id = t.id
     RETURNING foo.*
 )
 INSERT INTO foo (id, bar, baz)
 SELECT id, bar, baz FROM temp_foo WHERE id NOT IN (select * from upsert);


How to create a column with rows in postgres? I want to create a table that withholds an array of values inside one column in table.

Example in JSON form:

 "trainNumber": 1,
 "departureDate": "2020-01-26",
 "operatorUICCode": 10,
 "operatorShortCode": "vr",
 "trainType": "IC",
 "trainCategory": "Long-distance",
 "commuterLineID": "",
 "runningCurrently": false,
 "cancelled": false,
 "version": 266667265768,
 "timetableType": "REGULAR",
 "timetableAcceptanceDate": "2019-11-08T10:34:22.000Z",
 "timeTableRows": [
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T04:57:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "scheduledTime": "2020-01-26T05:02:00.000Z",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T05:03:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "cancelled": false,
     "scheduledTime": "2020-01-26T05:05:00.000Z",
     "causes": []
   },

As show in example "timeTableRows".

How do I create this?

You could try adding a json array column (json[]), but it depends on your access patterns  what the best column type would be.

Example:

ALTER TABLE tableName
ADD COLUMN timeTableRows json[];

And then:

INSERT INTO tableName
(
    timeTableRows
)
VALUES
( array['{
   "type": "DEPARTURE",
   "scheduledTime": "2020-01-26T04:57:00.000Z",
   "causes": []
 }',
 '{
   "type": "ARRIVAL",
   "scheduledTime": "2020-01-26T05:02:00.000Z",
   "causes": []
 }']::json[])


What happens as a database grows to the HDD's lmit? I'm running a PostgreSQL database and from what I read, the database is unlimited in size. Is there any material I can read to understand what occurs when the HDD fills to capacity? What sort of monitoring must be done in order to insure continuous operation?

One solution would be to create an arbitrary limit of free space and once that free space has been reached, you would force the user to purge off older data. Is that a proper solution? I'm interested in learning what other solutions exist and how databases are properly maintained.

When the hdd fills the DB could no longer write and it will crash.
But that is the easy thing to solve & monitor- whenever you reach 85% capacity just add more or clear old data.

The harder/more important part is to ensure performance is not impacted by the growth in data (more rows to scan)
Usually this is handled by partitions and cleanup processes which somewhat mitigate the first issue as well.

Regards
Jony


convert_unixtime output wrong date Behind the scene: Basically I operate an app that heavily relies on time calculation. We store unixtime in the database according to the user's timezone. Now to perform specific operations we need to convert that time from the native timezone to UTC. We recently transferred the application from another server It was working perfect but when we moved the application to a new server. The calculation that was written in Server Side Langauge was accurate but those calculations in which we wrote the logic in stored procedure output wrong date while converting it from unix_time to timestamp. Now you might question how do you know that output is wrong? Because I ran the same query over 2 different servers and the one that I am talking about returned wrong output.

The result from server having bug.

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 11:10:04

The accurate result:

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 05:10:04

If you put the same value of unixtime in the epoch converter (https://www.epochconverter.com/) you will get the value above mentioned select timezone America/New York.

I have also checked that clock of the host machine is fine. Please tell me how to resolve this error.
I am using MariaDB version 10.3

Use TIMESTAMP, not DATETIME.  Then set each client to the timezone where it resides, or to UTC for the main server.

That way, the conversions will be done automatically.  That is, no conversion code is needed.


What is missing in my trigger? I have a table in which view names can be inserted and the following trigger should ensure that only vew_names can be inserted that really exist in the System (that's all the code the trigger consists of):

CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE]
AS
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       RAISERROR ('You can only insert views in that table that really exist!',10, 1)
      ROLLBACK
   END -- end if
END -- end trigger

However upon creation of the trigger I get the following errors:

  Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 1
  [Batch Start Line 0] Incorrect syntax near 'INSERT'.

  Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 8 [Batch Start Line
  0] Incorrect syntax near 'END'.

What is missing?
I have carefully read the grammar of T-SQL Triggers but can't figure out why my trigger is wrong.

Note: As far as I am aware, you cannot create a database trigger as you are currently attempting to do...

That being said, your 2 error messages, "Incorrect syntax near 'INSERT'" and "Incorrect syntax near 'END'", are due to you using square brackets [] and missing a starting BEGIN respectively.

Your Code (with comments):

--Remove these Square Brackets...
CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE]
AS
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       -- You don't END an IF, you END a BEGIN
       RAISERROR ('You can only insert views in that table that really exist!',10, 1)
      ROLLBACK
   END -- end if
END -- end trigger

Working Example:

CREATE TABLE dbo.MySpecialViews (
    ViewName varchar(10)
);
GO

-- Table or View, not on the Database
-- ...also no [] around INSERT and UPDATE
CREATE TRIGGER CheckIfViewExists ON dbo.MySpecialViews FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT ViewName FROM MySpecialViews EXCEPT SELECT name FROM sys.views)
    -- Added a BEGIN for the code after the IF
    BEGIN
        RAISERROR('you can only insert views in that table that really exist!', 10, 1)
        ROLLBACK
    END
END;
GO

DROP TABLE dbo.MySpecialViews;

Hope this helps.


SELECT tt0.number
     , tt1.`stuff 1`
     , tt1.`stuff 2`
     , tt2.`data 2`
     , tt2.`data 5`
     , tt3.`info 1`
     , tt3.`info 4`
FROM (   SELECT t1.number FROM t1
       UNION DISTINCT
         SELECT t2.number FROM t2
       UNION DISTINCT
         SELECT t3.number FROM t3
     ) tt0
LEFT JOIN t1 tt1 ON tt0.number = tt1.number
LEFT JOIN t2 tt2 ON tt0.number = tt2.number
LEFT JOIN t3 tt3 ON tt0.number = tt3.number

This query assumes the number field is primary or at least unique key in each table.

UPDATE:

  Unfortunately this gave me errors saying that there were duplicate entries for number

If You got error You describe that is NOT MySQL error.

http://www.sqlfiddle.com/#!9/22d82dc/1


MySQL Workbench Database Sizes I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using.

Does anyone know of an easy way to figure this out?

If nothing else, the default location mysql/workbench uses for saving the raw data on a windows machine?

Thanks in advance!
Quintis

I have some queries you can run against the INFORMATION_SCHEMA

Run this to get the Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Run this to get the Total MySQL Data and Index Usage By Database

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Run this to get the Total MySQL Data and Index Usage By Database and Storage Engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT

In each query, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results.

(SELECT 0 pw) will Display the Report in Bytes
(SELECT 1 pw) will Display the Report in KiloBytes
(SELECT 2 pw) will Display the Report in MegaBytes
(SELECT 3 pw) will Display the Report in GigaBytes
(SELECT 4 pw) will Display the Report in TeraBytes
(SELECT 5 pw) will Display the Report in PetaBytes (please contact me if you run this one)

Here is a report query with a little less formatting:

SELECT IFNULL(db,'Total') "Database",
datsum / power(1024,pw) "Data Size",
ndxsum / power(1024,pw) "Index Size",
totsum / power(1024,pw) "Total"
FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum
FROM (SELECT table_schema db,data_length dat,index_length ndx
FROM information_schema.tables WHERE engine IS NOT NULL
AND table_schema NOT IN ('information_schema','mysql')) AA
GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;

Give it a Try !!!

UPDATE

I just ran the first query as is on a client's DB

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 3 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MyISAM         |             0.673 GB |             0.079 GB |             0.752 GB |
| InnoDB         |             4.227 GB |             2.436 GB |             6.663 GB |
| Total          |             4.900 GB |             2.515 GB |             7.415 GB |
+----------------+----------------------+----------------------+----------------------+
3 rows in set (0.79 sec)

mysql>

It works as is.

UPDATE 2015-01-16 14:46 EST

Here are Queries to Report Sizes and Computes the Memory Units On-The-Fly

Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT ENGINE,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT engine,data_length,index_length
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY ENGINE WITH ROLLUP) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database

SELECT IFNULL(DB,'Total') "Database",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM (SELECT DB,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM
(SELECT table_schema DB,data_length,index_length FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL) AAA GROUP BY DB WITH ROLLUP) AAA) AA) A,
(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database/Storage Engine

SELECT IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT DB,ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT table_schema DB,ENGINE,
data_length,index_length FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY DB,ENGINE WITH ROLLUP) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;


Index on Column with only 3 status? I have a table with millions of rows. This table has a column which we frequently filter(means we use it in WHERE clause frequently), the values are Insert/Update/Ignored. Is it make sense to add index to columns? Someone told me adding index on few values column have no benefit

Adding index on low cardinality column can make sense for instance in case you need to search for value which is quite rare - say you have column State with possibities of New/Processed and 95% rows set as Processed, and you have to get those New often - then index will help you find them fast. But having it present means optimizer can try to use in bad cases.

Usually it is suggested to not index such column by itself, but use it as part of multicolumn index which can help specific queries (for instance - you want to process those new records from oldest to newest, then you add index on (State, CreatedTime) and query it like "where State = 'New' order by CreatedTime ASC" and this index will let you have those New fast and already ordered.


How to detect compatibility errors - SQL Server 2012 I am changing a database's compatibility mode from 90 (SQL Server 2005) to 110 (SQL 2012) and I am wondering at what point would I see errors from breaking changes if there are any.

I ran Upgrade Advisor 2012 which spotted several Stored Procedures that needed updating. An  example was:

"In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias."

However, when I run this stored procedure in SQL 2012 (without having made changes) it doesn't show any errors. Also, when I changed the database compatibility level from 90 to 110 there were also no errors. Nor when I restored the database.

If I am not seeing errors anywhere, I am hoping that Upgrade Advisor caught everything. Is there any other ways I should check for compatibility errors?

Also, how is it possible that this stored procedure ran successfully even though Upgrade Advisor told me it would fail? Thanks :)

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;

Technically, it should be as follows, since o.modify_date is not in the SELECT list:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;

In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:

They forgot to remove the rule as they said they would
They had other complications which prevented the above syntax from being blocked by the parser

That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:

CREATE TABLE #foo(id INT);

IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
  DROP TABLE #foo;
END
GO

CREATE TABLE #foo(id INT);

This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL

I go over a few other issues in this blog post. A couple of other breaking changes:

https://sqlblog.org/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn_virtualfilestats
https://sqlblog.org/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database_id-db_id

If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:

https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.

And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:

Profiler
Server-side trace
Extended Events


MySQL Compare Tables and generate delta I have two exact tables with different data. I need to be able to generate a Mysql View with the following:

All new rows that don't appear in previous_table but appears in current_table.
All rows in previous_table that has updated column values in current_table.

The tables don't have any primary keys. I cannot change the schema because it is out of my control.

Any help would be deeply appreciated.

1)

SELECT t1.*
FROM current_table t1
LEFT JOIN previous_table t2 ON t1.field1 = t2.field1
                           AND t1.field2 = t2.field2
                     --    another fields in tables structures
                           AND t1.fieldN = t2.fieldN
WHERE t2.fieldX IS NULL /* fieldX - any field defined as NOT NULL */

2)

SELECT t2.*,
       t1.fieldZ -- new value
FROM current_table t1
INNER JOIN previous_table t2 ON t1.field1 = t2.field1
                            AND t1.field2 = t2.field2
                      --    another static fields in tables structures
                            AND t1.fieldN = t2.fieldN
                      --    altered field
                            AND t1.fieldZ != t2.fieldZ
                      /* if more than one field may be changed, then
                            AND (     t1.fieldZa != t2.fieldZa
                                   OR t1.fieldZb != t2.fieldZb
                               --  OR ...
                                )
                      */


Create a custom log table in MySQL Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. Is this possible in MySQL?

I experimented with trying to create a trigger for a system table (mysql.slow_log). It simply cannot be done. However, there is a rather unconventional method you may want to try:

Step 01) Activate the slow_log with the output as a table and restart mysql

[mysqld]
log-output=TABLE
long-query_time=0.0001

Step 02) Convert slow_log to MyISAM, copy it to areva_log, and index slow_log on db

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
CREATE TABLE mysql.areva_log LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log ADD INDEX (db);
SET GLOBAL slow_query_log = @old_log_state;

Step 03) Create a Stored Procedure to Copy all Slow Log entries whose db is areva into mysql.areva_log

DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`AppendToArevaLog` $$
CREATE PROCEDURE `mysql`.`AppendToArevaLog` ()
BEGIN
    SET @old_log_state = @@global.slow_query_log;
    SET GLOBAL slow_query_log = 'OFF';
    INSERT INTO mysql.aerva_log
    SELECT * FROM mysql.slow_log WHERE db = 'areva';
    SET GLOBAL slow_query_log = @old_log_state;
END $$
DELIMITER ;

Step 04) Call the stored procedure every 15 minutes using a crontab or using a mysql event

Step 05) Zap the slow log every week, month, or year:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
CREATE TABLE mysql.slow_log_empty LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_to_zap;
ALTER TABLE mysql.slow_log_empty RENAME mysql.slow_log;
SET GLOBAL slow_query_log = @old_log_state;
DROP TABLE mysql.slow_log_to_zap;

Give it a Try !!!


generate a series of dates in progress-db I am looking for a way in progress-db to generate a series of dates in the same way I would in PostgreSQL.
    select date::date
    from generate_series(
                          '2021-01-07'::date
                         ,'2021-01-12'::date
                         ,'1 day'::interval
                         ) date;

Returns the following dataset in rows:

1/7/2021 , 1/8/2021, 1/9/2021, 1/10/2021, 1/11/2021, 1/12/2021

I looked in the documentation, but I am unable to find any generate_series keywords or anything like it.
Is this something that can be done in this RDBMS? If this is not possible, please let me know.

Disclaimer: I'm not familiar with progress-db.
generate_series() is a PostgreSQL, set-returning function. AFAIK, generate_series() is not natively available in any other DBMS. (Except maybe in DBMS that are derived from PostgreSQL: Redshift, Greenplum, etc.)
You can get the same effect from most DBMS, though. There are a lot of ways to do that; I don't know whether progress-db supports any of these.

Program a stored procedure.
Use a calendar table.
Use a common table expression (CTE). (Not supported)
And I'm sure there are other ways.

A calendar table seems like the "lowest common denominator". It works in all SQL DBMS. For whole days, 100 years takes less than 40k rows. A hundred years of whole minutes takes about 53 million rows. Your query, running against a calendar table of 100 years of minutes (53m rows, near worst case) boils down to this.
select distinct cal_minute::date as cal_date
from caltest
where cal_minute between '2021-01-07' and '2021-01-12'
order by cal_date;
----
cal_date
date
--
2021-01-07
2021-01-08
2021-01-09
2021-01-10
2021-01-11
2021-01-12

On my computer, which isn't anything special, it consistently runs in less than 10ms, and most of that time is sorting. If the order of rows doesn't matter--and it probably shouldn't--it runs in less than 3ms. (A wider range will take longer, of course.)
Against a 40k table of just dates, the equivalent of your query clocked in at about 0.1ms.


Best configuration of my.cnf for make fast Magento navigation I have a Magento 1.7 version site with 7000 configurable products.
Site navigation is slow.
From the moment I click on a site link to when the address in the url bar changes several seconds pass.
Configuring the file my.cnf I have had improvements. But I think the configuration is not optimal.
What do you suggest to edit in my my.cnf file considering that I have a VPS with the following features?

CPU: ten cores
Intel® Xeon® E5-2620v3, E5-2630v4 or 4114 processor
50 GB RAM (guaranteed)
1200 GB disk space (100% SSD)
100% SSD disk space
UNLIMITED traffic!
1 Gbit / s port
DDoS protection (details)

This is my.cnf configuration:

#query_cache_size = 50G
#table_open_cache = 2000
#innodb_adaptive_flushing = 0
#innodb_adaptive_hash_index = 0
#innodb_autoextend_increment = 1000
#innodb_buffer_pool_instances = 64
#innodb-defragment=1
open_files_limit = 2000
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_size = 5G
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 2
thread_concurrency = 30
table_open_cache = 2000
thread_cache_size = 32
#table_cache = 2000
query_cache_size = 30M
query_cache_limit = 1G
join_buffer_size = 3G
tmp_table_size = 2G
key_buffer = 70M
innodb_autoextend_increment=512
max_allowed_packet = 1G
max_heap_table_size = 1G
read_buffer_size = 50M
read_rnd_buffer_size = 50M
bulk_insert_buffer_size = 50M
myisam_sort_buffer_size = 50M
myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 5
myisam_repair_threads = 1
innodb_buffer_pool_instances = 5

innodb_buffer_pool_size = 5G --> change to 70% of available RAM (after excluding other apps)

tmp_table_size = 2G --> only 500M

But... Neither of these is likely to solve the problem you pose.

You "can't configure away a performance problem".  Instead, you must provide the slow queries and the schema (SHOW CREATE TABLE).  The solution may be as simple as adding a suitable composite index.


Percona Cluster Failed with Haproxy : Connection Limit Exceeded We have 3 node PXC cluster setup on amazon ec2. Its been working fine for around 4-5 months. Since last week, every node starting to fail due to MySQL connection limit exceeded error. we increased the max_connections  and still no help. We had about 1-2hr downtime since last week.

When a one node failed(Client connection failed) i was logged in to that MySQL server and i was able to do a SHOW FULL PROCESSLIST but it has only 20-30 connections. At the same time, when i tried to connect to the same server via another terminal, I got the connection limit exceeded error message. So i did a netstat -na | grep 3306 | wc -l and there were exactly 500 connections to 3306 port from loadbalancer ip with TIME_WAIT status.

max_connect_errors 1000000
max_connections 500

Setup as follows.

App1 ---> Haproxy1 ---> PXC Node1
App2 ---> Haproxy2 ---> PXC Node2
App3 ---> Haproxy3 ---> PXC Node3

Each haproxy configured with 1 active mysql node and 2 backup servers

server SQL1 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup
server SQL2 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3
server SQL3 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup

We also use clustercheck script from percona to monitor mysql nodes on port 50000 with help of xinex.d

Any suggestions?

OBSERVATION #1
You have max_connect_errors to 1000000 (One Million). If you ever reach that many consecutive connection failures, you would simply run FLUSH HOSTS; to clear up blocked connections. Given the high value of that setting, you will never need to run FLUSH HOSTS;
OBSERVATION #2
I am glad you mentioned your TIME_WAITs. I wrote this post in ServerFault back on Feb 01, 2012 : MySQL lowering wait_timeout value to lower number of open connections. This is a bit of a hack but it is a necessary evil in this instance.
In short, just run this on every PXC node
SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will  make TIME_WAITs timeout in 1 second. You have to run these lines every time a node reboots.

I got this idea from this post: How to forcibly close a socket in TIME_WAIT?
The accepted answer has a pictorial representation of when a TIME_WAIT comes into existence.
The answer with the idea that I liked is the one I am now suggesting.


WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong?

SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
AND MTR >= 0  OR MTR <= 4

Returns results that contain values inside and outside of the bounds of MTR being between 1 and 4. (from -3 to 75)

Alternately

SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
and MTR BETWEEN 0 AND 4

Is also returning results that are 0, -2, -3, and -4 - but nothing higher than 0 and less than 4 (except for the entries that are 0)

The correct syntax for this would be:

SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR
FROM MyTable
WHERE MyID = 2074163 AND (
    DATEDIFF(M, TheDate, TheOtherDate) >= 0 AND
    DATEDIFF(M, TheDate, TheOtherDate) <= 4
)


Do cross-joins get optimised by the client library? Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.

I came across Joins are for lazy people on SO, and it's made me question an assumption I'd made.

  Joining in the app server can be more
  efficient if joining on the database
  causes severe redundancy in the result
  set sent over the network. Consider
  tables A and B, where each row in A is
  associated with 20 rows in B, B has
  only 100 rows, and we want to fetch
  the first 1000 rows from A with
  associated rows from B. Joining in the
  database will result in 20 * 1000
  tuples sent across the network. If the
  join is done in the app server (first
  fetching the entire B table into
  memory), a mere 100 + 1000 rows are
  sent across the network.

I kinda assumed that a cross-join would be optimised in the client library, so that less data was sent over the network, and then merged, so that the app didn't need to glue the data together. Am I plain wrong, or does it vary depending on the database?

I'm reasonably confident that there is no optimization in the Oracle client libraries for repeating data.  If it is, it's below the SQL*Net trace layer which would surprise me.

There are definitely cases where it could make sense to do the join in the app server rather than in the database.  But it would be exceedingly rare that you'd have a table structure like this where you would also want to display the result of joining A to B to the user.  If you don't want to display the data to the user, you'd just make sure that the results were being processed in the database so that neither A nor B had to be sent over the network.

I have had one or two cases where I needed to get highly denormalized results to a client where it made sense to use the CURSOR function rather than doing a straight join solely in an effort to minimize the amount of data being sent over the network.  Of course, that requires that you're selecting a substantial amount of data from table A (DEPT in this case) so that the duplication of data actually becomes problematic.

SQL> ed
Wrote file afiedt.buf

  1  select dname,
  2         cursor( select ename, empno
  3                   from emp e
  4                  where e.deptno=d.deptno) emps
  5*   from dept d
SQL> /

DNAME          EMPS
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
CLARK            7782
KING             7839
MILLER           7934

RESEARCH       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
smith            7369
JONES            7566
SCOTT            7788
ADAMS            7876
FORD             7902

SALES          CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
ALLEN            7499
WARD             7521
MARTIN           7654
BLAKE            7698
TURNER           7844
SM0              7900

6 rows selected.

OPERATIONS     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


MySQL Database inside a VM on a high availablity cluster I'm looking to do an infrastructure overhaul.Currently I have a ton of small databases scattered amongst our assets and I wish to consolidate them.

Is consolidating all databases to 1 tuned database a good idea? Will I get greater performance if I only have a single database VM on a cluster?

The tuning options I've been looking into are:

innodb_buffer_pool_size=8000M
innodb_flush_method=O_DIRECT
innodb_file_per_table
log_slow_queries=/var/log/mysql.slow.log
thread_cache_size=4
query_cache_limit=1M
query_cache_size=128M
max_connections = 400
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
innodb_flush_log_at_trx_commit=2

Thank you for your time.

I would suggest separate instances for your critical production databases and consolidation to a single instance only for the small and less important MySQL databases.

In a consolidated MySQL environment, a single instance with n databases, any database could potentially impact on the others.

Additionally, it would be better to configure appropriately at least the critical databases and that will be easier when you're using separate instances.

Another important point is backup: different storage engines need different backup methods: if you use only transactional storage engines, you could avoid locking during the backup (xtrabackup, MEB, --single-transaction for mysqldump), non-transactional storage engines will require table lock during the backup. You could manage this by using flexible backup procedures, but if you need to generate a consistent backup for replication slave provisioning, for example, it would be more complicated if you have mixed engines across the databases.


How to get last 12 months values when some months have no records in the table I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data -

The requirement is to add the month columns for which there is no row with 0 as Total for last 12 months (i.e from 2018-11 to 2018-12). The expected output should be:

Kindly help me with the query formation?

Start with a table of numbers, which you then use with dateadd to make a table of months. Then you can left join it.

If you don’t have a table of numbers, you can use ROW_NUMBER on a large enough table.

SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable

You’ll also need your list of PartNumberKey and Depot_ID values.

DECLARE @month1 date = ‘20171201’, @month2 date = ‘20181101’; -- the semicolon is important!!
WITH months as (
    SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable
),
PartsDepots as (
    SELECT DISTINCT PartNumberKey, Depot_ID
    FROM dbo.MyTable
)
SELECT pd.PartNumberKey, pd.Depot_ID, m.TheMonth, COUNT(*) AS Total
FROM months m
CROSS JOIN PartsDepots pd
LEFT JOIN dbo.MyTable t
    ON t.TheDate >= m.TheMonth
    AND t.TheDate < DATEADD(month,1,m.TheMonth)
    AND t.PartNumberKey = pd.PartNumberKey
    AND t.Depot_ID = pd.Depot_ID
GROUP BY t.TheMonth, pd.PartNumberKey, pd.Depot_ID
;


Back Up - Free Drive Space Is there a way to know the free disk space available on the drive that the back up file will be created?

For example, SQL Server is installed in Server 1 and the back up file is scheduled to be created in network \server2.

Is there a way to know the free space of \server2?

If I execute xp_fixeddrives, it will only give drive space where the current instance is.

While sys.dm_os_volume_stats will only give drives where database_files are located.

One option would be to use XP_CMDSHELL and issue a simple DIR command and load the information into a table variable.  Then, delete the rows that don't have the word free and you should get the line that indicates the amount of free space.

DECLARE @DirectoryInfo TABLE (TextLine VARCHAR(max))

INSERT INTO @DirectoryInfo
EXEC xp_cmdshell 'dir <YourRemoteDirectory>'

DELETE
FROM @DirectoryInfo
WHERE isnull(TextLine, '') NOT LIKE '%free%'

SELECT *
FROM @DirectoryInfo

16 Dir(s)  6,247,769,628,672 bytes free


Calulating savings between two schemes I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same amount of data between the two schemes but it just seems too good to be true.

Am I calculating or going about this incorrectly?

First I'm using this query to determine size of the tables, so its possible this is the problem.

There is a total of 88324 rows. 1187 are considered root posts and have values for Title, ProductId, TotalViews and have a NULL for ParentPostId. 87137 are considered replies and have NULL for Title, ProductId, TotalViews and have a value for ParentPostId.

At any rate the query, linked to above, outputs the following results in Kilobytes which equates to a 45% improvement which seems to big of an improvement to me.

+---------+--------+
|Current  |Future  |
+---------+--------+
|94,936   |42,784  |
+---------+--------+

My table structure went from a current state of:

to future state of:

Turned out that the query I was using to calculate space combines indexes and data sizes into one. I used the sp_spaceused procedure as well as the below query to track down the the vast size discrepancy to two very large indexes on the current state table that I think were created by the tuning adviser. Those two indexes accounted for 60Mb!!, twice the size of the actual data, lol.

Without those huge indexes current state performs horribly while on the other hand future state doesn't require any additional indexes to perform as fast if not faster. Future state only added about 1.7Mb increase in storage needs which is far below the current states requirements.

query to find index sizes:

SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]
GO

result shows two huge indexes:

sp_spaceused 'Post_Old' result confirms that the those two indexes account for the discrepancy:


Restore Postgres Database on New Machine Trying to restore a postgres database on a new Macbook Pro.  I recreated the database using createdb dbname then tried to restore the old database using psql dbname < dbname_dump.bin but ended up with a bunch of errors like ERROR:  relation "comments" already exists and ERROR:  duplicate key value violates unique constraint "comments_pkey". What's up?

Try dumping the database, recreating, verifying it's empty, and re-importing? If it does it again then check the actual file you're importing and see if it has everything listed 2x.


Validating SQL Server Change Tracking Synchronization I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the latest changes and then checks to verify that the changes were synchronized correctly.
The way that it goes about this is:

It fetches the current change tracking version at the source together with some statistics about the source table that will later be used to verify that things synched correctly. The query looks like:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
WITH T AS (
    SELECT  [NumericValue] AS [NumericValue]
    FROM [SourceTable]
)
SELECT
    AVG(CONVERT([NumericValue])) AS Avg,
    SUM(CONVERT([NumericValue])) AS Sum,
    COUNT(*) AS Count,
    CHANGE_TRACKING_CURRENT_VERSION() AS CTVersion
FROM T;
COMMIT TRANSACTION;

It fetches all changes between the last version it synchronized to and the source version fetched in step 1. The query to fetch the changes in the desired version range looks like:

SELECT SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, [SYSKEY_PrimaryKey], [PrimaryKey], [NumericValue]
FROM (
    SELECT
        T.*,
        CT.SYS_CHANGE_OPERATION,
        CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END AS SYS_CHANGE_VERSION,
        [CT].[PrimaryKey] AS [SYSKEY_PrimaryKey]
    FROM CHANGETABLE(CHANGES [SourceTable], @startVersion) AS CT
        LEFT JOIN [SourceTable] AS T ON T.[PrimaryKey] = CT.[PrimaryKey]

    WHERE CT.SYS_CHANGE_OPERATION = 'D'
        OR (T.[PrimaryKey] = CT.[PrimaryKey])
        AND
            CASE
                WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN
                    CT.SYS_CHANGE_CREATION_VERSION
                ELSE CT.SYS_CHANGE_VERSION
            END <= @endVersion
) AS [SourceTable]

It gets the same statistics from the destination table that it did in step 1. The query looks like:

SELECT
    AVG(CONVERT([NumericValue])) AS Avg,
    SUM(CONVERT([NumericValue])) AS Sum,
    COUNT(*) AS Count
FROM [DestinationTable]

It compares the statistics from steps 1 and 2. If they match then all is good, if not then it knows there was a problem with the synchronization

The problem I'm having is that it is intermittently detecting that the values don't match on tables that experience very rapid updates. Investigating, I have found no evidence of anything actually going wrong in the synchronization process, but the destination seems to lag slightly behind where it needs to be to match the source. When the source table updates slow down, the destination always catches up and matches the source. It seems at this point that the problem stems from the version fetched in step 1 not actually matching the statistics that are fetched at the same time. This seems to indicate that the increment of the change tracking version is not performed atomically together with the actual changes to the table. Unfortunately I haven't found anything in the documentation that explicitly confirms or denies this theory, which makes it difficult to know whether there's something else I need to be looking for. Additionally, if it turns out to be true that the change tracking version and the table state aren't guaranteed to be consistent then I don't know how it would be possible for me to accurately perform validation after synchronization.
Any help clarifying whether it's expected for the version number to lag behind the actual state of the table, or suggestions of techniques I could use to get around this problem would be much appreciated.

If there was a delete after your initial statistics capture, your fetch all query is going to capture that.  If you apply that delete in your sync, it will technically be ahead of your capture point, which would product different values.
While this is possible to occur under a low volume of changes, it would be much more likely under a high volume.
I believe you want to evaluate the OR before AND, so should wrap those in ().  Also, I think you can just test against SYS_CHANGE_VERSION as I believe it will always have the same values a SYS_CHANGE_CREATION_VERSION for (I)nserts:
SELECT SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, [SYSKEY_PrimaryKey], [PrimaryKey], [NumericValue]
FROM (
    SELECT
        T.*,
        CT.SYS_CHANGE_OPERATION,
        CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END AS SYS_CHANGE_VERSION,
        [CT].[PrimaryKey] AS [SYSKEY_PrimaryKey]
    FROM CHANGETABLE(CHANGES [SourceTable], @startVersion) AS CT
        LEFT JOIN [SourceTable] AS T ON T.[PrimaryKey] = CT.[PrimaryKey]

    WHERE (CT.SYS_CHANGE_OPERATION = 'D' OR (T.[PrimaryKey] = CT.[PrimaryKey]))
        AND SYS_CHANGE_VERSION <= @endVersion
) AS [SourceTable]


How to call an Oracle Function with multi out Parameters though LINKSERVER I need help as I am facing this problem. when I pass 2 output parameter to a function through a linked server I am getting this error. For 1 out parameter its working fine but when I use two output parameters it gives this error:

  “Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

Here is the query:

DECLARE @RET INT

EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
, @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
, @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE
, @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
AT linkserver_name;

You can try same trick as mentonied at this answer on StackOverflow.

In your case SQL statement string for EXECUTE must look like this:

DECLARE
  vRetValue number;
  vErrorCode varchar2(100);
  vErrorDescription varchar2(4000);
  vRetCursor sys_refcursor;
BEGIN
  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription);
  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual;
  ? := vRetCursor;
END;

And call to this code like this:

DECLARE @RET_CURSOR CURSOR;

EXECUTE (
  'DECLARE '+
  '  vRetValue number;  '+
  '  vErrorCode varchar2(100); '+
  '  vErrorDescription varchar2(4000); '+
  '  vRetCursor sys_refcursor; '+
  'BEGIN '+
  '  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription); '+
  '  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual; '+
  '  ? := vRetCursor; '+
  'END;' ,
  @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH,
  @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT,
  @PASSPORT_NO, @PPT_EXP_DATE,
  @RET_CURSOR OUTPUT
) AT linkserver_name;

Sorry, I can't check the syntax, but hope that principle is clear enough.

Another thing is the code above is a workaround and must be used only if code with multiple output parameters won't work at all.
Most probable source of such behavior is a bugs or version incompatibility of MS OLE DB Provider for Oracle or Oracle client.


Database design for a table with both bulk insert and single row insert I have to design a table where records will be inserted in two ways.

Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records will also need an bulk update for the status. e.g. from 'Pending' to 'Completed'.
Single row insert from a LOB application, via stored procedure, say one thousand rows per day. Some of these records will not require the status update and some will.

Process 1 is already in place (The text file is used as an external table. insert/update via a stored procedure) but we are willing to re-design this as currently we have about 2 million rows but we are expecting major volume growth of say 30 million records per year from the batch plus say 3.5 million from the application. Data for 7 years needs to be stored. Data will be queried using a Id, type and date range (say current year) with pagination.

The preferred DB is Oracle 11G. But I am also open to SQL Server 2012 enterprise edition. In this case process 1 will perhaps be replaced by SSIS.

My questions are:

What are the considerations required for designing a table like this? Should the bulk and single row insert be in different tables with identical structure? I wouldn't like the bulk insert to affect the performance of single row insert and query when happening at the same time.
Is there any significant difference between using Oracle vs SQL Server for this scenario specially for performance?
Is SSIS appropriate to use for process 1 if SQL Server is chosen?

1a) If the two insert methods create the same data then making them one table seems to make the most sense. After a bulk load I would recomend running maintenance scripts to make sure that all indexing is up to date.

1b) The only way to know if the bulk insert and the single insert would effect each other in this specific case would be to test it. With sufficient hardware this would not be an issue.

2) Both SQL Server and Oracle are mature products that can handle what you are describing very well. It sounds like you have a preference for Oracle which should be able to handle what you are attempting.

3) SSIS would be a great solution for what you are describing concerning loading millions of rows into SQL Server.


How to recreate an Index with the same settings? I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.

I do not have the previous version in source control
I didn't find most of them in sys.indexes DMV (FILLFACTOR is there, but not ONLINE)
Finding the index in Object Explorer and scripting it out fills default values for the ONLINE setting (and omits DATA_COMPRESSION)
I didn't find the property in the OBJECTPROPERTY DMF
Brent Ozar's sp_BlitzIndex also doesn't provide some of these advanced settings
sp_helpindex provides me with the same limited information as sp_help over the parent table.
I cannot test if WITH (DROP_EXISTING = ON) recreates the index with the same settings, because I can't find the settings anywhere.

Any ideas?

You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu.

There you can define what you would like to script and what not. Compression is there.

As for the other properties - have a look at this StackOverflow question - it will probably shed some light:

SORT_IN_TEMPDB is not a property of an index, but rather an option that you can specify that says how the index is created. Once the index exists (i.e. the create index statement completes), whether or not SORT_IN_TEMPDB was specified at creation time isn't stored anywhere because it doesn't matter.