pt-online-schema-change fails with "Duplicate entry" on MariaDB

Reported by yetanotherape on 2013-01-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
High
Brian Fraser

Bug Description

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.

Daniel Nichter (daniel-nichter) wrote :

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?

Changed in percona-toolkit:
status: New → Triaged
yetanotherape (yetanotherape) wrote :

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.

Daniel Nichter (daniel-nichter) wrote :

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

summary: - pt-online-schema-change reprorts duplicate entry
+ pt-online-schema-change fails with "Duplicate entry" on MariaDB
Changed in percona-toolkit:
milestone: none → 2.1.9
importance: Undecided → High
tags: added: duplicate-key mariadb
Brian Fraser (fraserbn) on 2013-01-28
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
Changed in percona-toolkit:
status: Triaged → In Progress
Daniel Nichter (daniel-nichter) wrote :

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.

Brian Fraser (fraserbn) on 2013-01-31
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers