moving a Partition of Partitioned Table to other File Group, SQL Server I created partitioned table on DateTime column. for last 6 month.
I placed 3 oldest month on a file group named 'FG_OlderData', and 3 newest month on a file group named 'FG_NewerData'.
Partition Function Definition :

CREATE PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange (DateTime)
AS RANGE RIGHT FOR VALUES (
                        -- older_than_current_minus_5
        '20150121', -- current_minus_5
        '20150220', -- current_minus_4
        '20150321', -- current_minus_3
        '20150421', -- current_minus_2
        '20150522', -- current_minus_1
        '20150622',  -- current
        '20150723'  -- Future
)
GO

Partition Schema Definition :

CREATE PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
AS PARTITION pf_Positions_LastSixMonth_MonthlyRange
TO (
      FG_OlderData,
      FG_OlderData,
      FG_OlderData,
      FG_OlderData,
      FG_NewerData, -- minus 2 month (to be moved to OlderData)
      FG_NewerData, -- minus 1 month
      FG_NewerData, -- current month
      FG_NewerData  -- future month+
      );
GO

The Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    0   0   less than   2015-05-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    0   0   less than   2015-06-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    0   0   less than   NULL

First i switched  (20150522) partition's data to temp able to move to older file group :

ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION pf_Positions_LastSixMonth_MonthlyRange('20150522')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

Then Merge Switched partition :

ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('20150522');

Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    0   0   less than   2015-06-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   NULL

The Problem :
When i Split the '20150522' Range on OlderData, it remain in NewerData File group and next partion ('20150622') Goes to OlderData FileGroup.

ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_OlderData;

ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
          SPLIT RANGE ('2015-05-22');

Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   **FG_NewerData**    0   0   less than   **2015-05-22** 00:00:00.000
OnlineGeoPosition   6   **FG_OlderData**    0   0   less than   **2015-06-22** 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    0   0   less than   NULL

After this command, i suppose to have  '2015-05-22' partition on OlderData File group but it remains in FG_NewerData File Group and instead , the next partition '20150622' goes to FG_OlderData FileGroup.

What is the problem with my code? as you saw i used Range Right.

There are a couple of issues here.  First, the partition/filegroup mapping shown in your question does not match the DDL.  A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL.  Consider downloading the partition details custom report from codeplex that can run from an SSMS object explorer table context menu to show the boundaries and filegroups: http://ssmspartcustomrpt.codeplex.com/releases/view/30936 .

OnlineGeoPosition   1   FG_OlderData    less than                  2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-01-21 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000

When you merge a RANGE RIGHT function, the removed partition is the one on the right.  Data from the removed partition are moved to the retained one on the left.  So merging a RANGE RIGHT sliding window partition always moves from newer to older partitions.  However it is best to MERGE and SPLIT empty partitions as you are doing to avoid expensive data movement during partition DDL, which is about 4 times that of normal DML. Conversely, a SPLIT of a RANGE RIGHT function creates a new partition to the right of the specified boundary, and on the NEXT used filegroup, which is the main problem you are having.

Below is a sample script to achieve a sliding window with the new partition/filegroup mappings below.  This script uses 2 staging tables, one for the latest partition on the old data filegroup and another for the oldest partition on the new data filegroup (the one to be moved).  This allows the boundary to be safely merged with both adjacent partitions empty.  You'll need either check constraints on the staging tables or another partition function/scheme to constrain the staging table data to match the target partition boundaries for the switch in.  Data are moved efficiently using a CREATE INDEX...DROP_EXISTING.

OnlineGeoPosition   1   FG_OlderData    less than   2015-02-20 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-08-22 00:00:00.000

Sample script:

--switch out newest partition on FG_OldData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
TO dbo.OnlineGeoPosition_OlderTemp;

--switch out oldest partition on FG_NewData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

--move empty partition from new data to old data
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-04-21');

--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-04-21');

--rebuild each index with drop existing to move data from new to old filegroup
CREATE UNIQUE CLUSTERED INDEX PK_OnlineGeoPosition_NewerToOlderTemp
ON dbo.OnlineGeoPosition_NewerToOlderTemp(PartitioningColumn)
WITH(DROP_EXISTING=ON)
ON FG_OlderData;

--switch staging tables data back in
--staging tables must have a check constraint or be partitoned similarly as the target partition
ALTER TABLE dbo.OnlineGeoPosition_OlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
ALTER TABLE dbo.OnlineGeoPosition_NewerToOlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')

--remove oldest partition
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-01-21')
TO dbo.OnlineGeoPosition_OlderTemp;
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-01-21');

--create new partition on new data filegroup for future data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-08-22');
GO


Differences of MySQL locking for update with WHERE and IN So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+
| id | some_val                             | created_at              |
+----+--------------------------------------+-------------------------+
|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+

Trans1:

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2:

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.

If we use the performance_schema.data_locks table we get a better idea which locks are different.

Here we can see the supremum, or gap lock is the additional lock occurs with the RANGE query. While the is possible to avoid because because id 3 exists as a primary key, the MySQL developers haven't implemented this as it complicates locking and adds checks in a rather critical and time sensitive execution path.

Note, setting SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; will make locks equivalent as gap locks are only REPEATABLE READ.

Schema (MySQL v8.0)

CREATE TABLE example (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `some_val` VARCHAR(36),
  `created_at` datetime
);

INSERT INTO example
  (`id`, `some_val`, `created_at`)
VALUES
  ('1', '33aa186d-e2de-4c41-aa50-000000000001', '2019-03-17 15:15:21.130'),
  ('2', '33aa186d-e2de-4c41-aa50-000000000002', '2019-03-17 15:15:31.292'),
  ('3', '33aa186d-e2de-4c41-aa50-000000000003', '2019-03-17 15:16:01.396');

Query #1

BEGIN;

Query #2

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #3

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | ---------------------- |
| INNODB | 2070:1063      | 2070                  | 48        | 4        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |                        |
| INNODB | 2070:2:4:2     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 2070:2:4:1     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 2070:2:4:3     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 2                      |
| INNODB | 2070:2:4:4     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 3                      |

Query #4

ROLLBACK;

Query #5

BEGIN;

Query #6

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #7

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | --------- |
| INNODB | 2071:1063      | 2071                  | 48        | 9        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |           |
| INNODB | 2071:2:4:2     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1         |
| INNODB | 2071:2:4:3     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 2         |
| INNODB | 2071:2:4:4     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 3         |

Query #8

ROLLBACK;

View on DB Fiddle


Improve CTE performance is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER?

DECLARE @File_Name VARCHAR(8000),
        @Disk VARCHAR(5)
SET @File_Name = 'MARSQLUTILITY,AdventureWorksDW_Data'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP]
           ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)

