Recover from Postgres FATAL: could not open file pg_tblspc/ I have a pretty big PostgreSQL 9.1.3 running on Ubuntu 10.04. The data is spread over multiple tablespaces = physical drives.

One of these drives is gone, so the directory of that tablespace doesn't exist any more. e.g.: I have lost the directory where the symlink in "pg_tblspc/176967555" linked to.

Well. Status: After a restart, that DBMS comes up with no error. Accessing that particular database is not possible though

  psql: FATAL:  could not open file pg_tblspc/176967555/

I tried to simply create these folders as empty, but then the PG wants a PG_VERSION and pg_filenode.map file in that directory, which I can not simply create.

90% of the data in the affected database is stored in other, sane tablespaces. But I can't access any table in the database, because some of the tables were stored on the now-vanished tablespace.

My goal is to read data from the unaffected tablespaces. It would be fine if postgres would just drop anything that lied on that tablespace.

I recovered most files from the lost tablespace directoy (e.g. pg_tblspc/176967555/). When I put that recovered folder back in place, PG is still compaining about a missing file when accessing that DB - one file which I could not recover.

Could starting the DBMS with zero_damaged_pages=true help to just ignore the missing files? If zero_damaged_pages intended to be used for the ''missing file'' szenario?
edit: No luck -  it will still complain about the missing file:

set zero_damaged_pages = true;
SET
postgres=# \connect problemdb ;
FATAL:  could not open file "pg_tblspc/176967555/PG_9.1_201105231/123304298/135285149": No such file or directory

What are my options?

Should continue I to try to recover the database with the broken tablespace? This discussion seems to provide some tips on how to restore when single files are missing. Can I somehow create these files with dd ?

Should I try to get the needed tables from the binary files with pg_filedump ?

Someone disussed the option to import a tablespace to a new database on the postgres mailinglist in 2009, but there seems to be no way.

Isn't this a pretty normal crash scenario: some file has vanished - and you still wan't to access the tables stored in the other files?

Many thanks in advance for any help.
Steve

The very first thing to do is to make a copy of whatever data files you still have, and to keep it and any backups safe until long after your recovery effort is complete.  Please read this (short) Wiki page:

http://wiki.postgresql.org/wiki/Corruption

Once you have done that, you can attempt various recovery strategies without fear that you will be worse off for the attempt, beyond the time required to try it.  In general I recommend carefully following one of the techniques described in the documentation -- attempts to cut corners or to be creative often lead to corruption.  Only a seasoned expert with a good understanding of PostgreSQL internals should attempt to deviate from the documented steps.

You didn't describe your backup strategy; details of what is available there may suggest alternatives you would not otherwise have.

Ultimately, if you have data of value which is not backed up, you may need to hand-edit the system tables to eliminate references to lost tablespace.  This is not for the faint of heart.  There are a number of companies with which you can contract for such services, many of whom have experience with recovery from catastrophic hardware failure like this.

http://www.postgresql.org/support/professional_support/

I am not affiliated with any of these companies.


Query to get number of items from different table reference in another table I have 5 tables which are

- Project(PK Project_ID),
- Bottles (PK Bottle_ID, FK Project_ID),
- Plastics (PK Plastic_ID, FK Project_ID),
- Glasses (PK Glass_ID,FK Project_ID) and
- Cups (PK Cup_ID, FK Project_ID)

How can I get one SQL query to return the number of Bottles ,Plastics , Glasses  and Cup per Project,  where One Project has many Bottles ,Plastics , Glasses  and Cup ?

Something like this will work, though is not necessarily efficient on all DB engines:

select Project_ID,
count(distinct Bottle_ID) bottlesCount,
count(distinct Plastic_ID) plasticsCount,
count(distinct Glass_ID) glassesCount,
count(distinct Cup_ID) cupsCount
from Project
left join Bottles using (Project_ID)
left join Plastics using (Project_ID)
left join Glasses using (Project_ID)
left join Cups using (Project_ID)
group by Project_ID;


Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant? I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard.

I found this article on Microsoft Docs which states it is not, but it refers to ODBC driver and Microsoft Jet engine - not sure if this is exactly the same thing, shouldn't it also relate to T-SQL?

I don't believe that any version of any database platform on earth is fully compliant with any version of the standard. This is probably why you're not finding any claims of 100% compliance...

You can find some promising starting points over on Stack Overflow in the following Q & A: Database Engines and ANSI SQL Compliance

Many references linked from there are not maintained because database platforms are evolving all the time and this would be many full-time jobs to stay on top of it all. Basically:

no database platform is 100% compliant, but several come close
platforms have proprietary additions on top of the standard, so your definition of "fully compliant" may vary from someone else's


Restore Database script I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and restore the same .bak file (on the same server) into a new database, this way, i always have the most up-to-date database to test on.

here are the steps:

back up database ABC (call it abc.bak)
drop (or clean/clear) database abc_test
restore abc.bak into abc_test

Please let me know if anyone know or have a script that does so.

Thanks!

You could set up a simple job to backup the database to a single location and then restore it. It would look something like this

--First we back up the DB
BACKUP DATABASE [ABC] TO  DISK = N'C:\temp\ABC.bak' WITH NOFORMAT, NOINIT,
        NAME = N'ABC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
        GO
--Then we restore the DB
        RESTORE DATABASE [ABC_test] FROM  DISK = N'C:\temp\ABC.bak' WITH  FILE = 1,  MOVE N'ABC' TO N'C:\DBFiles\ABC_test.mdf',
        MOVE N'ABC_log' TO N'C:\DBFiles\ABC_test_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
        GO

You might want to delete the file before you back it up. You could do this with powershell (my recommendation), xp_delete_file (undocumented SP), or xp_cmdshell (not sure if it is enabled on your system or not)


Mysql Windows Issues I've been having allot of issues with my mysql databases on Windows dying on me. About once a week or so it will refuse to work and I'll spend all night poking at it & a few computer restarts later things will magically start working it seems.

I've done some searching but didn't turn up much.

What would cause these errors whenever I try to access a table?

2014-01-06 20:10:14 c7c InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2014-01-06 20:10:14 c7c InnoDB: Error: Fetch of persistent statistics requested for table "memory28_web"."core_settings" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

I have just addressed this a month ago: InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

This usually happens when one upgrades mysql from 5.5 to 5.6. The cuurrent tools for MySQL upgrading does not account for the only 5 system tables that are InnoDB.

Such errors are really innocuous and would not impede InnoDB, provided you do not activate features that need them.

This answer does not address your real issues. It just explain away the error message you posted.

You can create the tables in phpmyadmin. Just run this:

CREATE TABLE `mysql`.`slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

UPDATE 2014-01-17 11:00 EST

You last commented

  It's weird, the database says those tables exist when trying to create them. But the errors say they don't... MySql is behaving ok at the moment. I'm going to keep this question open for a bit I think. Look for more clues next time it dies on me

The reason MySQL says the tables exist in simple. First, here is an XRay of InnoDB

If you look at ibdata1 in the picture, you see the data dictionary. The data dictionary is insisting that the tables exist. Your solution to clean this up is

Run set global innodb_fast_shutdown = 0;
mysqldump everything to a SQL file
shutdown mysql
delete ibdata1, ib_logfile0, ib_logfile1
startup mysql (ibdata1, ib_logfile0, ib_logfile1 get recreated)
See my posts

Apr 19, 2012 : MySql - Clean ibdata1
Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)

With a clean data dictionary, you can now create the missing InnoDB system tables.

Give it a Try !!!


Fastest way to insert 30 thousand rows in SQL Server I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest.

I have tried 30000 normal inserts

insert into ##MyTempTable (ID) ...

I have tried

insert into ##MyTempTable (ID) (
 select 1002000 union all
 select 1002001 union all
 select 1002002 ...

The fastest way so far has been:

insert into ##MyTempTable (ID)
values
(1002000),(1002001),(1002002),(1002003) ...

Because this type of insert only accepts a maximum of one thousand rows, so then I repeated that insert 30 times.

Would you be able to help me?

If you are really just trying to insert 30000 ID values that increment by 1, starting at 1002000, I highly doubt you'll be able to rig up anything in C# that will be faster than something like this:

INSERT ##MyTable(ID)
  SELECT 1001999 + n FROM
  (
    SELECT TOP (30000) n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) AS x;

In my lowly Windows VM on a Mac host this occurs sub-second.

For other ideas about generating sets / sequences without looping (e.g. a numbers table may be even faster), see this blog series:

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

If the values are defined and not some incremental sequence (e.g. 30,000 values you somehow coerced into a DataTable), then you can consider using a table-valued parameter. First, create this type in your database (you just do this once):

CREATE TYPE dbo.MyIDs AS TABLE(ID INT PRIMARY KEY);

Now have a stored procedure that accepts this:

CREATE PROCEDURE dbo.TakeMyIDs
  @m dbo.MyIDs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT ##MyTable(ID) SELECT ID FROM @m;
END
GO

Now call your stored procedure from C# and pass the DataTable as a parameter:

// assuming an active connection object, conn,
// and a populated DataTable called dtIDs:

SqlCommand cmd = new SqlCommand("dbo.TakeMyIDs", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvp1 = cmd.Parameters.AddWithValue("@m", dtIDs);
tvp1.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
cmd.Dispose();


Update column with different value for multiple rows using a csv file I often find myself using Excel to create update queries.

When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple).

Here's an example SQL Fiddle.

If I have a list like:

Productname,Text
OpenIDM,testing
OpenAM,Tested
OpenDJ,great

I want to update the table with these values, so I need to create queries like this:

update ForgeRock set text = 'testing' where productname = 'OpenIDM';
update ForgeRock set text = 'Tested' where productname = 'OpenAM';
update ForgeRock set text = 'great' where productname = 'OpenDJ';

The format I receive the "To Be Updated" string in could be anything: Excel, csv, text.

The number of rows involved varies, depending on the project. Sometimes it could be just a few thousand rows, but other times it could be tens of thousands. I never timed myself or the time it takes to execute, but the execution time isn't really the problem. I just want an easier way to achieve this.

By "easier", I mean I should be able to update the table as mentioned without the need of Excel or a temporary table.

For example: to load data from a file it is possible to use load data infile.

-- Create some working space:
CREATE TEMPORARY TABLE t (
    productname ...,
    text ...
);
-- copy all the .CSV into it:
LOAD DATA INFILE INTO t ... .csv ...;
-- do the Update en masse:
UPDATE ForgeRock
    JOIN t ON t.productname = ForgeRock.productname
    SET ForgeRock.text = t.text;

Assuming that nothing changes from run to run, exactly the same statements can be reused.

If you also need to insert "new" data, see IODKU.


What's the smartest way to find the scale of an unrestricted numeric type? By laziness and convenience I integrated quite a lot of data from a partner in an unrestricted numeric type in my PostgreSQL database.

However it now seems that depending of different shipments from this partner the scale of the numeric varies from zero to over 20 decimal places (or scale) which don't make a lot of sense and probably consume quite a lot of storage for no good reason. So I would like to restrict my numeric field to a reasonable scale.

However because my partner don't provide any recommendations I would like to identify the occurrence of each scale in my data-set to potentially identify a reasonable middle ground between 0 and 20 (zero obviously not being an option and given the 17 Million row-count, aggregated sums ARE going to be impacted by my decision).

It will also be needed during following rounding to avoid actually increase the scale of values that were shipped with a scale of zero.

Long story short, what the nicest way to compute that for a specific number stored as an arbitrary numeric?

Best I could come up to is that, but is there a more elegant way to do that without converting to text?

SELECT
  my_numeric,
  COALESCE(
    char_length(                            -- Finding size of string extracted by a...
      substring(my_numeric::text,'\.(\d*)') -- regexp to return all digits right from '.'
    ),                                      -- but if scale is 0 substring return NULL
  0                                         -- so I handled this inside a COALESCE
  ) AS my_numeric_scale
FROM
(VALUES
(0.1::numeric),
(0.12),(0.123),
(0.1234),
(0.12345),
(0.123456),
(0.000001),
(0.100000)
) foo (my_numeric)

is there a more elegant way to do that

Yes, use the scale() function which was introduced in 9.6:

SELECT my_numeric,
       scale(my_numeric)
FROM (
 VALUES
    (0.1::numeric),
    (0.12),(0.123),
    (0.1234),
    (0.12345),
    (0.123456),
    (0.000001),
    (0.100000)
) foo (my_numeric)

Online example: https://rextester.com/MVJQR23795


Un-optimize a MySQL database? I want to run ANALYZE TABLE and OPTIMIZE TABLE on a large production MySQL database (v 5.0) to increase performance.

But as this would lock tables causing downtime for an unknown amount of time, I want to test it on a separate instance first. I want to run some test queries on a test database, then optimize it, then run the test queries again.

Is it possible to copy a MySQL database while keeping the same un-optimized physical storage of table and index data?

No.  Do not expect much, if any, performance boost with OPTIMIZE TABLE.

ANALYZE TABLE is slow on MyISAM, the engine you should be moving away from.  It is fast in InnoDB.  It is almost never needed in either engine.  Only if you have a particular query that defies rational analysis by a human, should you consider ANALYZE.

OPTIMIZE TABLE will rebuild the table.  That requires blocking all writes to it for the duration.

Back to your final question...  Dumping the data (not the files) will provide you with something that can be loaded into another server for the testing you suggest.  However, the dump, itself, involves some degree of locking.  This depends on which engine you are using and whether you need a "consistent" dump.

SHOW TABLE STATUS will give you a clue of Data_free.  For MyISAM, that is how much space can be recouped with OPTIMIZE.  But even a large number there does not necessarily mean much performance boost.  For InnoDB, Data_free has less meaning and its meaning depends on the value of innodb_file_per_table when the table was created.


Database level health check did not trigger failover on losing a disk I'm testing the new database level detection option in 2016 SQL Enterprise SP2 CU7 edition, and it does not seem to be working as expected.  We have a 2 node setup, synchronous commit, automatic failover on both nodes.  Database level health detection option is checked.  On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG.  I ran a select * from a table which read from the missing disk, and got the expected 823 error, which was logged in the error log.  I ran it a few times, and the error log recorded the 823 multiple times.

The availability group did NOT fail over as it was supposed to when this happened.  I waited for about 3 minutes to see if a failover would occur, and it never did.  How can i find out how often the DB level health check routine is set to run?  I understand this needs to see the issue in 4 consecutive runs according to this article:
enhanced database level failover

I checked the health check timeout value in the AG, and it was 30 seconds.

I also reviewed the failure condition level on the server and it is set to On CriticalServerErrors, but as I understand it, this setting is completely independent of the database level health check, and either of them should be able to trigger a failover on their own.  Is this correct?

The only thing I can think of that is preventing this is the pending timeout in the WSFC manager.  this has a value of 3 minutes before it will take the cluster resource offline.

Any idea where else I should be looking for why this did not fail over?

On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG. I ran a select * from a table which read from the missing disk [...]

Since you're on 2016, the database level health checks are checking that the database is online (which taking secondary file offline won't change) and that we can write to the transaction log. Since both of these are true, your test passes. That's how it works in 2016.

  Any idea where else I should be looking for why this did not fail over?

Yes, see above. This was changed to encompass more in 2017.


psql coloured prompt misbehaving In theory, there is a possibility to use coloured prompts in psql.  I decided (not the first time :) that I need them, so I thought I would give them a try.  So I have in my .psqlrc the following:

\set prompt_colour '%[%033[1;33m%]'
\set reset_colour '%[%033[0m%]'

\set PROMPT1 :prompt_colour'%n@%/%R%#%':reset_colour

This is very nice, I have a fancy yellow prompt.  Everything is good until I don't start walking back in the command history and hit a long enough command (in my experiments, 37 is OK, 38 is too long and multiline commands of any length do the same) - when I do, the Home button does not go back to the end of the row, it stops (in this case) 11 characters to the right.  If now I try to edit the command, it gets edited in the wrong positions.

I try to depict this:

# SELECT 123456789012345678 FROM a_table;
[here is an error, of course, but it doesn't count]
# SELECT 123456789012345678 FROM a_table;
[ cursor is  ^ here after pressing Home ]
[ hitting Enter gives the error ]
ERROR:  syntax error at or near "ELECT"
LINE 1: ELECT 123456789012345678 FROM a_table;

(From the documentation it looks like this version should also work but here the control characters aren't parsed, so I have no coloured prompt by the two expressions echoed:

\set PROMPT1 "%:prompt_colour:%n@%/%R%#%:reset_colour:"

but this results in

%[%033[1;33m%]postgres@postgres=#%[%033[0m%]

)

So does anybody has any idea how to do this properly?

Somehow I managed to omit the versions so here they are:

ubuntu 12.10
psql 9.1.9 (as far as I know, it is installed from a standard ubuntu package)
both libreadline5 and 6 are installed, also libedit2

My findings:

Following Peter's advice, I tried modifying the pg_wrapper script to link against libreadline.so.6 - in itself it works but the error is still there.  The same with libreadline.so.5 (which is the default one, I think).  With libeditline.so.0 the situation is much worse.

I have experience this exact same thing in the past.  It usually happens because you have linked psql against an inferior readline-like library.  If you are using libedit, use libreadline instead.  If you are using libreadline, use a different one.  Details depend on your operating system and method of installation.


Should an INSERT cause an exclusive lock on a foreign key? I'm working on a deadlock issue.

Process A is doing a simple INSERT into TableA which has a FK to TableB

Process B is doing a complicated SELECT on a join that includes TableA and TableB

I'll include the trace info below, but basically what I think is happening is that the insert to TableA is causing an Exclusive Lock (X) on the Primany INDEX of TableB due to TableA having a FK to TableB.  We do have referential integrity enabled for that FK, but there is no update to TableB needed by doing an insert into TableA, so it seems odd to me that an exclusive lock is required just to check for the existence of the FK value.

Is this expected behavior?  If so, is there anything I can do to mitigate this?  I honestly wasn't expecting such a basic/vanilla insert to cause a deadlock.

Also, it's not my real question, but if you happen to know what "subresources=FULL" means I'd be interested to know.

EDIT: Just to be clear about the deadlock:

processInserting is inserting to TableA and has an X lock on the Primary Index on TableB (the foreign key for TableA).  ProcessSelecting is waiting for a RangeS-S lock on this index.

processSelecting is selecting from a join of many tables including TableA and TableB and has a S lock on TableA (Becuase it's joining on it).  ProcessInserting is waiting for an IX lock on this table.

EDIT 2: Giving some more details.  The "select" query I was calling processSelecting is an extremely torturous query that uses a torturous view as part of the join, so it's kind of a mess to look at.

This is the DDL for the RoutePlan (TableA) and Form (TableB) tables.

http://pastebin.com/gWftciEG

RoutePlan has a trigger on it to log historical changes:

http://pastebin.com/WjvNxPFK

The SP doing the INSERT into Routeplan is:

http://pastebin.com/c6JqNFaX

The SP doing the SELECT is:

http://pastebin.com/micNhuPf

And the View CurrentAndPriorApprovers_View is defined as

http://pastebin.com/hHSzNJyc

And here is the full trace log info

http://pastebin.com/qenwhJcN

resource-list

keylock hobtid=72057594059882496 dbid=66 objectname=TableB indexname=PK_TableB id=lock204d08500 mode=X associatedObjectId=72057594059882496

owner-list
 owner id=processInserting mode=X

waiter-list
 waiter id=processSelecting mode=RangeS-S requestType=wait

objectlock lockPartition=0 objid=516196889 subresource=FULL dbid=66 objectname=TableA id=lock2044c5080 mode=S associatedObjectId=516196889

owner-list
 owner id=processSelecting mode=S
waiter-list
 waiter id=processInserting mode=IX requestType=convert

Here's my first recommendation to remove the deadlock (and this is a very common cause with the same resolution every time). Your RoutePlan_Save procedure currently has this logic:

IF EXISTS (SELECT ... WHERE key = @key)
  UPDATE ... WHERE key = @key;
ELSE
  INSERT(key) VALUES(@key);

So you actually have two queries here that require locks on the table, regardless of whether you are ultimately going to perform an insert or update. Stop doing that! What is the point of checking if a row exists, and then in a separate query, firing an update statement that again has to check if the row exists? This requires more resources, not less, than just trying to update the row not knowing whether it exists. So do this instead:

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

You can also isolate the other read-only processes by using a different isolation level that doesn't require them to wait for write operations to complete. NOLOCK / READ UNCOMMITTED is a popular one, but READ COMMITTED SNAPSHOT - assuming you can take the hit on tempdb - is way better IMHO. There are other alternatives, too. I happened to blog about this just yesterday:

Bad habits : Putting NOLOCK everywhere


Strategy for backup for databases with little use (almost archived databases) SYSTEM:
I have a system with about 150 databases, each of about 2gb in size.

130 of those are mostly inactive but need to be online. Those are set to simple recovery, active dbs are set to full.

I have a daily full backup and hourly tran log backups. I am using sql server maintenance plan backup and I am backing up all user databases

PROBLEM
This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.

QUESTIONS
I am looking for a back strategy that would not result in unnecessary backup size.

I am not using differential backup (which I think would reduce the size of the backup of the inactive DBs) because I am afraid of having to deal with large set of files in case of having to rebuild from backup.

I know I could maintain different backups maintenance plans, and update it as a db goes inactive, and as I add new active databases, however, this is manual process and we add a few databases a month. I've done this before and mistakes were made...

Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?

How about the backup compression? I have never used it, not sure what this will do to my CPU usage (currently the DB server is typically <15% CPU)

Thank you!

PS, i am not a DBA, but a developer.

This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.

As you said you are not using compression I believe you can get good relief when you use backup compression. Just forget about the CPU utilization I have used backup compression many a times and the CPU utilization is just 5-10 % more in extreme cases. I suggest you to use it but before that read The Documentation

  I am looking for a back strategy that would not result in unnecessary backup size.

The backup strategy is to make sure you have valid backup when needed and using that you can loose as less data as possible, there is hardly a strategy for making backup size less unless you use data compression or backup compression.

  Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?

Backup size as such is not influenced by much by recovery model it determines the amount of data loss you can have in case disaster strikes.

Moral: Use backup compression it might solve all your queries.


UserNotFound mongodb error I know there are lot of articles with the same issue and I explored every other article trying everything and still this error persists.
Here are the steps I followed:

use admin.

created an admin user with role: dbAdminAnyDatabase

use mydb.

created an admin user with different name for mydb

created a readonly user with: db.createUser({user: "nameuser", pwd: "password", roles: [{role: "read", db: "mydb"}]});

setup a mongod.conf file with:

security:
authorization: enabled
net:
port: 12345
bindIp: 0.0.0.0   #default value is 127.0.0.1

then run the command: sudo mongod --port 27017 --dbpath ~/data/mongodb --config /etc/mongod.conf

and then on localhost i run the command to check:
mongo -u nameuser -p password localhost:12345/mydb

then the error come as:
connecting to: mongodb://localhost:12345/mydb MongoDB server version: 3.6.3 2020-10-04T03:05:37.054+0530 E QUERY    [thread1] Error: Authentication failed. : DB.prototype._authOrThrow@src/mongo/shell/db.js:1608:20 @(auth):6:1 @(auth):1:2 exception: login failed

and on server side the error looks like this:
2020-10-04T02:53:11.835+0530 I NETWORK  [listener] connection accepted from 127.0.0.1:60240 #3 (1 connection now open) 2020-10-04T02:53:11.835+0530 I NETWORK  [conn3] received client metadata from 127.0.0.1:60240 conn: { application: { name: "MongoDB Shell" }, driver: { name: "MongoDB Internal Client", version: "3.6.3" }, os: { type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" } } 2020-10-04T02:53:11.836+0530 I ACCESS   [conn3] SCRAM-SHA-1 authentication failed for nameuser on mydb from client 127.0.0.1:60240 ; UserNotFound: Could not find user nameuser@mydb 2020-10-04T02:53:11.837+0530 I NETWORK  [conn3] end connection 127.0.0.1:60240 (0 connections now open)

Now please tell me if I can try anything new. I have tried so many articles from stackoverflow but none address the issue. Every resolved issue is the common mistake which I have checked already and I am not doing any of these. Tell me if you have any suggestion to work this out.

You need to use

--authenticationDatabase admin

parameter...  Because, even you give "use mydb" before creating user...  User is created at the admin -database.
So,
mongo -u nameuser -p password  --authenticationDatabase admin localhost:12345/mydb


Calculating the maximum seen so far for each point in time What I'm trying to calculate is a bit tricky to describe, so please bear with me...

I have a table with product numbers, and the price of each product each week over a period of 4 years. Price can go up or down between the weeks. For each date, I'm trying to calculate the maximum price up to that point in time for each product.

In the sample table below, the third column (Price) is the data I have; the fourth column (Maximum Price PIT) is what I'm trying to calculate. - EDIT: to be clear, this column does not exist yet!

For example, on 15/01/2012 for product 001, the maximum price seen up to that point in time was 50, even though in the next week it goes up to 60 (which is the maximum over the whole period).

I think that I might need to use some kind of loop, but I really don't know where to start. I have a pretty good grip of the basics of SQL, but I get a bit fuzzy when it gets to window functions and further. I've googled the problem quite a bit, but haven't been able to find any pointers.

Can anyone help? Let me know if you need me to clarify what I'm asking for!

Product | Date  | Price | Max Price PIT (this column is what I want to calculate)
001   01/01/2012    25  25
001   08/01/2012    50  50
001   15/01/2012    35  50
001   22/01/2012    60  60
001   29/01/2012    50  60
001   05/02/2012    15  60
002   01/01/2012    18  18
002   08/01/2012    7   18
002   15/01/2012    10  18
002   22/01/2012    20  20
002   29/01/2012    30  30
002   05/02/2012    25  30

Thank you!

p.s. the table is 19391864 rows.

If you need a rolling max you can use both CROSS APPLY or LEFT JOIN, here is the CROSS APPLY version

SELECT Product, Date, Price, b.[Max Price PIT]
FROM   Table1 a
       CROSS APPLY (SELECT MAX(Price) [Max Price PIT]
                    FROM   Table1 b
                    WHERE  a.Product = b.Product
                      AND  a.Date >= b.Date) b

SQLFiddle demo

With SQLServer 2012 or better it'll be possible to use the new ROWS clause to get the same effect

SELECT Product, Date, Price
     , MAX(Price) OVER (PARTITION BY Product ORDER BY [Date]
                        ROWS UNBOUNDED PRECEDING) [Max Price PIT]
FROM   Table1

The key point is to get all the rows before the current for the group and apply the statistical function only to them, sometime this type of presentation is called "rolling", for example rolling sum/total if you get the total value of cash till the current row.


How to maintain Index and Statistics on AWS RDS? In the process of setting up the SQL Server Index and Statistics Maintenance code on our AWS RDS instance, I've managed to set up the scripts and can run it from SSMS.

Please advice on how to setup SQL Server Agent considering the restrictions of RDS?

I am running SQL Server RDS maintenance jobs (DBCC CHECKDB, index reorgs/rebuilds, statistics updates) without a problem.  The only limitation I had to work around was that I could not run sp_updatestats, so instead had to generate an 'UPDATE STATISTICS' script for each table like this:

DECLARE @sql NVARCHAR(max) = '';
SELECT @sql = @sql+
'UPDATE STATISTICS ' + '[' + table_name + ']' + ' WITH FULLSCAN;
PRINT ''UPDATING STATISTICS ON ' + table_name +'...'';
'
FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @statement=@sql;


Best way to backup mysql database? How do you backup your mysql database so you can be sure that you are 100% protected of data loss whatever problems that may happen? I am thinking to rent one or two servers that i will use only for backup. Then i will setup rsync cron jobs that will do incremental backups daily to these two servers. Will this be enough? Do you use other methods than rsync to backup you database?

In my biased opinion (I'm author of TwinDB) the best way to take backups from MySQL is to use TwinDB. Not only it will save from blunders like rsyncing database files (which with 100% guarantee leads to corrupted tablespace) but also lets you take incremental copies correctly.


Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime? Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler.
create proc spFoo
    @param bit
as
begin
    if @param = 0
    begin
        select *
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        select top 10 *
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

Attempting the above returns the following error

Msg 2714, Level 16, State 1, Procedure spFoo, Line 19
There is already an object named '#bar' in the database.

In a human-readable sense, the proc appears to be fine: only one select into statement will ever be executed since they're wrapped inside the if-else blocks. Very well though, SQL server can't confirm that the statements are logically excluded from each other. Perhaps more confusing though is that the error remains when the drop table #foo is placed inside the if-else block ( which one assumes would tell the compiler to deallocate the object name ) as below.
create proc spFoo
    @param bit
as
begin
    select top 1 *
    into #bar
    from [master].dbo.spt_values

    if @param = 0
    begin
        drop table #bar;

        select *
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        drop table #bar;

        select top 10 *
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

The proc itself is fine. I sucked it up and wrote the create table #foo( ... ) and insert #foo ( ... ) statements, I'd been trying to skip with the select * into  syntax. At this point, I'm just trying to understand why the compiler crapped out on me with the lazy-guy syntax. The only thing I can think of is that the DDL command reserves the object name IN TEMPDB.
Why the bold text?
create proc spIck
as
begin
    create table #ack ( col1 int );
    drop table #ack;
    create table #ack ( colA char( 1 ) );
    drop table #ack;
end;

This fails with the same error code as above. But the following...
create proc spIck
as
begin
    create table ack ( col1 int );
    drop table ack;
    create table ack ( colA char( 1 ) );
    drop table ack;
end;

... succeeds. The same follows above to the original proc attempt. So...
My Question Is This
What is the difference ( and why is it present ) in object name reservation for TempDB objects as opposed to user databases. None of the Logical Query Processing references nor DDL command references I've reviewed appear to explain this.

This has nothing to do with object name reservations in TempDB or anything to do with runtime. This is simply the parser not able to follow logic or code paths that assures that your code couldn't possibly try to create that table twice. Notice that you get the exact same (non-runtime!) error if you just click the Parse button (Ctrl+F5). Basically, if you have this:

IF 1=1
  CREATE TABLE #foo(id1 INT);
ELSE
  CREATE TABLE #foo(id2 INT);

The parser sees this:

  CREATE TABLE #foo(id1 INT);
  CREATE TABLE #foo(id2 INT);

Why does it not work this way for actual tables, including actual user tables created in TempDB (note that it isn't database-specific, either)? The only answer I can suggest is that the parser has a different set of rules for #temp tables (there are a lot of other differences, too). If you want more specific reasons, you'll need to open a case with Microsoft and see if they'll give you any further details. My guess is you will be told: "this is the way it works."

Some more info in these answers:

Is it "legal" to CREATE and DROP #SomeTable more than once?
Can I recreate a temp table after dropping it?


how service sid operate under the covers? This question is out of curiosity and it left me in confusion whether service account as any role to play in SQL Server startup and shutdown?

Can anybody explain how SQL Server 2008 Service Sids work and what is difference between Service SID and Service Account?

Little background :
From SQL Server 2008, I see the service sid's are added to the SQLServer Groups which gets created on the computer.
For example, I have installed SQL Server 2008 instance on my Desktop PC and when open Computer management and check the sql server groups for my newly installed instance,
I see that Service SID is added to the group.

Start -> Run -> compmgmt.msc

I used the below command at command prompt to view the SID. I see the same SID is getting added to the windows groups created by sql server instance.

sc showsid MSSQLSERVER

Now my actual question is, prior to SQL Server 2008, we used have domain account used as service account and this is one which gets added to the Groups which are created during SQL Server installation.
But from SQL Server 2008, it is the service SID which is being added to the Groups which gets created.
If the service SID is now assigned the necessary permissions, what is the need for the Service account then? Is is just a container for the service?

It makes even more interesting if we consider Windows Server 2008 and SQL Server 2008 clusters.
On Windows Server 2008 cluster, the cluster service runs as Local system  and considering I am using service sids instead of domain group, how come WFCS is able to communicate with Active Directory to bring the CNO's (sql network name) online and offline.

Any comments on that? Basically I wanted to know how these SID's works under the covers and what is the use service account from SQL Server 2008 onwards?

Service SIDs aren't a SQL Server thing. It's a technic inside windows to isolate different services from another. In the basic example where sql server is using NetworkService as account, many other services could have access to the SQL Server or its files.

Service SIDs and domain accounts have another importang aspect: if you change the domain account, all ACL secured objects are still valid and don't have to be updated.

See also http://support.microsoft.com/kb/2620201


date difference in postgresql I have dates in 2 tables. Table student is as below:

ID|Name|Examdate
1  John 01/10/2015
2  Fred 11/12/2014

Table retake as below:

ID|RetakeDate
1  02/10/2016
2  05/11/2015

The IDs in retake relate to the students' IDs in student, i.e. they are the same.

I am trying to create another table where only students who have taken a retake after 12 months or more exist. The difference between examdate and RetakeDate needs to be >= 12months.

How do I achieve this?

There could be multiple rows in both tables for the same student. There will be multiple rows on both tables, as this will allow me to see how many times they have taken a retake and how many times an exam. At this moment in time I don't want to see if the retake is for the latest exam. I just want to see how many retakes they have taken all together.
End result should be comparing each Exam date with Retake date. Some more explanation:

  Q: So  if a student has dates in 2012, 2013, 2014 and then 2 retakes in 2016?

  A: There should 3 rows in the result (one for each date)

  Q: What if it's three exams – in 2012, 2013, 2014 – and (a) two retakes – in 2014 and 2016? or (b) one retake – in 2014? What should be expected as the result in each of those cases?

  A: It doesn't matter. As long as the (a) retake was taken after 12 months or more, they should appear in the result.

This query will return the times a student retook an exam after more than 12 months from the first take:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND RetakeDate::date - '12 months'::interval >= Examdate::date
GROUP BY Name

It subtracts 12 months from RetakeDate and checks if the resulting date is greater or equal to Examdate.

This query works too:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND age(RetakeDate::date, Examdate::date) >= '12 months'::interval
GROUP BY Name

The function age(timestamp, timestamp) is documented here: https://www.postgresql.org/docs/current/static/functions-datetime.html

ATTENTION

Be aware that those two queries can give different results for periods that are exactly 12 months. The following returns true:

select age('2000-12-31'::date, '2000-01-01'::date) = '12 months'::interval;

 ?column?
----------
 t
(1 row)

while this query returns false:

select '2000-12-31'::date - '12 months'::interval = '2000-01-01'::date;

 ?column?
----------
 f
(1 row)

In fact:

select '2000-12-31'::date - '12 months'::interval;
      ?column?
---------------------
 1999-12-31 00:00:00
(1 row)

while:

select age('2000-12-31'::date, '2000-01-01'::date);
       age
-----------------
 11 mons 30 days
(1 row)

It is your choice to choose age() over date - interval. To solve this, you can use > against >=.


Multiple one-to-one relations I'm trying to design database relations for archaeological places in a country. There are around 9000 places registered. An archaeological place can have multiple types. Types will be like Cave, Mound, Necropolis...There are around 100 different types. For example an archaeological place can be a necropolis and a mound at the same time. For different types, different attributes will be valid. For example, a mound type will have attributes such as diameter, direction etc...Another type, for example, a cave will have attributes such as depth etc...
Common attributes for archaeological places are id, name and location. I thought about creating a table with these common attributes and link to other tables. Each table linked to will hold information specific to a certain type.
The problem is there will be multiple one-to-one relations.
A particular id, name or location entry may point to tables type1, type2 etc.. but it cannot point to two or more different entries in the same table. Number of different tables an entry points to differs. It can point to one or 10 or more. So it is not balanced. If I create 100 one-to-one relations and most of them will be empty for most of the entries.
Can there be a better design for representing such data? Better in terms of compactness... I can simply create a huge table with all the attributes as columns but some of the attributes will simply mean nothing for some types. Are there different ways to structure multiple one-to-one relations?
Edit:
Here is an example:
Lets say that there are 3 types

A, B, C

Attributes for these types are as follows:

A: x, y, z
B: z, k
C: x, m

A sample table will look like as follows:
Entry   Location    Type    x   y   z   k   m
entry1  location1   A       o   o   o
entry2  location1   B               o   o
entry3  location2   B               o   o
entry4  location4   C       o               o

"o" represents data.
As you can see, entry1 and entry2 share the same location but their type is different.
I started with creating a table for locations(plus names which are unique to locations). Then I will have seperate tables for each type. Entries in location table will have one-to-one connections to different type tables. I couldn't find a better way to organize this.
At the end, I will create an app which shows archaeological places nearby. Users will be able to select and filter certain types.

Your problem can be solved with the following set of tables:

A table location, with attribute id, and attributes common to all location (like position, etc.)

A table for each type of location, with all the attributes specific with that table, and an attribute location_id which is a foreign key for the table location.

Then you could consider of adding, if necessary, an attribute to the table location that represent in a compact form all the types associated to that location (for instance an array if you use PostgreSQL, or something similar).


Pros and cons in combining current data and historical data in the same table What is the pros and cons in designing a RDB table to store both current data and historical data together? Is it scaleable?

Is this approach better than having one table for current data and one table for historical data?

I have old posts where I have discussed something like this before

Nov 30, 2012 :  How much does table size affect performance?
Oct 07, 2011 : How Does Table Partitioning Help?

The pros can never outweigh the cons. The reason ? Should you attempt to scale hardware and cache settings, you make room for both current and historical data to be cached. This introduces the possibility of the following:

Caching old data
Not using the old data that was recently cached
Having to prune that old data from caches to make room for current and historical data
Start these three problems over again

Even if you tune queries, caches could still suffer. How?
MyISAM
Recall that there are session-level caches for reading ahead for data. This is set by read_buffer_size and read_rnd_buffer_size. This affects only MyISAM tables since MyISAM only caches indexes system-wide. Each thread caches its own data.
InnoDB
Retrieving pages of data you may not need is also possible if your queries do full table scans or index range scans. Since each page for InnoDB is 16K, a page could end up with both current and historical data if you are not paying attention and just loading both willy-nilly.
SUGGESTIONS
Keep current data in one table and historical in another. It will be faster to retrieve current data. You can also scale caches for current data only.
If you really want old and new data in one table, do the following:

Create an empty database
Load all historical data first, sorted by datetime
Load all current data next, sorted by datetime

As an added bonus, you should consider partitioning tables by month. This will spread out table data and corresponding indexes. This makes it easier to defragment or delete by partition.


SQL Query - Pivot with Ranges This is my sample table
BSEG_ID DST_ID  BILL_SQ AUDIT_CALC_AMT  BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT
12334   REV-WSC 62      6.40667         For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter
12334   REV-WSC 62      64.06667        For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter
12334   REV-WSC 3.6     11.16           For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter
12334   REV_WWSC 62     3.20333         For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter
12334   REV_WWSC 62     32.03333        For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter
12334   REV_WWSC 3.6    5.58            For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter
11111   REV-WSC  62     6.40667         For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter
11111   REV-WSC  62     64.06667        For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter
11111   REV-WSC 3.6     11.16           For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter
11111   REV_WWSC 62     3.20333         For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter
11111   REV_WWSC 62     32.03333        For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter
11111   REV_WWSC 3.6    5.58            For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter

Expected Output #1
Range for Bill_SQ
BSEG_ID DST_ID     0-10    10> <= 20    >20
11111   REV_WWSC    0          0         1
11111   REV-WSC     0          0         1
12334   REV_WWSC    0          0         1
12334   REV-WSC     0          0         1

Expected Output #2
Range for Sum of AUDIT_CALC_AMT
BSEG_ID DST_ID      0-10    10> <= 20   >20
11111   REV_WWSC    0         0          1
11111   REV-WSC     0         0          1
12334   REV_WWSC    0         0          1
12334   REV-WSC     0         0          1

This is my current query:
select a.bseg_id,a.dst_id, sum (a.bill_sq) as WaterConsumption ,sum (a.AUDIT_CALC_AMT) as SWConsumption from CI_BSEG_CALC_LN a
--where a. BSEG_ID = '209875510608'
AND a.DST_ID in ('REV-WSC','REV-WWSC')
group by a.bseg_id, a.dst_id;

Please suggest a way for achieving the desired output.
This is what I have done so far
select * from ( select Bseg_ID , BILL_SQ, count (*)
from CI_BSEG_CALC_LN
where BILL_SQ Between '0' AND '5'
--AND BILL_SQ Between '6' AND '10'
--AND BILL_SQ Between '11' AND '15'
--AND BILL_SQ Between '16' AND '20'
--AND BILL_SQ > '20'
group by Bseg_ID, BILL_SQ) PIVOT (Count (BSEG_ID) for BILL_SQ IN ('0-5'));

In Mysql You can use SUM with an comparision, which returns 1 if the condition uis met or 0 if not.

SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`BILL_SQ` BETWEEN 0 AND 10) '0-10',
    SUM(`BILL_SQ` > 10 AND `BILL_SQ` <= 20) '10> <= 20',
    SUM(`BILL_SQ` > 20) '>20'
FROM CI_BSEG_CALC_LN
GROUP BY `BSEG_ID`, `DST_ID`

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2

SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`res_sum` BETWEEN 0 AND 10) '0-10',
    SUM(`res_sum` > 10 AND `res_sum` <= 20) '10> <= 20',
    SUM(`res_sum` > 20) '>20'
FROM
    (SELECT
         `BSEG_ID`, `DST_ID`,
         SUM(`AUDIT_CALC_AMT`) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY `BSEG_ID`, `DST_ID`) t1
GROUP BY `BSEG_ID`, `DST_ID`

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1

db<>fiddle here
For ORACLE

SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN BILL_SQ >= 0 AND BILL_SQ <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN BILL_SQ > 10 AND BILL_SQ <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN BILL_SQ > 20 THEN 1 ELSE 0 END) ">20"

FROM CI_BSEG_CALC_LN
GROUP BY BSEG_ID, DST_ID

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  11111 | REV-WSC  |    1 |         0 |   2

SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN res_sum >= 0 AND res_sum <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN res_sum > 10 AND res_sum <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN res_sum > 20 THEN 1 ELSE 0 END) ">20"
FROM
    (SELECT
         BSEG_ID,DST_ID,
         SUM(AUDIT_CALC_AMT) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY BSEG_ID, DST_ID) t1
GROUP BY BSEG_ID, DST_ID

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  11111 | REV-WSC  |    0 |         0 |   1

db<>fiddle here


Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB? I'm trying to config user to run logMiner from scheduled job periodly.

my user called admin
I've grant the admin user the following:

 grant select on v_$database to admin
 grant execute on DBMS_LOGMNR to admin
 grant select on v_$logmnr_contents to admin

but still, when I'm trying to select from v$logmnr_contents I get - ORA-01031 insufficient privileges.

for testing I'm running the following script:

create or replace procedure Test_LogMiner AS
first_scn NUMBER(8);
last_scn NUMBER(8);
log_count NUMBER(8);
BEGIN
  Select current_scn into last_scn from V$DATABASE;
  --Select max(mined_scn) into first_scn from my_table;
  first_scn := last_scn-100;
  DBMS_LOGMNR.START_LOGMNR(startScn=>first_scn, endScn=>last_scn,
     options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE);
  WRITE_LOG('LOG_DIR', 'After START_LOGMNR');
  -- get error on this select - ORA-01031 insufficient privileges--
  select count(*) into log_count from v$logmnr_contents;
  WRITE_LOG('LOG_DIR', 'select count(*)='||log_count);
  DBMS_LOGMNR.END_LOGMNR();
  WRITE_LOG('LOG_DIR', 'After END_LOGMNR');
EXCEPTION WHEN OTHERS THEN
  WRITE_LOG('LOG_DIR', 'Error: '||SQLERRM);
END;

I can ran the same scenario, mean start logMiner and select from v$logmnr_contents, when it not run as procedure, from SQL developer as admin user (means: the create proc. removed and the rest code put in another begin / end)

It also runs when I run the proc from 'sys as dba' user.

My question is: What else should I grant in manner the procedure work?

You need EXECUTE_CATALOG_ROLE to be assigned to the user.

  You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.

For details:Using LogMiner to Analyze Redo Log Files


Split varchar2 variable based on vertical line delimiter I have following plsql code

DECLARE
   v_personal_info VARCHAR2(100);
   v_name VARCHAR2(100);
   v_signature VARCHAR2(100);
   CURSOR c_personal_info
   IS
     select personal_info from users where name is not null;
BEGIN
  FOR lr_row IN c_personal_info
  LOOP
    v_personal_info := lr_row.personal_info;
    -- Need to split v_personal_info based on |
  END LOOP;
END;
/

Typical values of v_personal_info will look like 'Aniket Thakur | athakur' and I need to split this to different variables like

v_name := 'Aniket Thakur'
v_signature := 'athakur'

How should I do that. Is there any in built function that does a split based on predefined delimiter?

You can use the functions INSTR to locate the poition of '|' in the string and SUBSTR to retrieve the relevant part of the string.

So

select
    SUBSTR(personal_info, 1, INSTR(personal_info, '|') - 1),
    SUBSTR(personal_info,INSTR(personal_info, '|') +1 )
from users

will returrn two columns with the appropriate parts of personal_info.


What is the correct way of filtering a property by multiple features? Having the following tables:

Property
Feature
PropertyHasFeature (property_id, feature_id)

What is the right way to search properties by specifying multiple features? I want to list all properties that have both Feature A and Feature B.

Right now my approach is to join the Property table two times and to add two conditions, like this:

INNER JOIN Feature as feature_1
ON feature_1.property_id=Property.id

INNER JOIN Feature as feature_2
ON feature_2.property_id=Property.id

WHERE feature_1.id = <ID of first feature> AND feature_2.id = <ID of second feature>

Is this the right way? So if I the users selects 10 features I will have to do 10 joins with the Feature table? Or is there a better way?

It is better to use something like this:

select distinct p.* from Property p
inner join PropertyHasFeature fp on (fp.p_id = p.id)
inner join Feature f on (fp.f_id = f.id)
where f.id in (<ID of first feature>, <ID of second feature>)

In this case you will not need to add the joins when the feature list is getting larger. This will select all the properties having one of the required features.

If you need properties having both features without adding multiple joins, you can try using the following (works for MySQL):

select id, property_text from
(select p.id as id, p.property_text, count(f_id) as list_count from Property p
 inner join PropertyHasFeature fp on (fp.p_id = p.id)
 inner join Feature f on (fp.f_id = f.id)
 where f.id in (<ID of first feature>, <ID of second feature>)
 group by p_id) as sub
where list_count = 2

To expand the list of the required features, you will have to change the list_count value to the length of the list of your required properties.


Join without indexing the entire table I have a query that pulls the 3 most recently commented posts on our forum. The issue is is that COUNT(forum_posts.id) as posts, is indexing the entire database of 600,000 posts and the query takes forever to complete. I'm left joining the table like this LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id. Apparently I will have to use another approach to this. Any suggestions?

SELECT    forum_topics.id,
         forum_topics.title,
         forum_topics.date_created,
         forum_topics.updated,
         forum_topics.last_activity,
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content,
         forum_topics.author_id,
         users.username,
         users.avatar,
         COUNT(forum_posts.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id
GROUP BY forum_topics.id
ORDER BY forum_topics.last_activity DESC LIMIT 3

Result from SHOW CREATE TABLE forum_posts

CREATE TABLE `forum_posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8mb4 NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `editor_id` int(10) unsigned DEFAULT NULL,
  `topic_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102183 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics_seen` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4

