Show sql statements from mysql binlog (GTID) I have mysql binlogs  for replication through GTID.

I'm trying to show executed update/insert statements like this:

mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024

But all I see is something like this, no traces of update or insert stmt:

SET TIMESTAMP=1431681617/*!*/;
BEGIN
/*!*/;
# at 746987321
# at 746987392
# at 746987484
#150515 11:20:17 server id 1  end_log_pos 746987515 CRC32 0xeb874754    Xid = 997501767
COMMIT/*!*/;
# at 746987515
#150515 11:20:22 server id 1  end_log_pos 746987563 CRC32 0xc5ece64a    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'a4ade293-c63a-11e4-94cf-005056944a56:2059057'/*!*/;
# at 746987563
#150515 11:20:22 server id 1  end_log_pos 746987650 CRC32 0x92296355    Query   thread_id=71622 exec_time=0 error_code=0

I'm not sure about format/configuration of GTID replication and where to look for it...

It seems that the option  --verbose must be added:

mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005

In the result you see:

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

@x are the table columns in their order

I prefer to use

mysqlbinlog -v --base64-output=DECODE-ROWS /files/

because:

"If no --base64-output option is given, the effect is the same as --base64-output=AUTO"
"The combination of --base64-output=DECODE-ROWS and --verbose provides a convenient way to see row events only as SQL statements" (it surpresses the BINLOG statements)

"Specify --verbose or -v twice to also display data types and some metadata for each column."

Source of quotes: man mysqlbinlog


Resource Hosting Subsystem was terminated which caused Availability group to fail The following error was occurred in cluster events and the availability group was failed which resulted the databases in non-synchronizing state.

A component on the server did not respond in a timely fashion. This caused the cluster resource 'AG' (resource type 'SQL Server Availability Group', DLL 'hadrres.dll') to exceed its time-out threshold. As part of cluster health detection, recovery actions will be taken. The cluster will try to automatically recover by terminating and restarting the Resource Hosting Subsystem (RHS) process that is running this resource.

Please help me to find the root cause (A component on the server did not respond in a timely fashion).

That error means the AG failed one of the health detection timeouts (lease, session, or health check) summarized in this table from the docs:
Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups - Summary of Timeout Guidelines
Your first stop should be to review the SQL Server error log on each node in the AG, to see if there was something going on that might have caused the instance to stop responding to the cluster AG resource.  For instance, you might have crash dumps, deadlocked schedulers, etc.
To dig into the cluster-related details, you'll need to call the PowerShell Get-ClusterLog cmdlet (see here) to get the cluster log file from each node in the AG.  Then find the time associated with the failure error message you mentioned in the question.  Review the cluster log files around that time period for errors (search for "ERR") or any messages of interest from the AG resource DLL itself (which will include "[RES]" or "[hadrag]").
I would expect you'll find messages that include "timed out" or "IsAlive" showing the timing of the failures.  At this point, you should be able to figure out which server was not responding, and investigate that directly.
Figuring these things can be difficult.  Microsoft support has released a tool that can be used to categorize some types of failovers, which might be useful to you:
Failover Detection Utility - Availability Group Failover Analysis Made Easy

Unfortunately I couldn't find much information out there but you might want to review this article on Failover Clustering by Microsoft (it's seemed to help other people with similar issues, and goes pretty in depth): https://techcommunity.microsoft.com/t5/failover-clustering/failover-clustering-networking-basics-and-fundamentals/ba-p/1706005


How to grant permissions on a table-valued function Am I doing it right...?

I have a function that returns money...

CREATE FUNCTION functionName( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  RETURNS money AS BEGIN

  DECLARE @v_dint money   set @v_dint = computation_here
     set @v_dint = round(@v_dint, 2)

  RETURN @v_dint
END
GO
Grant execute on functionName to another_user
Go

Im just wondering if this is possible to be converted to iTVF?

I've tried doing this but I got an error:

CREATE FUNCTION functionName ( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )
RETURNS TABLE AS
RETURN SELECT returnMoney = computation_here
GO
Grant execute on functionName to another_user  Go

ERROR:

  Msg 4606, Level 16, State 1, Line 2
      Granted or revoked privilege EXECUTE is not compatible with object.

This function is used like this:

update table_name set interest = functionName(col1,col2...) where...

Thanks in advance!

Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT.

You must now GRANT SELECT ON functionName TO another_user;

From BOL:

  Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it.

I tried to use:

GRANT SELECT ON functionName TO another_user

But it didn't work, then, I used EXECUTE instead of SELECT, and it works now


SQL Server Agent Job failure We have a log backup job running every hour. The log backups are being taken for all the databases except one of them. For security purposes lets call this database FAILED_DB.
These are the things I have tried:

I looked at the history of the job and it has been failing since Friday.

In the job history, it states that Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). However, FAILED_DB is set to Full recovery model & there has been a full backup taken last night.

In the SQL Server logs, it states BACKUP failed to complete the command BACKUP LOG FAILED_DB. Check the backup application log for detailed messages.

I also checked the Event Viewer, and it still states to check the backup application log for detailed message.

Has anyone ever faced this error before? And if so, what was your way to resolve this? Any pointers would be much appreciated.

You included the following in your question:

Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).

The error you're seeing (error 4214) indicates that there has been no current database backup performed.
This is the actual text of that error message:

BACKUP LOG cannot be performed because there is no current database backup.

I was able to look that up using this query:
SELECT m.message_id
    , m.severity
    , m.text
FROM sys.messages m
WHERE m.message_id = 4214
    AND m.language_id = 1033; --Show the English version of the message

This means you need to ensure you take a full database backup in order for the BACKUP LOG to complete without error.
If, by chance, you changed the recovery model of the database from simple recovery to full recovery since the last full backup occurred, you won't be able to do a BACKUP LOG until you take another full database backup.  Changing the recovery model doesn't actually take effect until a full database backup completes.  The following excerpt was taken from the Microsoft Docs:

Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

One reason for this error is that there is insufficient space on disk to complete the backup.
Either redirect the backup to a different drive with more space or add space to the disk and verify that it can complete.


PowerShell script to access Oracle system views I am using PowerShell script to access oracle 11g's system views
as  SELECT COUNT (*) AS COUNT FROM v$session but I keep getting "ORA-00942: table or view does not exist"

I have created a user with sysdba privilege and all connections are a fine as I can even query other schemas without errors, but I only get error when I access any system views. can anyone tell me what I am doing wrong?

I also gave permission as "GRANT SELECT on v_$session TO mynewuser even tho I know its not needed if the user has sysdba.

What powershell syntax are you using?

If you do not enclose the select between single quotes, Powershell will treat it like a variable. If that's the problem, please try:

$command.CommandText = 'SELECT COUNT (*) AS COUNT FROM v$session'

You need to grant the DBA role to the user. SYSDBA does not give you the right to these objects.


Large scale, hard to model design advice needed I would love to hear some opinions or thoughts on a MySQL database design.

Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of these systems is unique, and will be reporting unique data.

The data sent can be categorized as frequent, and infrequent data. The infrequent data is only sent about once a day and doesn't change much; it is basically just configuration based data.

Frequent data is sent every 2-3 minutes while the system is turned on, and represents the current state of the system.

This data needs to be stored in a database for each system, and be accessible at any given time from a PHP page. Essentially for any system in the field, a PHP page needs to be able to access all the data on that client system and display it. In other words, the database needs to show the state of the system.

The information itself is all text-based, and there is a lot of it. The config data (that doesn't change much) is key-value pairs and there are currently about 100 of them.

My idea for the design was to have 100+ columns, and 1 row for each system to hold the config data. But I am worried about having that many columns, mainly because it isn't too future proof if I need to add columns in the future. I am also worried about insert speed if I do it that way. This might blow out to a 2000 row x 200 column table that gets accessed about 100 times a second so I need to cater for this in my initial design.

I am also wondering, if there are any design philosophies out there that cater for frequently- and seldom-changing data based on the engine. This would make sense as I want to keep INSERT/UPDATE time low, and I don't care too much about the SELECT time from PHP.

I would also love to know how to split up data. If frequently changing data can be categorised in a few different ways should I have a bunch of tables, representing the data and join them on selects? I am worried about this because I will probably have to make a report to show common properties between all systems (i.e. show all systems with a certain condition).

I hope I have provided enough information here for someone to point me in the right direction, any help on the matter would be great. Or if someone has done something similar and can offer advice I would be very appreciative.

Store the data in flat text files which are uniquely named based on each system, not in a database.

Databases (particularly relational ones) excel at storing data in tables with a consistant schema, relating those tables to one another and querying them in arbitrary ways. What you are describing is (based on my reading) basically a web page to display text files. There is only one way to access the data (via what is effectively a unique key, the system name), there don't appear to be any relationships, and the data will vary considerably from system to system (hence your issue with lots of columns).

So I don't see much benefit in using a relational database (a document database such as RavenDB, CouchDB or MongoDB may be a better fit, but even they are likely to be overkill for what you describe).

If you're really keen on using MySQL, just store the contends of the file in a table you can lookup via a name:

system_name varchar(255) NOT NULL Primary Key
file_type char(2) NOT NULL = 'F' or 'IF' (for frequent or infrequent)
data Longtext NULL

And parse your textual data in PHP (or simply display it).

You could store your data exactly as you receive it, or process it into XML, JSON or YAML to standardise it.

I just noticed the show common properties requirement.

Perhaps you could have columns in your table for those common properties, but not all the properties. You can quickly query the common properties while still being able to view the full data. (I don't have direct experience with any of the above document databases, but I believe all of them will let you index arbitrary fields in your documents, which will be much more flexible than a fixed number of columns).

If you really want to stay with MySQL, you may want to look into the Entity-Attribute-Value pattern. (IMO, EAV is an anti-pattern, but it should be OK just for the common properties).

Of course, if display time isn't really a big worry, just opening and parsing 1000 text files isn't going to take too long (I'd expect ~10 seconds).

Why not visualise the data first and then based on that it should be easy to make decisions.

The hidden relationships between the objects, it's flow etc.


Should developers be able to query production databases? Should developers be given permission to query (SELECT / read only) production databases?  The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production instance.

One of the test instances is a copy of production restored from a production backup once a week, so there aren't any problems with developers actually seeing the data.

What good reasons are there for not allowing developers to query production (except for simply not wanting them to have access to read sensitive data)?

It really depends on whether the developer has any support responsibilities.  If they are on the hook for third line support then they will probably need to look at the production database to do this.

Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.

For most development purposes, mirrors or snapshots of the production database will be adequate, and probably better than the live production database.  If you are doing anything involving integration then you will want stable database environments where you can control what's in them.  Anything involving reconciliation will also need the ability to look at a controlled point in time.

If the problem is that you don't have production mirror environments or any means to put a copy of production data somewhere for your developers then this is a somewhat different question.  In that case your developers really need at least one mirror environment.  If you can't see what the problem is in the data then it's kind of hard to troubleshoot it.

I'm not sure why everyone assumes developers are stupid and do not know anything. I get a sample of a ton of different roles where they messed up and should not be "in production". I've DBAs, Sys Admins, Network Admins, Developers, etc... all mess up.

No one (dev, dba, sa) has access to any server or database in any environment with there normal network login. They all have specific "admin" accounts that must be used. Yes, typically the dba and sa are using theirs more often, but even they should tread lightly. I 've burned by everyone.

So, on a good day, no IT role needs access. However, the sh!t hits the fan, all hands on deck and we need the right people solving the problem. This usually lead by the developer who knows the application and guide the dba and sa to certain points. It's just the unneeded delay or requesting and approval.

In addition, approval is never followed by any type auditing anyway, so approval means nothing.


Find percentage of NULL storage in Oracle table I have a table in Oracle 11g Standard One Edition:

table1

col1 col2 col3 col4 col5 col6 col7 col8       col9 col10 col11
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    NULL 2    3    4    5    NULL 1 Jan 2009 19   21    22
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    9    2    3    4    5    A    NULL       19   21    22
1    NULL 2    3    4    5    B    NULL       19   21    22

The table desc is:

Name                 Null Type
-------------------- ---- -------------
COL1                      NUMBER
COL2                      NUMBER
COL3                      NUMBER
COL4                      NUMBER
COL5                      NUMBER
COL6                      NUMBER
COL7                      VARCHAR2(255)
COL8                      DATE
COL9                      DATE
COL10                     DATE
COL11                     VARCHAR2(255)

I need to find out what is the percentage of storage a table consumes with values NULL?

Example: table1 storage consumed is 1 GB, and NULLs inside it consumes 100MB, so, NULL takes up 10% of the storage.

Also, are there alternate representations of NULL in ORACLE?

NULL value

  Absence of a value in a column of a row. Nulls indicate missing,
  unknown, or inapplicable data. A null should not be used to imply any
  other value, such as zero.

There are no alternate definitions of NULL in Oracle.  A column can't take less space than NULL unless the column doesn't exist at all.  Also, if a null value is in the last column of the row, the row will take less space than the same null column as an earlier column.  Here is the wording in the documentation:

  In general, try to place columns that frequently contain nulls last so
  that rows take less space. Note, though, that if the table you are
  creating includes a LONG column as well, then the benefits of placing
  frequently null columns last are lost.

Since in your example data, none of the rows with nulls have them last, we can ignore this space optimization.  We can also not compare the lack of a column to a column with nulls as that would be the true "space" of a null, which according to some block dumps I did is one byte per null per row.

In this case we are actually comparing the space of a null vs. the space of a value.  Because any value will by definition use more space than a null, my answer is that, a null value will decrease not increase the amount of space consumed, therefore creating a percentage compared to the size of the table is meaningless.

You could do a block dump of a row containing a value and then set the value to null and dump the block again to see how much additional space is available, but that number and the resulting ratio you could create with it and the size of the table would be a measure of space used by values rather than space used by NULLs.

For percentage, count non-null values and total values, then do the math

SELECT count(*) as record_count,
   count(COL1) as COL1_not_null,
   count(COL2) as COL2_not_null   FROM yourtable

count(columnname) counts how many times the value is not null, and count(*) counts the number of records.


Difference between partitioning and fragmentation I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results (deviding a relation horizontally/vertically/both).

So what's the difference between fragmentation and partitioning?

The terminology depends on the RDBMS. In SQL server: Partitioning is a DB feature that can be used to improve performance and/or manageability of your DB.

Fragmentation happens when your rows in the data pages are non-contiguous due to insert/delete operations. Your DB performance will decrease if you have high fragmentation of your data.

look here.
Remember partitioning and fragmentation on your local hard disk.

http://de.wikipedia.org/wiki/Skalierbarkeit#Skalierung_hinsichtlich_Datenbankeintr.C3.A4ge_-_Denormalisierung


MySQL - prevent OS cache for query On a high load production server I have many SQL selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but these queries are still cached with OS cache. The server has 64G ram and used ram is still 100%, about 47G is OS cache. If I manually drop OS cache with sync; echo 3 > /proc/sys/vm/drop_caches, then everything run more smoothly. But in few seconds the OS cache grows again to about 47G and uses all free ram. And I know, most of this data will be read only once, so there is no point for caching.

Is there any way to say to OS - "I know, I will not read this data again, don't cache them." ? Would be great, if this can be set for single mysql query or process.

And one more thing, I know about innodb_flush_method=O_DIRECT, but these tables are Aria engine on MariaDB

No. There  is no possibility to pass for operating system request not to cache file reads for IO done for specific query. You can reduce sysctl parameter vm.swappiness to 1 to make cache recovery more aggressive, but that affects all IO caching in the server.

Can you check if your query_cache_type is set to 1 ?

0 or OFF - Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.

1 or ON - Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.

2 or DEMAND - Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

I'm not very familiar with Aria but I believe it follows these settings. I'd test this option to see what happen.

More information:

http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_type


Force drop db while others may be connected I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB.

How can I implement it?

I'm using dropdb currently, but other tools are possible.

PostgreSQL 13 added:
DROP DATABASE mydb WITH (FORCE);

The manual:

FORCE
Attempt to terminate all existing connections to the target database.
It doesn't terminate if prepared transactions, active logical
replication slots or subscriptions are present in the target database.
This will fail if the current user has no permissions to terminate
other connections. Required permissions are the same as with
pg_terminate_backend, described in Section 9.27.2. This will
also fail if we are not able to terminate connections.

In PostgreSQL 12 and earlier, you cannot drop a database while clients are connected to it.
At least, not with the dropdb utility - which is only a simple wrapper around DROP DATABASE server query.
Quite robust workaround follows:
Connect to your server as superuser, using psql or other client. Do not use the database you want to drop.
psql -h localhost postgres postgres

Now using plain database client you can force drop database using three simple steps:

Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).
/* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

/* Method 2: use ALTER DATABASE. Superusers still can connect!
ALTER DATABASE mydb CONNECTION LIMIT 0; */

Force disconnection of all clients connected to this database, using pg_terminate_backend.
 SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity
 WHERE datname = 'mydb';

 /* For old versions of PostgreSQL (up to 9.1), change pid to procpid:

 SELECT pg_terminate_backend(procpid)
 FROM pg_stat_activity
 WHERE datname = 'mydb'; */

Drop the database.
 DROP DATABASE mydb;

Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.

If you're on something like RDS where connections without a database selected put you into the DB you asked to be created by default you can do this variant to get around yourself being the last open connection.

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;

 CREATE DATABASE temporary_db_that_shouldnt_exist with OWNER your_user;

 \connect temporary_db_that_shouldnt_exist
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'the_db_you_want_removed';

 DROP DATABASE IF EXISTS the_db_you_want_removed;
 --
 -- Name: the_db_you_want_removed; Type: DATABASE; Schema: -; Owner: your_user
 --

 CREATE DATABASE savings_champion WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

 ALTER DATABASE the_db_you_want_removed OWNER TO your_user;

 \connect the_db_you_want_removed

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;


SQL Server storage limits on Azure Looking at SQL Server maximum capacity limits on here: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.120).aspx

Database size can be as big as 524,272 terabytes.

Here, you could see VM size limits on Azure: http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx

If you look at the table on the second page, the maximum number of data disks that could be attached is 16 (1 TB each). Does that mean on Azure, SQL Server cannot house more than 16 TBs and anything more than that would require a new VM?

Yes, all the storage that you can attach to a single Azure VM is 16TB so that's the practical limit on storage for a single server.

Now that said, there isn't a problem that can't be worked around.

If you needed say a 100TB database, you could spin up 7 VMs, attach 16TB of storage to each one.  Then spin up an 8th VM which has SQL Server on it, then create the database using SMB network shares on the 7 other machines storing the database on the 7 remote machines.

Note: you can go around 16TB limitation by store your database files directly in Azure Storage: http://msdn.microsoft.com/en-us/library/dn385720.aspx.
This works starting from SQL Server 2014


Reorganize and Rebuild Indexes, Pros and Cons? I'm studying SQL Server and now am learning how to Reorganize and Rebuild Indexes. I've read already at msdn.microsoft, but It's not clear for me yet.
Please advise what is the best way when I have to use it. And what are the pros and cons of using it?

From Rebuild or Reorganize: SQL Server Index Maintenance by Kendra Little:
Short version:

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Should I use REBUILD or REORGANIZE?

If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.
If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.
If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.

Read Kendra's article for other important information you should take into account.

Rebuilding ends up with a superior result.  Use it whenever possible.

Use reorganize only when you're forced to, such as

You don't have Enterprise edition features like online rebuilds, and aren't allowed to lock the index for long enough to rebuild it offline.

Even during off hours or on weekends

You aren't allowed to put a rebuild's load on the system for long enough to finish, and so you run it in "chunks".

This is not going to get good results over time.


SQL Server Partitioning - what to use for partition key? I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 360 million rows in the Coupon Instance table and up to 72 million (assuming max 20% redemption rate) in the Redemption table. I get the feeling that these numbers are too big for a single partition?

My question is - what to use as the partition key? One obvious candidate would be by issuance event, giving approximately 6 partitions. But then I think that maybe even that would give a partition size that is too large to allow for optimal performance? Would it be possible to partition by two keys eg by issuance event + last digit of the customer id? So the logic would be:

If issuance event = 1 and last digit of customer id < 5 then
    Store in partition 1
Else if issuance event = 1 and last digit of customer id >4 then
    Store in partition 2
Else if issuance event =2 and last digit of customer id <5 then
    Store in partition 3
Else if issuance event =2 and last digit of customer id >4 then
    Store in partition 4
Etc...

Also, I'm not sure of the spec of the database server that we'll need. Will 16gb and 8CPUs be enough? The db needs to be able to return a result from the coupon instance table, keyed on a numeric barcode value in less than half a second. The expected transaction request for validate (select) and redeem (insert) is expected to peak at approximately 3,500 per minute.

The SQL Server 2008r2 64bit db server will be provisioned as VM from a very powerful host with access to a high performance and large capacity SAN.

I'd be very grateful for any advice from those that have deployed a SQL Server solution to manage similar volumes.

Regards

Rob.

The server spec questions should be directed to either Serverfault or DBA.SE.

For the partitioning question, I don't think you necessarily need to partition for this.

360m rows is a lot but it's not too unwieldy.

Do NOT under any circumstances try to partition based on the last digit of a field.  I'm not sure this would even work, but it's not SARGable which wouldn't be tenable.

If you only need to do a single row seek based on a numeric key, partitioning probably will not help.

If you do decide to pursue the partition route, bear in mind to be effective all your queries need to include your partition key(s) so the engine knows which partition to check.  Otherwise it will check them all and you actually hurt performance.

Unless you do partitioning because of archiving off old data, you are doing it for the wrong reason and should not do it.


Select last row for a certain group I have two tables:

Table users

columns | type   | notes
--------|--------|-------
id      | bigint | PK
...     | ...    | ...

Table events

columns    | type    | notes
-----------|---------|------
id         | bigint  | PK
user_id    | bigint  | FK(`users`.`id`)
event_name | varchar | ...
...        | ...     | ...

Example of Table events

id | user_id | event_name | ...
---|---------|------------|-----
 1 |       1 | A          | ...
 2 |       1 | B          | ...
 3 |       2 | F          | ...
 4 |       3 | C          | ...
 5 |       1 | A          | ...
 6 |       2 | A          | ...

I want get the latest event of a user (e.g: user_id is 1). So far, I found two solutions:

SELECT events.*
    FROM events
    WHERE events.id = (SELECT MAX(events.id) FROM events WHERE (events.user_id = 1));

and

SELECT events.*
    FROM events
    WHERE events.user_id = 1
    ORDER BY events.id DESC
    LIMIT 0, 1;

Which one is the better solution for this case? Or maybe, is there any better solution?

FYI, I use MySQL 5.6.19 and InnoDB is the db engine.

There would be marginal difference if any between both the queries, however, the second query is much simpler, so if it were me, I would prefer the second one!

Another way to do this would be:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
WHERE events.user_id = 1

Oh, and if you want to get the results of all user collectively, this query would help:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
GROUP by events.user_id

The output would be something like:

-------------------------------------
MAX(events.id) | user_id | event_name
-------------------------------------
       5       |    1    |    A
       6       |    2    |    F
       4       |    3    |    C


What is the instance name of the box I'm logged into? I'm at the linux prompt of a computer running Oracle 11.

Without connecting to the database, how can I determine what is the instance name of the Oracle server running on this box?

If the DB is running, you can look for the pmon process:

ps auxww | grep pmon | grep -v grep

You'll see one pmon process for each database that is up. For example ora_pmon_YOURDB, where YOURDB is the instance name.

You can also ask the listener which services it is serving connections for, with:

lsnrctl status

if you've installed Oracle with the installer, you can try with this:

  echo $ORACLE_SID


How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE Consider a table with the following columns:

phone BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(64),
state VARCHAR(64)

We would like to partition the table on state, however that would require dropping the primary key constraint on phone and replacing that constraint with a UNIQUE constraint. We make use of INSERT ... ON DUPLICATE KEY UPDATE and I would like to know how it would affect the system. Surprisingly little is written on the subject from reliable sources, and I'm sure that there are hidden gotchas which will not be immediately apparent to my naive eye.

What problems might we encounter with a partitioned table lacking a primary key, especially in regards to INSERT ... ON DUPLICATE KEY UPDATE queries?

The database in question in running MySQL 5.5 on Amazon RDS. The table in question has upward of 20 million rows and is often used in JOINs with other similarly-sized tables.

When we talk about partitioning tables in relational database platforms is recommended that it is always done on tables where the field is numeric. As a suggestion I would create an additional field that would serve as a surrogate key and this in turn would be used for partitioning the table.


Oracle data dictionary, tell built-in roles apart from custom roles In Oracle, if you describe the DBA_ROLES view, you only get three columns:

SQL> describe dba_roles;
 Name                      Null     Type
------------------------------------------------
 ROLE                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                  VARCHAR2(8)
 AUTHENTICATION_TYPE                VARCHAR2(11)

There is no column that could tell me whether or not a given role is one of the built-in roles the RDBMS comes with, like SET_CATALOG_ROLE or a user-created role like LEMONADE_STAND_MANAGER.

How can I get a list of all user-created roles (excluding all built-in roles)?

A list of predefined roles in Oracle 12c can be found in the Security Guide. Also in Oracle 12c the view dba_roles has a column ORACLE_MAINTAINED. A 'Y' in this column tells you that this is a role created by the Oracle installation scripts. For Oracle versions before 12c this column does not exist. So for Oracle version lower than 12c you have to check the manuals or search the internet for lists of Oracle predefined roles.

Note

There is a dictionary table SYS.USER$ that contains all roles and users. This view is not visible to non dba users.

select user#,type#,name from user$ order by user#;

lists all  users and roles ordered by their internal Iduser#. If typeis 0 the item is a role. Most of the time these IDs will be created in a sequential manner, so that roles and user created during installation have a low Id. But I found 12c databases with a lot of Oracle created roles with a high Id. Also if you upgrade a database then there will be roles created by Oracle scripts that have a higher Id than user created roles. So the user# is not a reliable criterion.

Unlike schema objects, roles aren't contained in any schema objects. Therefore, there is no "role owner". As an example, you can create a role (given the user has create role privilege), alongside with example tables, indexes... If you then drop the user, nothing will happen to the role, and only schema objects will be dropped.

Reference: http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#BABCADIB


Why SQL server does not have column-level locking? As far as i know, the lowest level locking is row-level

However i don't see the reason why it can not have column level locking

Assume that 2 queries simultaneously updates 2 different columns.

So why it can not happen? Why each update has to lock at least row level?

This behavior tells me that i should split table into smaller tables so less row level locking would happen

Am i incorrect?

This feature could exist. It would consume resources and complicate the architecture. So it's a trade-off that had to be made.

For the same reason some databases go in the opposite direction: They lock on the page or table level. MongoDB even has a global write lock for the entire database! This surely makes some physical implementation concerns much easier.

SQL Server's locking model already is quite sophisticated and, in my experience, addresses practical scenarios very well. I'm not sure column level locking would make it a better product (even if the developer time were free for Microsoft).

Because even row-level locking is "fake" to some extent, no pun intended - the smallest thing you can really lock is a block on the underlying filesystem, which is the atomic unit of I/O, which may be anywhere from 512 bytes to 8k usually. So since you need to serialize at that level anyway, a column lock doesn't give you anything a row lock wouldn't concurrency and performance-wise.


Always On Availability Group, Always redirect user to read only instance We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are intending to put in the database.

The users only have rights to read from the database but when they connect (through SSMS) through the AG Listener they always connect to the active node.

I've tried to get them to access the read only instance directly but they're stuck in their ways and after a day or two they're all back on the active node again.

Is there a way for SQL Server to say this user is always going to be with intent readonly and redirect them there?

NOTE: I've tried setting 'ApplicationIntent = ReadOnly' in the additional connection parameters but this doesn't seem to redirect to the secondary node, and isn't the ideal solution as they will inevitably forget to set it up for new starters.

SQL Server 2012 Enterprise, Availability group 1 primary, 1 readable secondary with synchronous commit.

I'm not intending for a user to connect to a linked server, or through any other server. The users connect directly to the database through SSMS (no other application) and I would like the AG Listener (or something around there) to be able to direct that user to a secondary node if one is available (as it only has read access there is no point in it accessing primary) without the user having to do anything, since they move around machines and will forget to add the application intent. Also I find adding that to the additional connection parameters does not always direct you to the secondary node.

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

It looks like this is now a feature in SQL Server 2019.
From Secondary to primary replica read/write connection redirection (Always On Availability Groups) in the official documentation:

SQL Server 2019 preview CTP 2.0 introduces secondary to primary replica read/write connection redirection for Always On Availability Groups. Read/write connection redirection is available on any operating system platform. It allows client application connections to be directed to the primary replica regardless of the target server specified in the connections string.
For example, the connection string can target a secondary replica. Depending on the configuration of the availability group (AG) replica and the settings in the connection string, the connection can be automatically redirected to the primary replica.


MySQL shutdown unexpectedly 2013-09-09 10:21:44 5776 [Note] Plugin 'FEDERATED' is disabled.
2013-09-09 10:21:44 1624 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-09-09 10:21:44 5776 [Note] InnoDB: The InnoDB memory heap is disabled
2013-09-09 10:21:44 5776 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-09-09 10:21:44 5776 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-09-09 10:21:44 5776 [Note] InnoDB: Not using CPU crc32 instructions
2013-09-09 10:21:44 5776 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-09-09 10:21:44 5776 [Note] InnoDB: Completed initialization of buffer pool
2013-09-09 10:21:44 5776 [Note] InnoDB: Highest supported file format is Barracuda.
2013-09-09 10:21:44 5776 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 19862295 in the ib_logfiles!
2013-09-09 10:21:44 5776 [Note] InnoDB: Database was not shutdown normally!
2013-09-09 10:21:44 5776 [Note] InnoDB: Starting crash recovery.
2013-09-09 10:21:44 5776 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-09-09 10:21:44 5776 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace casualme/scraped_user uses space ID: 2 at filepath: .\casualme\scraped_user.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd
InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

My phpmyadmin is not working. How can I make it work again? I have a very large database.

I have the following files. db.opt , scraped_user.frm , scraped_user.ibd is there a way to recover my database from these files? I just want to get the table's data.

I want to reinstall my xampp but is there a way to back up my db when I can't run it? Like copying this folder 'DBNAME' with files db.opt, scraped_user.frm, scraped_user.ibd.

I tried the first option suggested by InnoDB above. Still not working. In second I cant remove the .ibd file. I don't have a backup of my table. On the third item, I don't know where it is located. Been looking for it.

I have managed to recover my data. (so happy)

After hours of reading forums and solutions I found a way to fix it. Here's what I did:

In xampp I backed up my old copy (after crash) of folder DbScrapes (dbname) from C:\xampp\mysql\data with files db.opt, scraped_user.frm, scraped_user.ibd
In C:\xampp\mysql\data I backed up an old copy (after crash) of files ib_logfile0, ib_logfile1, ibdata1.
Uninstalled my xampp
Restarted my computer to be sure ports that xampp is using will be close
Checked if there is a MySQL service in services.msc
On my computer there was a MySQL service.
I deleted it using cmd command sc delete "service_name"
Restart again to make sure everything is fresh.
Installed a new copy of xampp
Test if apache and MySQL is ok, if it's ok click stop
Copy the files that I have backed up to the location where I get them so it's C:\xampp\mysql\data
Run xampp's apache and MySQL again.

Now I'm running xampp and I have my data.

Note: Don't delete ibdata1 - it will delete all your database tables.

The crashing issue I think was a bug in xampp.

Just add row innodb_force_recovery = 1 in my.ini file.


What is best strategy for backup on streaming replication(hot standby) environment I have questions.
What is best strategy for backup on streaming replication(hot standby) environment.
I’d like to do backup on slave server and restore(PITR).

First, I set up streaming replication(hot standby).

I tried backup using barman tool on slave.
But barman tool couldn’t execute on slave because pg_start_backup() can’t be executed on slave sever(read only).
I tried backup using pg_basebackup on slave.
But pg_basebackup is not included wal and wal archive.
Because I need a PITR(point-in-time-recovery), I tried to copy wal and wal archive from master to slave.
It was successful but was complicate and also inefficient.

Anyone have good strategy for backup on streaming replication(hot standby) environment?

Also, If  backup is executed on master(about 100GB DB size), How much it has effect on master?

Thanks,

As of >= 1.3.1 Barman supports backup from a standby replica (concurrent_backup). Barman config, e.g. /etc/barman.d/standby.conf looks like this:

[standby]
description = "Replica of main PostgreSQL DB"
ssh_command = ssh postgres@db02
conninfo = host=db02 user=postgres
backup_options = concurrent_backup
streaming_conninfo = host=db02 user=postgres
streaming_archiver = on

If your master is running on PostgreSQL <= 9.5 you'd have to install pgespresso extension (there are binary packages e.g. for Debian from PGDG APT repos). PostgreSQL 9.6 introduced native streaming API, there's no need for special extension.

On standby server make sure to configure archive_command:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p barman@backup:/var/lib/barman/standby/incoming/%f'

the incoming directory should match

barman:~$ barman diagnose | grep incoming_wals_directory

Also on standby server update pg_hba.conf (where 10.0.0.3 is ipaddress of barman server):

host   all             postgres            10.0.0.3/32        trust

And enable WAL files streaming:

barman~$ barman receive-wal standby

You can check your configuration using:

barman:~$ barman check standby

Server standby:
        PostgreSQL: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        pgespresso extension: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Then you should be ready to run full backup:

   barman:~$ barman backup standby

Please go through below links

http://blog.thecodepath.com/2012/02/13/adventures-in-scaling-part-3-postgresql-streaming-replication/

http://bartek.im/blog/2012/12/04/postgresql-92-streaming-primer.html

and if it is helpful please give feedback.and raise this solution.


Azure SQL Database finding latest available restore point From reading books online it is clear that there are two types of retention for Azure SQL Database.

Backups for point-in-time restore
Backups for long-term retention

When it comes to restore there are multiple options.

Point-in-time restore
Deleted database restore
Geo-restore

When I think of Geo-Restore the latest restore point cannot be same as Point in-time restore as backup has to be copied in a different region/data center.

  There is a delay between when a differential backup is taken and when
  it is geo-replicated to an Azure blob in a different region. This
  delay can be up to an hour, so, if a disaster occurs, there can be up
  to one hour data loss.

To find the latest restore point for Geo backup I can use Get-AzureRmSqlDatabaseGeoBackup command.

Question:

How can I find out LastAvailableBackupDate for point in-time restore?

You can use Get-AzureRmSqlDatabaseRestorePoints to get the earliest possible restore point for the db.  The output from this command is below:

ResourceGroupName        :
ServerName               :
DatabaseName             : testdb
Location                 : East US
RestorePointType         : CONTINUOUS
RestorePointCreationDate :
EarliestRestoreDate      : 2/23/2019 1:53:30 AM
RestorePointLabel        :

Unfortunately, this command does not give you the latest restore point.

According to Microsoft the transaction log backups generally occur every 5 - 10 minutes.  I attempted to do a test restore with the recovery point 2 minutes behind the current time and got the following error:

  Choose a restore point between earliest restore point and latest backup time which is 6 minute before current time.

For azure sql databases full log Backups are scheduled to run every 5-10 minutes.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups
So you can issujust assume last available restore point is 10 minutes before current time.
In reality it depends on what you are using it for. If you are issuing automated restores I would go for 30min-1hr before current time just to be sure.


PostgreSQL: How to backup only One Schema from a database and restore it on another server I have a database named "A" which has two schemas "B" and "C".
I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres.
Do I have to create a new DB on new sever as of name "A" and then restore the Schema "B" on it.
pls help with commands.

--- information from comment on Peter's answer:

I want to backup and restore Schema "B " + data. Secondly I forgot to mention that Postgresql 9.1 running on Ubuntu 12.04

You can select which schemas to dump with the -n option of pg_dump.  Create a dump of schema B:

pg_dump ...other...options... -Fc -n B >dump.dmp

Restore the dump file:

pg_restore -d somedb dump.dmp

The target database does not have to have the same name as the original one.

Note that you will have problems if schema B has dependencies on schema C.  Then you won't be able to restore it separately.

This can be used to take schema backup

pg_dump -U UserName -h HostName -s DBName > "file.sql"

This can be used to restore the DB

psql -d DBName -h HostName -U UserName <  "file.sql"


Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause.

Is there any way we can speed this up or use a different approach?

Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
         WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
         WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
         WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
         WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
         ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
        END AS col51
  from source_server.table')

After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it?

SELECT
           ISNULL(A.Col1,B.Col1) Col1
          ,ISNULL(A.Col2,B.Col2) Col2
          ,ISNULL(A.Col3,B.Col3) Col3
       ...etc
         ,ISNULL(A.Col50,B.Col50) Col50
         ,ISNULL(A.Col51,B.Col51) Col51
  FROM Table1 A -- linked server table
  FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table
  WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows
       OR  (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes
       OR  (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 <> A.Col50) --updates

it is in some way unreadable. Why not trying to call a stored procedure and getting the dataset?

select * from
OPENQUERY(link_server_name,'exec your_sp par1,par2)

What's more, instead of using CONVERT what about of using CAST?

CAST is ANSI compliant.

Best,


What is the meaning of "select * from relational(owner.table)"? We recently got some errors in our alert log of the form

301598: Tue Jul 16 03:58:29 2013
301599: ORA-01555 caused by SQL statement below
301600: SELECT * FROM RELATIONAL(<owner>.<table>)

I know how to handle ORA-01555 errors, but I never encountered something like SELECT * FROM RELATIONAL. I looked in DBA_OBJECTS, but there is no such function/operator/whatever.

Googling for 'SELECT * FROM RELATIONAL' only returned questions related to ORA-01555.

So, my question is: What is the meaning of the RELATIONAL keyword here?

It appears to be inert. I tested this on XE 11 and found that it does nothing.

CREATE TABLE dbausers AS SELECT * FROM dba_users;

-- Function doesn't affect result set.
SELECT * FROM dbausers
MINUS
SELECT * FROM RELATIONAL(dbausers);

SELECT * FROM RELATIONAL(dbausers)
MINUS
SELECT * FROM dbausers;

The line below "ORA-01555 caused by SQL statement below " is the SQL Statement causing the UNDO to swap.

So RELATIONAL is not an Oracle thing, it is an object created by a user. It is a function with owner.table parameter.


UPDATE with multiple WHERE and SELECT condition - MySql So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the name that match to that ID but I think I need multiple WHERE conditions and SELECT, may anyone help me out? Thanks!
I have the following code to update

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?,
                           localizacao = ?, fabricante = ?, modelo = ?,
                           imei = ?, numero_serie = ?, ativo_sap = ?,
                           anexo_a = ?, evento = ?, data_evento = ?,
                         SELECT id_colaborador FROM colaboradores
                               WHERE nome = ? AND WHERE id_ativo = ?";

Your query (while removing PHP code and formatting) looks like

UPDATE ativos
SET ativo = ?,
    comentario = ?,
    data_aquisicao = ?,
    localizacao = ?,
    fabricante = ?,
    modelo = ?,
    imei = ?,
    numero_serie = ?,
    ativo_sap = ?,
    anexo_a = ?,
    evento = ?,
    data_evento = ?,
    SELECT id_colaborador
    FROM colaboradores
    WHERE nome = ?
    AND WHERE id_ativo = ?

It is syntactically incorrect. Use Multiple-table UPDATE syntax. Final query must look near

UPDATE ativos a
  JOIN colaboradores c
/* next line maybe wrong, question is not clear */
    ON a.id_ativo = c.id_colaborador
SET a.ativo = ?,
    a.comentario = ?,
    a.data_aquisicao = ?,
    a.localizacao = ?,
    a.fabricante = ?,
    a.modelo = ?,
    a.imei = ?,
    a.numero_serie = ?,
    a.ativo_sap = ?,
    a.anexo_a = ?,
    a.evento = ?,
    a.data_evento = ?
/* next line maybe wrong, question is not clear */
WHERE c.nome = ?

If I've understood your question correctly, you can join the table and set the column to be the name instead of the ID:

UPDATE a
SET a.id = b.Name
FROM TableA a
    INNER JOIN TableB b ON a.ID = b.ID


A column value depends on the values of two other columns, of which exactly one is nullable In the Person table, either the Citizen_IdentityID or the Foreigner_WorkPermitID will contain NULL marks.

The PersonID_ID holds either the value of the Citizen_IdentityID or Foreigner_WorkPermitID (whichever that does not retain a NULL mark).

In other words, PersonID_ID is dependent on the value of either Citizen_IdentityID or Foreigner_WorkPermitID, and there are other attributes (eg. IDColor) that depends only on PersonnID_ID.

How should I structure the design for normalization?

The above entities are just a simple substitution of the actual use case.

If I'm understanding your description, PersonID_ID is a duplicate of Citizen_IdentityID or Foreigner_WorkPermitID (whichever is not NULL).  If this is true, I'd suggest something similar to what DCook has suggested:

remove Citizen_IdentityID and Foreigner_WorkPermitID (eliminate duplicate data)
add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively

Depending on where your data is coming from, this design would allow for the inclusion of future person types (eg, in the US you have citizens, visa holders, green card holders; in some countries there are clear differences in citizen vs resident vs visitor vs non-visitor visa - not to mention different types of visas). Obviously the need for additional person types will depend on what you're trying to model.

Looking at this a bit more ...

Can an individual's type/status change over time (eg, foreigner becomes citizen; citizen relinquishes citizenship and becomes a foreigner)?

The reason I ask is that you'd obviously need to change the value of the Person_Type and PersonID_ID columns ... probably not a big deal ... but based on your model it appears you'll be using PersonID_ID as part of the PK in at least one other table, so that table's PK would also need to be updated ... while certainly doable, this could quickly get messy depending on the number of tables and records that would need their PK modified, plus the hassles of working around RI constraints.  The mess gets a little nastier if you also need to maintain some sort of history/audit-record for a person (eg, having to store/map the changing of PKs ... yuck!).

So now I'm wondering if PersonID_ID should be a separate, truly unique value, independent of the Person_Type? and if so, then you'd probably need to retain the Citizen_IdentityID/Foreigner_WorkPermitID value, but perhaps give it a more generic name.

So, that would change my suggestion to something like:

add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively
change PersonID_ID to a be it's own unique value (could be done through something like an identity column or a unique-PK generator); this value would never change, even if Person_Type were to change over time
replace the Citizen_IdentityID/Foreigner_WorkPermitID columns with a single, generic ID that serves as a FK to the Citizen/Foreigner tables, as appropriate); perhaps something like Person_TypeID (int not NULL)?

OK, one more concern/question ...

Can a person ever be associated with more than 1 Person_Type (C/F)?

For example, if a foreigner goes through the naturalization process to become a citizen, how would this be represented in your model?

Would the person now have both a Foreigner_WorkPermitID and a Citizen_IdentityID, thus requiring the storage of both values in the Person table?

Or would you update the Person table to show a change from 'Foreigner' to 'Citizen' (ie, change Person_Type and Person_TypeID)?

I think what you're looking for is pretty simple,

CREATE TABLE foo ( d int PRIMARY KEY );
CREATE TABLE bar (
  a int REFERENCES foo,
  b int REFERENCES foo,
  CHECK (
    (a IS NOT NULL AND b IS NULL)
    OR (a IS NULL AND b IS NOT NULL)
  )
);

However, I probably wouldn't do that. This sounds a lot easier:

CREATE TABLE foo ( d int PRIMARY KEY, is_foreign bit NOT NULL );
CREATE TABLE bar (
  a int REFERENCES foo NOT NULL
);


Remove Dot at end of line of the string in SQLserver I would to like to remove the dot at end of the line alone if exists in the string in SQL server.

Example:

ABC Private's co., ltd.

Required output:

ABC Private's co., ltd

You can use like to check for a . at the end of the string.

declare @S nvarchar(max) = 'ABC Private''s co., ltd.';

select case when @S like '%.'
         then left(@S, len(@S) - 1)
         else @S
       end;

I know its an old question but looks like all the answers are complicated.

So this one might be so simple.

For SQL Server

select case

when right(rtrim(f1),1) = '.' then substring(rtrim(f1),1,len(rtrim(f1))-1)

else f1 END AS f1

From Table1

For PGSQL and MYSQL

select rtrim(Enames,'.') from table_name

Hope it helps someone..


How should I structure my time based index? So I have this table in SQL Server 2008.  Let's just say it's called TimeSheets.

Now, that table has two columns I am concerned with.

TimeSheetDate and UserCustomerKey.

TimeSheetDate is a DateTime column and UserCustomerKey is a GUID.

UserCustomerKey is the primary ID of a join table for Users and Customers.

Typically, the primary application is going to query TimeSheets by the TimeSheetDate and UserCustomerKey.  And since we're dealing with time sheets, we usually need the most recent time sheets for reports.

So I created an index like:

TimeSheetDate (DESC)
UserCustomerKey (ASC)

Since we normally care about descending time sheet searches (most recent time sheets from today), this index makes sense.

However, there may be applications that JUST search by TimeSheetDate and not care about the UserCustomerKey.

1)  So, do I need to also create a single index for TimeSheetDate or will the above index also include it?

2)  Do I need to create another index that has UserCustomerKey BEFORE TimeSheetDate or does it matter?