--Need to divide by the number of operations in that timeframe to get average wait time per operation.
--SELECT MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
SELECT [IO1].[TIMESTAMP],
       [IO1].[NAME],
      ([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) AS Avg_Stall_Per_Operation
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
--Need to make sure not dividing by 0 when there has been no operations
AND (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk

CTEs don't allow you to add indexes, they behave similarly to views but they aren't a persistent object in the database so they can't have indexes (unlike views).

If the bottle neck is the JOIN operation you should resort to a temp table and add an index on the column you will be using to speed up the JOIN operation.


Speed of WAL send/receive when replicating 1/ Description:

I have a cluster include two machines

Master: postgresql 9.3 run on Centos
Slave : postgresql 9.3 run on Centos

Master ----> Slave : relpica from Master to Slave by asynchronous method (M send WAL, S receive WAL)

2/ Question:

How can I monitor (catch) speed of WAL (ex: 1MB/s) is sent from Master to Slave ?

You can use pg_stat_replication along with pg_xlog_location_diff. Checking the differences can tell you how many bytes of xlog have flown from the master to the slave.


How to safe delete (shred) a MySQL database? I want to store sensitive data in a MySQL database for a while, so I'd like to know a way to delete a MySQL database without having a chance of recovery. In Linux, we have a tool named shred which overwrites the content of the file repeatedly, so its content can never be recovered anymore. Problem is: I want to do it with a specific MySQL database. I know MySQL stores data in /var/lib/mysql, but there are several files there and it would destroy all databases and break MySQL. Any ideas?

InnoDB leaves user data in several places.

ibdata1 if innodb_file_per_table=OFF or respective *.ibd file if innodb_file_per_table=ON. This is where InnoDB stores the data.
ib_logfile0 and ib_logfile1 stores recent modifications.
Double write buffer (which resides in ibdata1 no matter what innodb_file_per_table is) stores recent modifications.

To reliably delete user data you need to shred ibdata1 - because of the double write buffer. If you do that, MySQL instance will be destroyed. So, I would say it's impossible to "shred" a particular database/table.

In your case I would create a replica with mysqldump (or mydumper, not with xtrabackup or a file system snapshot) and destroy the original instance/disk/server.


How to get table definition of a PostgreSQL table? I would like to get field's name, datatype of each field, length of each field and precision of each field of a PostgreSQL table. Please let me SQL query to get this information.

I suggest you use pg_dump to get tables' and views' definition, it's more convenient. The following command gets the definition of the database francs.

pg_dump -h 127.0.0.1 -E UTF8 -s -v francs > francs.ddl

-s means only dump matching  schema, not data.


How to measure the number of database writes on SQL Server 2008 R2 How would I measure the number of database writes on SQL Server 2008 R2

For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?

I would look at using extended events. Truthfully I have not worekd with these and it's one of those things I have not sat down and tried to learn...yet. So I cannot give you an exact example of how you would impliment with your example.

The advantage to these over trace files is no performance hit. They were specifically designed with performance in mind. So letting it collect information over a long period should not be a problem. You can check out using it for monitoring system activity here. Jonathan Keyhayias did a good month-long series on using extended events. The first day gives a good overview of them here.

He also created a SSMS add-in that makes it a little easier to work with the Extended Events sessions.


What is the purpose of the keyword "ID" in PostgreSQL? I'm new to PostgreSQL and I ran into problems using the following query:

SELECT * FROM table1 WHERE ID = 1

  ERROR:  column "id" does not exist
  LINE 2: WHERE ID = 1

where ID is a column I added via a GUI-tool.
I then found out that ID is a keyword in my current version of PSQL and should be quoted. But I can't recognize what is this ID for.

On the linked page there is the following notice:

  It is important to understand before studying Table C-1 that the fact that a key word is not reserved in PostgreSQL (==true) does not mean that the feature related to the word is not implemented.

So, ID is not marked as a PostgreSQL keyword and there seems to be a feature connected to it, but I can't find out which. SELECT ID itself turns into the same error message as posted above.

In PostgreSQL, inserting a column named

"ID"

is one thing. Inserting a column named

ID

is another.

create table test (
  "ID" integer not null
);

insert into test values (1);

select *
from test
where ID = 1;

ERROR: column "id" does not exist

  Quoting an identifier also makes it case-sensitive, whereas unquoted
  names are always folded to lower case.


Set default scheme in Redshift I'm trying to set the default schema to a user in Redshift with alter option, because I do not want it to write to the public schema by default. I am using the following order:

alter user user_1234 set search_path to atc_schema;

It does not give me any error, but when I create a table with the user without specifying the schema, it is not created in the schema indicated in the previous statement.

The problem comes from using Aginity, since this client applies that the default path is $user, public.

I've tried it in SQL Workbench/J and it works without problems.


Differences between Latin1_General_CI_AS and Latin_General_BIN collations? We want to write a test which proves that the collation of a column in the database is indeed Latin1_General_CI_AS. The database's default collation is Latin_General_BIN.

So we need to have some observable difference between those two databases, write a test using comparing/ordering and assert the correct outcome (which should be compared/ordered by Latin1_General_CI_AS).

Anybody know what kind of difference we could observe?

_BIN will order in the binary (ASCII order) of characters while CI_AS will use dictionary order with case insensitivity and accent sensitivity so BIN will treat "A" and "a" as different characters and order them accordingly and sort the alphabet in this order.

  ...XYZ[]^_`abc

That is the characters will be sorted by their ASCII code values

As a simple test

declare @Chartable table (character char (1))
insert into @Chartable (character)
Values ('a'),('A'),('z'),('Z'),('_'),('æ'),('Æ')
SELECT  character COLLATE Latin1_General_BIN
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT  character  COLLATE Latin1_General_CI_AS
   from @Chartable order by character COLLATE Latin1_General_CI_AS
SELECT distinct character COLLATE Latin1_General_BIN
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT distinct character  COLLATE Latin1_General_CI_AS
   from @Chartable order by character COLLATE Latin1_General_CI_AS


Setting up DRBD on an active MySQL server When it comes to setting up DRBD and MySQL, is the following possible?

Set up DRBD on an active MySQL server
Set up DRBD with no downtime allowed

Unfortunately, it is not possible to do either one.

Once you create /dev/drbd0 on both DB Servers, you have to mount the DRBD Primary on a folder. That folder cannot have anything with active file handles. The protocol I use is:

drbdadm primary drbd0
mount /dev/drbd0 /var/lib/mysql
service mysql start

You want DRBD to house MySQL Data. Here is what you must do instead

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
drbdadm primary drbd0
mount /dev/drbd0 /var/lib/mysql
cp -R /var/lib/mysql_old /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
service mysql start

As shown, stopping mysqld is mandatory.

UPDATE 2013-03-16 21:46 EDT

There is something incredibly tough you can try

Given ServerDB1 with MySQL Running
Given ServerDB2 to introduce as DRBD partner

STEP 01) Setup DRBD on Separate ServerDB2

As I mentioned earlier, you can house mysql on a DRBD mount.

STEP 02) Setup MySQL Replication

Huh ??? How did MySQL Replication enter the picture

You need to mysqldump everything in ServerDB1
Load the mysqldump into MySQL running in ServerDB2
Setup Replication so all changes are migrated since loading of ServerDB2 was done

STEP 03) Failover Your Application

In simple English, use the IP address of ServerDB2 in your application instead of ServerDB1

You should wait and make sure you have a low peak time to do this failover

STEP 04) Setup DRBD on Separate ServerDB1

Setup DRBD
drbdadm secondary drbd0
drbdadm coonect drbd0

This is an oversimplified description but you can look up the actual code to sync a DRBD pair

Here is my old DRBD download-and-install script

DRBD_VERSION=8.3
DRBD_SUBVERSION=0
DRBD_VERSION_NUMBER=${DRBD_VERSION}.${DRBD_SUBVERSION}
cd
rm -rf drbdinstall
mkdir drbdinstall
cd drbdinstall
wget http://oss.linbit.com/drbd/${DRBD_VERSION}/drbd-${DRBD_VERSION_NUMBER}.tar.gz
gzip -d drbd-${DRBD_VERSION_NUMBER}.tar.gz
tar -xvf drbd-${DRBD_VERSION_NUMBER}.tar
cd drbd-${DRBD_VERSION_NUMBER}
make rpm
rpm -Uvh dist/RPMS/x86_64/drbd-*

You can go to www.drbd.org to find out the latest version number and update the aforemtioned script

Give it a Try !!!


Error Code: 1215. Cannot add foreign key constraint I'm creating a DBSM on Mysql Workbench, but when Itry creating the last table i get an error because it can't add the foreign key, but i can't understand why since the child key has the same data type and collation as the parent key and the name isn't repeated.
That's the code for the two tables:
CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2),

PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L)
);

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP),
FOREIGN KEY (NUMERO_DIP) REFERENCES PRESTITO(N_DIP)
);

Is there someone able to help?
Thank you very much!

I suspect you have the direction of the foreign key relationship wrong. If dipartament.numero_dip is the primary key, then you should reference it from prestito, not the other way around:
CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP)
);

CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2),
PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L),
FOREIGN KEY(N_DIP) REFERENCES DIPARTIMENTO(NUMERO_DIP)
);


Can an application connect to databases from on-premise SQL Server Instance and Azure SQL together? Can a single application connect to two different databases each of which is in different Instances. One instance has on-premise deployment, the other one is Azure SQL(not SQL in Azure VM). So basically is it possible for an application to access data from both Instances?

Yes, you can use so many connections you need.

For instance, if you're programming for .Net framework simply instantiate two or more SqlConnection class and set up the ConnectionString property according the DB you are trying to access.

You can find some examples about ConnectionString's here.

And MS Docs about Azure ConnectionString's here.


Am fetching data from database i need to pass variable in my query.but am not getting how to pass variable in query import MySQLdb as mdb
con=mdb.connect(host="localhost",user="root", passwd="yogi@123", db="wisdomjo_db2011")
cur=con.cursor()
cur.execute("SELECT suggestion,related FROM `auto_related` WHERE suggestion!='' AND type='S' GROUP BY suggestion")
numrows = cur.rowcount
for x in xrange(0,numrows):
    row = cur.fetchone()
    cur.execute("SELECT suggestion,LOWER(REPLACE(suggestion,' ','')) as suggest,related FROM `auto_related` WHERE LOWER(REPLACE(suggestion,' ',''))=%s  AND type='S' ORDER BY Id LIMIT 0,6",(row[0]))
    data=cur.fetchall()
    print data

Do the JOIN in SQL, not with a client-side loop.  Cutting down on the number of round-trips to the server helps; cutting down on the number of queries to parse and execute helps, even though the resulting query is more complex.

But the real slowdown is not having the result of LOWER(REPLACE(suggestion,' ','')) as an indexed column in the table.


Increased server load after InnoDB deadlock I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, until about a week ago.

I've had a deadlock because two cronjobs were clashing with each other, I believe. Anyway, we've got that fixed by killing both queries and running the cronjobs after each other manually.

All seemed good, except for the fact that the server load is peeking a lot since then (about every 5 minutes) which I don't really know why. I've checked the running queries at those moments and they seem fine.

Do you have any tips where to look on how to fix this? Couldn't find too many information about this on the internet. Thanks in advance.

may be you need to clear or reload various internal caches
use these commands
flush tables ;
flush logs ;
you can find more details about flush command in this link
http://dev.mysql.com/doc/refman/5.0/en/flush.html


script task sssis file extension is missing? Hi I have problem with script task C# I create I have to prefix log file with date but my problem is not adding file extension
log file is log_yyyy_MM_dd without extension
I want ti extension
log file is log_yyyy_MM_dd.txt or CSV

    private string ReadVariable(string varName)
    {
        object rtnValue = null;
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForRead(varName, ref var);
            rtnValue = var[varName].Value.ToString();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
        return rtnValue.ToString();
    }

    private void WriteVariable(string varName, object value)
    {
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForWrite(varName, ref var);
            var[varName].Value = value;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
    }

    public void Main()
    {
        //Read Variables
        String sFlatFileConnection = ReadVariable("FlatFileConnection");

        //Get Run Date
        DateTime dtRunDate = DateTime.Now;

        sFlatFileConnection = sFlatFileConnection + dtRunDate.ToString("yyyy_MM_dd");
        WriteVariable("FlatFileConnection", sFlatFileConnection);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

Have you tried just adding the extension to the variable like this?

sFlatFileConnection = sFlatFileConnection + dtRunDate.ToString("yyyy_MM_dd") + '.txt';


Select one row from n number table; Join / Union table with looping I have a list table.

locations_list

id |  location | table
----------------------
1  | Location 1 | location_1
2  | Location 2 | location_2
3  | Location 3 | location_3

location_1

delivered_time       |  item_name
-----------------------------------------------
2017-06-08 16:37:57  | Bag 1
2017-06-08 19:37:57  | Bag 2

location_2

delivered_time       |  item_name
-----------------------------------------------
2017-06-08 15:37:57  | Bag 3
2017-06-08 20:37:57  | Bag 4

output expected

 id |  location | table       | delivered_time       |  item_name
 ------------------------------------------------------------------
 1  | Location 1 | location_1 | 2017-06-08 16:37:57  | Bag 2
 2  | Location 2 | location_2 | 2017-06-08 20:37:57  | Bag 4
 3  | Location 3 | location_3 |                      |

The join table names are in first table. I don't have option to put all delivery info into one table and do a where clause.

I tried UNION . But I don't know the table names. It is available in location table only.

How can I solve this?
dbfiddle link

Using the following test data, you may find the procedure underneath useful.  It will need some adjustments.  However, it may give you a starting point (MySQL 5.7).

Test data:

CREATE TABLE `loc_list` (
  `id` int(11) NOT NULL,
  `location_name` varchar(100) NOT NULL,
  `location_table_name` varchar(100) NOT NULL
);

CREATE TABLE `location_1` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

CREATE TABLE `location_2` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

INSERT INTO `location_1` (`delivered_time`, `item`) VALUES
('2017-06-08 14:07:57', 'Bag 1'), ('2017-06-08 14:08:57', 'Bag 2');

INSERT INTO `location_2` (`delivered_time`, `item`) VALUES
('2017-06-08 14:06:57', 'Bag 3'), ('2017-06-08 14:09:57', 'Bag 4');

INSERT INTO `loc_list` (`id`, `location_name`,`location_table_name`) VALUES
(1, 'Location 1', 'location_1'), (2, 'Location 2', 'location_2');

Table for storing the procedure's output:

create table output_(
  id_ integer
, location_ varchar(100)
, table_ varchar(100)
, delivered_time timestamp
, item_name varchar(100)
);

Draft version of the procedure:

--drop procedure findloc;

delimiter //

create procedure findloc()
begin
  declare t_name varchar(100) default '';
  DECLARE done TINYINT DEFAULT 0;
  declare cur_ cursor for select location_table_name from loc_list;
  declare continue handler for not found set done = 1 ;

  open cur_;
  read_loop: LOOP
    fetch from cur_ into t_name;
    if done then
      leave read_loop;
    end if;
    select @t := t_name ;
    set @qry = concat ('
      insert into output_
      (id_, location_, table_, delivered_time, item_name)
      select
        id, location_name, location_table_name,
        delivered_time, item
      from loc_list L, (select * from ', t_name, ') T
      where L.location_table_name = @t
    ') ;
    prepare stmt from @qry ;
    execute stmt ;
    deallocate prepare stmt ;
  end loop;
  close cur_;
end//

delimiter ;

-- clear the table - just in case
delete from output_;
-- execute the procedure
call findloc();

Output (just select from the output_ table):

mysql> select * from output_;
+------+------------+------------+---------------------+-----------+
| id_  | location_  | table_     | delivered_time      | item_name |
+------+------------+------------+---------------------+-----------+
|    1 | Location 1 | location_1 | 2017-06-08 14:07:57 | Bag 1     |
|    1 | Location 1 | location_1 | 2017-06-08 14:08:57 | Bag 2     |
|    2 | Location 2 | location_2 | 2017-06-08 14:06:57 | Bag 3     |
|    2 | Location 2 | location_2 | 2017-06-08 14:09:57 | Bag 4     |
+------+------------+------------+---------------------+-----------+

This may look a bit different from what you'd expect.  However, in your dbfiddle, you had (in the loc_list): location_table_name 'silo_1' and 'silo_2', whereas the timestamps and bags were INSERTed into the tables called 'location_1' and 'location_2', as @RDFozz has pointed out.


Can a weak entity have a primary key? Sorry for probably a very basic question, but in the literature and online, I have came across two different definitions of weak entities, which may sometimes be contradictory.

1) Weak entity is an entity which cannot exist without some other (owner) entity.

2) Weak entity does not have a primary key, but rather a partial key, and can be only uniquely identified by combining this partial key with a foreign key from owner entity.

Which one of these is true? Let us take the example of Customers->Orders relationship, where Orders have a unique OrderID. Here an Order cannot exist without a Customer, however it still has its own primary key. Would it be a strong or a weak entity then?

The primary key is a way to distinguish one row in a single table from all other rows in that same table. It is not a way to distinguish one row in the context of its associated rows from other tables.

Sometimes a table's primary key consists of a single column. A person's user_id would be an example.

Sometimes it is made up of several columns. A location is both latitude and longitude. This is known as a compound key. Sometimes one or more of those columns may also be a foreign key. This is termed a weak entity type.

To take your example - could a single row in the Orders table be distinguished from all other rows by the Order Number alone? Typically, yes. The order number is unique across the whole system. So given order number 8765 we know that's for customer A. This makes Order a strong entity type.

How about the OrderLine table? Given a single order line number, say "1", could we unambiguously find which Order that relates to? Typically no, because order line numbers start again for each Order. OrderLine is therefore a weak entity because its primary key (order number, order line number) requires the primary key from another related table, viz. Order.

So according to the business rules it makes no sense for an Order to exist without the Customer but according to the database rules this is OK. An OrderLine cannot exist without the Order under either set of rules.


TSQL - merge partitions online the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"

And the context:

I am working on quite common scenario - there is a production table that grows in linear way (a few hundreds rows every 5 minutes). But we do not have to keep historical data online, so we decided to use partitioning to "bite" the historical tail from the database and move it to another one.

There are many tutorials how to achieve this goal - create partition function, scheme, partition the table with it's indices, switch the partition to identical empty table and so on. And if I want to do this one more time, the "standard" way is to split the current partition and merge it's tail with the historical one.

Now what I want to end up with (example):
The table T is partitioned by some integer column P. Lets say there are at the moment three partitions and three filegroups. The partition function is set to split by values 30 and 60. The current value of the "counter" that produces values of the P column is 50. So there are some rows in partition 1 (HISTORY) which is now read-only, some rows in partition 2 (PRIMARY) that gets the periodical inserts and empty partition 3 (FUTURE).

Let the value of the counter be 70. Now the partition 1 and 2 are in the same situation. They are both read-only. The inserts are incoming with P value belonging to partition 3 only. And now I want to merge partitions 1 and 2 and move the result to another database. But I can see that during the merge of these two partitions all operations with the table T are being blocked. Even if it is INSERT to the third partition or there is selection predicate matching the partitioning function.  How can I set-up the scheme or something else to unblock SELECT/INSERT with P value from partition 3 only (that is out of the game with respect to the merge)?

I solved this situation using a simple workaround (very similar to DanGuzman's comment).

Merging partitions is in-blink-of-an-eye operation if and only if both partitions are empty. When the partition function is defined as RANGE RIGHT, then during the merge the leftmost partition will eat the other on the right side. Due to this all records have to be moved between partitions filegroups. The workaround is to rebuild or simply drop and recreate the staging table - the table with exactly the same schema as the source partitioned table. The only difference is the filegroup of the nonpartitioned table and it's indices must match filegroup of the partition that is going to be merged. This reduces both of operations - partition switching and partition merge to be metadata operation only. QED.


RMAN & Archivelog Deletion Policy confusion Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.

Tied to the same issue is the configure Archivelog deletion policy to backed up X times to disk.

Deleting input, or deleting input all, seems counterintuitive to me when the deletion policy is set to 7 times to match an overall backup policy of 7 days.

Is there anyone here who can explain to someone who may be just a little dense how this all plays together? And what should the Archivelog deletion policy be if the goal is to have one week’s worth of backups available at all times? All backups are weekly backed up to a tape drive. With seven days as overall, and 7 times on Archivelog deletions policy, am I backing it up 7 times or 7 times 7 times?

Thank you for any help anyone can offer……

Am a bit confused about what exactly is your question(s). Let me see if I can answer (at least) some part:

  Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.

I believe you are talking about DELETE INPUT part of the syntax of BACKUP command. This part tells oracle to delete the archived logs that exist at the time BACKUP command is run (as well as those generated during the execution of BACKUP command) ONLY AFTER BACKUP has successfully backed up the archived logs. Without this part, the archived logs are not deleted.

  Deleting input, or deleting input all, seems counterintuitive to me when the deletion policy is set to 7 times to match an overall backup policy of 7 days.

A deletion policy is used by Oracle to manage the space in flash recovery area. Oracle can delete archived logs in flash recovery area when the flash recovery area is full and there exist archived logs that satisfy deletion policy criteria i.e. if deletion policy criteria is set to 2 days and the flash recovery area gets full, say, on Thursday then oracle will automatically delete archived logs older than 2 days (i.e. before Wednesday) to free up the space.

  And what should the Archivelog deletion policy be if the goal is to have one week’s worth of backups available at all times?

That depends upon
a) How much data loss is the business/company is prepared to tolerate? &
b) What is the SLA (Service Level Agreement) time for recovery of database &
c) your backup strategy (hot/cold, full/incremental, image copy/backupset etc.)

Hope this helps.


How to get SQL Profiler record the user which login to your web application but the SQL username and password is specify in the web.config file? I have a web application that will retrieve data from a database. I have specify in the web application web.config file to have the username sa and password password.

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=sa;Password=password;

However, I notice that SQL Profiler will display the LoginName column as sa and not the actual user if a actual user log into the web application.

Example: Suppose a user WATSON\User1 come to the web application. The SQL Profiler will not record WATSON\User1 under the LoginName column. Then I will not know who have done something.

So, what should I do in order to have the SQL Profiler to record these user who login to the application from other domain?

As a good practice, do not use sa as the log on from you application to the database. Create another user in the database and use that user to interact with the database. That user should have restricted permissions to only be able to access the database objects it needs. I also hope that the password you use is not password. These steps will make your database a little bit more secure from attack/accidental/malicious activity issues.

Profiler is showing the connections to the database based on the user account connecting to the database - that is the user account in the connection string. You could create database accounts for each user, and alter the connection string to use that user account to connect with. This may be a workable solution if you do not have many users.  [It is also useful to do use separate users in conjunction with database permissions as a security/data integrity stratergy (ie user 'boss' has read/write privileges, user 'anonymous' has only read access to the data).]

If you can set up a cross-domain trust that would allow you to use Windows Authentication.  The web app would then need to be configured to impersonate the user that's using it otherwise the web service account will be displayed.

Another option if you are using stored procedures is to add the user ID as an argument to the call to the stored procedures which should show up in the trace.

If you have a data access layer, you could add a routine that logs the user, action and time stamp of each database action. The timestamps should correlate with the profile if you still need to profile information.


Merge performs always additional INSERTS I am facing the followign issue, my MERGE statements based on data (views) that do not deliver primary keys or any unique keys, always just ADD additional INSERTS on every execution to the target table.

Since I have used ALL COLUMNS in the comparison part, I was expecting that they are compared t the target and in case matched, NOT inserted but just left alone.

Instead the number of rows in the target keeps growing continuously with every execution.

Yes I know it might be better to deliver primary key. But I have not enough knowledge about the data delivered and in some cases it was not possible to find a primary key combination at all.

How can I prevent additional inserts in that case?

 MERGE INTO [Z].[IAPP] AS Target
    USING (
        SELECT
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP]
    ) AS Source ([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
    ON (Target.[SXD] = Source.[SXD] AND Target.[UUZQ] = Source.[UUZQ] AND Target.[SSRTD] = Source.[SSRTD] AND Target.[AAFFT] = Source.[AAFFT] AND Target.[IID] = Source.[IID])
    WHEN NOT MATCHED BY TARGET AND @Action = 'UI' THEN
        INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
        VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
    WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN
        DELETE;

I found the issue myself.

It was NULL values in the compared columns that could not be matched with one another. After adding ISNULL function around source and target to replace NULL in case it is there, it works as expected.

 MERGE INTO [Z].[IAPP] AS Target
    USING (
        SELECT
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP]
    ) AS Source

([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])

  ON (
     ISNULL(Target.[SXD],'nul') = Isnull(Source.[SXD],'nul') AND isnull(Target.[UUZQ],'nul') = isnull(Source.[UUZQ],'nul')  /* etc. */
    )
            WHEN NOT MATCHED BY

TARGET AND @Action = 'UI' THEN
            INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
            VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
        WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN
            DELETE;


Dynamically records not getting to cursor in trigger I am using cursor in trigger in MySQL. I want to get records of recently added id. I am using this:

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_property_ratings` AFTER INSERT ON `node` FOR EACH ROW BEGIN
DECLARE property_id integer;
DECLARE ratings integer;
DECLARE cur CURSOR FOR SELECT field_rating_rating, field_property_detail_target_id from field_data_field_rating as fr JOIN field_data_field_property_review as pr on pr.field_property_review_target_id = fr.entity_id JOIN field_data_field_property_detail as pd on pd.entity_id = pr.entity_id WHERE pr.field_property_review_target_id = NEW.nid;

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
call dolog(concat('node id-->', NEW.nid));
IF(NEW.type = 'review') THEN
    call dolog(concat('type-->', NEW.type));
    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO ratings, property_id;
            call dolog(concat('ratings -->', ratings));
            call dolog(concat('property_id -->', property_id));
            IF NOT(EXISTS(SELECT 1 FROM average_rating_property WHERE (pid = property_id))) THEN
              INSERT INTO average_rating_property (pid,total_ratings,total_count) VALUES (property_id, ratings, 1);
            ELSE
              UPDATE average_rating_property SET total_ratings = total_ratings + ratings AND total_count = total_count + 1  WHERE pid = property_id;
                call dolog('at update');
            END IF;
        END LOOP;
    CLOSE cur;
END IF;
END

If I provide static value instead of NEW.nid at line no.4 then I am getting records and  loop is working, else loop is not working. It means NEW.nid is not working in cursor. But it is working while logging at line no. 7.

Is there any mistake in my code?

Don't use a CURSOR in a Trigger.

Do use INSERT ... ON DUPLICATE KEY UPDATE ... instead of your select+insert/update.

IODKU can handle multiple rows at once, but I don't see why that would happen.  Perhaps Cursor's SELECT belongs there?

A typical pattern is:

INSERT INTO tbl (...)
    SELECT ...
    ON DUPLICATE KEY UPDATE
        a = VALUES(a), ...


Troubleshooting / traceroute within Oracle Instant Client? I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0.

Here's what I did:

Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same directory, then running odbc_install.exe.
Copied tnsnames.ora from the original Win2000 server (where the DB connection works just fine)
Updated Environment variables : PATH, TNS_ADMIN and rebooted Windows

When I run the end-user application, I get an error:

  ORA-12545 : Connect failed, because target host or object does not exist.

Looking more into this, many sources (e.g. http://www.orafaq.com/wiki/ORA-12545 ) recommend using the traceroute command. But I can't seem to find the \bin directory with that command anywhere (and obviously, command is not recognized), which leads me to believe that the instant client does not have this command? I do not have the installation package for the Administrator, so how can I do more troubleshooting under Instant Client?

At an OS-level e.g. from command-line, both ping and tracert work just fine with the DB server name). Oracle's traceroute should (supposedly, as per Oracle docs) give me the full error stack (instead of a single-line error) - hence it would give me additional hint why the DB connection on a recently reimaged server does not work.

As far as I can say, both Primary and Backup server are identical (I didn't work here 10 yrs ago though). Is there perhaps a package of admin tools I could download? Or just somehow unlock it within Instant Client? Are there any Oracle logs of the DB transactions / connection attempts?

From the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely rule out the networking (e.g. DNS/firewall/accessibility). Of course, before the server reimage, the DB connection worked just fine, so there is clearly something else missing in the first-time configuration.

I cannot seem to find any info on command line troubleshooting under instant client. Does one really need Administrator installation to do basic command line troubleshooting?

There is no trcroute for the instant client, but even if you had the full administrator client, trcroute may not return anything useful.

  By the way, from the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely
  rule out the networking (e.g. DNS/firewall/accessibility).

And that is the mistake people usually make, based on a false assumption. Just because you can ping, let's say, even tnsping the database server, does not mean at all you will be able to connect or name resolution works properly. Ping and tnsping checks work only up to the first entry point in a database connection, and they stop there, these tools alone are not enough for testing database connections.

Based on my experience, ORA-12545/TNS-12545 is a result of indirect name resolution problems. Even the official description suggests trying IP addresses instead of host names:

12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Whenever you connect through a listener, the listener may forward your request to another address that a participant does not understand => ORA-12545.

Make sure your client and server can resolve all addresses specified in the connection string, especially the addresses that appear in the output of lsnrctl services on the database server.

And below is an example.

My client:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan

My TNS entry:

[oracle@o62 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
MINDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mindb)
    )
  )

Run some checks:

[oracle@o62 ~]$ ping o6ca-scan.balazs.vm -c 3
PING o6ca-scan.balazs.vm (192.168.12.5) 56(84) bytes of data.
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=1 ttl=64 time=0.090 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=2 ttl=64 time=0.111 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=3 ttl=64 time=0.094 ms

--- o6ca-scan.balazs.vm ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1998ms
rtt min/avg/max/mdev = 0.090/0.098/0.111/0.012 ms

[oracle@o62 ~]$ tnsping mindb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:35:06

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mindb)))
OK (0 msec)