CREATE TABLE `forum_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `game` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `icon` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_order` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `author_id` int(11) unsigned DEFAULT NULL,
  `editor_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_activity` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `views` int(11) NOT NULL DEFAULT 0,
  `is_locked` int(11) NOT NULL DEFAULT 0,
  `is_sticky` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_forum_topics_forum_categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7040 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE users
(
  id INTEGER,
  username VARCHAR (30),
  avatar   VARCHAR (30)
);

Mysql version: 10.4.8-MariaDB

You need to add these indexes:

CREATE INDEX ix_forum_posts_topic_id ON forum_posts(topic_id);
CREATE INDEX ix_forum_topics_last_activity ON forum_topics(last_activity);
CREATE INDEX ix_forum_topics_seen_topic_id_user_id  ON forum_topics_seen(topic_id, user_id);

After that you could use this simple query:

WITH CTE_topics AS (SELECT id FROM forum_topics ORDER BY last_activity DESC LIMIT 3)
SELECT    forum_topics.id,
         forum_topics.title,
         forum_topics.date_created,
         forum_topics.updated,
         forum_topics.last_activity,
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content,
         forum_topics.author_id,
         users.username,
         users.avatar,
         (SELECT COUNT(forum_posts.id) FROM forum_posts WHERE  forum_posts.topic_id = forum_topics.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics
INNER JOIN CTE_topics ON forum_topics.id = CTE_topics.id
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id;


Is there a way to speed up a DISTINCT query? I have a table t in a database (PostgreSQL 10.4):

\d t;
                Table "public.t"
  Column  |          Type          | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
 sn       | character varying(11)  |           |          |
 site     | character varying(50)  |           |          |
Indexes:
    "site_2018_idx" btree (site), tablespace "indexspace"
    "sn_2018_idx" btree (sn), tablespace "indexspace"

I need to find distinct 'sn's for specific site, and I do it like this:

SELECT DISTINCT sn FROM t WHERE site='a_b301_1' ORDER BY sn ;

It works, but is very slow, to return 75 distinct 'sn' values it takes about 8 minutes!
Is there a way to speed it up?
The explain analyze gives this output:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=42873094.21..42873103.25 rows=3615 width=12) (actual time=190431.413..190431.417 rows=75 loops=1)
   Output: sn
   Sort Key: t.sn
   Sort Method: quicksort  Memory: 28kB
   ->  HashAggregate  (cost=42872844.42..42872880.57 rows=3615 width=12) (actual time=190431.233..190431.263 rows=75 loops=1)
         Output: sn
         Group Key: t.sn
         ->  Bitmap Heap Scan on public.t  (cost=874850.36..42695793.24 rows=70820471 width=12) (actual time=8755.163..168773.143 rows=43096912 loops=1)
               Output: sn, site
               Recheck Cond: ((t.site)::text = 'a_b301_1'::text)
               Heap Blocks: exact=783666
               ->  Bitmap Index Scan on site_2018_idx  (cost=0.00..857145.24 rows=70820471 width=0) (actual time=8540.835..8540.835 rows=43096912 loops=1)
                     Index Cond: ((t.site)::text = 'a_b301_1'::text)
 Planning time: 0.466 ms
 Execution time: 190433.289 ms
