Pt-osc Weird Drop Table Result

1

I am using pt-online-schema-change version 3.4.0. I ran it like this

      pt-online-schema-change \
          --user=admin \
          --ask-pass \
          --alter "MODIFY test_id BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT" \
          --alter-foreign-keys-method=auto \
          --max-load Threads_running=50 \
          --critical-load Threads_running=200 \
          --print \
          --progress=time,10 \
          D=tech,t=tech_thread_data,h=test.c4jaskd.us-east-1.rds.amazonaws.com \
          --execute | tee /tmp/$$.pt-osc

And for some reason at the end of the process, it gave this result

RENAME TABLE `tech`.`tech_thread_data` TO `tech`.`_tech_thread_data_old`, `tech`.`_tech_thread_data_new` TO `tech`.`tech_thread_data`
2022-10-10T10:40:52 Swapped original and new tables OK.
2022-10-10T10:40:52 Dropping old table...
DROP TABLE IF EXISTS `tech`.`_tech_thread_data_old`
2022-10-10T10:56:49 Error dropping trigger: DBI connect('tech;host=test.c4jaskd.us-east-1.rds.amazonaws.com;mysql_read_default_group=client','admin',...) failed: Can't connect to MySQL server on 'test.c4jaskd.us-east-1.rds.amazonaws.com' (111) at /usr/bin/pt-online-schema-change line 2282.

2022-10-10T10:56:49 Error dropping trigger: DBI connect('tech;host=test.c4jaskd.us-east-1.rds.amazonaws.com;mysql_read_default_group=client','admin',...) failed: Can't connect to MySQL server on 'test.c4jaskd.us-east-1.rds.amazonaws.com' (111) at /usr/bin/pt-online-schema-change line 2282.

2022-10-10T10:56:49 Error dropping trigger: DBI connect('tech;host=test.c4jaskd.us-east-1.rds.amazonaws.com;mysql_read_default_group=client','admin',...) failed: Can't connect to MySQL server on 'test.c4jaskd.us-east-1.rds.amazonaws.com' (111) at /usr/bin/pt-online-schema-change line 2282.

2022-10-10T10:56:49 To try dropping the triggers again, execute:
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_del`;
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_upd`;
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_ins`;
2022-10-10T10:56:49 Dropping triggers...
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_del`;
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_upd`;
DROP TRIGGER IF EXISTS `tech`.`pt_osc_tech_tech_thread_data_ins`;
Altered `tech`.`tech_thread_data` but there were errors or warnings.
2022-10-10T10:56:49 Error dropping the old table: DBD::mysql::db do failed: Lost connection to MySQL server during query [for Statement "DROP TABLE IF EXISTS `tech`.`_tech_thread_data_old`"] at /usr/bin/pt-online-schema-change line 9740.

I understand that this looks like a network issue, but the weird thing is that “tech._tech_thread_data_old” table was actually removed. Is this even possible?

1 Like
2

Hi @brianestrada658

From the output you shared, I can see the DROP was running for at least 16 minutes before printing the errors (From 10:40:52 to 10:56:49), so my guess here is the following:
1- pt-osc issued the DROP of a huge table.
2- The DROP itself was running for a long time in your DB (either due to locks or due to the drop itself).
3- pt-osc lost connection while waiting for the drop to complete, hence the error.
4- The DROP command was still running in MySQL (even though pt-osc lost connection).

Finally, the DROP finished successfully in you environment, and that’s why the table is no longer in your DB node.

1 Like
3

This make sense!!! The table is indeed huge (apologize for not even mentioning this). Thanks!

2 Likes