I can ping the host in my TNS entry, even tnsping works, so I will be able to connect, right? Well, no:

[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:36:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

So let's check what trcroute says:

[oracle@o62 ~]$ trcroute mindb

Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:36:59

Copyright (c) 1995, 2014, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

No errors, everything should be OK, still, I can't connect. So much for trcroute.

Now let's do some real troubleshooting, and check the server side. The address I am connecting to:

[oracle@o6ca1 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:40:13

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-APR-2016 18:15:18
Uptime                    0 days 0 hr. 24 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o6ca1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.5)(PORT=1521)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
  Instance "mindb2", status READY, has 1 handler(s) for this service...
The command completed successfully

And the services registered here:

[oracle@o6ca1 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:39:37

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca1-vip.balazs.vm)(PORT=1521))
  Instance "mindb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca2-vip.balazs.vm)(PORT=1521))

Notice in the above output, the mindb service has 2 instances/handlers, with different addresses. Whenever I connect to o6ca-scan.balazs.vm, my requests will be forwarded to o6ca1-vip.balazs.vm or o6ca2-vip.balazs.vm. My client can't understand those addresses, but if I fix it:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.3    o6ca1-vip.balazs.vm o6ca1-vip
192.168.12.4    o6ca2-vip.balazs.vm o6ca2-vip
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan
[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:45:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Apr 07 2016 18:45:07 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>

The above is a very common mistake when using Oracle RAC. But ORA-12545 is not specific to RAC, that can happen in single instance environments as well. All you need is a shared server configuration, and name resolution problems on the database server. Same story short:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "exdb" has 1 instance(s).   Instance "exdb", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:55 refused:0 current:15 max:1022 state:ready
         DISPATCHER <machine: o61.balazs.vm, pid: 592>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=42571))