(15 rows)

Additional information

After I created another index, as was recommended (site, sn), the time decreased significantly, from 8 min to 30 sec. It is great, I just don't understand why is this the case? How is one multi-column index better than two separate indexes, in this case?

As suggested, an index on (site, sn) could speed this up, especially if the table is well-vacuumed so that it benefits from index-only scans.

If that is not enough of a speed up, then there is a way to use this index in a "skip scan" or "Loose Index Scan" to speed up queries where the number of distinct values is much lower than the size of the index. Unfortunately PostgreSQL's planner does not automatically detect opportunities for this, but you can force it yourself by writing a recursive common table expression.  You can find a discussion of this technique on the PostgreSQL wiki, although you will need to adapt it to your parameterized version.

The resulting queries are quite ugly, so you might want to wrap them into a view.  Or in this case, since it is parameterized, into a set-returning function.  Something like this works for me:

CREATE FUNCTION public.foobar(text) RETURNS SETOF text
    LANGUAGE sql
    AS $_$ with recursive r as (
  (select sn from t where site = $1 order by sn limit 1)
   union all
  SELECT (SELECT t.sn FROM t WHERE site=$1 and t.sn > r.sn ORDER BY sn LIMIT 1) from r where sn is not null
)
select * from r where r.sn is not null $_$;

