I have an INSERT
statement that looks like this:
INSERT INTO officer (officer_number,
name,
bank_id)
VALUES ('',
'',
8)
ON DUPLICATE KEY UPDATE officer_number = '',
name = '',
bank_id = 8,
id = LAST_INSERT_ID(id)
This way of doing it has been working just fine. It stopped working when I added the following trigger:
CREATE TRIGGER officer_update BEFORE UPDATE ON `officer`
FOR EACH ROW SET NEW.updated_at = NOW(), NEW.created_at = OLD.created_at
It's not that the officer
record isn't getting inserted. It just seems that the trigger is hijacking LAST_INSERT_ID()
or something. I say this because the next query that's executed is this:
INSERT INTO account (import_id,
branch_id,
account_number,
officer_id,
customer_id,
open_date,
maturity_date,
interest_rate,
balance,
opening_balance)
VALUES ('123',
'4567',
'789',
'0', # This is the officer id which is of course invalid
'321',
'1992-04-22',
'2012-05-22',
'0.0123',
'0',
'10000')
Since I've run dozens of successful imports with the same exact file, I haven't changed my code, and now my imports aren't working after I added this trigger, I must deduce that the trigger is the culprit. I had a similar situation with another table and removing the trigger fix the problem.
So my questions are:
I have another trigger on officer.created_at
(and a lot of other tables' created_at
s) and I would prefer to avoid some sort of awkward solution where I have a trigger on created_at
but a DEFAULT CURRENT_TIMESTAMP
on updated_at
. For some reason, MySQL only allows one auto-timestamp per table, so I can't do CURRENT_TIMESTAMP
for both created_at
and updated_at
.
Here is the SHOW CREATE TABLE
for officer
:
CREATE TABLE `officer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`officer_number` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`bank_id` bigint(20) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `officer_number` (`officer_number`,`name`),
UNIQUE KEY `officer_number_2` (`officer_number`,`bank_id`),
KEY `bank_id` (`bank_id`),
CONSTRAINT `officer_ibfk_1` FOREIGN KEY (`bank_id`) REFERENCES `bank` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102735 DEFAULT CHARSET=latin1
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
Your INSERT ... ON DUPLICATE KEY UPDATE
appears to be a way of preventing an error if officer_number
already exists. Do you need the update to happen (to fire the TRIGGER), or could you instead use INSERT IGNORE
?:
INSERT IGNORE INTO officer (officer_number,
name,
bank_id)
VALUES ('',
'',
8);
That would simply do nothing if officer_id
already exists, thus removing the need for the update (and therefore LAST_INSERT_ID()
) altogether.
If that is not possible, then perhaps your INSERT ... ON DUPLICATE KEY UPDATE
could be tweaked. I'm not clear on the purpose of:
id = LAST_INSERT_ID(id)
LAST_INSERT_ID()
(without any arguments), returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.
However, if you supply an argument, it returns the value of that argument, and the next call to LAST_INSERT_ID()
(without any arguments), returns the same value. For example:
SELECT LAST_INSERT_ID(100);
+---------------------+
| LAST_INSERT_ID(100) |
+---------------------+
| 100 |
+---------------------+
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 100 |
+------------------+
So, if we assume that id == 100
, then this should be true:
SELECT LAST_INSERT_ID(id);
+--------------------+
| LAST_INSERT_ID(id) |
+--------------------+
| 100 |
+--------------------+
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 100 |
+------------------+
Following on from that:
id = LAST_INSERT_ID(id)
Should be the same as:
id = id
Or, as suggested by Josh Davis, it shouldn't be necessary at all. Have you tried simply id = id
? What exactly happens when you exclude it?
The manual states that:
However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined
and:
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own.
As you are using both LAST_INSERT_ID()
and LAST_INSERT_ID(expr)
, the behaviour is undefined. Furthermore, the TRIGGER may be regarded as being one connection (it's run directly on the server), whereas the INSERT and CREATE statements are possibly called from a different connection. Given this, and the various changes and bugs that have been reported associated with LAST_INSERT_ID between versions, it's likely that there will be problems with your approach.
Going back to what Josh Davis said, I'd be inclined to resolve the use of id = LAST_INSERT_ID(id)
in your INSERT statement. It would also be helpful to know how you derive the officer_id
in your INSERT INTO account
statement - the one which is receiving a zero value.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With