I have a dispatcher listening on o61.balazs.vm. If I request a shared server connection, and the listener tries to forward the request to o61.balazs.vm, but can not resolve that address, that will result an ORA-12545 as well.


Finding by ID in postgresql very slow on big table I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is around 2 seconds.

Application is hosted on heroku and I'm using Crane database (https://addons.heroku.com/heroku-postgresql) with 410MB of RAM.

Is there any way to speed this up? I'm querying for videos 50 times per second on average, and new videos are inserted/updated at rate of 50 per second.

explain analyze SELECT * FROM videos WHERE id = 17841464 LIMIT 1;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.892..2337.894 rows=1 loops=1)
   ->  Index Scan using videos_pkey on videos  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.888..2337.888 rows=1 loops=1)
         Index Cond: (id = 17841464)
 Total runtime: 2337.943 ms

Here is how the table looks like:

 \d+ videos;
                                                           Table "public.videos"
     Column     |            Type             |                      Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('videos_id_seq'::regclass) | plain    |              |
 uuid           | character(11)               | not null                                            | extended |              |
 channel_id     | integer                     | not null                                            | plain    |              |
 category_id    | integer                     |                                                     | plain    |              |
 title          | character varying(255)      |                                                     | extended |              |
 published_at   | timestamp without time zone |                                                     | plain    |              |
 view_count     | bigint                      |                                                     | plain    |              |
 like_count     | integer                     |                                                     | plain    |              |
 dislike_count  | integer                     |                                                     | plain    |              |
 favorite_count | integer                     |                                                     | plain    |              |
 comment_count  | integer                     |                                                     | plain    |              |
 disabled       | boolean                     | default false                                       | plain    |              |
 created_at     | timestamp without time zone |                                                     | plain    |              |
 updated_at     | timestamp without time zone |                                                     | plain    |              |
