Pt-online-schema-change fails on add or drop (probably on everything)

mrphil · July 30, 2021, 6:07am

I have a fresh install (test) database machine running Centos8, percona-server with mysql8.
Followed

create Centos8 server (on DigitalOcean)
install percona (Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS — Percona Server 8.0 Documentation)
install tools (Installing Percona Toolkit — Percona Toolkit Documentation)

From there I restored a snapshot of data from our working Percona Mysql8 production instance.

This is my first time using percona-toolkit against mysql8. Nothing is working.
Old pt-online-schema-change calls for 5.7 do not work any more. Even simple things fail:

pt-online-schema-change --alter-foreign-keys-method=auto --dry-run --print --alter "ADD column marketplace22 varchar(25)"  D=app_production,t=sales_ranks

Results in:

(in cleanup) Error altering new table `app_production`.`_sales_ranks_new`: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1 [for Statement "ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace22 varchar(25) "] at /usr/bin/pt-online-schema-change line 9512.

If I execute the code inside of the mysql prompt it works…so it is something about configuration, but really it should be automatic.

Full Call

[root@centos-db-test ~]# pt-online-schema-change  --user=root --ask-pass --alter-foreign-keys-method=auto --execute --print --alter "add column marketplace22 varchar(25)"  D=app_production,t=sales_ranks
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_CTYPE = "UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
Enter MySQL password: 
No slaves found.  See --recursion-method if host centos-db-test 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
Child tables:
  `app_production`.`sales_rank_data_points` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Altering `app_production`.`sales_ranks`...
Creating new table...
CREATE TABLE `app_production`.`_sales_ranks_new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `marketplace22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
  KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
Created new table app_production._sales_ranks_new OK.
Altering new table...
ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace22 varchar(25) 
2021-07-29T07:20:00 Dropping new table...
DROP TABLE IF EXISTS `app_production`.`_sales_ranks_new`;
2021-07-29T07:20:00 Dropped new table OK.
`app_production`.`sales_ranks` was not altered.
	(in cleanup) Error altering new table `app_production`.`_sales_ranks_new`: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1 [for Statement "ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace22 varchar(25) "] at /usr/bin/pt-online-schema-change line 9512.

Error altering new table `app_production`.`_sales_ranks_new`: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1 [for Statement "ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace22 varchar(25) "] at /usr/bin/pt-online-schema-change line 9512.

matthewb · July 30, 2021, 3:00pm

Looks like that column already exists?

marketplace22 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

matthewb · July 30, 2021, 3:02pm

Run like this:
[root@centos-db-test ~]# PTDEBUG=1 pt-online-schema-change --user=root ....
to see a lot more information about what is happening.

mrphil · August 2, 2021, 12:14am

Here is the end of the log for that. As you can see the column (I am now trying to add marketplace33 varchar(25)) doesn’t exist. It creates the temporary table but fails on doing the alter. I am logging in to mysql as root, so I don’t think it’s a permissions issue - and I can issue the alter from within the mysql prompt without problem.

# Daemon:2599 71946 Starting daemon
# Daemon:2669 71946 Daemon running
# TableParser:3282 71946 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:3287 71946 DBI::db=HASH(0x556ee41ac198) USE `app_production`
# TableParser:3291 71946 SHOW CREATE TABLE `app_production`.`sales_ranks`
# TableParser:3301 71946 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Creating new table...
# pt_online_schema_change:10795 71946 CREATE TABLE `app_production`.`_sales_ranks_new` (
#   `id` int NOT NULL AUTO_INCREMENT,
#   `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
#   `rank` int DEFAULT NULL,
#   `item_id` int DEFAULT NULL,
#   `tenant_id` int DEFAULT NULL,
#   `created_at` datetime DEFAULT NULL,
#   `updated_at` datetime DEFAULT NULL,
#   `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
#   `marketplace22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
#   PRIMARY KEY (`id`),
#   KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
#   KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
# ) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
CREATE TABLE `app_production`.`_sales_ranks_new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `marketplace22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
  KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
