Indexing Strategy for the query performance I have a question on indexing strategy as i am trying to do indexing for a database.

I understand the basic indexing strategy which are:

Clustered index on PrimaryKey
Add Non Clustered index for other columns as per requirement.
Should have indexes for WHERE/JOIN/GROUP BY/ORDER BY columns
Covering indexes for columns to avoid keylookup.
Check the workload and avoid adding too many indexes
Index Foreign Keys
Index any other unique keys etc..
I still have some questions and i couldn't find an exact answer to my questions.

Here is an example of a table that i have.

USE [DEMODB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee_Header](
    [Id] [uniqueidentifier] NOT NULL,
    [PageId] [uniqueidentifier] NOT NULL,
    [Dept] [varchar](256) NULL,
    [DeptName] [varchar](256) NULL,
    [SubUnit] [varchar](256) NULL,
    [ShiftType] [varchar](256) NULL,
    [ReportDate] [varchar](256) NULL,
    [ReportTime] [varchar](256) NULL,
    [AccessNumber] [varchar](256) NULL,
    [AccessOnAfterDate] [varchar](256) NULL,
    [AccessOnAfterTime] [varchar](256) NULL,
    [EmpBand] [varchar](256) NULL,
    [WorkSite] [varchar](256) NULL,
    [Location] [varchar](256) NULL,
    [Location_FieldValue] [varchar](256) NULL,
    [Location_Description] [varchar](256) NULL,
 CONSTRAINT [PK_Employee_Header] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

As you can see [Id] is the primary key here.[PageID] is the foreign key and it is not unique.
There are few queries which uses whereclause on `[Dept],[SubUnit], [ShiftType] and [ReportDate]'.

One query displays columns  [Dept] and [ShiftType] and other query displays [Dept],[ReportDate],[ReportTime],[EmpBand]

I am confused as to how to create NonClustered indexes with Include
columns so that same indexes can be used to both queries Or
should i create separate clustered indexes for where each columns
then create a separate single NonClustered index with PrimaryKey
    column as unique column and all the display columns in the include
    section.
Should i just keep the display columns in the keycolumn section?

Most of the examples available shows querying to a single table with an Id and one or more select column.
Can someone help me to understand how you approach similar situations?

I created two indexes as shown below.

First one is for the where clauses.I have all the where clause fields added as key columns in this non clustered index

CREATE NONCLUSTERED INDEX [ix_Employee_Filters] ON [dbo].[Employee_Header]
(
    [Dept] ASC,
    [SubUnit] ASC,
    [ShiftType] ASC,
    [ReportDate] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The second one is for the select columns.Some of the select columns are already part of the first index so i have only two columns that needs to be covered.
So i added them as Include fields with Id(Primary key field) as the key column.

CREATE NONCLUSTERED INDEX [ix_Employee_Include_Fields] ON [dbo].[Employee_Header]
(
    [Id] ASC
)
INCLUDE (   [ReportTime],
    [EmpBand]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Still when i run and see the execution plan, i get the message that there is a missing index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Employee_Header] ([ReportDate])
INCLUDE ([Id],[Dept],[ShiftType],[ReportTime],[EmpBand])

I don't understand why query optimizer is suggesting to a date filed as key field and other fields in the include section.
I already have a Nonclustered index with ReportDate among other where fields.

Appreciate for sharing your approach.But can anyone answer/share your perspective on the specific example mentioned?

For a general-purpose or OLTP design, the initial index design should be more conservative:

Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).

Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.

Then additional indexes as needed for optimization of the actual workload.  These can be indexes with included columns, filtered indexes, non-clustered columnstore indexes, or indexed views on a case-by-case basis.


Transaction log exploding during index reorganization Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

My client is running SQL Server 2005 x64 Standard on a beefy server with 16 GB of RAM, and separate drives for data, log and temp.  Daily backups include log backups.

Their accounting system (Accpac) database is around 45 GB and has been running on this server for the past 3 years with no problem.

About 3 or 4 months ago, during weekly maintenance plans that do an integrity check, index reorganizations, and update statistics, the log file ballooned to twice the size of the database and the job failed due to a disk full error.

At first, I thought the backups were not running so I dumped the log. I know I should not do this, but I didn’t really have any choice.  Then, I began monitoring the nightly backups.  All was good until the next index reorganization occurred.  The same problem occurred with the log running out of space and I realized it was happening during this task.

I moved the log files to the db disk and the db files to the log disk since the db disk was larger.  I wanted to see if I could at least get the job to finish by giving it more room.  Now it ballooned to over 3 times the size of the database and again filled up the disk.

For now, I have just turned off the weekly index reorganize task which is not really a good long term solution.

I'm just using the reorg index task that is in the maintenance plan designer GUI.  I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this.

Also, I do have "compact large objects" checked but assuming this is for blob data?  I don’t believe there are any BLOBs in this database.

Oh, I'm a developer wearing a DBA hat, so be gentle with me.

I forgot to mention recovery model is "bulk" vs. "full" - I believe this was set by the package vendor.

One thing to consider is to do more frequent transaction log backups. When you do a transaction log backup the log is truncated and and the space can be reused.


Datetime columns in sys.dm_db_mirroring_connections are an hour behind I have database mirroring set up on my SQL Server instance.

If I run SELECT * FROM sys.dm_db_mirroring_connections I get information about the mirroring connections, however, all the time columns in that table (connect_time, login_time, last_activity_time) are all an hour behind what they should be.

For example, last_activity_time is currently showing 2019-06-06 15:07:30.444 but the when I do SELECT GETDATE() I get 2019-06-06 16:07:48.324 (obviously a few seconds elsapsed between the two) which is the correct time now.

Where does this DMV get it's times from and how can I ensure they use the correct time?

Since it is presently summer time in the Northern hemisphere, it's likely that you're comparing winter time to summer time, which would explain the 1 hour difference.


Searching all Stored Procedures for an update of a specific column I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a unique code to help trace updates back to the code that caused them).

So I know someone is updating, for example, TerminalId in the Transaction table. How would I answer this?

  Show me all the stored procs that have an update statement on the Transaction table that explicitly change the TerminalId

Seems like this would be beyond any RegEx that would be easy to write.

Some updates statements are in this format, where the table name is after FROM:

update a
set
    ArrivalDate = @ArrivalDateLocalTime,
    LastUpdate = SYSDATETIME(),
    LastUpdateRef = 'REFGFI168',
    LastUpdateBy = @UserID
from Flight a where etc...

Do you have many procedures that getting the rough net and then eliminating any false positives manually would be extremely objectionable?

SELECT s.name, p.name, m.definition
  FROM sys.procedures AS p
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
WHERE LOWER(m.definition) LIKE '%update%transaction%terminalid%'
   OR LOWER(m.definition) LIKE '%update%terminalid%transaction%'
   OR LOWER(m.definition) LIKE '%with%transaction%update%terminalid%';

The last case to cover this form:

;WITH x AS (SELECT ... FROM dbo.Transaction)
UPDATE x SET TerminalID = ... FROM x INNER JOIN ...;

I don't know how much more reliable a "better" RegEx could be at identifying only those that truly update that column, and never identifying a false positive. In fact any real update statement that matched the above expressions (or any RegEx expression you would be likely to cook up initially) could be embedded in a comment or a string.

I'd suggest start simple. If the above yields way too many false positives then get more precise.


How to create via sql and detect via ERRORLOG SQL Server 2008 Deadlocks I am designing a monitoring program using HP Sitescope SIS.

The goal is detect deadlocks.

In doing so, I created a deadlock on an existing but nearly unused SQL Server 2008.

I used the following link:
http://panigorthi.blogspot.com/2012/01/how-to-create-deadlock-in-sql-server.html

Now the query has been running for over 23 hours. Would you then say the SQL Server cannot detect the deadlock?

How can I create a deadlock it can detect and kill?

What is an example output in the ERRORLOG when the deadlock is resolved?

Here is a link I read awhile back about retrieving deadlock information via extended events.
http://www.sqlservercentral.com/articles/deadlock/65658/

My guess is that if your query is still running then you have blocking not a deadlock.  Are you sure you went back to session 1 and ran the additional update statement?


PostgreSQL update column B with values generated from function that uses values from column A Pretty new to SQL.

I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | null             |
|  1.1.1.1 | null             |
|  4.4.4.4 | null             |
+----------+------------------+

I'm trying to create a function/script that will lookup the country ISO code for each clientip using a local install of the MaxMind GeoLite2 database and then update the corresponding clientip_country with the result to give:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | US               |
|  1.1.1.1 | AU               |
|  4.4.4.4 | US               |
+----------+------------------+

What's the best way to go about achieving this?

For example I'm able to retrieve the country ISO code of an IP using the following bash command:

$ mmdblookup -f /usr/share/GeoIP/GeoLite2-Country.mmdb -i 1.1.1.1 country iso_code | sed -e '/^$/d' -e 's/.*"\(.*\)".*/\1/'
AU

What direction should I be looking to? An SQL command that queries the GeoLite2 DB? Or a bash script that queries the Postgres DB and updates the clientip_country column with the results?

On @Akina's and @a_horse_with_no_name's advice I downloaded & imported MaxMind's GeoLite2 Country database (in CSV format) using the following:

create table geoip_blocks (
    network cidr,
    geoname_id bigint,
    registered_country_geoname_id bigint,
    represented_country_geoname_id bigint,
    is_anonymous_proxy bool,
    is_satellite_provider bool
);

copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;

create index geoip_blocks_network_idx on geoip_blocks using gist (network inet_ops);

create table geoip_locations (
    geoname_id bigint,
    locale_code varchar(2),
    continent_code varchar(2),
    continent_name varchar(255),
    country_iso_code varchar(2),
    country_name varchar(255),
    eu_member bool
);

copy geoip_locations from 'C:\tmp\GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

then ran an UPDATE to populate my clientip_country column with the appropriate 2 letter ISO country code using the clientip column data:

update
    client_info
set
    clientip_country = country_iso_code
from
    geoip_blocks
    inner join geoip_locations on geoip_blocks.geoname_id = geoip_locations.geoname_id
where
    network >>= client_info.clientip::inet
    and clientip_country is null;

Took a couple of minutes to update just over 100,000 records. Nice.


Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad effect on a production database.

My database server has 4 cores and I'm using SQL Server 2008 R2 SP3. I asked this question because I noted that its size is growing suddenly. I'm afraid that it may make the hard drive full.

I frequently run below query to see if there is page contention or not. I always see an empty result, which indicates that there is no page contention, therefore I did not add additional data file to tempdb.

(SELECT session_id AS [Session],
wait_duration_ms AS [WaitTime(ms)],
resource_description AS [Type]
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
AND (resource_description LIKE '2:%:1'
OR resource_description LIKE '2:%:2'
OR resource_description LIKE '2:%:3')

My tempdb is located on the local C drive. I have only one data file for it.

Multiple things to note here. tempdb is supposed to have same number of data file as many cores are available on the server(up to 8). So, in your case it is supposed to be 4 data files and they are should be of same size so that you won't have allocation contention. This is addressed by KB 2154845.
Below is explained by Mr. Brent Ozar for configuring tempdb:

configure one volume/drive for TempDB. Divide the total space by 9,
and that’s your size number. Create 8 equally sized data files and one
log file, each that size. Presto, the drive is full and your TempDB is
configured for easy performance.

I would stick to below script to find my bottlenecks for wait statistics:
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

I am not very sure if this will work on SQL Server 2008R2 as I don't have any environment with this version.
Regarding your main issue i.e. filling up of space at C drive and your only device of tempdb which is stored at the same drive can make your database corrupt as well as your operating system will stop working if all the space is used. So, I would suggest you to add three more data file of equal size and keep them at different drive and at the same time disable autogrowth option from the existing device as shown below:

Check how much of data is really used in tempdb and try to shrink that to an extent(its not a good option in normal scenario however you don't have many option since we don't want to reboot service now).
After above steps are done and you manage to survive during peak hour, you may plan to get downtime of your system for few minutes and would need to reboot your SQL service in order to move 1st data file from C drive to other drive.
You may use commands given at below links and follow steps to move data/log file of tempdb:
https://blog.sqlauthority.com/2016/06/26/moving-tempdb-new-drive-interview-question-week-077/
https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/
How to Move TempDB Files to a Different Drive or Folder?
Hope above helps.


How do I CAST a date in a subquery? Currently getting an error: Conversion failed when converting date and/or time from character string I have a column called Source in my table called TPR. The Source column is of datatype varchar and contains text like January 2020 and Q3 Completed. When I run a query that only extracts the date-like fields to CAST as dates like so:
SELECT CAST(Source AS DATE) AS Source
FROM TPR
WHERE Source NOT LIKE 'Q3 Completed`

I get the appropriate results. When I place the query above in a subquery to act as a filter like so:
SELECT CaseNumber
FROM TPR
WHERE Source =
     SELECT CAST(Source AS DATE) AS Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed`

I get the following error:
Conversion failed when converting date and/or time from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.

I've also tried the following but it too failed:
SELECT CaseNumber
FROM TPR
WHERE CAST(Source AS DATE) =
     SELECT CAST(Source AS DATE) AS Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed`

I don't understand why this is failing.

Use a CTE to get the wanted rows and then do the conversion
If the strings are not a correct format like seen below, you will get an error.

CREATE TABLE TPR(CaseNumber int,Source varchar(100))
GO

INSERT INTO TPR VALUES (1,'Q3 Completed'),(2,'2021/01/01')
GO

WITH CTE AS (
     SELECT Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed' )
     SELECT CaseNumber
FROM TPR
WHERE Source IN (SELECT Source FROM CTE)
GO

| CaseNumber |
| ---------: |
|          2 |

db<>fiddle here


How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"? When I run the following command I get an error, however one of my scripts requires it.

SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

Trivia: If you want to know what the HY00 stands for (a follow up I asked years later)....


MySQL : Why are there "test" entries in mysql.db? Recently, I posted an answer to a question about mysql.db.

Then, I got to thinking I should ask everyone this question:

I have noticed for years that upon installation of MySQL 5.0+, mysql.db is populated with two entries that allow test databases to be accessed by anonymous users.

You can see it by running this query:

mysql> select * from mysql.db where SUBSTR(db,1,4) = 'test'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

Are these entries in the mysql.db a security risk, and if so, why are they added by default to a new install?

UPDATE 2013-06-14 10:13 EDT

This morning someone downvoted my question, which I truly don't understand. In light of this event, here is why I took the time to make a rebuttal:

I installed MySQL 5.6.12 for a client this week in their Staging Cluster. I decided to check to see if this was still an on-going problem:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.10 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-14 10:10:13 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Guess what? This is still a problem even to this day !!!

MORAL OF THE STORY : Please check your mysql.db immediately after installation and remove anonymous logins and erase these test entries from mysql.db without delay.

Please note what MySQL 5.0 Certification Study Guide

say in its bulletpoints on Page 498 Paragraph 6:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

To get rid of those bad entries, run this please:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;

As @DTest mentioned in his comment to the question, you can also run mysql_secure_installation to this for you.

If an anonymous user can login to MySQL remotely, a simply disk attack can be launched to hurt the mysql installation. Here is an example:

USE test
CREATE TABLE rolando_tb (a int);
INSERT INTO rolando_tb VALUES (1);
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;

Run insert 30 times and you get a 7GB table

Imagine creating several of these tables in the test database
Imagine creating a Stored Procedure in the test database
The possibilities are endless as long as test and test_% exist in mysql.db

The seriousness of securing the mysql installation has not been fully documented by MySQL AB, and I don't think Oracle is interested in doing so today.

UPDATE 2012-02-18 16:45 EDT

It was suggested by @atxdba's comment that just running 'DROP DATABASE test;' should be the preferred method over touching mysql.db. Dropping the database named test simply removes the database that opens a conduit to a potential security hole.

Please take note of this query:

mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.09 sec)

Based on this, the following databases can be accessed fully by anonymous users:

test
test_db
test_001
test_1
test_data

While the following databases cannot be accessed fully by anonymous users:

testdb
test1
testdata
Test (Test is different from test in Linux-based systems, but it is still problem for MySQL running in Windows)

You will have to remember this subtle rule based on the mysql.db table. If you do not remember this, creating a test database named test or a database name whose first 5 characters is test_ will reopen the same type of security hole.

The most secure way around having to remember these things is to run these lines after an initial installation:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

then any database with any name can have a proper authentication setup. You can still run these two lines anytime.

UPDATE 2012-02-24 15:20 EDT

To openly demonstrate the danger of having anonymous users in mysql.db,
I would like to create a user that has only the usage privilege.

I will be using MySQL 5.5.12 on my Desktop

First, look at the mysql.db

mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.05 sec)

mysql>

According to this, any anonymous Joe can reach these databases.

I'll create a database test_mysqldb

mysql> create database test_mysqldb;
Query OK, 1 row affected (0.00 sec)

mysql> use test_mysqldb
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

Let's create an plain vanilla user called vanilla@localhost (no password)

mysql> CREATE USER vanilla@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR vanilla@localhost;
+---------------------------------------------+
| Grants for vanilla@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'vanilla'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

Next, from the DOS Command Line, let's connect to the mysql schema

C:\>mysql -uvanilla -Dmysql
ERROR 1044 (42000): Access denied for user 'vanilla'@'localhost' to database 'mysql'

C:\>

OK great. That's what I expected.

Next, from the DOS Command Line, let's connect to the test_mysqldb schema, create a table, and load it with numbers

C:\>mysql -uvanilla -Dtest_mysqldb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE rolando_tb (a bigint unsigned);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO rolando_tb VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM rolando_tb;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
+------+
16 rows in set (0.00 sec)

mysql> SELECT database();
+--------------+
| database()   |
+--------------+
| test_mysqldb |
+--------------+
1 row in set (0.00 sec)

mysql>

Did you see that? A user with USAGE privilege can create a table in a test database and fill it with data. This a clear and present danger. This is why I strongly recommend deleteing those test entries out of mysql.db to deter anonymous users from reaching test databases or accessing newly created test databases (via making a subfolder under the default datadir).

As a reminder, this is how you do it:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

UPDATE 2013-09-14 20:05 EDT

To demonstrate that the DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user=''; actually works, I ran this on MySQL 5.6.13 today:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.43 sec)

mysql> delete from mysql.db where LEFT(db,4)='test';
Query OK, 2 rows affected (0.04 sec)

mysql> select db,user,host from mysql.db2 where LEFT(db,4)='test';
Empty set (0.00 sec)

mysql>

Just as a public service announcement, please run

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

or just run mysql-secure-installation and put this potential danger to bed.


DB2 BOOLEAN type, EXISTS predicate and nullability strange behavior I'm using DB2 LUW v11.1.4.5,
It seems that EXISTS predicate, when FALSE returns NULL (is it documented ?)
In the following query on X is null is true, all the rest acts like FALSE is a synonym for NULL, but the metadata say all the columns are not nullable
(see dbfiddle)
with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

now with the following query every column is nullable, it shows that x really is NULL with EXISTS
with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
  union all
  select  'count' stmt,
    (select count(*) from sysibm.sysdummy1 where ibmreqd = 'N') > 0 as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

My question is : did someone know about it, it seems to me that's a problem on the server but do you know if it may come from the JDBC driver (using 4.27.25) ? Is the problem still there using later versions ?

An interesting question with the answer that is not so interesting: in this trivial example your EXISTS is optimized away, so an attempt to reference this predicate evaluation result is undefined. If you run db2exfmt (or another tool that can retrieve the "optimized" version of the statement), you will see that this is what gets executed in the end:
SELECT 'exists' AS "STMT",
   $INTERNAL_FUNC$() AS "X",
   $INTERNAL_FUNC$() AS "x is null",
   $INTERNAL_FUNC$() AS "NOT x",
   $INTERNAL_FUNC$() AS "x = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(x)",
   CHAR($INTERNAL_FUNC$()) AS "char(x)",
   $INTERNAL_FUNC$() AS "Y",
   $INTERNAL_FUNC$() AS "y is null",
   $INTERNAL_FUNC$() AS "NOT y",
   $INTERNAL_FUNC$() AS "y = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(y)",
   CHAR($INTERNAL_FUNC$()) AS "char(y)"
FROM (SELECT NULL
FROM (VALUES ) AS Q1
WHERE ('Y' = 'N')) AS Q2

This is likely Working As Designed™, because predicates, of which EXISTS is one, are not meant to be used in place of expressions. Db2 tries to do its best to turn predicate evaluation outcomes into values, but the optimizer has an upper hand.
Things work as you expect when you use expressions and predicates where they respectively belong:
with boolstmt as
(
  select  'exists' stmt,
    case when exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') then true else false end as x
  from sysibm.sysdummy1
 )


Query Processing at backend (I/O on disk) I have a table with 100k rows created on PostgreSQL 9.3

create table demo_bbb
(
  id numeric NOT NULL,
  code_bbb character varying,
  column_02 character varying,
  column_03 character varying,
  column_04 character varying,
  column_05 character varying,
  column_06 character varying,
  column_07 character varying,
  column_08 character varying,
  column_09 character varying,
  column_10 character varying,
  CONSTRAINT demo_bbb_pk PRIMARY KEY (id)
);

Here's my test (each query is run 3 times):

(1) select id from demo_bbb b ;         -- database query time: ~26ms
(2) select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
(3) select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
(4) select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms

Result: Average time of (4) < time of (2), (3)  (32ms < 45 ms)

Explain analyze:

(1) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=6) (actual time=0.008..22.088 rows=100000 loops=1)"
(2) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..38.702 rows=100000 loops=1)"
(3) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..32.098 rows=100000 loops=1)"
(4) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=232) (actual time=0.007..17.702 rows=100000 loops=1)"

I am curious to know what happened but I can not understand. Could you give me some explanations of how these queries did ?

Note: I traced these queries by using SQL Profiler (download from Enterprisedb)

Pretty much a WAG because you haven't shown EXPLAIN (BUFFERS, OUTPUT) data, but:

select id from demo_bbb b; -- database query time: ~26ms

If you're on 9.2 or newer this probably did an index-only scan on the unique index used to implement the primary key constraint. Given the latter times that's the most likely case; if it'd done a seqscan (as it would on older versions) you'd have got faster results from later queries.

select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms

These are likely to be doing seqscans, and possibly out-of-line TOAST fetching.

The different times will be partly due to caching effects, possibly partly offset by the need to read in more out-of-line TOAST data when you select more columns.

PostgreSQL uses two levels of caching - the operating system's disk cache, and its own shared_buffers. Both are block caches. It does not have a query result cache.

For more information see the PostgreSQL manual and the existing resources about PostgreSQL benchmarking, tuning, caching behaviour, etc.


Make SQL Server Agent retry failed script with Try/Catch I have a script that has a try catch in it so that if it fails I can roll back the transaction. It runs on a job scheduled through SQL Server Agent. But when it fails SQL Server Agent does not see that it failed, I'm assuming because I catch the error. I want it to still retry the job even if I catch the error. I put in the RAISEERROR command in my catch part of the script to try and throw the error up to SQL Server Agent but it did not work. Any solutions?

Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent

Did you set the job step to retry (Retry Interval and Retry Attempts)

Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?


SQL Server is configured to allow remote connections I use SQL Server 2012 and SSAS Tabular Mode (My Instance Name Is MSSQLSERVER2012T)

Then I download AdventureWorks Tabular Model SQL Server 2012 From This link

I open it with Visual Studio 2010 and configure my Instance Name like this picture :

When I deploy my project I have This picture :

But after one minute I get this error :

And when I click on Details Of Error I get this message :

OLE DB or ODBC error: Login timeout expired; HYT00;
A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books Online.; 08001;
Named Pipes Provider: Could not open a connection to SQL Server [2]. ; 08001.

A connection could not be made to the data source with
the DataSourceID of 'a16b8d9d-3811-4be1-91e7-a9d0a2df4d38',
Name of 'SqlServer localhost'.

And

But it not work for me???

In my SSAS one Database was created like this picture :

Your named Pipes connections are disabled, try fixing this


Excluding (or subtracting) multiple dateranges from a daterange in PostgreSQL I have a list of items which contain: product_id, period and availability.

The link below references my SQLFiddle Example.
I want to get the availability periods of each product for a given period, e.g:

List the availability periods for the period [2018-11-01, 2018-11-30] of the prod_id = 10.

It will display:

 1. [2018-11-01, 2018-11-01]
 2. [2018-11-03, 2018-11-03]
 3. [2018-11-06, 2018-11-07]
 4. [2018-11-10, 2018-11-31]

The operator difference of daterange does not support this type of differences and it returns :

ERROR: result of range difference would not be contiguous

I realized that it is not possible to get the suitable results using the operator with such data.Is there any link or another solution to select the products in this way?

Disjoint ranges.

What essentially you're asking to do is exclude a subset of a range. That creates a "discontinuous ranges" or "disjoint ranges" which can not be contained . It's better to see this with an int,

([1,10] - [3,7]) = ([1,2] + [8,10])

These two forms are the same, they're just different ways of writing it. This is simply not supported by the daterange type from the docs,

  The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be represented.

That's not to say you can't accomplish the same thing just explode the ranges and subtract out the values you don't want.

SELECT d::date
FROM generate_series(
  '2018-11-01'::timestamp without time zone,
  '2018-11-30'::timestamp without time zone,
  '1 day'
) AS gs(d)
WHERE NOT EXISTS (
  SELECT
  FROM test
  WHERE test.period @> d::date
    AND test.available IS FALSE
);

Note always do date-math including date-range generate with timestamp without time zone or you may encounter DST bugs


Enable Oracle Database options in an existing database I want to enable Oracle Real Application Testing option in my existing database, but I couldn't find the way to do that using Oracle Universal Installer (OUI) or Database Configuration Assistant (DBCA). Do I have to reinstall the Oracle software in order to enable the options that I didn't choose to install during Oracle software installation?

The version of the Oracle Database installed is 11.2.0.3.

No, you don't have to reinstall the Oracle software. According to the section "Oracle Database 11g Release 2 (11.2.0.1) New Features" in the Database Installation Guide 11g Release 2:

  Oracle Universal Installer no longer provides the custom installation
  option of individual components. Use the chopt tool, a command-line
  utility that is located in the ORACLE_HOME/bin directory, to configure
  the database options.

