Pt-online-schema-change loses data from unaltered primary key column `id`

It seems that pt-online-schema-change can’t handle certain kinds of tables. The content of one column is lost entirely and since it’s part of the primary key some rows are lost as well.

I’m running /usr/bin/pt-online-schema-change --alter="DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci" --no-check-plan D=global,t=_views_display on the table below:

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.'

This gives the following warning although the columns are not altered:

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.

The resulting table has only empty strings in the id column and all but one matching row is deleted. Any ideas on how to resolve this?

1 Like

The problem stops occurring when I remove the DEFAULT '' from the id column’s definition. This looks a lot like a bug.

1 Like

Hi, would you mind opening a bug with this information?

Thanks for reporting this

I’m going to file a bug report tomorrow. Initially I just wasn’t sure this isn’t something on my part that I overlooked. :slightly_smiling_face: