ON DUPLICATE KEY UPDATE doesn't work when there's an UPDATE trigger

Question

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:

1. Can someone explain what, specifically, is causing my officer id to get set to 0?
2. What's a good solution to this problem?

I have another trigger on officer.created_at (and a lot of other tables' created_ats) 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

Tropical

Could you please post the results of SHOW CREATE TABLE officer and the query which works incorrectly? Your title says ON DUPLICATE KEY UPDATE but there is no such query in the post. – Julee

Woops. Good catch. I've updated the question. – Tropical

Can you please paste your code that you use to retireve the value of LAST_INSERT_ID()? seems to be working fine on my machine MySql 5.1.53 – Noninterference

Answer 1

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.

Subshrub

Here's what I ended up doing: I used INSERT IGNORE but if I left it at that, the LAST_INSERT_ID() wouldn't come up right. After my INSERT, I just selected whatever record matched whatever I just inserted. It may not be the most succinct solution in the world but it works and I'm tired of messing around with it. – Tropical

Answer 2

I don't understand why you try to update the id. Can't you just remove the call to LAST_INSERT_ID()?

INSERT INTO officer (officer_number,
                     name,
                     bank_id)
VALUES ('',
        '',
        8)

ON DUPLICATE KEY UPDATE officer_number = '',
                        name = '',
                        bank_id = 8

Also, you should post your MySQL version (as well as the engine used), as there's been some changes to LAST_INSERT_ID() behaviour in the past.

Theron

It doesn't work without the LAST_INSERT_ID(). I'm using MySQL 5.1.49 and InnoDB. – Tropical