Strange pt-online-schema-tookit behaviour

Polyglot Projects Percona Toolkit
1
  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

  1. pt-osc creates temporary table, shows “Created new table database_name._user_new OK.” and hangs.

  2. 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…

  1. 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)

  1. 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.idCREATE 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.usersSwapped original and new tables OK.Dropping old table…DROP TABLE IF EXISTS database_name._users_oldDropped old table database_name._users_old OK.Dropping triggers…DROP TRIGGER IF EXISTS database_name.pt_osc_database_name_users_del;DROP TRIGGER IF EXISTS database_name.pt_osc_database_name_users_upd;DROP TRIGGER IF EXISTS database_name.pt_osc_database_name_users_ins;Dropped triggers OK.Successfully altered database_name.users.

Martin Arrieta
@martinarrietac