Restore mysql database with different name How can we restore mysql database with different name from mysqldump file.
 I dont't want to open dump file and edit it. Any other better methods?

You can let mysqldump create the dump in such a way that it does not create or select  the database.

EXAMPLE : You are dumping the database db1 and loading it into database db2

This will put in the CREATE DATABASE and the USE commands in the dump

mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql

This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)

mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql

You can load it into another database (such as db2) in one of four(4) ways:

OPTION 1

$ mysqldump -u... -p... --routines --triggers db1 | mysql -u... -p... -A -Ddb2

OPTION 2

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2 < /root/db1.sql

OPTION 3

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2
mysql> source /root/db1.sql

OPTION 4

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A
mysql> use db2
mysql> source /root/db1.sql

Give it a Try !!!

If you using macOS and MySQLWorkbench. First export the database into a single .sql file. Then use the "Data import" util. It will show error because the new database name is not matching the exported one. You have to copy the whole excecuting command on the log and:

replace the --database flag
remove the --defaults-file flag

Example: sudo /Applications/MySQLWorkbench.app/Contents/MacOS/mysql --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=ion_development < "/Users/2359media/dumps/Dump20201110.sql"


How to keep an unique counter per row with PostgreSQL? I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related document.

I initially came up with something like:

current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);

But there is a race condition!

I'm trying to solve it with pg_advisory_lock, but the documentation is a bit scarce and I don't fully understand it, and I don't want to lock something by mistake.

Is the following acceptable, or am I doing it wrong, or is there a better solution?

SELECT pg_advisory_lock(123);
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(123);

Shouldn't I lock the document row (key1) for a given operation (key2) instead? So that would be the proper solution:

SELECT pg_advisory_lock(id, 1) FROM documents WHERE id = 123;
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(id, 1) FROM documents WHERE id = 123;

Maybe I'm not used to PostgreSQL and a SERIAL can be scoped, or maybe a sequence and nextval() would do the job better?

SEQUENCE is guaranteed to be unique, and your use-case looks applicable if your number of documents isn't too high (else you have a lot of sequences to manage). Use the RETURNING clause to get the value that was generated by the sequence.
For example, using 'A36' as a document_id:

Per document, you could create a sequence to track the increment.
Managing the sequences will need to be handled with some care. You could perhaps keep a separate table containing the document names and the sequence associated with that document_id to reference when inserting/updating the document_revisions table.

 CREATE SEQUENCE d_r_document_a36_seq;

 INSERT INTO document_revisions (document_id, rev)
 VALUES ('A36',nextval('d_r_document_a36_seq')) RETURNING rev;

FOR rowdata1 IN
    select   employee_id, accrual_group_code, accrual_type, start_date,accru_date from z_123  where (doc_id =0 or doc_id is null ) order by accru_date
            LOOP        counter = counter+1;

            update  z_123 set company_id = 2 , doc_id= counter where employee_id = rowdata1.employee_id
                            and  accrual_group_code = rowdata1.accrual_group_code and  accrual_type = rowdata1.accrual_type and accru_date = rowdata1.accru_date;
            END LOOP;


Consistency in ACID and CAP theorem, are they the same? From my understanding, the consistency in ACID is ensuring the data integrity.

However, the consistency in CAP means the data can appear quickly in distributed system.

Does that mean: they are not the same concept?

They are not really the same, because of the scope of the data
ACID

Atomicity
Consistency : All Applied Data Changes Provide Consistent View of Data For All DB Connections
Isolation
Durability

CAP

Consistency (All Nodes Have Same Data via Eventual Consistency)
Availability
Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system

SYNOPSIS

ACID addresses an individual node's data consistency
CAP addresses cluster-wide data consistency