For example, to enable Oracle Real Application Testing, you would first shut down your database, and then issue chopt as follows:

chopt enable rat

For more information on how to enable and disable other database options with chopt, consult "Enabling and Disabling Database Options" section in the Database Installation Guide 11g Release 2.


How can I make a select statement get blocked? Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning.  I prefer only InnoDB tables.

To prepare the test, I created a table

CREATE TABLE `test`.`client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

and inserted 1,000,000 rows into the table.

Now I will begin the test.

In MySQL client #1, I deleted all the rows:

mysql #1> delete from client;

While the delete is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=1;
+---------+------+
| id      | name |
+---------+------+
| 1       | joe  |
+---------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.

Next I will try another test.  I insert 1,000,000 rows into the table.

In MySQL client #1, I update all the rows:

mysql #1> update client set name='Bill';

While the update is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=100;
+-----+------+
| id  | name |
+-----+------+
| 100 | joe  |
+-----+------+
1 row in set (0.00 sec)

mysql #2> select * from client where id=1000;
+------+------+
| id   | name |
+------+------+
| 1000 | joe  |
+------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.

So now my question, how can I demonstrate a SQL select statement getting blocked by another simple SQL delete or or update statement, using InnoDB tables?  Or does MySQL never have any blocking issues?

PS I am not trying to simulate two processes deadlocking each other, just one large update or insert blocking a select.

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

You can have UPDATEs and DELETEs blocked by SELECTs

How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

You can just lock the table before the SELECT and unlock it afterwards

LOCK TABLES table READ;
SELECT ... ;
UNLOCK TABLES;

You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the  MySQL Documentation

Give it a Try !!!


Postgresql - how to list indexes on a table that is partitioned? This command works for non-partitioned tables:

select * from pg_indexes

But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.

To drop an index, I need the original name.

How do I list all indexes on a table that is partitioned (with the name it was created)?

Workaround:

create or replace view vw_list_indexes
as
    select i.relname as indexname, t.relname as tablename, t.OID, idx.indisprimary
    from pg_class i
    join pg_index idx on idx.indexrelid = i.oid
    join pg_class t on t.oid = idx.indrelid
    where i.relkind = 'I';

select * from vw_list_indexes where oid = 'schema.table'::regclass;


Auto Increment a column that is not unique...? I've been thinking on this for a few hours, and I can't work out a viable solution - I can think of solutions, but they are clunky.

Using MySQL, I want to create an invoice table using auto increment for the invoice number. Pretty standard stuff. HOWEVER, my challenge is that I wish to have multiple companies generating invoices in this table, but all still with the unique invoice number that is auto incremented from the previous one.

For example:

Company A's previous invoice is 3010
Company B's previous invoice is 2144
Company C's previous invoice is 7889877

When Company A creates their next invoice I want it to be 3011. B to be 2145 etc.

I had the idea of doing a query WHERE company = 'B' ORDER BY id DESC then using PHP to increase that value++, then insert a new record with the invoice number inserted from PHP. In this case the 'id' field would be irrelevant to the actual invoice number, and just be a unique identifier.

Is there a better way?

That seems possible in MyISAM but afaik not using InnoDB:

CREATE TABLE `invoice_sequences` (
  `company_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`company_id`,`invoice_id`)
) ENGINE=MyISAM;

http://sqlfiddle.com/#!9/d135c/1 (if the fiddle returns an error, try again; it seems a bit slow sometimes).


SQL Server agent option in SQL Server Express I am using SQL Server 2008 Express. I don't have SQL Server Agent option in it. How can I enable it?

I have searched on internet, but couldn't find anything. Do I need to install anything?

You can't, as found under the 'Management Tools' section here: http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.100%29.aspx

If this is just for your development environment, you can pick up a copy of Developer Edition for about $50 US.  When you release, you will need a copy of Standard Edition or higher to be able to use the job agent.


Will creating reports on R using MySQL Database update my database on MySQL? I use MySQL to store my data. To analyze the data I want to be able to generate monthly reports on R. So far I was able to connect to MySQL using dbConnect but I want to make sure that none of the tweaking I do affects the database I have in MySQL. Below is the code I used to import the database/table to R.

install.packages("RODBC")
library(RODBC)
Data<-dbConnect(drv, username="<insert username here>", password="<insert password here>", dbname ="<insert database name here>")
Table1 <- fetch(dbSendQuery(Data, "select * from Table"),-1)

Using the fetch function I am able to load the table into R but I want to make sure that I am not changing it on MySQL at the same time. Would applying any operations, adding columns etc affect the original database in MySQL?

When you copy data from MySQL into rstudio (or your UI of choice), r pulls the data into memory.

When you then manipulate the data (by adding extra columns, for example), this happens entirely in memory & the source data (in your case a MySQL query, but it could just as easily have been from a csv file etc) is not modified in any way.

r is wonderful, but requires a slightly different way of thinking than SQL - hope you’re enjoying it.


how to "revoke" a "GRANT OPTION" from an mysql user/database? i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when the account where added. It looks like this:

GRANT USAGE ON *.* TO 'freg2'@'%' IDENTIFIED BY PASSWORD '*XXXXX';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, \
    REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, \
    LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, \
    CREATE ROUTINE, ALTER ROUTINE \
    ON `freg2`.* TO 'freg2'@'%' WITH GRANT OPTION;

I searched the net (and this site) for a solution on how to remove that "GRANT OPTION" thingy, without recreating the whole grant/account . so far without luck.

Is there really no way just to remove "this" privilege or right from that account ?

thanks in advance.


With Query return more than one line In this query I want to display the columns of partition and subpartition of a table. It works fine if the nom_tabla query get only one table, but when the select give us more than one table it doesn't works.

with nom_tabla as
(
  select table_name from dba_tables
  where table_name like 'TE%'
),
col_part as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_part_key_columns WHERE NAME in
  (
    select *
    from nom_tabla
  )
  GROUP BY name
  ORDER BY name
),
col_subpart as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_subpart_key_columns
  WHERE NAME IN
  (
    select * from nom_tabla
  )
  GROUP BY name
  ORDER BY name
)
select
  t.OWNER PROPIETARIO,
  t.TABLE_NAME as TABLA,
  ' - ' as PARTICION,
  NVL (pt.PARTITIONING_TYPE,' - ') AS TIPO_PART,
  ' - ' as SUBPARTICION,
  NVL(pt.SUBPARTITIONING_TYPE, ' - ') AS TIPO_SUBPART,
  NVL(t.tablespace_name,' - ') as TS,
  NVL((select * from col_part),'-') ,
  NVL((select * from col_subpart),'-')
  From dba_tables t, dba_part_tables pt
  where   t.owner not in
  ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
  and     t.owner=pt.owner(+)
  and t.table_name=pt.table_name(+)
  and t.TABLE_NAME in (select * from nom_tabla);

I think that the problem is in this section of the query because it return more than one record:

, NVL((select * from col_part),'-') , NVL((select * from col_subpart),'-')

I don't know how to avoid this problem.

Of course.

col_part as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_part_key_columns WHERE NAME in
  (
    select *
    from nom_tabla
  )
  GROUP BY name
  ORDER BY name
)

If nom_table has more than 1 row, the subquery returns more than 1 row as well.

This works:

   select
      t.owner as propietario,
      t.table_name as tabla,
      ' - ' as particion,
      nvl(pt.partitioning_type, ' - ') as tipo_part,
      ' - ' as subparticion,
      nvl(pt.subpartitioning_type, ' - ') as tipo_subpart,
      nvl(t.tablespace_name, ' - ') as ts,
      nvl(col_part.part_por, '-'),
      nvl(col_subpart.part_por, '-')
    from
      dba_tables t
      left join dba_part_tables pt on (t.owner = pt.owner and t.table_name = pt.table_name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_part_key_columns group by owner, name order by owner, name) col_part on (t.owner = col_part.owner and t.table_name = col_part.name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_subpart_key_columns group by owner, name order by owner, name) col_subpart on (t.owner = col_subpart.owner and t.table_name = col_subpart.name)
    where
      t.owner not in ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
      and t.table_name like 'TE%'
    ;


DynamoDB: How to model data that's shared and queried by all users? I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head around.

Let's say I have users in a table, with the user id being the primary key. Most information is specific to the user. I want to be able to communicate with my users so I want the ability to make announcements to them. These announcements are shared across all the users. I can store user specific information about announcements in their own attributes like read and unread announcements.