Indexes:
    "videos_pkey" PRIMARY KEY, btree (id)
    "videos_uuid_idx" UNIQUE, btree (uuid)
    "videos_latest_by_channel_idx" btree (channel_id, published_at DESC)
    "videos_top_by_channel_idx" btree (channel_id, view_count DESC)
Has OIDs: no

Honestly I have never seen a single value btree lookup take this long.  I don't think your problem is simply in your query.  I think it is elsewhere.

First you say you have a lot of writes.  This probably means you are constantly pushing a lot of stuff out of the buffers and doing a lot of random disk I/O.  I would not be surprised if a lot of the bottleneck is in the general I/O and your lack of RAM.

For 18 million rows, these don't look very large.  They may all fit in memory.

At any rate the first thing to do if had access to the command line on the server would be to take CPU I/O wait vs user vs system time (you may be able to do that elsewhere in your code).  Also run explain with (ANALYSE, BUFFERS, VERBOSE) set so that you can see much more about what is going on.  These should give you an idea if your problem is underwhelming CPU activity, slow RAM, or lots of disk I/O but as it is, there is very little that can be done.

Another thing you can do is this:

VACUUM ANALYSE VERBOSE;

See if that speeds things up.


MySQL: After inserting data into a table, the table remains empty i'm trying to run this simple query:
insert into diff_set_values(value)
select value
from diff_sets
group by value;

