Pt-online-schema-change not working as expected

seebhoopi · December 13, 2021, 12:52am

I dont have foriegn key in my table, but still getting the below warning and error message. Please let me know the changes to make it work?

bhoopathi@it:~$ pt-online-schema-change --alter-foreign-keys-method=none --dry-run --print --alter “ADD column marketplace22 varchar(25)” D=testdb,t=tutorials_tbl,u=root,h=localhost --ask-pass
WARNING! Using alter-foreign-keys-method = “none”. This will typically cause foreign key violations!
This method of handling foreign key constraints is only provided so that the database administrator can disable the tool’s built-in functionality if desired.

Continue anyway? (y/N)y
Enter MySQL password:
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
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See MySQL Bugs: Access denied
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

matthewb · December 13, 2021, 5:30pm

If you don’t have FKs, then why are you even specifying that parameter? Just remove that parameter.

seebhoopi · December 14, 2021, 5:09am

tried without “–alter-foreign-keys-method”, still not able to alter the table. Anything i need to change?
bhoopathi@it-Latitude-3510:~$ pt-online-schema-change --dry-run --print --alter “ADD column marketplace22 varchar(25)” D=testdb,t=tutorials_tbl,u=root,h=localhost --ask-pass
Enter MySQL password:
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
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See MySQL Bugs: Access denied
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

matthewb · December 14, 2021, 5:27am

This table itself may not have any FKs, but is there another table with FKs referencing this table? That could be your issue.

seebhoopi · December 14, 2021, 5:28am

Thanks for your reply Matthew…I have created test db with single table. There is no foreign key in any table.

matthewb · December 14, 2021, 5:35am

Run like this and see if you can figure out why PTOSC is giving this error:

$ PTDEBUG=1 pt-online-schema-change --dry-run ...

seebhoopi · December 14, 2021, 6:20am

Version used information

bhoopathi@it-Latitude-3510:~$ pt-online-schema-change --version
pt-online-schema-change 3.1.0

Please refer the attached file. I am getting the same issue.

There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See MySQL Bugs: Access denied
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

seebhoopi · December 14, 2021, 12:25pm

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

matthewb · December 14, 2021, 3:38pm

Ok. That’s clearly some sort of bug. Can you please open a https://jira.percona.com issue under the ‘Percona Toolkit’ project with all that information?

carlos.salguero · December 16, 2021, 2:19pm

Hi,
Could you provide the output of bin/pt-online-schema-change --version?
That message of for preventing and old error in MySQL but it should be detecting you are using a newer MySQL version. I think you might have an old Toolkit version.

matthewb · December 16, 2021, 2:48pm

@carlos.salguero he posted this above

carlos.salguero · December 17, 2021, 2:27pm

Hello again.
That check was updated on [PT-1940] ptsoc dropswap with mysql8: revise rejection - Percona JIRA and it is oging to be released on PT v3.4.
You can download the latest binary from Github: GitHub - percona/percona-toolkit: Percona Toolkit

Regards

Ivan_Groenewold · October 27, 2022, 5:27pm

Actually this is not fully fixed in 3.4.0 yet. If the table doesn’t have any FK pt-osc will still abort with an error.

chiliNUT · August 15, 2024, 7:22pm

I was on 3.1.0 and experienced the same issue. I updated to 3.6.0 and it seems to be fixed!

Topic		Replies	Views
pt-online-schema-change drop the old and new table Percona Toolkit	2	2322	June 24, 2017
Pt-online-schema-change fails on add or drop (probably on everything) Percona Server for MySQL 8.0	10	3383	August 3, 2021
pt-online-schema-change - rename FK problem Percona Toolkit	0	1077	March 27, 2018
Drop-FK-pt-online-schema-change. Percona Toolkit	1	1669	October 10, 2012
pt-online-schema-change concepts Percona Toolkit	1	712	April 2, 2015

Pt-online-schema-change not working as expected

Related topics