Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #1099836: pt-online-schema-change fails with "Duplicate entry" on MariaDB

Description

**Reported in Launchpad by yetanotherape last update 15-02-2013 17:10:54

Tool version: pt-online-schema-change 2.1.8
Command: pt-online-schema-change --alter "DROP COLUMN test_column" --nodrop-old-table --execute u=root,D=test,t=test_table
MariaDB version: mysql Ver 15.1 Distrib 5.5.28-MariaDB, for Linux (x86_64) using readline 5.1

Run command on server that is in use.
Output:
...
`test`.`test_table` was not altered.
(in cleanup) Error copying rows from `test`.`test_table` to `test`.`_test_table_new`: Copying rows caused a MySQL error 1062:
Level: Warning
Code: 1062
Message: Duplicate entry '622374' for key 'PRIMARY'
Query: INSERT LOW_PRIORITY IGNORE INTO ...

It seems that MariaDB return some warnings while executing SHOW WARNINGS after INSERT IGNORE.
On MySQL (mysql Ver 14.14 Distrib 5.5.24, for debian-linux-gnu (x86_64) using readline 6.2) SHOW WARNINGS return nothing.

Environment

None

Smart Checklist

Activity

lpjirasync January 24, 2018 at 2:19 PM

**Comment from Launchpad by: Daniel Nichter on: 29-01-2013 16:39:20

Indeed, MariaDB fixed this "bug" in 5.5.28:

https://kb.askmonty.org/en/mariadb-5528-changelog/ : "Added warnings for duplicate key errors when using INSERT IGNORE"

I say "bug" because although http://dev.mysql.com/doc/refman/5.5/en/insert.html says "If you use the IGNORE keyword, errors that occur while executing the http://dev.mysql.com/doc/refman/5.5/en/insert.html statement are treated as warnings instead.", even in MySQL 5.5.29 that is not actually the case.

So is it a MySQL or documentation bug? I checked 5.0.96 and it does not generate a warning. Yet, http://dev.mysql.com/doc/refman/4.1/en/insert.html says it's treated as a warning.

So I think we have a clear case here were historical precedence of how things actually working has become the rule, despite equally historical documentation to the contrary.

In any case, Brian is going to try making the tool simply ignore "Duplicate entry" warnings altogether because that's what we intend to do by using INSERT IGNORE. So far, I can't think of why ignoring this warning would cause any problems.

lpjirasync January 24, 2018 at 2:19 PM

**Comment from Launchpad by: Daniel Nichter on: 21-01-2013 16:11:59

That's good to know. I'll set up a MariaDB sandbox and try to reproduce this then. Thanks for the info.

lpjirasync January 24, 2018 at 2:19 PM

**Comment from Launchpad by: yetanotherape on: 21-01-2013 16:03:25

I think that it isn't a bug of MariaDB. I create simple table with primary key in MariaDB, then i try INSERT INTO ... with duplicated id, SHOW WARNINGS returns row with level "Error". And when i try INSERT IGNORE INTO ..., SHOW WARNINGS returns row with level "Warning".
MySQL returns same result in first case, and nothing in second case.
MariaDB seems to return just more verbose data.
It happens consistently for any table and data i've tried.

lpjirasync January 24, 2018 at 2:19 PM

**Comment from Launchpad by: Daniel Nichter on: 21-01-2013 15:07:20

Is this a known MariaDB bug with SHOW WARNINGS or does the query really only produce a warning on MariaDB? Does this happen consistently? for any table and data?

Done

Details

Assignee

Reporter

Priority

Labels

Smart Checklist

Created January 24, 2018 at 2:19 PM
Updated January 24, 2018 at 2:19 PM
Resolved January 24, 2018 at 2:19 PM