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

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.