The problem (if it's not clear already) is that there is only one set of announcements but they will be queried by every user frequently, leading to an anti-pattern of DynamoDB and potential throttling.

My initial thoughts are to make k copies of announcements and label the keys announcement_copy_1, announcement_copy_2 ... announcement_copy_k, and then on the query to check for new announcements, I would randomly assign an integer 1-k to query the announcements. Each announcement copy would be the partition key and I would have sort keys with the date of the specific announcement, and attributes with the text and type of announcement.

I'm not confident if this is the best approach to this problem, or if I'm missing something. Also I am looking at going serverless with AWS Lambdas if that affects anything.

Thank you in advance for any suggestions or advice!

I'm intrigued as well.  :)  I have a use in mind that would likewise have userID as the PK.

Sounds like you could put an 'announcements.json' file in an S3 bucket, then have your application read announcements from that file.

S3 can handle 5,500 read requests per second, according to:
https://aws.amazon.com/about-aws/whats-new/2018/07/amazon-s3-announces-increased-request-rate-performance/


PostgreSQL replication with fsync disabled? I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL.

However, as part of my scheme to meet another requirement (that the in-memory database have somewhere to recover from when volatile memory is lost), I would like to stream or otherwise push writes to a replica. However, the PostgreSQL hot standby capability is based on the WAL. Clearly, I can't use this.

How could I achieve these goals using PostgreSQL features?

Thanks.

Turning fsync off does not prevent writes to WAL, it only prevents those writes from being explicitly synced.  So you can still use this to feed hot standby.  But if your database is so busy you need to do this, I wonder how well the hot standby can keep up with it.

Also, turning synchronous_commit off will get you much of the benefit without the corruption risk of turning fsync off.

Finally, anyone running such busy system should have the budget for some kind of nonvolatile write buffer, which would render fsyncs much less of a performance problem.


Create a view and populate 1 column with the extracted day-month from a epoch date from the main table I have this main table (year 2010):

  Serial   | Day_start | Data
-----------|-----------|--------
  1        |1262304000 | data1
  2        |  epoch    | data2
  3        |  epoch    | data3
  4        |  [...]    | .
  5        |  epoch    | .
  6        |  epoch    | .
  7        |  epoch    | .
  [...]    |  epoch    | .
  365      |1293753600 | .
  366      |  epoch    |

Where Serial corresponds to the day of the year.

Serial: 1 -> 1/jan/2010 -> 1262304000

I want to create a view, where the first column of this view extracts the day-month from the epoch of this table.

CREATE OR REPLACE VIEW year_2010 AS
 SELECT row_number() OVER (ORDER BY "MainTable".Day_start) AS index,
    "MainTable".Day_start,
    "MainTable".Data
   FROM "MainTable"
  WHERE "MainTable".Day_start >= 1262304000 AND "MainTable".Day_start <= 1293753600;

With this SQL I create a view using the column Day_start as Index. Each, for each year. This table contains 8 Years of data (that's why I do not use the "Serial".

How do I extract the day-month from the epoch and rebuild the view?
I can't find the function I would need from here:
https://www.postgresql.org/docs/9.1/static/functions-datetime.html

The generated view should be:

Index(date)| Data(double precision)
-----------|--------
  01-01    | data1
  02-01    | data2
  03-01    | data3
  04-01    | .
  [...]    | .
  06-11    | .
  07-11    | .
  [...]    | .
  31-12    | .
  01-01    |

The answer is, like you already mentioned in the PostgreSQL documentation. An example is in the reply of Postgres: how to convert from unix epoch to date?

select timestamp with time zone 'epoch' + Day_start * interval '1 second'
from   "MainTable";

or simpler (thanks to @a_horse_with_no_name):

select to_timestamp(day_start)
from   "MainTable";

Use the to_char(timestamp, 'DD-MM') to get the day and month. The result to put in your CREATE VIEW would be something like:

select to_char(timestamp with time zone 'epoch' + Day_start * interval '1 second',
               'DD-MM') AS index;
from   "MainTable";

If you want the whole date as DATE type then use:

select (timestamp with time zone 'epoch' + Day_start * interval '1 second')::date AS index;
from   "MainTable";


How do I duplicate constraints from one existing table to another existing table with an identical structure? I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the constraints to table_b.

All the constraints are regular foreign keys to the primary key of table_a and for the purposes of this question, we can assume that the data are the same in both tables, but I still don't want to drop and recreate table_b; I want to add the constraints independent of table creation.

Is there a generic way of doing this without having to be provided details of the tables' structure or the specific constraints of table_a?

CREATE TABLE table_m
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null unique,
    unique (f1, f2)
);

CREATE TABLE table_a
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null,
    constraint fk_f1_f2 foreign key (f1,f2) references table_m (f1,f2),
    constraint fk_f3    foreign key (f3)    references table_m (f3)
);

CREATE TABLE table_b
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null
)

You can use information_schema view to get information about FOREIGN KEYS in this way:

SELECT   tc.constraint_name,
         tc.table_name,
         ccu.table_name AS foreign_table_name,
         string_agg(distinct kcu.column_name, ',') AS column_name,
         string_agg(distinct ccu.column_name, ',') AS foreign_column_name
FROM     information_schema.table_constraints AS tc
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY'
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

constraint_name | table_name | foreign_table_name | column_name | foreign_column_name
:-------------- | :--------- | :----------------- | :---------- | :------------------
fk_f1_f2        | table_a    | table_m            | f1,f2       | f1,f2
fk_f3           | table_a    | table_m            | f3          | f3

You can dynamically build SQL statements to add FOREIGN KEYS.

Copy&Paste and EXECUTE or use a function like this SO answer to execute.

SELECT   format('ALTER TABLE table_b ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES table_m(%s);',
                 tc.constraint_name,
                 string_agg(distinct kcu.column_name, ','),
                 string_agg(distinct ccu.column_name, ','))
FROM     information_schema.table_constraints AS tc
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY'
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

| format                                                                                |
| :----------------------------------------------------------------------------------------- |
| ALTER TABLE table_b ADD CONSTRAINT fk_f1_f2 FOREIGN KEY (f1,f2) REFERENCES table_m(f1,f2); |
| ALTER TABLE table_b ADD CONSTRAINT fk_f3 FOREIGN KEY (f3) REFERENCES table_m(f3);          |

dbfiddle here


SQL queries break our game! (Back-end server is at capacity) We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that take more than a couple of seconds, any SQL actions performed by our game will fail with the error:

HTTP/1.1 503 Service Unavailable: Back-end server is at capacity

This obviously brings down our game!

I've monitored CPU usage on the RDS instance during these periods, and though it does spike, it doesn't go much over 50%. Previously we were on a smaller instance size and it did hit 100%, so I'd hoped just throwing more CPU capacity at the problem would solve it. I now think it's an issue with the number of open connections. However, I've only been working with SQL for 8 months or so, so I'm no expert on MySQL configuration.

Is there perhaps some configuration setting I can change to prevent these queries from overloading the server, or should I just not be running them whilst our game is up?

I'm using MySQL Workbench to run the queries.

Here's an example....

SELECT *
FROM BlueBoxEngineDB.Transfer
WHERE Amount = 1000
AND FromUserId = 4
AND Status='Complete';

As you can see, it's not overly complex. There are only 5 columns in the table.

Any help would be very much appreciated - Thanks!

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage.

Please create and index, such as

CREATE INDEX BlueBoxEngineDB.TransferIdx1
ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status);

and then run again your query. It should complete in a glimpse.


RMAN - How to add date in backup tag I know that the default of the RMAN is to add the date in the following format: TAGYYYYMMDDTHHMMSS, But if I wanted something like this: BKP_FULL_11112019. How I do this? I tried using %T but it does't work.

I want to do something like this:

backup as compressed backupset incremental level 0 database tag = 'BKP_FULL_current_date_here'

Which current_date_here must be the current date.

Construct the tag in your script and pass it as a substitution variable. Example on Linux with bash:

$ cat BKP_FULL.rcv
run
{
  backup as compressed backupset incremental level 0 database tag '&1';
}
$ export MYTAG='BKP_FULL_'$(date +%Y%m%d%H%M%S)
$ echo $MYTAG
BKP_FULL_20191111195400
$ rman target / cmdfile=BKP_FULL.rcv using $MYTAG

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 11 19:54:14 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MIN19 (DBID=2133113873, not open)

RMAN> run
2> {
3>   backup as compressed backupset incremental level 0 database tag 'BKP_FULL_20191111195400';
4> }
5>
Starting backup at 11-NOV-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=169 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/MIN19_O71/system01.dbf
input datafile file number=00002 name=/oradata/MIN19_O71/sysaux01.dbf
input datafile file number=00003 name=/oradata/MIN19_O71/undotbs01.dbf
input datafile file number=00004 name=/oradata/MIN19_O71/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-NOV-19
channel ORA_DISK_1: finished piece 1 at 11-NOV-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/03ugkif9_1_1 tag=BKP_FULL_20191111195400 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 11-NOV-19

Starting Control File and SPFILE Autobackup at 11-NOV-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2133113873-20191111-01 comment=NONE
Finished Control File and SPFILE Autobackup at 11-NOV-19