Regarding Consistency in the CAP theorem Julian Brown writes in his article (http://www.julianbrowne.com/article/brewers-cap-theorem):

  A service that is consistent operates fully or not at all. Gilbert and
  Lynch use the word “atomic” instead of consistent in their proof,
  which makes more sense technically because, strictly speaking,
  consistent is the C in ACID as applied to the ideal properties of
  database transactions and means that data will never be persisted that
  breaks certain pre-set constraints. But if you consider it a preset
  constraint of distributed systems that multiple values for the same
  piece of data are not allowed then I think the leak in the abstraction
  is plugged (plus, if Brewer had used the word atomic, it would be
  called the AAP theorem and we’d all be in hospital every time we tried
  to pronounce it).

Thus the C in CAP would correspond to the A in ACID.

With Gilbert and Lynch he refers to the article "Seth Gilbert and Nancy Lynch: Brewer's conjecture and the feasibility of consistent, available, partition-tolerant web services"


Allow users to only view the databases they have access to I wonder if there's any way to allow a user to only view the specific databases they have access to. For example.
Let's say we have a alwaysOn with one local only database and 10 databases that is added in the AO. The user need read rights to one of the 10 databases added in AO. But when giving user rights to that database and he connects to the AO he can view every database but only access the one he got rights to of course. And if the AO is active where the local database is located he will also see that one.

We have tried a few things but nothing successful and from what we've been reading it doesn't seem to be a way unless the user is granted dbowner rights.

Anyone got any ideas?

By default a login can see all the databases on a server.

You can DENY VIEW ANY DATABASE TO [your login or serverrole];, after which a login can only see the databases where he has access.

To verify what databases a login can see, try this when you're connected as an administrative user:

--- This changes the security context to the login "dummy"
EXECUTE AS LOGIN='dummy';

--- Lists the databases visible to that user:
SELECT * FROM sys.databases;

--- Important: changes the security context back again:
REVERT;

The VIEW ANY DATABASE permission is assigned to the server-level principal (the login, i.e. not the user, which is the database-level principal), and therefore, you'll need to apply it to each availability group replica. I would recommend that you apply (deny) this permission on a server role rather than individual users to keep it manageable.

More detailed reading on the subject:

https://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/

Use DCL command to operate on specific database.

Grant select on db_name.table_name to user identified by password

Then commit();

So user will only read the database object.


SQL Server 2016 high idle CPU and queries extremly slow I have a ~10 day old installation of WinServer2012R2 and SQL Server Express 2016 for testing. I am the only user on this machine. A database with a .bak from SQL Server 2005 of ~250MB is restored without any issues. After a reboot of the machine the process "SQL Server NT - 64 Bit" uses 0% CPU.

After a couple of minutes or hours and some simple queries (no updates/inserts!) from SSMS CPU usage of "SQL Server NT - 64 Bit" suddenly jumps to ~15% and stays there, even when idle. From that point on queries which usually take less than a second suddenly take 2 minutes. During an actual query the CPU usage does NOT increase. The server becomes virtually unusable in this state.

Only connecting SQL Server Profiler then takes >30sec. Beside my own queries I see only very few queries from SQLServerCEIP / SQLTELEMETRY, ~3 per minute.

Restarting SQL-Server does not solve it. CPU usage jumps right back to ~15%. Even after hours SQL-Server does not recover. Only rebooting the entire machine solves the issue.

As this is an "out of the box" installation, there is only a small database, virtually no queries, only me as a user and probably no locks, the many articles on regular SQL-Server performance issues talk about lot of things that don't really apply here. It seems as SQL-Server exclusively wants to concentrate on some internal task.

This is a virtual machine with 2GB RAM and dual Xeon at 2GHz. I also have VS2016 on it and it's really fast. No antivirus, not even Windows Defender. Already late here. I will try the sp_whoisactive tomorrow. I really wonder WHAT SQL-Server IS doing there... On the previous machine with 1 GB the same DB ran under SQLServer2005 for 10 years without issues...

I am not an SQL-Profiler expert. Where should I start looking?

Since your CPU utilization is low (and you have a great CPU set), I would check for memory pressure first. With SQL Express 2016 you are limited to 1410 MB of memory, but your VM only has 2GB. Give your VM 1GB more of memory and see if that helps. You can also check your SQL Server log if it is paging memory to file.

If that does not work, try setting the database setting LEGACY_CARDINALITY_ESTIMATION to ON. Reference: Using DB Compatibility Level 130 with Old CE in SQL Server 2016. We ran into this with some of our upgrades; results will vary with each database.

The following patch describes a similar problem.  It may be worth trying:

https://support.microsoft.com/en-us/help/3195888/fix-high-cpu-usage-causes-performance-issues-in-sql-server-2016


Postgres Windows post-install "The database cluster initialization failed" I've seen many solutions and have been working on this issue for hours now.

After installation the post-installation script fails with the message above.

This is a development installation of EnterpriseDB On Windows 10 Pro (German Edition with English language pack).

No user created
No service created
No initialization happened

What I read about and tried without success (I hope I don't forget anything, as far as I remember these are all (at least most) solutions I tried posts from up to 10 years ago):

Checked whole file system privileges up to root of C:\, I have no other drive - this is a dev laptop.
Installed different locales because I read there could be issues. I tried "default", "C", "US English"
COMSPEC and other ENVs are correct, no trailing semicolon or other disturbances.
postgres password is simple

The installation works perfectly on a different laptop.

What I observed:

This issue is caused by a failing objShell.Run call in the VBS script.

The call is simply not executed. No error message. It "looks like" objShell.Run is called, seems to fail somehow and the DoCmd function where it is called returns. I don't even get a return value from objShell.Run, it just jumps out of DoCmd, thats it.

The written batch file exists. The path variables in the VBS are correct. The log file created inside the batch file is not created therefore I think it is not called at all. But it is created when I prevent the batch file from being deleted and calling it manually from command line. I prefixed the objShell.Run with "cmd /c " which caused it to run "more" but kept on getting other errors.

Unfortunately after several reinstalls, tries, reboots and so on I don't have the logs anymore. But if it is required for problem solving I'd be happy to provide it tomorrow.

I need to say I am not familiar with VBS but that is how far I was able to debug it. I also tried /X /D but I think i need deprecated tools for debugging, do I?

I spent hours with this problem and I'll leave it for today but I'm in desperate need for help.

Thanks.

(edit 1) Added which binary distribution

There are two Windows binary packages available for download from the PostgreSQL site: one by BigSQL and one by EDB.

You don't tell us which one you tried, but my advice is to give up on the one that you are using and try the other one.

The steps described in the first answer of the following post helped me with this issue
https://stackoverflow.com/questions/23533980/postgresql-running-post-install-step-the-database-cluster-initialisation-failed


Length of varchar column - is there an advantage to using 255? I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255? Is there a performance or storage optimisation advantage to certain specific varchar lengths? Or is this just old wisdom which is no longer applicable to current versions of MariaDB and MySQL?

I rant against 255 occasionally.  Sure, there used to be some reasons for '255', but many are no longer valid, and even counter-productive.
In MySQL, there are reasons to stop at 191, 255, 767, 3071, 64K, and probably other values.  Some depend on Engine, some on CHARACTER SET, etc.
A VARCHAR is stored as a 1- or 2-byte length plus enough bytes for the current text in whatever charset you have specified.  However, the choice of 1 or 2 is not driven only by the individual column; it is driven by the total row size.  That is, this is not a valid excuse for using 255.
Use a length that is

Big enough to conservatively never be exceeded, yet
As small as seems reasonable.

While I am ranting...  CHAR (fixed length) is rarely advised.  And almost always it should be CHARACTER SET ascii -- country_code, postal_code, Y/N, M/F, MD5, UUID, base64, etc.  (MD5 and UUID should be taken a step further, but that is another rant.)
Potential negative impacts of blindly using '255':

If you have a lot of columns, you could hit a max row size limit and CREATE TABLE will fail.
You could overflow a limit on index size.
Complex SELECTs may need a temp table, and may use MEMORY for it.  In this case, VARCHAR(255) becomes CHAR(255) for the temp table.  And, if using utf8, that is 755 bytes for every row!  (8.0 fixes this design flaw?)

Related:  Don't blindly use BIGINT for all numbers.  It takes 8 bytes.  Even INT is overkill, at 4 bytes.  See MEDIUMINT, SMALLINT, and TINYINT.  Be aware that the (2) on INT(2) means nothing; it still takes 4 bytes.

We've taken a completely different rule now. We use one of two things:

`NVARCHAR(max)`

if it won't be indexed.

`NVARCHAR(400)`

if it will be indexed.

Generally speaking we believe that limits are obsolete and we really shouldn't care anymore. Disk space is cheap.  The 400 ensures the index can always be created on the most restrictive target platform. It is my understanding that the 255 index limit on MySQL is gone else I would have written 255. The thing about VARCHAR is you will not be penalized for allowing it to be bigger than it needs to be so there isn't much downside to being too big, so we just set it big and don't think about it anymore.


on deleting a row, the update logic is run also I am using a after insert, update delete trigger.

When I insert a record in my application called as clarity, it inserts a row in the test table with the value as 'Insert'

When I update the record in my application, it insert a row in my test table with the value as 'Update'

When I delete the record in my application, it adds two rows in my test table with the value as 'Update' and 'Delete' in that order.

Why would deleting a row call the update logic of the trigger.

My code is as below

USE [claritydb]
GO
/****** Object:  Trigger [niku].[TRIG_STATUSRPT]    Script Date: 11/11/2019 11:04:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @STATUSID INT,
            @PROJECTID INT,
            @SCHEDULE_STATUS INT,
            @SCOPE_STATUS INT,
            @EFT_STATUS INT,
            @OVERALL_STATUS INT,
            @ACTION_TYPE VARCHAR(50),
            @MAXSTATUSIDVAR INT,
            @DelCount int,
            @InsCount int,

   SELECT @InsCount = Count(*) FROM INSERTED
   SELECT @DelCount = Count(*) FROM DELETED

---- Get data from inserted/ updated
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM inserted
 ---- Get data from deleted
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM deleted
SELECT TOP 1 @MAXSTATUSIDVAR= ID FROM ODF_CA_COP_PRJ_STATUSRPT op where op.ODF_PARENT_ID = @PROJECTID  ORDER BY ID DESC

   If @InsCount > 0 and @DelCount = 0
   Begin
      -- At least 1 row inserted. Your Insert Trigger logic here
        INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Insert',GETDATE(),@MAXSTATUSIDVAR)

   End
   Else If @DelCount > 0 and @InsCount = 0
   Begin
      -- at least 1 row deleted. Your Delete Trigger logic here
       INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Delete',GETDATE(),@MAXSTATUSIDVAR)
   End
   Else If @DelCount > 0 and @InsCount > 0
   Begin
      -- old row deleted, new row inserted; both indicates an update.
      -- your update logic here.
              INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Update',GETDATE(),@MAXSTATUSIDVAR)
   End
END

Instead of diagnosing your update/delete logic issue, I think it's better to just re-write your trigger.  The biggest problem with your trigger is that it's not set based, so that any attempt to modify multiple records at a time will fail, or more accurately give you odd results.  I suspect that this is at least part of the problem you are experiencing with it.

Please take a look at the one below.  It should work for you, but I apologize if I got some column names incorrect.  The CTE_Details at the top fuses the INSERTED and DELETED tables together and will properly return the Action taken based on whether it's an insert, update or delete.  It then inserts that into your reporting table.

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID
            , I.ODF_PARENT_ID
            , I.COP_SCHEDULE_STATUS
            , I.COP_SCOPE_STATUS
            , I.COP_COST_EFT_STATUS
            , ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELTED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID
            , D.ODF_PARENT_ID
            , D.COP_SCHEDULE_STATUS
            , D.COP_SCOPE_STATUS
            , D.COP_COST_EFT_STATUS
            , ActionTaken = 'Delete'
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
    SELECT T.ID AS STATUSID
        , T.ODF_PARENT_ID AS PROJECTID
        , T.COP_SCHEDULE_STATUS AS SCHEDULE_STATUS
        , T.COP_SCOPE_STATUS AS SCOPE_STATUS
        , T.COP_COST_EFT_STATUS AS EFT_STATUS
        , T.ActionTaken
        , GETDATE() AS CREATED_DATE
        , OP.ID AS MAXSTATUSIDVAR
    FROM CTE_Details AS T
        OUTER APPLY (   SELECT TOP (1) M.ID
                        FROM ODF_CA_COP_PRJ_STATUSRPT AS M
                        WHERE M.ODF_PARENT_ID = T.ODF_PARENT-ID
                        ) AS OP

END

** EDIT ** - I fixed a bug in my example code for the Insert action path, it wouldn't simulate the error you are seeing.

I have an example here below that shows it working correctly.  I think you have something else going on.  If you run the example code below you should see four entries.  2 inserts, 1 update and 1 delete.

DROP TABLE IF EXISTS dbo.TestMe;
DROP TABLE IF EXISTS dbo.TestMeReport;

CREATE TABLE dbo.TestMe
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestValue VARCHAR(100) NULL
    )

CREATE TABLE dbo.TestMeReport
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestMeID INT NOT NULL
    , TestValue VARCHAR(100) NULL
    , ActionTaken VARCHAR(20) NULL
    )

GO

CREATE TRIGGER trgTestMe ON dbo.TestMe
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID, I.TestValue, ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELETED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID, D.TestValue, 'Delete' AS ActionTaken
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO dbo.TestMeReport
    (TestMeID, TestValue, ActionTaken)
    SELECT T.ID, T.TestValue, T.ActionTaken
    FROM CTE_Details AS T

END

GO

TRUNCATE TABLE dbo.TestMe
TRUNCATE TABLE dbo.TestMeReport

INSERT INTO dbo.TestMe (TestValue)
VALUES ('Test1')
    , ('Test2')

UPDATE dbo.TestMe
SET TestValue = 'Test3' WHERE TestValue = 'Test1'

DELETE FROM dbo.TestMe WHERE TestValue = 'Test2'

SELECT * FROM dbo.TestMeReport

Hm looking at your trigger code it looks like your trigger fires 2 times - because in a single run it can only deliver 1 result (IF .. then "INSERT" ELSE IF .. then "DELETE" ELSE IF.. then "UPDATE" )

Leaves the small problem to find out, why your trigger is fired 2 times with update first


Two queries usually run fast but get slow when joined There are those 2 tables

CREATE TABLE IF NOT EXISTS `media` (
  `mid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `order_type` int(11) NOT NULL,
  `media_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `performed_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `flag` int(11) NOT NULL,
  PRIMARY KEY (`mid`),
  UNIQUE KEY `mid` (`mid`),
  UNIQUE KEY `media_id` (`user`,`media_id`),
  KEY `user` (`user`,`timestamp`),
  KEY `performed_by` (`performed_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306 ;

CREATE TABLE IF NOT EXISTS `media_resources_used` (
  `muid` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` varchar(50) NOT NULL,
  `resources_used` text NOT NULL,
  PRIMARY KEY (`muid`),
  UNIQUE KEY `media_id` (`media_id`),
  KEY `media_id_2` (`media_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=1507815;

They're connected by media_id. One row from media_resources_used can have n rows at media.

They both have over 1 million entries.

media_resources_used is used to determine what resources have been used on a media with a media_id and this works like a queue.

First I reserve a media row

UPDATE media SET performed_by = 'worker_id' WHERE performed_by = NULL LIMIT 1

Then I want to fetch the media row with the corresponding media_resources_used info

SELECT * FROM media AS me INNER JOIN media_resources_used AS au ON me.media_id = au.media_id WHERE me.performed_by = 'worker_id'

But this takes around 20 seconds to complete.

EXPLAIN shows

+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys |     key      | key_len |  ref  |  rows   |                      Extra                      |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
|  1 | SIMPLE      | me    | ref  | performed_by  | performed_by | 63      | const |       1 | Using index condition                           |
|  1 | SIMPLE      | au    | ALL  | NULL          | NULL         | NULL    | NULL  | 1439398 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+

And it seems the index at media_id isn't even used.

When I run

SELECT * FROM media WHERE performed_by = 'worker_id'
SELECT * FROM media_resources_used WHERE media_id = '$media_id_from_above'

separated they both execute under 0.001 seconds.

What could be the issue there?

Change the line

UNIQUE KEY media_id (user,media_id)

to

UNIQUE KEY media_id (media_id)

then execute the query it will perform better


Warning before issuing delete/update command in mysql/oracle , then executes Is there any utility / function either in mysql / oracle that prevent I issue some delete/update command wrongly, a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to run this command" , if yes then delete/update command will execute?

I have googled this, but not a specific solution is provided for this.
I tried SOURCE also, but it is not exactly what I want.

Do any one have any idea over this?

If I'm about to fire update command on > 100 000 records, a prompt should come asking "do you really want to run?", like in Unix if we use rm filename, it asks if you are you sure you want to delete?

if i issue some delete/update command wrongly

There is no such thing as a "wrong" delete or update command unless it is "wrong" in the sense of being syntactically invalid, referencing nonexistent objects, or attempting something for which the user does not have permission... and those aren't going to execute anyway.

So you must be talking about something like this:

DELETE FROM t1; -- not wrong, deletes all of the rows by excluding none
UPDATE users SET email = 'test@localhost'; -- not wrong, updates all rows with same value

There is sure-fire no way for the system to correctly guess "hey, that seems wrong" and ask you to rethink your decision. The DBMS's job is to do what you ask in the most optimal way, not second-guess whether you know what you're doing.

MySQL does have a mode that tries to protect you, by stopping you from executing updates or deletes that aren't using an index to find rows, with the --i-am-a-dummy flag that enables sql_safe_updates on a per-connection basis -- or you can enable that setting at a global level on the server.

$ mysql --i-am-a-dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4642931
Server version: 5.5.30 MySQL Community Server (GPL)

mysql> use sakila;
Database changed

mysql> delete from film;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
                    without a WHERE that uses a KEY column

I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold.

You could, of course, disable autocommit, requiring you to type COMMIT; to actually commit your DML changes to the database, but that's only true and fully helpful if you're exclusively using a transactional storage engine like InnoDB, and not MyISAM, so I don't know how much that solves.

Those blessed with extra lazyiness will figure out that they can type ; COMMIT; at the end of every statement (true in the mysql cli, not universally).

The person who is sufficiently conscientious to think before committing is probably sufficiently conscientious to not type queries that don't make sense.

You can use

The following is the syntax to create a trigger in MySQL:

CREATE TRIGGER
  { BEFORE | AFTER }
  { INSERT | UPDATE | DELETE }
  ON <table name>
  FOR EACH ROW
  <triggered action>


Update multiple rows with different date for each row I want to update all records in my database table with a fixed value for pwDays and an increasing value in pWDate.

My current statement:

update [POSUSER] set passDays = '60', passChangeDate = '2017-01-01 00:00:00'

But I do not want all users to have to reset their passwords on the same day. So I would like to increment the passChangeDate by 1 day for every 10 to 20 users, is there a "simplistic" way to do this in a single SQL statement?
(All users must have a new passChangedDate, but no more than 20 users must have the same date.)

The table has a primary key field that is a integer value. (USERID)

Note I want to only increase the date after every +/-10% users.

Since you have a primary key userid, you can do it with a query like :

update p
set passDays = '60',
    passChangeDate = dateadd(dd, offset, '2017-01-01 00:00:00')
from [POSUSER] p
join (
    select  userid,
            row_number() over(order by userid) / 10 as offset
    from [POSUSER]
    ) t on p.userid = t.userid

This query will work the same with any type of primary key (numeric or not) or any set of unique columns. But if there is no way to build a unique identifier of a row, the query won't work and you'll have to make your update another way.

Ok I played around a bit and this works very well (adapted from Auto Increment in an UPDATE statement by Phil Haack), except for 1 small issue:

DECLARE @passChangeDate smalldatetime
SET @passChangeDate = '2017-01-09 00:00:00'

update [POSUSER]
set @passChangeDate = passChangeDate = DATEADD(dd,1,@passChangeDate),
passDays = '60';

The above increments every users date with 1 day, but I would prefer only every 10 - 20 users. But maybe the simple answer is to use hours?

DECLARE @passChangeDate smalldatetime
SET @passChangeDate = '2017-01-09 00:00:00'
update [POSUSER]
set @passChangeDate = passChangeDate = DATEADD(hh,1,@passChangeDate),
passDays = '60';


MySql - How can I improve this query? I have the following table: http://pastebin.com/XH6FvhZ8

and the following query:

SELECT *
FROM (SELECT *
      FROM `job_logs`
      ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?)
GROUP BY chain, subchain, job_type

here is the explain of this query: http://pastebin.com/AjXgzRBA

this query usually takes over 4K ms to execute, any ideas how I can speed the query execution? I have no problem adding indexes to table if needed

thanks!

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;

Try this:

SELECT
  *
FROM (
  SELECT *
  FROM `job_logs`
  WHERE user_id = ? AND workflow_state = ?
  ORDER BY id DESC
  ) tmp
GROUP BY chain, subchain, job_type


Alternatives for a LEFT OUTER JOIN query Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.

SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

But these are not pretty solutions.

i had two queries without outer join, which worked fast and correct on Microsoft SQL Server but both did not work out on H2 Database
one of em because MSSQL uses + to concatenate strings and H2 uses CONCAT (which is crap because afaik this does not match the SQL specifications)

my final solution is to create one single query in my code and find the correct stuff with code.

One of the queries i tried was this one (works on Microsoft SQL Server but not on H2 due different concatenate syntax)

SELECT * FROM tSysNLS
WHERE nlsGuid = CASE
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?2) then ?4+'.'+?5+'.'+?2
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?1) then ?4+'.'+?5+'.'+?1
ELSE ?4+'.'+?5+'.'+?3
END

where ?# are parameters
?1 = 'de_AT'
?2 = 'de'
?3 = 'en'
?4 = 'Contact'
?5 = 'firstName'


How to fix "Access denied for user 'root'@'localhost'" error for MySQL using Mac OS X? After reading a few of the similar problems to mine and none of them working, I thought I'd attempt to be more specific to see if my root problem needed to be fixed differently than the others.

I've been using MySQL for about a year now and it's always worked fine -- but I got a new Macbook Pro (10.9.3) yesterday, and everything migrated and runs great except for MySQL. It's running:

My root user is enabled and I checked to see that the password is the same as my user login (and the same as it's always been) -- but every time I try to run it, whether it be through Terminal or through Sequel Pro, I always get the same:

Access denied for user 'root'@'localhost' (using password: YES)

I also traditionally use Sequel Pro -- which migrated over from my old computer without any problems, and retained the connection details its always had:

But I'm getting the same error message through Sequel Pro as I am through Terminal:

Does anybody have any ideas of things I can try? After attempting some of the other similar questions on here, I've unfortunately come up with nothing.

Thanks!

Run this /usr/bin/mysql_upgrade -u root -p
This should reset the rights and grant access with u logged in as 'root'@'localhost'
Thereafter run this /usr/bin/mysql -u root -p... et viola!


Select IDS where all values can't be found in another table Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve

I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.

DB looks like:

Table 1

BOOK ID| User ID
1      |   1
1      |   2
2      |   17
2      |   16
3      |   17
3      |   16
4      |   3
4      |   16

Table 2

USER ID | Name
   1    | Jeff
   2    | Bill
   3    | Fred

The desired outcome would be to bring up Book IDs 2 and 3 as both IDs only have users that cannot be found in Table 2.

I think the sentence is quite clear, using a left join to get null USER_ID's and returning those where count = 0;

SELECT
    BOOK_ID, COUNT(USERS.USER_ID) AS USERS
FROM
    BOOKS
LEFT JOIN
    USERS
    ON USERS.USER_ID = BOOKS.USER_ID
GROUP BY
    BOOK_ID
HAVING COUNT(USERS.USER_ID) = 0;

BOOK_ID | USERS
------: | ----:
      2 |     0
      3 |     0

db<>fiddle here

select a.bookid
from table1 a left join table2 b on a.userid = b.userid
group by a.bookid
having count(b.userid) = 0


pg_dump permission denied for sequence I have a strange issue with dumping post-data section.

Dump command is:

pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump

Here is an error message:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation verification_code_user_mapping_id_seq
pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq

But when I'm trying to execute this query in psql it works well:

db_name=>  SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq;
             sequence_name             | start_value | increment_by | max_value | min_value | cache_value | is_cycled
---------------------------------------+-------------+--------------+-----------+-----------+-------------+-----------
 verification_code_user_mapping_id_seq |           1 |            1 |           |           |           1 | f
(1 row)

Here are the permissions granted to user root

db_name=> \dg root
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 root      | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

I granted default privileges, select and all to all tables and sequences in schema public but with no luck.

Could you assist please?
Thanks in advnce!

UPDATE

db_name=> \dp verification_code_user_mapping_id_seq
                                              Access privileges
 Schema |                 Name                  |   Type   | Access privileges | Column privileges | Policies
--------+---------------------------------------+----------+-------------------+-------------------+----------
 public | verification_code_user_mapping_id_seq | sequence | root=rwU/root     |                   |
(1 row)

I'm using an Amazon RDS.

Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped.

pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name

My guess that this is simply a permission issue towards the object that is attempted to be dumped. Excluding the sequence would work fine for pg_dump to continue.


Would a table benefit if it was a heap I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always added at the end. The average row size is 1570 bytes.

There are a lot of page splits because new rows are added frequently. No rows get updates/deleted and there is a non-clustered index on the table so rows can be selected.
Due to the page splits, the clustered index is always fragmented > 65%.

I wonder my table would benefit of removing the clustered index and make it a heap table?

This is how my table + non-clustered index looks like:

CREATE TABLE [dbo].[LogEntry](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Application] [varchar](20) NOT NULL,
[EntityFullName] [varchar](80) NOT NULL,
[Action] [int] NOT NULL,
[UserName] [varchar](25) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[EntityId] [varchar](50) NOT NULL,
[WhatChanged] [nvarchar](max) NULL,
CONSTRAINT [PK_LogEntry] PRIMARY KEY CLUSTERED(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] )
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ID_Application_Entity_FullName_TimeStamp] ON [dbo].[LogEntry]
(
    [Application] ASC,
    [EntityFullName] ASC,
    [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Update:
Someone had enabled auto-shrink behind my back => this will be the cause of the fragmentation

This started as a comment/questions but it got to long so I moved it here:

I'm really thrown by this question.  1.5mil rows isn't really all that big.  And the point behind an identity is that it's ever increasing.  If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation you're seeing.

Couple of questions:

Are you doing IDENTIY_INSERTS?  Basically specifying what the identity value should be?  Or have you re-set the identity at some point so that you are inserting into the middle of the range?

Typically if you are doing inserts it looks like this:

5 6 7 8 < Next insert goes here >

But if you have something like this (assume your next identity value is 4)

 1 2 3 < Next insert goes here > 100 101

Then you could be seeing quite a few page splits.  But in the normal course of things you shouldn't be.

Is there any chance you are shrinking your database?  Auto_shrink or a maint plan/job that does shrinks?  If so it's the shrink that's causing your fragmentation not the clustered index.

In general there is nothing wrong with a HEAP and they can be faster for INSERTs.  My biggest concern with them tends to be if you are doing large numbers of deletes or updates (which you say you aren't).  In those cases you can get a space leak and end up with a table that is multiple GBs in size but has 0 rows.

Actual answer

Given you have a log file, and are only ever inserting, you could try dropping the PK and see how performance goes (in a test environment first of course). Once you've run some tests using your workload and seeing how things go then make your change in production and monitor there for a while. You might even consider dropping the identity column entirely.

Do check that SHRINK thing though.  That's a killer.

The FILLFACTOR on the table is 100, thus there are no free pages in the cluster index. If you're doing lots of inserts try setting FILLFACTOR to something like 80.  And read the SQL Server books online regarding FILLFACTOR.  ;-)


Solving supertype-subtype relationship without sacrificing data consistency in a relational database Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.

Here is an example using common requirements:

An online store sells different categories of product.
The system must be able to retrieve the list of all product categories, say food and furniture.
A customer may order any product and retrieve his order history.
System must store specific properties depending on the product category ; say the expiration_date and calories for any food product and manufacture_date for any furniture product.

If it wasn't for requirement 4, the model could be quite straightforward:

Problem is trying to solve requirement 4. I thought of something like this:

In this approach, the relationships product-furniture and product-food are supertype-subtype (or superclass-subclass) associations; the primary key of the subtype is also a foreign key to the supertype primary key.

However, this approach can not guarantee the category referenced via a foreign key to the product will be consistent with its actual subtype. For instance, nothing stops me from setting food category to a product tuple having a subtype row in the Furniture table.

I read various articles about inheritance in modelling relational databases, especially this one and this one which were very helpful but didn't solve my problem for the reason mentioned above. But whatever model I come with, I'm never satisfied with the data consistency.

How can I solve requirement 4 without sacrificing data consistency ? Am I going all wrong here ? If so, what would be the best way to solve this problem based on these requirements ?

One common way is to add a classifier that is "inherited" like:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... NOT NULL
,     UNIQUE (product_id, products_type)
,     CHECK (product_type IN ('food', 'furniture'))
);

product_type would typically be a code of some kind. Might be a foreign key to a "lookup" table instead of a check constraint. For the sub-types:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
     REFERENCES products (product_id, product_type)
,    CHECK (product_type = 'food')
);

and a similar one for furniture. The constraints guarantee that product_type is consistent between super- an sub- tables.
There are products (I've heard) that allow sub-selects in CHECK constraints, but the majority do not. For such product something like:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
,    FOREIGN KEY (product_id)
     REFERENCES products (product_id)
,    CHECK (
         (SELECT product_type
          FROM products p
          WHERE p.product_id = product_id) = 'food'
     )
);

could be used.
An alternative to the latter is to use before triggers for insert/update, and signal an exception if the wrong product_type is used. Personally I don't fancy using procedural code for integrity constraints, but I guess it is a matter of taste.

I don't think a database should be responsible for stopping someone from misclassifying the sub-type of a record. That seems much more like a business logic layer problem. So, I don't see too big of a problem with your second scenario. I've seen it work pretty well in even very large enterprise solutions.

At large table size (like 2 million or more), the JOIN query that will be necessary to combine sub-class and super-class is going to have trouble scaling with ad-hoc queries if you need to join more than a few thousand results between the two tables. For that reason a Table-per-hierarchy model might be better - the object inheritance is flattened into a single physical table.

I do wonder about what would be the best way to handle the case of re-classifying a product in your example. Any sub-type record that was previously created would need to be deleted since only one sub-type should exist for each super-type record - assuming only a single layer of inheritance. Should that be a database trigger or something handled in the Business Logic Layer?


How to Find SYS Privilege Users I want to know which users have SYS and SYSDBA privilege before export/import operation.

I searched a lot but I could not find.

To your question:

I want to know which users have SYS and SYSDBA privilege before export/import operation.

SYSDBA is a role, not a user or schema. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
As per the Burleson consulting blog, the v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER roles.
SQL> desc v$pwfile_users;

Name Null?  Type
----------------------------------------- -------- ----------------------------
USERNAME    VARCHAR2(30)
SYSDBA      VARCHAR2(5)
SYSOPER     VARCHAR2(5)
SYSASM      VARCHAR2(5)

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE TRUE FALSE
TOM                            TRUE FALSE FALSE

In 11g there is a new column in v$pwfile_users for the SYSASM privilege:
SYS AS SYSDBA @ orcl11 SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

For further details see this Tom Kyte article.%3A%3AP11_QUESTION_ID:2659418700346202574">Here

Maybe you want privileges on objects owned by SYS from DBA_TAB_PRIVS.
select table_name from dba_tab_privs where owner='SYS' and grantee='PUBLIC';

This would give you all the objects owned by SYS and accessible by PUBLIC.


Why is the First Transaction Log backup of the day huge I noticed that the first transaction log backup of the day, at 0700 is like 3GB. We run a full backup each night at 22:00. We are a 9-5 shop so there is no real activity overnight. We take TLog backups hourly during the day and they average around 30 MB. How can I find out why this first log backup is so big?
Thanks!

That depends on what is happening in the night.

If you have additional maintenance jobs (index rebuilds, index reorganizations, statistics updates) then you possibly have a lot of changes occurring.
If you are also importing large amounts of data, then that is another possible source of changes.
Deleting data is also a modification and will be logged.

A database that is truly dormant will normally not have huge transaction log backups.

You may also want to consider either taking a second full backup and a transaction log backup after the nightly maintenance, and use that backup as your base for the day's log backups solely to avoid the massive post-maintenance log file, or to just postpone the full backup and subsequent transaction log backups till after the maintenance.

It sounds like the system is configured so the first transaction log is a full log (back how far, system setting?) and the remainder are incremental.


Why don't databases create their own indexes automatically? I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly requested data.

Update

This is now implemented in SQL Server Azure. It generates recommendations

and index management can be configured to be automatic.

  Enable automatic index management

  You can set the SQL Database Advisor to implement recommendations
  automatically. As recommendations become available they will
  automatically be applied. As with all index operations managed by the
  service if the performance impact is negative the recommendation will
  be reverted.

Original Answer

Some databases do already (kind of) create indexes automatically.

In SQL Server the execution plan can sometimes include an Index Spool operator where the RDBMS dynamically creates an indexed copy of the data. However this spool is not a persistent part of the database kept in synch with the source data and it cannot be shared between query executions, meaning execution of such plans may end up repeatedly creating and dropping temporary indexes on the same data.

Perhaps in the future RDBMSs will have the capacity to dynamically drop and create persistent indexes according to workload.

The process of index optimisation is in the end just a cost benefit analysis. Whilst it is true that humans may have more information about relative importance of queries in a workload in principle there is no reason why this information could not be made available to the optimiser. SQL Server already has a resource governor that allows sessions to be classified into different workload groups with different resource allocations according to priority.

The missing index DMVs mentioned by Kenneth are not intended to be implemented blindly as they only consider the benefits to a specific query and make no attempt to take account of the cost of the potential index to other queries. Nor does it consolidate similar missing indexes. e.g. the output of this DMV may report missing indexes on A,B,C and A,B INCLUDE(C)

Some current issues with the idea are

The quality of any automated analysis that does not actually create the index will be highly dependant upon the accuracy of the costing model.
Even within the field of automated analysis an offline solution will be able to be more thorough than an online solution as it is imperative that an online solution should not add large book keeping overhead to the live server and interfere with its primary purpose of executing queries.
The indexes created automatically in response to workload will necessarily be created in response to  queries that would have found them useful so will lag behind solutions that create the indexes in advance.

It is probably reasonable to expect the accuracy of costing models to improve over time but point 2 looks trickier to solve and point 3 is inherently insoluble.

Nevertheless probably the vast majority of installs are not in this idealised situation with skilled staff who continuously monitor, diagnose, and anticipate (or at least react to)  changes in workloads.

The AutoAdmin project at Microsoft Research has been running since 1996

  The goal of this project is to make databases self-tuning and
  self-administering by exploiting knowledge of the workload

The project home page lists several intriguing projects. One is particularly relevant to the question here

  Another interesting problem arises when there is no DBA available
  (e.g. an embedded database or a small business). In such scenarios, a
  low touch continuous index tuning approach may become important. We
  have explored solutions ...[in]  “An Online Approach to Physical Design Tuning” in ICDE
  2007.

The authors state

  With increasingly common DBMS features like online indexes , it is
  appealing to explore more automatic solutions to the physical design
  problem that advance the state of the art.

The paper introduces an algorithm

  Its main characteristics are:

  As queries are optimized, we identify a relevant set of candidate indexes that would improve performance. This feature allows query
  processing to continue in parallel with indexes that are built in the
  background.
  At execution time, we track the potential beneﬁts that we
  lose by not having such candidate indexes and also the utility of
  existing indexes in the presence of queries, updates, and space
  constraints.
  After we gather enough “evidence” that a physical design change is beneﬁcial, we automatically trigger index creations or deletions.
  The online nature of our problem implies that we will generally lag behind optimal solutions that know the future. However, by carefully
  measuring evidence, we ensure that we do not suffer from “late”
  decisions signiﬁcantly, thus bounding the amount of incurred loss

The implementation of the algorithm allows for throttling in response to changes in server load and also can abort index creation if during creation the workload changes and expected benefit falls below the point that it is deemed worthwhile.

The conclusion of the authors on the topic of Online versus traditional physical tuning.

  The online algorithms in
  this work are useful when DBAs are uncertain about the future behavior
  of the workload, or have no possibility of doing a comprehensive
  analysis or modelling. If a DBA has full information about the
  workload characteristics, then a static analysis and deployment by
  existing tools (e.g., [2, 3]) would be a better alternative.

The conclusions here are similar to those in another paper Autonomous Query-driven Index Tuning

  Our approach cannot beat the index advisor if the whole workload is
  known in advance. However, in dynamic environments with evolving and
  changing workloads the query-driven approach produces better results.

They are not smart, they are a piece of code.
Every time you input new data to a database, it needs to find a new location to it and a map to find it when it is requested.
Indexing sounds easier than it is, you just give a new number to a new chunk of data?
Well, how about if the next query is not about the last chunk of data but about 36271 chuncks earlier? You can easily find it with your index, right? But what if the query includes a word like "fishing" to be found in the old 36271 chunk made in 1997? Ho? Not a word about fishing in the old article.

If data came to the database one by one, it could be indexed like that.
But simple indexing will have you wrong results and/or slow performance sooner or later...


How can sqlplus knows where ORACLE_SID point? As I understand, SID is unique value to identify an Oracle database instance.

And I can set default database instance sqlplus use by changing ORACLE_SID environment variable.

But how can sqlplus know where the SID point?

I have thought tnsnames.ora let sqlplus know the information but there seems not present that kind information.
(If so when does sqlplus use tnsnames.ora?)

When you use SQLPlus and make a bequeath connection (e.g., not going through the listener and not specifying the SID/Service Name in the connect string or using a TNS alias), SQLPlus/Oracle uses the combination of ORACLE_SID and ORACLE_HOME to uniquely identify the instance that it connects to.

Important to note: You may have multiple instances running on one server which all share the same OH, but they will have different SIDs.  Or you could have multiple instances running from multiple Oracle Homes.  Two instances on one machine will never share the same combination of SID and OH.

It's important to note that this connection method will only work when you're making connections from the command line of the server that the Oracle database/listener is running on.

If you're connecting from a remote server (or from the same server if you need to connect to a specific service - e.g. a PDB in 12c), you must specify in the connect string one of either a tnsnames alias, or the hostname [optional: port] and the service name you're connecting to.

e.g,:

sqlplus scott/tiger@hr

This assumes the tnsnames.ora on the client is set up with an alias for "hr".  This TNS alias will have the hostname, port, and SID/Service Name in it.

sqlplus scott/tiger@hostname[:port]/servicename

This connects to the hostname and SID/Service Name provided on the command line.  ORACLE_SID, ORACLE_HOME variables and TNS are entirely unrelated to this connection and are not used.

A real example:

sqlplus hr/hr@productiondatabase.company.com:1521/hrdb

The workings of how SQLPlus connects to the database without a listener is not documented, so any answer given here would be speculation.


Table with N associations Suppose I have three tables: meta_tags, users, posts. (just an example)

Users and Posts both could have tags, so my meta_tags table could be structured like

 meta_tags
   - user_id
   - post_id
   - tag

But, I want my meta_tags table to be extendable. If I later create an item table that could also have a tag, I'd need to adjust my meta_tag table with a new _id column.

Would it be better to instead format my meta_tags table like

 meta_tags
   - thing_id
   - thing_type
   - tag

so that my query would be

 SELECT tag FROM meta_tags WHERE thing_id = 1 AND thing_type = "user";

vs

 SELECT tag FROM meta_tags WHERE user_id = 1;

or, maybe I should have separate tables? user_tags, post_tags, etc..

I guess my question is...which way is considered best practice? Are their significant performance impacts I should be aware of?

A way to resolve this would be to put the joining of things with tags into separate tables.  You would have your original tables users, posts, meta-tags and perhaps items.  In addition, you would have user-tags, post-tags, and perhaps item-tags.  The later three tables would only have the keys from the specific type table (e.g. user_id) and the key from the meta-tags table.

This design would also allow you to have other attributes to a tag that apply across all the base tables.

neither

base table meta
- user_id  PK FK user
- post_id  PK FK post

tag user table  meta_tags_user_string
- user_id  PK FK  meta
- post_id  PK FK  meta
- tag_ID   PK FK  tag_Def
- value

tag post table  meta_tags_post_string
- post_id  PK FK  meta
- tag_ID   PK FK  tag_Def
- value

You will need separate meta_tags for each value type
e.g. meta_tags_dateTime


MySQL Replication Master push to Slave I have two servers that need to be synced. One is online and has a public IP address. The other is internal and has no public IP address - nor is there a way to get it a public IP so that's off the table.

I'd like to set up replication between the two servers through MySQL but as I understand it, the MySQL slave needs to be able to connect to the master (i.e. the master doesn't just simply push data out) and that's a problem since there's no public IP or interface to connect to.

Is there any way to replicate and simply tell the master to push data to the slave (which it can access)? Alternatively, is there some other solution? I have considered a mysqldump periodically but the data is going to grow very quickly resulting in too-large dump files.

Make sure that you are able to connect to Remote MySQL server (Online server) from a Slave server machine.
You can verify by using telnet command

# telnet <online server IP><space><MySQL port>

Now follow the replication procedures mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

For Database synchronization refer existing answer here:

https://stackoverflow.com/questions/7946540/how-do-i-sync-2-mysql-databases

At first, create OpenVPN Server on your online database server.

Then, connect from your internal database server to your online database server.

Now, your database server should be able to PING each other, and can be set to use normal replication as it should.


inet_client_addr in PostgreSQL don't return IP i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return  is '::1/128'.
This is a problem with my script or with the server?

    CREATE OR REPLACE FUNCTION trg_aud_cms() RETURNS TRIGGER AS $aud_cms$
BEGIN
    --
    -- Cria uma linha na tabela para refletir o estado anterior ao atual
    IF (TG_OP = 'DELETE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('D', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('U', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('I', USER, inet_client_addr(), now(), NEW.codcms, NEW.titlecms, NEW.contentcms, NEW.hashtagcms);
            RETURN NEW;
    END IF;
    RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$aud_cms$ LANGUAGE plpgsql;

CREATE TRIGGER aud_cms
AFTER INSERT OR UPDATE OR DELETE ON cms
FOR EACH ROW EXECUTE PROCEDURE trg_aud_cms();

You could abuse the application_name setting to pass the HTTP client IP from PHP to Postgres.

In PHP, once connected:

<? pg_query("SET application_name TO '$_SERVER[REMOTE_ADDR]'"); ?>

Or set it right at connection time with pg_connect which would normally support it in the conninfo string (see Connection Strings in the manual)

In your trigger, use   current_setting('application_name') to retrieve this information,
instead of inet_client_addr() which doesn't do what you want, as already answered.

There should also be a check in the trigger that this value is what you expect before storing it, since the trigger may be fired in any session, not just the ones initiated by PHP.

Function inet_client_addr() just return the client IP，just look the example;

--1 Return null

[pg92@redhatB ~]$ psql skytf skytf;
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr
------------------

(1 row)

--2 Return 127.0.0.1

[pg92@redhatB ~]$ psql -h 127.0.0.1 skytf skytf;
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr
------------------
 127.0.0.1
(1 row)

--3 Return the client physical Ip

[pg92@redhatB ~]$ psql -h 192.168.1.36 skytf skytf;
Password for user skytf:
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr
------------------
 192.168.1.36
(1 row)


Sum date between two date and group by month I have a table as below:
Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

I am using a SQL table-valued function to return a table that has 2 columns: Month and Count total Date of this month.
Example:

Joe: 10 days in Apr, 20 days in May
John: 28 days in May

Finally I will return a new table

Month
Count

4
10

5
48

I tried to use datediff and datepart to group by month, but don't know how to sum after group. Is there any way to do this?
Besides, I want to add filter from date and to date.

Another approach:
CREATE TABLE dbo.DateRanges
(
    [Name] varchar(20) NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL,

    PRIMARY KEY ([Name], [Start Date]),
    CHECK ([End Date] >= [Start Date])
);

INSERT dbo.DateRanges
    ([Name], [Start Date], [End Date])
VALUES
    (CONVERT(varchar(20), 'Joe'), CONVERT(date, '20/04/2021', 103), CONVERT(date, '20/05/2021', 103)),
    (CONVERT(varchar(20), 'John'), CONVERT(date, '01/05/2021', 103), CONVERT(date, '28/05/2021', 103));

Solution:
SELECT
    [Month] = Months.m,
    [Count] =
        SUM
        (
            -- Number of days in the current month
            1 + DATEDIFF
            (
                DAY,
                -- Latest of [Start Date] and current month start date
                IIF(DR.[Start Date] <= MonthRange.StartDate, MonthRange.StartDate, DR.[Start Date]),
                -- Earliest of [End Date] and current month end date
                IIF(DR.[End Date] >= MonthRange.EndDate, MonthRange.EndDate, DR.[End Date])
            )
        )
FROM dbo.DateRanges AS DR
JOIN
(
    -- Month numbers
    VALUES
        (01), (02), (03), (04), (05), (06),
        (07), (08), (09), (10), (11), (12)
) AS Months (m)
    -- Months that overlap the [Start Date], [End Date] range
    ON Months.m BETWEEN MONTH(DR.[Start Date]) AND MONTH(DR.[End Date])
    OR Months.m BETWEEN MONTH(DR.[End Date]) AND MONTH(DR.[Start Date])
CROSS APPLY
(
    -- Start and end day of each month
    VALUES
        (
            DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1),
            EOMONTH(DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1))
        )
) AS MonthRange (StartDate, EndDate)
GROUP BY
    Months.m
ORDER BY
    Months.m;

Output:

Month
Count

4
11

5
48

Online demo:
db<>fiddle demo

TL;DR
I added some sample data over a previous year (see fiddle) and included a period which straddled New Year's Eve. The approach I've used assumes the existence of a calendar table - ways of doing this using recursive CTE's are discussed below. By adding the WHERE clauses supplied (commented out), one can either restrict the date range and/or whether there was any stay in a given month.
SELECT
  DATEPART(mm, ad.tdate)    AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",  -- Not strictly necessary
  DATEPART(yy, ad.tdate)    AS "YEAR",
  SUM
  (
    CASE
      WHEN s.start_date IS NOT NULL THEN 1
      ELSE 0
    END
  ) AS "Stays"
FROM all_dates ad
LEFT JOIN stay s
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
-- WHERE ad.tdate >= '2021-03-01'
-- AND ad.tdate <= '2021-06-30'       -- Restrict dates of stays
-- WHERE s.start_date IS NOT NULL     -- Show only months with at least 1 stay
GROUP BY DATEPART(yy, ad.tdate), DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(yy, ad.tdate), DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

Result (see extra data in fiddle - this result appears to be consistent):
Month        Name   YEAR    Stays
    1     January   2020        0
    2    February   2020        0
    3       March   2020        0
    4       April   2020        0
    5         May   2020       12
    6        June   2020       20
    7        July   2020       12
    8      August   2020       20
    9   September   2020        0
   10     October   2020        6
   11    November   2020       11
   12    December   2020       17
    1     January   2021        2
    2    February   2021        2
    3       March   2021        2
    4       April   2021       11
    5         May   2021       48
    6        June   2021        0
    7        July   2021        0
    8      August   2021        0
    9   September   2021        2
   10     October   2021        0
   11    November   2021       11
   12    December   2021        5

More detailed discussion:
There are two or three alternative methods to the accepted answer which might work. This is a classic case of where a calendar table is required. There are two ways of doing this - the first is by using "classic" SQL - using a function - and also by using the more modern CTE (Common Table Expression) approach, in particular a recursive CTE (see links in the reference above).
The first gem I came across was this article by Jon Tavernier on how to create a (materialised) calendar table in SQL Server - I pared it back to the minimum required by this problem, although this sort of table has so many potential uses, you may wish to consider doing it for period 10-20 years (only ~ 3.5K - 7K records) and with far more fields (e.g. 1st day of month, end of accounting period...).
Just one small point - your table definition seems to be simplified, but I would suggest that you make it something more like this - increasing the amount of information, the server has makes it easier for it to come up with a good plan:

CREATE TABLE stay
(
  name VARCHAR(255) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE   NOT NULL,

  CONSTRAINT n_sd_ed_pk PRIMARY KEY (name, start_date, end_date),

  CONSTRAINT n_sd_uq UNIQUE (name, start_date),

  CONSTRAINT n_ed_uq UNIQUE (name, end_date),

  CONSTRAINT sd_lt_ed_ck CHECK (start_date < end_date),

);

You might also want to ensure that there is no overlap between a given name and their start_date and end_date - but I don't know how to do this in SQL Server short of using a trigger - my UNIQUE constraints are an attempt at that through SQL - but they won't prevent nested intervals...
Here's an article on how to do it using a trigger.
First approach (calendar table - Tavernier):
The code for approach 1 is available on the fiddle here.
DECLARE @start_dt AS DATE = '2021-01-01';       -- Date from which the calendar table will be created (inclusive).
DECLARE @end_dt   AS DATE = '2022/01/01';       -- Calendar table will be created up to this date (not inclusive).

CREATE TABLE all_dates
(
 tdate DATE PRIMARY KEY
);

WHILE @start_dt < @end_dt
BEGIN
  INSERT INTO all_dates
  (
    tdate   -- can generate many more useful values - see article by Jon Tavernier
  )
  VALUES
  (
    @start_dt
  )
  SET @start_dt = DATEADD(DAY, 1, @start_dt)
END;

and then I check by:
SELECT * FROM all_dates;

Result (snipped for brevity):
  tdate
2021-01-01
2021-01-02
2021-01-03
2021-01-04
2021-01-05

Next, we set up our test data as per the OP's question (I called the table stay):
name    start_date  end_date
Joe     2021-04-20  2021-05-20
John    2021-05-01  2021-05-28

Then, I ran this query:
SELECT
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "M Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(mm, ad.tdate);

Result:
Month     Name  Count
    4    April     11
    5      May     48

The month name wasn't included in the spec, and can easily be dropped - just thought it was a nice to have.
I then turned on Statistics as follows:
SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

and reran the query - results discussed below.
Second approach (calendar year table using pre-defined dates via a CTE):
The code for this approach is available on the fiddle here.
Sometimes consultants aren't allowed to create physical tables, and I wanted the natural approach for this is to use a CTE approach and I stumbled across this next gem (Creating a date dimension or calendar table in SQL Server) by Aaron Bertand (of this parish).
He begins his piece with these sage words:

One of the biggest objections I hear to calendar tables is that people
don't want to create a table. I can't stress enough how cheap a table
can be in terms of size and memory usage, especially as underlying
storage continues to be larger and faster, compared to using all kinds
of functions to determine date-related information in every single
query. Twenty or thirty years of dates stored in a table takes a few
MBs at most, even less with compression, and if you use them often
enough, they'll always be in memory.

He goes on to say that:

This is a one-time population, so I'm not worried about speed, even
though this specific CTE approach is no slouch. I like to materialize
all of the columns to disk, rather than rely on computed columns,
since the table becomes read-only after initial population. So I'm
going to do a lot of those calculations during the initial series of
CTEs. To start, I'll show the output of each CTE one at a time.

So, even the function in the first approach might be suitable - but, CTE's (Common Table Expressions - also, see the link to RECURSIVE CTE's within) were, as best as I can ascertain, introduced into SQL Server after Tavernier's article above.
I adapted the code from Bertrand's second CTE as follows:
WITH seq (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, '2021-01-01', '2021-12-31')
),
all_dates (tdate) AS
(
  SELECT DATEADD(DAY, n, '2021-01-01')  FROM seq
)
SELECT tdate FROM all_dates
OPTION (MAXRECURSION 0);  -- Important!!! Fails after 100 recursions if not present

Result (snipped for brevity):
         tdate
2021-01-01 00:00:00.000
2021-01-02 00:00:00.000
2021-01-03 00:00:00.000

So, we're golden as far as our calendar table is concerned...
Then, I just put that over my original query from approach 1 as follows:
WITH seq (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, '2021-01-01', '2021-12-31')
),
all_dates (tdate) AS
(
  SELECT DATEADD(DAY, n, '2021-01-01') FROM seq
)
SELECT
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

Result (same):
Month    Name   Count
    4   April      11
    5     May      48

So, I did the same as for the first approach and turned on Statisics - discussed below.
3rd approach (restricted dates):
The fiddle is available here.
Here, in the interests of "efficiency", I restricted the dates in the calendar table to the MIN() of start_time and the MAX() of end_time in the stay table as follows:
WITH stays (min_sd, max_ed, no_days) AS
(
  SELECT
    MIN(start_date) AS min_sd,
    MAX(end_date) AS max_ed,
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) AS no_days
  FROM stay
),
seq(n, tdate) AS
(
  SELECT 0, (SELECT min_sd FROM stays)
  UNION ALL
  SELECT n + 1, DATEADD(DAY, n + 1, (SELECT min_sd FROM stays))
  FROM seq
  WHERE n < (SELECT no_days FROM stays)
)
SELECT * FROM seq;

Result (snipped for brevity):
n   tdate
0   2021-04-20
1   2021-04-21
2   2021-04-22
(39 rows...)

So, now we have every date between the earliest start_date and the latest end_date.
And using these dates as our calendar table, we formulate the query:
WITH stays (min_sd, max_ed, no_days) AS
(
  SELECT
    MIN(start_date) AS min_sd,
    MAX(end_date) AS max_ed,
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) AS no_days
  FROM stay
),
all_dates(n, tdate) AS
(
  SELECT 0, (SELECT min_sd FROM stays)
  UNION ALL
  SELECT n + 1, DATEADD(DAY, n + 1, (SELECT min_sd FROM stays))
  FROM all_dates
  WHERE n < (SELECT no_days FROM stays)
)
SELECT
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

Result (same):
Month    Name   Count
    4   April      11
    5     May      48

I have some performance stats at the end of the fiddles. It is, of course, very difficult to draw many conclusions from performance analyses of (relatively) tiny amounts of data on a server over which one has no control - I would urge you to test with your own data and under your own load...


EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other? Whenever I need to check for the existence of some row in a table, I tend to write always a condition like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Some other people write it like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

When the condition is NOT EXISTS instead of EXISTS: In some occasions, I might write it with a LEFT JOIN and an extra condition (sometimes called an antijoin):

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

I try to avoid it because I think the meaning is less clear, specially when what is your primary_key is not that obvious, or when your primary key or your join condition is multi-column (and you can easily forget one of the columns). However, sometimes you maintain code written by somebody else... and it is just there.

Is there any difference (other than style) to use SELECT 1 instead of SELECT *?
Is there any corner case where it does not behave the same way?
Although what I wrote is (AFAIK) standard SQL: Is there such a difference for different databases / older versions?
Is there any advantage on explicity writing an antijoin?
Do contemporary planners/optimizers treat it differently from the NOT EXISTS clause?

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well.

In some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there is not even evaluated.

About the LEFT JOIN / IS NULL antijoin method, a correction: this is equivalent to NOT EXISTS (SELECT ...).

In this case, NOT EXISTS vs LEFT JOIN / IS NULL, you may get different execution plans. In MySQL for example and mostly in older versions (before 5.7) the plans would be fairly similar but not identical. The optimizers of other DBMS (SQL Server, Oracle, Postgres, DB2) are - as far as I know - more or less capable of rewriting these 2 methods and considering the same plans for both. Still, there is no such guarantee and when doing optimization, it is good to check the plans from different equivalent rewrites as there could be cases that each optimizer doesn't rewrite (eg. complex queries, with many joins and/or derived tables / subqueries inside the subquery, where conditions from multiple tables, composite columns used in the joining conditions) or the optimizer choices and plans are affected differently by the available indexes, settings, etc.

Also note that USING cannot be used in all DBMS (SQL Server for example). The more common JOIN ... ON works everywhere.
And the columns needs to be prefixed with the table name/alias in the SELECT to avoid errors/ambiguities when we have joins.
I also usually prefer to put the joined column in the IS NULL check (although the PK or any non-nullable column would be OK, it might be useful for efficiency when the plan for LEFT JOIN uses a non-clustered index):

SELECT a_table.a, a_table.b, a_table.c
  FROM a_table
       LEFT JOIN another_table
           ON another_table.b = a_table.b
 WHERE another_table.b IS NULL ;

There is also a third method for antijoins, using NOT IN but this has different semantics (and results!) if the column of the inside table is nullable. It can be used though by excluding the rows with NULL, making the query equivalent to the previous 2 versions:

SELECT a, b, c
  FROM a_table
 WHERE a_table.b NOT IN
       (SELECT another_table.b
          FROM another_table
         WHERE another_table.b IS NOT NULL
       ) ;

This also usually yields similar plans in most DBMS.

IF EXISTS(SELECT TOP(1) 1 FROM is a better habit long term and across platforms simply because you do not need to even start worrying about how good or bad your current platform/version is; and SQL is moving from TOP n towards parameterizable TOP(n). This should be a learn-once skill.


How to install repmgr 4.2 (ubuntu) apt-get install postgresql-10-repmgr  give me repmgr 4.3.  However, my primary server is still on 4.2.

How do I install repmgr version 4.2?

Thanks in advance!

An archive of old packages (3.3.2 and later) for Debian/Ubuntu-based systems is available here:
http://atalia.postgresql.org/morgue/r/repmgr/


Difference in duration of SP:Completed and sum(SP:StmtCompleted) I have a stored procedure, which sometimes displays a big difference in duration for the overall execution time and the execution time for each of its statements. The procedure consist of 4 statements, each with a maximum run-time of 4ms. But some procedure has Sp:Completed duration of around 1000 ms or more.
The procedure is called very often (at least once every second, or more).
My first idea was about recompile time, but this procedure is rather simple and doesn't need to be recompiled. I run trace to find recompile events relating this procedure and can't catch any record in it.
I want to use MS SQL 2017 if it may help. Every duration which I mention here was obtained from the SQL Server Profiler.
What is the reason for the long procedure execution time?

It's just in how and what they measure. The statement complete measures only that statement. It doesn't measure any wait time while that statement didn't start, etc. The procedure complete measures all sorts of extraneous management, blocks, waits, etc. There's just not going to be a perfect correlation between the two.

You should enable below setting which will show you where the time is being consumed and also enable the show plan for the query.

  set statistics time on

This will help you to track weather query taking time in parsing or execution the statement and with the help of execution plan you can easily tune the query.


When running `make installcheck` I get can not connect to database On Ubuntu when I run make installcheck I'm getting

/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/9.5/bin'    --inputdir=test --load-language=plpgsql --dbname=contrib_regression base math equality
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
command failed: "/usr/lib/postgresql/9.5/bin/psql" -X -c "DROP DATABASE IF EXISTS \"contrib_regression\"" "postgres"
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:272: recipe for target 'installcheck' failed
make: *** [installcheck] Error 2

What's the reason for this?

This is on the pg-bignum extension.

If you're trying to build an extension on Ubuntu, you may have to set the port to isolate which version of PostgreSQL you wish to test the extension on with PGPORT. You can see here make install check is connecting on 5433 with the socket name .s.PGSQL.5432,

You likely want port 5432

make PGPORT=5433 installcheck


InnoDB row locking - how to implement I've been looking around now, reading the mysql site and I still can't see exactly how it works.

I want to select and row lock the result for writing, write the change and release the lock. audocommit is on.

scheme

id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime)

Select an item with a status of pending, and update it to working. Use an exclusive write to make sure that the same item is not picked up twice.

so;

"SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR WRITE"

get the id from the result

"UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>

Do I need to do anything to release the lock, and does it work like I've done above?

What you want is SELECT ... FOR UPDATE from within the context of a transaction.  SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE.  It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to.  Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary.  It also has a tendency to multiply in a codebase as people cut and paste.

Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.

First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ.  This is normally unnecessary, as it is the default isolation level for InnoDB:

session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

In the other session, update this row.  Linda got married and changed her name:

session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

But now, we want exclusive access to this row, so we call FOR UPDATE on the row.  Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction.  That's not REPEATABLE READ, that's READ COMMITTED

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | BROWN     |
+------------+-----------+
1 row in set (0.00 sec)

Let's test out the lock set in session1.  Note that session2 cannot update the row.

session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

But we can still select from it

session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address           |
+-------------+------------+-----------+------------+-------------------+
|           3 | LINDA      | BROWN     |          7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)

And we can still update a child table with a foreign key relationship

session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> COMMIT;

Another side effect is that you greatly increase your probability of causing a deadlock.

In your specific case, you probably want:

BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;

If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:

UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;

Hope this makes some sense.

Alternatively, you could fragment the record fields to allow parallel writing and bypass the row locking (fragmented json pairs style).
So if one field of a compound read record was an integer/real you could have fragment 1-8 of that field (8 write records/rows in effect).
Then sum the fragments round-robin after every write into a separate read lookup.
This allows upto 8 concurrent users in parallel.

As you are only working with each fragment creating a partial total, there is no collision and true parallel updates (ie you write lock each fragment rather than the whole unified read record). This only works on numerical fields obviously. Something that relies on mathematical modification to store a result.

Thus, multiple write fragments per unified read field per unified read record.
These numerical fragments also lend themselves to ECC, encryption and block level transfer/storage.
The more write fragments there are, the greater the parallel/concurrent write access speeds on saturated data.

MMORPG suffer massively with this issue, when large numbers of players all start hitting each other with Area of Effect skills. Those multiple players all need to write/update every other player at exactly the same time, in parallel, creating a write row locking storm on unified player records.


Do I need a locking read? I am inserting rows in a table, based on a value and on the existence of a row, which  I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The basic steps are (pseudocode)

function doStuff(int someID){
    found = `SELECT someIDFROM targettable`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

The problem is ofcourse that if process1 reads uniqueID, figures it should insert the value, and process2 does the same before the write is finished, I get 2 lines in my targettable. This is possible (someID is not unique for that table), but in this case unwanted because it was done on wrong assumptions because of the concurrency.

Now my assumption/feeling is that a transaction alone is not enough for this. The second process will have consistent reads I think, but that does not help prevent the second INSERT, does it? so this will not do (I think, please advice me on this. This could be where I'm going wrong). (I'm quite sure at the moment this is not enough to ensure correct behaviour, because of testing)

function doStuff(int someID){
    'START TRANSACTION HERE'
    found = `SELECT someIDFROM targettable where someid = someID `
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
    COMMIT TRANSACTION
}

From what I  read about InnoDB locking reads it could be that I should use those, but I'm not sure as I have never worked with them. It could also be the transaction is enough, and I'm just begin foolish.

My current idea is to use SELECT someID FROM targettable FOR UPDATE, which I figure will give me a lock. But I'm not sure what lock, since it depends on isolation level. This would look like so:

function doStuff(int someID){
    found = `SELECT someIDFROM targettable where someid = someID FOR UPDATE`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

I'm not sure how to cope with the value I get from another table, but that might be another thing.

Summarizing: Can I get away with just using a transaction, do I need to use a locking-read (if so, how?) or is there another option I missed?

Update: If I do something like

START TRANSACTION;
SELECT * FROM targettable WHERE someid = 3 FOR UPDATE

And query that select in another thread, I get no result (locked?) until this transaction is finished. But if I put an INDEX on that someid, I DO! I'm probably missing something, but I wasn't expecting that.
How can I be sure I don't insert this row twice?

I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT.  That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

In the best case, you run 10000 SELECTs are find that someID exists
In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}

I think this link might do what you're trying to accomplish.  Basically it's insert where not exists statement: https://stackoverflow.com/a/3025332/16501