but after it is finished, there is no data in the destination table diff_set_values. Unfortunately, there's so much data in the source table diff_sets, that the query runs a little more than an hour. Too long for MySQL Workbench to keep the connection to the MySQL server. It says "Error Code: 2013. Lost connection to MySQL server during query" after 30 seconds, but doesn't tell me if any other error occured.
information about the tables and server
The source table diff_sets contains about 186 million records with 4 columns (ID1 int, ID2 int, diffType int, value varchar(64)). Primary Key: ID1, ID2, diffType. The column value also has an index idx_diff_sets_value.
Example data:
ID1     ID2     diffType  value
25151   24978   15        13ddfb5fa96ffebffbdfd3ff7dd7cabd7
66675   66670   14        13ddfb5fbb5cdefe7bffffbd359775ffe
66701   66675   18        13ddfb5fbb5cdefe7bffffbd359775ffe
25789   25750   14        13ddfb5fbf7fdeff7fabff3edbfcefadf
25789   25778   17        13ddfb5fbf7fdeff7fabff3edbfcefadf

The destination table diff_set_values is empty and contains a column value varchar(64) and 6 other int columns that will be filled with null. Those columns will be updated later.
I'm running MySQL Community Server 8.0.16 on my Notebook. I'm accessing it with MySQL Workbench 8.0 and I have exclusive access to the database.
What I tried
I've created a stored procedure and inserted all error handlers I could think of.
CREATE DEFINER=`root`@`localhost` PROCEDURE `fillInterDiffSets`()
BEGIN
    declare continue handler for sqlexception
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    declare continue handler for sqlwarning
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    declare continue handler for not found
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    insert into diff_set_values(value)
    select value
    from diff_sets
    group by value;
END

When I run that procedure, I can watch the size of the diff_set_values.ibd file increasing. After a bit more than an hour the increasing stops at about 5.2 GiB. When I run show processlist, the process is still active for a few minutes.
When the process disappears from the show processlist, there are no records in the diff_set_values table and yet no error messages in the sp_errors table.
I tried, if the error handlers work by removing the group by part of the query, which immediately results in a "duplicate key" message in the sp errors table.
I tried to use select distinct value from diff_sets instead of select value from diff_sets group by value. Doesn't help.
I tried select sql_big_result distinct value from diff_sets. The only difference is that the file size of the ibd file doesn't increase and show processlist only tells me "creating sort index", but eventually disappears from the processlist.
I limited the query by appending limit 0, 1000000 to it. That works as expected.
A few months ago, the diff_sets table was smaller and the query worked properly.
I'm running out of ideas what to do. Is there a way to see all errors that occured on the server? Do you have any idea how to solve the problem?

This may run faster:
 INSERT INTO diff_set_values(value)
    select DISTINCT value
    from diff_sets;

Either would run faster if diff_set_values had INDEX(value).  However, adding the index would take a long time.
Increase the timeout.


Force SQL Server to run query conditions as written? I'm using SQL Server 2008 R2 and I have this pseudo query (SP) :

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

The problem is that the query takes a very long time to execute -- even if I execute the SP with @LinkMode=2.

As you noticed, the long-running query should be executed only if @LinkMode  is null, which is not the case here. In my case @LinkMode = 2 !

However, if I change it to  :

 select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

the SP does run fast.

I've heard before that sometimes the optimizer can optimize the order of criteria.

So I ask :

Even if the optimizer chooses a different route, what can be faster than checking if =null? I mean, I think that checking if a==null is much faster than running the other long query...
How can I force SQL Server to run the query as I've written it (the same order)?

You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.

To summarize the problem:  SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation.  The "matching" that occurs here is purely textual, so the actual value of a variable will not affect this.

That's good 99% of the time, but in some cases it's bad.  One case where it's bad is when someone tries to construct a WHERE clause as though its like a short-circuiting IF statement in C, etc.  This doesn't work well, because the SQL compiler has to make one query plan that will work regardless of what the parameter values actually are, and the only way that it can handle these "clever" logical switching-conditions in the WHERE clause is to make a simple brute-force plan that just scans the whole table, filtering the rows as it goes, without leveraging any indexes.

Not suprisingly, this makes them uniformly slow, no matter what the parameter/variable values are.


What is the correct model for related tables with millions of rows? I need to create a question and answer tables that will have millions (maybe billions) of rows.

The current model is:

Question Table
id_question (PK, auto increment)
id_user
question_content
question_date

Answer Table
id_answer (PK, auto increment)
id_question
id_user
answer_content
answer_date

Is this a correct model (considering better query performance)? Should I add the id_question and id_user columns to the primary key?

Thanks

You're right. Your tables would look something like this then ( oracle ddl ) :

create table Users (id int,
                    name varchar2(1000),
                    constraint pk_users primary key (id)
);

create table Questions (id int,
                        user_id int,
                        content CLOB,
                        question_date date,
                        constraint pk_questions primary key (id),
                        constraint fk_questions_user_id foreign key (user_id) references users(id)
                        );

create table Answers (id int,
                      question_id int,
                      user_id int,
                      content CLOB,
                      answer_date date,
                      constraint pk_answers primary key (id),
                      constraint fk_answers_questions foreign key (question_id) references Questions(id),
                      constraint fk_answers_user_id foreign key (user_id) references users(id)
);


Warning: #1292 Incorrect datetime error when using DATE_ADD() Doing this works fine:
SELECT * FROM table WHERE foo < '2021-01-09 00:00:00'
But this one:
SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00'
results in
Warning: #1292 Incorrect datetime value: 'foo'
What could possibly be causing this?
(MariaDB 10.4.17)

You try to add to the string 'foo' 5 days.
Use backticks for column names
SELECT * FROM table WHERE DATE_ADD(`foo`, INTERVAL 5 DAY) < '2021-01-09 00:00:00'


Ticket-like table and growing database I manage a MySQL database through phpMyAdmin. I have a table for cinema cupouns tickets. I upload a bunch of tickets, and then I assign tickets to users.

Each ticket contains,

id          -> int(11) unsigned PRI auto_increment // should it be bigger?
ticket_id   -> varchar(20) // should this be the primary key?
type        -> enum('3d', 'common', 'monster')
price       -> int(11)
user        -> varchar(16)
expire      -> date
updated     -> on update CURRENT_TIMESTAMP
created     -> timestamp

So, when tickets are created, user fields are empty, that's how I check how many are available. When a user needs tickets I select the top ones (with empty user value) limiting by the quantity and assign them to the user in the user field.

Sample data

ID  ticket_id               type    price   user        expire          updated                 created
1   V100070263526SA5K3W     3d      45      27060192    2015-07-30      2015-04-01 12:10:06     2015-03-31 08:39:19

I wonder if I'm doing fine, or how should I optimize the design in terms of a database table, considering a bunch of 400 new ticket uploads happen every month.

In general, this design will do the job without problems, specially that you only handle 400 rows per month, which is relatively small number.

However, I have few recommendations to improve the design of the table:

price is better to be decimal, unless you want to store the price in "cents". i.e. if the ticket price is $5.99. you either have to store it as 599 and let the application process it when doing calculation, or viewing it; or, you store it in decimal(5,2)
user field is better to be an integer, and the user ID in the users table is better to be an integer
Your primary key is good. Don't change it to ticket_id.
Maybe a used flag field would be more clear to know which tickets are already used (This is personal preference)
Depending on the queries you run against this table you may create the required indexes.


Why are my Postgresql sessions writing directly on disk? I have a write-heavy application on a PostgreSQL 9.4 on CentOS 6 that I'm trying to tune.

Using iotop, I see that my session processers are doing all the heavy writing on disk when (to my understanding) only the checkpointer and writer processes should be doing it.

I thought that the sessions might be writing temp files due to lack of workmem but when I query the pg_stat_database, the columns "temp_files" and "temp_bytes" are zero. I'm trying to log this kind of usage via system parameter "log_temp_files" but nothing appears on the log files.

Can somebody help me? Are there any kind of data that the sessions write directly on disk besides temp files?

EDIT: Added an iotop example