Recovery Manager complete.
$

As you can see, the tag was set as tag=BKP_FULL_20191111195400.


Any gotchas with changing IP address of Postgres Master? We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated and for the sake of safety I'd rather not terminate it before the new one is bedded in).

Aside from the IP changes required in the pg_hba.conf, are there any other points I need to be mindful of?

I'm currently going through the slave and checking for instances of the primary's IP address and it's showing up in some files in $POSTGRES_DATA/main/base/ which is why I'm asking.

Postgres 9.1 on Ubuntu 12.04

Thanks

There are several places you'd want to change the IP address for a PostgreSQL primary server. Most of them are actually outside of PostgreSQL itself or its configuration, though.
You already mentioned the pg_hba.conf so that doesn't need to be mentioned.

Tables that you have inet types for, or other columns you store IP addresses in. If you found IP addresses in $PGDATA/base via a grep, then that probably means that you have IP address entries in your tables, and looking at the information_schema.columns table for those would be most helpful.
Really, needing to touch anything inside of a PostgreSQL data directory outside of .conf files is not recommended at all.

postgresql.conf listen_addresses entries.

Any recovery.conf files that have the primary's IP address referenced for streaming replication.

IP addresses listed in pgbouncer or pgpool-II configurations.

IPTables or other firewall rules on your servers.

Application configurations.

Remote backup scripts.

These are just highlights.
A comprehensive list will require you going patiently through everything in your setup. Creating a good checklist of what needs to be changed in the event of a failover (and keeping it up to date!) will help you immensely in the future, and improve your disaster recovery and high-availability readiness.
Hope that helps. =)


Translating a query from MS Access to SQL Server I have a legacy query from years ago that was written for an MS Access backend:

    DELETE category.*
    FROM category
    LEFT JOIN product_category ON category.id = product_category.categoryid
    WHERE product_category.categoryid IS NULL
      AND category.id IN
        (SELECT cat.id
         FROM category AS cat
         LEFT JOIN category AS cat_par ON cat_par.category_parent = cat.id
         WHERE cat_par.category_parent IS NULL
           AND cat.category_parent != 0
           AND cat.link IS NULL);

My system was changed to SQL Server and this query no longer runs.
I have tested the subquery (in the parenthesis) separately and that works as expected. Just no records are getting deleted.

I have tried changing  the first two lines to read DELETE FROM category (the only syntax difference I was able to look up) but still no deletion occurs.

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join.

So in your case change the top row from

DELETE category.*

to

DELETE category


Unable to use datetime2(0) in Partition Function I have a large table that I want to partition by a datetime2(0) column. AFAIK, datetime2(0) is stored as 'YYYY-MM-DD hh:mm:ss'. However, I can't create the partition function with the same format.

Use PartitionLogTest
Go
CREATE PARTITION FUNCTION pfRequestTimeRange (DATETIME2(0))
AS
RANGE RIGHT FOR VALUES (NULL,
                        '2015-01-01 00:00:00',
                        '2016-01-01 00:00:00',
                        '2017-01-01 00:00:00',
                        '2018-01-01 00:00:00')

Error message:

Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

I've tried formats like '20150101000000' or '2015-01-01 00:00:00.000' but still couldn't make it work. Is there a correct format or I can't/shouldn't create partition function with datetime2(0)?

Removing the NULL as the first partition value should get you past the error.
(UPDATE - Read the comments further down from Dan's post - he actually DOES recommend the action of the OP - you just need to use convert(datetime2(0),null)

Also - check out Dan Guzman's Table Partitioning Best Practices.

Well - after re-reading Dan's post, he actually DOES recommend creating the first partition with a NULL boundary for RANGE RIGHT,

  I suggest specifying a NULL value for the first boundary of a RANGE
  RIGHT partition function.  This NULL boundary serves as the upper
  boundary of the permanent first partition as well as the lower
  boundary for the second partition containing data outside the expected
  range.  No rows are less than NULL so the first partition will always
  be empty.  It is therefore safe to map the first partition to the
  previously mentioned dummy filegroup even if you need to house data
  outside the expected range.  That being said, there is no harm in
  mapping the first partition to another filegroup other than lack of
  clarity.


Getting error when using Oracle imp command to import dmp file I am importing a dmp file using the following command:

imp user/pass file=file.dmp log=logfile.log full=y ignore=y destroy=y

I get the following errors:

. . importing table                "MESSAGE_BOARD"
IMP-00058: ORACLE error 22993 encountered
ORA-22993: specified input amount is greater than actual source amount
IMP-00028: partial import of previous table rolled back: 219638 rows rolled back
. . importing table       "MESSAGE_BOARD_ARCHIVES"
IMP-00058: ORACLE error 22993 encountered
ORA-22993: specified input amount is greater than actual source amount
IMP-00028: partial import of previous table rolled back: 2477960 rows rolled back

I have tried increasing the tablespace size and adding a second datafile to no avail. I'm a total newb at Oracle so any help will be appreciated. Googled this for hours and still can't come up with a solution.

Thanks!

Basically, your first step should be to try specifying a larger buffer (one large enough to hold your LOB).  And, so you're not surprised, this operation can take many, many hours (or even days) depending on the size of the import.
And, IIRC, this error can also be thrown if your import version is less than your export version... so check that, if moar buffer doesn't fix this.
If that fails, you'll need to provide moar details.

ORA-22993: specified input amount is greater than actual source amount

*Cause:  (1) For LOB write, the amount of data received is different from the amount that was indicated would be sent.  (2) For LOB copy and loadfromfile, the end of the source LOB/FILE value was reached before the specified input amount was copied/loaded.
*Action: (1) will happen when using OCI's piecewise mechanism with polling or with a callback function.  Modify the code either to send the amount specified or to pass 0 as the input amount so that any amount of data can be sent.  (2) will happen if the specified input amount is too large for the source LOB/FILE given the starting source offset.  Either decrease the starting source offset, or decrease the amount to copy/load.


Should I believe Query Store runtime stats? There is this weird behavior of Query Store runtime stats that I observe at many of my servers with different scenarios that makes me not to believe these stats. Or am I wrong in something?

E.g. trivial query like this with a given query plan:

Stats (sys.query_store_runtime_stats) reports absolutely insane number of max_logical_io_reads at some accidental interval:

But table has only 27 pages allocated altogether!

I am experiencing this phenomenon with different queries cross many environments. And it's ruining my regressed query analysis.

Queries with different numbers of reads don't have different plans.

The heap table shown only updates and inserts rarely. It's just coincident I used a heap as an example. I experience this with clustered tables too.

Pathology!

This could happen to queries in ways that wouldn't impact the plans:

Heaps: Forwarded Fetches
Indexes: Internal fragmentation

Additionally some selects that cause writes may trigger additional reads if there are stats updates, spills, spools, etc. Though none of those show in your plan, someone else may some along later and find this tidbit useful.

Heaps

DROP TABLE IF EXISTS dbo.el_heapo;
CREATE TABLE dbo.el_heapo ( col1 BIGINT, col2 VARCHAR(8000) DEFAULT 'A' );

INSERT dbo.el_heapo ( col1 )
SELECT TOP 1000000
       x.rn
FROM   (   SELECT     ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 / 0 )) AS rn
           FROM       sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;

Run a simple query:

SELECT COUNT_BIG(*)
FROM   dbo.el_heapo;

Table 'el_heapo'. Scan count 1, logical reads 2718
CPU time = 62 ms,  elapsed time = 62 ms.

Look at the table: No forwarded fetches.

SELECT OBJECT_NAME(ddips.object_id) AS TableName,
       ddips.index_type_desc,
       ddips.forwarded_record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(),
                                      OBJECT_ID('dbo.el_heapo'),
                                      0,
                                      NULL,
                                      'DETAILED') AS ddips;

Cause some forwarded fetches:

UPDATE dbo.el_heapo
SET    col2 = REPLICATE('Z', 1000)
WHERE  col1 % 3 = 0;

If you run the count and forwarded fetch query again, you should see:

~330k forwarded fetches

Table 'el_heapo'. Scan count 1, logical reads 380564
 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 360 ms.

But the execution plan will be the same.

Indexes

Create a dummy table do I don't mess up my real Users table:

SELECT *
INTO dbo.TempUsers
FROM StackOverflow2013.dbo.Users AS u;

CREATE UNIQUE CLUSTERED INDEX c ON dbo.TempUsers(Id);

Check on the table size:

SELECT   TOP 2147483647
         s.name AS schema_name,
         OBJECT_NAME(ps.object_id) AS table_name,
         i.name AS index_name,
         ps.row_count,
         ps.in_row_used_page_count,
         (ps.reserved_page_count * 8. / 1024.) AS reserved_MB,
         (ps.lob_reserved_page_count * 8. / 1024.) AS reserved_LOB_MB,
         (ps.row_overflow_reserved_page_count * 8. / 1024.) AS reserved_row_overflow_MB