Created new table app_production._sales_ranks_new OK.
Altering new table...
ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace33 varchar(25); 
# pt_online_schema_change:9510 71946 ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace33 varchar(25); 
# CleanupTask:6711 71946 Calling cleanup task CODE(0x556ee41b8cc0)
# pt_online_schema_change:9397 71946 Clean up new table
# TableParser:3472 71946 Checking `app_production`.`_sales_ranks_new`
# TableParser:3478 71946 SHOW TABLES FROM `app_production` LIKE '\_sales\_ranks\_new'
# TableParser:3493 71946 Table app_production _sales_ranks_new exists
# pt_online_schema_change:9403 71946 New table exists: yes
2021-08-02T00:11:06 Dropping new table...
DROP TABLE IF EXISTS `app_production`.`_sales_ranks_new`;
# pt_online_schema_change:9442 71946 DROP TABLE IF EXISTS `app_production`.`_sales_ranks_new`;
2021-08-02T00:11:06 Dropped new table OK.
# pt_online_schema_change:9274 71946 Clean up done, report if orig table was altered
`app_production`.`sales_ranks` was not altered.
	(in cleanup) Error altering new table `app_production`.`_sales_ranks_new`: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1 [for Statement "ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace33 varchar(25); "] at /usr/bin/pt-online-schema-change line 9512.

Error altering new table `app_production`.`_sales_ranks_new`: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1 [for Statement "ALTER TABLE `app_production`.`_sales_ranks_new` add column marketplace33 varchar(25); "] at /usr/bin/pt-online-schema-change line 9512.

# Cxn:4123 71946 Destroying cxn
# Cxn:4133 71946 DBI::db=HASH(0x556ee41b22c8) Disconnecting dbh on centos-db-test 
# Cxn:4123 71946 Destroying cxn
# Cxn:4133 71946 DBI::db=HASH(0x556ee41ac198) Disconnecting dbh on centos-db-test

matthewb · August 2, 2021, 4:06am

Worked without issue for me.

[centos@mysql1-T1 ~]$ pt-online-schema-change --version
pt-online-schema-change 3.3.1
[centos@mysql1-T1 ~]$ mysql --version
mysql  Ver 8.0.23-14 for Linux on x86_64 (Percona Server (GPL), Release 14, Revision 3558242)

mysql1-T1 mysql> CREATE TABLE `sales_ranks` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `rank` int DEFAULT NULL,
    ->   `item_id` int DEFAULT NULL,
    ->   `tenant_id` int DEFAULT NULL,
    ->   `created_at` datetime DEFAULT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `marketplace22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
    ->   KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
    -> ) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected, 7 warnings (0.08 sec)

mysql1-T1 mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3778 | 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.                              |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3778 | 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.                              |
| Warning | 3778 | 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.                              |
| Warning | 1287 | 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead                                                                                 |
| Warning | 3778 | 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.                              |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql1-T1 mysql> Bye
[centos@mysql1-T1 ~]$ pt-online-schema-change --alter-foreign-keys-method=auto --execute --print --alter "ADD column marketplace33 varchar(25)" D=imdb_rocks,t=sales_ranks
No slaves found.  See --recursion-method if host mysql1-T1 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 `imdb_rocks`.`sales_ranks`; ignoring --alter-foreign-keys-method.
Altering `imdb_rocks`.`sales_ranks`...
Creating new table...
CREATE TABLE `imdb_rocks`.`_sales_ranks_new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `marketplace22` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
  KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
