Pt-online-schema-change: MySQL Warning 1062

Polyglot Projects Percona Toolkit
,
1

I am using pt-online-schema-change 3.6.0 and I ran it like this:

pt-online-schema-change
–user=root
–password=x
–alter “MODIFY COLUMN c decimal(12,2) signed NOT NULL”
–alter-foreign-keys-method=drop_swap
–max-load Threads_running=400
–critical-load Threads_running=1000
–nocheck-foreign-keys
–recursion-method=none
–print
–pause-file=/tmp/percona_pause
–progress=time,10
–set-vars lock_wait_timeout=2
–statistics
D=x,t=y,h=z
–execute

When I ran this in production, the tool finished but I saw this in the statistics:

Event Count
================== =====
INSERT 2830
mysql_warning_1062 6492

The 1062 seems to be duplicate entries. This seems to be triggered at this part of the code:

           if ( !$stats->{"mysql_warning_$code"}++ ) {  # warn once
              warn "Copying rows caused a MySQL error $code: "
                 . ($warn_code{$code}->{message}
                    ? $warn_code{$code}->{message}
                    : $message)
                 . "\nNo more warnings about this MySQL error will be "
                 . "reported.  If --statistics was specified, "
                 . "mysql_warning_$code will list the total count of "
                 . "this MySQL error.\n";

Should I now be concerned that the altered/new table has data integrity issues since the pt-osc reported duplicate errors? Note that when I ran this on a backup instance, I did not see this at all.

2

If you’re not familiar with how pt-osc works, here’s the short version:

  • make new table exactly like original
  • alter new table as you’ve requested
  • add triggers to old table to capture data that is changed by running applications
  • copy rows (SELECT … → INSERT) from old to new
  • when done copying, rename old to oldold, rename new to old
  • drop triggers

What can happen is that while ptosc is processing rows 10,000->20,000, your app updates row 31,334. the trigger on old causes this update to copy to new. no issues. now, ptosc is working on rows 30,000->40,000. Your modified row, 31,334 is already in the new table. but ptosc doesn’t know that and it tries anyway. duplicate key error 1062. this is correct because we don’t want to overwrite the modified row in the new table with the row from old table.

Your data should be fine.

1 Like
3

Thank you very much. This makes sense.