Thank you for the bug report. Sorry but this behavior isn't a bug instead is an expected behavior how AUTO_INCREMENT works when that column is omitted in the INSERT statement and also no syntax error exists (it is valid syntax). The sample how AUTO_INCREMENT works is illustrated at:
http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
so how a new value of system_user_id is created (2 in your sample) ON DUPLICATE KEY UPDATE hasn't effect.

[25 May 2009 23:47]
Michael McLaughlin

While the URI you've referenced does point to the auto increment behavior, it has no reference to the ON DUPLICATE KEY UPDATE behavior in this context.
It strikes me that the intent of the ON DUPLICATE KEY UPDATE performs the behavior as the Oracle MERGE statement. Oracle's MERGE statement does enforce matching the submitted key against the current key, which is how MySQL performs when the primary key column is included in the overriding signature.
I don't understand why an override signature would be allowed that excludes the auto increment column. It certainly does allow an insertion anomaly. Isn't the better documentation reference this:
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
If this isn't a bug, it's certainly something to watch out for when using the ON DUPLICATE KEY UPDATE clause. It appears a change to the online document I've referenced may help users - 12.2.5.3.

[26 May 2009 5:56]
Sveta Smirnova

Thank you for the feedback.
Re-closed as "Not a Bug".

[7 Feb 2013 17:26]
Neike Taika-Tessaro

Sorry, I'm very late to the party, :( but I got here from a comment on the documentation and... I admittedly don't understand the fuss. Could you bear with me for a moment, not just for my benefit, but (hopefully) that of others reading the comments in the documentation?
As I understand it:
INSERT ODKU is an INSERT first and foremost. If your INSERT statement doesn't conflict with a unique key (i.e. UNIQUE or PRIMARY), there is no reason to proceed with ODKU and it naturally won't fire. No duplicate key was attempted to be inserted.
In the example you cited, you might have intended for 'system_user_name' to be UNIQUE - but... if you HAD made it so, the row would have been updated, entirely without the inclusion of the auto_increment column.
(That being said, it might indeed make sense to have ODKU fail on principle if no unique keys are accessed in INSERT, but that has nothing to do with auto_increment.)
Am I somehow completely misunderstanding the issue?

[28 Nov 2014 6:21]
Henry Antona

hi. im having problems with my code, can you help me find the best solution? thanks in advance.