MySQL Bugs: #65597: INSERT IGNORE does not raise WARNING

Bug #65597	INSERT IGNORE does not raise WARNING
Submitted:	13 Jun 2012 8:44	Modified:	19 Aug 2013 20:39
Reporter:	sam wootton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.62-log, 5.5	OS:	Linux (Opensuse)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
INSERT IGNORE does not raise a warning when unique constraint is violated.

Documentation (
http://dev.mysql.com/doc/refman//5.5/en/insert.html ), states:

"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued."

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+----------------+
| Variable_name           | Value          |
+-------------------------+----------------+
| protocol_version        | 10             |
| version                 | 5.1.62-log     |
| version_comment         | SUSE MySQL RPM |
| version_compile_machine | x86_64         |
| version_compile_os      | suse-linux-gnu |
+-------------------------+----------------+
5 rows in set (0.00 sec)

Regards, Sam

How to repeat:
I have a unique constraint (uc_details) on a table "table_a", it is unique on name, artist, file.

Here is proof of the constraint:

INSERT INTO table_a(name, pwd, email) 
VALUES ("sam", "test123", "sam@something.com");
Query OK, 1 row affected (0.00 sec)

INSERT INTO table_a(name, pwd, email)
VALUES ("sam", "test123", "sam@something.com");
ERROR 1062 (23000): Duplicate entry 'sam@something.com' for key 'uc_details'

Now when i re-run this with IGNORE:

INSERT INTO table_a(name, pwd, email) 
VALUES ("sam", "test123", "sam@something.com");
Query OK, 0 rows affected (0.00 sec)

...i get no errors? SHOW WARNINGS:

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

Max error count is fine:

SHOW VARIABLES LIKE 'max_error_count';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+

1 row in set (0.00 sec)

Regards, Sam

Correction on initial report:

"it is unique on name, artist, file"  should read:

"it is unique on name, pwd, email".

Please can you change this in my original post?

Regards, Sam

Please, send the output of:

show create table table_a\G

I actually obfuscated the original table/data names, the original table was "mf_tracks", and here is the out put:

       Table: mf_tracks
Create Table: CREATE TABLE `mf_tracks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `genre` int(11) NOT NULL,
  `name` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `artist` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user` int(11) NOT NULL,
  `file` text COLLATE utf8_unicode_ci NOT NULL,
  `uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `repository` int(11) NOT NULL,
  `runtime` double DEFAULT '2.5',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_tracks` (`name`,`artist`,`repository`),
  KEY `genre` (`genre`),
  KEY `user` (`user`)
) ENGINE=MyISAM AUTO_INCREMENT=406 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I added constraint afterward:

ALTER TABLE mf_tracks
ADD CONSTRAINT uc_tracks UNIQUE (name,artist, file);

mysql> describe mf_tracks;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| genre      | int(11)      | NO   | MUL | NULL              |                             |
| name       | varchar(150) | YES  | MUL | NULL              |                             |
| artist     | varchar(150) | YES  |     | NULL              |                             |
| user       | int(11)      | NO   | MUL | NULL              |                             |
| file       | text         | NO   |     | NULL              |                             |
| uploaded   | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| repository | int(11)      | NO   |     | NULL              |                             |
| runtime    | double       | YES  |     | 2.5               |                             |
+------------+--------------+------+-----+-------------------+-----------------------------+
9 rows in set (0.00 sec)

Regards, Sam

Are you sure you were able to that another UNIQUE constraint? This is what I get (file is a text column!):

mysql> ALTER TABLE mf_tracks
    -> ADD CONSTRAINT uc_tracks2 UNIQUE (name,artist, file);
ERROR 1170 (42000): BLOB/TEXT column 'file' used in key specification without a
key length

What we need is a complete, repeatable test case to just copy/paste, not guess what exactly you did.

This is * exactly * what i ran:

mysql> INSERT INTO mf_tracks(name, artist, file, genre, user, repository)
    -> VALUES ("01 Closer", "Kings of Leon", "01 Closer - Kings of Leon.mp3", 1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mf_tracks(name, artist, file, genre, user, repository)
    -> VALUES ("01 Closer", "Kings of Leon", "01 Closer - Kings of Leon.mp3", 1, 1, 1);
ERROR 1062 (23000): Duplicate entry '01 Closer-Kings of Leon-1' for key 'uc_tracks'

mysql> INSERT IGNORE INTO mf_tracks(name, artist, file, genre, user, repository)  VALUES ("01 Closer", "Kings of Leon", "01 Closer - Kings of Leon.mp3", 1, 1, 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

Should a warning be raised? With IGNORE there is nothing wrong if a duplicate is skipped. This is expected and basically the idea of IGNORE I think.

Peter
(not a MySQL person)

Documentation (
http://dev.mysql.com/doc/refman//5.5/en/insert.html ), states:

"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued."

Where does it say that a warning be be returned when skipping a duplicate row? I don't see that in the quote.

"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead"

Valeriy Kravchuk, is this enough for you to replicate the error?

Regards, Sam

OK, this is easy to verify with a very simple test case:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tunique(c1 int unique);
Query OK, 0 rows affected (0.58 sec)

mysql> insert into tunique values(1);
Query OK, 1 row affected (0.20 sec)

mysql> insert into tunique values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'c1'
mysql> insert ignore into tunique values(1);
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings\G
Empty set (0.02 sec)

But, on the other hand, than same manual page, http://dev.mysql.com/doc/refman//5.5/en/insert.html, lists all cases when (any?) INSERT can produce a warning, and duplicate key is not listed there.

So, I think this is a request for a more clear explanation of intended behavior of INSERT IGNORE in case of duplicate key.

I think this is more a matter of unique constraint violation?

The documentation clearly states

http://dev.mysql.com/doc/refman

"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. "

A constraint violation is an error.  Thus, with "IGNORE", according to the above, should be "treated as a warning instead".

Regards, Sam

Valeriy Kravchuk, is there any update on this?

Regards, Sam Wootton

I push this bug, cause i get the same one with the mysql version: 5.1.67

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

This is a bug, but until such time as it may be fixed, I'll document this case as an exception:

If you use the IGNORE keyword, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row still is not inserted, but no error occurs. Ignored
errors may generate warnings instead, although duplicate-key errors
do not.

Hi Paul,

Many thanks for detailing the change.  Could you let me know when / where this will be published?.

Are there any plans to revert your proposed change to the documentation and raise warnings with conflicts, in the future?

Regards, Sam

sam, the changed wording is published on the page for which you originally reported the issue:

http://dev.mysql.com/doc/refman//5.5/en/insert.html

Regarding future plans, I cannot comment.