# Pt-online-schema-change not working as expected

**URL:** <https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444>\
**Category:** Percona Toolkit\
**Created:** [December 13, 2021, 12:52am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444 "2021-12-13T00:52:19Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![seebhoopi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/seebhoopi/32/5306_2.png) [@seebhoopi](https://forums.percona.com/u/seebhoopi)\
**Post date:** [December 13, 2021, 12:52am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/1 "2021-12-13T00:52:19Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 13, 2021, 5:30pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/2 "2021-12-13T17:30:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seebhoopi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/seebhoopi/32/5306_2.png) [@seebhoopi](https://forums.percona.com/u/seebhoopi)\
**Post date:** [December 14, 2021, 5:09am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/3 "2021-12-14T05:09:49Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 14, 2021, 5:27am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/4 "2021-12-14T05:27:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seebhoopi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/seebhoopi/32/5306_2.png) [@seebhoopi](https://forums.percona.com/u/seebhoopi)\
**Post date:** [December 14, 2021, 5:28am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/5 "2021-12-14T05:28:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 14, 2021, 5:35am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/6 "2021-12-14T05:35:28Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![seebhoopi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/seebhoopi/32/5306_2.png) [@seebhoopi](https://forums.percona.com/u/seebhoopi)\
**Post date:** [December 14, 2021, 6:20am UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/7 "2021-12-14T06:20:07Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![seebhoopi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/seebhoopi/32/5306_2.png) [@seebhoopi](https://forums.percona.com/u/seebhoopi)\
**Post date:** [December 14, 2021, 12:25pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/8 "2021-12-14T12:25:21Z")

</div>

I have narrated what exactly i have done. Please refer the steps below.  
Step 1: Create DB

```auto
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

```auto
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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 14, 2021, 3:38pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/9 "2021-12-14T15:38:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [December 16, 2021, 2:19pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/10 "2021-12-16T14:19:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 16, 2021, 2:48pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/11 "2021-12-16T14:48:21Z")

</div>

> [@seebhoopi](#):
>
> pt-online-schema-change 3.1.0

@carlos.salguero he posted this above

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [December 17, 2021, 2:27pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/12 "2021-12-17T14:27:53Z")

</div>

Hello again.  
That check was updated on [[PT-1940] ptsoc dropswap with mysql8: revise rejection - Percona JIRA](https://jira.percona.com/browse/PT-1940) 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](https://github.com/percona/percona-toolkit)

Regards

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [October 27, 2022, 5:27pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/13 "2022-10-27T17:27:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![chiliNUT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/chilinut/32/17682_2.png) [@chiliNUT](https://forums.percona.com/u/chiliNUT)\
**Post date:** [August 15, 2024, 7:22pm UTC](https://forums.percona.com/t/pt-online-schema-change-not-working-as-expected/13444/14 "2024-08-15T19:22:43Z")

</div>

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