How do I design a database for a resource scheduling and allocation application? I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an hourly basis).

Now if I create two tables (resource and project), how do I achieve this? How should the tables be designed? Do I create a new entry for every day and resource?

Any help is much appreciated!

The only possible reason that you might want to consider creating an entry for each resource and day is that it would give you the ability to use declarative constraints to enforce a business rule that each resource can only be allocated to one project (at most) per day.  You didn't say anything about this being a business rule, so I wouldn't suggest modeling it that way.  It's just more data to maintain.

You could use a schema like this:

RESOURCE:
 id (PK),
 name,
 ...

PROJECT:
 id (PK),
 name,
 ...

ALLOCATION:
 resource_id (PK/FK),
 project_id (PK/FK),
 start_date (PK),
 finish_date

This lets you assign resources to multiple projects over one or more ranges of dates.  It has the advantage of minimizing the number of records to maintain, assuming that resources are assigned to projects for more than just a day at a time.

Alternatively, if you want the allocation table to be structured so that it enforces the rule that each resource can only be allocated to one project at a time, then you could use the following schema.  Note that this requires a record per resource per day.

ALLOCATION:
  resource_id (PK/FK),
  project_id (FK),
  work_date (PK)

If you wanted to enforce this business rule through application logic instead of through declarative constraints in the database, you could use the first schema noted above instead.

Edit: Query to detect overlaps

If you use the first option, where allocations include a date range, then you need to use some application logic to enforce a business rule that says a resource can only be allocated to one project at a time.

To do this, what you want to do is this:  Before every insert or update of a record in ALLOCATION, you need to check to see if the record you are about to write would overlap in an illegal way with all of the other records that are already in the table.

A query that would find potentially overlapping records would look something like this:

select
  resource_id
, project_id
, start_date
, finish_date
from ALLOCATION
where resource_id = @ResourceOfInterest
  and start_date <= @NewFinishDate
  and finish_date >= @NewStartDate

If you are inserting and the above query returns zero rows, then you are good to go.

If there are one or more rows returned, you can insert without creating an overlap.

If there is one row and you are updating, you need to make sure that you aren't just finding the row that you plan to update.  You have to check the other column values to see if that is what you've found.

Queries to Answer Resource Allocation Questions

Using allocation tables with date ranges (instead of one record per day) makes answering some kinds of questions a little more complex.  Still, it isn't hard to answer the same questions and the time you save maintaining daily rows will more than compensate for a little extra complexity in some of your queries.  For example:

What does the resource allocation look like today?

select
  resource_id
, project_id
-- whatever else you want to join in...
from ALLOCATION
-- inner join whatever else you want...
where start_date <= @TodaysDate
  and finish_date >= @TodaysDate

Which projects have no resources assigned next Monday?

select
  A.resource_id
, A.project_id
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
where not exists
( select B.resource_id, B.project_id
  where B.start_date <= @NextMondaysDate
    and B.finish_date >= @NextMondaysDate
    and B.resource_id = A.resource_id
    and B.project_id = A.project_id )

How many resources were assigned to each project each day last month?

select
  A.project_id
, B.CheckDate
, COUNT(A.resource_id)
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
right outer join
(select DATEADD(DD,N-1,@FirstDayOfMonth) as CheckDate from NUMBER
 where N <= DATEDIFF(DD,@FirstDayOfMonth,@LastDayOfMonth)+1) B
on  A.start_date <= B.CheckDate
and A.finish_date >= B.CheckDate
group by
  A.project_id
, B.CheckDate
-- Also group by anything else you've joined in, like project name, etc.

This last one uses a Number Table which is something every database developer should have in their toolbox.


Does creating a unique constraint on a Postgres column remove the need to index it? Does creating a unique constraint on a Postgres column remove the need to index it?

I expect that an index is automatically needed to maintain the constraint efficiently.

Yes. A UNIQUE constraint is implemented with the help of a unique index -  a b-tree index with default ascending sort ordering over all involved columns. The index is created and maintained automatically, and is used for all purposes like a plain unique index by Postgres.

There is no need to create another (redundant) unique index like it, that would be a waste of resources.

Detailed explanation:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Is unique index better than unique constraint when an index with an operator class is required

Some rare exceptions apply for multicolumn indexes with special sort options:

Optimizing queries on a range of timestamps (two columns)


if-then-else gives a boolean headache I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2:

I need to concatenate three columns into one but in three different ways depending on the result in the last one. Column 3 can be a null-value, an alphanumeric letter or begin with '-' followed by a number. I tried the following:

if (select * from col3  where col3 is null)
 select col1 + ' ' + col2
else if (select * from col3 where col3 like '-%'
 select col1 + ' ' + col2 + col3
else if (select * from col3 where col3 not like '-%'
 select col1 + ' ' + col2 + ' ' + col3

Separately, they work like a charm. When trying to combine them like the above statement they throw me an error saying I have created a Boolean instead of the expected condition.

How do I formulate this to be correct?

The syntax you are looking for is the case statement.  You can use IF .. EXISTS for controlling the flow in your code but for something like this the CASE WHEN statement is what you want.

SELECT CASE WHEN col3 IS NULL THEN col1 + ' ' + col2
    WHEN col3 LIKE '-%' THEN col1 + ' ' + col2 + col3
    WHEN col3 NOT LIKE '-%' THEN col1 + ' ' + col2 + ' ' + col3
    END AS ConcatCol
FROM TableName;

and simplified to:

SELECT col1 + ' ' + col2
       + CASE WHEN col3 IS NULL THEN ''
            WHEN col3 LIKE '-%' THEN col3
            ELSE ' ' + col3
         END AS ConcatCol
FROM TableName;

Assuming I understood what you are trying to do of course.

EDIT: Added alias for column


Validation logic for Mobile Number Column in SQL Server I am creating a validation script and need help with mobile number validation logic with below details.  Mobile number must be:

10 digits in length.
Cannot begin with a 1 or a 0 (For example:1144684679 or 04468-4679
Cannot contain all the same digit.  For example: 5555555555

I am using PATINDEX for this but could not come up with the proper result.

I seriously doubt the real world utility of the specified validation rules but a reasonably concise way of validating them is below.

SELECT PhoneNumber,
       Valid = CASE
                 WHEN PhoneNumber LIKE CONCAT('[23456789]', REPLICATE('[0123456789]', 9))
                      AND PhoneNumber LIKE CONCAT('%[^', LEFT(PhoneNumber, 1), ']%')
                   THEN 'valid'
                 ELSE 'invalid'
               END
FROM   (VALUES ('0123456789'),
               ('1144684679'),
               ('5555555555'),
               ('5555555554'),
               ('9876543210'),
               ('9876')) PhoneNumbers(PhoneNumber)

+-------------+---------+
| PhoneNumber |  Valid  |
+-------------+---------+
|  0123456789 | invalid |
|  1144684679 | invalid |
|  5555555555 | invalid |
|  5555555554 | valid   |
|  9876543210 | valid   |
|        9876 | invalid |
+-------------+---------+


Update a column using LOAD_FILE function I have created a table lfile. The structure of table is:

CREATE TABLE `lfile` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

In the table I have 3 records:

select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | ravi  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Now I want to update 'ravi' with 'ravikumar' using LOAD_FILE.

  UPDATE lfile SET name=LOAD_FILE('/home/ravi/Desktop/t') WHERE id=1;

But it updates the name column with a NULL value.

mysql> select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | NULL  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Settings:

show variables like 'character_set_filesystem';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_filesystem | utf8  |
+--------------------------+-------+

Now i want to update 'ravi' with 'ravikumar'

LOAD FILE loads an entire table, not one column, not one cell.

Instead,

UPDATE lfile
    SET name = 'ravikumar'
    WHERE name = 'ravi';


Troubleshooting slow connection establishment The client and the server are running on Windows on different subnets.
I'm facing a 20s delay to establish a connection to a SQL 2012 Always On availability group using .NET Core Sql client using sql authentication.
I have no problem establishing a connection to other SQL Server instances (no availability group). The connection establishment is instantaneous.
I do not have the same problem when using the MS sql command line client (sqlcmd which uses ODBC).
I need help solving this problem. I don't know yet if it is network or "AlwaysOn" configuration issue.
I'm able to provide network traces if requested.

EDIT 1: Wireshark traces
106 42.144914   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
107 42.690863   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
108 42.816460   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
109 44.157170   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
110 44.700425   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
111 44.824821   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
112 48.161575   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
113 48.708869   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
114 48.832817   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
115 50.537018   172.23.10.49    10.150.200.213  TCP 55  [TCP Keep-Alive] 56094 → 1433 [ACK] Seq=8294 Ack=3419 Win=2101760 Len=1
116 50.537561   10.150.200.213  172.23.10.49    TCP 66  [TCP Keep-Alive ACK] 1433 → 56094 [ACK] Seq=3419 Ack=8295 Win=64512 Len=0 SLE=8294 SRE=8295
117 50.585230   10.150.200.213  172.23.10.49    TCP 60  [TCP Keep-Alive] 1433 → 56094 [ACK] Seq=3418 Ack=8295 Win=64512 Len=1
118 50.585293   172.23.10.49    10.150.200.213  TCP 66  [TCP Keep-Alive ACK] 56094 → 1433 [ACK] Seq=8295 Ack=3419 Win=2101760 Len=0 SLE=3418 SRE=3419
119 56.161835   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
120 56.724399   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
121 56.833741   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1

Since the connection is fast from sqlcmd, but slow from the .NET Core SqlClient driver, it's most likely this problem is on the client driver side (rather than an AG configuration issue).
You should add MultiSubnetFailover=true to the connection string to speed things up, as it shortens the retry interval. See the docs for details:
Connecting With MultiSubnetFailover

When MultiSubnetFailover=True is specified for a connection, the client retries TCP connection attempts faster than the operating system’s default TCP retransmit intervals. This enables faster reconnection after failover of either an AlwaysOn Availability Group or an AlwaysOn Failover Cluster Instance, and is applicable to both single- and multi-subnet Availability Groups and Failover Cluster Instances.

This isn't needed anymore on .NET Framework 4.6.1 and higher because TransparentNetworkIPResolution is on by default (which causes the driver to attempt connections to all the replicas in parallel, and use the first one that works, rather than trying them one-by-one).  But it looks like that enhancement hasn't made it to .NET Core yet.


How to use two actions and a function in an Extended Events Histogram for deadlocks? I'd like to get a histogram through extended events that shows the count of deadlocks per databasename by date, but I'm hitting two walls:

How get a histogram to include two actions
How to get the collect_system_time rounded to just the date -- I don't see a way to use a function on an action.

The (relatively) easy answer would be to query and shred the xel files and produce the result set I want (and then copy/paste to Excel if I want a chart), but I was curious if there was a way to do it directly within an XE session histogram.

A target is a place where events captured by an Extended Events (XE) session go to live.  A histogram (previously synchronous_bucketizer or asynchronous_bucketizer) is a target only visible in the sys.dm_xe_session_targets DMV whilst the XE session is running.  An .xel file is also a target persisted to a file.  Although you can have multiple targets per session (eg ring buffer, histogram, event file, event counter) you cannot place a histogram in an event file, a target within a target.  Nor can you use custom functions within the XE session to modify the attributes of actions and events you capture.

However what you could do, is create an XE session using the histogram target and the sqlserver.database_xml_deadlock_report event and poll it on a regular basis, eg 5 minutes to the hour in my example below.  Run the code in the 'Setup' section first, then place the second bit of code in a SQL Agent task schedule to run each hour.  This code groups the information from the XE session up into date, hour and database format and persists it to a table:

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Setup
--IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
--  DROP EVENT SESSION XE_DeadlockHistogram ON SERVER;
--GO

IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
CREATE EVENT SESSION XE_DeadlockHistogram ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.histogram
(
SET slots = 32                      --< this should be greater than number of databases on your server; gets rounded up nearest power of 2 anyway
  , filtering_event_name = 'sqlserver.database_xml_deadlock_report' --,
  , source_type = 0                 -- < 0|1 = event|action
  , source = 'database_name'
)
WITH (
    MAX_MEMORY = 4096KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 10 SECONDS,
      MAX_EVENT_SIZE = 0KB,
      MEMORY_PARTITION_MODE = NONE,
      TRACK_CAUSALITY = OFF,
      STARTUP_STATE = OFF
    )
GO

-- Tracking table
IF OBJECT_ID('dbo.deadlockHistogram') IS NULL
    CREATE TABLE dbo.deadlockHistogram (
        deadlockDate        DATE NOT NULL,
        deadlockHour        TINYINT NOT NULL,
        dbName              SYSNAME NOT NULL,
        sessionStartTime    DATETIME NOT NULL,
        deadlockCount       SMALLINT NOT NULL,

        CONSTRAINT PK_deadlockHistogram PRIMARY KEY ( deadlockDate, deadlockHour, dbName )
        )
GO

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- SQL Agent Job Code START
-- Place this code in a SQL Agent job and schedule to be run every hour at 5 minutes to the
-- hour, eg 6.55, 7.55 etc
-- !!TODO Wrap in a stored proc; add to Utilities database
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, s.create_time AS sessionStartTime, SYSDATETIME() AS dateAdded, CAST( SYSDATETIME() AS DATE ) AS deadlockDate, DATEPART( hour, SYSDATETIME() ) AS deadlockHour
INTO #tmp
FROM sys.dm_xe_session_targets st
    INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address
WHERE s.name = 'XE_DeadlockHistogram'
  AND st.target_name = 'histogram'
GO

-- Collect current deadlock info from dm_xe_session_targets and tag with current date/hour
;WITH cte AS
(
SELECT
    CAST( dateAdded AS DATE ) AS deadlockDate,
    DATEPART( hour, dateAdded ) AS deadlockHour,
    v.c.value( '.', 'SYSNAME' ) dbName,
    sessionStartTime,
    s.c.value( '@count', 'INT' ) deadlockCount
FROM #tmp t
    CROSS APPLY t.target_data.nodes('HistogramTarget') td(c)
        CROSS APPLY td.c.nodes('Slot') s(c)
            CROSS APPLY s.c.nodes('value') v(c)
)
INSERT INTO dbo.deadlockHistogram ( deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount )
SELECT deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount
FROM cte t
WHERE NOT EXISTS
    (
    SELECT *
    FROM dbo.deadlockHistogram h
    WHERE t.deadlockDate = h.deadlockDate
      AND t.deadlockHour = h.deadlockHour
      AND t.dbName = h.dbName
    )
GO

-- Stop and restart the session to reset the historgram counts
IF EXISTS( SELECT * FROM sys.dm_xe_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = STOP
    RAISERROR( 'XE session %s stopped successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = START
    RAISERROR( 'XE session %s started successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
RAISERROR( 'XE session %s does not exist.', 16, 1, 'XE_DeadlockHistogram' )
GO

-- SQL Agent Job Code END
------------------------------------------------------------------------------------------------

Stopping and restarting the session during the Agent job is important to reset the histogram.  When deadlocks occur, the session will record XML that looks like this:

<HistogramTarget truncated="0" buckets="32">
  <Slot count="2">
    <value>tempdb</value>
  </Slot>
  <Slot count="1">
    <value>testCS</value>
  </Slot>
</HistogramTarget>

The code above shreds it and over time it will build up into data that looks like this:

This is a simple example, your business requirements may be more complex, but have a look through the code and let me know what you think.  You could probably also achieve something similar polling the system_health session (as mentioned by @Kin) but you would require some more heavy-weight XQuery which I can help you with if required.  The system_health session also targets to ring buffer which may wrap around / cycle, so this tightly scoped histogram session isn't a bad idea.

NB I am not using the sqlserver.xml_deadlock_report event as database name was always master, I guess where deadlock monitor runs.


How do I enforce a write-once then read only database table in SQL? Is it even possible?

My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to it - there are other tables in the same schema which will be updated/deleted as normal.

My understanding is that for data integrity purposes these sorts of constraints should be applied at the database layer, but I can't find a clean, widely accepted way of doing this - is this a use case where I'd just be better doing it in the application layer?

The ideal would be for a way to do it in plain SQL, so as to be agnostic of what DB platform is used, since that may be subject to change, but I realise that may be too much to ask for, so if it has to be platform-dependent, some flavour of MySQL is preferred.

Thank you!

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement processing, but it can be overridden by users with elevated privileges, like root.
Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use the SIGNAL statement in the trigger body to raise an exception, which would prevent updates and deletes respectively. There is slight performance penalty that you will pay for this, but also some added security, as even privileged users won't be able to get past the error without disabling or dropping the triggers.


What "tool" to use for monitoring instances and where to store this data? I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server instances for monitoring purposes. My problem is what "tool" to use and where to store this administration data. My choices are

Create an admin database for each instance and store monitoring
data, queries and stored procedures separately
Create an admin database for e.g development database and query data from all instances into this database
Use SSIS to gather data from all instances and store this data into admin database (dev instance)?
Use PowerShell to query data from all instances and store this data into admin database (dev instance?)

What other choices do I have?

sql sentry
Quest spotlight for sql server enterprise
Microsoft Operations Manager
Various RedGate tools
Your own scripts, data collectors and jobs
Windows Performance monitor


Error in restore database I use this query in SQL Server 2008 R2 to restore DB1 database:

RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

The following error is displayed:

  Msg 3102, Level 16, State 1, Line 1
  RESTORE cannot process database 'DB1' because it is in use by this session. It is recommended that the master database be used when performing this operation.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The message seems pretty clear: you cannot be using the DB1 database when trying to restore it - instead, use the master database!

So in SQL Server Management Studio, first close all windows that are open and using that database! Then run this:

USE master
GO

RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;


get average of number of likes in mysql from two tables let's say I have a posts table:

post_id    user_id    text
1             2        abcd
2             1        wert
3             3        yhj

and I have a ranks table:

post_id     liker_user_id      score
1              3                 3
3              4                 1
3              3                 5

I want to get avg(score) for each post so I came up with this simple query:

SELECT posts.post_id, posts.text , avg(ranks.score) as `ranking` FROM posts LEFT JOIN ranks ON ranks.post_id = posts.post_id GROUP BY posts.post_id;

so this is fine but the problem is, it is possible that a post does not have any ranking in the ranks table, so there will be nothing in the ranking field and it gives me something like this:

post_id   text   ranking
1         abcd     3
2         wert
3         yhj      3

note that there is nothing in ranking for post_id = 2.
I want a zero for such results, like this:

post_id   text   ranking
1         abcd     3
2         wert     0
3         yhj      3

how can I do this in MySQL?

You can use the COALESCE function to return zero if it has no results like so:

SELECT posts.post_id, posts.text , COALESCE(AVG(ranks.score),0) as `ranking`
  FROM posts
  JOIN ranks
    ON ranks.post_id = posts.post_id
 GROUP BY posts.post_id;


Best way to handle temporary data Say I have a table prices. Users can see the prices of this table (I'm simplifying it). Users also can send prices to the server, so the server is going to collect these data in another table (temp_prices).

When the server detects that there are 50 prices or more, then the server calculates a median of those temporal prices and update the table prices with the calculated value (again, very simplified).

The first idea that came to my mind was have two tables with the same schema but different names (prices and temp_prices).

I don't know if this is a good/common practice. I am missing something?
Also, what mechanism should I use to trigger the server to inspect the table temp_prices? I think about cron but again, I don't know if there is something I'm missing (another tool or common practice for doing this kind of stuff).

Thanks.

Depending on the database you're using, you could have an ON INSERT trigger that checks if there are >= 50 records in prices and then runs the recalculate-and-update process (you don't have to do all of that in the trigger itself, you could separate it out into a stored procedure that is called by the trigger).

As for prices and temp_prices, I don't see anything terribly wrong with it. I imagine that the structures are the same, but they are being used in very different ways. If you did want to merge the tables, you would probably need to have an indicator of some sort to show which records came in from the users, and which ones were there before. Considering that there will be different kinds of activity on these two types of prices, it might make sense for performance reasons to leave them in separate tables.


DBCC CHECKIDENT behaving inconsistently I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables and update with the latest versions of the reference tables so that This one script can be updated and stored in a source repo to be used on whatever db server needs to run it.

I have a problem with DBCC CHECKIDENT reseeding to use 0 if This is the first run on a fresh database or reseeding to 1 (the desired result) if it is a subsequent run of the same script on the database.

The relevant code at the beginning of the script

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

How can I get this to behave consistently on initial and subsequent runs so that I don't break foreign keys by having row 1 becoming row 0?

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id
WHERE last_value IS NOT NULL

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

adding WHERE last_value IS NOT NULL as suggested by the second answer in the question Aaron Bertrand linked too solved this issue.


SQL Server IO per session Activity Monitor can show real time the overall IO performance in MBytes/s on a server, but cannot show the same per session level.

I know about DMVs, and sp_who2 to retrieve session level io consumption , but there, its only the number of reads/writes can be extracted, not the actual Megabytes/s.

How can I see real time, session level io performance in MBytes/s?

You can use either a Profiler trace or sp_whoisactive to get the number of reads per session.  Reads are the number of logical disk reads in 8KB pages, so if the number of reads is 2, for example, that might equate to 16KB of disk I/O.


Where are some useful SQL puzzles to teach SQL in a workplace? I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice.

I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there that you could suggest?

Joe Celko's books and newsgroup posts are a good source of puzzles (with a variety of answers). They might be a bit more intermediate/advanced and not MySQL-specific, but are easy to follow and generally come from practical real-world situations.

Particularly, hunt down a copy of Joe Celko's SQL Puzzles and Answers (link to Google preview), it's a compilation of some of the best.

The SQLTeam forums occasionally have some interesting problems too, but generally focused on MS SQL Server.

The HackerRank SQL domain has a variety of SQL challenges that can be completed using multiple implementations (e.g.: Oracle, MySql, MS SQL Server, DB2).


PostgreSQL 9.5, getting "cached plan must not change result type" error I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in this case, I resorted to an array) the error would be gone. Perhaps any one experienced a similar error or knows what this error relates to? I might need to create temporary tables in functions sooner or later.

I'm porting an old Delphi XE6 program from a legacy proprietary database to PostgreSQL, changing the TxxTable components (proprietary) to TFDTable ones (FireDAC) pointing to a copy of the database in PostgreSQL 9.5.

Things were working peachy until I created this plpgsql function that inserts and updates some data here and there. I ran the function via PGConnection.ExecSQLScalar('SELECT do_stuff(:id)', FID);, where PGConnection is my TFDConnection to PostgreSQL (same where I have many other tables open and working well), do_stuff is the function in question, and FID is an ID I send as a parameter. The function runs well, as it does when running it from PgAdmin, but the connection becomes unstable as any already open table afterwards trying to do anything (insert/edit/move) will raise the following error:

  ERROR:  cached plan must not change result type
  STATEMENT:  FETCH FORWARD 50 FROM "357STM"`

FireDAC normally opens tables and queries using cursors:

  LOG:  execute 358STM: DECLARE "357STM" CURSOR WITH HOLD FOR SELECT *  FROM table

If I execute do_stuff(:id) via an TFDQuery component, the function itself fails (FireDAC executes it as a cursor), raising the same error stated above.

After tearing my hear out debugging, I realised that the only way to avoid the error was avoid creating any temporary tables inside the function. Whereas creating the function as CREATE TEMP TABLE tbl ON COMMIT DROP AS SELECT * FROM some_table WHERE .... or as CREATE TEMP TABLE tbl (id int, value text); the error was raised. Without CREATE TEMP TABLE, no errors raised.

Any clues will be highly appreciated!

FireDAC FDQuery.

Cursor kind in fetch options was set to ckAutomatic. I changed it to ckDefault and it works now.


How do I in "Navicat for PostgreSQL" run a simple PL/pgSQL script like I would in MS SQL Server Management Studio? I don't want a function or anything. I just want to run/test a simple script with some PL/pgSQL features in it. Something like the following:

IF 1=1 THEN
    SELECT concat('Hello', 'World');
END IF;

I'm sure the syntax is correct, but it doesn't work in the normal "Query Editor" like I'd do it in Microsoft SQL Server. I get the following error:

[Err] ERROR:  syntax error at or near "IF"
LINE 1: IF 1=1 THEN
        ^

I'm running Navicat for PostgreSQL version 11.1.13 (64-bit) on a Windows 10 machine. select version() says the following:

PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

edit:

I just realized that this question might be the victim of the X-Y Problem. What I really want to do is to test the following way of inserting values ignoring duplicates:

BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
END;

I found it here: Optimal way to ignore duplicate inserts?

I get the following error:

[Err] ERROR:  syntax error at or near "INSERT"
LINE 2:         INSERT INTO db_table (tbl_column) VALUES ('test');
                ^

DO $$
BEGIN
    INSERT INTO t1 (id) VALUES (6);
EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.

END$$;

You can not use IF outside procedure code, but Your construction possible run like this


Appending backups while overwriting expired sets I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written.  For example the INIT, NOINIT. One says it will try to overwrite everything. The other says it will append regardless.

I want something in-between that. I want expired media sets to no longer be in the file, but for new backup jobs to append to the file along side any sets that have not yet expired.

Will the SQL below perform the desired backup?

BACKUP DATABASE [mydb]
TO  DISK = N'c:\Backups\mydb.bak'
WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', NOSKIP,
NOREWIND, NOUNLOAD,  STATS = 10, RETAINDAYS = 60
GO

If I run the above, and there is a backup set older than 60 days, will it be removed from the file?

Edit:

In response to RichardTheKiwi's suggestion I have rewritten the code to create a new file each time.  I will then write a windows script to delete files older than a certain date

declare @filename varchar(max)
set @filename = 'c:\sqlbackups\pptd_' +  replace(replace(convert(varchar, getdate(), 120),':','_'),' ','_') + '.bak'
BACKUP DATABASE [Peter's pointless test database]
TO  DISK = @filename
WITH FORMAT, INIT,  NAME = N'pptd-Full Database Backup',
NOREWIND, NOUNLOAD,  STATS = 10
GO

Unfortunately, no, that won't work.  It will keep appending but will leave the expired sets in the same archive.  This screenshot shows a backup archive using your script, set to RETAINDAYS = 2, run once in November (VM trickery) and again today.

I think what you're after is a rolling backup, so assuming this is weekly, then after a burn in period, it should retain 7 old backup sets, remove one and append a new one.

Personally, I would keep a single backup set per file and use the cleanup feature in the maintenance wizard to remove old files; that or a regular Windows Scheduled Task.  There shouldn't be any real need to keep backup sets in a single file.


How can I find the source of postgresql per-connection memory leaks? I'm using postgresql 9.5.4 on amazon RDS with ~1300 persistent connections from rails 4.2 with "prepared_statements: false".  Over the course of hours and days, the "Freeable Memory" RDS stat continues to go down indefinitely but jumps back up to a relatively small working set every time we reconnect (restart our servers).  If we let it go too long, it goes all the way to zero and the database instance really does start to go into swap and eventually fail.  Subtracting the freeable memory over days from the peaks when we restart we see that there are 10's of MB per connection on average.

Digging into the per-pid RSS from enhanced monitoring, we see the same slow growth on example connection pids but the total RSS seems to just be a proxy for actual memory usage per connection (https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/).

How can I either:

Change the default.postgres9.5 parameters below to avoid unbounded memory growth per-connection
Determine what queries cause this unbounded growth and change them to prevent it
Determine what type of buffering/caching is causing this unbounded growth so that I can use that to do either of the above

If you were using community PostgreSQL, the definitive answer to figuring out where the memory is going on Linux is to connect to a bloated backend with gdb, and do p MemoryContextStats(TopMemoryContext) and then inspect the output from the server log file.  But since you are not using community PostgreSQL, I guess the definitive answer would be to contact your paid support and ask them to figure it out for you.

The most likely cause for this is that PostgreSQL keeps a per-connection cache of metadata about all the database objects (tables, indexes, etc.) it has touched during the connection lifetime.  There is no upper limit on how big this cache can get, and no mechanism for expiring it.  So if you have hundreds of thousands or millions of these things, and a long-lived connection will eventually touch each one of them, then their memory usage will continuously grow.  The usual solution to this (assuming you can't reduce the number of objects in your database) is to set up your connection pooler so that each connection has a maximum time to live before being closed rather than recycled.


Connect to Oracle from SQL Server We are migrating an application from Oracle to SQL Server. The vendor is responsible for the majority of the migration efforts, but there is one piece that was developed in-house that I'm having trouble with.

The old application on Oracle has a procedure that collects specific data and then another procedure inserts that data into a staging table in our revenue system (a different Oracle server). This is an Oracle procedure via an Oracle Public Database Link.

In SQL Server, I'm trying to rewrite this process using SQL Server's Linked Servers. I've created the Linked Server connection to the revenue system and it can connect via that link.

From SQL*Plus, I can issue this statement as that user:

select * from rs.rs_myapp;

And I get results. (rs is the revenue system user, not the user I connect as. rs_MyApp table is the staging table for our data in their system.)

But if I attempt to do this in SQL Server:

select * from [rstest-Link]..rs.rs_myapp

I get this message:

  Msg 7314, Level 16, State 1, Line 50 The OLE DB provider
  "OraOLEDB.Oracle" for linked server "rstest-Link" does not contain
  the table ""rs"."rs_myapp"". The table either does not exist or
  the current user does not have permissions on that table.

I can do this, however:

select * from openquery([rstest-Link], 'select count(*) from rs.rs_myapp')

And that gets results back, showing that the link does work.

However, I cannot complete the SQL Server procedure, because of that 7314 error. The procedure is really basic, it has exactly one line of code in it:

Insert Into [rstest-Link]..rs.rs_myapp (ACCOUNT_NUMBER, FROM_DATE, TO_DATE,
        Data1, Data2, SAMPLE_DATE, Content1, Content2)
    select ACCOUNT_NUMBER, FROM_DATE, TO_DATE, MyData1, MyData2, SAMPLE_DATE, MyContent1, MyContent2
    FROM dbo.RS_INTERFACE;

But without resolving the 7314 error, there's no way to create the procedure.

So how do I do this? Am I going to have to use a cursor and issue some sort of code for each row, like:

INSERT OPENQUERY ([rstest-Link], 'SELECT ... FROM ...')
VALUES ('...');

Oracle uses a case-sensitive catalog, and hides that ugly fact from you by silently converting non-quoted identifiers to all caps, both in DDL and DML.

The name of your table is actually RS.RS_MYAPP, and SQL Server may be sending the identifier to Oracle as "rs"."rs_myapp", which would fail at Oracle.

So try

select * from [rstest-Link]..RS.RS_MYAPP


ORA-12505: TNS:listener does not currently know of SID given in connect descriptor Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD.
I referred below mentioned threads, but issue is still not resolved:

  https://stackoverflow.com/questions/5661610/tns-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

  https://stackoverflow.com/questions/3267850/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

Contents of my tnsnames.ora:

ORCL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=localhost)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl)
    )
  )

Output of 'lsnrctl services':

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-FEB-2012 23:53:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

All my oracle services are running. I tried to start database using startup command:

C:\Users\PPPP>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 15 23:56:59 2012

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 214624
Session ID: 5 Serial number: 3

In listener.log file, I see this error:

Thu Feb 16 00:13:28 2012
16-FEB-2012 00:13:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)*            (USER=PRITHVIRAJ-PC$))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)    (HOST=127.0.0.1)(PORT=59161)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in     connect descriptor

Please help me to sort out this issue.

As the ORA-19815 message indicates, your 3GB recovery destination is full; the database is unable to archive any more redo log files. It has reached a point where it needs to switch a full redo log file out, but there are none available for re-use because they can't be archived. I imagine this is why it wouldn't shut down cleanly, and possibly why you wanted to shut down in the first place as you wouldn't have been able to do much with the database in that state.

The short-term fix is to increase the size of the recovery area, assuming you have enough disk space, e.g.:

startup nomount
alter system set db_recovery_file_dest_size=4G scope=both;
alter database mount;
alter database open;

But you then need to work out how to manage your archives, and set the recovery area size to a sensible value for your backup and retention needs. This may be as simple as scheduling a job to remove obsolete backups, e.g. from the Enterprise Manager console's Availability tab, under Manage Current Backup; but you need to determine the best course for you (and not blindly follow advice from some random guy on the Internet who knows nothing about your requirements, and not that much about EM/RMAN either).


MySQL conversion from float (10,2) to fixed(10,2) I have a database that contains financial data stored as floats. Naturally, this can present a real issue at random times (eg, when there is an exact value that cannot be approximated closely enough with a floating-point algorithm).

My question is:

Will using  ALTER TABLE table MODIFY field fixed(10,2) preserve the fixed value that is currently shown by MySQL when you select the field?
Is there any possibility that the value would be something other than that?

BAD NEWS : I do not think the value will be preserved !!!
I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.
GOOD NEWS : Here is how you can safely compare the data conversion:
If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:
DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload  the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.
Give it a Try !!!


How check that the Oracle Database is up? For our application we have a configuration file for connecting to the Oracle database (URL, username, password).

The application takes 2-5 minutes to start.  If there is something wrong with database I see this in logs, but I have to wait 2-5 minutes. This is a lot of time.

So I want to find out quickly and simply a way to determine that the database is all OK and online.

Do you have any idea how I might do it ?

If you run the following query:

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

It should return:

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
YOUR_DBASE       OPEN         ACTIVE


SSAS Tabular QueryMode Confusion. Why Hybrid mode is useful? I read couple of articles about QueryMode in Tabular. I finally noticed that hybrid modes (DirectQuery with In-Memory and In-Memory with DirectQuery) are NOT useful for my case. Actually they are not working the way I like.

We have huge Tabular databases and we use In-Memory for all partitions and concern is we run out of memory at some point.

My thought is, if I could use DirectQuery for old partitions and In-Memory for recent partitions (newer dates), this would help us to have fast performing  reports for recent dates and keep data for recent dates in RAM and leave older data which are not accessed often to use DirectQuery.

Does my thought make sense? Is there any way we use Tabular databases this way?

Thanks for sharing your thoughts.

You are correct that currently (up through SSAS 2017 Tabular) is is not possible to have mixed mode partitions. The whole model can operate in DirectQuery or In-Memory. The DirectQueryMode connection strong property can specify whether SSAS should use DirectQuery or In-Memory for answering the current query. But that's not honestly too much better than just deploying the model to two different databases, one DirectQuery and one In-Memory.

There is a similar connection string DataView=Sample which has SSAS answer the query using a special cached partition marked as the sample partition.

But this is still not what you want because a particular query won't union results from DirectQuery partitions and sample In-Memory partitions.

I will try to update this answer if a future release of SSAS adds the feature you are wanting.


How do I drop the TIME portion from DATEADD? I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth column uses the VARCHAR data type.

/****** Generate Random Date For Each Row (Between 1900 - 2015) ******/
UPDATE CCCINC.dbo.Employees
SET DateOfBirth = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 41975), '1900-01-01');

Result:

Feb 28 1930 12:00AM

How do I remove the TIME portion from the result? Also, if I wanted to change the DATE format to YYYYMMDD how would I accomplish that?

Well, assuming that someone is holding a gun to your head, forcing you to not change the data type to something more sensible and logical:

UPDATE CCCINC.dbo.Employees
SET DateOfBirth = CONVERT(CHAR(8), DATEADD(DAY, ABS(CHECKSUM(NEWID())
  % 41975), '1900-01-01'), 112);

Strong, strongly, strongly suggest you change the data type of that column to use the right data type (DATE). Then you can just use your original query and not worry about the "format" at all. Format is what you worry about when you present the data, not when you store it.

Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries


Select distinct combinations of rows I try to select distinct combinations of rows (author_id, recipient_id), preserving following order and limit. Like on Facebook you have a list of recent conversations with given users. For example I'm a user with id=665 and I need a list of 20 recent conversations, 20 last distinct people a user with id=665 has talked. My current code:

select * from (
  select distinct on (author_id, recipient_id) * from private_message where author_id=665 or recipient_id=665
) p_m
order by id desc
limit 20;

In this case everything is ok besides that I want to keep bigger id (more recent), so message with id=10 needs to be drop, because it's duplicate of id=18.

So the expected result is:

If I get it right you would like the last row for each column combination (regardless of column order). Easiest is probably to enumerate the rows (using least, GREATEST for column order) in desc order and choose the first one

SELECT ... FROM (
    SELECT...
           , row_number() over (partition by least(author_id, recipient_id)
                                           , greatest(author_id, recipient_id)
                                order by updated_at desc) as rn
    FROM private_message
    WHERE 665 IN (author_id, recipient_id)
) as t
WHERE rn = 1
LIMIT 20


mysql dump import incredibly slow on my developer's machine I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in 40 minutes)
So this is factor 53 slower.

The specs:

Server A:
   MySQL Version: 5.5.30-1.1 (Debian)
   2 GB RAM
   1 core QEMU Virtual CPU version 1.0 - cpu MHz: 3400.020

Workstation B:
   MySQL Version: 5.5.41-MariaDB-1ubuntu0.14.04.1
   14 GB RAM
   4 cores Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz - cpu MHz: 1600.000

The mysql/maria config is the stock config.

I switched yesterday to MariaDB on my workstation - but before MariaDB the stats were even worse.

I already removed all databases on my workstation - no difference.

The big question is: How can the performance be factor 53 slower? I can not work like this :-(

My import command:

pv sql/master.sql | mysql -h'localhost' -u'root' -p'root' 'master'

iostat -xm 5

server A:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
      17,43    0,00   30,28   51,85    0,00    0,44

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00   254,03    0,00 1305,45     0,00     6,09     9,56     0,78    0,60    0,00    0,60   0,57  74,25

workstation B:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
       7,32    0,00    3,22    5,03    0,00   84,42

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     1,40    0,80  172,40     0,00     0,56     6,72     1,17    6,75   12,00    6,72   5,40  93,52

dd if=/dev/zero of=tempfile bs=1M count=1024 conv=fdatasync,notrunc

server A:

1073741824 bytes (1,1 GB) copied, 18,6947 s, 57,4 MB/s

workstation B:

1073741824 bytes (1,1 GB) copied, 8,95646 s, 120 MB/s

This answer speeded up everything a lot:

https://stackoverflow.com/a/2167641/292408

I simply

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

at the beginning, and

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

at the end.

Now it took 3 minutes.

(Courtesy of @andreasemer via twitter)


Restoring Postgresql database from WAL without backup I am trying to restore data in my database. How can I restore data from WAL without any database backups? Can I generate a backup file from WAL logs?

If you only have the WAL, with no base backup (copy of the data directory, pg_basebackup, etc), you cannot restore. Full stop. And no, you cannot use a dump from pg_dump to restore WAL on top of.

WAL only contains changes to the data directory, and is meaningless without a base backup to apply it to.

Imagine you have a half page from one of your bank statements, without a running balance shown in a column. You want to use it to find out the balance on your account. You cannot possibly do that, since you don't know the starting or ending balance. Same issue here.


Based on the result from the table, get the result from another table I have two tables users and products:

1) users:

 id |  username    | ... |
--------------------------
 1  | user1name 1  | ... |
 2  | user2name 2  | ... |
 3  | user3name 4  | ... |
 4  | user4name 5  | ... |

2) products:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |
 3 |  product3name 3 |    2    |
 4 |  product4name 4 |    3    |

I want to get products based on the result from the users table. I have a user table connection with a product table.

My request:

SELECT P.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.USER_ID)
            WHERE LOWER(U.USERNAME) LIKE LOWER('%use1r%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID

What answer I expect to receive:

id |  product_name   | user_id |
--------------------------------
 1 |  product1name 1 |    1    |
 2 |  product2name 2 |    1    |

You need to move the condition on the username into the JOIN condition. Applying a WHERE condition on the outer joined table turns your outer join back into an inner join.

Additionally - given your sample data - %use1r% won't match any row, I assume you meant to write %user1%. As you query only returns columns from the products table, I think you meant to use p.user_id in the group by as ell to remove duplicate products that stem from the join.

SELECT p.*
FROM products AS p
  LEFT JOIN users AS U ON u.user_id = p.user_id
                      AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your expected output, it seems you want an inner join as you only want products assigned to the matching user.

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
                 AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

With an inner join it doesn't matter where you put the condition on the username. The above is equivalent to:

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
WHERE LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your usage of the group by I wonder if you are trying to remove duplicate products from the result. In that case an EXISTS query is maybe what you really want

SELECT p.*
FROM products AS p
WHERE EXISTS (SELECT *
              FROM users u
              WHERE u.user_id = p.user_id
                AND LOWER(u.username) LIKE LOWER('%use1r%'));

Then you don't need to remove unwanted duplicates with a group by


MySQL connection timeout on connection spike I have a little bit difficult problem, whenever a spike of connection occurs our MySQL server, connecting from all sources to the MySQL is practically impossible, the max_connections setting is not reached, load is ok 2-3 on a total 48 core.

My suspicion is the back_log and OS back_log, and my question is should i decrease or increase the back_log / os back_log ? or what am i doing wrong

Server info:

  OS Debian GNU Linux 7.7
  MySQL 5.6.16-enterprise-commercial-advanced-log
  CPU 2 x Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz
  256 GB DDR3 @ 1866Mhz ECC
  RAID 10 (6 x 1.2 TB SAS 10k)

MySQL settings

  back_log = 2000 max_connections = 61440 max_connect_errors = 256
  key_buffer_size = 2048M table_open_cache = 32768 sort_buffer_size = 8M
  read_buffer_size = 8M read_rnd_buffer_size = 32M ft_min_word_len = 4
  thread_stack = 256K max_allowed_packet = 1024M bulk_insert_buffer_size
  = 64M thread_cache_size = 512 query_cache_size = 128M thread_concurrency = 48 innodb_read_io_threads=64
  innodb_write_io_threads=64 innodb_checksum_algorithm=INNODB
  binlog_checksum=NONE tmp_table_size = 128M max_tmp_tables=20480
  max_heap_table_size = 64M join_buffer_size = 128M
  innodb_buffer_pool_size = 160G innodb_thread_concurrency = 0
  open-files-limit = 655360 innodb_open_files = 655360
  plugin-load=thread_pool.so thread_pool_size = 16

OS settings:

  net.core.netdev_max_backlog = 20480
  net.ipv4.tcp_max_syn_backlog = 20480

P.S Please do not ask or leave links regarding to tutorials or mysql documentation, for i have done many tests and this is a question suited for Rolando or persons with strong mysql background.

Thanks,

What caught my eye immediately was the following

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
join_buffer_size = 128M

The sum of these buffer sizes is 176M.

This would be the maximum amount of RAM used per connection.

If you multiply that by your max_connections (61440) you get

11331331686400 Bytes
11065753600 KB
10806400 MB
10553.125 GB
10.3057861328125 TB

This means that you would need to have 10.5 TB of RAM to accommodate 61440 open connections.

I have mentioned this formula before

May 09, 2013 : Should I increase max_connections in AWS RDS t1-micro for MySQL?
Mar 28, 2014 : MySQL not releasing memory

Here is a query you can run to see these metrics

SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) BBB) AA) A,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) B;

You can run this one to test out changing max_connections (Example: What if you wanted to lower max_connections to 76):

SET @conn = 76;
SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT @conn maxconn) BBB) AA) A,
(SELECT @conn maxconn) B;

YOUR CURRENT SETTINGS

Let's compare how much RAM can potentially be consumed given your current settings

Look at your InnoDB Buffer Pool (innodb_buffer_pool_size). It's 160G.
Look at your MyISAM Key Cache (key_buffer_size). It's 2048M or 2G.
256G - 160G - 2G = 94G available to the OS
94G / 176M (per connection) = 546 Connections
You will never reach 546 connections because mysqld is competing with the OS for RAM

SUGGESTIONS

You need to lower your max_connections so that it does not eat the 94G of leftover RAM.
You may need to consider lowering join_buffer_size if your joins are fast.
You could probably lower read_buffer_size and read_rnd_buffer_size if you are not reading from MyISAM tables.

GIVE IT A TRY !!!


Update end date by adding duration to start date in derby database I created table using Derby database.
CREATE TABLE PROJECT
(PID  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
PSDATE DATE,
PDURATION INTEGER,
PEDATE DATE,
PRIMARY KEY (PID)
);

I attempted to update end date in this project table, adding duration to start date.
UPDATE PROJECT
SET PEDATE = ADD_MONTHS (PROJECT.PSDATE, PROJECT.PDURATION)
WHERE PID = PROJECT.PID;

But I am getting below error
"[Exception, Error code 30,000, SQLState 42Y03] 'ADD_MONTHS' is not recognized as a function or
 procedure.
 Line 1, column 1"

Later I tried with below code
select {fn TIMESTAMPADD(SQL_TSI_DAY, +pduration, psdate)}
from name.PROJECT
where pid = project.PID

Can I use TIMESTAMPADD function to update project table in Derby database?
Thanks in advance to help me to solve this problem.

UPDATE PROJECT
SET PEDATE = DATE( {fn TIMESTAMPADD(SQL_TSI_MONTH, PDURATION, PSDATE)} );

This query (provided by Akina) updates my end date when adding duration to start date.


mysql auto adjusting max connections values I have set max connections to around 2000 in my.cnf file.

max_connections=2048

I have upgraded to 5.5.20 and now I see the following line in the error log.

120201 19:40:24 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

Why does mysql changed the max connections value to 214 ?

# mysqladmin variables | grep max_connections
| max_connections | 214

Soft and hard open files restricted by OS is 1024

# ulimit -Sa | grep "open files"
open files                      (-n) 1024

# ulimit -Ha | grep "open files"
open files                      (-n) 1024

The number of actually used max connections:

# mysql -e"show status like '%used_connections%'"
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 95     |
+----------------------+-------+

It seems OK to me. You cannot have max_connections larger than that, because you open_files_limit is too low. For every connection MySQL opens several files. Take a look here.

So, you will have to increase open files limit on your OS, and afterwards, you can put higher max_connections value.


MySQL: Storing unique URLs I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If not create a new row with this URL.

Obviously I want this to be fast. What is the best option?

Make the actual URL a varchar that is UNIQUE and look by this url?
Make a hash of the URL and use it as a primary key of sort?
Other ideas?

I would definitely go with a hash of the url and make the hash a unique index. A hash has a fixed length, so you can use CHAR to specify the length of the column, which grants a slight performance boost over VARCHAR or TEXT.

But might I suggest using INSERT IGNORE instead of making two calls to the database? Something like:

INSERT IGNORE INTO urlTable VALUES ('urlHash');

This has the benefit of ignoring any duplicate errors that might arise from attempting to insert a duplicate hash, without first having to do a SELECT COUNT(*) query.


Parse error in pg_hba.conf Hope this is not as stupid a question as I think it is. But I'm in a loop and unable to find a solution.

I installed Postgres in centos machine. The installation worked fine (apparently). Now I have 3 Linux users - root, admin and postgres. In order to allow all connections I updated the pg_hba.conf file with necessary changes. Now, if I try to restart (from root or postgres user), I get below error:

-bash-4.2$ systemctl start postgresql-9.5.service
  ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
 Authentication is required to manage system services or units.
 Authenticating as: Admin (admin) Password:
 ==== AUTHENTICATION COMPLETE === Job for postgresql-9.5.service failed
 because the control process exited with error code. See "systemctl status
 postgresql-9.5.service" and "journalctl -xe" for details.

Please find below outputs:

-bash-4.2$ journalctl -xe
Hint: You are currently not seeing messages from other users and the system.
  Users in the 'systemd-journal' group can see all messages. Pass -q to
  turn off this notice. No journal files were opened due to insufficient permissions.

-bash-4.2$ systemctl status postgresql-9.5.service
 ● postgresql-9.5.service - PostgreSQL 9.5 database server    Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled; vendor preset: disabled)    **Active: failed** (Result: exit-code) since Wed 2018-07-25 12:58:51 IST; 1min 26s ago
 Docs: https://www.postgresql.org/docs/9.5/static/   Process: 109849 **ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)**   Process: 109843 ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)  Main PID: 102339 (code=exited, status=0/SUCCESS)

I searched further and various links (most notably this ) pointed to the fact that I needed to run something like the below, which I did:

  /usr/pgsql-9.5/bin/postgresql95-setup initdb

But that would give error:

  Data directory is not empty!

The only way out of this is to delete the directory /var/lib/pgsql/9.5/data completely, which would leave me with no DB and no pg_hba.conf

pg_hba.conf:

Relevant error messages:

2018-07-25 12:53:06.218 IST >LOG: invalid authentication method "0.0.0.0/0" <
2018-07-25 12:53:06.218 IST >CONTEXT: line 80 of configuration file "/var/lib/pgsql/9.5/data/pg_hba.conf" <
2018-07-25 12:53:06.218 IST >FATAL: could not load pg_hba.conf <
2018-07-25 12:58:50.617 IST >LOG: invalid authentication method "0.0.0.0/0" <
2018-07-25 12:58:50.617 IST >CONTEXT: line 80 of configuration file "/var/lib/pgsql/9.5/data/pg_hba.conf" <
2018-07-25 12:58:50.617 IST >FATAL: could not load pg_hba.conf

From the documentation:

  A record can have one of the seven formats

  local      database  user  auth-method  [auth-options] host
  database  user  address  auth-method  [auth-options] hostssl
  database  user  address  auth-method  [auth-options] hostnossl
  database  user  address  auth-method  [auth-options] host
  database  user  IP-address  IP-mask  auth-method  [auth-options]
  hostssl    database  user  IP-address  IP-mask  auth-method
  [auth-options] hostnossl  database  user  IP-address  IP-mask
  auth-method  [auth-options]

Your line:

local all all 0.0.0.0/0 md5

.. does not match any of these.

I suspect you want one of these instead:

host all all 0.0.0.0/0 md5

or:

local all all md5


How to do Partition for MySQL 100GB table in zero downtime? I have a Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime?

In 100GB table the records are increasing for each and every seconds.How can I do partition with out affecting the incoming records?

Using Percona-Mysql 5.5.30.Innodb-file-per-table was enabled.

Please tell me the steps.

You may use one of the online-alter-table tools: these are specialized scripts which simulate an ALTER TABLE operation, using TRIGGERs and shadow tables, and which allow for non-interruptive process. Thus, they will copy your table in small chunks into a shadow table even while you are modifying (inserting, deleting, updating) your table, synching the two.

They end with a very brief (up to a few seconds, typically sub second) lock, where your original table is thrown away and the "shadow" table kicks in to replace it.

Two such tools are:

oak-online-alter-table, as part of the openark-kit (disclosure: I'm author of this tool). Been around since 2009 and the first of its kind.
pt-online-schema-change, as part of the percona-toolkit; later arrival but more sophisticated and more tested.


Table Not Created On All MySQL Masters Here is the multi master setup.
Table is not created on middle master.

log-slave-updates is not enabled in my.cnf file.

Master 1

mysql> create database replication;
mysql> use replication;
mysql> CREATE TABLE T1 (ID INT Not Null) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin;

Master 2

mysql> use replication;
mysql> select * from T1;
ERROR 1146 (42S02): Table 'replication.T1' doesn't exist

Master 3

mysql> use replication;
Database changed
mysql> select * from T1;
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

@Michaelsqlbot's comment makes it easy to see the issue

Replication must be going

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M3 -> M2-> M1 -->--+

or

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M1 -> M3-> M2 -->--+

and M2 does not have log-slave-updates

Given that topology, here is the sequence of events that must have taken place

You created table T1 on M1
M3 has table T1
M2 does not have table T1 because M2 does not have log-slave-updates
You inserted 2 into table T1

If the INSERT was done on M1, M3 has data, M2 has no data and no table T1
If the INSERT was done on M3, M1 has no data, M2 has no data and no table T1

EPILOGUE

Configuring log-slave-updates tells a Slave to take a binlog event from its relay logs, execute it, and record the event in its local binlogs (if log-bin is enabled on the Slave). Thus, M2 not having log-slave-updates enabled in your MultiMaster setup is the root cause.

Big thanks to @Michaelsqlbot for the hint.


MySQL Replication - Resync master after slave taking over A few days ago, we've had to stop every machine for some maintenance. Two of these were a MySQL master-slave. After the restart, the master didn't get the service back online and, due to the critical nature of the database, the slave took over, basically functioning as a normal, non-replicated service.

There were no changes to any of the my.cnf configuration files. The slave received various UPDATE and INSERT commands, so what will happen if I simply start the master back again? Will it manage to sync back? If not, how should I proceed?

If the Master was already acting as a Slave, then you really had a Master-Master setup, and the replication from the old Slave (current Master) will flow to the old Master that had been down.

Otherwise, you are in deep weeds.  You may as well promote the currently-acting-as-Master machine to be the Master.  And then rebuild the other machine as a Slave.  Or rather a Master in Master-Master setup so you don't hit this problem again.


Return even empty groups when grouping by date I have the following query:

SELECT DISTINCT
           COUNT(CD_BarCode)
           ,CD_Carrier
           ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date
FROM TB_AGIL
WHERE
           DT_Arriving >= @date
           AND DT_Arriving < DATEADD(MONTH,+1,@date)
           AND CD_TRACKING = 14
GROUP BY CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11)
ORDER BY Date

I use this to see the results from last month's cargo movimentation. The results are a list of cd_tracking amount grouped by date like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

My problem is that if there is no cd_tracking = 14 on a given day, that day will not show on the result list. As you can see, there is no Aug 2nd on the list.

I'd like to know if there is a way to bring every day on the result list, even if there is no cd_tracking = 14 on that day. Like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  NULL  |   44    | 02/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

Move the condition from the WHERE clause to a conditional count:

SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY
    CD_Carrier,
    DATEPART(DAY, DT_Arriving)
ORDER BY
    [Date];

Other approaches to implement the deprecated GROUP BY ALL syntax can be found here.


Drop and restore PostgresSQL database without losing settings This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted (there were errors in the Write-Ahead Log) and I'm trying to dump, re-initialize, and restore it to get it back in a stable state.

However, it seems like initdb, the recommended way to create a fresh stable database, actually creates an entire database cluster. In order to run this command, I'd first have to drop my database cluster, but the documentation for the pg_dropcluster command says it removes "all files that belong to a given PostgreSQL cluster; that includes the data directory, the log file, and all configuration files." I don't want to delete the logs and configuration files, just the data.

Since there's only one user-created database in my cluster (i.e. the one I'm trying to restore), I could just connect to the cluster, drop that database, and recreate it from the dump. But then I wouldn't be running initdb, and maybe that would leave my database cluster in some corrupted state.

Is there a way to drop and restore the data contents of a cluster without deleting all of the logs and configuration settings? Alternatively, is it OK to just drop and restore the database to recover it from corruption?

If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.

If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.

As the configuration files are just plain text (as are the logfiles) you can simply copy the backup over into the new data directory after running initdb.

(Note: I don't know what pg_dropcluster does. It is not a standard Postgres tool)


MySQL user account 'From Host' setting I've just installed Wamp server 2 and I get the following 4 default MySQL users:

    USER         FROM HOST
    <anonymous>  localhost
    root         localhost
    root         127.0.0.1
    root         ::1

What does the ::1 mean?

I think that is the new way to express home based on the IPv6 protocol. It's new to me as well.

By this, my guess is your WAMP installed MySQL 5.5.

Just remember two things

There is no place like 127.0.0.1
There is no place like ::1


Unpivot with rows converted to columns as well as columns converted to rows I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows.   To try and illustrate what I mean I have included a picture...

I believe that the data in a given column needs to be of the same type,  so if that's the case then it's ok to have all the columns as varchar.   The data in the source table will not need to be part of any sums,  just displayed as-is.  I am just trying to 'rotate' this to display the data in a vertical way for end-user consumption.

This query uses both UNPIVOT and then PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
    FROM @data
) d
UNPIVOT (
    [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

It first unpivots your data in order to get a regular table
It then pivots this table back to the new required format

I have to cast everything as varchar. Based on your data model, you may have to cast them to another type. An aggregate function is required by PIVOT. Since this is a 1 to 1 match, it works with either of MAX or MIN.

With SQL Server >= 2008 you can also replace UNPIVOT by Table Value Constructor and CROSS APPLY along with PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT v.[Type], [value], [Data]
    FROM @data d
    CROSS APPLY (values
        (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
        , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
        , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
        , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
    ) as v([type], [value], [Data])
) unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

Without PIVOT/UNPIVOT you can use this query with any (old) version of SQL Server:

SELECT [Data]
    , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
    , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
    SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

Output:

Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

Data:

Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
    ('x', 123456, '$500', 6, 30)
    , ('Y', 654321, '$200', 36, 90);


Kerberos authentication not working with Linked Servers in SQL Server 2012 I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012.  We are moving from SQL Server 2005 on Windows Server 2008, where we already have this up-and-running correctly.

In SQL Server 2012, Kerberos authentication is not working.

Each server has it's own Active Directory account that has the "Write Service Principal Names" and "Read Service Principal Names" rights granted through Active Directory Users and Computers.  Whenever I connect to the SQL Server 2005 servers, and run:

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

I see:

net_transport    auth_scheme
TCP              KERBEROS

When I perform the same query against my new SQL Server 2012 instances, I see:

net_transport    auth_scheme
TCP              NTLM

If I use SetSPN -Q MSSQLSvc/* to query Active Domain for Service Principal Names, I see both the 2005 and 2012 servers listed, exactly the same way other than the name of the server.

For instance:

MSSQLSvc/SERVERa2005.domain.inet
MSSQLSvc/SERVERa2005domain.inet:1433
MSSQLSvc/SERVERb2005.domain.inet
MSSQLSvc/SERVERb2005domain.inet:1433
MSSQLSvc/SERVERa2012.domain.inet
MSSQLSvc/SERVERa2012domain.inet:1433
MSSQLSvc/SERVERb2012.domain.inet
MSSQLSvc/SERVERb2012domain.inet:1433

What else do I need to do to enable Kerberos Authentication to SQL Server 2012?  Books Online seems to have nothing else to say, except that the SPN's have to be setup.  Which clearly, they are.  SQL Server Error logs on both 2012 machines say:

2012-12-10 14:55:47.630 The SQL Server Network Interface library
                            successfully registered the Service Principal Name (SPN)
                            [ MSSQLSvc/SERVERa2012.domain.inet ] for the SQL Server
                            service.

2012-12-10 14:55:47.630 The SQL Server Network Interface library
                            successfully registered the Service Principal Name (SPN)
                            [ MSSQLSvc/SERVERa2012.domain.inet:1433 ] for the SQL
                            Server service.

2012-12-10 14:55:47.590 SQL Server is attempting to register a Service
                            Principal Name (SPN) for the SQL Server service.
                            Kerberos authentication will not be possible until a
                            SPN is registered for the SQL Server service. This is an
                            informational message. No user action is required.

Dealing with Active Directory is always so much fun.  The single most important thing here is to realize you are dealing with distributed data that can take time to propagate across your network.

The SQL Servers in question had their name changed as part of an upgrade procedure; we replaced an existing machine (SQL01) running SQL Server 2005 with a new machine (SQL03) running SQL Server 2012.  SQL03 was the name of the new machine when I initially set it up in the domain.  SQL01 had an existing SPN associated with a single domain account we used for several SQL Servers running 2005.  Since it is a best practice to only run a single machine under any given domain account, I created a new account and configured SQL03 to run with that account name.  After taking the original SQL01 out of service, and renaming SQL03 to SQL01, there was an SPN conflict.

I used the SetSPN.exe utility to delete the conflicting SPN (on the old domain account) - and it still didn't work.  At this point, I did nothing further and moved on to other items.  When I came back around 30 minutes later, KERBEROS authentication was working.  I simply needed to wait for the SPN change to propagate among our domain controllers.

I used SetSPN -L DOMAIN\Account and compared that output with SetSPN -Q MSSQLSvc/Machine.domain.inet:1433 to find the duplicate SPNs, and then used SetSPN -D MSSQLSvc/Machine.domain.inet:1433 DOMAIN\Account to remove the old SPNs.


sql-server optimize query with lots of null checks I have a query such as:

select something
from somewhere
join stuff on condition
join more_stuff on condition
where somewhere.sometime is null
and somewhere.someone is null
and stuff.whatever is null

and so on.

I try nonclustered group indexing somewhere.sometime and somewhere.someone, the query slows down. I read somewhere that MSSQL keeps a bitmap of nulls for each row.

The question is, how to speed up the query so that access to rows with the specified null values is faster and indexed? (so you only scan rows with the specified nulls, not full table scan) Can I somehow take advantage of this null bitmap ?

NULL bitmap is indeed used for query optimization that involve columns with NULL values, but there are a few considerations here:

NULL bitmap is not present on nonclustered indexes (you'll see why), on the other hand it is always present on the leaves of clustered indexes, and heap tables.
NULL bitmap cannot be built for index records, but exclusively for data records (this is why you cannot have it for nonclustered indexes). That means you can have a NULL bitmap either on the table (organized as heap) or on a clustered index (leaves).
NULL bitmap is not present for tables with only SPARSE columns.

You should check if you have a NULL bitmap indeed. My guess is that yours is missing. You could use DBCC to check the record attributes:

Open a query window and issue:

DBCC IND (DATABASE_NAME, 'TABLE_NAME', -1)
DBCC TRACEON (3604)
DBCC PAGE (DATABASE_NAME, PageFID, PagePID, 3)
DBCC TRACEOFF (3604)

where PageFID and PagePID are displayed by the first command DBCC IND then check for the Record Attributes and check if NULL_BITMAP is present.

If there isn't any, then create a clustered index on the table, and check again.
You could also give us the execution plan, and the actual definition of the table (with dummy column names if confidential).


What does this error mean "A slave with the same server_uuid/server_id as this slave has connected to the master;"? Upon searching for this, the main cause of this error is usually when using snapshots or cloning the machine. But I did not do this. I perform mysqldump --master-data=2 on the master then restore the dump on my slave server. Then start the slave on the position indicated on the outfile.
Any idea on what caused this? I am running a multi-source replica and this error is from just 1 source.
here is the complete error:
Got fatal error 1236 FROM MASTER WHEN reading DATA FROM BINARY LOG: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'VM1-bin.000006' at 641809774, the last event read from '/binlogdata/VM1-bin.000009' at 146836375, the last byte read from '/binlogdata/VM1-bin.000009' at 146836375.';

You must have a different value in the my.cnf on each server in a replication setup.  server_id must be different on each machine, else replication will be trying to go in a loop.


Is it possible to change ER diagram style in MySQL Workbench? How? I am really new to database visualization. I am creating the ER diagram of my databases. I am using MySQL Workbench 6.3 (community version). I am creating the diagram using the default style. The style looks like this:

Is it possible to change the style to be like the image below?

If yes, How can i do this?
or if you have another database designer that can create diagram like the second style, it would help.
Thank you!

You can change the look of the diagrams a bit, evn though not exactly as shown in your sshot. Under the model menu there are 2 submenus for object and relationship notations:

The result looks then like this:


Optimize index on a 2,135,044,521 row table I have an I/O problem with a large table.

General stats

The table has the following main characteristics:

environment: Azure SQL Database (tier is P4 Premium (500 DTUs))
rows: 2,135,044,521
1,275 used partitions
clustered and partitioned index

Model

This is the table implementation:

CREATE TABLE [data].[DemoUnitData](
    [UnitID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Value1] [decimal](18, 2) NULL,
    [Value2] [decimal](18, 2) NULL,
    [Value3] [decimal](18, 2) NULL,
    CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED
    (
        [UnitID] ASC,
        [Timestamp] ASC
    )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

The partitioning is related to this:

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

Quality of service

I think the indexes and statistics are well maintained every night by incremental rebuild/reorganize/update.

These are the current index stats of the most heavily used index partitions:

These are the current statistics properties of the most heavily used partitions:

Problem

I run a simple query on a high frequency against the table.

SELECT [UnitID]
    ,[Timestamp]
    ,[Value1]
    ,[Value2]
    ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

The execution plan looks like this: https://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

My problem is that these queries produce an extremely high amount of I/O operations resulting in a bottleneck of PAGEIOLATCH_SH waits.

Question

I have read that PAGEIOLATCH_SH waits are often related to not well-optimized indexes. Are there any recommendations you have for me how to reduce I/O operations? Maybe by adding a better index?

Answer 1 - related to comment from @S4V1N

The posted query plan was from a query I executed in SSMS. After your comment I do some research on the server history. The accual query exceuted from the service looks a bit different (EntityFramework related).

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))

SELECT 1 AS [C1], [Extent1]
   .[Timestamp] AS [Timestamp], [Extent1]
   .[Value1] AS [Value1], [Extent1]
   .[Value2] AS [Value2], [Extent1]
   .[Value3] AS [Value3]
FROM [data].[DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1)

Also, the plan looks different:

https://www.brentozar.com/pastetheplan/?id=H1fhALpKG

or

https://www.brentozar.com/pastetheplan/?id=S1DFQvpKz

And like you can see here, our DB performance is hardly influenced by this query.

Answer 2 - related to answer from @Joe Obbish

For testing the solution I replaced Entity Framework with a simple SqlCommand.
The result was an amazing performance boost!

The query plan is now the same as in SSMS and the logical reads and writes drop to ~8 per execution.

The overall I/O load drop to almost 0!

It also explains why I get a big performance drop after I changed the partition range from monthly to daily. The missing of partition elimination resulted in more partitions to scan.

You might be able to reduce PAGEIOLATCH_SH waits for this query if you're able to change the data types generated by the ORM. The Timestamp column in your table has a data type of DATETIME but the parameters @p__linq__1 and @p__linq__2 have data types of DATETIME2(7). That difference is why the query plan for the ORM queries is so much more complicated than the first query plan that you posted that had hardcoded search filters. You can get a hint of this in the XML as well:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))">

As is, with the ORM query you cannot get any partition elimination. You will get at least a few logical reads for every partition that's defined in the partition function, even if you're just searching for a day of data. Within each partition you get an index seek so it doesn't take long for SQL Server to move on to the next partition, but perhaps all of that IO is adding up.

I did a simple reproduction to be sure. There are 11 partitions defined within the partition function. For this query:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

Here's what IO looks like:

  Table 'DemoUnitData'. Scan count 11, logical reads 40

When I fix the data types:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

IO is reduced as a result of partition elimination:

  Table 'DemoUnitData'. Scan count 2, logical reads 8


Why is cost threshold for parallelism ignored? SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.

Instance settings

MAXDOP: 8
Cost Threshold for Parallelism: 175

Ran sp_BlitzCache (from the Brent Ozar toolset, those guys rock) for further diagnosis and results show queries with cost under 175 going parallel.

Anyone ever see Cost Threshold for Parallelism being ignored?

Anyone ever see Cost Threshold for Parallelism being ignored?

It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be chosen.

So, the parallel plan will have a lower cost that the serial one (which you cannot see). It is perfectly possible for the final parallel plan to have an estimated cost below the Threshold - the point is the best serial plan candidate exceeded the Threshold.

An example can be seen in my blog post on parallel plan bitmaps.


Can I use Oracle Standard or Enterprise edition for my own training purposes? Question is about Oracle licensing terms.

I would like to learn more about Oracle Database administration. Unfortunatelly I am using 32 bit verison of LInux, so I can't install Oracle 11 XE (I can't find 32-bit edition though).

Is it OK, if I download and install Oracle Standard or Entrprise on my personal notebook, and use it with no additional costs and hidden license issuses, is it even possible? I won't be using it for any commerce, even open-source project - I will only be using this installation to improve my DBA skills.

Yes, that's fine:

  All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes.

The full Developer License can also be found on OTN, including:

  Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, prototyping, and demonstrating Your application and only as long as Your application has not been used for any data processing, business, commercial, or production purposes, and not for any other purpose.

As with any licensing question, I'd suggest you read Oracle's info carefully including the full license and satisfy yourself that what you want to do is OK rather than just taking my word for it.


Can Cassandra be used to increase database write rate? I am developing a database for an analysis job that requires parsing terabytes of information into a database. My job is to create and populate a database and using it to answer analyst's questions. In the course of such questions, we identify deficiencies in the way that the data is being represented in the database. I then have to alter the database structure, which often involves re-parsing and re-reading everything into the database.

The parsing is time consuming, but straightforward to parallelize; since I got a 48-node cluster it doesn't take very long any more. However, I started the project using MariaDB (frankly, no one knew the data-sets would get so big). The big time holdup is now writing into the database: billions of writes every time I modify the database taking over 24-hours with 2 months of collected data. Since we will be gathering data for many more months and years, I can see where the write requirements will soon become prohibitive.

I have to make recommendations about how/if we will expand the database in the future. However, I know next to nothing about distributed databasing. I would like to know: will switching to a distributed database be expected to decrease the write time by increasing the number of nodes to which I can write.

Regarding the data itself, it is designed with an indexed master table (~10^7 rows as of now) and several un-indexed subsidiary tables designed to be joined with the master (~10^9 rows), if that is relevant.

Just so you know, because the database supports strategic analysis and report generation, and not real time information, there is not as much incentive to speed up database read times. Leaving the data un-indexed and accepting long join times is just fine; report generation runs overnight. But the long time for database re-building is starting to chew up whole work days, which is not as fine.

...be expected to decrease the write time by increasing the number of nodes to which I can write.

Yes.  While the article is dated, Netflix performed a scalability experiment back in 2011, as detailed here:

Benchmarking Cassandra Scalability on AWS - Over a million writes per second

Essentially, they found that Cassandra was indeed linearly scalable.  That is, the amount of ops/second that your cluster can sustain is directly proportionate to the number of nodes in your cluster.

  the database supports strategic analysis and report generation, and not real time information

This is the part that makes me pause.  Cassandra is typically not a good fit as an analytics back-end.  The reason for this, is that (due to its distributed nature) your table structures must be modeled according to your read queries.

Essentially, that means one table serves one query.  Additional queries requiring different columns in the WHERE clause, typically have to be served by different tables hosting duplicate data.  This can quickly become cumbersome in an OLAP environment.

So while Cassandra's masterless, distributed architecture can help scale your writes, your reads quickly lose flexibility.