Created new table imdb_rocks._sales_ranks_new OK.
Altering new table...
ALTER TABLE `imdb_rocks`.`_sales_ranks_new` ADD column marketplace33 varchar(25)
Altered `imdb_rocks`.`_sales_ranks_new` OK.
2021-08-02T04:04:12 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name  : pt_osc_imdb_rocks_sales_ranks_del
SQL   : CREATE TRIGGER `pt_osc_imdb_rocks_sales_ranks_del` AFTER DELETE ON `imdb_rocks`.`sales_ranks` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `imdb_rocks`.`_sales_ranks_new` WHERE `imdb_rocks`.`_sales_ranks_new`.`id` <=> OLD.`id`; END
Suffix: del
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name  : pt_osc_imdb_rocks_sales_ranks_upd
SQL   : CREATE TRIGGER `pt_osc_imdb_rocks_sales_ranks_upd` AFTER UPDATE ON `imdb_rocks`.`sales_ranks` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `imdb_rocks`.`_sales_ranks_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `imdb_rocks`.`_sales_ranks_new`.`id` <=> OLD.`id`; REPLACE INTO `imdb_rocks`.`_sales_ranks_new` (`id`, `product_category`, `rank`, `item_id`, `tenant_id`, `created_at`, `updated_at`, `marketplace`, `marketplace22`) VALUES (NEW.`id`, NEW.`product_category`, NEW.`rank`, NEW.`item_id`, NEW.`tenant_id`, NEW.`created_at`, NEW.`updated_at`, NEW.`marketplace`, NEW.`marketplace22`); END
Suffix: upd
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name  : pt_osc_imdb_rocks_sales_ranks_ins
SQL   : CREATE TRIGGER `pt_osc_imdb_rocks_sales_ranks_ins` AFTER INSERT ON `imdb_rocks`.`sales_ranks` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `imdb_rocks`.`_sales_ranks_new` (`id`, `product_category`, `rank`, `item_id`, `tenant_id`, `created_at`, `updated_at`, `marketplace`, `marketplace22`) VALUES (NEW.`id`, NEW.`product_category`, NEW.`rank`, NEW.`item_id`, NEW.`tenant_id`, NEW.`created_at`, NEW.`updated_at`, NEW.`marketplace`, NEW.`marketplace22`);END
Suffix: ins
Time  : AFTER
-----------------------------------------------------------
2021-08-02T04:04:12 Created triggers OK.
2021-08-02T04:04:12 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `imdb_rocks`.`_sales_ranks_new` (`id`, `product_category`, `rank`, `item_id`, `tenant_id`, `created_at`, `updated_at`, `marketplace`, `marketplace22`) SELECT `id`, `product_category`, `rank`, `item_id`, `tenant_id`, `created_at`, `updated_at`, `marketplace`, `marketplace22` FROM `imdb_rocks`.`sales_ranks` LOCK IN SHARE MODE /*pt-online-schema-change 5957 copy table*/
2021-08-02T04:04:12 Copied rows OK.
2021-08-02T04:04:12 Analyzing new table...
2021-08-02T04:04:12 Swapping tables...
RENAME TABLE `imdb_rocks`.`sales_ranks` TO `imdb_rocks`.`_sales_ranks_old`, `imdb_rocks`.`_sales_ranks_new` TO `imdb_rocks`.`sales_ranks`
2021-08-02T04:04:12 Swapped original and new tables OK.
2021-08-02T04:04:12 Dropping old table...
DROP TABLE IF EXISTS `imdb_rocks`.`_sales_ranks_old`
2021-08-02T04:04:12 Dropped old table `imdb_rocks`.`_sales_ranks_old` OK.
2021-08-02T04:04:12 Dropping triggers...
DROP TRIGGER IF EXISTS `imdb_rocks`.`pt_osc_imdb_rocks_sales_ranks_del`
DROP TRIGGER IF EXISTS `imdb_rocks`.`pt_osc_imdb_rocks_sales_ranks_upd`
DROP TRIGGER IF EXISTS `imdb_rocks`.`pt_osc_imdb_rocks_sales_ranks_ins`
2021-08-02T04:04:12 Dropped triggers OK.
Successfully altered `imdb_rocks`.`sales_ranks`.
[centos@mysql1-T1 ~]$
[centos@mysql1-T1 ~]$ mysql imdb_rocks
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 767673
Server version: 8.0.23-14 Percona Server (GPL), Release 14, Revision 3558242

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql1-T1 mysql> show create table sales_ranks\G
*************************** 1. row ***************************
       Table: sales_ranks
Create Table: CREATE TABLE `sales_ranks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `marketplace` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `marketplace22` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `marketplace33` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sales_ranks_on_item_id` (`item_id`) USING BTREE,
  KEY `index_sales_ranks_on_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8203494 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

mrphil · August 2, 2021, 5:28am

Ok…that’s good for you.

I don’t mind so much that it doesn’t work, but I mind that it doesnt work without any hint of why. I have 3 machines now that this happens on.

Our production system (Centos8, Percona Server 8)
2 testing systems, both Centos8 with Percona Server 8

The only difference perhaps is that my table has foreign keys.
I note that your log says:

No foreign keys reference `imdb_rocks`.`sales_ranks`; ignoring --alter-foreign-keys-method.

Whereas mine says:

Will automatically choose the method to update foreign keys.

Because I have a child table:

# pt_online_schema_change:11032 72268 Finding child tables
# pt_online_schema_change:11043 72268 SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='app_production' AND referenced_table_name='sales_ranks'
# NibbleIterator:6131 72268 EXPLAIN SELECT * FROM `app_production`.`sales_rank_data_points` WHERE 1=1
# NibbleIterator:6133 72268 $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 => 'sales_rank_data_points',
#   type => 'ALL'
# };
# 
# pt_online_schema_change:11070 72268 Child tables: $VAR1 = [
#   {
#     db => 'app_production',
#     name => '`app_production`.`sales_rank_data_points`',
#     row_est => 1,
#     tbl => 'sales_rank_data_points'
#   }
# ];
# 
# VersionParser:2033 72268 VersionParser got a dbh, trying to get the version
# VersionParser:2081 72268 InnoDB support: DEFAULT
# VersionParser:2093 72268 InnoDB version: 8.0.25-15
# pt_online_schema_change:11032 72268 Finding child tables
# pt_online_schema_change:11043 72268 SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='app_production' AND referenced_table_name='sales_ranks'
# NibbleIterator:6131 72268 EXPLAIN SELECT * FROM `app_production`.`sales_rank_data_points` WHERE 1=1
# NibbleIterator:6133 72268 $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 => 'sales_rank_data_points',
#   type => 'ALL'
# };
# 
# pt_online_schema_change:11070 72268 Child tables: $VAR1 = [
#   {
#     db => 'app_production',
#     name => '`app_production`.`sales_rank_data_points`',
#     row_est => 1,
#     tbl => 'sales_rank_data_points'
#   }
# ];
# 
Child tables:
  `app_production`.`sales_rank_data_points` (approx. 1 rows)
Will automatically choose the method to update foreign keys.

(Sorry, it’s a huge amount of output, maybe I didn’t go far enough back in it)

matthewb · August 2, 2021, 3:19pm

Yea, this is giving me a broken picture of what’s really happening. Save the entire debug output to a txt file and upload to a new issue at https://jira.percona.com/

mrphil · August 3, 2021, 1:49am

Bug added to Jira ([PT-2002] Pt-online-schema-change fails on add or drop (probably on everything) - Percona JIRA) … I’ve never used Jira before so hopefully it is formatted correctly.
Management is steaming at this point because all development is halted due to the inability to make DB changes!
I am sure it is some configuration issue I have done, but we can’t see it here.

matthewb · August 3, 2021, 3:38am

Can you provide the schema for the child table(s)?
I read the entire output you provided in the JIRA. Thanks. Sill not seeing what could be the issue. The code at line 9510 shows us exactly what it will execute 2 lines later. (same variable). And the SQL syntax matches my test too. Sounds dumb, but can you take out the ‘;’ in your --alter?

mrphil · August 3, 2021, 4:11am

Thanks for sticking with me!

Removing the ; didn’t change anything

Here’s that other table

CREATE TABLE `sales_rank_data_points` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sales_rank_id` int DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `sample_date` date DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sales_rank_data_points_on_sales_rank_id` (`sales_rank_id`),
  KEY `index_sales_rank_data_points_on_tenant_id` (`tenant_id`),
  KEY `index_sales_rank_data_points_on_sample_date` (`sample_date`),
  CONSTRAINT `fk_rails_d0cb195dee` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`),
  CONSTRAINT `fk_rails_f41bf13cb9` FOREIGN KEY (`sales_rank_id`) REFERENCES `sales_ranks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=595736080 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPRESSED

There is also a Tenants table that is essentially just an id and name.

Just to reiterate, we used to use ptosc all the time when we were on percona/mysql5.7 these problems have just shown up under mysql8.

bob · August 3, 2021, 6:50pm

I have also used a perl DBI debugging variable additionally to PTBEBUG=1. It is
DBI_TRACE=15
but this will barf a lot…You’ve been warned!

Topic		Replies	Views
Pt-online-schema-change not working as expected Percona Toolkit	12	4229	October 27, 2022
pt-online-schema-change is not working right Percona Toolkit	2	1603	July 7, 2014
Pt-online-schema failure due to duplicated constraint names and from multiple foreign keys Percona Toolkit mysql , percona	2	239	March 15, 2024
pt-online-schema-change issue with multi master setup Percona Toolkit	4	763	November 19, 2018
Help with pt-online-scheme-channge Percona Toolkit	1	658	August 28, 2013

Pt-online-schema-change fails on add or drop (probably on everything)

Related Topics