Skip to:

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

pt-online-schema-change can't use options port

Description

 

pt-online-schema-change --alter "ADD COLUMN \`c_name\` VARCHAR(50) COMMENT '用户名'" --print --charset=utf8 --chunk-time 0.5 --critical-load Threads_connected:200,Threads_running:200 --max-load Threads_connected:250,Threads_running:250 --recurse=1 --check-interval 1 --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=1 --no-version-check --recursion-method=processlist --progress percentage,1 --user="yearning_admin" --password="****" --host=172.16.0.11 --port=3366 D=sre_test,t=candles

 

The DEBUG message is

 

# OptionParser:921 4190 Description: pt-online-schema-change alters a table's structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully. # Usage: pt-online-schema-change [OPTIONS] DSN # DSNParser:2246 4190 DSN string made from options: A=utf8,P=3366,u=yearning_admin,p=****,h=172.16.0.11 # DSNParser:2186 4190 Parsing A=utf8,P=3366,u=yearning_admin,p=****,h=172.16.0.11 # DSNParser:2205 4190 Finding value for S # DSNParser:2215 4190 Copying value for S from defaults # DSNParser:2205 4190 Finding value for F # DSNParser:2215 4190 Copying value for F from defaults # DSNParser:2205 4190 Finding value for A # DSNParser:2205 4190 Finding value for P # DSNParser:2205 4190 Finding value for u # DSNParser:2205 4190 Finding value for p # DSNParser:2205 4190 Finding value for h # DSNParser:2205 4190 Finding value for D # DSNParser:2215 4190 Copying value for D from defaults # DSNParser:2205 4190 Finding value for t # DSNParser:2215 4190 Copying value for t from defaults # DSNParser:2299 4190 DBI:mysql:sre_test;host=172.16.0.11;charset=utf8;mysql_read_default_group=client # DSNParser:2348 4190 DBI:mysql:sre_test;host=172.16.0.11;charset=utf8;mysql_read_default_group=client yearning_admin **** mysql_enable_utf8=>1, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0 # DSNParser:2348 4190 DBI:mysql:sre_test;host=172.16.0.11;charset=utf8;mysql_read_default_group=client yearning_admin **** mysql_enable_utf8=>1, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0 Cannot connect to MySQL: DBI connect('sre_test;host=172.16.0.11;charset=utf8;mysql_read_default_group=client','yearning_admin',...) failed: Can't connect to MySQL server on '172.16.0.11' (111) at /usr/bin/pt-online-schema-change line 2350.

 

Then I change the options like this

 

pt-online-schema-change --alter "ADD COLUMN \`c_name\` VARCHAR(50) COMMENT '用户名'" --print --charset=utf8 --chunk-time 0.5 --critical-load Threads_connected:200,Threads_running:200 --max-load Threads_connected:250,Threads_running:250 --recurse=1 --check-interval 1 --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=1 --no-version-check --recursion-method=processlist --progress percentage,1 --user="yearning_admin" --password="****" --host=172.16.0.11 D=sre_test,t=candles,P=3366

 

It's OK,and the DEBUG message is

  1. # OptionParser:921 4211 Description: pt-online-schema-change alters a table's structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

  2. # Usage: pt-online-schema-change [OPTIONS] DSN

  3. # DSNParser:2246 4211 DSN string made from options: A=utf8,u=yearning_admin,p=****,h=172.16.0.11

  4. # DSNParser:2186 4211 Parsing A=utf8,u=yearning_admin,p=****,h=172.16.0.11

  5. # DSNParser:2205 4211 Finding value for S

  6. # DSNParser:2215 4211 Copying value for S from defaults

  7. # DSNParser:2205 4211 Finding value for F

  8. # DSNParser:2215 4211 Copying value for F from defaults

  9. # DSNParser:2205 4211 Finding value for A

  10. # DSNParser:2205 4211 Finding value for P

  11. # DSNParser:2215 4211 Copying value for P from defaults

  12. # DSNParser:2205 4211 Finding value for u

  13. # DSNParser:2205 4211 Finding value for p

  14. # DSNParser:2205 4211 Finding value for h

  15. # DSNParser:2205 4211 Finding value for D

  16. # DSNParser:2215 4211 Copying value for D from defaults

  17. # DSNParser:2205 4211 Finding value for t

  18. # DSNParser:2215 4211 Copying value for t from defaults

  19. # DSNParser:2299 4211 DBI:mysql:sre_test;host=172.16.0.11;port=3366;charset=utf8;mysql_read_default_group=client

  20. # DSNParser:2348 4211 DBI:mysql:sre_test;host=172.16.0.11;port=3366;charset=utf8;mysql_read_default_group=client yearning_admin ***** mysql_enable_utf8=>1, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0

  21. # DSNParser:2377 4211 DBI::db=HASH(0x17c4858) /!40101 SET NAMES "utf8"/ # DSNParser:2382 4211 Enabling charset for STDOUT

  22. # DSNParser:2529 4211 DBI::db=HASH(0x17c4858) SET SESSION innodb_lock_wait_timeout=1

  23. # DSNParser:2529 4211 DBI::db=HASH(0x17c4858) SET SESSION lock_wait_timeout=60

  24. # DSNParser:2529 4211 DBI::db=HASH(0x17c4858) SET SESSION wait_timeout=10000

  25. # DSNParser:2397 4211 DBI::db=HASH(0x17c4858) SELECT @@SQL_MODE