Setting up the table and index with:

create table t as select
    floor(random()*1.2)::int::varchar as site,
    (-floor(50*log(random()))::int)::varchar as sn
from generate_series(1,10000000);

create index on t (site ,sn);


db2cli license file not being used I'm using the db2 driver for CLI and ODBC to configure an ODBC connection to DB2 on IBM i/OS from CentOS. I'm trying the following command:
db2cli execsql -connstring "protocol=TCPIP;database=MyDB;hostname=MyHost;ServiceName=446;uid=MyUser;pwd=MyPass"

And getting the following error which seems pretty straightforward:
[IBM][CLI Driver] SQL1598N  An attempt to connect to the database server failed because of a licensing problem.  SQLSTATE=42968

I believe I have the paths and everything correct:
# Environment
export PATH=$PATH:/opt/DB2/odbc_cli/clidriver/bin/
export DB2_CLI_DRIVER_INSTALL_PATH=/opt/DB2/odbc_cli/clidriver
export LD_LIBRARY_PATH=/opt/DB2/odbc_cli/clidriver/lib

license file: /opt/DB2/odbc_cli/clidriver/license/db2consv_is.lic

I have also attempted to move the license to a few other paths without any success. It is valid and works on another (windows) installation without issues. Any idea what I can troubleshoot from here?

