pt-online-schema-change: Error dropping trigger

Polyglot Projects Percona Toolkit
1

Hi. Have next problem on alter table (this problem stopped galera cluster 3 nodes):

# pt-online-schema-change --alter "CHANGE amount amount DECIMAL(12,4) COMMENT 'Сумма платежа'" D=database1,t=invoices --execute

No slaves found. See --recursion-method if host prod-mysql-db-1 has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `database1`.`invoices`...

Creating new table...

Created new table database1._invoices_new OK.

Altering new table...

Altered `database1`.`_invoices_new` OK.

2014-09-11T13:41:21 Creating triggers...

2014-09-11T13:41:21 Created triggers OK.

2014-09-11T13:41:21 Copying approximately 3980093 rows...

Copying rows caused a MySQL error 1265: Data truncated for column 'amount' at row 1654

No more warnings about this MySQL error will be reported. If --statistics was specified, mysql_warning_1265 will list the total count of this MySQL error.

Copying `database1`.`invoices`: 5% 08:11 remain

Copying `database1`.`invoices`: 10% 08:10 remain

Copying `database1`.`invoices`: 16% 07:40 remain

Copying `database1`.`invoices`: 21% 07:15 remain

Copying `database1`.`invoices`: 26% 06:49 remain

Copying `database1`.`invoices`: 31% 06:26 remain

Copying `database1`.`invoices`: 36% 06:02 remain

Copying `database1`.`invoices`: 41% 05:35 remain

Copying `database1`.`invoices`: 46% 05:08 remain

Copying `database1`.`invoices`: 51% 04:42 remain

Copying `database1`.`invoices`: 56% 04:13 remain

Copying `database1`.`invoices`: 61% 03:44 remain

Copying `database1`.`invoices`: 66% 03:15 remain

Copying `database1`.`invoices`: 71% 02:47 remain

Copying `database1`.`invoices`: 76% 02:19 remain

Copying `database1`.`invoices`: 81% 01:51 remain

2014-09-11T13:49:39 Copied rows OK.

2014-09-11T13:49:40 Swapping tables...

2014-09-11T13:49:40 Swapped original and new tables OK.

2014-09-11T13:49:40 Dropping old table...

2014-09-11T13:49:42 Dropping triggers...

2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 To try dropping the triggers again, execute:

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_del`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_upd`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_ins`;

Altered `database1`.`invoices` but there were errors or warnings.

(in cleanup) 2014-09-11T13:49:42 Error dropping the old table: DBD::mysql::db do failed: Lost connection to MySQL server during query [for Statement "DROP TABLE IF EXISTS `database1`.`_invoices_old`"] at /usr/bin/pt-online-schema-change line 9291.



2014-09-11T13:49:43 Dropping triggers...

2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: (no error string) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: (no error string) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 Error dropping trigger: DBI connect('database1;;mysql_read_default_group=client','',...) failed: (no error string) at /usr/bin/pt-online-schema-change line 2261



2014-09-11T13:49:43 To try dropping the triggers again, execute:

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_del`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_upd`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_ins`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_del`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_upd`;

DROP TRIGGER IF EXISTS `database1`.`pt_osc_database1_invoices_ins`;

Altered `database1`.`invoices` but there were errors or warnings.

How to avoid similar problems in the future?

2

What version of pt-online-schema-change you are using ? And what is the version of MySQL Server ?
I noticed few bugs with pt-osc reported with dropping triggers and on dropping the old table.

I would recommend to try with latest version of pt-osc and if still appears the same error then running with PTDEBUG=1 will help as it will capture lots of debugging output and helps to find out what causing the issue.

3

Hi mirfan! Thank’s for reply!

Version of pt-online-schema-change: 2.2.10


SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------------------+
| innodb_version | 5.6.20-68.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.20-68.0-56-log |
| version_comment | Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+--------------------------------------------------------------+

MySQL crash report:


Thread pointer: 0x2b7c2d0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f04281aaaa0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8c8e1e]
/usr/sbin/mysqld(handle_fatal_signal+0x36c)[0x680ffc]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf030)[0x7f097d3c8030]
/lib/x86_64-linux-gnu/libc.so.6(+0x1134da)[0x7f097b6e34da]
/usr/sbin/mysqld(_ZSt8for_eachISt23_Rb_tree_const_iteratorIP14dict_foreign_tE27dict_foreign_remove_partialET0_T_S6_S5_+0x1a9)[0xa3dfe9]
/usr/sbin/mysqld[0xa3917c]
/usr/sbin/mysqld[0x98e22a]
/usr/sbin/mysqld[0x8edf77]
/usr/sbin/mysqld(_Z15ha_delete_tableP3THDP10handlertonPKcS4_S4_b+0xb5)[0x5e1e65]
/usr/sbin/mysqld(_Z23mysql_rm_table_no_locksP3THDP10TABLE_LISTbbbb+0x535)[0x73e905]
/usr/sbin/mysqld(_Z14mysql_rm_tableP3THDP10TABLE_LISTcc+0x1c7)[0x73fcd7]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x170f)[0x6f75af]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x588)[0x6fbbb8]
/usr/sbin/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Relay_log_infoPKcj+0x52f)[0x8678bf]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x67)[0x866177]
/usr/sbin/mysqld(_Z14wsrep_apply_cbPvPKvmjPK14wsrep_trx_meta+0x4ba)[0x5d85ba]
/usr/lib/libgalera_smm.so(_ZNK6galera9TrxHandle5applyEPvPF15wsrep_cb_statusS1_PKvmjPK14wsrep_trx_metaERS6_+0xd3)[0x7f093557d103]
/usr/lib/libgalera_smm.so(+0x1d8d9e)[0x7f09355b1d9e]
/usr/lib/libgalera_smm.so(_ZN6galera13ReplicatorSMM9apply_trxEPvPNS_9TrxHandleE+0xa4)[0x7f09355b4b24]
/usr/lib/libgalera_smm.so(_ZN6galera13ReplicatorSMM11process_trxEPvPNS_9TrxHandleE+0x40)[0x7f09355b6a00]
/usr/lib/libgalera_smm.so(_ZN6galera15GcsActionSource8dispatchEPvRK10gcs_actionRb+0x3f4)[0x7f0935596fb4]
/usr/lib/libgalera_smm.so(_ZN6galera15GcsActionSource7processEPvRb+0x5b)[0x7f0935597c3b]
/usr/lib/libgalera_smm.so(_ZN6galera13ReplicatorSMM10async_recvEPv+0x63)[0x7f09355b79b3]
/usr/lib/libgalera_smm.so(galera_recv+0x23)[0x7f09355c5893]
/usr/sbin/mysqld[0x5d9040]
/usr/sbin/mysqld(start_wsrep_THD+0x27a)[0x5c310a]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7f097d3bfb50]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f097b6abe6d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2c0d380): DROP TABLE IF EXISTS `database1`.`_invoices_old`
Connection ID (thread ID): 30566
Status: NOT_KILLED

You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.