Environment

None

Attachments

2
  • 23 Nov 2020, 04:20 AM
  • 23 Nov 2020, 04:20 AM

Smart Checklist

Activity

Show:

Donovan Hubbard May 19, 2023 at 3:56 PM

This bug still exists in version 3.5.1

~ pt-online-schema-change --version pt-online-schema-change 3.5.1 ~ pt-online-schema-change -h "192.168.64.2" -P 4444 -u root --ask-pass  --execute D=foo,t=bar Enter MySQL password: Cannot connect to MySQL: DBI connect('foo;host=192.168.64.2;mysql_read_default_group=client','root',...) failed: Can't connect to MySQL server on '192.168.64.2:3306' (61) at /usr/local/Cellar/percona-toolkit/3.5.1/libexec/bin/pt-online-schema-change line 2345.

binger November 23, 2020 at 1:50 PM

Ok.Thinks.Wish all the best wishes for you.

Lalit Choudhary November 23, 2020 at 1:29 PM

For now, as a workaround use port as follows in your pt-online-schema-changes command,
--host=172.16.0.11 D=sre_test,t=candles,P=3366
Will fix this bug in the Percona toolkit,  and you can use the fixed version pt-osc  binary in the toolkit you are using (like Yearning)

Till the time we are fixing this use the workaround method to mentioned mysql port. 

binger November 23, 2020 at 1:01 PM

