Announcement

Announcement Module
Collapse
No announcement yet.

Strange pt-online-schema-tookit behaviour

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strange pt-online-schema-tookit behaviour

    1. I'm doing the following:


    pt-online-schema-change --execute --print --user=username --ask-pass --alter "ADD COLUMN new_column TINYINT(1) DEFAULT NULL" D=database_name,t=user


    2. pt-osc creates temporary table, shows "Created new table database_name._user_new OK." and hangs.

    3. After enabling PTDEBUG I saw following lines in an endless loop:


    ...# TableParser:3274 2253 Checking `database_name`.`_user_new`# TableParser:3278 2253 SHOW TABLES FROM `database_name` LIKE '\_user\_new'# TableParser:3288 2253 Table does not exist...


    4. But mysql actually finds this table:


    mysql> SHOW TABLES FROM `database_name` LIKE '\_user\_new' -> ;+---------------------------------------+| Tables_in_database_name (\_user\_new) |+---------------------------------------+| _user_new |+---------------------------------------+1 row in set (0.03 sec)


    5. How could this happen? Any ideas?

  • #2
    Hi,

    I made a test with your command and it works, maybe you are using an old version of pt-osc. Please try to get the last version and try again.

    Here is my test:


    # pt-online-schema-change --versionpt-online-schema-change 2.1.7# pt-online-schema-change --execute --print --socket=/backup/mysql.sock --user=root --ask-pass --alter "ADD COLUMN new_column TINYINT(1) DEFAULT NULL" D=database_name,t=usersEnter MySQL password: Altering `database_name`.`users`...Creating new table...CREATE TABLE `database_name`.`_users_new` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`)) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1Created new table database_name._users_new OK.Altering new table...ALTER TABLE `database_name`.`_users_new` ADD COLUMN new_column TINYINT(1) DEFAULT NULLAltered `database_name`.`_users_new` OK.Creating triggers...CREATE TRIGGER `pt_osc_database_name_users_del` AFTER DELETE ON `database_name`.`users` FOR EACH ROW DELETE IGNORE FROM `database_name`.`_users_new` WHERE `database_name`.`_users_new`.`id` <=> OLD.`id`CREATE TRIGGER `pt_osc_database_name_users_upd` AFTER UPDATE ON `database_name`.`users` FOR EACH ROW REPLACE INTO `database_name`.`_users_new` (`id`, `name`, `countrycode`, `district`, `population`) VALUES (NEW.`id`, NEW.`name`, NEW.`countrycode`, NEW.`district`, NEW.`population`)CREATE TRIGGER `pt_osc_database_name_users_ins` AFTER INSERT ON `database_name`.`users` FOR EACH ROW REPLACE INTO `database_name`.`_users_new` (`id`, `name`, `countrycode`, `district`, `population`) VALUES (NEW.`id`, NEW.`name`, NEW.`countrycode`, NEW.`district`, NEW.`population`)Created triggers OK.Copying approximately 4321 rows...INSERT LOW_PRIORITY IGNORE INTO `database_name`.`_users_new` (`id`, `name`, `countrycode`, `district`, `population`) SELECT `id`, `name`, `countrycode`, `district`, `population` FROM `database_name`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) /*pt-online-schema-change 11105 copy nibble*/SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `database_name`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/Copied rows OK.Swapping tables...RENAME TABLE `database_name`.`users` TO `database_name`.`_users_old`, `database_name`.`_users_new` TO `database_name`.`users`Swapped original and new tables OK.Dropping old table...DROP TABLE IF EXISTS `database_name`.`_users_old`Dropped old table `database_name`.`_users_old` OK.Dropping triggers...DROP TRIGGER IF EXISTS `database_name`.`pt_osc_database_name_users_del`;D ROP TRIGGER IF EXISTS `database_name`.`pt_osc_database_name_users_upd`;D ROP TRIGGER IF EXISTS `database_name`.`pt_osc_database_name_users_ins`;D ropped triggers OK.Successfully altered `database_name`.`users`.


    Martin Arrieta
    @martinarrietac

    Comment

    Working...
    X