This was due to a version mismatch between the license file and the ODBC driver. The licence .lic file has ProductVersion=10.5 in text in the body. Using the matching driver version has fixed the issue for me.


HELP with SQL WHERE syntax I have to pull out a range of entries from a database, all based around two pieces of data.  In itself that's fine. An example of the data is:

Record   ID    Tags
1        1423  Day, Sun, Warm
2        1423  Day, Sun, Wet
3        1742  Night, Warm
4        2743  Night, Warm, Dry, Stars
5        4832  Sunrise, Bright, Clear, Cool

Now in the above data, the ID is an internal company ID, but Record is a unique DB ID.  There are 2,500 rows of similar data in the database, with other data that's not related to this query.  Now we need to pull the data in the following style, written here in a symbolic, rather than specific SQL so people can understand it.  I've included brackets to emphasis which parts must match:

WHERE (ID=1423 AND tags contains Warm)
OR WHERE (ID=2743 AND Tags contains Dry)
OR WHERE (ID=4832 AND Tags contains Cold)

The question is how do I get the SQL statement to work right?  I've tried

SELECT DISTINCT ID
FROM DBTABLE
WHERE ID=2743 AND Tags LIKE '%Warm%'
    OR ID=4832 AND Tags LIKE '%Cool%'

But it doesn't seem to return the correct records.  I have a specific query which I know will return 5 records, but every time it returns more, including ones that don't match.  I'm thinking the SQL is mixing up, for example, where ID=2743 AND (tags like warm or 2743) AND Tags like dry or 4832 etc...

Can anyone point me in the right direction.  In essence it is multiple where statements, where each statement has to match an ID and have a 'LIKE' tag. but the statement can match one or more of the given WHERE statements.  Does that make sense?

As Martin Smith and dezso commented, you can add parentheses to your query to be explicit in your intent since they can change how the where clause is evaluated:

SELECT DISTINCT ID AS IDs
FROM DBTABLE
WHERE ((ID = 1423) AND (Tags LIKE '%Warm%'))
OR ((ID = 2743) AND (Tags LIKE '%Dry%'))
OR ((ID = 4832) AND (Tags LIKE '%Cool%'))

This returns a 3-record result set containing 1423, 2743 & 4832 from your example data.

Parentheses can also make your SQL queries more readable and maintainable.


How to get value of last updated row from a table? I want to insert the last updated row into a new table using a trigger?

How to fetch that row if any of the column (value) from that table has been updated or changed?

I am using this code:

CREATE TRIGGER database.tbl1_AFTER_UPDATE
    AFTER UPDATE ON tbl1
FOR EACH ROW
BEGIN
    INSERT INTO tbl2 (c1,c2,c3)
    SELECT c1,c2,c3
    from tbl1;
END

But this is inserting all the rows from tbl1 into tbl2. I want only the updated rows to be inserted into tbl2.

How can I do it?

From http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html:

  Within the trigger body, the OLD and NEW keywords enable you to access
  columns in the rows affected by a trigger. OLD and NEW are MySQL
  extensions to triggers; they are not case sensitive.

So if you want the new rows being inserted on another table, you should use the syntax:

INSERT INTO tbl2 (c1, c2, c3) VALUES (NEW.c1, NEW.c2, NEW.c3);


Is there an easier way to show why this query is always equivalent to bitor? This works, and I can't see right off the bat why:

CREATE OR REPLACE FUNCTION bitor ( x bigint, y bigint ) RETURNS bigint AS $body$
BEGIN
     RETURN x + y - bitand(x,y);