FROM     sys.dm_db_partition_stats AS ps
JOIN     sys.objects AS so
    ON  ps.object_id = so.object_id
    AND so.is_ms_shipped = 0
    AND so.type <> 'TF'
JOIN     sys.schemas AS s
    ON s.schema_id = so.schema_id
JOIN sys.indexes AS i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY ps.object_id, ps.index_id, ps.partition_number

Should be 44,450 pages and 348MB.

Simple query:

SELECT COUNT(*)
FROM dbo.TempUsers AS tu

Table 'TempUsers'. Scan count 1, logical reads 44439
   CPU time = 219 ms,  elapsed time = 229 ms.

Update the DisplayName column to be the max length (nvarchar 40):

UPDATE t
SET t.DisplayName = t.DisplayName + REPLICATE('A', 40 - (DATALENGTH(t.DisplayName) / 2))
FROM dbo.TempUsers AS t

Now the table is much larger:

88,392 pages, 691MB.

Query results:

Table 'TempUsers'. Scan count 1, logical reads 88381
   CPU time = 250 ms,  elapsed time = 245 ms.


Designing Database access levels At the moment our production and development databases give sysadmin access to all of devops, third party developers, etc.
I'd like to propose new access levels based upon existing AD groups, but I'm wondering if anyone can give pointers on what levels of access should be defined?

At the moment I've come up with 3 roles:

Admin - giving sysadmin access
Developer - giving permission to create/drop tables, insert/update/delete.
Viewer - read only access.

We also have different applications running on each database/server, and it was proposed that we don't separate these by access level. So, if one person has sysadmin on the 'Sales' database, they would also have the same level on 'Human Resources', etc.

It was also suggest that that our new AD groups would be server agnostic, to ease in transitioning new application access.

I'm wondering if anyone can give their opinions how you've defined your access levels, etc.

Thank you.

Having generalized security across all servers / applications would be a bad approach. With your suggestion, once you let John Doe from Customer Service have read access the Customer database (which he needs in order to accomplish his job), you effectively give him read access to job applicants in an HR database. The HR database would contain Personally Identifiable Information (PII). Since John Doe is not apart of HR, he shouldn't have the permissions to access this data. Allowing him to read out of the HR database would cause compliance issues and could result in major fines when those auditors bust through the door!

John Doe would also be able to read salaries out of the Payroll database, and ohh boy, once that info goes public you're bound to have some tension in the office; employees will question why the new guy is being paid more than them, etc.

Now that I've beat that horse, here is an article I ran across recently discussing how to setup AD groups with specific levels of access to specific databases. This goes into a little bit more detail than you may need, such as creating scripts to create the AD groups, but should be a helpful start.

http://www.sqlservercentral.com/articles/Security/133778/


Multi server administration - Failed to import Maintenance Plan to named instance I have a multi server administration environment and several maintenance plans deployed already. My MSX server is SQL2014. The problem i'm having is deploying maintenance plans to a 2008R2 named instance which is running alongside a default 2014 instance (which is working with MSX MPs already).

I'm getting this error:

[000] Failed to import Maintenance Plan 'MSA backup' from MSX: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.
 ---> System.Runtime.InteropServices.COMException (0xC0011008): Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.

I added the account under which the agent service is running on the MSX to the sysadmin role on the named instance.

I know it's trying to import to SSIS but there can only be one SSIS instance on a machine and that is the default, 2014 instance.

Locally created MPs run without problems. I can provide more info if needed.

I have googled without luck so far, but will continue.

TY.

Your problem is that the Maintenance Plans are really just SSIS Packages under the hood. As your MSX server is 2014, I'm assuming that is where you created them, which means the SSIS package version will be 2014. You will most likely have to downgrade your packages to have them run on SQL 2008 R2, which means you won't be able to include them in your master job server.

This link states that SSIS only supports target servers that are the same version or higher than the master server version, so your 2008 R2 server would either have to be the master or you need to revert to using T-SQL based maintenance jobs instead of Maintenance Plans.


Aggregating spend by customer where accounts can be re-assigned to different customers I want to join unique customer ids with their spends.

Problem: The account_no can be assigned to a different customer (A,B,C etc) at different point in time (assume an employee A leaving the organization and his account no is assigned to B).

Eg from the tables below: If a new customer, say B, is assigned an account number 123 (which was earlier assigned to A) on 1 Mar2015 then spend post 1 Mar2015 must fall in Bs remit til his account 123 is assigned to the next customer.

Tables to Join: Table 1 and Table 2

Desired Result: Table 3

Table 1: Assignment of account to different customers at different dates

Customer_ID   Account_no     Date
A              123           1Feb2015
B              123           1Mar2015
A              124           5Mar2015
C              123           1May2015
B              124           20May2015

Table 2: Daily level spend info

Account_no      Spend        Date
123             100         2Feb2015
123             200         2Mar2015
124             200         7Mar2015
123             200         17May2015
124             150         25May2015

Desired Output:

Customer_ID     Spend
A               100+200=300
B               150+200=350
C               200

The account No can be assigned twice, thrice or more to a different customer in case it is closed by multiple customers.

How do I aggregate the spend customer Id wise?

You need a kind of best-match join like @TypoCubeᵀᴹ's approach, but depending on the actual data (i.e. number of rows per Account_no) this might create a huge intermediate result before aggregation (and needs a uniqueness contraint on table2(Account_no, Date)).

The same logic logic can be achieved by a different approach using UNION ALL and LAST_VALUE:

SELECT Customer_ID, Sum(spend)
FROM
 (
   SELECT
      -- assign the "previous" Customer_ID to the current row
      Last_Value(Customer_ID IGNORE NULLS)
          -- within a date first rows from table1, followed by table2
      Over (ORDER BY Date, x) AS Customer_ID,
      Spend
   FROM
    (
      SELECT
         1 AS x, -- flag indicating source table
         Customer_ID,
         Account_no,
         dt,
         -- need to cast to datatype of table2.Spend
         Cast(NULL AS ...) AS Spend
      FROM cust
      UNION ALL
      SELECT
         2 AS x,
         -- need to cast to datatype of table1.Customer_ID
         Cast(NULL AS ...),
         Account_no,
         dt,
         spend
      FROM spend
    ) AS dt
   QUALIFY -- only rows from table2
           x = 2
       AND -- only if table1 and table2 Account_no match
           Last_Value(CASE WHEN x = 1 THEN Account_no END IGNORE NULLS)
           Over (ORDER BY dt, Spend) = Account_no

 ) AS dt
GROUP BY Customer_ID
;


How can to make a new user see table in existing schema I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this case). Is it even possible?

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?

Log in as HR user and run following statement (first replacing the OTHER_USER with actual username):

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.


How do you set the MySQL password on Mac OS X? I've downloaded mysql 5.25 on mac os x Lion (10.7.4). I just tried setting my password in the usual way, however I'm getting an access denied error which is odd:

[user@Macintosh:~] #mysqladmin -uroot password 'secret'

mysqladmin: connect to server at 'localhost' failed

Is there something I'm missing about setting the root password for this version of mysql on this version of the mac? The database is definitely running according to what I see in the control panel / ps -ef.

Are you getting an access denied error or something else?  mysqladmin should be printing a second line with more details, too. Have you looked over the installation notes for 5.5.25 (the current GA) at http://dev.mysql.com/doc/refman/5.5/en/osx-installation-notes.html ?

Sometimes the client don't look for the mysql socket file in the same place as it was written, or something deletes it if it's written in /tmp or another similar location.  If so then connecting over TCP might work (add -h 127.1 to the command line options).

If you need to reset the password, you can stop mysql and start it (temporarily) with an option to just allow any access without authentication.  I find the easiest way to get the current options it's running with is ps axwwwwwww | grep mysql.


How to get the rowid in a TRIGGER for another table in SQLite Given

the below schema

CREATE TABLE Item (
    _id         INTEGER      NOT NULL,
    name        NVARCHAR     NOT NULL,
    parent      INTEGER          NULL
        CONSTRAINT fk_Recursive
            REFERENCES Item(_id),
    PRIMARY KEY(_id AUTOINCREMENT)
);
CREATE TABLE Container (
    _id         INTEGER      NOT NULL,
    name        NVARCHAR     NOT NULL,
    root        INTEGER      NOT NULL
        CONSTRAINT fk_Container_root
            REFERENCES Item(_id),
    PRIMARY KEY(_id AUTOINCREMENT)
);

Used in client

e.g. Java equivalent prepared statements with ?s and params of these two statements:

INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);

Question

Is it possible to write a trigger (or anything else) without client code (only SQL) which achieves the above results with only

INSERT INTO Container(name) VALUES('My Box');

Note: Of course the client code will retrieve the new rowid for the inserted Container, but I don't care about the new (root) Item, that will be used much later by reading from Container.

