MySQL Bugs: #67366: INSERT IGNORE INTO always returns TRUE

Bug #67366	INSERT IGNORE INTO always returns TRUE
Submitted:	24 Oct 2012 18:52	Modified:	24 Oct 2012 22:29
Reporter:	Dotan Cohen	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.5	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When using INSERT IGNORE INTO on a UNIQUE field, even if _all_ the values are dupes it still returns "Query OK", a response with equates TRUE (such as returned by third-party libraries such as that provided with PHP):

mysql> INSERT IGNORE INTO someTable (name) VALUES ('beer'), ('stress');
Query OK, 0 rows affected (0.96 sec)
Records: 2  Duplicates: 2  Warnings: 0

I suggest that iff _all_ of the values are dupes, then some error be returned. If it is thought that this may no be backwards-compatible, then how about an alternative syntax INSERT OMIT INTO or INSERT NEGLECT INTO that does return ERROR iff _all_ the values are dupes.

mysql> INSERT IGNORE INTO someTable (name) VALUES ('beer'), ('stress');
ERROR 1234 (123456): All entries are duplicates for key 'name'

-or-

mysql> INSERT OMIT INTO someTable (name) VALUES ('beer'), ('stress');
ERROR 1234 (123456): All entries are duplicates for key 'name'

How to repeat:
rfe

You can find that out by looking at the number of duplicates.

To be more specific, just compare if "Records" equals "Duplicates". That information is also available by looking at the number of affected rows.

No point in introducing a new syntax for something that can be easily deduced.

Thank you Davi, I do have a working workaround for my current code. The issue tracker is certainly not a user support forum!

Although there exist workarounds today, in my opinion a straightforward method done with a single query is necessary as well. In fact, I don't think that new syntax should be introduced, but rather that the existing syntax should return a FALSE equivalent (ERROR) if all records are dupes.