Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1167

LP #1246754: pt-online-schema-change loosing data when making non unique column a new PK

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by Przemek last update 31-10-2013 19:14:21

      CREATE TABLE `mytable` (
      `userid` int(10) unsigned NOT NULL DEFAULT '0',
      `time` int(10) unsigned NOT NULL DEFAULT '0',
      `what` enum('paidaccount') DEFAULT NULL,
      `action` enum('new','renew','expire','return','ext') DEFAULT NULL,
      KEY `userid` (`userid`),
      KEY `time` (`time`)
      ) ENGINE=InnoDB

      mysql> select * from mytable;
      --------------------------------+

      userid time what action

      --------------------------------+

      32 2333232 paidaccount new
      32 111 paidaccount new
      32 222 paidaccount ext

      --------------------------------+
      3 rows in set (0.00 sec)

      mysql> alter table mytable add primary key(userid);
      ERROR 1062 (23000): Duplicate entry '32' for key 'PRIMARY'

      mysql> select * from mytable;
      --------------------------------+

      userid time what action

      --------------------------------+

      32 2333232 paidaccount new
      32 111 paidaccount new
      32 222 paidaccount ext

      --------------------------------+
      3 rows in set (0.00 sec)

      [root@lap-prz ~]# pt-online-schema-change --execute p=xxx,D=test,t=mytable --alter "ADD PRIMARY KEY(userid)"
      Operation, tries, wait:
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Altering `test`.`mytable`...
      Creating new table...
      Created new table test._mytable_new OK.
      Altering new table...
      Altered `test`.`_mytable_new` OK.
      2013-10-31T15:39:43 Creating triggers...
      2013-10-31T15:39:43 Created triggers OK.
      2013-10-31T15:39:43 Copying approximately 3 rows...
      2013-10-31T15:39:43 Copied rows OK.
      2013-10-31T15:39:43 Swapping tables...
      2013-10-31T15:39:43 Swapped original and new tables OK.
      2013-10-31T15:39:43 Dropping old table...
      2013-10-31T15:39:43 Dropped old table `test`.`_mytable_old` OK.
      2013-10-31T15:39:43 Dropping triggers...
      2013-10-31T15:39:43 Dropped triggers OK.
      Successfully altered `test`.`mytable`.

      mysql> select * from mytable;
      --------------------------------+

      userid time what action

      --------------------------------+

      32 2333232 paidaccount new

      --------------------------------+
      1 row in set (0.00 sec)

      mysql> show create table mytable\G

                                                          • 1. row ***************************
                                                            Table: mytable
                                                            Create Table: CREATE TABLE `mytable` (
                                                            `userid` int(10) unsigned NOT NULL DEFAULT '0',
                                                            `time` int(10) unsigned NOT NULL DEFAULT '0',
                                                            `what` enum('paidaccount') DEFAULT NULL,
                                                            `action` enum('new','renew','expire','return','ext') DEFAULT NULL,
                                                            PRIMARY KEY (`userid`),
                                                            KEY `userid` (`userid`),
                                                            KEY `time` (`time`)
                                                            ) ENGINE=InnoDB DEFAULT CHARSET=latin1
                                                            1 row in set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Smart Checklist