Not the answer you need?
Register and ask your own question!

pt-online-schema-change: Error dropping trigger

RNZRNZ EntrantCurrent User Role Beginner
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?

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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.
  • RNZRNZ Entrant Current User Role Beginner
    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.
    
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.