19156 be/4 postgres    0.00 B/s  382.33 K/s  0.00 %  0.00 % postgres: checkpointer process
19167 be/4 postgres    0.00 B/s  146.96 K/s  0.00 %  3.03 % postgres: example example 10.18.14.1(59536) idle
19190 be/4 postgres    0.00 B/s  146.16 K/s  0.00 %  2.89 % postgres: example example 10.18.14.1(59551) idle
19164 be/4 postgres    0.00 B/s  144.17 K/s  0.00 %  2.20 % postgres: example example 10.18.14.1(59533) idle
19172 be/4 postgres    0.00 B/s  128.64 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59541) idle
19161 be/4 postgres    0.00 B/s  122.27 K/s  0.00 %  1.88 % postgres: example example 10.18.14.1(59525) idle
19174 be/4 postgres    0.00 B/s  119.08 K/s  0.00 %  2.08 % postgres: example example 10.18.14.1(59543) idle
19170 be/4 postgres    0.00 B/s  116.29 K/s  0.00 %  2.62 % postgres: example example 10.18.14.1(59539) idle
19169 be/4 postgres    0.00 B/s  115.50 K/s  0.00 %  1.99 % postgres: example example 10.18.14.1(59538) idle
 1304 be/4 root        0.00 B/s  115.50 K/s  0.00 %  0.92 % [flush-253:0]
19173 be/4 postgres    0.00 B/s  113.90 K/s  0.00 %  1.97 % postgres: example example 10.18.14.1(59542) SELECT
19175 be/4 postgres    0.00 B/s  113.50 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59544) idle
19476 be/4 postgres    0.00 B/s  111.91 K/s  0.00 %  1.91 % postgres: example example 10.18.14.1(59618) idle
19434 be/4 postgres    0.00 B/s  110.32 K/s  0.00 %  1.77 % postgres: example example 10.18.14.1(59606) DISCARD ALL
19168 be/4 postgres    0.00 B/s  101.16 K/s  0.00 %  1.82 % postgres: example example 10.18.14.1(59537) SELECT
19166 be/4 postgres    0.00 B/s   99.57 K/s  0.00 %  2.82 % postgres: example example 10.18.14.1(59535) idle
19171 be/4 postgres    0.00 B/s   98.77 K/s  0.00 %  1.78 % postgres: example example 10.18.14.1(59540) idle
19179 be/4 postgres    0.00 B/s   98.37 K/s  0.00 %  2.22 % postgres: example example 10.18.14.1(59547) idle
19163 be/4 postgres    0.00 B/s   96.38 K/s  0.00 %  1.72 % postgres: example example 10.18.14.1(59532) idle
19189 be/4 postgres    0.00 B/s   94.39 K/s  0.00 %  2.26 % postgres: example example 10.18.14.1(59550) idle

Thanks in advance.

Write-heavy operations like COPY or INSERT INTO ... SELECT FROM will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Even if your write heavy operations do not use a buffer access strategy:

There is no reason for checkpointing to be doing most of the writing unless our checkpoint_segments are set too small.
If your bgwriter is not aggressive enough, it might not get enough data written and so the user backends will do it themselves.  You could try to make bgwriter more aggressive.  But ever since version 8.3.20 or so, the bgwriter is not very useful anyway, and having the backends do writes themselves is rarely a problem anymore.


Merge multiple column values into one column in different table? I've done this before, but I don't remember how. I need to select all rows with unique values from several columns in one table, and insert them into one column in a different table. For the destination table they need to be on separate rows in the destination column.

Here's an example:

                        Source Table
+-----+------------+------------+------------+------------+
|  id |  column1   |  column2   |  column3   |  column4   |
+-----+------------+------------+------------+------------+
|  1  |   value1   |   value1   |   value1   |   value1   |
+-----+------------+------------+------------+------------+
|  2  |   value2   |   value3   |   value4   |   value5   |
+-----+------------+------------+------------+------------+

 Destination Table
+-----+-----------+
|  id |  column1  |
+-----+-----------+
|  1  |   value1  |
+-----+-----------+
|  2  |   value2  |
+-----+-----------+
|  3  |   value3  |
+-----+-----------+
|  4  |   value4  |
+-----+-----------+
|  5  |   value5  |
+-----+-----------+

I tried this query but of course it didn't work because the columns aren't equal.

INSERT INTO table2 (column1)
SELECT DISTINCT column1, column2, column3, column4
FROM table1

Try with union

insert into table2(column1)
select column1 from table1
union
select column2 from table1
union
select column3 from table1


MySQL consuming a lot of CPU I’m having trouble with a new server. I installed the optimized configuration for Ubuntu server with EasyEngine of VirtuBox.

When migrating my website with template newspaper, the CPU consumption is very high.

Here is a mysql tuner report.

MySQL tuner result:

I have a database with more than 400 thousand posts. My server is:

Intel Core i7-6700K CPU
HDD 250 GB SSD
RAM 16 GB DDR4-2133 PC4-17000

with easyengine + rediscache + wordpress.

With little access the consumption of CPU is a load average: 7.56, 3.43, 4.81.

Thank you in advance for your attention.

Use persistent connections, cpu will drop dramatically. Then check slow queries.


Referencing a single table from all tables in the database At current, I have around 130 tables in my database, anticipating a growth of circa 200 tables in total.

Now I have several tables (well, most of them, actually) that contain properties like CreatedBy / ModifiedBy / DeletedBy, which take an nvarchar(MAX) as their data type and contain the name of the user.

This makes versioning a lot harder, because the name of the user might change during the lifetime of the application.

Now I've thought about referencing each and every *By-Column via a Foreign Key to the users table. However this would result in 600 foreign keys on a single table (expecting the 200 tables with 3 foreign keys per table, if not more).

Are there any drawbacks? What's the best practice?

As a side note (not relevant for the dba aspect of my question, I presume): I'm using Entity Framework in C#, only referencing a single part of it in my code (because I don't want and don't need 600 collections in my User class).

You may want to consider a Type 2 slowly changing dimension table and normalize your user name data. This will give you a better picture of whom is changing the data over time and allow you to use a smaller more efficient surrogate key for your *By columns.


History from SQL Agent Jobs (SQL Server 2005) how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records
are these records stored in system databases, not only in plain log files
how can I get records generated by SQL Agent Jobs only
is this history settable,

You need msdb.dbo.sysjobhistory and a few JOINs of course to read the data

To change how SQL Server Agent uses this table, use this stored procedure with suitable values

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100


Creating schema for a shop that sells items from multiple tables I am trying to create a table in mySQl that contains a list of all the items that a shops. For example:

shops (shop_ID (PK), shop_name)

shops_and_items (shop_ID (PK), item_ID (PK), price)

The issue I'm facing is that a shop can sell two types of products. For example, furniture and clothing. In my database, I already have separate tables for both furniture items and clothing items. The suggested shops_and_items schema therefore does not work.

I'm wondering, what is the best way to go about it. Should I create two tables shop_and_furniture_items and shop_and_clothing_items ?

I recommend reading the following link on Database Inheritance: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx

Databases don't naturally fit hierarchies - they do work, but it's not their strong suit.

In your case I would change shops_and_items for

**products**(product_id (PK), shop_ID (FK), ... then columns for common fields: name, price, stock, delivery time)

Then...

Option 1 :: Fewer Product Categories

If you only intend to have furniture and clothing then you could do

**clothes**(product_ID(FK and PK), size, colour, gender etc)
**furniture**(product_ID(FK and PK), height, width, depth, material etc)

Note that product_ID is a foreign key and primary key... a product would either have a corresponding record in clothes OR furniture. This doesn't need to be auto incrementing. A product has one clothes item or one furniture item (not both).

Option 2 :: Many Product Categories

If you intend to expand to lots of different products (electrical, food, ) and possibly sub-products then you will have a lot of tables and it will be a mess to manage! In this case it's easier to go down a meta-data route like WordPress http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png

WordPress uses wp_posts for a post, page, any many other plugin-defined types (map, questionnaire, gallery etc), if they need extra properties then they are stored in wp_postmeta.

You would keep the products table but add on:

**product_meta**(product_id(FK), meta_name, meta_value)

e.g.
4 (Surfer T-Shirt), colour, white
4, size, Medium
4, gender, male
18 (TV Stand), material, metal
18, height 40cm
18, depth 33cm
18, width 60cm

This might need some more validation before the data goes into the database as meta_value will most likely be a varchar column so you will need to standardise how you interpret values. Will your code use M/F for Male/Female? What units for measurments?

I recommend grabbing some open source shopping carts and installing them just to find out how they structure their SQL code - they will have their own solutions for sub-categories and custom properties of products.


Shrink Database Did Not Reclaim Much Space I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive.

Yesterday evening I dropped a large table that was no longer needed and last night I set up a maintenance plan to shrink the database with 25% free space to remain after shrink, and to rebuild the indexes.

