Paste P7804

schema change T85757
ActivePublic

Authored by Banyek on Wed, Nov 14, 11:24 AM.
root@cumin2001:/home/banyek# PYTHONPATH=/home/banyek/wmfmariadbpy/ ./wmfmariadbpy/wmfmariadbpy/osc_host.py --host db2046 --dblist mediawiki-config/dblists/s6.dblist --table user --no-replicate "CHANGE COLUMN user_options _user_options_drop blob NOT NULL"
Host : db2046
Port : 3306
Databases : ['frwiki', 'jawiki', 'ruwiki']
Table : user
Alter SQL : CHANGE COLUMN user_options _user_options_drop blob NOT NULL
method : percona
pt dry args : ['--recurse=0', '--set-vars=sql_log_bin=off', '--check-slave-lag=db2046']
pt args : ['--recurse=0', '--set-vars=sql_log_bin=off', '--check-slave-lag=db2046']
ddl args : ['SET SESSION innodb_lock_wait_timeout=1;', 'SET SESSION lock_wait_timeout=60;', 'set session sql_log_bin=0;']
analyze : False
continue? yes/no yes
host: db2046, database: frwiki
WARNING frwiki : user encountered problems
continue? yes/no no
abort
Banyek created this paste.Wed, Nov 14, 11:24 AM
Created new table frwiki._user_new OK.
Altering new table...
ALTER TABLE `frwiki`.`_user_new` CHANGE COLUMN user_options _user_options_drop blob NOT NULL
Altered `frwiki`.`_user_new` OK.
Not creating triggers because this is a dry run.
CREATE TRIGGER `pt_osc_frwiki_user_del` AFTER DELETE ON `frwiki`.`user` FOR EACH ROW DELETE IGNORE FROM `frwiki`.`_user_new` WHERE `frwiki`.`_user_new`.`user_id` <=> OLD.`user_id`
CREATE TRIGGER `pt_osc_frwiki_user_upd` AFTER UPDATE ON `frwiki`.`user` FOR EACH ROW REPLACE INTO `frwiki`.`_user_new` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_email`, `_user_options_drop`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_newpass_time`, `user_editcount`, `user_password_expires`) VALUES (NEW.`user_id`, NEW.`user_name`, NEW.`user_real_name`, NEW.`user_password`, NEW.`user_newpassword`, NEW.`user_email`, NEW.`user_options`, NEW.`user_touched`, NEW.`user_token`, NEW.`user_email_authenticated`, NEW.`user_email_token`, NEW.`user_email_token_expires`, NEW.`user_registration`, NEW.`user_newpass_time`, NEW.`user_editcount`, NEW.`user_password_expires`)
CREATE TRIGGER `pt_osc_frwiki_user_ins` AFTER INSERT ON `frwiki`.`user` FOR EACH ROW REPLACE INTO `frwiki`.`_user_new` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_email`, `_user_options_drop`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_newpass_time`, `user_editcount`, `user_password_expires`) VALUES (NEW.`user_id`, NEW.`user_name`, NEW.`user_real_name`, NEW.`user_password`, NEW.`user_newpassword`, NEW.`user_email`, NEW.`user_options`, NEW.`user_touched`, NEW.`user_token`, NEW.`user_email_authenticated`, NEW.`user_email_token`, NEW.`user_email_token_expires`, NEW.`user_registration`, NEW.`user_newpass_time`, NEW.`user_editcount`, NEW.`user_password_expires`)
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `frwiki`.`_user_new` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_email`, `_user_options_drop`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_newpass_time`, `user_editcount`, `user_password_expires`) SELECT `user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_email`, `user_options`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_newpass_time`, `user_editcount`, `user_password_expires` FROM `frwiki`.`user` FORCE INDEX(`PRIMARY`) WHERE ((`user_id` >= ?)) AND ((`user_id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 23724 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `user_id` FROM `frwiki`.`user` FORCE INDEX(`PRIMARY`) WHERE ((`user_id` >= ?)) ORDER BY `user_id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `frwiki`.`pt_osc_frwiki_user_del`;
DROP TRIGGER IF EXISTS `frwiki`.`pt_osc_frwiki_user_upd`;
DROP TRIGGER IF EXISTS `frwiki`.`pt_osc_frwiki_user_ins`;
2018-11-14T11:45:53 Dropping new table...
DROP TABLE IF EXISTS `frwiki`.`_user_new`;
2018-11-14T11:45:53 Dropped new table OK.
Dry run complete.  `frwiki`.`user` was not altered.
No slaves found.  See --recursion-method if host db2046 has slaves.
Will check slave lag on:
  db2046
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
Not updating foreign keys because --alter-foreign-keys-method=none.  Foreign keys that reference the table will no longer work.
Altering `frwiki`.`user`...
--alter appears to rename these columns:
  user_options to _user_options_drop
The tool should handle this correctly, but you should test it first because if it fails the renamed columns' data will be lost!  Specify --no-check-alter to disable this check and perform the --alter.
`frwiki`.`user` was not altered.
--check-alter failed.
WARNING frwiki : user encountered problems
root@cumin2001:/home/banyek# PYTHONPATH=/home/banyek/wmfmariadbpy/ ./wmfmariadbpy/wmfmariadbpy/osc_host.py --method=ddl --host db2046.codfw.wmnet --dblist mediawiki-config/dblists/s6.dblist --table user --no-replicate --debug "CHANGE COLUMN user_options _user_options_drop blob NOT NULL"
Host        : db2046.codfw.wmnet
Port        : 3306
Databases   : ['frwiki', 'jawiki', 'ruwiki']
Table       : user
Alter SQL   : CHANGE COLUMN user_options _user_options_drop blob NOT NULL
method      : ddl
Connected to db2046.codfw.wmnet:3306/(none)
Executing 'show slave status'
pt dry args : ['--recurse=0', '--set-vars=sql_log_bin=off', '--check-slave-lag=db2046.codfw.wmnet']
pt args     : ['--recurse=0', '--set-vars=sql_log_bin=off', '--check-slave-lag=db2046.codfw.wmnet']
ddl args    : ['SET SESSION innodb_lock_wait_timeout=1;', 'SET SESSION lock_wait_timeout=60;', 'set session sql_log_bin=0;']
analyze     : False
continue? yes/no yes
Changed database to 'frwiki'
host: db2046.codfw.wmnet, database: frwiki
SET SESSION innodb_lock_wait_timeout=1;
Executing 'SET SESSION innodb_lock_wait_timeout=1;'
SET SESSION lock_wait_timeout=60;
Executing 'SET SESSION lock_wait_timeout=60;'
set session sql_log_bin=0;
Executing 'set session sql_log_bin=0;'
alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL
Executing 'alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL'
Changed database to 'jawiki'
host: db2046.codfw.wmnet, database: jawiki
SET SESSION innodb_lock_wait_timeout=1;
Executing 'SET SESSION innodb_lock_wait_timeout=1;'
SET SESSION lock_wait_timeout=60;
Executing 'SET SESSION lock_wait_timeout=60;'
set session sql_log_bin=0;
Executing 'set session sql_log_bin=0;'
alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL
Executing 'alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL'
Changed database to 'ruwiki'
host: db2046.codfw.wmnet, database: ruwiki
SET SESSION innodb_lock_wait_timeout=1;
Executing 'SET SESSION innodb_lock_wait_timeout=1;'
SET SESSION lock_wait_timeout=60;
Executing 'SET SESSION lock_wait_timeout=60;'
set session sql_log_bin=0;
Executing 'set session sql_log_bin=0;'
alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL
Executing 'alter table `user` CHANGE COLUMN user_options _user_options_drop blob NOT NULL'
Disconnecting from 3306:db2046.codfw.wmnet/ruwiki