I don't want to waste resources if I don't have to.

Thanks.

If a single column is used by a query for filtering, it can use an index if the that column is the left-most column in the index.

So assuming the TimeSheetDate is the left-most column in your index, you don't need to create another index for it.

Whether or not you should create another index in a reversed order is dependent on the selectivity of the UserCustomerKey column and whether queries filter using it.

First of all, since I assume the timesheet dates are rising, not falling, you should replace the

TimeSheetDate (DESC)
UserCustomerKey (ASC)

with

TimeSheetDate (ASC)
UserCustomerKey (ASC)

This is important! Through the DESC you basically force SQL Server to insert new entries at the start of the index. That is bad because it leads to continuous page splits and a heavily fragmented index. Even if you rebuild the index and it is perfectly unfragmented after that, you have absolutely no performance gain from having the youngest dates at the beginning of the index. That's just not how a B-Tree index works. The ASC and DESC options are there so you can try to influence the index in a way that the inserts happen at the end (not the start!) because that's what those indexes are optimized for.

The date+key index can be used just fine with date-only queries, but not for key-only queries.

An index containing only the date would gain you a minimal advantage for the date-only queries. I assume that the date/key index is made UNIQUE, so if you have only the date in it (not unique), SQL Server adds a 4 byte uniquiefier to each entry. This is still smaller than the 16 byte guid for the customer key, so you have the advantage of a smaller index for these queries. But it costs you space for another index, including the associated cost on insert/update/delete, buffer cache, I/O, statistics updates and so on, so I wouldn't recommend it.

Creating an additional index in reverse order (key+date) doesn't make any sense at all. It has the same size and the same depth as the original index (after defragmentation, otherwise it would be even larger), so the number of pages you have to read to get to a specific entry (if you give both date and key in the where clause) is exactly the same. If you have key-only queries, you would need an additional index containing only the customer key.

Edit: There is one scenario where a [key+date] index would actually make sense: If you have frequent queries like

SELECT ... FROM x WHERE UserCustomerKey='...' ORDER BY TimeSheetDate

because in that case the index already provides the correct sort order for the query. If such queries are relatively seldom, it's not worth the additional index space to include the date column. BTW in that case it doesn't matter if you have asc or desc in the index or the order by. SQL Server can read an index forwards or backwards, according to need, because the leaf pages form a double linked list.


Oracle 11g http listener configuration for PL/SQL server pages Could please someone point me how to configure oracle db to be able to display PL/SQL Server pages. I have successfully created and mapped dad with the DBMS_EPG.

Here is the listener:

# listener.ora Network Configuration File: D:\app\Ja\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 8080))
    )
  )

ADR_BASE_LISTENER = D:\app\Ja

When I want to enter

  localhost:8080/my_dad/home

I get error: No data received.

How do I solve this?

check tnsnames.ora file

C:\oracle\product\10.2.0\Oracle\NETWORK\ADMIN

ORCL=
   (DESCRIPTION =
       (ADDRESS_LIST =
             (ADDRESS =
                  (PROTOCAL = TCP)
                  (HOST = 123.456.789.123)
                  (PORT = 1521)
             )
       )
       (CONNECT_DATA = (SID = ORCL))
   )


Create tablespace "error in creating database file, access denied (OS 5)" I try to create a database file in this location

I've checked the route and everything, I've tried several times to create the tablespace like this...

...but as you can see, I was just lead to several errors. What can I do? What am I doing wrong?

Do you have permission on this directory?
Under which user are you running SQL*PLUS? Administrator? –
if no than got permission first

if yes

CREATE TABLESPACE lmtbsb DATAFILE 'file_path/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


SQL Server 2012 - Large backup file size We have the following setup:

Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak.
From then, a transactional backup is performed every hour. This is appended to Backup1.bak.
Once a day at midnight (except Sundays) a differential backup is performed. This is also appended to Backup1.bak.
The following Sunday at midnight, Backup1 is zipped and moved to external media, a new full backup is created and is placed in Backup2.bak. Backup1.bak is deleted.

The database is running in full recovery mode, and all backups only operate on one database in one instance.

This works well in SQL Server 2008. However, we are now starting to roll out SQL Server 2012 implementations, and the problem we are encountering is that the BAK file is growing exponentially. A typical MDF file size rarely exceeds 15Gb and LDF rarely exceeds 50Gb, but the BAK file is sometimes as big as 900Gb by Saturday. This is naturally causing capacity constraints on client sites with 1Tb HDDs.

Is there a different way of doing transactional and differential backups in 2012 as compared to 2008?

Did you use INIT as a parameter to the backup command?

Otherwise, the dump is appended to the backup file.


Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable I have a set of starting and ending times that I need to store in an Oracle database.  I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where a time value, such as 9:30AM, falls in between the start time and end time).  In SQL Server I would just use a TIME datatype field, but Oracle does not appear to have an equivalent.

I have seen a number of blogs and forums that recommend just using a DATE field and doing conversions with TO_CHAR then comparing, or to store the time values as varchar(4 / HHMM) or varchar(6 / HHMMSS) fields.  Both of these seem needlessly inefficient.

Is there a better or more efficient way to accomplish this in Oracle?

If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.

For example:

create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2
----------- -----------
0 10:30:0   0 13:45:0

SELECT max(t2-t1) FROM times;

MAX(T2-T1)
-----------
0 3:15:0.0

You also have standard functions to extract the various components of the time (hour, minute, etc.):

SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times;

        HR         MN        SEC
---------- ---------- ----------
         9         30          0
        10         30          0
        11         30          0

I was looking up on the same scenario of storing hh:mm (or hhmm) into an oracle column. I tried the above example (of loading data into timing table) and found that since we use random value generator it is storing data 12.0 instead of 12.00 in the hoursText column (which is varchar). Also I think the queries are not using indexes since we have functions defined on the column in the

select ... where to_date(hoursText) between ...

I think we do not need this conversion to to_date. Why not:

select  * from timing where hoursText between '12:0' and '13:00' ;

I think this query will use the index and return results fast.


