I have narrated what exactly i have done. Please refer the steps below.
Step 1: Create DB
CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
-- testdb.Test_tbl definition
Step 2: Create table
use testdb;
CREATE TABLE `Test_tbl` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Step 3: Execute the toolkit command
PTDEBUG=1 pt-online-schema-change --dry-run --print --alter "ADD COLUMN city varchar(25) DEFAULT NULL" D=testdb,t=Test_tbl,u=root,h=localhost --ask-pass
# DSNParser:2294 153797 DBI:mysql:testdb;host=localhost;mysql_read_default_group=client
# DSNParser:2343 153797 DBI:mysql:testdb;host=localhost;mysql_read_default_group=client root mypassword mysql_enable_utf8=>0, AutoCommit=>1, PrintError=>0, RaiseError=>1, ShowErrorStatement=>1
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826af3d8) SET SESSION innodb_lock_wait_timeout=1
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826af3d8) SET SESSION lock_wait_timeout=60
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826af3d8) SET SESSION wait_timeout=10000
# DSNParser:2392 153797 DBI::db=HASH(0x55f1826af3d8) SELECT @@SQL_MODE
# DSNParser:2402 153797 DBI::db=HASH(0x55f1826af3d8) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:2416 153797 DBH info: DBI::db=HASH(0x55f1826af3d8) $VAR1 = {
# '@@hostname' => 'it-Latitude-3510',
# 'CONNECTION_ID()' => 110,
# 'DATABASE()' => 'testdb',
# 'VERSION()' => '8.0.27-0ubuntu0.20.04.1'
# };
# Connection info: Localhost via UNIX socket Character set info: $VAR1 = [
# {
# Value => 'latin1',
# Variable_name => 'character_set_client'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_connection'
# },
# {
# Value => 'utf8mb4',
# Variable_name => 'character_set_database'
# },
# {
# Value => 'binary',
# Variable_name => 'character_set_filesystem'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_results'
# },
# {
# Value => 'utf8mb4',
# Variable_name => 'character_set_server'
# },
# {
# Value => 'utf8mb3',
# Variable_name => 'character_set_system'
# },
# {
# Value => '/usr/share/mysql/charsets/',
# Variable_name => 'character_sets_dir'
# }
# ];
# $DBD::mysql::VERSION: 4.050 $DBI::VERSION: 1.643
# Cxn:3965 153797 DBI::db=HASH(0x55f1826af3d8) Setting dbh
# Cxn:3970 153797 DBI::db=HASH(0x55f1826af3d8) SELECT @@server_id /*!50038 , @@hostname*/
# Cxn:3972 153797 DBI::db=HASH(0x55f1826af3d8) hostname: it-Latitude-3510 1
# Cxn:3953 153797 DBI::db=HASH(0x55f1826af3d8) Connected dbh to it-Latitude-3510 h=localhost
# DSNParser:2241 153797 DSN string made from options:
# DSNParser:2178 153797 No DSN to parse
# DSNParser:2294 153797 DBI:mysql:testdb;host=localhost;mysql_read_default_group=client
# DSNParser:2343 153797 DBI:mysql:testdb;host=localhost;mysql_read_default_group=client root mypassword mysql_enable_utf8=>0, AutoCommit=>1, PrintError=>0, RaiseError=>1, ShowErrorStatement=>1
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826b5fe8) SET SESSION innodb_lock_wait_timeout=1
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826b5fe8) SET SESSION lock_wait_timeout=60
# DSNParser:2502 153797 DBI::db=HASH(0x55f1826b5fe8) SET SESSION wait_timeout=10000
# DSNParser:2392 153797 DBI::db=HASH(0x55f1826b5fe8) SELECT @@SQL_MODE
# DSNParser:2402 153797 DBI::db=HASH(0x55f1826b5fe8) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:2416 153797 DBH info: DBI::db=HASH(0x55f1826b5fe8) $VAR1 = {
# '@@hostname' => 'it-Latitude-3510',
# 'CONNECTION_ID()' => 111,
# 'DATABASE()' => 'testdb',
# 'VERSION()' => '8.0.27-0ubuntu0.20.04.1'
# };
# Connection info: Localhost via UNIX socket Character set info: $VAR1 = [
# {
# Value => 'latin1',
# Variable_name => 'character_set_client'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_connection'
# },
# {
# Value => 'utf8mb4',
# Variable_name => 'character_set_database'
# },
# {
# Value => 'binary',
# Variable_name => 'character_set_filesystem'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_results'
# },
# {
# Value => 'utf8mb4',
# Variable_name => 'character_set_server'
# },
# {
# Value => 'utf8mb3',
# Variable_name => 'character_set_system'
# },
# {
# Value => '/usr/share/mysql/charsets/',
# Variable_name => 'character_sets_dir'
# }
# ];
# $DBD::mysql::VERSION: 4.050 $DBI::VERSION: 1.643
# Cxn:3965 153797 DBI::db=HASH(0x55f1826b5fe8) Setting dbh
# Cxn:3970 153797 DBI::db=HASH(0x55f1826b5fe8) SELECT @@server_id /*!50038 , @@hostname*/
# Cxn:3972 153797 DBI::db=HASH(0x55f1826b5fe8) hostname: it-Latitude-3510 1
# Cxn:3953 153797 DBI::db=HASH(0x55f1826b5fe8) Connected dbh to it-Latitude-3510 h=localhost
# Percona::XtraDB::Cluster:8073 153797 it-Latitude-3510 SHOW VARIABLES LIKE 'wsrep_on'
# Percona::XtraDB::Cluster:8075 153797 $VAR1 = undef;
#
# VersionParser:2033 153797 VersionParser got a dbh, trying to get the version
# VersionParser:2081 153797 InnoDB support: DEFAULT
# VersionParser:2093 153797 InnoDB version: 8.0.27
# pt_online_schema_change:8600 153797 innodb_stats_peristent is ON, enabling --analyze-before-swap
# TableParser:3445 153797 Checking `testdb`.`Test_tbl`
# TableParser:3451 153797 SHOW TABLES FROM `testdb` LIKE 'Test\_tbl'
# TableParser:3466 153797 Table testdb Test_tbl exists
# pt_online_schema_change:10726 153797 SHOW TRIGGERS FROM `testdb` LIKE 'Test\_tbl'
# TableParser:3256 153797 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:3261 153797 DBI::db=HASH(0x55f1826af3d8) USE `testdb`
# TableParser:3265 153797 SHOW CREATE TABLE `testdb`.`Test_tbl`
# TableParser:3275 153797 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
# TableParser:3474 153797 Storage engine: InnoDB
# TableParser:3307 153797 Table cols: `id`
# TableParser:3474 153797 Storage engine: InnoDB
# NibbleIterator:6001 153797 EXPLAIN SELECT * FROM `testdb`.`Test_tbl` WHERE 1=1
# NibbleIterator:6003 153797 $VAR1 = {
# extra => undef,
# filtered => '100',
# id => 1,
# key => undef,
# key_len => undef,
# partitions => undef,
# possible_keys => undef,
# ref => undef,
# rows => 1,
# select_type => 'SIMPLE',
# table => 'Test_tbl',
# type => 'ALL'
# };
#
# NibbleIterator:5871 153797 One nibble: yes
# NibbleIterator:5933 153797 Auto-selecting best index
# TableParser:3386 153797 Indexes sorted best-first:
# NibbleIterator:5966 153797 Best index: undef
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
# pt_online_schema_change:10779 153797 Finding child tables
# pt_online_schema_change:10790 153797 SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='testdb' AND referenced_table_name='Test_tbl'
# pt_online_schema_change:10793 153797 No child tables found
# VersionParser:2033 153797 VersionParser got a dbh, trying to get the version
# VersionParser:2081 153797 InnoDB support: DEFAULT
# VersionParser:2093 153797 InnoDB version: 8.0.27
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See https://bugs.mysql.com/bug.php?id=96145
Since pt-online-schema change needs to rename the old <-> new tables as the final step, and the requested table has FKs, it cannot be executed under the current MySQL version
# Cxn:4096 153797 Destroying cxn
# Cxn:4106 153797 DBI::db=HASH(0x55f1826b5fe8) Disconnecting dbh on it-Latitude-3510 h=localhost
# Cxn:4096 153797 Destroying cxn
# Cxn:4106 153797 DBI::db=HASH(0x55f1826af3d8) Disconnecting dbh on it-Latitude-3510 h=localhost