END;
$body$
LANGUAGE PLPGSQL
;

I have posted a couple things around SE for a more mathematical explanation, so I understand why it works logically now (at least in circuitry) but I was curious to post it here as it ultimately started as a query problem. Or maybe, the linked post is already the clearest way to show this?

In short, you want to prove that:

bitand(x,y) + bitor(x,y) = x + y

Split the x and y numbers into the respective bits. Now, lets prove first that the same stands for bits a and b (a and b can only have the values 0and 1):

bitand(a,b) + bitor(a,b) = a + b

But for bits these are trivial:

bitand(a,b) = least(a,b)
bitor(a,b) = greatest(a,b)

Therefore (assuming a <=b without any loss of generality):

bitand(a,b) + bitor(a,b) = least(a,b) + greatest(a,b) = a + b

So, back to x and y and splitting them:

x = a0 * 1  +  a1 * 2  +  a2 * 4  + ... +  an * 2^n
y = b0 * 1  +  b1 * 2  +  b2 * 4  + ... +  bn * 2^n

x + y = (a0+b0) * 1  +  (a1+b1) * 2  +  ... +  (an+bn) * 2^n

But we can do the same for bitand and bitor:

bitand(x,y) = c0 * 1  +  c1 * 2  +  c2 * 4  + ... +  cn * 2^n
bitor(x,y)  = d0 * 1  +  d1 * 2  +  d2 * 4  + ... +  dn * 2^n

bitand(x,y) + bitor(x,y) = (c0+d0) * 1  +  (c1+d1) * 2  +  ... +  (cn+dn) * 2^n

Considering also that:

bitand(ai,bi) = ci
bitor(ai,bi) = di

we apply our previous results to find:

x + y = (c0+d0) * 1  +  (c1+d1) * 2  +  ... +  (cn+dn) * 2^n

and we have a proof.


Multiple Transaction Log files and performance impact So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have.  I've seen several post saying you should only have 1 transaction log file for each database, nothing in any Microsoft whitepapers though. However in many cases I've seen that increasing the number of transaction log files for a given database will actually improve the write performance to the database.  I should note that all these databases are in Full recovery model and are using a large SAN frame for the I/O sub system.  If as many of the posts say that the transaction writes all happen serially to the one file until the end of the file is reached then the writes move on to the subsequent log file, why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk?  In the most recent case we saw IO jump from 700 Kb/s to more than 60 Mb/s by increasing the number of log files from 1 to 8.  Any input would be appreciated.

Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database.

Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files across multiple disks and have done so for multiple databases, and you are observing better I/O now because the log has switched to that file on the faster disk, or is more isolated from your other data/log files. In other words, I believe any observed I/O difference is due entirely to other factors and is merely a coincidence, not due to the fact that you added log files alone. SQL Server is explicitly designed to only use one log file at a time - so how could multiple log files possibly improve log write performance, unless the current log file is on faster / more isolated disk? I think you need to provide better empirical evidence (and in doing so you may discover for yourself the true cause of the improved performance).

Please read these posts in full - they were written by a pretty smart guy who worked on the SQL Server storage team for quite some time, so I don't think he's making any of this stuff up for fun:

Multiple log files and why they’re bad
Importance of proper transaction log size management

Also Kimberly Tripp touches on this in a worthwhile article:

8 Steps to better Transaction Log throughput

Note that none of the 8 steps involves adding a transaction log file. In fact she recommends against it.

There are other perils to having multiple log files, particularly if they are large (think RTO) - and there really is nothing to gain.


CTAS with clustered index on SQL Server Is it possible to create a table with a clustered key from a query in a single step or do I have to do this:

select 1 as id into foo;
create unique clustered index pk_foo on foo(id)

You have to add the index in a separate step.

From MSDN:

  Indexes, constraints, and triggers defined in the source table are not
  transferred to the new table, nor can they be specified in the
  SELECT...INTO statement. If these objects are required, you must
  create them after executing the SELECT...INTO statement.


Designing a SQL database for a Tire Purchase System Ok so I don't have code for this as I am unsure of how it would even go. Take this scenario I am working on, a purchase system for, let's say, tires.

I have one table named tStock and another one for customers named as cOrder.

tStock has these columns:

(PK) Tyre_ID
(VARCHAR) Manufacturer
(int) Quantity

while cOrder has:

(PK) Customer_ID
(varchar) Cust_name
(int) Purchase_quantity

What I hope to achieve is that when I have a form that takes a customer's details and orders of how many tires (s)he wants min of 1 tire max of 4 tires that it will then subtract this amount from the database and update it.

Would I be right in thinking that I would need to have customer_ID and possibly purchase_quantity in as a  foreign key constraint for it to update?

Also would it need to be joined in someway i.e outer, equijoin, natural?

Any suggestions is greatly appreciated

I would have three tables minimum for a purchasing system. One for customer, one for products, one for orders.

    --Customer
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [AdditionalInfoRequired] [varchar](50) NOT NULL,
 CONSTRAINT [CustomerId] PRIMARY KEY CLUSTERED
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Product
CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [ProductId] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Order
CREATE TABLE [dbo].[Order](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
--OrderDetails
CREATE TABLE [dbo].[OrderDetails](
    [OrderDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
    [OrderDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Product]
GO

Let me know if you have any question.


Find Number of Records Higher Than A Specific Number In Other Table I have two tables with following structure:
First Table:

ID    Name    Total_courses_allowed
1     Ahmad     4
2     Khan      3
3     Mahmood   2

Second Table:

ID   Course_attended   emp_id
1           f             1
2           t             1
3           f             1
4           t             1
5           f             2
6           t             2
7           t             2
8           f             3
9           f             3
10          t             1
11          f             1
12          t             2
13          f             2

For each record in first table there should be as many records as their total_courses allowed in second table. but for some of the records it is created more than that their allowed number of courses to attend like for, ID 1 of first table 6 rows are generated in second table whereas it should be only 4.

I need a SQL query to find all the records which have more entries in the second table than their total allowed courses to attend.

Thanks,

I'm sorry if it is not well formatted but I'm on mobile.

You can get it by counting attended courses and getting those records where total is lower.

select id, name, total_couses_allowed
from table1
inner join (select emp_id, count(*) as courses
            from table2
            where course_attended = 't'
            group by emp_id) t2
on table1id = t2.emp_id
where total_courses_allowed < courses


Why use RDS over EC2 for Postgres? I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up.

Pricing:

I can get an EC2 c3.large instance with two SSDs (16GB each), two vCPUs and 4GB of memory on demand for $0.132 per hour [1].

The closest (considering price) RDS machine would be db.m3.medium for $0.125 per hour (Single-AZ) [2]. This machine has the same amount of memory but only one vCPU [3]. Additionally I would have to pay for storage and io [2].

So the prices would be very similar for these two.

The advantages for EC2:

As mentioned already, one more vCPU.
I can put the Write-Ahead-Log on the second disc when using EC2 (great performance improvement when writing a lot to the db).
I can run pgbouncer on my EC2 instance (more performance because I can keep connections open).
I can edit the configuration file (will eventually improve performance)

The advantages for RDS:

Automatically does daily backups.

Can RDS cover the advantages that EC2 has (especially 2.)? Is there any other advantages?

Like most things, it's a cost vs effort/complexity trade-off.

RDS is easier, but less flexible, and costs more for the same level of performance.

(In particular, note that there's no way to replicate into or out of RDS)


Efficiently Filter Large Set With Disjunctions Let's say I have a single table

CREATE TABLE Ticket (
    TicketId int NOT NULL,
    InsertDateTime datetime NOT NULL,
    SiteId int NOT NULL,
    StatusId tinyint NOT NULL,
    AssignedId int NULL,
    ReportedById int NOT NULL,
    CategoryId int NULL
);

In this example TicketId is the Primary Key.

I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed:

The query will always perform a range filter on an
InsertDateTime
The query will always ORDER BY InsertDateTime DESC
The query will page results

The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example:

SELECT
    TicketId
FROM (
    SELECT
        TicketId,
        ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum
    FROM Ticket
    WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01'
      AND StatusId IN (1,2,3)
      AND (CategoryId IN (10,11) OR CategoryId IS NULL)
    ) _
WHERE RowNum BETWEEN 1 AND 100;

Now assume the table has 100,000,000 rows.

The best I can come up with is a covering index that includes each of the "optional" columns:

CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime DESC
) INCLUDE (
    SiteId, StatusId, AssignedId, ReportedById, CategoryId
);

This gives me a query plan as follows:

SELECT

Filter

Top

Sequence Project (Compute Scalar)

Segment

Index Seek

It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal.

Are there better strategies for implementing this kind of searching?

I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client.

If this particular work load is the majority of queries against the table you might consider:

ALTER TABLE Ticket ADD CONSTRAINT PK_Ticket PRIMARY KEY NONCLUSTERED (TicketId);

CREATE UNIQUE CLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime ASC
);

Considerations:

can you use datetime2 (SQL 2008+; flexible precision)
will InsertDateTime be unique within your precision
if the times are not constrained unique sql will add a hidden uniquifier column of type int. This is added to all nonclutred index so they can reference the correct clustered record

Advantages:

Adds new rows to the end of the table
prevent writing the optional filter columns twice (once in the clustered, and once on the index leaf for the include)
majority of you time will still be on a cluster index seek with more or less filers.
then add other nonclustered index for most popular column pairs


Azure SQL Database in different locations I'm deploying a Sitecore website using Azure and Azure SQL, and one of the requirements is to have some of the databases in different Azure Regions but sharing the same data among the other databases.

For example:
1. Web database will be in East US and West US
2. Sitecore website also in East US and West US
3. Website from East US will point to East database, and Website from West US will point to West database
4. Databases must be in sync (some lag is acceptable)

I had a look into Azure SQL Data Sync but looks like it is in preview mode, and I was wondering if Azure SQL has another feature to address. Despite the fact Azure SQL will be in place, I'd also wondering if on-premises SQL have anything similar?

Based on your scenario Data Sync is the best fit. It's the only Azure PaaS solution for Data Synchronizaion.

Transactional replication is supported for on-premise to Azure. If sync in this direction is enough for you, you can use replication. See Transactional Replication to Azure SQL DB now in public preview


Error ORA-00918 not raised as expected when using the FETCH command The Oracle 12c documentation about FETCH is telling the below

  If the select list contains columns with identical names and you
  specify the row_limiting_clause, then an ORA-00918 error occurs. This
  error occurs whether the identically named columns are in the same
  table or in different tables. You can work around this issue by
  specifying unique column aliases for the identically named columns.

But in Oracle Live SQL i am able to run the below Query succesfully.
(there we will have 2 columns returned in the result with same name "department_id")

select *
from hr.employees e
join hr.departments d
on (e.department_id = d.department_id)
fetch next 10 rows with ties
;

Could someone explain me if this is where we should expect the Oracle Error or it the documentation meant another scenario ?

Thanks

SD

Do not believe everything you read in Oracle documentation.

Try the below:

CREATE TABLE a (test NUMBER not null, test1 VARCHAR(6) not null, id VARCHAR(5) not null);
CREATE TABLE b ( id VARCHAR(5) not null, lang VARCHAR(5) not null, test1 VARCHAR(6) not null, test2 VARCHAR(5) not null );

insert into a values(1, 'AA',10);
insert into a values(2, 'BB',20);
insert into a values(3, 'CC',30);
commit;

insert into b values(10, 'eng','AA','test2');
insert into b values(20, 'tamil','BB','test2');
commit;

Then:

1) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ;
2) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
3) SELECT aa.test, aa.test1, aa.id, bb.test1 as t1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
4) SELECT * FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;