For safety,I have change mysql port. And some toolkit,like [Yearning|https://github.com/cookieY/Yearning   ,used pt-osc. Can you repair this issue? Think you.|https://github.com/cookieY/Yearning],using]

Lalit Choudhary November 23, 2020 at 11:30 AM

Hi

Thank you for the report.

Verified as described.

Issue reproducible with --port option when using non-default port, with 3306 port it works fine.

 

Test:

--port=3306  (Working when using default mysql port)

 

percona-toolkit-3.2.1/bin$ ./pt-online-schema-change --alter "ADD COLUMN \`c1_name\` VARCHAR(50) COMMENT '用户名'" --print --charset=utf8 --chunk-time 0.5 --critical-load Threads_connected:200,Threads_running:200 --max-load Threads_connected:250,Threads_running:250 --recurse=1 --check-interval 1 --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=1 --no-version-check --recursion-method=processlist --progress percentage,1 --user="remote" --password="Msandbox@123" --host=192.168.0.105 --port=3306 D=test,t=t11 No slaves found. See --recursion-method if host testos 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 No foreign keys reference `test`.`t11`; ignoring --alter-foreign-keys-method. Altering `test`.`t11`... Creating new table... CREATE TABLE `test`.`_t11_new` ( `c1` int(11) NOT NULL, `c2` char(20) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Created new table test._t11_new OK. Altering new table... ' Altered `test`.`_t11_new` OK. ADD COLUMN `c1_name` VARCHAR(50) COMMENT '用æ·å 2020-11-23T16:28:05 Creating triggers... 2020-11-23T16:28:05 Created triggers OK. 2020-11-23T16:28:05 Copying approximately 1 rows... INSERT LOW_PRIORITY IGNORE INTO `test`.`_t11_new` (`c1`, `c2`) SELECT `c1`, `c2` FROM `test`.`t11` LOCK IN SHARE MODE /*pt-online-schema-change 991 copy table*/ 2020-11-23T16:28:06 Copied rows OK. 2020-11-23T16:28:06 Analyzing new table... 2020-11-23T16:28:06 Swapping tables... RENAME TABLE `test`.`t11` TO `test`.`_t11_old`, `test`.`_t11_new` TO `test`.`t11` 2020-11-23T16:28:06 Swapped original and new tables OK. 2020-11-23T16:28:06 Dropping old table... DROP TABLE IF EXISTS `test`.`_t11_old` 2020-11-23T16:28:06 Dropped old table `test`.`_t11_old` OK. 2020-11-23T16:28:06 Dropping triggers... DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t11_del` DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t11_upd` DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t11_ins` 2020-11-23T16:28:06 Dropped triggers OK. # Event Count # ====== ===== # INSERT 1 Successfully altered `test`.`t11`. percona-toolkit-3.2.1/bin$ mysql -h 192.168.0.105 -u remote -p -e 'desc test.t11;' Enter password: +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | char(20) | YES | | NULL | | | c1_name | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+

 

 

changed mysqld port to 3306:

percona-toolkit-3.2.1/bin$ ./pt-online-schema-change --alter "ADD COLUMN \`c2_name\` VARCHAR(50) COMMENT '用户名'" --print --charset=utf8 --chunk-time 0.5 --critical-load Threads_connected:200,Threads_running:200 --max-load Threads_connected:250,Threads_running:250 --recurse=1 --check-interval 1 --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=1 --no-version-check --recursion-method=processlist --progress percentage,1 --user="remote" --password="Msandbox@123" --host=192.168.0.105 --port=3366 D=test,t=t11 Cannot connect to MySQL: DBI connect('test;host=192.168.0.105;charset=utf8;mysql_read_default_group=client','remote',...) failed: Can't connect to MySQL server on '192.168.0.105' (111) at ./pt-online-schema-change line 2345.

No issue for mysql connectivity with mysql client.

percona-toolkit-3.2.1/bin$ mysql -h 192.168.0.105 -u remote -p --port=3366 -e 'desc test.t11;' Enter password: +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | char(20) | YES | | NULL | | | c1_name | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+

PTDEBUG:

 

$ PTDEBUG=1 ./pt-online-schema-change --alter "ADD COLUMN \`c2_name\` VARCHAR(50) COMMENT '用户名'" --print --charset=utf8 --chunk-time 0.5 --critical-load Threads_connected:200,Threads_running:200 --max-load Threads_connected:250,Threads_running:250 --recurse=1 --check-interval 1 --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=1 --no-version-check --recursion-method=processlist --progress percentage,1 --user="remote" --password="Msandbox@123" --host=192.168.0.105 --port=3366 D=test,t=t11 # OptionParser:919 18909 Description: pt-online-schema-change alters a table's structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully. # Usage: pt-online-schema-change [OPTIONS] DSN # DSNParser:2241 18909 DSN string made from options: p=Msandbox@123,A=utf8,u=remote,P=3366,h=192.168.0.105 # DSNParser:2181 18909 Parsing p=Msandbox@123,A=utf8,u=remote,P=3366,h=192.168.0.105 # DSNParser:2200 18909 Finding value for D # DSNParser:2210 18909 Copying value for D from defaults # DSNParser:2200 18909 Finding value for S # DSNParser:2210 18909 Copying value for S from defaults # DSNParser:2200 18909 Finding value for t # DSNParser:2210 18909 Copying value for t from defaults # DSNParser:2200 18909 Finding value for p # DSNParser:2200 18909 Finding value for A # DSNParser:2200 18909 Finding value for F # DSNParser:2210 18909 Copying value for F from defaults # DSNParser:2200 18909 Finding value for u # DSNParser:2200 18909 Finding value for P # DSNParser:2200 18909 Finding value for h # DSNParser:2294 18909 DBI:mysql:test;host=192.168.0.105;charset=utf8;mysql_read_default_group=client # DSNParser:2343 18909 DBI:mysql:test;host=192.168.0.105;charset=utf8;mysql_read_default_group=client remote Msandbox@123 mysql_enable_utf8=>1, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0 # DSNParser:2343 18909 DBI:mysql:test;host=192.168.0.105;charset=utf8;mysql_read_default_group=client remote Msandbox@123 mysql_enable_utf8=>1, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0 Cannot connect to MySQL: DBI connect('test;host=192.168.0.105;charset=utf8;mysql_read_default_group=client','remote',...) failed: Can't connect to MySQL server on '192.168.0.105' (111) at ./pt-online-schema-change line 2345. # Cxn:4122 18909 Destroying cxn

 

Done

Details

Assignee

Reporter

Priority

Components

Affects versions

Fix versions

Labels

Smart Checklist

Created November 23, 2020 at 4:20 AM
Updated June 12, 2024 at 5:29 PM
Resolved March 15, 2024 at 11:09 PM