Bug #71492 Expected ERROR 1104, none given with INSERT IGNORE
Submitted: 27 Jan 2014 11:11 Modified: 28 Jan 2014 15:12
Reporter: Dotan Cohen Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.33 OS:Linux (Linux ab 3.2.0-58-virtual #88-Ubuntu SMP Tue Dec 3 17:58:13 UTC 2013 x86_64 x86_64 x86_64 GNU/Linuxd)
Assigned to: CPU Architecture:Any

[27 Jan 2014 11:11] Dotan Cohen
Despite the 'IGNORE', I would expect to be informed that the following query could not be performed due to using SAFE-UPDATES:

mysql> INSERT IGNORE INTO someTable SELECT foo, bar FROM oldTable;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO someTable SELECT foo, bar FROM oldTable;
Query OK, 4648305 rows affected (2 min 8.42 sec)
Records: 9201328  Duplicates: 4553023  Warnings: 0

This is the error message that I would expect:
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

The 'IGNORE' is to continue processing _when_an_insert_fails_, not to suppress messages that the query cannot be run at all.

How to repeat:
1) Start MySQL with -U
2) Use INSERT IGNORE INTO to copy more than 1000 rows from one table to another.

Suggested fix:
Output the correct error message.
[27 Jan 2014 14:28] MySQL Verification Team
Sorry, but this is not a bug.

Keyword IGNORE serves one purpose only. It is there to instruct server to ignore any unique key violations, be it primary or unique key. It is not there to check and abort for any other problems, including MAX_JOIN_SIZE overflows.

Thank you for your interest.
[27 Jan 2014 15:02] Dotan Cohen
@Sinisa: I agree with you completely. I think that you misread my comment. I do expect MySQL to return the error, but it does not!

Please reopen.
[27 Jan 2014 15:12] MySQL Verification Team

You are correct !!!

However, please check whether you have any of the strict modes enabled. Those are some that are by default in 5.6, but not in 5.5. In any case, check sql_mode, because, as our manual clearly states:

   With either of the strict mode options, you can cause errors to be
   treated as warnings by using INSERT IGNORE or UPDATE IGNORE rather
   than INSERT or UPDATE without IGNORE.
[28 Jan 2014 7:24] Dotan Cohen
mysql> select @sql_mode;
| @sql_mode |
| NULL      |
1 row in set (0.00 se
[28 Jan 2014 9:14] Dotan Cohen
mysql> select @@sql_mode;
| @@sql_mode |
|            |
1 row in set (0.01 sec)
[28 Jan 2014 14:16] MySQL Verification Team
I have quoted the wrong paragraph. I have now used, instead, the latest version of 5.5 manual and it clearly shows that all errors that occur with INSERT command are ignored when this keyword is used. Quotation:
•	If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example,  .... 
Then, several examples are provided, but these are just examples. What is important is that with IGNORE, all errors are turned into warnings.
[28 Jan 2014 15:12] Dotan Cohen
Thank you, Sinisa. Note that neither an error nor a warning are provided.