1), 3) and 4) runs successfully on 12.1.0.2, 2) throws ORA-00918.

On 12.2.0.1, all 4 runs successfully.

The one thing to keep in mind: never rely on implicit behaviour you can not control. Use explicit, unique aliases.


pt-table-checksum - Diffs cannot be detected because no slaves were found (1) I'm new to Percona tools. I am trying to use pt-table-checksum as follows:

pt-table-checksum h=localhost -u root -p xyz--replicate=percona.checksums --create-replicate-table --databases=mysql

But get the error.

Diffs cannot be detected because no slaves were found

Please read the --recursion-method documentation for information.

My.cnf

#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
default-character-set=utf8

[mysqld]
datadir=/spacedb
socket=/tmp/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
init-connect='SET NAMES utf8'
character-set-server = utf8
max_connections = 300

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqladmin]
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock
default-character-set=utf8

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = user

[mysqld1]
socket     = /tmp/mysql.sock
port       = 3306
pid-file   = /var/run/mysqld/mysqld.pid
datadir    = /spacedb
language   = /usr/share/mysql/english
server-id  = 1
log-bin    = mysql-bin
binlog_format = mixed

# Expire_logs + MaxBinlogSize
expire_logs_days = 7
max_binlog_size = 100M

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout=1

init-connect='SET NAMES utf8'
character-set-server = utf8
max_connections = 300

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/run/mysqld/mysqld2.pid
datadir    = /repdb
language   = /usr/share/mysql/english
server-id  = 2
relay-log  = mysqld2-relay-bin

innodb_lock_wait_timeout=1
init-connect='SET NAMES utf8'
character-set-server = utf8

Do I have to use DSN? And how does it work?

According to the Documentation on --recursion-method

  Preferred recursion method for discovering replicas. Possible methods
  are:

METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table

  The processlist method is the default, because SHOW SLAVE HOSTS is not
  reliable. However, the hosts method can work better if the server uses
  a non-standard port (not 3306). The tool usually does the right thing
  and finds all replicas, but you may give a preferred method and it
  will be used first.

  The hosts method requires replicas to be configured with report_host,
  report_port, etc.

  The dsn method is special: it specifies a table from which other DSN
  strings are read. The specified DSN must specify a D and t, or a
  database-qualified t. The DSN table should have the following
  structure:

CREATE TABLE `dsns`
(
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `parent_id` int(11) DEFAULT NULL,
   `dsn` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
);

  To make the tool monitor only the hosts 10.10.1.16 and 10.10.1.17 for
  replication lag and checksum differences, insert the values
  h=10.10.1.16 and h=10.10.1.17 into the table. Currently, the DSNs are
  ordered by id, but id and parent_id are otherwise ignored.

You could do this setup for DSN if you want, but you do not have to go that far yet. Why?

The default --recursion-method is the processlist. This simply means that you did not run START SLAVE; on the port 3307 instance. You cannot use --recursion-method processlist on a Master where all the Slaves are stopped. When you run START SLAVE; on port 3307, a DB Connection should appear in the processlist of the Master (3306 instance) from the Slave (3307 instance). In light of this, you should just run START SLAVE; on port 3307 and then run the pt-table-checksum.

Give it a Try !!!


Representing N:N relation as a functional dependency in a database design As a software developer, I already have some experience in designing a more or less normalized database schema, but I haven't received any formal training in it before. This semester, I took a university class on databases. We are being taught the formal way of designing a schema based on relational algebra. First, we collect the attributes we want to store:

bookstore<TITLE, AUTHOR, CATEGORY, YEAR, PUBLISHER, PUBLISHER_ADDRESS>

Then we find functional dependencies between the attributes:

fbook : { TITLE, AUTHOR } -> { CATEGORY, YEAR, PUBLISHER, PUBLISHER_ADDRESS }
fpublisher : { PUBLISHER } -> { PUBLISHER_ADDRESS }

After that we can normalize this schema. (For the sake of simplicity, I assumed that a book is defined by its title and author alone, no two editions or two copies of the same edition are stored.)

Now what happens when a book can have more than one category? How do I represent that relation with dependencies? The category can't be a secondary attribute any more, but if it's primary, how do I proceed?

What does this define?

{ TITLE, AUTHOR, CATEGORY } -> ???

We are told that the empty set cannot be on the right side of a dependency.

The question you raise has to do with the definition of first normal form (1NF). Whether the answer directly involves functional dependencies depends in part on the definitions you accept. Wikipedia has a fairly simple article about 1NF.

title                                author    year  category
--
An Introduction to Database Systems  CJ Date   2003  databases, modeling, storage, retrieval

If you look at the column "category" one way, it contains a single value. Depending on your dbms and your design, that value might be the string "databases, modeling, storage, retrieval", or it might be the array "{databases, modeling, storage, retrieval}".

If you look at the column "category" another way, it contains four values. Those values are the four strings "databases", "modeling", "storage", and "retrieval".

In database design, the solution is to use two tables. But I don't think you can decompose the "bad" table by projection (which CJ Date identifies as the decomposition operator), because projection doesn't split the content of a column into multiple rows. (Projection doesn't give you four rows from the single value "databases, modeling, storage, retrieval", which is what you need to do. "Join", the recomposition operator, doesn't yield a single value like "databases, modeling, storage, retrieval", either.)

The inability to decompose by projection suggests that the solution to this problem doesn't have to do with functional dependencies. The resulting table would have three attributes, {title, author, category}, the only key would also be {title, author, category}, and that table would be in 5NF.


Why are parallel writes in MySQL as slow as sequential writes? I have many MySQL write-only processes. They all go to the same MySQL master. I run them sequentially to get a baseline time. I basically chain together several mysql command-line clients, one-after-another. Later, I parallel fire up several mysql clients at the same time to get an experimental time. Why are the times the same?

More facts:

Each client hits a separate schema. This eliminates the possibility of table or row locking contention between clients.
I have tried this on Win32 XP with a couple of cores and 4GB RAM vs. RedHat 5.6 64-bit with 24 virtual cores and 32GB RAM. The baseline vs. experimental is the same on both.
On the RedHat I've tried all TCP clients vs. all SOCKET clients. No difference probably because the server isn't under load.

Yes, I think you're IO bound and you haven't parallelized the harddrives involved in the writes.  There are lots of things that go into insert performance. You can learn more here.


Mongodb. Convert field to bool I'm trying to change field type to bool among all documents in collection. Here is the code

db.product.find().forEach(function(doc) {
if (doc.checked === "Y") {
doc.checked = true;
} else if (doc.checked === "N") {
doc.checked = false;
}
db.product.save(doc);
});

Mongodb returns no error but doesn't convert fields.

I solved problem using 'db.product.update' instead of 'db.product.save'


Can I restore a SQLServer db from URL but not from azure? I have backups in S3.  If I create presigned URLs, can I restore directly from the URL to my on premise SQL Server instance?

No, the FROM URL syntax of the restore command is meant only to work with Azure.  You must find another way of restoring the backups from Amazon storage.

Unfortunately, I don't know much about Amazon's offerings, but did find.

You can mount the storage as a drive in windows.  Then restore from
that.
Does Amazon offer an SMB accessible storage solution much like
Azure Files?

But failing those, you will just need to copy locally and then restore.  But this is probably your best bet anyways, as you want to avoid involving delicate restore operations with pulling (supposedly large) files across the internet.  By doing this in two distinct steps, you can more easily identify where it failed.