Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

pt-online-schema-change: unknown_error

Description

When attempting to add auto increment option to Primary key using pt-osc, I’ve seen “unknown_error” at the end of a successful run.

 

When executed via the command line, the response is as follows.

pt-online-schema-change --alter "MODIFY membership_history_seq bigint NOT NULL AUTO_INCREMENT" D=membership,t=member_membership_history\ --no-drop-new-table \ --chunk-size=3000 \ --sleep=0.01 \ --skip-check-slave-lag \ --defaults-file=/home1/irteam/db/mysql/my.cnf \ --port=13306 \ --user=admin \ --password='Dolphin121!@!1' \ --max-load="Threads_running=100" \ --critical-load="Threads_running=200" \ --chunk-index=primary \ --charset=utf8mb4 \ --socket=/home1/irteam/db/mysql/tmp/mysql.sock\ --set-vars="innodb_lock_wait_timeout=1,lock_wait_timeout=1" \ --no-check-alter \ --statistics \ --execute 1> Cannot connect to A=utf8mb4,P=13306,S=/home1/irteam/db/mysql/tmp/mysql.sock,h=aspm-aspdb-t1002,p=...,u=admin No slaves found. See --recursion-method if host aspm-aspdb-t1001 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 Altering `membership`.`member_membership_history`... Creating new table... Created new table membership._member_membership_history_new OK. Altering new table... Altered `membership`.`_member_membership_history_new` OK. 2025-01-08T07:02:06 Creating triggers... 2025-01-08T07:02:06 Created triggers OK. 2025-01-08T07:02:06 Copying approximately 3284207 rows... Copying `membership`.`member_membership_history`: 27% 01:20 remain Copying `membership`.`member_membership_history`: 53% 00:52 remain Copying `membership`.`member_membership_history`: 78% 00:24 remain 2025-01-08T07:04:09 Copied rows OK. 2025-01-08T07:04:09 Analyzing new table... 2025-01-08T07:04:09 Swapping tables... 2025-01-08T07:04:09 Swapped original and new tables OK. 2025-01-08T07:04:09 Dropping old table... 2025-01-08T07:04:10 Dropped old table `membership`.`__member_membership_history_old` OK. 2025-01-08T07:04:10 Dropping triggers... 2025-01-08T07:04:10 Dropped triggers OK. # Event Count # ================== ===== # INSERT 1170 # mysql_warning_1062 2 # unknown_error 1 Successfully altered `membership`.`member_membership_history`.

 

This is my table:

> show CREATE TABLE membership.member_membership_history\G *************************** 1. row *************************** Table: member_membership_history Create Table: CREATE TABLE `member_membership_history` ( `membership_history_seq` bigint NOT NULL, `payco_id_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `membership_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `cust_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `eng_first_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `eng_last_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `card_number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, `card_pin_number` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `membership_grade_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `membership_grade_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `membership_expire_sdate` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `membership_expire_edate` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `membership_state_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `client_os_type_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `register_ymdt` datetime NOT NULL, PRIMARY KEY (`membership_history_seq`), KEY `ink01_member_membership_history` (`payco_id_no`,`membership_code`), KEY `ink02_member_membership_history` (`register_ymdt`), KEY `idx_membership_state_code_no_code` (`membership_state_code`,`payco_id_no`,`membership_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

 

I checked the code, but I’m not sure how to figure it out.

https://github.com/percona/percona-toolkit/blob/release-3.0.13/bin/pt-online-schema-change

sub error_event { my ($error) = @_; return 'undefined_error' unless $error; my $event = $error =~ m/Lock wait timeout/ ? 'lock_wait_timeout' : $error =~ m/Deadlock found/ ? 'deadlock' : $error =~ m/execution was interrupted/ ? 'query_killed' : $error =~ m/server has gone away/ ? 'lost_connection' : $error =~ m/Lost connection/ ? 'connection_killed' : 'unknown_error'; return $event; }

 

I would like to understand the possible reasons and causes that might lead to an 'unknown_error'. I also want to know recommended actions, such as leaving more detailed logs for future jobs, and any other precautions to take in such cases.

 

Seen this on:

  • Ubuntu 22.04

  • MySQL 8.0.35

  • pt-online-schema-change 3.0.13

Environment

None

Activity

Show:

Aaditya Dubey February 3, 2025 at 1:31 PM
Edited

Hi

Thank you for the report.
Unfortunately, I’m unable to repeat the behaviour from my end:

<-alter "MODIFY membership_history_seq bigint NOT NULL AUTO_INCREMENT" D=test,t=member_membership_history\ > --no-drop-new-table \ > --chunk-size=3000 \ > --sleep=0.01 \ > --skip-check-slave-lag \ > --defaults-file=/home/adi/sandboxes/msb_ps8_0_40/my.sandbox.cnf \ > --port=8035 \ > --user=msandbox \ > --password='msandbox' \ > --max-load="Threads_running=100" \ > --critical-load="Threads_running=200" \ > --chunk-index=primary \ > --charset=utf8mb4 \ > --socket=/tmp/mysql_sandbox8035.sock \ > --set-vars="innodb_lock_wait_timeout=1,lock_wait_timeout=1" \ > --no-check-alter \ > --statistics \ > --execute No slaves found. See --recursion-method if host localhost 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 Altering `test`.`member_membership_history`... Creating new table... Created new table test._member_membership_history_new OK. Altering new table... Altered `test`.`_member_membership_history_new` OK. 2025-02-03T13:26:21 Creating triggers... 2025-02-03T13:26:21 Created triggers OK. 2025-02-03T13:26:21 Copying approximately 8982 rows... 2025-02-03T13:26:22 Copied rows OK. 2025-02-03T13:26:22 Analyzing new table... 2025-02-03T13:26:22 Swapping tables... 2025-02-03T13:26:22 Swapped original and new tables OK. 2025-02-03T13:26:22 Dropping old table... 2025-02-03T13:26:22 Dropped old table `test`.`_member_membership_history_old` OK. 2025-02-03T13:26:22 Dropping triggers... 2025-02-03T13:26:22 Dropped triggers OK. # Event Count # ====== ===== # INSERT 1 Successfully altered `test`.`member_membership_history`.

Please run same command with debug mode PTDEBUG=1 pt-online-schema-change --alter .. and please share the output.

Please run the same test with the latest PT version, which is 3.7; it looks like you are using a pretty older version.

Details

Assignee

Reporter

Priority

Components

Affects versions

Labels

Needs QA

Yes

Smart Checklist

Created January 8, 2025 at 1:19 AM
Updated February 3, 2025 at 1:32 PM
Configure

Flag notifications