1

I've attempted to alter table using pt-online-schema-change in our master-master replication environment (Percona used as a cluster). It seems, that an operation completed successfully, but inspecting MySQL logs I've found the following:

140724 20:41:49 [ERROR] Slave SQL: Error 'This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'' on query. Default database: 'forumhouse'. Query: 'CREATE DEFINER=forumhouse@% TRIGGER pt_osc_forumhouse_xf_user_profile_del AFTER DELETE ON forumhouse.xf_user_profile FOR EACH ROW DELETE IGNORE FROM forumhouse._xf_user_profile_new WHERE forumhouse._xf_user_profile_new.user_id <=> OLD.user_id', Error_code: 1235

What may this mean? And why pt-online-schema-change ignored an error? Is it safe to use this great tool in our situation?

pt-online-schema-change version is 2.2.9. Server config:

# MySQL server variables for 127.0.0.1

auto_increment_increment = 2
auto_increment_offset = 1
autocommit = ON
automatic_sp_privileges = ON
back_log = 50
basedir = /usr
big_tables = OFF
binlog_cache_size = 32768
binlog_direct_non_transactional_updates = OFF
binlog_format = ROW
binlog_stmt_cache_size = 32768
bulk_insert_buffer_size = 8388608
character_set_client = utf8
character_set_connection = utf8
character_set_database = utf8
character_set_filesystem = binary
character_set_results = utf8
character_set_server = latin1
character_set_system = utf8
character_sets_dir = /usr/share/percona-xtradb-cluster/charsets/
collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = latin1_swedish_ci
completion_type = NO_CHAIN
concurrent_insert = AUTO
connect_timeout = 10
datadir = /var/lib/mysql/
date_format = %Y-%m-%d
datetime_format = %Y-%m-%d %H:%i:%s
default_storage_engine = InnoDB
default_week_format = 0
delay_key_write = ON
delayed_insert_limit = 100
delayed_insert_timeout = 300
delayed_queue_size = 1000
div_precision_increment = 4
enforce_storage_engine = 
engine_condition_pushdown = ON
error_count = 0
event_scheduler = OFF
expand_fast_index_creation = OFF
expire_logs_days = 1
external_user = 
extra_max_connections = 1
extra_port = 0
fast_index_creation = ON
flush = OFF
flush_time = 0
foreign_key_checks = ON
ft_boolean_syntax = + -><()~*:""&|
ft_max_word_len = 84
ft_min_word_len = 4
ft_query_expansion_limit = 20
ft_stopword_file = (built-in)
general_log = OFF
general_log_file = /var/lib/mysql/rhino0.log
group_concat_max_len = 1024
have_compress = YES
have_crypt = YES
have_csv = YES
have_dynamic_loading = YES
have_flashcache = YES
have_geometry = YES
have_innodb = YES
have_ndbcluster = NO
have_openssl = DISABLED
have_partitioning = YES
have_profiling = YES
have_query_cache = YES
have_response_time_distribution = YES
have_rtree_keys = YES
have_ssl = DISABLED
have_symlink = YES
hostname = rhino0.forumhouse.ru
identity = 0
ignore_builtin_innodb = OFF
init_connect = 
init_file = 
init_slave = 
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_method = estimate
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_partitions = 1
innodb_additional_mem_pool_size = 8388608
innodb_autoextend_increment = 8
innodb_autoinc_lock_mode = 2
innodb_blocking_buffer_pool_restore = OFF
innodb_buffer_pool_instances = 1
innodb_buffer_pool_populate = OFF
innodb_buffer_pool_restore_at_startup = 0
innodb_buffer_pool_shm_checksum = ON
innodb_buffer_pool_shm_key = 0
innodb_buffer_pool_size = 85899345920
innodb_change_buffering = all
innodb_checkpoint_age_target = 0
innodb_checksums = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
innodb_corrupt_table_action = assert
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = 
innodb_dict_size_limit = 0
innodb_disallow_writes = OFF
innodb_doublewrite = ON
innodb_doublewrite_file = 
innodb_fake_changes = OFF
innodb_fast_checksum = OFF
innodb_fast_shutdown = 1
innodb_file_format = Antelope
innodb_file_format_check = ON
innodb_file_format_max = Antelope
innodb_file_per_table = OFF
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = 
innodb_flush_neighbor_pages = area
innodb_force_load_corrupted = OFF
innodb_force_recovery = 0
innodb_ibuf_accel_rate = 100
innodb_ibuf_active_contract = 1
innodb_ibuf_max_size = 42949656576
innodb_import_table_from_xtrabackup = 0
innodb_io_capacity = 200
innodb_kill_idle_transaction = 0
innodb_large_prefix = OFF
innodb_lazy_drop_table = 0
innodb_lock_wait_timeout = 50
innodb_locking_fake_changes = ON
innodb_locks_unsafe_for_binlog = OFF
innodb_log_block_size = 512
innodb_log_buffer_size = 8388608
innodb_log_file_size = 52428800
innodb_log_files_in_group = 2
innodb_log_group_home_dir = ./
innodb_max_bitmap_file_size = 104857600
innodb_max_changed_pages = 1000000
innodb_max_dirty_pages_pct = 75
innodb_max_purge_lag = 0
innodb_merge_sort_block_size = 1048576
innodb_mirrored_log_groups = 1
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 0
innodb_open_files = 300
innodb_page_size = 16384
innodb_print_all_deadlocks = OFF
innodb_purge_batch_size = 20
innodb_purge_threads = 1
innodb_random_read_ahead = OFF
innodb_read_ahead = linear
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_recovery_stats = OFF
innodb_recovery_update_relay_log = OFF
innodb_replication_delay = 0
innodb_rollback_on_timeout = OFF
innodb_rollback_segments = 128
innodb_show_locks_held = 10
innodb_show_verbose_locks = 0
innodb_spin_wait_delay = 6
innodb_stats_auto_update = 1
innodb_stats_method = nulls_equal
innodb_stats_on_metadata = ON
innodb_stats_sample_pages = 8
innodb_stats_update_need_lock = 1
innodb_strict_mode = OFF
innodb_support_xa = ON
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_thread_concurrency_timer_based = OFF
innodb_thread_sleep_delay = 10000
innodb_track_changed_pages = OFF
innodb_use_atomic_writes = OFF
innodb_use_global_flush_log_at_trx_commit = ON
innodb_use_native_aio = ON
innodb_use_sys_malloc = ON
innodb_use_sys_stats_table = OFF
innodb_version = 5.5.37-35.0-35.0
innodb_write_io_threads = 4
insert_id = 0
interactive_timeout = 28800
join_buffer_size = 131072
keep_files_on_create = OFF
key_buffer_size = 8388608
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
large_files_support = ON
large_page_size = 0
large_pages = OFF
last_insert_id = 0
lc_messages = en_US
lc_messages_dir = /usr/share/percona-xtradb-cluster/
lc_time_names = en_US
license = GPL
local_infile = ON
lock_wait_timeout = 31536000
locked_in_memory = OFF
log = OFF
log_bin = ON
log_bin_trust_function_creators = OFF
log_error = /var/log/mysqld.log
log_output = FILE
log_queries_not_using_indexes = OFF
log_slave_updates = OFF
log_slow_admin_statements = OFF
log_slow_filter = 
log_slow_queries = OFF
log_slow_rate_limit = 1
log_slow_rate_type = session
log_slow_slave_statements = OFF
log_slow_sp_statements = ON
log_slow_verbosity = 
log_warnings = 1
log_warnings_suppress = 
long_query_time = 10.000000
low_priority_updates = OFF
lower_case_file_system = OFF
lower_case_table_names = 0
max_allowed_packet = 1048576
max_binlog_cache_size = 18446744073709547520
max_binlog_files = 0
max_binlog_size = 1073741824
max_binlog_stmt_cache_size = 18446744073709547520
max_connect_errors = 10
max_connections = 151
max_delayed_threads = 20
max_error_count = 64
max_heap_table_size = 16777216
max_insert_delayed_threads = 20
max_join_size = 18446744073709551615
max_length_for_sort_data = 1024
max_long_data_size = 1048576
max_prepared_stmt_count = 16382
max_relay_log_size = 0
max_seeks_for_key = 18446744073709551615
max_slowlog_files = 0
max_slowlog_size = 0
max_sort_length = 1024
max_sp_recursion_depth = 0
max_tmp_tables = 32
max_user_connections = 0
max_write_lock_count = 18446744073709551615
metadata_locks_cache_size = 1024
min_examined_row_limit = 0
multi_range_count = 256
myisam_data_pointer_size = 6
myisam_max_sort_file_size = 9223372036853727232
myisam_mmap_size = 18446744073709551615
myisam_recover_options = OFF
myisam_repair_threads = 1
myisam_sort_buffer_size = 8388608
myisam_stats_method = nulls_unequal
myisam_use_mmap = OFF
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
new = OFF
old = OFF
old_alter_table = OFF
old_passwords = OFF
open_files_limit = 1024
optimizer_fix = ON
optimizer_prune_level = 1
optimizer_search_depth = 62
optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema = OFF
performance_schema_events_waits_history_long_size = 10000
performance_schema_events_waits_history_size = 10
performance_schema_max_cond_classes = 80
performance_schema_max_cond_instances = 1000
performance_schema_max_file_classes = 50
performance_schema_max_file_handles = 32768
performance_schema_max_file_instances = 10000
performance_schema_max_mutex_classes = 200
performance_schema_max_mutex_instances = 1000000
performance_schema_max_rwlock_classes = 30
performance_schema_max_rwlock_instances = 1000000
performance_schema_max_table_handles = 100000
performance_schema_max_table_instances = 50000
performance_schema_max_thread_classes = 50
performance_schema_max_thread_instances = 1000
pid_file = /var/lib/mysql/rhino0.forumhouse.ru.pid
plugin_dir = /usr/lib64/mysql/plugin/
port = 3306
preload_buffer_size = 32768
profiling = OFF
profiling_history_size = 15
protocol_version = 10
proxy_user = 
pseudo_slave_mode = OFF
pseudo_thread_id = 20749
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_min_res_unit = 4096
query_cache_size = 0
query_cache_strip_comments = OFF
query_cache_type = ON
query_cache_wlock_invalidate = OFF
query_prealloc_size = 8192
query_response_time_range_base = 10
query_response_time_stats = OFF
rand_seed1 = 0
rand_seed2 = 0
range_alloc_block_size = 4096
read_buffer_size = 131072
read_only = OFF
read_rnd_buffer_size = 262144
relay_log = 
relay_log_index = 
relay_log_info_file = relay-log.info
relay_log_purge = ON
relay_log_recovery = OFF
relay_log_space_limit = 0
report_host = 
report_password = 
report_port = 3306
report_user = 
rpl_recovery_rank = 0
secure_auth = OFF
secure_file_priv = 
server_id = 101
skip_external_locking = ON
skip_name_resolve = OFF
skip_networking = OFF
skip_show_database = OFF
slave_compressed_protocol = OFF
slave_exec_mode = STRICT
slave_load_tmpdir = /tmp
slave_max_allowed_packet = 1073741824
slave_net_timeout = 3600
slave_skip_errors = OFF
slave_transaction_retries = 10
slave_type_conversions = 
slow_launch_time = 2
slow_query_log = OFF
slow_query_log_always_write_time = 10.000000
slow_query_log_file = /var/lib/mysql/rhino0-slow.log
slow_query_log_timestamp_always = OFF
slow_query_log_timestamp_precision = second
slow_query_log_use_global_control = 
socket = /var/lib/mysql/mysql.sock
sort_buffer_size = 2097152
sql_auto_is_null = OFF
sql_big_selects = ON
sql_big_tables = OFF
sql_buffer_result = OFF
sql_log_bin = ON
sql_log_off = OFF
sql_low_priority_updates = OFF
sql_max_join_size = 18446744073709551615
sql_mode = 
sql_notes = ON
sql_quote_show_create = ON
sql_safe_updates = OFF
sql_select_limit = 18446744073709551615
sql_slave_skip_counter = 0
sql_warnings = OFF
ssl_ca = 
ssl_capath = 
ssl_cert = 
ssl_cipher = 
ssl_key = 
storage_engine = InnoDB
stored_program_cache = 256
sync_binlog = 1
sync_frm = ON
sync_master_info = 0
sync_relay_log = 0
sync_relay_log_info = 0
system_time_zone = MSK
table_definition_cache = 400
table_open_cache = 400
thread_cache_size = 0
thread_concurrency = 10
thread_handling = one-thread-per-connection
thread_pool_high_prio_mode = transactions
thread_pool_high_prio_tickets = 4294967295
thread_pool_idle_timeout = 60
thread_pool_max_threads = 100000
thread_pool_oversubscribe = 3
thread_pool_size = 24
thread_pool_stall_limit = 500
thread_stack = 262144
thread_statistics = OFF
time_format = %H:%i:%s
time_zone = SYSTEM
timed_mutexes = OFF
timestamp = 1406284321
tmp_table_size = 16777216
tmpdir = /tmp
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
tx_isolation = REPEATABLE-READ
unique_checks = ON
updatable_views_with_limit = YES
userstat = OFF
version = 5.5.37-35.0-55-log
version_comment = Percona XtraDB Cluster (GPL), Release rel35.0, Revision 756, WSREP version 25.10, wsrep_25.10.r3985
version_compile_machine = x86_64
version_compile_os = Linux
wait_timeout = 28800
warning_count = 0
wsrep_OSU_method = TOI
wsrep_auto_increment_control = ON
wsrep_causal_reads = OFF
wsrep_certify_nonPK = ON
wsrep_cluster_address = gcomm://192.168.100.20,192.168.100.66
wsrep_cluster_name = fh_cluster
wsrep_convert_LOCK_to_trx = OFF
wsrep_data_home_dir = /var/lib/mysql/
wsrep_dbug_option = 
wsrep_debug = OFF
wsrep_desync = OFF
wsrep_drupal_282555_workaround = OFF
wsrep_forced_binlog_format = NONE
wsrep_load_data_splitting = ON
wsrep_log_conflicts = OFF
wsrep_max_ws_rows = 131072
wsrep_max_ws_size = 1073741824
wsrep_mysql_replication_bundle = 0
wsrep_node_address = 192.168.100.20
wsrep_node_incoming_address = AUTO
wsrep_node_name = rhino0.forumhouse.ru
wsrep_notify_cmd = 
wsrep_on = ON
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_provider_options = base_host = 192.168.100.20; base_port = 4567; cert.log_conflicts = no; debug = no; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT15S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 1; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.100.20; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; 
wsrep_recover = OFF
wsrep_reject_queries = NONE
wsrep_replicate_myisam = OFF
wsrep_restart_slave = OFF
wsrep_retry_autocommit = 1
wsrep_slave_threads = 1
wsrep_sst_auth = ********
wsrep_sst_donor = 
wsrep_sst_donor_rejects_queries = OFF
wsrep_sst_method = xtrabackup-v2
wsrep_sst_receive_address = AUTO
wsrep_start_position = 025e2495-11ae-11e4-9bd8-b7ff319b2780:9

1 Answer 1

1

pt-online-schema-change requires setting triggers on a table. MySQL (prior to version 5.7) only allows one trigger per event (before insert, after insert etc.) This error is informing you know that a trigger is already defined, and pt-online-schema-change is prevented from running.

tl;dr: You need to remove your triggers so the tool can run. With MySQL 5.7+ this will not be required.

1
  • The table didn't have any triggers prior to executing pt-online-schema-change. Jul 29, 2014 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.