Skip to:

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

pt-osc loses data from unaltered primary key column

Description

When I run an alter query that doesn't affect any keys or constraints using pt-osc on the following table. It loses all data in the id-column (replaced with the empty string as default value) and some rows that are "duplicates" get lost as well.

Here is the table definition used.

CREATE TABLE `views_display` ( `vid` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'The view this display is attached to.', `id` varchar(64) NOT NULL DEFAULT '' COMMENT 'An identifier for this display; usually generated from the display_plugin, so should be something like page or page_1 or block_2, etc.', `display_title` varchar(64) NOT NULL DEFAULT '' COMMENT 'The title of the display, viewable by the administrator.', `display_plugin` varchar(64) NOT NULL DEFAULT '' COMMENT 'The type of the display. Usually page, block or embed, but is pluggable so may be other things.', `position` int(11) DEFAULT 0 COMMENT 'The order in which this display is loaded.', `display_options` longtext DEFAULT NULL COMMENT 'A serialized array of options for this display; it contains options that are generally only pertinent to that display plugin type.', PRIMARY KEY (`vid`,`id`), KEY `vid` (`vid`,`position`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED COMMENT='Stores information about each display attached to a view.'

During the execution I also get a warning:
Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because the new table index uses column id which does not exist in the original table.

Removing the DEFAULT '' from the the id-column solved the problem.
 

Environment

None

Attachments

1
  • 01 Apr 2022, 06:48 AM

Smart Checklist

Activity

Show:

Aaditya Dubey April 13, 2022 at 9:59 AM

Hi ,

Thank you for the updates.
I was able to reproduce the issue with https://perconadev.atlassian.net/browse/PT-3#icft=PT-3.2.1, however issue is not repeatable with the latest version of https://perconadev.atlassian.net/browse/PT-3#icft=PT-3.3.1, please find my test case below

When testing with PT version : 3.2.1 $ ./pt-online-schema-change --user='xxxx' --password='xxxx' --socket=/tmp/mysql_sandbox8027.sock --execute --alter="DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci" --no-check-plan D=test,t=_views_display No slaves found.  See --recursion-method if host xxxx has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait:   analyze_table, 10, 1   copy_rows, 10, 0.25   create_triggers, 10, 1   drop_triggers, 10, 1   swap_tables, 10, 1   update_foreign_keys, 10, 1 Altering `test`.`_views_display`... Creating new table... Created new table test.__views_display_new OK. Altering new table... Altered `test`.`__views_display_new` OK. Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because the new table index uses column id which does not exist in the original table. 2022-04-13T05:27:30 Creating triggers... 2022-04-13T05:27:30 Created triggers OK. 2022-04-13T05:27:30 Copying approximately 18 rows... 2022-04-13T05:27:30 Copied rows OK. 2022-04-13T05:27:30 Analyzing new table... 2022-04-13T05:27:30 Swapping tables... 2022-04-13T05:27:30 Swapped original and new tables OK. 2022-04-13T05:27:30 Dropping old table... 2022-04-13T05:27:30 Dropped old table `test`.`__views_display_old` OK. 2022-04-13T05:27:30 Dropping triggers... 2022-04-13T05:27:30 Dropped triggers OK. Successfully altered `test`.`_views_display`. Before PT table data count: mysql [localhost:8027] {msandbox} (test) > select * from _views_display; +-----+---------+--------------------------+----------------+----------+---------+ | vid | id      | display_title            | display_plugin | position | address | +-----+---------+--------------------------+----------------+----------+---------+ |   1 |         | Mitmachen Block          | block          |        2 | NULL    | |   7 |         | Themen und Knoten        | block          |        2 | NULL    | |  11 |         | Block                    | block          |        2 | NULL    | |  14 |         | Master                   | default        |        1 | NULL    | |  15 |         | Master                   | default        |        1 | NULL    | |  16 |         | Master                   | default        |        1 | NULL    | |  20 | default | Master                   | default        |        1 | NULL    | |  20 | page_1  | Seite                    | page           |        2 | NULL    | |  21 | block   | Block Publikationen      | block          |        3 | NULL    | |  21 | block_1 | Block Neuigkeiten        | block          |        5 | NULL    | |  21 | block_2 | Block Presseaussendungen | block          |        7 | NULL    | |  21 | block_3 | Block Publikationen full | block          |        8 | NULL    | |  21 | block_4 | Block Neuigkeiten full   | block          |        9 | NULL    | |  21 | block_5 | Block Presse full        | block          |       10 | NULL    | |  21 | default | Master                   | default        |        1 | NULL    | |  21 | page    | Publikationen            | page           |        2 | NULL    | |  21 | page_1  | Neuigkeiten              | page           |        4 | NULL    | |  21 | page_2  | Presseaussendungen       | page           |        6 | NULL    | +-----+---------+--------------------------+----------------+----------+---------+ 18 rows in set (0.00 sec) After PT-OSC run table data count ~ [Rows Deleted] mysql [localhost:8027] {msandbox} (test) > select * from _views_display; +-----+----+---------------------+----------------+----------+---------+ | vid | id | display_title       | display_plugin | position | address | +-----+----+---------------------+----------------+----------+---------+ |   1 |    | Mitmachen Block     | block          |        2 | NULL    | |   7 |    | Themen und Knoten   | block          |        2 | NULL    | |  11 |    | Block               | block          |        2 | NULL    | |  14 |    | Master              | default        |        1 | NULL    | |  15 |    | Master              | default        |        1 | NULL    | |  16 |    | Master              | default        |        1 | NULL    | |  20 |    | Master              | default        |        1 | NULL    | |  21 |    | Block Publikationen | block          |        3 | NULL    | +-----+----+---------------------+----------------+----------+---------+ 8 rows in set (0.01 sec) When testing with PT version : 3.3.1 $ ./pt-online-schema-change --user='xxxx' --password='xxxx' --socket=/tmp/mysql_sandbox8027.sock --execute --alter="DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci" --no-check-plan D=test,t=_views_display No slaves found.  See --recursion-method if host xxxx has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait:   analyze_table, 10, 1   copy_rows, 10, 0.25   create_triggers, 10, 1   drop_triggers, 10, 1   swap_tables, 10, 1   update_foreign_keys, 10, 1 Altering `test`.`_views_display`... Creating new table... Created new table test.__views_display_new OK. Altering new table... Altered `test`.`__views_display_new` OK. 2022-04-13T03:20:20 Creating triggers... 2022-04-13T03:20:20 Created triggers OK. 2022-04-13T03:20:20 Copying approximately 18 rows... 2022-04-13T03:20:20 Copied rows OK. 2022-04-13T03:20:20 Analyzing new table... 2022-04-13T03:20:20 Swapping tables... 2022-04-13T03:20:20 Swapped original and new tables OK. 2022-04-13T03:20:20 Dropping old table... 2022-04-13T03:20:20 Dropped old table `test`.`__views_display_old` OK. 2022-04-13T03:20:20 Dropping triggers... 2022-04-13T03:20:20 Dropped triggers OK. Successfully altered `test`.`_views_display`. No row deletion before and after pt-osc run mysql [localhost:8027] {root} (test) > select * from _views_display; +-----+---------+--------------------------+----------------+----------+ | vid | id      | display_title            | display_plugin | position | +-----+---------+--------------------------+----------------+----------+ |   1 |         | Mitmachen Block          | block          |        2 | |   7 |         | Themen und Knoten        | block          |        2 | |  11 |         | Block                    | block          |        2 | |  14 |         | Master                   | default        |        1 | |  15 |         | Master                   | default        |        1 | |  16 |         | Master                   | default        |        1 | |  20 | default | Master                   | default        |        1 | |  20 | page_1  | Seite                    | page           |        2 | |  21 | block   | Block Publikationen      | block          |        3 | |  21 | block_1 | Block Neuigkeiten        | block          |        5 | |  21 | block_2 | Block Presseaussendungen | block          |        7 | |  21 | block_3 | Block Publikationen full | block          |        8 | |  21 | block_4 | Block Neuigkeiten full   | block          |        9 | |  21 | block_5 | Block Presse full        | block          |       10 | |  21 | default | Master                   | default        |        1 | |  21 | page    | Publikationen            | page           |        2 | |  21 | page_1  | Neuigkeiten              | page           |        4 | |  21 | page_2  | Presseaussendungen       | page           |        6 | +-----+---------+--------------------------+----------------+----------+ 18 rows in set (0.01 sec)

 Please use the latest version of PT which can be found here at https://www.percona.com/downloads/percona-toolkit/LATEST/

Roman April 13, 2022 at 5:35 AM

Sorry, still have to find my way around JIRA

pt-online-schema-change --alter="DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci" --no-check-plan D=global,t=_views_display

Roman April 13, 2022 at 5:34 AM

Hi!

Sorry forgot to post that here. The command used is:

{{pt-online-schema-change --alter="DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci" --no-check-plan D=global,t=_views_display }}

Aaditya Dubey April 12, 2022 at 6:18 PM

Hi ,

Thank you for the report.
We required ALTER statement which is causing the issue, please share the same to further debug the issue.

Roman April 1, 2022 at 6:50 AM

I’m attaching a slighlty simplified export of the table with some content to give you an easy way to reproduce this. Before running pt-osc this table has 18 rows, which are reduced to 8 afterwards.

Done

Details

Assignee

Reporter

Priority

Components

Affects versions

Smart Checklist

Created April 1, 2022 at 6:41 AM
Updated February 29, 2024 at 8:47 PM
Resolved April 13, 2022 at 9:59 AM