Suggestions on database model design/ performance I'm currently building an 'online examination' type of web app using Ruby on Rails and PostgreSQL (or MySQL), which is going to be part of a bigger application once pushed to production. Since I just recently got into RD design I'd appreciate some feedback on the following design.

App Specifications

There are 1000+ different questions. Each question can have 2-5
different options, only 1 option is correct.
The user is presented with 10 questions at a time.
If a user answers a question correctly 2 times, the question will not
be shown to him again (let's call those questions now completed).
If a user answers a question incorrectly then he needs to answer the
question 2 MORE times correctly.
The user passes the exam when there are no more questions left to
show.

My current database schema is:

user
-user_id
-name etc

question
-question_id
-text
-correct_id (or option_id, stores the correct option id)

option
-option_id
-text
-question_id

user_answer
-user_id
-question_id
-is_correct

So in order to create a '10 question test' we could:

create a list of every completed question querying the user_answer table
create a list of possible questions (all questions - completed questions)
finally serve the user with 10 random questions from the last list.

Is there a better approach to represent "the remaining times a question needs to be answered" inside the database? Please feel free to suggest even a completely different design.

What concerns me is that for each single user there would be more than 2x1000 user_answer rows. This quickly becomes a huge number, so I guess there must be a better way.

Would using a json array be okay in this case? But as far as I understand it'd quickly become a hassle to add more questions etc.

As you are at the DBA site, it is likely we will give you database solutions :-)

As far as I can see, you are stuck with your two options - a row for every users answer or a large list. Best bet in terms of database design is to go for a row for every users answer and to make the columns as narrow as possible.

My solution to this would be to change your field "is_correct" in the "user_answer" table to "answer_status" and make it a tinyint. If the user gets the answer wrong, this is set to 0. If the user gets the answer right, it is incremented by 1. For displaying non correctly answered questions, you would scan for all questions where "answer_status" is < 2. Now the column would represent:

0 = question not yet answered or answered incorrectly
1 = question answered correctly once
2 = question answered correctly

When the user starts their exam, you will make an entry for every question in the question table for that user, with the default answer_status = 0.

If your "question_id" column and "user_id" column are both ints, you will store 9 bytes per row - which means if you use 100MB for this table, you would be storing ~119 million rows. If 10,000 rows per user, you will be storing data for ~12,000 users. You would most likely want to cluster this table on "user_id".

In addition, you could clean up the "user_answer" table by setting a flag in a "user_exam" table. It is likely you will need this table to encapsulate the questions into an exam and to list which exams users are enrolled for. This table could have a "status" field which could be set to "Completed" when a user has passed the exam. Subsequently all of the rows in the "user_answer" table can be deleted for that exam, for that user, once they have completed all the answers (save possibly getting some statistics like length of time taken). This would reclaim space in your database when the tables clustered index is rebuilt.

You can also see below web page on online examination database design help .
https://databaseinmysql.blogspot.com/2019/04/introduction-of-online-examination.html


Will OPTIMIZE TABLE have any impact on my data? I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE? Will indexes and primary keys still remain the same? Are there areas of the database that will be slower after the optimization?

OPTIMIZE TABLE basically does three(3) things

Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics

Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp RENAME mytable;
DROP TABLE mytablezap;
ANALYZE TABLE mytable;

However, based on

the table mydb.mytable
the datadir being /var/lib/mysql

let's look at Storage Engine Specifics
MyISAM
The MyISAM table mydb.mytable is physically stored in three files

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.MYD (data)
/var/lib/mysql/mydb/mytable.MYI (indexes)

The conceptual description of running OPTIMIZE TABLE would copy data pages and index pages into a new .MYD and .MYI. This will eliminate having fragmented pages in either file.
InnoDB
There are two viewpoints to take into consideration
Viewpoint #1 : innodb_file_per_table disabled
With innodb_file_per_table disabled, all data pages and index pages for every InnoDB table are stored inside the system tablespace (better known as the file ibdata1).
When you run OPTIMIZE TABLE on an InnoDB table that is stored in ibdata1, all the data and index pages are written contiguously so all the pages for the table are together. The bad news is that it makes ibdata1 grow rapidly.
Viewpoint #2 : innodb_file_per_table enabled
With innodb_file_per_table enabled, all data pages and index pages for every InnoDB table are stored outside ibdata1. Here is the physical storage of mydb.mytable:

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.ibd (data and indexes)

When you run OPTIMIZE TABLE on an InnoDB table that is stored outside of ibdata1 (system tablespace), this perform the conceptual steps that lead to shrinking the .ibd file.
I have written about this before

Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Dec 21, 2012 : Is there a progress indicator for OPTIMIZE TABLE progress?
Jan 07, 2013 : Database space doesn't match ibdata1 size

Now, for your original questions...

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

I just explained how OPTIMIZE TABLE works for both storage engines. This can be time-consuming depending on the size of the data and indexes. Outside of an Earthquake or Power Outage, running OPTIMIZE TABLE does not harm to the data and indexes get rebuilt.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE?

Ditto

Will indexes and primary keys still remain the same?

Yes

Are there areas of the database that will be slower after the optimization?

No way. Querying a table with no fragmentation can only be faster

pros: faster database with more free memory afterwards

cons: can lock tables "For InnoDB tables prior to 5.7.4 and other table types, MySQL locks the table during the time OPTIMIZE TABLE is running"

can also put load on the database so may want to do it when there's no peak load

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html


Optimizing queries on a range of timestamps (two columns) I use PostgreSQL 9.1 on Ubuntu 12.04.

I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this query.

create table (
   start_date_time timestamp,
   end_date_time timestamp,
   id_phi integer,
   primary key(start_date_time, end_date_time,id_phi);

This table contains roughly 2M records.

Queries like the following took enormous amounts of time:

select * from time_limits as t
where t.id_phi=0
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

So I tried adding another index - the inverse of the PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

I got the impression that performance improved: The time for accessing records in the middle of the table seems to be more reasonable: somewhere between 40 and 90 seconds.

But it's still several tens of seconds for values in the middle of the time range. And twice more when targeting the end of the table (chronologically speaking).

I tried explain analyze for the first time to get this query plan:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

See the results on depesz.com.

What could I do to optimize the search? You can see all the time is spent scanning the two timestamps columns once id_phi is set to 0. And I don't understand the big scan (60K rows!) on the timestamps. Aren't they indexed by the primary key and idx_inversed I added?

Should I change from timestamp types to something else?

I have read a little about GIST and GIN indexes. I gather they can be more efficient on certain conditions for custom types. Is it a viable option for my use case?

For Postgres 9.1 or later:
CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);
In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:

PostgreSQL index not used for query on range

Consider your query:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

Sort order of the first column id_phi in the index is irrelevant. Since it's checked for equality (=), it should come first. You got that right. More in this related answer:

Multicolumn index and performance

Postgres can jump to id_phi = 0 in next to no time and consider the following two columns of the matching index. These are queried with range conditions of inverted sort order (<=, >=). In my index, qualifying rows come first. Should be the fastest possible way with a B-Tree index1:

You want start_date_time <= something: index has the earliest timestamp first.
If it qualifies, also check column 3.
Recurse until the first row fails to qualify (super fast).
You want end_date_time >= something: index has the latest timestamp first.
If it qualifies, keep fetching rows until the first one doesn't (super fast).
Continue with next value for column 2 ..

Postgres can either scan forward or backward. The way you had the index, it has to read all rows matching on the first two columns and then filter on the third. Be sure to read the chapter Indexes and ORDER BY in the manual. It fits your question pretty well.
How many rows match on the first two columns?
Only few with a start_date_time close to the start of the time range of the table. But almost all rows with id_phi = 0 at the chronological end of the table! So performance deteriorates with later start times.
Planner estimates
The planner estimates rows=62682 for your example query. Of those, none qualify (rows=0). You might get better estimates if you increase the statistics target for the table. For 2.000.000 rows ...
ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

... might pay. Or even higher. More in this related answer:

Check statistics targets in PostgreSQL

I guess you don't need that for id_phi (only few distinct values, evenly distributed), but for the timestamps (lots of distinct values, unevenly distributed).
I also don't think it matters much with the improved index.
CLUSTER / pg_repack / pg_squeeze
If you want it faster, yet, you could streamline the physical order of rows in your table. If you can afford to lock your table exclusively (at off hours for instance), rewrite your table and order rows according to the index with CLUSTER:
CLUSTER time_limits USING idx_time_limits_inversed;

Or consider pg_repack or the later pg_squeeze, which can do the same without exclusive lock on the table.
Either way, the effect is that fewer blocks need to be read from the table and everything is pre-sorted. It's a one-time effect deteriorating over time with writes on the table fragmenting the physical sort order.
GiST index in Postgres 9.2+
1 With pg 9.2+ there is another, possibly faster option: a GiST index for a range column.

There are built-in range types for timestamp and timestamp with time zone: tsrange, tstzrange. A btree index is typically faster for an additional integer column like id_phi. Smaller and cheaper to maintain, too. But the query will probably still be faster overall with the combined index.

Change your table definition or use an expression index.

For the multicolumn GiST index at hand you also need the additional module btree_gist installed (once per database) which provides the operator classes to include an integer.

The trifecta! A multicolumn functional GiST index:
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

Use the "contains range" operator @> in your query now:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

SP-GiST index in Postgres 9.3+
An SP-GiST index might be even faster for this kind of query - except that, quoting the manual:

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

Still true in Postgres 12.
You would have to combine an spgist index on just (tsrange(...)) with a second btree index on (id_phi). With the added overhead, I'm not sure this can compete.
Related answer with a benchmark for just a tsrange column:

Perform this hours of operation query in PostgreSQL

I managed to rapidly increase (from 1 sec to 70ms)

I have a table with aggregations of many measurements and many levels (l column) (30s, 1m, 1h, etc) there are two range bound columns: $s for start and $e for end.

I created two multicolumn indexes: one for start and one for end.

I adjusted select query: select ranges where their start bound is in given range. additionally select ranges where their end bound is in given range.

Explain shows two streams of rows using our indexes efficiently.

Indexes:

drop index if exists agg_search_a;
CREATE INDEX agg_search_a
ON agg (measurement_id, l, "$s");

drop index if exists agg_search_b;
CREATE INDEX agg_search_b
ON agg (measurement_id, l, "$e");

Select query:

select "$s", "$e", a, t, b, c from agg
where
    measurement_id=0
    and l =  '30s'
    and (
        (
            "$s" > '2013-05-01 02:05:05'
            and "$s" < '2013-05-01 02:18:15'
        )
        or
        (
             "$e" > '2013-05-01 02:00:05'
            and "$e" < '2013-05-01 02:18:05'
        )
    )

;

Explain:

[
  {
    "Execution Time": 0.058,
    "Planning Time": 0.112,
    "Plan": {
      "Startup Cost": 10.18,
      "Rows Removed by Index Recheck": 0,
      "Actual Rows": 37,
      "Plans": [
    {
      "Startup Cost": 10.18,
      "Actual Rows": 0,
      "Plans": [
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 26,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone))",
          "Plan Rows": 29,
          "Parallel Aware": false,
          "Actual Total Time": 0.016,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.016,
          "Total Cost": 5,
          "Actual Loops": 1,
          "Index Name": "agg_search_a"
        },
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 36,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone))",
          "Plan Rows": 39,
          "Parallel Aware": false,
          "Actual Total Time": 0.011,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.011,
          "Total Cost": 5.15,
          "Actual Loops": 1,
          "Index Name": "agg_search_b"
        }
      ],
      "Node Type": "BitmapOr",
      "Plan Rows": 68,
      "Parallel Aware": false,
      "Actual Total Time": 0.027,
      "Parent Relationship": "Outer",
      "Actual Startup Time": 0.027,
      "Plan Width": 0,
      "Actual Loops": 1,
      "Total Cost": 10.18
    }
      ],
      "Exact Heap Blocks": 1,
      "Node Type": "Bitmap Heap Scan",
      "Plan Rows": 68,
      "Relation Name": "agg",
      "Alias": "agg",
      "Parallel Aware": false,
      "Actual Total Time": 0.037,
      "Recheck Cond": "(((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone)) OR ((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone)))",
      "Lossy Heap Blocks": 0,
      "Actual Startup Time": 0.033,
      "Plan Width": 44,
      "Actual Loops": 1,
      "Total Cost": 280.95
    },
    "Triggers": []
  }
]

The trick is that your plan nodes contain only wanted rows. Previously we got thousands of rows in plan node because it selected all points from some point in time to the very end, then next node removed unnecesary rows.


Merge statement deadlocking itself I have the following procedure (SQL Server 2008 R2):

create procedure usp_SaveCompanyUserData
    @companyId bigint,
    @userId bigint,
    @dataTable tt_CoUserdata readonly
as
begin

    set nocount, xact_abort on;

    merge CompanyUser with (holdlock) as r
    using (
        select
            @companyId as CompanyId,
            @userId as UserId,
            MyKey,
            MyValue
        from @dataTable) as newData
    on r.CompanyId = newData.CompanyId
        and r.UserId = newData.UserId
        and r.MyKey = newData.MyKey
    when not matched then
        insert (CompanyId, UserId, MyKey, MyValue) values
        (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId, UserId, MyKey form the composite key for the target table. CompanyId is a foreign key to a parent table.  Also, there is a non-clustered index on CompanyId asc, UserId asc.

It is called from many different threads, and I am consistently getting deadlocks between different processes calling this same statement. My understanding was that the "with (holdlock)" was necessary to prevent insert/update race condition errors.

I assume that two different threads are locking rows (or pages) in different orders when they are validating the constraints, and thus are deadlocking.

Is this a correct assumption?

What is the best way to resolve this situation (i.e. no deadlocks, minimum impact on multi-threaded performance)?

(If you view the image in a new tab, it is readable.  Sorry for the small size.)

There are at most 28 rows are in the @datatable.
I have traced back through the code, and I cannot see anywhere that we start a transaction here.
The foreign key is set up to cascade only on delete, and there were no deletions from the parent table.

There would not be a problem if the table variable only ever held one value.  With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company.

Process A reads the destination, finds no row, and inserts the value '1'.  It holds an exclusive row lock on value '1'.  Process B reads the destination, finds no row, and inserts the value '2'.  It holds an exclusive row lock on value '2'.

Now process A needs to process row 2, and process B needs to process row 1.  Neither process can make progress because it requires a lock that is incompatible with the exclusive lock held by the other process.

To avoid deadlocks with multiple rows, the rows need to be processed (and tables accessed) in the same order every time.  The table variable in the execution plan shown in the question is a heap, so the rows have no intrinsic order (they are quite likely to be read in insertion order, though this is not guaranteed):

The lack of consistent row processing order leads directly to the deadlock opportunity.  A second consideration is that the lack of a key uniqueness guarantee means that a Table Spool is necessary to provide correct Halloween Protection.  The spool is an eager spool, meaning all rows are written to a tempdb worktable before being read back and replayed for the Insert operator.

Redefining the TYPE of the table variable to include a clustered PRIMARY KEY:

DROP TYPE dbo.CoUserData;

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL PRIMARY KEY CLUSTERED,
    MyValue integer NOT NULL
);

The execution plan now shows a scan of the clustered index and the uniqueness guarantee means the optimizer is able to safely remove the Table Spool:

In tests with 5000 iterations of the MERGE statement on 128 threads, no deadlocks occurred with the clustered table variable.  I should stress that this is only on the basis of observation; the clustered table variable could also (technically) produce its rows in a variety of orders, but the chances of a consistent order are very greatly enhanced.  The observed behaviour would need to be retested for every new cumulative update, service pack, or new version of SQL Server, of course.

In case the table variable definition cannot be changed, there is another alternative:

MERGE dbo.CompanyUser AS R
USING
    (SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
    R.CompanyId = @CompanyID
    AND R.UserID = @UserID
    AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN
    INSERT
        (CompanyID, UserID, MyKey, MyValue)
    VALUES
        (@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);

This also achieves the elimination of the spool (and row-order consistency) at the cost of introducing an explicit sort:

This plan also produced no deadlocks using the same test.  Reproduction script below:

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL /* PRIMARY KEY */,
    MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
    CompanyID   integer NOT NULL

    CONSTRAINT PK_Company
        PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
    CompanyID   integer NOT NULL,
    UserID      integer NOT NULL,
    MyKey       integer NOT NULL,
    MyValue     integer NOT NULL

    CONSTRAINT PK_CompanyUser
        PRIMARY KEY CLUSTERED
            (CompanyID, UserID, MyKey),

    FOREIGN KEY (CompanyID)
        REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE
    @DataTable AS dbo.CoUserData,
    @CompanyID integer = 1,
    @UserID integer = 1;

INSERT @DataTable
SELECT TOP (10)
    V.MyKey,
    V.MyValue
FROM
(
    VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
        (6, 6),
        (7, 7),
        (8, 8),
        (9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();

BEGIN TRANSACTION;

    -- Test MERGE statement here

ROLLBACK TRANSACTION;

One other possible approach - I've found Merge to sometimes present locking and performance issues - it may be worth playing with the Option (MaxDop x) query option

In the dim and distant past SQL Server had an Insert Row Level Locking option - but this seems to have died a death, however a clustered PK with an identity should make inserts run clean.