The trigger would be along the lines of:

CREATE TRIGGER Rooter
INSTEAD OF INSERT ON Container WHEN (root IS NULL) BEGIN
    SET newRoot = INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, newRoot);
END;

Changing Container.root to NULL and creating an AFTER INSERT TRIGGER shouldn't be an option. But then I guess the same problem of getting the Item's rowid would still stand.

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version:

-- We need a view to create the trigger on
CREATE VIEW Container_Auto AS select * from Container;

-- Since it's automatic we need to check if we need to create a root
CREATE TRIGGER Rooter_Auto
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NULL) BEGIN
    INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, last_insert_rowid());
END;
-- If we don't need a root (i.e. the client supplied it), just pass the values through
CREATE TRIGGER Rooter_Transparent
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NOT NULL) BEGIN
    INSERT INTO Container VALUES (new._id, new.name, new.root);
END;

-- Tests
INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);
INSERT OR IGNORE INTO Container(name) VALUES('Won''t work');
INSERT INTO Container_Auto(name) VALUES('All Automatic');
INSERT INTO Container_Auto(_id, name) VALUES(100, 'Specific ID');
INSERT INTO Container_Auto(name, root) VALUES('Specific root', 1);

-- Results
SELECT '---- Container ----';
SELECT * from Container;
SELECT '---- Item ----';
SELECT * from Item;

Output with comments:

---- Container ----
1|My Box|1          -- everything as we specified
                    -- note the OR IGNORE failed because root IS NULL
3|All Automatic|2   -- the failed insert incremented the rowid as well as created a new root
100|Specific ID|3   -- the supplied _id is used, new root is created
101|Specific root|1 -- the supplied root is used, _id is incremented
---- Item ----
1|ROOT|             -- created manually
2|ROOT|             -- created automatically by INTO Container_Auto(name)
3|ROOT|             -- created automatically by INTO Container_Auto(_id, name)

Notes:

Using INSERT INTO Table VALUES format helps to catch schema modification bugs since when a new column is added the INSERT will fail instead of ignoring the new column.
One can still use the Container table to insert into.
last_insert_rowid() doesn't work when used after an INSERT INTO on a view with INSTEAD OF INSERT, i.e. after INSERT INTO Container_Auto, you'll have to requery the inserted row :(


How to insert a record with generated identifiers using UUID()? The Problem

Overview

So I'm trying to create records that have:

id (primary key, auto-increment)
4 columns that are each a different UUID generated using the MySQL
functions: REPLACE(UUID(), '-', ''). These 'obfuscated keys' are
used for external lookup in other tables. REPLACE() is used to
strip the dashes (-) from the UUID generated by MySQL, which is
required to later transform the UUID into a binary format.  Please
don't worry about that!!!

The Table structure is as follows:
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| info            | varchar(32) | YES  | UNI | NULL    |       |
| note            | varchar(32) | YES  | UNI | NULL    |       |
| auth            | varchar(32) | YES  | UNI | NULL    |       |
| test            | varchar(32) | YES  | UNI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Environment

OS         : CentOS 7
Database   : MariaDB/MySQL
DB Version : 5.5

Partial Solutions Found

I have found a few articles that seem to point "a direction", however I have been unable to solve this.

Trigger before insert : Partially works, but only on the first column.  The MySQL error is: Error Code: 1235. This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
  Also requires the columns to allow null defaults.  Seems like too much of a band-aid hack.

CREATE TRIGGER before_insert_infoid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.info = replace(uuid(),'-','');
CREATE TRIGGER before_insert_noteid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.note = replace(uuid(),'-','');
CREATE TRIGGER before_insert_authid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.auth = replace(uuid(),'-','');
CREATE TRIGGER before_insert_testid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.test = replace(uuid(),'-','');

Direct using insert : Works, but means that the logic is not contained in the database.  Also, I get a MySQL warning warning(s): 1364 Field 'id' doesn't have a default value.

INSERT INTO `meanlearn`.`students`
(`info`,
`note`,
`auth`,
`test`)
VALUES
(REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''));

Preliminary Conclusion / Request for Solutions

I'm sure I'm missing something.  Any and all suggestions are greatly appreciated.

One way to vercome the multiple trigger error would be ot use one trigger - and not 4 - to set the new UUID values:

DELIMITER //
CREATE TRIGGER before_insert_on_user_set_uuids
  BEFORE INSERT ON user
  FOR EACH ROW
    BEGIN
      SET new.info = replace(uuid(),'-','');
      SET new.note = replace(uuid(),'-','');
      SET new.auth = replace(uuid(),'-','');
      SET new.test = replace(uuid(),'-','');
    END ;  //
DELIMITER ;

It would also be good idea to store the UUIDs in BINARY(16) columns instead of VARCHAR(32). Saves 16x4 bytes per row (plus 16 per row multiplied by the number of simple indexes on the columns) and some speed for not considering collations and character sets. And I assume you had the varchar column as ASCII and not UTF-8 which would be a real waste.


Procedure and Automation in Mysql i have a table with these columns and the values are coming in these tables from  a csv file at a regular interval of n minutes ( by cron the load infile command) :

 ID (PK)        status      first      last     value
 1                Q          12         15       low
 2                Q          12         15       low
 3                O          12         15       High

now whenever next data comes , it should first check whether ID is present in table , if it is then it simply update that ID's corresponding field , and if new ID come , it simply insert the data.

For this i have created a procedure :

     create procedure test.testproc (IN no int , IN status varchar(10))
     begin if exists (select id from test.testproc where id = no)
     then
     update baseline set status=status where id=no;
     else insert into  tname(id,status) value(no,status);
     end if;
     end$$

Now what i want to know :

should i create two tables for this i.e one is a temporary table and one main table  ?   ** i'm 100% confuse here what should i do here
how should i automate all these i.e data comes from a csv file , then my load infile script runs, then my above procedure should execute and checking that if ID already present it will simply update else it will insert.

OR , if you have any other process , please tell

I think first you can load infile to MEMORY temporary table. And then just:

INSERT INTO tname (id, status) SELECT id, status FROM tname_temp
  ON DUPLICATE KEY UPDATE tname.status=tname_temp.status;
TRUNCATE TABLE tname_temp;

INSERT ... SELECT syntax reference.

Of course id field has to have unique key and your csv files has to fit in memory.

MEMORY engine tables are very fast so you should have just tiny overhead to load data in two steps. As one of drawbacks they have just table level locks so their use is limited. But that does not impact this data load scenario.


Recursively get ids of tree I have a table foo.

So we have a hiearchical object structure where on foo has one parent (or none) and a list of children.
What I want is a sql statement that lists all id:s that can be found either traversing upwards and downwards in that hierarchy.
How?
My current attempt is
CREATE PROCEDURE [dbo].[GetChain]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo
        WHERE parent_id IS NULL

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON foo.parent_id = p.id
    ),
    chainIDsDownwards AS
    (
        SELECT id, parent_id FROM foo

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsDownwards c ON foo.id = c.parent_id
    )

    SELECT * FROM chainIDsUpwards WHERE id = @starting_id
    UNION
    SELECT * FROM chainIDsDownwards WHERE id = @starting_id
END

but it does only give me a single row.
edit
Sample data:

EXEC [GetChain] @starting_id = 5
returns

but I expect:

5, 2
2, 1
1, null
11, 5

Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=aeaf40c57b3940ce725dccf553a8d9e2
edit 2
I thought I had found the correct sql being
CREATE PROCEDURE [dbo].[GetChain]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.id = foo.parent_id
    ),
    chainIDsDownwards AS
    (
        SELECT id, parent_id FROM foo WHERE parent_id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsDownwards c ON foo.id = c.parent_id
    ),
    chainIDs AS
    (
        SELECT id FROM chainIDsUpwards
        UNION
        SELECT id FROM chainIDsDownwards
    )

    SELECT id FROM chainIDs
END

but that will not find any parents for 9. It only finds 9 itself, but not 3 or 1.
Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=10041653e78b52bc8ec0e3c40cfff7a0

Basically, you need to move your @starting_id predicate to the base case of the recursive part:
WITH chainIDsUpwards AS
(
    SELECT id, parent_id FROM foo
    WHERE id = @starting_id

    UNION ALL

    SELECT foo.id, foo.parent_id FROM foo
    JOIN chainIDsUpwards p ON foo.id = p.parent_id
)
SELECT * FROM chainIDsUpwards;

If you put the predicate as:
SELECT * FROM chainIDsUpwards WHERE ...;

it will filter all rows that do not satisfy the predicate.
I also believe that you need to change the join clause in both cte:s if you want the behavior to correspond with the name. I find it easier mentally to start with the cte in the join like:
SELECT foo.id, foo.parent_id
FROM chainIDsUpwards p
JOIN foo
    ON p.parent_id = foo.id

Fiddle