Why do my ON UPDATE/ON DELETE rules disappear? I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working?

I am using phpMyadmin to run my hand written queries and I can see in the relational view (part of phpMyadmin) of the OrderContains table that there are no connections.
I can't even find the Orders.orderNr in the list of possible connections.

Any suggestions?

Here are my table definitions:

CREATE TABLE Users   (
    userNr          INT,
    password        VARCHAR(50),
    language        CHAR(3),
    name            VARCHAR(32),
    email           VARCHAR(32),
    phone           VARCHAR(32),
    security_level  INT,
    address         VARCHAR(32),
    comment         TEXT,

    PRIMARY KEY     (userNr)
)  ENGINE=INNODB;

CREATE TABLE Customers   (
    userNr          INT,

    FOREIGN KEY (userNr)    REFERENCES  Users(userNr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    PRIMARY KEY (userNr)
) ENGINE=INNODB;

CREATE TABLE Items   (
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    description     TEXT,
    weight          FLOAT,
    added           DATE,

    PRIMARY KEY(itemNr, revision)
) ENGINE=INNODB;

CREATE TABLE Orders  (
    customer        INT,
    orderNr         INT,
    title           VARCHAR(32),
    added           DATE,
    status          CHAR(1),
    notes           TEXT,

    PRIMARY KEY     (customer, orderNr),
    FOREIGN KEY     (customer)      REFERENCES  Customers(userNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;

CREATE TABLE OrderContains   (
    customer        INT,
    orderNr         INT,
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    quantity        INT,

    PRIMARY KEY     (customer, orderNr, itemNr, revision),
    FOREIGN KEY     (customer, orderNr) REFERENCES  Orders(customer, orderNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    FOREIGN KEY     (itemNr, revision)  REFERENCES  Items(itemNr, revision)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;

I made the following query in phpMyAdmin
    SHOW CREATE TABLE OrderContains

and I got this result:

CREATE TABLE `OrderContains` (
 `customer` int(11) NOT NULL DEFAULT '0',
 `orderNr` int(11) NOT NULL DEFAULT '0',
 `itemNr` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `revision` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `quantity` int(11) DEFAULT NULL,
 PRIMARY KEY (`customer`,`orderNr`,`itemNr`,`revision`),
 KEY `itemNr` (`itemNr`,`revision`),
 CONSTRAINT `OrderContains_ibfk_1` FOREIGN KEY (`customer`, `orderNr`)     REFERENCES `Orders` (`customer`, `orderNr`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `OrderContains_ibfk_2` FOREIGN KEY (`itemNr`, `revision`)     REFERENCES `Items` (`itemNr`, `revision`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I've also tried restarting the server.

DISCLAIMER : Not a user of phpMyAdmin

When visually comparing the table design and the screenshot, I noticed something interesting: It seems that phpMyAdmin is not designed to graphically represent a compound constraint (a constraint with more than one column). How can you tell ???

The Users table has a PRIMARY KEY on the single column userNr. A primary key is a constraint. (Just run select count(1),CONSTRAINT_TYPE from information_schema.table_constraints group by CONSTRAINT_TYPE; and will see PRIMARY KEY registered as a constraint).
The Customers table has the sinlge column userNr as PRIMARY KEY also, thus it is a constraint.
The Orders table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
The Items table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
The OrderContains table has 3 constraints, 1 PRIMARY KEY and 2 Foreign Key. Notice that the two foreign key constraints are defined as having two columns each but the only the first column of each constraint is displayed in the screenshoot.

You can use information_schema.KEY_COLUMN_USAGE

mysql> desc KEY_COLUMN_USAGE;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |     |         |       |
| TABLE_CATALOG                 | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME                   | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

mysql>

Notice that the information for constraints and all participating columns can be located here. Since you can see the constraints you defined using the mysql client, rest assured that you are not going crazy. It just seem to me phpMyAdmin is not coded correctly for multicolumn constraints.

Therefore, phpMyAdmin cannot properly see the UPDATE and DELETE CASCADE rules for a compound constraint because its source code is most likely using the first column only as the reference point to the information_schema database.


Associate SQL Server Queries with Databases Is there a way via T-SQL and DMVs to show which database a query was run against?

The goal is to look at CPU and I/O by query by database so I can redistribute the databases as I reconfigure the environment.

I do not want to use trace, profiler or extended events if I can help it. I also do not want to shred XML if I can avoid it. I am looking for history not just active.

You have to concentrate on Memory, Disk IO and CPU utilization to balance out your environment.

Best is to start collecting and dumping the results in a utility database and to give you a good baseline using sql agent on a scheduled basis.

Alternatively, you can use Performance data collector (applies to sql server 2008 and up)

MEMORY:

You can look into buffer pool usage (what databases are occupying more space in buffer pool ?)

From :Performance issues from wasted buffer pool memory

SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

DISK IO:

In terms of Disk IO, you need to move away the mdf/ldf files having the most disk IO to a better/faster LUN/Array :

From : How to examine IO subsystem latencies from within SQL Server

SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

Also refer to Leveraging sys.dm_io_virtual_file_stats

CPU:

From : Brent's answer

SELECT total_worker_time/execution_count AS AvgCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration
, total_elapsed_time AS TotalDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1
, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS txt
, query_plan
FROM sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 1 DESC

A gold mine of DMV related queries : SQL Server Diagnostic Information Queries for September 2014

For completeness, if you are using sql server 2012, you can use the system health reporting dashboard for Visualizing sp_server_diagnostics results.


Making a message system I have created a messaging system with MySQL structure is as follows
      `message_id` int(11) NOT NULL AUTO_INCREMENT,
      `message_from` int(11) NOT NULL,
      `message_to` int(11) NOT NULL,
      `message_body` varchar(255) NOT NULL,
      `message_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `message_deleted` tinyint(1) NOT NULL DEFAULT '0',
      `message_read` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`message_id`),
      KEY `message_from` (`message_from`),
      KEY `message_to` (`message_to`)

Currently The PK is an AUTO_INCREMENT so I am wondering if using this kind of key will be efficient for this type of system. Also the main reason why i am having a asking this question is to help in my function I have been having some issues with loading more messages. due to the fact that messages are deleted the PKs will not be consistent such as 1,2,3,10,255,300. When I try to run the more function using
(SELECT MAX(message_id)
 FROM member_messages
 WHERE (message_to = :userId
 AND message_from = :friendId)
 OR
 (message_to = :friendId
 AND message_from = :userId))AS max_id,
FROM member_messages AS MSG
WHERE (MSG.message_to = :userId
AND MSG.message_from = :friendId)
OR
(MSG.message_to = :friendId
AND MSG.message_from = :userId)
AND MSG.message_deleted = '0'
HAVING MSG.message_id >= max_id - 20
AND MSG.message_id <= :upperId
ORDER BY message_id ASC

In effect this query should return messages from the last 20 entered, but due to the PK being inconsistent sometimes no messages are returned. Eg. if the max_id is 200, 200- 20 is 180 and the messages with ID between this range are deleted or not associated with this members(userId) recipient(friendId). Any insight into this problem as to whether I should change the key type or change the query itself? Thanks for your time.
EDIT
Just a side note I used ASC because I wanted the newest messages to appear at the bottom of the DIV, while the fetched results would be older and appear at the top.

Your primary key is fine. I don't see a reason to change it.

The issue you are having is because you are basing your query on the messsage_id and it being without gaps. You could base your queries on the message_time column (I suppose this holds the datetime that the message was sent).

Even if you keep using the messsage_id for the queries (assuming that biggest id means the latest message - which may not always be correct), you can still write a query that returns always 20 messages, gaps or not - just change ORDER BY message_time DESC to ORDER BY messsage_id DESC in the 3 places it appears:

SELECT *
FROM
  (   ( SELECT message_id, message_time,
               'U <- F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :userId
          AND message_from = :friendId
     --   AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20
      )
    UNION ALL
      ( SELECT message_id, message_time,
               'U -> F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :friendId
          AND message_from = :userId
          AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20
      )
    ORDER BY message_time DESC
        LIMIT 20
  ) AS tmp
ORDER BY message_time ASC;

Other issues:

You probably meant to have the deleted = 0 check on both cases (message sent by me to friend and sent by friend to me). If yes, undelete the commented line above - and note that AND has higher precedence than OR in SQL. The condition you probably wanted to use was (note the removed pair of parenthesis):

WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
  AND ...

So, you could write the query like this:

SELECT *
FROM member_messages AS msg
WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
      AND message_deleted = 0
ORDER BY message_time DESC
    LIMIT 20 ;

I used UNION for efficiency reasons. Complicated conditions with OR may not be optimized very well. You could also add an index on (deleted, message_from, message_to, message_time).


Why does MySQL flag a duplicate key where none exists? I'm working on adding an auto-increment field to a large table.

The field is already populated (roughly 1-500M) and new inserts are manually incrementing correctly. To verify, I ran a query to show any duplicates:

mysql> SELECT new_id, COUNT(*) AS count FROM my_table GROUP BY new_id HAVING count > 1;
#      No Results

And when I manually look at the latest records I see what looks like an auto-incrementing BIGINT(20) field. And no NULL's exist in that column.

Yet when I try to change the index to a UNIQUE INDEX (from non-unique index, but with unique values), it triggers a DUPLICATE KEY error. And the duplicate key is always MAX(new_id)+1, so a SELECT for the offending duplicate always returns no results.

Here's what I'm running:

mysql> SELECT MAX(new_id) FROM my_table;
+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_table
        DROP INDEX `idx_new_id`,
        ADD UNIQUE INDEX `idx_new_id ` USING BTREE (`new_id `);

ERROR 1062 (23000): Duplicate entry '512345679' for key 'idx_new_id'

But why? This record doesn't even exist! The MAX is 1 less than this and hasn't changed since before the query. So I prove it...

mysql> SELECT MAX(new_id) FROM my_table;

+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM my_table WHERE new_id = 512345679;
# No results

Why did MySQL flag 512345679 as a duplicate if record 512345679 doesn't even exist?

I am so far unsuccessful in duplicating the problem from scratch. (db-fiddle)

Can you point me where to look next?

Ultimately I'm headed toward changing the primary key from another field to this one. But am trying to prevent downtime by pre-populating the unique ids (already done). If I am successful with this step I'll switch this new_id field to the PRIMARY KEY and AUTOINCREMENT it.

Here's the SHOW CREATE for my_table:

CREATE TABLE `my_table` (
  `new_id` bigint(20) NOT NULL,
  `id` bigint(20) NOT NULL,
  `username` varchar(80) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_username` (`username `),
  KEY `idx_new_id` (`new_id`) USING BTREE,
  KEY `idx_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I tried running your code in MySQL 5.7.12 for MacOS. I can't seem to duplicate this, but I found something interesting.

SQL-Fiddle has MySQL 5.7.17. You got the error in MySQL 5.7.13. Was anything with regard to dropping and adding a secondary index fixed between those versions ? I believe it was. Please note the Release Notes for 5.7.14, bulletpoint 21 under the subheading Bugs Fixed:

  InnoDB: An assertion was raised during rollback of an ALTER TABLE operation that dropped and added a secondary index. (Bug #22005726)

Question: What is a secondary index ? According to the MySQL Documentation for Clustered and Secondary Indexes right under the heading How Secondary Indexes Relate to the Clustered Index:

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

This show that a UNIQUE INDEX is just a secondary index. A secondary index would have a copy of the PRIMARY KEY attached to every index entry.

My assertion is that a bug was introduced in 5.7.13 that did not exist in 5.7.12 when it comes to dropping and creating an index on the same column(s) at the same time.

Look back at your ALTER TABLE command

mysql> ALTER TABLE my_table
        DROP INDEX `idx_new_id`,
        ADD UNIQUE INDEX `idx_new_id` USING BTREE (`new_id `);

You dropped an index with a specific index name (idx_new_id) and with a specific column list (just one column, new_id) and then said add a new index with the same name and the same column list but make it the index UNIQUE. The net effect is that the index was never really dropped and the UNIQUE attribute was simply attached to the already existing non-unique index. Then, under the hood, mysqld attempted to load data into the table which is already populated and an index that's still populated, thus creating a duplicate key error. Please note I am only asserting this. I would have to look at source code for 5.7.13 to see if this is indeed occurring.

Notwithstanding, 5.7.12 and 5.7.14 cannot reproduce this problem. This indicates that this strange condition was addressed when making 5.7.14.

In light of all my conjecture, here are my suggestions:

SUGGESTIONS

SUGGESTION #1 : Upgrade to 5.7.14 or above

SUGGESTION #2 : Change the ALTER TABLE to use a different index name

mysql> ALTER TABLE my_table
       DROP INDEX `idx_new_id`,
       ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

SUGGESTION #3 : Do 2 ALTER TABLE commands

mysql> ALTER TABLE my_table DROP INDEX `idx_new_id`;
mysql> ALTER TABLE my_table ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

GIVE IT A TRY !!!


What performance advantages does append-only postgres allow? Suppose we have a Postgres Database with only a single table. It is known that no UPDATE statements will ever be run on it, nor will any DELETEs. It is 100% append-only.

Some databases exist that rely on this behavior and manage to get specific performance gains as a consequence, such as Datomic. However, this is not the typical way Postgres is expected to be run so I imagine to benefit, performance-wise, I'll need to configure it specially.

What are the primary ways I can take advantage of my no UPDATE/DELETE guarantee?

I believe I could all-but-disable vacuum, but I'm not 100% certain of that and I don't think that's a huge win anyway. What are the largest configuration changes that could be made here?

If it matters, writes happen at a specific time once-a-day and never at other times. However, I think that detail is getting into "too localized" territory so I'll edit it out of this question if it isn't helpful. (It if is helpful, I'll edit out the disclaimer portion here!)

If you wish to benefit from index-only scans (IOS), you will still need to vacuum your tables even if they are append-only.  Indeed, autovacuum was never adjusted for the needs of IOS, so it is likely you will need to schedule your own vacuums for IOS to be maximally effective on append only tables.  Certainly don't disable autovac, it will naturally not do stuff when there is nothing for it to do so no good will come from disabling it.

Other than that, I don't think there is much you can do.  This situation will greatly benefit from the freeze map introduced in 9.6, but there is nothing you need to do to activate that, other than use a version at least that high.


SQL Server Import Wizard Excel into SQL Server 2016 Error 0xc020901c I am trying to import an Excel file with 3914 rows of data into an existing table on SQL Server 2016 Express through the SQL Server Management Studio. I imported a similar file last month, but this is a monthly update file, so I deleted everything from the table before I imported the new file. However, now, when I go to finish the wizard and execute everything, I get the following error messages:

  Messages

  Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
   An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL >Server"
  Hresult: 0x80004005  Description: "Unspecified error".
  (SQL Server Import and Export Wizard)

  Error 0xc020901c: Data Flow Task 1:
  There was an error with Destination - IACList.Inputs[Destination >Input].Columns[Approval Number] on Destination - IACList.Inputs[Destination >Input].
  The column status returned was: "The value violated the integrity constraints >for the column.".
  (SQL Server Import and Export Wizard)

  Error 0xc0209029: Data Flow Task 1: SSIS Error Code >DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - >IACList.Inputs[Destination Input]"
  failed because error code 0xC020907D occurred, and the error row disposition on >"Destination - IACList.Inputs[Destination Input]" specifies failure on error.
  An error occurred on the specified object of the specified component.  There >may be error messages posted before this with more information about the >failure. (SQL Server Import and Export Wizard)

  Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  >The ProcessInput method on component "Destination - IACList" (35) failed with
  error code 0xC0209029 while processing input "Destination Input" (48). The >identified component returned an error from the ProcessInput method. The error >is
  specific to the component, but the error is fatal and will cause the Data Flow >task to stop running.  There may be error messages posted before this with more
  information about the failure. (SQL Server Import and Export Wizard)

It looks to me as though the error has something to do with the Approval Number column. When I query the table, it shows 3,824 records on the server after importing, but there should be 3,915 records. I checked the record that I believe would be number 3,824 in the Excel file, and there doesn't seem to be any issue with it. Perhaps I'm misunderstanding the error message, but I don't see a reason the integrity constraint would be violated. It is a primary key, but there is no other field in the column that matches it, so it is unique.

The important portion of that is the following:

  There was an error with Destination - IACList.Inputs[Destination >Input].Columns[Approval Number] on Destination - IACList.Inputs[Destination >Input]. The column status returned was: "The value violated the integrity constraints for the column."

So check the data type of the "Approval Number" column in SQL, or any other constraints/foreign keys you've added to the table, and compare that to the values in your new spreadsheet.

If the table was created based on values from the first spreadsheet, it is possible the columns is set to a too-small datatype, or the wrong datatype entirely.

For example, maybe the first spreadsheet contained only numbers in the "Approval Number" column, so the column in SQL has a datatype of INT. But the second one has some rows with letters ("197234b" or "unknown" or something), or rows with values too large for an INT datatype.

You'll need to either change the datatype of that column to something more appropriate for the actual data you are receiving (varchar or BIGINT or whatever), or edit the spreadsheet before you import it to remove any bad values.

EDIT: Also check out the ideas in this Stackoverflow question: looks like Excel sometimes tries to import extra blank rows at the end of the list. You can handle this in one of several ways:

Press CTRL-END in Excel, this will take you to the bottom of where Excel thinks there is data. Delete (don't just clear) those entire extra rows.
You can change the constraints on the destination to "Allow NULLs" in the destination rows, then delete them from SQL afterwards
You can change the filter criteria for your input in SSIS to exclude any rows with missing values
You can add an "error" output to the data transformation in SSIS to output any failing rows to a different dataset, which you can either ignore, or insert into a table with no constraints (all varchar(255)s, ALLOW NULL on every column, etc). That way if there are different errors in the future, you can see right away which rows were an issue.


SQL Server freezes (because of application), need logging We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unannounced) will cause the SQL Server to freeze. I can't even restart the SQL Server service; I have to restart the entire machine.

Needless to say, I can't open a query window to run sp_who2 to find the cause. It could be days before the issue shows up again. Is there any type of logging I can put in place to better track down what is causing the SQL Server to freeze up?

exec xp_readerrorlog only shows me what has happened after the restart so it isn't much help.

At the time it freezes, CPU is pegged at 90-97% and memory is maxed at 8 GB. Server has 12 GB but the max is set to 8192 for SQL Server.

SQL Server has a Dedicated Administrative Connection, see How to: Use the Dedicated Administrator Connection with SQL Server Management Studio. The DAC uses distinct, pre-reserved resources (memory, CPU, IO ports etc) exactly so it can be used even when the server is 'frozen'. From the DAC you can run queries that can identify the cause of the spike.

When the server is restarted the ERRORLOG is not deleted, it gets recycled. The errorlog from before the shutdown is usually ERRORLOG.1, then ERRORLOG.2 etc, and is located in the same folder as the current one. Just open it and inspect the tail of the file. Your previous restart logs are probably there still, have a look and see what you can find. Also see if there are any .mdmp files in the same location...


Problem with restoring database which has a filestream When I restored a database backup file, which uses FILESTREAM feature, everything was fine but after a while the database went to the (recovery pending) mode. I tried restarting SQL Server service and it still didn't work. I decided to delete the database and restore it again but I got this error while restoring:

  Restore of database 'Ka4' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError: FILESTREAM feature is disabled.
  (Microsoft.SqlServer.SmoExtended)

I've reinstalled SQL Server instance but nothing has changed and it still keeps saying the same error.

According to this page I'm sure that FILESTREAM feature is enabled.

P.S. This is my SQL Server version:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58

Thanks to @Nic.
It seems that there's a bug with Windows 10 Creators Update. After reading this article I could manage to solve the problem by changing the service startup account to built-in account LocalSystem and now it works.


Sales by month cross tab query using pivot I am trying to show sales by month using PIVOT in Sql Server 2005.  I know it's a common question, but I just can't figure out what I am missing here:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in (1,2,3,4,5,6,7,8,9,10,11,12) --this shows error??
) piv;

I am getting an error at the "in" clause in my pivot.  Can anyone tell me what I am doing wrong?

Since your new columns are numbers, you need to escape the names with square brackets:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;

The square brackets [] are used to delimit identifiers. Since the column name begins with a number, this must be delimited. See MSDN for more details.


When to use a spare index over a dense index? I have a question in my textbook:

  When is it preferable to use a dense index rather than a sparse index? Explain your answer.

My first thought was that the dense index would be good when it'd cost a cheap / worthwhile amount of space relevant to the performance gain. When I was googling for the answer (to verify), I found this answer in multiple places

  It is preferable to use a dense index instead of a sparse index when
  the file is not sorted on the indexed field (such as when the index is a secondary index) or when the index file is small compared to the size of memory.

and it's not very clear to me what it's saying.

For the first reason

  when the file is not sorted on the indexed field (such as when the index is a secondary index)

What is "the file", and likewise which field is "the indexed field", the field in the primary index or the one in the secondary index? Also just in general, I'm still not sure how this justifies the dense index.

For the second part

  when the index file is small compared to the size of memory.

What is "the size of memory"  in this context? Is this trying to say what I said? (the dense index is cheap to make?)

I'd really appreciate a better explanation/clarification of the above.

In context:

  when the file is not sorted on the indexed field (such as when the index is a secondary index)

"the file" = the database table (or similar object) being indexed
"the indexed field" = the field being considered for the new index. A sparse index relies on the records being sorted in the same order as the indexed field.

Example: data in table is sorted on ID:

  ID    Last Name
   1    Smith
   2    Francis
   3    Jones
   4    Zygoski
   5    Bohr
   6    Josephson
   7    Michaels
   8    Able

And, imagine this is a sparse index on Last Name (with pointers to the appropriate records:

Index
  Able
  Francis
  Jones
  Smith

Now, to find "Bohr", You go to "Able" and then search sequentially. Unfortunately, "Able" is the last record in the file, so, you can't get to "Bohr" from there.

  when the index file is small compared to the size of memory.

Basically, if the entire index can be loaded into memory, and still have plenty of room to load in the data from the table that's needed, then the database engine should be able to find each record in the index, and go directly to that record in the data table (instead of possibly having to pull in several pages from the table, scanning to locate the record it wants). If the dense index takes up too much room in memory, then either you have to read it in in parts, or it's going to squeeze the available memory for the rows you need from the data table.

Or, at least, that's my best guess. Note that I'm speaking form the view of someone who's been working with databases for almost 30 years; when I was in class, I don't remember dense vs sparse indexes being discussed.


Trying to understand how to use postgresql generate_series() for this operation First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence
This is the old question: Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

But I will rephrase the question again:

Those are my initial tables

Users: id(int) PK, weeks_to_live(int)
Calendar: id(int) PK, user_id (int) (FK -> Users(id))*
CalendarField: id(int) PK, text(text), rating(int), calendar_id(int) (FK ->Calendar(id))*, week_number(int)

Point is, when I generate my calendar in my app, actually my Users, and Calendar tables get filled properly.
Upon a generation they will end up looking like this

users

id   weeks_to_live
1    3
2    2

calendar

id   user_id
1    1
2    2

That part is fine, but I need my CalendarField table to be autogenerated too, and thats where supposedly generate_series() should come into action, so they will end up looking like this

CalendarField

id    text     rating    calendar_id   week_number
1     ''       0         1             0
2     ''       0         1             1
3     ''       0         1             2
4     ''       0         2             0
5     ''       0         2             1

As you can observe:

id: is autoincremental
text: always defaults to "",
rating: always defaults to 0,
calendar_id (fk) : is the relation with each calendar, who has an amount of calendarFields row equaling to the number up to weeks_to_live in users
week_number: this number, should begin at 0, and keep incrementing while its < than weeks_to_live in the users table

Now my question is, how do I generate that last insert so my CalendarField table ends up filling with that info?. I suppose that with generate series, I wont need to create an enormous insert with == weeks_to_live values in case it gets huge. But I don't understand how to do it.

Or should I do a giant Insert then?

You need to join users and calendar and then cross join that with a generate_series().

insert into calendar_field (text, rating, calendar_id, week_number)
select '', 0, c.id, g.wn
from calendar c
  join users u on u.id = c.user_id
  cross join generate_series(0, u.weeks_to_live) as g(wn);

Online example


MySQL table partition truncation not to propagate on a slave I have a table now. It has 7 seven partitions, and each day I truncate a partition(not delete, just empty the partition).

I have a use case where I would like to have a full backup of the table as well. The backup I want to keep is obviously on some other instance preferably a slave.

So in concise form, I want to know if is it possible that truncation query never runs on slave.

But other queries, runs fine.

I am open to any other better approach to achieve the same.

You do a TRUNCATE PARTITION on the Master daily, correct?  And you do not want that to be done on the Slave, correct?

If yes, then

SET SQL_LOG_BIN = 0;
TRUNCATE ...;
SET SQL_LOG_BIN = 1;

https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html

But, beware.  Once you have more than, say, 100 partitions, the table is likely to be slower and slower on the Slave.  And, at some point you will hit the maximum number of partitions.

I can't think of any thing that will "break" because of having more data (and more partitions) on the Slave than on the Master.  I assume you never otherwise modify the data in 'old' partitions.


If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration? Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

DECLARE @LoopId INT = 1;

WHILE (@LoopId < 100)
BEGIN
    INSERT INTO Table2
    SELECT Field1
    FROM Table1
    WHERE ForeignKeyField = @LoopId

    SET @LoopId = @LoopId + 1;
END

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

If the session has started a transaction before the loop, then all statements will be rolled back.  But by default each statement runs in a separate transaction, and only the currently-running INSERT would be rolled back.


SQL Server 2000 to 2008 to 2012 We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to know is - Is there anything I can do now to make it compatible to 2012 also at the same time. Atleast those changes that are backward compatible from 2012 to 2008 etc.. The reason, I want to do this, is to minimize any effort in future (3 to four years from now) when we decide to move from 2008 to 2012. Anything at all I can do or need to be aware of?

The best answer to  this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions.

For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but on the way out in a future version) and it is finally gone in SQL Server 2012. So if you kept that syntax in 2008 it would work, but would require work after upgrading to SQL 2012 or greater or you would have a failure.

To get you started, check out these links that take you to the articles that describe what features are no longer supported in the listed version and what features are due to be removed in the future.. Then look for these in your code.. I would also say you may want to look at future compatibility beyond SQL Server 2012 if you are holding off 3+ years.. For instance TEXT fields will still work in SQL Server 2012, but they won't likely work in the version out after SQL Server 2012 according to the links below.

SQL Server 2012 Deprecated Feature List
SQL Server 2008R2 Deprecated Feature List

You should also make sure that you let your database run in SQL Server 2008 compatibility mode after your upgrade to 2008 and ensure it works well in 2008 and to allow you to upgrade it to 2012 - SQL Server 2000 Databases, or databases operating in SQL Server 2000 compatibility mode will not migrate to SQL Server 2012.

I also wrote an answer that talks extensively about upgrades and migrations and general preparedness steps.


SQL server 2012 on Windows server 2012 I know this is a basic question. but I can't find any answer on the internet. I know SQL server 2012 is running on Windows 2012 server but when I look at the start page (it has IE, Control panel, task manager and etc). but how do I start SQL server 2012 that is running on this server?

Start->Run (Windows R if you prefer) SQLServerManager11.msc

That is the snapin for managing your SQL Server Instances.

Unless of course you mean SQL Server Management Studio which by default would be installed at "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"


SUM Case Logic with Distinct This is my first post, so please be gentle :)

I have a couple of queries:

SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'Work Web'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'

SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'OUTLOOK'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'

Now I've been asked to pull the data for the last year, so I thought I would use a case statement, but cannot figure out how to incorporate the distinct user part into the sum case statement....so I get huge numbers, obviously

Select 'Date' = format(h.ACTIONDATETIME, 'MMM yyyy')
, sum(CASE WHEN h.CUSTOM2 = 'Work Web' THEN 1 ELSE 0 END) WorkWeb
, sum(CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN 1 ELSE 0 END) Outlook
, 'UserCount' = COUNT(DISTINCT(h.USERID))
From MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where u.LOGIN = 'y'
and Year(h.ACTIONDATETIME)>2017
Group By Format(h.ACTIONDATETIME,'MMM yyyy')

What is returning is the following, which is a sum. I am looking for a distinct count for each way.

Thanks for any assistance

count(column) does not count null values so you could use count() with a case statement that returns h.USERID or null.

, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'Work Web' THEN h.USERID ELSE NULL END) UserCountWorkWeb
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN h.USERID ELSE NULL END) UserCountOutlook


How to use variables inside a select (SQL Server)? If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice?

Example:

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
      ,hashbytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com') as HashedEmail
FROM sometable

How do I avoid writing this twice without using more than one select?

Derived table

SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    (
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
   ) foo

or CTE:

;WITH cEMail AS
(
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
)
SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    cEMail

However, in this case I'd consider using a computed column with the hash precalculated


Correlated Subquery Versus Non-Correlated Subquery There are three tables that may be used by a business to keep track of the absent days of its employees. It's from a collection of SQL puzzles by Joe Celko. The reader is tasked with concocting a DELETE statement to remove those employees who have accrued 40 or more absent days. And for that purpose we use a subquery. The one proposed by the author is this:

DELETE FROM Personnel
WHERE emp_id = (SELECT A1.emp_id
FROM Absenteeism AS A1
WHERE A1.emp_id = Personnel.emp_id
GROUP BY A1.emp_id
HAVING SUM(severity_points) >= 40);

And my question is, why go for a correlated one? Isn't it just more efficient to run something like one below?

DELETE FROM Personnel
WHERE emp_id =
(SELECT emp_id FROM Absenteeism
GROUP BY emp_id
HAVING SUM(severity_points) >= 40)

Thank you!

The non-correlated version you proposed will fail if the subquery returns more than one row (which it likely will).  You should use IN rather than = here.  The purpose of Celko's correlation is to make sure only zero or one rows is returned from the subquery (because it matches and groups on emp_id).

Also, don't forget to alias the table inside the subquery and explicitly reference the alias.  Since both tables (Personnel and Absenteeism) have an emp_id column, you want to be very clear where you are referring to which table.

This is more of a "best practice" for readability, but can also prevent logical issues and mistakes in queries.  For instance, this query would delete every row in the table:

DELETE FROM Personnel WHERE emp_id IN (SELECT emp_id);

Of course, this may seen like a contrived example, but it happens: sql server 2008 management studio not checking the syntax of my query

So here's the updated query:

DELETE FROM Personnel
WHERE emp_id IN
(SELECT a.emp_id FROM Absenteeism a
GROUP BY a.emp_id
HAVING SUM(a.severity_points) >= 40)

Given those changes: you're right that the correlation isn't strictly necessary in this case.

Logically, it seems like the correlated version might be more efficient, especially if there are lots and lots of rows in the Absenteeism table that don't have a match in the Personnel table (because the unbounded subquery might read more data).

But one would have to test to be sure.  The query optimizer can take the declarative query you've written and do many different things with it.


Please explain the difference between row and page compression I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community.  I have read several articles, but wanted to have a place where this addressed in DB Administrators.

This is a good reference and explanation for row v page (no pun intended) compression:
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-sql-server-data-compression/

In short though, page compression encompasses the algorithms contained with row level compression and then also covers Prefix compression and Dictionary compression.  Prefix and dictionary compression finds patterns in the data and replaces them with smaller values.  During decompression it will do the reverse and restore the data to its original values.

Row level makes easier compression decisions, like reducing metadata, and removing unused space, like the letter 'Y' stored in a CHAR(1000).

The article referenced explains this in much greater detail.


Foreign key constraint is incorrectly formed (MySQL) So, I have these two tables
CREATE TABLE IF NOT EXISTS `inart_ps_categories` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `num_children` int(11) DEFAULT '0',
  `flag` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `inart_ps_category_associations` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
    FOREIGN KEY (categoryid) REFERENCES `categories` (categoryid) ON DELETE CASCADE,
    FOREIGN KEY (inart_ps_category_id) REFERENCES `inart_ps_categories` (inart_ps_category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

It outputs "Foreign key constraint is incorrectly formed" for no reason. The first Foreign key is correct and if I erase the second one, the query is executed. Can you see something wrong here?

Without seeing your categories table, your only problem is that the referenced columns like inart_ps_category_id need an index, which you can achieve by using KEY(inart_ps_category_id):
CREATE TABLE IF NOT EXISTS `inart_ps_categories` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `num_children` int(11) DEFAULT '0',
  `flag` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY(`inart_ps_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `inart_ps_category_associations` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
    FOREIGN KEY (categoryid) REFERENCES `categories` (categoryid) ON DELETE CASCADE,
    FOREIGN KEY (inart_ps_category_id) REFERENCES `inart_ps_categories` (inart_ps_category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;


How to give access to a user on only two specified procedures and specified tables on SQL Server? We would like to introduce an external orchestrator in order to feed some tables from two stored procedures on SQL Server. However, we will need to create an 'orchstr' user for the orchestrator.
We want to give access to the user 'orchstr' to execute only the two specified stored procedures (so there is a restriction on the other procedures). And access on the tables to be fed only.
Is it possible? How could we do this?
I'm still a beginner, it would be too kind of you to help me.
We have SQL Server 2012 Enterprise Edition.

Bu default, a user don't have any premissions. So, all you have to do is to grant the permissions you want:
GRANT EXEC ON proname TO usrname
GRANT SELECT ON tablename TO username

I don't understand what you mean by "tables to be fed".
If a proc operates on a table (SELECT, INSERT ...) then it is enough to have EXEC permissions on the proc, you don't need the (SELECT, INSERT...) privilege on the table in order to execute the proc. This assumes the same over of the proc as the table, called "ownership chaining".


What does the presence of rsAccessDenied mean? I'm troubleshooting a SSRS security issue where one user is being denied access, while another user with apparently identical settings is working as expected.

I'm seeing "The permissions granted to user 'mydomain\myAccount' are insufficient for performing this operation" in the browser, but curiously NOT seeing the (rsAccessDenied) at the end.

Is this indicative of something that can help me troubleshoot the issue?

Check the rights to the folder and report in SSRS. You can do this in the "Security" option under the "Folder Settings" menu. Click "Edit Item Security" and provide your user(s) with the correct rights and permissions.


Get rows changed between two dates (latest and previous) I have a table _sync_history with this data:

+----+----------------+---------------------+
| id | object_name    | sync_time           |
+----+----------------+---------------------+
|  1 | territory      | 2015-07-21 22:29:02 |
|  2 | user           | 2015-07-21 22:29:19 |
|  3 | userterritory  | 2015-07-21 22:29:38 |
|  4 | account        | 2015-07-21 22:30:05 |
|  5 | accountshare   | 2015-07-21 22:39:04 |
|  6 | address_vod__c | 2015-07-22 00:47:38 |
|  7 | group          | 2015-07-22 01:23:30 |
|  8 | recordtype     | 2015-07-22 01:23:51 |
|  9 | user           | 2015-07-23 13:04:20 |
| 10 | account        | 2015-07-23 13:04:59 |
+----+----------------+---------------------+

I want to get the rows on user between 2015-07-21 22:29:19 and 2015-07-23 13:04:20 meaning the rows between last sync_time and previous sync_time for a given object_name. I was doing this:

SELECT * FROM `user` WHERE lastmodifieddate > (SELECT sync_time FROM `_sync_history` WHERE object_name='user' ORDER BY sync_time ASC LIMIT 1)

But that will give me just the ones from last sync_time and above which is not right, how do I get the ones I need?

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1.

I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user':

SELECT *
FROM user
WHERE lastmodifieddate >
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 1
        )
  AND lastmodifieddate <
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 0
        ) ;


Transaction level difference between using a large IN filter VS. "BEGIN TRAN/COMMIT" I would like to delete about 100,000 records with minimal server overhead.  I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here.  Which would be better:

1-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID IN (
1
,2
,3
,4
...
,100000
)
COMMIT

2-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...
COMMIT

3-

DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...

My assumption is that #1 would cause a scan based on sample %, pick out the items, remove them, and log it as a series of transactions.  Perhaps it would base the info in the transaction log as to what pages were changed instead of each indvl. transaction, thus you can only rollback the entire activity and remark the pages, or you could commit. Is that correct?

On #2, I'm thinking does the GO statement cause a lot more transaction log activity by not allowing the SQL Server Storage Engine to roll all of these into 1 large transaction, but still provide some optimization for the transaction log with the BEGIN TRAN - COMMIT blocks, thus making it more effective than #3 but less effective than #1.

I then would assume #3 would be the worst one as each indvl. transaction is logged.

If anyone has any good blog posts or test scenarios they could point me to that would be great too.  I've looked at ways to dig deep in the transaction log to figure it out myself but at this point, I thought I would ask you guys.

Thanks!

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction.  There will be lots of locking and probably blocking while the delete is being run.  #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one.  #2 and #3 should take about the same amount of time to run.  #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this.  This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END


What are the disadvantages of using surrogate keys? I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value.

I know some advantages, for example that there is no need to identify unique columns without NULLs for primary keys, and there is no need to manage composite primary keys, normal forms are easier to manage, and uniqueness is guaranteed.

I want to know, is there any good reason regarding to performance, or index structures, etc. that should make us use real world factful keys instead of surrogate ones?

Thanks.

There are many disadvantages to using surrogate keys, the most important ones IMHO are:

Using natural keys significantly reduces the number of joins that your queries need to perform. With a surrogate key, you will always have to join back to the original table to get meaningful values.
Surrogate keys 'abstract' the actual values users are looking for from the optimizer which, significantly hinders the usefulness of statistics.
Natural keys enforce logical consistency and prevent duplicates. Surrogate keys are way too often abused as a magic solution, without regard to the candidate keys which in turn result in logical duplication of data.

While there are (rare) cases that justify the use of surrogate keys, the state-of-the-art is that 99.9% of the databases that i've seen just use this 'magic one-size-fits-all' solution, with detrimental impact on performance, modularity, and data consistency.

To learn some more about it, watch this session that I delivered on the subject at Silicon Valley Code Camp 2017.


oracle expdp similar method to export datas for SQL Server On our oracle databases, we regularly run database export through expdp/impdp utility (wrapped for readability):

$ expdp user/[password] SCHEMAS=<nom_schema> DIRECTORY=expDatapump
     DUMPFILE=exp.<schema>.dmp LOGFILE=exp.<schema>.log

Thanks to it we are able to refresh data from a specific schema on test database, for example.

I am wondering if there is such a way to do this in Microsoft SQL Server. I have tested the  -> tasks -> "export data" wizard within SSMS, but that was not very helpful (useable only for very small db, PK & FK deleted etc).

Can you perform dumps from your SQL Server databases? On an other hand, is it preferable (if possible) to directly restore a specific SQL Server schema from a .bak file?

There is no way to restore a partial database from a SQL Server backup file.  If you are looking to export and import data from various tables in one SQL Server database to another, you'll need to use one of the following methods:

BCP export and import
SQL Server Integration Services (SSIS)
SQL Server Replication
A third party tool, such as Informatica.  This is not an endorsement of their product.

This question and potentially others listed in the "Related" section to the left may offer more help.


InnoDB index concurrent inserts I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, each in a transaction.

Because there may be clashes, I need to make a decision for each row:

INSERT INTO my_table (a, b, c)
values ('a', 'b', 3)
ON DUPLICATE KEY UPDATE
a = IF(c<VALUES(c), a, VALUES(a)),
b = IF(c<VALUES(c), b, VALUES(b)),
c = IF(c<VALUES(c), c, VALUES(c));

If I run a few (e.g. 10) parallel insertion threads, is this a good idea? Will it cause me deadlocks due to contention on the index?

SHOW CREATE TABLE -- need to see the PRIMARY KEY and the "index".

100-1000 is optimal (10K might be ok) for "batching" INSERTs -- That is, a single INSERT statement with 100 to 1000 rows in it.

You have shown a single row being inserted.  Will it work change to

values ('a', 'b', 3), ('c', 'd', 4), ...

That might give you a 10-fold speedup.

That is also pretty good for the size of a transaction.  You could also set innodb_flush_log_at_trx_commit = 2.

Parallel threads might help.  In that case, transactions are vital, plus code to replay a transaction if it fails.  And you might be better off with 100 rows at a time -- to minimize deadlocks or delays due to other locking.

innodb_buffer_pool_size should be 70% of available RAM (assuming you are using ENGINE=InnoDB).


Are implicit transactions per batch or per session in SQL Server? Imagine I am running multiple batches through management studio, separated by the GO command. I'd like to know how implicit transactions will behave - is the transaction committed on a per batch basis or once for the entire execution.

What exactly do you mean by "implicit transactions"? Do you mean that you have the session property of IMPLICIT_TRANSACTIONS enabled and are using that feature? Or do you mean that you just aren't specifying BEGIN TRANSACTION? (i.e. creating an "explicit" transaction)?

If you are meaning the IMPLICIT_TRANSACTIONS feature, that simply issues the BEGIN TRAN and nothing more. So whether or not you are using IMPLICIT_TRANSACTIONS to issue the BEGIN TRAN or if you are doing that explicitly yourself, you are still responsible for issuing the COMMIT or ROLLBACK. And yes, Transactions can span batches, whether you are using GO to separate batches or executing multiple queries by hitting F5 at different times.

In either case -- IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN  TRAN statement -- nothing is automatically committed. The only thing that might be automatic in these two cases is a ROLLBACK if you close the connection without issuing the COMMIT or ROLLBACK first.

Without either IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN  TRAN statement, then each individual query is its own transaction, that is "auto-committed" and you need not worry about it. Meaning, 2 separate INSERT statements are two completely separate transactions.


mysql: select join table with latest record for both table This question has been asked at here as well.

i had 2 table, tableA and tableB looking like this:

tableA:

tableB:

i manage to get the latest record by auto increment id by using the following sql for tableA and tableB:

SELECT *
FROM tableA
WHERE id IN (SELECT MAX(id) AS id
             FROM tableA
             GROUP BY social_num)

SELECT *
FROM tableB
WHERE id IN (SELECT MAX(id) AS id
             FROM tableB
             GROUP BY social_num)

how to join them together, so that, i get the latest record on tableA and latest record on tableB link by social_num?
i tried following sql, it work:

SELECT *
FROM tableA
LEFT JOIN tableB ON tableA.social_num = tableB.social_num
WHERE tableA.id IN (SELECT MAX(id) AS id
             FROM tableA
             GROUP BY social_num)
AND
tableB.id IN (SELECT MAX(id) AS id
             FROM tableB
             GROUP BY social_num)

but how do i show all of other user from tableA togather as well, like Morris Q

DDL Information

CREATE TABLE `tableA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `tableA` */

insert  into `tableA`(`id`,`fullname`,`social_num`,`email`) values (1,'David J','1155','davidj@gmail.com'),(2,'Brian H','2244','brianh@gmail.com'),(3,'Hawkins M','6677','hawkins@gmail.com'),(4,'Marry K','7122','marry@gmail.com'),(5,'Utah O','9123','utah@gmail.com'),(6,'James L','1266','james@gmail.com'),(7,'David J','1155','david@hotmail.com'),(8,'Marry K','7122','marry@gmail.com'),(9,'Johnson E','1180','johnson@gmail.com'),(10,'Hawkins M','6677','hawkins@yahoo.com'),(11,'Morris Q','1461','morris@gmail.com'),(12,'David J','1155','david@yahoo.com');

CREATE TABLE `tableB` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `phone_num` varchar(255) DEFAULT NULL,
  `fav_color` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `tableB` */

insert  into `tableB`(`id`,`fullname`,`social_num`,`phone_num`,`fav_color`) values (1,'Brian H','2244','912-112-1231','blue'),(2,'Johnson E','1180','912-221-5512','red'),(3,'David J','1155','812-231-6125','green'),(4,'Utah O','9123','741-661-3125','red'),(5,'Hawkins M','6677','881-331-6612','blue'),(6,'James L','1266','934-513-5132','yellow'),(7,'Brian H','2244','785-513-9821','green'),(8,'David J','1155','960-231-5151','black'),(9,'Hawkins M','6677','135-661-3516','red');

Any help would be great, im using mysql 5.5

I hope that query is selfexplanatory enough.

SELECT a.*, b.*
  FROM
       (SELECT MAX(w.id) AS id
             , w.social_num
          FROM tableA AS w
         GROUP BY w.social_num
       ) AS maxa
  LEFT JOIN
       (SELECT MAX(z.id) AS id
             , z.social_num
          FROM tableB AS z
         GROUP BY z.social_num
       ) AS maxb  ON maxb.social_num = maxa.social_num
  JOIN tableA       AS a  ON a.id = maxa.id
  LEFT JOIN tableB  AS b  ON b.id = maxb.id
;

The only assumption I've made is that persons from tableB are the subset of persons in the tableA and there is no persons listed in the table B and not in the tableA. Otherwise the FULL OUTER JOIN not implemented by mysql should be emulated by UNION.


Send email when some users connect to SQL Server I am looking for a way to create a logon trigger in SQL Server.

Basically i need to do is when it matches some users or profiles on logon it send an alert e-mail.

I have found some things for Oracle but nothing for SQL Server.

Thanks.

Wouldn't it be better to store logins in a table and then provide a report for that table? Or perhaps, email a report that shows a list of logins for that day.

/* create test login */
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
GO
GRANT VIEW SERVER STATE TO login_test;
GO

USE AUDIT
GO

/* Creating audit table*/
CREATE TABLE audit.dbo.LogonAuditing
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)

GO

/* Create Login Audit Trigger */
CREATE TRIGGER connection_audit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

       DECLARE @LogonTriggerData xml,
               @EventTime datetime,
               @LoginName varchar(50),
               @ClientHost varchar(50),
               @LoginType varchar(50),
               @HostName varchar(50),
               @AppName varchar(500)

       SET @LogonTriggerData = eventdata()

       SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()

    INSERT INTO audit.dbo.LogonAuditing
     (
        SessionId,
        LogonTime,
        HostName,
        ProgramName,
        LoginName,
        ClientHost
    )
    SELECT
        @@spid,
        @EventTime,
        @HostName,
        @AppName,
        @LoginName,
        @ClientHost

END;

/* in case you want to drop the trigger
drop trigger connection_audit_trigger on all server
    go
*/


Best practice for creating tablespaces in Postgresql PostgreSQL version : 11.2
OS : RHEL or Oracle Linux 7.6 (Yet to be decided)

I am at the design stage of setting up a production database. In production, the DB will be around 300GB to 400GB in size.

This is what I have in mind. Please let me know if this is a good idea for a PostgreSQL production deployment.

I will have the following file system mounted with the following sizes:

/db ----> 50 GB
/pgdata ---> 500 GB

I will initialize the database cluster in the custom location /db/postgres/pg11/data .
And right from the start I will start creating tablespaces like this:

CREATE TABLESPACE orders_tbs LOCATION '/pgdata/<db_name>/orders_tbs';

...and place business objects in these tablespaces like this:

CREATE TABLE orders (id int, order_item text) tablespace orders_tbs;

Thank You Arkhena, CL

In the above case, I thought of creating a separate filesystem for datafiles (/pgdata) and keeping the config files and logs in /db.
So, my idea was bad.

Since I am in RHEL/Oracle Linux , by default, my $PGDATA will be /var/lib/pgsql/11/data .
But, I prefer to have my $PGDATA in a custom location like /db/postgres/pg11/data

Since the datafiles reside in $PGDATA/base directory , how about creating a disk layout like below using LVM?

A 50GB filesystem for $PGDATA's top parent directory /db and a separate 500 GB filesystem for $PGDATA/base directory ?

[root@localhost ~]# df -Ph
Filesystem                      Size  Used Avail Use% Mounted on
<output snipped>
.
.
/dev/mapper/VolGroup1-LogVol02  50G    23M  49.9G   1% /db
/dev/mapper/VolGroup1-LogVol04  500G  2.7M  499.9G  1% /db/postgres/pg11/data/base

I need to check with our Linux Admin on how the above disk layout can be optimally created without causing any storage bottlenecks.

Tablespaces in PostgreSQL exist for some really particular needs (and I doubt a less than 500 GB is in that case) and for SQL compliance.
If you plan to create tablespaces to store eventually everything on the same disk, please don't. If you plan to create tablespaces inside $PGDATA, please don't.

Tablespaces lead to more complex recovery operations (if you need one). You'll curse yourself later, trust me.

You'll find a lot of excellent advices in Christophe Pettus slides (PostgreSQL when it's not your job). The slide 27 is about tablespaces and why not using them.


String to timestamp in datastage 9.1.2 Apologies for asking a question that I suppose is very basic. I'm just starting out with Streams.
I'm trying to read a sequential  files containing a timestamp field and a value field, like this:

150601172623+0530
150601172041+0530

I want add this value to oracle 11g database as a timestamps. Is there any way in datastage 9.1.2 for this conversation...?

Result should be like below

150601172623+0530 -> 2015-06-01 05:26:23 PM
150601172041+0530 -> 2015-06-01 05:20:41 PM


Nutritional Information Data Model I'm trying to create a model for food and it's nutrition facts.

My criteria is:

A food can have multiple servings.
Multiple servings (of a food) can point to the same nutrition facts. (For example: if there are 3 serving sizes for a food, then 2 of them point to the same nutrition facts row, but the third serving size points to a different nutrition facts row.)

Additional information

My thinking why multiple servings can be be linked to the same nutrition facts: On a nutritional label, the serving size can be written like: 2 cookies (50g). I wanted to be able to separate the two sizes (cookies and grams), but have them connected to the same nutritional profile, should they need to be changed.

The second point is where I'm kind of struggling.

This is what I have so far, however, the serving_sizes table seems weird and is perhaps there is a better way. These are my concerns:

I think I want the servings table to have an auto-incrementing ID column because I want to record in another table that I, for example, ate cookies with a serving size of 50 grams. I also realize that size serving_sizes ID would be unique since it is a PK, I could just record the serving size ID, as that uniquely identifies the relationship. (Perhaps that means something is incorrect?) Also, given that this is a many-to-many relationship, is an auto-incrementing ID correct here?
How do I prevent the nutrition_facts_id in serving_sizes from being used by another food_id?

Note: I haven’t discussed this scenario with a domain expert since this is a personal project/exercise. However, I have spent a significant amount of time thinking and doing research about this domain.

If your intention is that you want to have multiple equivalent units of measure, all of which share the identical nutrition information, then you should flip things around and make nutrition_facts either a child of food directly.  The serving_sizes would then be a child of your nutrition facts, not of food (except as a grand child through nutrition facts.  Something like this:

food -+-----< nutrition_facts -+-----< serving_sizes

This would let you have multiple sets of nutrition facts, say for "box of cookies" and "two cookies" each stored only once, with these units of measure stored as either cookie units or equivalent grams.

If you only want to have one set of nutrition facts... such as would appear on a nutrition label, then you have a 1:1 between food and nutrition facts, which gives you the option of moving the columns from nutrition_facts into the food table as an alternative design.  This may or may not appeal to you, depending on your design inklings.


Selecting row by field and exact list of join id I have 2 tables. For simplicity, first table Parent contains only id and name. Other table Child contains id and parent_id.

There are going to be many parent rows with the same name. They will have different childrens, so I want to be able to select a parent row only if it has childs from the list I provide.

Let's say I want a Parent with name 'test' only if it has Childs with id (223, 224, 225).

So far I achieved this with following query:

SELECT p.*, GROUP_CONCAT(c.id) as childs
FROM `parent` p
    INNER JOIN `child` c ON p.id = c.parent_id
WHERE p.name = 'test'
GROUP BY p.name
HAVING childs = '223,224,225'
ORDER BY c.id

However I think it's not a good solution if it uses id concatenation

You can apply conditional aggregation

HAVING sum(case when c.id in (223,224,225) then 1 else -1 end) = 3

This assumes that those ids are unique.


I need a way to give a sql authenticated user read access to a windows directory A SQL-authenticated user SBA_USER is selecting from a View which uses Linked Server which points to an Excel spreadsheet in the I:\Folder directory on a windows server.
Problem I have is that the access to the directory is controlled by Windows and the Login SBA_USER is not a Windows account.
I need a way to give SBA_USER read access to the directory I:\Folder. Please suggest.

One thing you could try is to create stored procedure that would select from a View
When creating stored procedure, use EXECUTE AS clause and set it to a Windows-authenticated account (grant all necessary permissions to this account, required to read from I:\Folder)
Grant execute on procedure to SBA_USER
This way when SBA_USER  executes procedure, procedure does select from View, from behalf of the Windows-authenticated account


Best Approach for PII and Databases PII, or Personally Identifiable Information, involves information that may include the following, among other data:

First Name
Last Name
Email Address
Phone (any of them)
Billing Address
Shipping Address

Security researchers and legislation are recommending we encrypt the data. In my case, I'm using PHP7 and MariaDB. So, I can store encrypted PII using the latest recommended and battle-tested encryption APIs. However, now when I need an admin system, I need to be able to:

Sort data by PII table columns
Do case-insensitive partial keyword searches

Unfortunately, I'm not clear on how to do that smoothly with encrypted data stored in MariaDB (and which might also use PHP7).

Typical implementations that I'm familiar with, dealing with PHI (protected health information), which has stricter rules than simple PII, use these technical components:

Data-at-rest encryption, which includes either database-level encryption where supported by the DBMS or disk-level encryption, as well as backup and archived transaction log encryption.
Data-in-transit encryption, which essentially means TLS or IPSec for all network links.
Encryption key management system.
Row- and column-level access control. This should be implemented at the database level if the DBMS is capable of it, or in the application layer otherwise.
Network access control through explicit whitelisting (all network paths and ports are locked down by default and only open where needed).
Security monitoring of network traffic, OS and database logs for unusual access patterns.
Audit logging of all access paths, both at the database level (using database wire protocol capture if the DBMS has no native audit facility) and at the application level.
Secure erasure of data beyond its retention period.

This should cover most compliance requirements when implemented properly and augmented by the necessary organizational measures (identity and access management, separation of duties etc.)

The corollary of the above is that MySQL/MariaDB may not be a good choice for your use case.


Oracle home is missing I have an Oracle 12c database running in OEL 6.. yesterday due to some OS related issues, my Oracle home software mount-point got erased completely.

The only thing that is left is the pfile and data files. I have got that.
I do not have backups as well.

What are the options I have got? If I just install the New Oracle home and start with the pfile and with the data files, will I be able to start the database as it is?

With only the pfile and datafiles, you will not be able to open the database "by the book". You can recreate the lost controlfiles with a CREATE CONTROLFILE statement (specifying the remaining datafiles), but the next step would be a recovery, using the redo logs, which you do not have.

If you want to try to salvage some data from the database, you can open it without recovering. After you have created the controlfile and your database is in MOUNT state:

alter system set "_allow_resetlogs_corruption"=true scope=spfile;
startup force mount
alter database open resetlogs;

This is not guaranteed to work, the database may remain open or crash immediately/randomly depending on the scale of inconsistency of the datafiles.


Simple Query intermittently returning 0 results I'm having a strange problem and need help troubleshooting.

if I run this query on my gallery table:

SELECT   *
FROM     `gallery`
WHERE    awarded = 1
ORDER BY awarded_date DESC LIMIT 29 OFFSET 0

it works perfectly, but then randomly return 0 results, for a few seconds and then continues working.

I could be wrong but i think i have narrowed the problem down to the 'awarded' column, as other queries of the same database seem to work without this issue.

the awarded column is an int with value 1 or 0

any thoughts on what could be causing this?

Thanks.

Improve the situation by adding a composite index:

INDEX(awarded, awarded_date)

Is there some other process that is doing bulk changes to awarded?  Perhaps not transaction-safe?


mysql partition enabled or not? When I run show variables like 'have_partitioning'; it return empty record. When I run show plugins I got this result  partition | ACTIVE | STORAGE ENGINE|NULL    | GPL| My MySQL version is 5.6.29 and I'm running it in CentOS. What I have to do to get Yes when I run 'show variables like 'have_partitinoing

have_partitioning variable is removed from MySQL version is 5.6.29. That's why it is not showing.Instead of it you can use show plugins for knowing partition is enabled or not.If status value is Active then it is enabled.You can also disable partition by adding skip-partition=True while starting mysql server.


MySQL select with between optimization I got issues with some geo IP query. So basically here is create code of table:

CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `begin` (`begin`),
    INDEX `end` (`end`),
    INDEX `code` (`code`)
)

And query:

SELECT * FROM `geo_ip_city` USE INDEX ( `end` ) WHERE `begin` <= 2523596988 AND `end` >= 2523596988 LIMIT 1

Table size is about 4.5m records.

This query runs when all other criteria doesn't work, but still on peak times quite often.

In worst case scenario (where nothing found) it executes for 1.9444 seconds.

Here is explain:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  geo_ip_city range   end end 8   NULL    2297506 Using index condition; Using where

So when i have alot of requests for this feature, my servers going crazy.

Question:

Can I do something with this table or query to increase performance (maybe partitioning or complex keys)? Or should I watch in some other way?

Your conditions are searching for ranges. Now imagine you're looking in a dictionary and looking up all words where the first letter is "greater" than A. Of how much use is the index? You want to narrow the search range down as much as possible. MySQL most of the time can only use one index per table. Combine those begin and end indexes.

CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `idx_begin_end` (`begin`, `end`)
);

I also removed the index on code because it's also pretty much useless. It consists of two letters. Assuming there are no special characters/symbols included, this leaves you with 26*26=676 possible values for this column. That's a selectivity of 676 / 4.500.000 = 0.0001. You want to have the selectivity as near to 1 as possible.


Migrating old SQL Servers I found myself in a rather complex situation and need some advice...

We are moving our complete environment to a new Server and virtual servers.

I have reached a point where i found out that some of the old Servers (2000 and 2003) that I wanted to dispose contain Databases which are required for some of our (old but essential) software packages. These contain hardcoded data about these old servers and cannot be altered, so i'm stuck with them.

I wonder if there is a way to create a virtual name/path or sth on the new server, so i could move the old databases there, but while this is done, the software still needs to recognize them by their old name. For example - old database in on old server STHSRV1/name1 or STHSRV2/name2, and i want both of these to be on the new server, but accessible through their old names.

Is this even possible?

Thanks everyone

Would creating an alias be any use to you?


How do I create a Mongo config file to allow access to http on port 28017 Well I want to configure the mongo server to allows access for http.

I put this on my mongo config file:

port = 28017
rest = true

And when I restart my mongod service and try to access I got this message on the browser.

  It looks like you are trying to access MongoDB over HTTP on the native
  driver port.

I check my logs:

cat /log/mongod.log
2015-08-07T18:07:27.430+0000 [initandlisten] MongoDB starting : pid=20333 port=28017 dbpath=/data 64-bit host=ip-x-y-z-d
2015-08-07T18:07:27.430+0000 [initandlisten] db version v2.6.10
2015-08-07T18:07:27.430+0000 [initandlisten] git version: 5901dbfb49d16eaef6f2c2c50fba534d23ac7f6c
2015-08-07T18:07:27.430+0000 [initandlisten] build info: Linux build18.nj1.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-08-07T18:07:27.430+0000 [initandlisten] allocator: tcmalloc
2015-08-07T18:07:27.430+0000 [initandlisten] options: { config: "/etc/mongod.conf", net: { http: { RESTInterfaceEnabled: true, enabled: true }, port: 28017 }, processManagement: { fork: true }, security: { authorization: "enabled" }, storage: { dbPath: "/data" }, systemLog: { destination: "file", path: "/log/mongod.log" } }
2015-08-07T18:07:27.434+0000 [initandlisten] journal dir=/data/journal
2015-08-07T18:07:27.434+0000 [initandlisten] recover : no journal files present, no recovery needed
2015-08-07T18:07:27.778+0000 [initandlisten] waiting for connections on port 28017
2015-08-07T18:07:27.778+0000 [websvr] admin web console waiting for connections on port 29017

I refresh the browser and the logs show me that:

2015-08-07T18:24:09.246+0000 [initandlisten] connection accepted from 181.196.26.130:64683 #24 (1 connection now open)
2015-08-07T18:24:09.246+0000 [conn24] end connection IPAddress:64683 (0 connections now open)
2015-08-07T18:24:09.493+0000 [initandlisten] connection accepted from 181.196.26.130:53343 #25 (1 connection now open)
2015-08-07T18:24:09.493+0000 [conn25] end connection IPAddress:53343 (0 connections now open)

So If the log says that it waiting for connection on that port give me a respond but I can't see the http admin view, any idea or tips?

You're on RHEL6 or a derivative from your kernel version 2.6.32-431.3.1.el6.x86_64. The default firewall only allows ssh incoming. You'll need to poke a hole in your firewall. Look here for firewall tips:

http://wiki.centos.org/HowTos/Network/IPTables


Django Multi Table Inheritance and Preserving Child and Child History Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if the bank and restaurant tables don't have the same pk as place, I would think the answer is yes. But I also know that unless you put parent_link=True on it, erasing the child row automatically deletes the parent row.

My use case is to preserve the history of Restaurant despite it’s having now become a Bookstore, and if a Restaurant is simultaneously a Bookstore, to be able to keep both places in my db.   Is the best way to do this with a fk to each of the bookstores, restaurants and hardware stores instead of either a OneToOne or multi table inheritance? Or is there some other way I’m not aware of? This has to be a solved problem, but so far I haven't found it. I'm currently looking at NFL databases because players can be on more than one team, (albeit not at the same time), and they have a recorded history of their team and individual stats - to see if I can hack those into what I want.

I'm even willing to consider an ArrayField or Hstore. I'm on Postgres 9.4 and trying to maintain 3NF. All wisdom accepted. Thanks.

I suggest you have a look at this answer.

Use deletion.Collector with manual collection:

from django.db.models import deletion

collector = deletion.Collector(using=minor._state.db)
collector.add([minor])
collector.delete()


Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria? I have a table
create table r_prot_search(
    openprot_release_id integer,
    annotation_group_sig text,
    dataset_ids integer[]
);

With a composite index:
create index my_index on r_prot_search using gin(
  openprot_release_id,annotation_group_sig,dataset_ids gin__int_ops
);

The table contains a huge number of rows, and the following query is not filtering using the criteria on column "dataset_ids"
explain
  select count(*)
  from r_prot_search q
  where q.openprot_release_id = 75
    and q.annotation_group_sig = '117:118'
    and 195 = any (q.dataset_ids);

Aggregate  (cost=2211461.49..2211461.50 rows=1 width=8)
  ->  Bitmap Heap Scan on r_prot_search q  (cost=9619.83..2211459.47 rows=810 width=0)
        Recheck Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))
        Filter: (195 = ANY (dataset_ids))
        ->  Bitmap Index Scan on idxps_ds_2_15  (cost=0.00..9619.62 rows=837562 width=0)
              Index Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))

Any idea how to improve performance ?

It turns out the index gets used with the following array operator:
q.dataset_ids @> ARRAY[195]


Foreign Key to Multicolumn Primary Key? I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried to create tables with query below, I get this error: "there is no unique constraint matching given keys for referenced table 'category_product'". I don't know what is wrong with this design or logic.

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id)
);

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id),
    CONSTRAINT FK_PRODUCTS FOREIGN KEY (id) REFERENCES category_product(cat_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id),
    CONSTRAINT FK_CATEGORY_PRODUCT FOREIGN KEY (id) REFERENCES category_product(product_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

So what kind of structure I need to achieve what I am trying?

You need FKs in category_product table, not in categories and products:

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id)

);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id),
    CONSTRAINT FK_CATEGORY_PRODUCT_CATEGORY FOREIGN KEY(cat_id) REFERENCES categories(id)
    ON DELETE CASCADE,
    CONSTRAINT FK_CATEGORY_PRODUCT_PRODUCT FOREIGN KEY(product_id) REFERENCES products(id)
);


Can you combine database mirroring with AlwaysOn Availability Groups? I assume the answer is "no" but the SQL Server Management Studio 2012 GUI implies that you might be able to. In other words, if you right-click on a database that is an AlwaysOn Availability Group primary replica, you are given the option to mirror it (Tasks --> Mirror...).

Is that just a SSMS GUI 'feature'..? I've experimented by trying to mirror the database to a different instance (i.e. an instance that is not an availability replica) and the GUI lets me fill in all the necessary details and takes me right up to the point where you choose your mirroring type (High Performance or High Safety) and click Start Mirroring. An error message appears saying that the database is already enabled for mirroring (presumably because it is in an AlwaysOn availability group).

Probably I am the only person daft enough to attempt this but I'd welcome a confirmation that indeed it is impossible to seperately mirror an AlwaysOn availability group database. (I can't find any confirmation either way when Googling).

By the way, (just by way of background), in case you wonder I don't just set up another AlwaysOn Availability Group secondary replica (instead of attempt database mirroring), the issue is that my intended mirror instance is another instance on the same Windows Server Failover Cluster (as my AlwaysOn Availability Groups primary/secondary instance). It transpires that you can’t have more than one availability replica (per availability group) on a single WSFC cluster server node. This is the precise error message:

SQL Server instance 2 is installed on the same computer as server instance 1, which is already selected for this availability group. Each Windows Server Failover Clustering (WSFC) node can host only one availability replica per availablity group. Specify a server instance on a different WSFC node.

You are correct, it is impossible to mirror a database that is part of an Always On Availability group.  You can consider logshipping.

Ref:

https://blogs.msdn.microsoft.com/sqlalwayson/2010/12/08/alwayson-availability-group-and-database-mirroring/

  "Does AlwaysOn Availability Group replace Database Mirroring going
  forward?” The short answer is we recommend that you migrate from the
  mirroring configuration or even mirroring and log shipping
  configuration to using Availability Group. Database Mirroring will
  still be available in the Denali release but will be phased out over
  subsequent releases. Log Shipping will continue to be available in
  future releases.

For the error you are getting read this document especially section: Windows System Requirements and Recommendations.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability#PrerequisitesForAGs


Select rows where smaller row does not exist Consider the following table data:

ID NAME
1 Kshitiz
2 Aman
5 Aakash
6 Manjeet

Here there are records missing between 2 and 5. What query can I use to select row 5 in this table?

Oracle supports LAG so you should be able to do something like.

SELECT T.ID,
       T.NAME
FROM
(
SELECT ID,
       NAME,
       LAG(ID) OVER (ORDER BY ID) AS PREVID
FROM YourTable
) T
WHERE ID > PREVID + 1;

To compare the Id with the previous one and only return ones with a gap greater than 1.

(Untested as SQL Fiddle is currently returning errors when I try and create an Oracle Fiddle)


SQl server 2008R2 Performance issue after SP3 update We have recently applied SP3 KB2979597 and Security Update Patch KB4057113 on Sql server 2008R2 but after that our couple stored procedures runs slow, it's taking almost 4 to 5 hours, but normally it was running under 5 minutes.
Any one has idea that SP update causing any issue?
We have applied earlier same SP and security patch on test server and we didn't have any issue.
Any quick guidance really appreciate.
Thanks for your help!

Any one has idea that SP update causing any issue?

The security KB (KB4057113) is for the spectre / meltdown group of CPU security attacks.  They have been known to cause some performance slowdowns, but nothing that drastic.  Still, I thought it would be worth mentioning in an answer.
You may want to install the latest operating system patches to make sure you have the latest and greatest changes on that side of things (the initial patches addressed the security flaw, while some subsequent ones addressed performance).
See this Q&A on the site for more information on what you need to do:
As a SQL Server DBA, what do I need to know about the meltdown/spectre vulnerabilities?
Other than that, you should get execution plans for the slow procs from before and after the update, and compare them.  The more likely culprit is a plan change, which could be based on a number of factors (often unrelated to the update, like parameter sniffing).  Feel free to share them here to get specific help with them.


Disadvantage of Changing database schema - Insert or delete new columns/tables in middle As a read in lot of links , for e-commerce shopping sites, as it may require to add lot of attributes in regular basis, is it okay if we go with Flat tables ?

Whats the disadvantage of changing database schema or inserting/deleting new columns & tables in middle of the project ?

also Is there any problems if we keep lot of NULL values in database ?

As i heard EAV is Evil , I want to create shopping site with flat tables....

The foundation of your system is your database.  If you change your database schema then you also have to change your code.  That means that keeping a stable data model helps to reduce the amount of rework you need to do in your code.

Having said that, in the real world requirements change.  It's a good idea to invest some time up front to get your data model as good as it can be.  After that, the real world will intrude and you'll have to modify your database schema.

With careful planning, you can save yourself a lot of time reworking your code when easily anticipated changes happen.

In terms of Null values, there is nothing wrong with them in and of themselves.  However, if you find yourself with a table that has a lot of Null values, it could be a sign that your design is not properly normalized.  In a transactional processing system like an e-commerce site, you should start with a database design in third normal form (3NF) and only denormalize with great care and deliberation, and only when necessary.  This will help to keep your data quality from degrading.

EAV should be avoided as a general rule, although there are situations where it is a sane choice.  I've argued here that online product catalogs is one of those situations.


How to set the language of a doc to a DB field Is there a way to set the language of a document body to a value fetch from the DB in SSRS? I tried with a simple expression and I got this error. I wonder what the best workaround is

I think I found a work around for you.

Set up a new dataset to pull back the language value (let's say language_dataset into column language_value).
Create a parameter, let's say called language.  Set it to internal and set its default value -> Get values from a query and use the dataset and value you just set up.
In the Report properties, Language -> expression and set the expression to =Parameters!language.Value.

Let me know if you need more details.