The job ran successfully, however the size of the .mdf file was only reduced by about 1.5GB even though the sp_spaceused results indicate that most of the database is unallocated space.

database_name,database_size,unallocated space
redacted,28717.69 MB,28349.03 MB

I was expecting a far larger decrease in the size of the database.

Did I improperly configure the maintenance plan? Am I misunderstanding what happens when a database is shrunk? Both?

When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.


"Aggregation" by hour when data may be much longer or much shorter I have duration data which looks like this:

deviceid                          StartRange            EndRange            Duration    eventname   value
57a6f2499aaaeb1580f1ca36    2016-08-18 13:37:02.020 2016-08-18 13:38:22.020 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 13:38:22.020 2016-08-18 14:14:06.380 2144        Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:14:06.380 2016-08-18 14:15:26.380 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 14:15:26.380 2016-08-18 14:16:06.380 40          Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:16:06.380 2016-08-19 00:00:00.000 35034       Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:02:41.840 2015-08-18 15:04:01.840 80          Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:01.840 2015-08-18 15:04:41.840 40          Engine  1
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:41.840 2016-08-18 13:37:02.020 31617141    Engine  2

(I basically log the data every time a state changes, so for each point in time there can be only 1 value.)

On this data I need to allow users to get the total duration per value on any given range and on any given interval (minutely, hourly, daily... yearly).

Narrowing the range is not a problem, however the interval I can't figure out.

The problem is that 1 row can be turned into X datapoints in that query but how to open the data and then "bucket it" and aggregate it?

One idea is to start with a list of hours. Consider a table of numbers and then convert it to a list of hours using dateadd. Like this but with hour instead of days: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788

Then join your table of hours to your point table ON h.thehour > dateadd(hour,-1,r.StartRange) AND h.thehour < r.EndRange

Now you can work out the number of seconds (or however granular you want it) within each hour using datediff with CASE, taking the smaller bounds of the hour and range. Something like:

SUM(datediff(second,
    CASE WHEN h.thehour > r.StartRange
         THEN h.thehour
         ELSE r.StartRange
    END,
    CASE WHEN dateadd(hour,1,h.thehour) < r.EndRange
         THEN dateadd(hour,1,h.thehour)
         ELSE r.EndRange
    END
))

You could also just use a table of numbers and consider the first hour is zero, but then you'll need to convert it to hours before doing the aggregate, so it'll be easier to get your hourly blocks first.


Rollback on \include error in psql I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this:

\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre

I'm executing it using psql:

psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e --single-transaction -d my_db -f my_script.sql

The problem is that errors with the include meta command do not cause a transactiopn rollback. e.g. if some of tableX.cre files is missing, any changes before its include will be commited. However, if there's some SQL syntax error, everyting works as expected.

Is it possible to somehow handle include related errors and rollback the active transcation?

I'm using PostgreSQL 9.2.1.

Finally I've got an answer at pgsql-general mailing list by Adrian Klaver (link)

There is some kind of unexpected behavior with using both --set ON_ERROR_STOP=on and --single-transaction options. The ON_ERROR_STOP stops the processing, but does not rollback current transaction in case of \include error.

So, the solution for my initial problem will be removing the single-transaction option and wrapping explicit BEGIN/COMMIT statements around my main script.


Is this SQL schema normalized according to 3NF? This schema is for a sports statistics/data app

Note:

Pink background = string/char/varchar
Yellow background = int/tinyint
Green background = dateTime
bold column = PRIMARY key
id_* = FOREIGN key

Edit: round two.

Assuming a yellow background means that column is part of the primary key, I think Game could use some more attention.  First up, homeScore and awayScore shouldn't be part of any key.  It is acceptable (though unlikely) that every singe row have the same values for these two columns.  Second, id_WinningTeam is can be calculated from the home and away teams and their scores.  It could be handy to retain it for performance but I'd suggest it be taken out of any key.

I can see some candidate keys which could provide uniqueness, depending on some other rules.  If you limit Venue to single-concurrent-use places, like a football field where only one game can take place at a time, id_Venue and datetime becomes a candidate key.  If you are including places like squash clubs where many games can be running simultaneously then this won't work.  Similarly id_HomeTeam and datetime will be unique and could be a key.

PlayerTeams may be redundant to PlayerGames, depending on what questions you will be asking of this system.  If you keep it adding joining and leaving dates could prove useful.

On a point of style - you use both singular (Game) and plural (PlayerGames) names.  This is the stuff of religions wars but I'd suggest you pick one and stick with it.


How does Oracle 10g use indexes? I have table called result:

CREATE TABLE result
    (id_graduate                    NUMBER NOT NULL,
    id_sml                         NUMBER NOT NULL,
    last_update                    DATE NOT NULL,
    id_kt                          NUMBER NOT NULL,
    value_kt                       NUMBER NOT NULL,
    id_res                         NUMBER NOT NULL)
/
ALTER TABLE result
ADD CONSTRAINT pk_id_res PRIMARY KEY (id_res)

And an index:

CREATE UNIQUE INDEX x_pk_id_res ON result
  (
    id_res                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING
/

When I run SQL query #1, Oracle uses index X_PK_ID_RES

select ss.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

But, when I run SQL query #2, Oracle does not use index X_PK_ID_RES. Instead it uses an ACCESS FULL TABLE access path.

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

So, I added a new column id_res2:

ALTER TABLE RESULT  ADD (  ID_RES2 NUMBER );

And I created a new index:

CREATE UNIQUE INDEX x_id_res2 ON result
  (
    id_res2                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING

When I run SQL query #3, Oracle uses index X_ID_RES2.

select ss.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

And, when I run SQL query #4, Oracle uses index X_ID_RES2 too.

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

Why did Oracle use an index in SQL #4, but did not use one in SQL #2?

When Oracle uses an index, it has to lookup the actual data of the table in the table itself. Remember, an index is just a list of pointers to the actual data ordered in some way. The index itself only contains the indexed columns.

So, if you specify

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

You are asking for the contents of ALL columns in v_journal and result. Oracle can then choose based on heuristics (rule based) or statistics (nowadays) what is expected to be cheaper. When the number of expected returned rows is high compared to the total size of the tables, a full table scan is considered cheaper (and is often faster, but please remember it may purge your database cache as a nasty side effect).

When you replace it by something just needing the data in the index like:

select count(*)
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

All needed data can be found in the index and the index will be used as a cheap full table scan (full index scan), since the index is probably smaller in size in bytes.


MySQL-NT is crashing frequently MySQL is crashing frequently and there is no error in the err log file.

The following error message was found in the event viewer:

  faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0 fault address 0X001176b8

MySQL version - 5.0.24
OS - Win2003 with 4GB Ram

Is there any problem with combination of MySQL 5.0.24 and Win2003?

I would upgrade away from MySQL 5.0 altogether.

Please note you are using mysqld-nt.exe

It has been a very long (probably over 1.5 yrs) since the NT-featured mysqld was distributed. The only benefit to using mysqld-nt was if you are using shared memory or named pipes, which were just nuances when MySQL 5.0 came out for Windows.

The MFC C++ runtime distributables have been updated several times over the years. If you have been running updates against Win2003, things are probably very different for mysqld-nt.exe now.

Before upgrading, try using mysqld.exe instead of mysqld-nt.exe. If you still get the same crashing behavior, switch out of MySQL 5.0 for Windows and go to MySQL 5.5 because MySQL Support for Windows 2003 went EndOfLife back in April of 2011. I would also think about using a Windows 7 Server instead of Windows 2000/2003/NT.


Joining columns with null marks into one row I used a sub select to get the data I want. I'm struggling to merge it all into one row now though.

This is an example of the result where its bike but there are loads on them:

bike    bike    null    null    bike    6       6
bike    bike    null    null    bike    null    null
bike    bike    2       2       bike    null    null

Any idea how I can merge or 'join', if you will, these rows together to act as one?

Output would ideally be bike 2 2 6 6.

The large data output above is literally the output of the query I ran.

It's difficult without seeing your query, but you want to aggregate on your fields with null values if you want to remove the redundant rows.

This query would give you one row of data in this example, I'm not sure how you mean to get your output where you are removing two rows.

SELECT
     Field1
    ,Field2
    ,MAX(Field3) Field3
    ,MAX(Field4) Field4
    ,Field5
    ,MAX(Field6) Field6
    ,MAX(Field7) Field7
FROM TableName
GROUP BY
     Field1
    ,Field2
    ,Field5