[MDEV-6871] Multi-value insert on MyISAM table that makes slaves crash Created: 2014-10-15  Updated: 2014-12-15  Due: 2014-11-10  Resolved: 2014-12-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - MyISAM
Affects Version/s: 5.5.40
Fix Version/s: 5.5.41

Type: Bug Priority: Minor
Reporter: Eric Webster Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian 7, MariaDB 5.5.40 from your repository


 Description   

This insert will make all slaves crash if run on a MyISAM table. If the table is InnoDB it works fine. Statement based or row based replication doesn't seem to matter.

# Master
 
MariaDB [test]> CREATE TABLE `test` (
 
    ->   `col1` int(11) NOT NULL,
 
    ->   `col2` int(11) NOT NULL,
 
    ->   PRIMARY KEY (`col1`,`col2`),
 
    ->   KEY `col2` (`col2`)
 
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
# Both
 
MariaDB [test]> show global variables like '%packet%';
 
+--------------------------+------------+
 
| Variable_name            | Value      |
 
+--------------------------+------------+
 
| max_allowed_packet       | 1073741824 |
 
| slave_max_allowed_packet | 1073741824 |
 
+--------------------------+------------+
 
2 rows in set (0.00 sec)
 
 
 
INSERT INTO `test` VALUES (2775,974),(2775,975),(2775,976),(2778,977),(2778,978),(2782,979),(2790,986),(2790,1139),(2792,840),(2792,984),(2792,989),(2793,982),(2793,992),(2793,993),(2793,994),(2795,323),(2795,332),(2797,980),(2797,997),(2797,998),(2798,1103),(2798,1104),(2799,841),(2799,985),(2799,988),(2833,983),(2833,990),(2833,991),(2834,981),(2834,995),(2834,996),(2835,316),(2835,317),(3007,854),(3007,856),(3008,855),(3008,857),(3009,823),(3009,824),(3014,1),(3015,1),(3016,2),(3017,2),(3018,3),(3019,3),(3024,842),(3024,843),(3024,844),(3025,845),(3025,846),(3025,847),(3040,31),(3041,32),(3042,52),(3042,55),(3043,53),(3043,54),(3044,278),(3044,279),(3044,280),(3044,281),(3044,282),(3044,283),(3044,284),(3044,285),(3045,1),(3046,1),(3049,220),(3050,221),(3050,222),(3051,2),(3052,2),(3053,223),(3054,224),(3055,225),(3056,226),(3057,227),(3058,228),(3059,229),(3060,327),(3066,236),(3067,237),(3068,238),(3069,239),(3070,240),(3080,241),(3081,242),(3082,247),(3083,248),(3084,249),(3085,250),(3086,251),(3087,252),(3088,253),(3089,254),(3090,255),(3091,256),(3092,257),(3093,258),(3094,259),(3096,263),(3097,264),(3100,273),(3100,302),(3101,266),(3102,267),(3103,268),(3104,269),(3105,270),(3111,275),(3112,238),(3113,272),(3115,286),(3116,318),(3116,319),(3117,290),(3117,292),(3118,238),(3119,291),(3119,293),(3120,304),(3121,305),(3122,306),(3123,307),(3124,308),(3125,309),(3126,310),(3127,311),(3128,312),(3128,336),(3129,313),(3129,350),(3130,314),(3131,315),(3131,351),(3132,325),(3132,328),(3134,502),(3138,334),(3139,338),(3139,339),(3140,340),(3140,341),(3141,344),(3141,345),(3142,346),(3142,347),(3149,351),(3149,354),(3150,351),(3150,356),(3152,358),(3152,359),(3153,361),(3153,370),(3154,363),(3154,369),(3156,350),(3156,371),(3159,376),(3160,377),(3160,379),(3160,384),(3161,378),(3161,380),(3161,383),(3162,388),(3162,389),(3162,390),(3169,392),(3169,393),(3169,394),(3170,395),(3170,396),(3170,397),(3171,398),(3171,399),(3171,400),(3172,401),(3172,402),(3172,403),(3173,404),(3173,405),(3173,406),(3178,351),(3178,421),(3190,411),(3190,412),(3191,413),(3191,414),(3192,415),(3192,416),(3193,417),(3193,418),(3194,419),(3194,420),(3195,353),(3195,424),(3196,425),(3196,426),(3197,427),(3197,428),(3198,429),(3198,430),(3199,431),(3199,432),(3200,433),(3200,434),(3201,435),(3201,436),(3202,437),(3202,438),(3203,439),(3203,440),(3204,441),(3204,442),(3205,443),(3205,444),(3206,445),(3206,446),(3207,447),(3207,448),(3208,449),(3208,450),(3209,451),(3209,452),(3210,453),(3210,454),(3211,455),(3211,456),(3212,457),(3212,458),(3213,459),(3213,460),(3214,461),(3214,462),(3215,463),(3215,464),(3218,466),(3218,467),(3218,468),(3219,469),(3219,470),(3219,471),(3220,474),(3220,475),(3220,476),(3221,477),(3221,478),(3221,479),(3222,480),(3222,481),(3223,482),(3223,483),(3224,484),(3224,485),(3225,486),(3225,487),(3227,503),(3227,505),(3228,506),(3228,507),(3230,508),(3230,509),(3231,510),(3231,511),(3232,512),(3232,513),(3233,514),(3233,515),(3234,516),(3234,517),(3235,518),(3235,519),(3237,521),(3237,522),(3239,524),(3239,525),(3240,526),(3240,527),(3241,528),(3241,529),(3242,530),(3242,531),(3243,532),(3243,533),(3244,534),(3244,535),(3245,536),(3245,537),(3246,538),(3246,539),(3252,540),(3252,541),(3254,543),(3254,544),(3254,545),(3255,547),(3255,548),(3255,571),(3256,550),(3256,551),(3256,572),(3257,553),(3257,554),(3257,573),(3258,556),(3258,557),(3258,574),(3259,559),(3259,560),(3259,575),(3260,561),(3260,562),(3260,563),(3261,565),(3261,576),(3262,566),(3262,567),(3263,568),(3263,569),(3263,570),(3264,577),(3264,578),(3265,579),(3265,580),(3266,581),(3266,582),(3266,591),(3267,583),(3267,584),(3267,592),(3268,585),(3268,586),(3268,593),(3269,587),(3269,588),(3269,594),(3270,589),(3270,590),(3271,595),(3271,596),(3271,597),(3272,598),(3272,599),(3273,600),(3273,601),(3273,602),(3274,603),(3274,604),(3274,605),(3275,606),(3275,607),(3275,608),(3276,609),(3276,610),(3276,611),(3277,612),(3277,613),(3277,614),(3278,615),(3278,616),(3279,617),(3279,618),(3279,619),(3279,628),(3279,629),(3280,620),(3280,621),(3280,622),(3281,623),(3281,624),(3281,625),(3282,626),(3282,825),(3283,630),(3283,631),(3284,632),(3284,633),(3284,634),(3285,635),(3285,940),(3286,638),(3286,639),(3286,640),(3287,641),(3287,642),(3287,643),(3288,644),(3288,645),(3288,646),(3289,647),(3289,648),(3289,649),(3290,650),(3290,651),(3290,652),(3291,653),(3291,654),(3291,655),(3292,656),(3292,657),(3292,658),(3293,659),(3293,660),(3293,661),(3294,662),(3294,663),(3294,664),(3295,665),(3295,666),(3295,667),(3296,668),(3296,669),(3296,670),(3297,671),(3297,672),(3297,673),(3298,674),(3298,675),(3298,676),(3299,677),(3299,678),(3299,679),(3300,680),(3300,681),(3300,682),(3301,683),(3301,684),(3301,685),(3302,686),(3302,687),(3302,688),(3303,689),(3303,690),(3303,691),(3304,692),(3304,693),(3304,694),(3305,695),(3305,696),(3305,697),(3306,698),(3306,699),(3306,700),(3307,701),(3307,702),(3307,703),(3308,704),(3308,705),(3308,706),(3309,707),(3309,708),(3310,709),(3310,710),(3311,711),(3311,712),(3311,713),(3312,714),(3312,715),(3312,716),(3313,717),(3313,1167),(3314,720),(3314,721),(3314,722),(3315,723),(3315,724),(3315,725),(3316,726),(3316,727),(3316,728),(3317,729),(3317,730),(3317,731),(3318,732),(3318,733),(3318,734),(3319,735),(3319,736),(3319,737),(3320,738),(3320,739),(3320,740),(3321,741),(3321,742),(3322,743),(3322,744),(3323,745),(3323,746),(3323,747),(3324,748),(3324,749),(3324,750),(3325,751),(3325,752),(3325,753),(3326,754),(3326,755),(3327,756),(3327,757),(3328,758),(3328,789),(3329,761),(3329,790),(3330,762),(3330,763),(3331,768),(3331,785),(3331,786),(3332,769),(3332,783),(3332,784),(3335,766),(3336,767),(3343,770),(3343,771),(3344,772),(3344,773),(3345,774),(3345,775),(3347,776),(3347,777),(3347,987),(3348,778),(3348,779),(3349,780),(3372,781),(3372,782),(3373,787),(3373,788),(3376,791),(3376,792),(3377,793),(3377,794),(3378,799),(3378,800),(3379,801),(3379,802),(3380,795),(3380,796),(3381,797),(3381,798),(3383,805),(3384,806),(3384,807),(3385,808),(3385,809),(3386,810),(3386,811),(3387,812),(3387,814),(3388,815),(3388,816),(3391,817),(3391,818),(3391,819),(3392,820),(3392,821),(3392,822),(3393,826),(3393,827),(3394,828),(3394,829),(3395,830),(3395,831),(3396,834),(3396,835),(3397,832),(3397,833),(3398,836),(3398,837),(3399,838),(3399,839),(3410,850),(3410,851),(3411,852),(3411,853),(3412,848),(3412,849),(3419,860),(3419,951),(3420,859),(3420,861),(3422,862),(3422,863),(3423,864),(3423,865),(3424,866),(3424,867),(3424,872),(3424,873),(3425,868),(3425,869),(3425,874),(3425,875),(3426,878),(3426,879),(3427,876),(3427,877),(3428,880),(3432,884),(3432,885),(3432,886),(3434,887),(3434,888),(3434,889),(3441,894),(3441,895),(3442,896),(3442,897),(3444,904),(3445,905),(3449,906),(3449,907),(3450,908),(3450,909),(3453,910),(3458,915),(3458,916),(3459,917),(3459,918),(3463,919),(3463,920),(3485,929),(3486,930),(3487,931),(3488,932),(3489,933),(3493,2),(3494,2),(3501,934),(3502,936),(3503,938),(3504,939),(3505,941),(3506,942),(3507,943),(3508,944),(3509,945),(3510,946),(3511,947),(3512,948),(3514,949),(3514,950),(3515,953),(3516,954),(3517,955),(3518,956),(3519,957),(3520,958),(3521,959),(3527,960),(3527,965),(3528,961),(3528,962),(3529,963),(3529,964),(3530,966),(3530,967),(3531,968),(3531,969),(3535,970),(3535,971),(3536,972),(3536,973),(3540,999),(3540,1000),(3541,1001),(8888,9999);
 
 
 
# Results in
 
141014 20:28:09 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 5.5.40-MariaDB-1~wheezy-log
 
key_buffer_size=33554432
 
read_buffer_size=2097152
 
max_used_connections=2
 
max_threads=1502
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 15440031 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f20a2c12000
 
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 = 0x7f20cec7f970 thread_stack 0x40000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f20cf5d4f3b]
 
/usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7f20cf20dc12]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf030)[0x7f20ce906030]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x1235c8)[0x7f20cd2545c8]
 
/usr/sbin/mysqld(+0x8507e5)[0x7f20cf5857e5]
 
/usr/sbin/mysqld(+0x851a51)[0x7f20cf586a51]
 
/usr/sbin/mysqld(+0x851cca)[0x7f20cf586cca]
 
/usr/sbin/mysqld(+0x851c78)[0x7f20cf586c78]
 
/usr/sbin/mysqld(+0x852121)[0x7f20cf587121]
 
/usr/sbin/mysqld(+0x850bbd)[0x7f20cf585bbd]
 
/usr/sbin/mysqld(_ZN7handler12ha_write_rowEPh+0xb1)[0x7f20cf215541]
 
/usr/sbin/mysqld(_ZN14Rows_log_event9write_rowEPK14Relay_log_infob+0x145)[0x7f20cf2ccbf5]
 
/usr/sbin/mysqld(_ZN20Write_rows_log_event11do_exec_rowEPK14Relay_log_info+0x22)[0x7f20cf2ccfd2]
 
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x252)[0x7f20cf2c2952]
 
/usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_info+0x17d)[0x7f20cf06eedd]
 
/usr/sbin/mysqld(handle_slave_sql+0x830)[0x7f20cf06fd40]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7f20ce8fdb50]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f20cd20ce6d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x0): is an invalid pointer
 
Connection ID (thread ID): 1
 
Status: NOT_KILLED

Remove any one record to make it 669 total and it works fine. However, it doesn't seem to be just any data. An insert generated with something like:

for I in $(seq 1000 1670); do echo -n "($I,$I),"; done

Works just fine. However, the statement I've provided above will crash it without fail. Remove the secondary index and it also works just fine. The insert was taken from a mysqldump of another 5.5 host. I backed out the amount it was inserting until I found where it didn't crash.

 Comments   
Comment by Elena Stepanova [ 2014-10-15 ]

Hi Eric,

Could you please attach complete cnf files from master and slave, or output of SHOW VARIABLES?

Is the server also 5.5.40, are both of them Debian 7 64-bit?

Does the slave crash again when you try to restart it (and it attempts to replay the same event from the master, already written to the binlog but not executed on the slave yet, due to the crash)?

(So far I couldn't reproduce it, hence the additional questions).

Comment by Eric Webster [ 2014-10-15 ]

They are both 5.5.40 and Debian 64-bit. I originally hit the issue on 5.5.39 but upgraded and tested on 5.5.40 to make sure it wasn't fixed in a later version if an actual bug.

I am using mysql-multi if that's relevant, with this setup:

[mysqld_safe]
 
ledir                    = /usr/local/sbin
 
mysqld                   = mysqld_using_numactl
 
 
 
root@host:/usr/local/sbin$cat mysqld_using_numactl 
#!/bin/bash
 
# work around to startup mysqld using numactl
 
 
 
numactl=/usr/bin/numactl
 
mysqld=/usr/sbin/mysqld
 
 
 
# use exec to avoid having an extra shell around.
 
exec ${numactl} --interleave all ${mysqld} "$@"

Show variables output from master:

aria_block_size                                    8192
 
aria_checkpoint_interval                           30
 
aria_checkpoint_log_activity                       1048576
 
aria_force_start_after_recovery_failures           0
 
aria_group_commit                                  none
 
aria_group_commit_interval                         0
 
aria_log_file_size                                 1073741824
 
aria_log_purge_type                                immediate
 
aria_max_sort_file_size                            9223372036853727232
 
aria_page_checksum                                 ON
 
aria_pagecache_age_threshold                       300
 
aria_pagecache_buffer_size                         134217728
 
aria_pagecache_division_limit                      100
 
aria_recover                                       NORMAL
 
aria_repair_threads                                1
 
aria_sort_buffer_size                              134217728
 
aria_stats_method                                  nulls_unequal
 
aria_sync_log_dir                                  NEWFILE
 
aria_used_for_temp_tables                          ON
 
auto_increment_increment                           1
 
auto_increment_offset                              1
 
autocommit                                         ON
 
automatic_sp_privileges                            ON
 
back_log                                           50
 
basedir                                            /usr
 
big_tables                                         OFF
 
binlog_annotate_row_events                         ON
 
binlog_cache_size                                  32768
 
binlog_checksum                                    NONE
 
binlog_direct_non_transactional_updates            OFF
 
binlog_format                                      ROW
 
binlog_optimize_thread_scheduling                  ON
 
binlog_stmt_cache_size                             32768
 
bulk_insert_buffer_size                            8388608
 
character_set_client                               utf8
 
character_set_connection                           utf8
 
character_set_database                             latin1
 
character_set_filesystem                           binary
 
character_set_results                              utf8
 
character_set_server                               latin1
 
character_set_system                               utf8
 
character_sets_dir                                 /usr/share/mysql/charsets/
 
collation_connection                               utf8_general_ci
 
collation_database                                 latin1_swedish_ci
 
collation_server                                   latin1_swedish_ci
 
completion_type                                    NO_CHAIN
 
concurrent_insert                                  AUTO
 
connect_timeout                                    10
 
datadir                                            /data/mysql-1/
 
date_format                                        %Y-%m-%d
 
datetime_format                                    %Y-%m-%d                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             %H:%i:%s
 
deadlock_search_depth_long                         15
 
deadlock_search_depth_short                        4
 
deadlock_timeout_long                              50000000
 
deadlock_timeout_short                             10000
 
debug_no_thread_alarm                              OFF
 
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                            2
 
engine_condition_pushdown                          OFF
 
error_count                                        0
 
event_scheduler                                    OFF
 
expensive_subquery_limit                           100
 
expire_logs_days                                   3
 
external_user
 
extra_max_connections                              1
 
extra_port                                         0
 
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                                   /data/mysql-1/mysqld.log
 
group_concat_max_len                               1024
 
have_compress                                      YES
 
have_crypt                                         YES
 
have_csv                                           YES
 
have_dynamic_loading                               YES
 
have_geometry                                      YES
 
have_innodb                                        YES
 
have_ndbcluster                                    NO
 
have_openssl                                       DISABLED
 
have_partitioning                                  YES
 
have_profiling                                     YES
 
have_query_cache                                   YES
 
have_rtree_keys                                    YES
 
have_ssl                                           DISABLED
 
have_symlink                                       YES
 
identity                                           0
 
ignore_builtin_innodb                              OFF
 
ignore_db_dirs
 
in_transaction                                     0
 
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                           1
 
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                            134217728
 
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_doublewrite                                 ON
 
innodb_doublewrite_file
 
innodb_fake_changes                                OFF
 
innodb_fast_checksum                               OFF
 
innodb_fast_shutdown                               1
 
innodb_file_format                                 Barracuda
 
innodb_file_format_check                           ON
 
innodb_file_format_max                             Barracuda
 
innodb_file_per_table                              ON
 
innodb_flush_log_at_trx_commit                     2
 
innodb_flush_method                                O_DIRECT
 
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                               67092480
 
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                               536870912
 
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                             1000
 
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_simulate_comp_failures                      0
 
innodb_spin_wait_delay                             6
 
innodb_stats_auto_update                           1
 
innodb_stats_method                                nulls_equal
 
innodb_stats_on_metadata                           OFF
 
innodb_stats_sample_pages                          8
 
innodb_stats_update_need_lock                      1
 
innodb_strict_mode                                 ON
 
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_fallocate                               OFF
 
innodb_use_global_flush_log_at_trx_commit          ON
 
innodb_use_native_aio                              ON
 
innodb_use_stacktrace                              OFF
 
innodb_use_sys_malloc                              ON
 
innodb_use_sys_stats_table                         OFF
 
innodb_version                                     5.5.40-MariaDB-36.1
 
innodb_write_io_threads                            4
 
insert_id                                          0
 
interactive_timeout                                28800
 
join_buffer_size                                   8388608
 
join_buffer_space_limit                            2097152
 
join_cache_level                                   2
 
keep_files_on_create                               OFF
 
key_buffer_size                                    33554432
 
key_cache_age_threshold                            300
 
key_cache_block_size                               1024
 
key_cache_division_limit                           100
 
key_cache_segments                                 0
 
large_files_support                                ON
 
large_page_size                                    0
 
large_pages                                        OFF
 
last_insert_id                                     0
 
lc_messages                                        en_US
 
lc_messages_dir                                    /usr/share/mysql
 
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                                          /data/mysql-1/mysqld.err
 
log_output                                         FILE
 
log_queries_not_using_indexes                      OFF
 
log_slave_updates                                  ON
 
log_slow_filter                                    admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
 
log_slow_queries                                   ON
 
log_slow_rate_limit                                1
 
log_slow_verbosity                                 query_plan
 
log_warnings                                       2
 
long_query_time                                    1.000000
 
low_priority_updates                               OFF
 
lower_case_file_system                             OFF
 
lower_case_table_names                             0
 
master_verify_checksum                             OFF
 
max_allowed_packet                                 1073741824
 
max_binlog_cache_size                              18446744073709547520
 
max_binlog_size                                    104857600
 
max_binlog_stmt_cache_size                         18446744073709547520
 
max_connect_errors                                 100000
 
max_connections                                    1500
 
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                                 1073741824
 
max_prepared_stmt_count                            16382
 
max_relay_log_size                                 0
 
max_seeks_for_key                                  4294967295
 
max_sort_length                                    1024
 
max_sp_recursion_depth                             0
 
max_tmp_tables                                     32
 
max_user_connections                               0
 
max_write_lock_count                               4294967295
 
metadata_locks_cache_size                          1024
 
min_examined_row_limit                             0
 
mrr_buffer_size                                    262144
 
multi_range_count                                  256
 
myisam_block_size                                  1024
 
myisam_data_pointer_size                           6
 
myisam_max_sort_file_size                          9223372036853727232
 
myisam_mmap_size                                   18446744073709551615
 
myisam_recover_options                             BACKUP
 
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
 
old                                                OFF
 
old_alter_table                                    OFF
 
old_mode
 
old_passwords                                      ON
 
open_files_limit                                   30000
 
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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
 
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                                           /data/mysql-1/mysqld.pid
 
plugin_dir                                         /usr/lib/mysql/plugin/
 
plugin_maturity                                    unknown
 
port                                               3306
 
preload_buffer_size                                32768
 
profiling                                          OFF
 
profiling_history_size                             15
 
progress_report_time                               5
 
protocol_version                                   10
 
proxy_user
 
pseudo_slave_mode                                  OFF
 
pseudo_thread_id                                   11
 
query_alloc_block_size                             8192
 
query_cache_limit                                  1048576
 
query_cache_min_res_unit                           4096
 
query_cache_size                                   8388608
 
query_cache_strip_comments                         OFF
 
query_cache_type                                   ON
 
query_cache_wlock_invalidate                       OFF
 
query_prealloc_size                                8192
 
rand_seed1                                         0
 
rand_seed2                                         0
 
range_alloc_block_size                             4096
 
read_buffer_size                                   2097152
 
read_only                                          ON
 
read_rnd_buffer_size                               262144
 
relay_log                                          relay-bin
 
relay_log_index                                    relay-bin.index
 
relay_log_info_file                                relay-log.info
 
relay_log_purge                                    ON
 
relay_log_recovery                                 OFF
 
relay_log_space_limit                              0
 
replicate_annotate_row_events                      ON
 
replicate_do_db
 
replicate_do_table
 
replicate_events_marked_for_skip                   replicate
 
replicate_ignore_db
 
replicate_ignore_table
 
replicate_wild_do_table
 
replicate_wild_ignore_table                        mysql.general_log,mysql.slow_log,localdata.%,lb_health.%
 
report_password
 
report_port                                        3306
 
report_user
 
rowid_merge_buff_size                              8388608
 
rpl_recovery_rank                                  0
 
secure_auth                                        OFF
 
secure_file_priv
 
server_id                                          3632211750
 
skip_external_locking                              OFF
 
skip_name_resolve                                  OFF
 
skip_networking                                    OFF
 
skip_replication                                   OFF
 
skip_show_database                                 OFF
 
slave_compressed_protocol                          OFF
 
slave_exec_mode                                    STRICT
 
slave_load_tmpdir                                  /data/mysql-1/tmp
 
slave_max_allowed_packet                           1073741824
 
slave_net_timeout                                  3600
 
slave_skip_errors                                  OFF
 
slave_sql_verify_checksum                          ON
 
slave_transaction_retries                          10
 
slave_type_conversions
 
slow_launch_time                                   2
 
slow_query_log                                     ON
 
slow_query_log_file                                /data/mysql-1/slow_queries.log
 
socket                                             /tmp/mysql-1.sock
 
sort_buffer_size                                   8388608
 
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                                        0
 
sync_frm                                           ON
 
sync_master_info                                   0
 
sync_relay_log                                     0
 
sync_relay_log_info                                0
 
system_time_zone                                   GMT
 
table_definition_cache                             2048
 
table_open_cache                                   2048
 
thread_cache_size                                  16
 
thread_concurrency                                 10
 
thread_handling                                    one-thread-per-connection
 
thread_pool_idle_timeout                           60
 
thread_pool_max_threads                            500
 
thread_pool_oversubscribe                          3
 
thread_pool_size                                   24
 
thread_pool_stall_limit                            500
 
thread_stack                                       262144
 
time_format                                        %H:%i:%s
 
time_zone                                          SYSTEM
 
timed_mutexes                                      OFF
 
timestamp                                          1413399455.374560
 
tmp_table_size                                     16777216
 
tmpdir                                             /data/mysql-1/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.40-MariaDB-1~wheezy-log
 
version_comment                                    mariadb.org                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          binary        distribution
 
version_compile_machine                            x86_64
 
version_compile_os                                 debian-linux-gnu
 
wait_timeout                                       28800
 
warning_count                                      0

Show variables output from slave:

aria_block_size                                    8192
 
aria_checkpoint_interval                           30
 
aria_checkpoint_log_activity                       1048576
 
aria_force_start_after_recovery_failures           0
 
aria_group_commit                                  none
 
aria_group_commit_interval                         0
 
aria_log_file_size                                 1073741824
 
aria_log_purge_type                                immediate
 
aria_max_sort_file_size                            9223372036853727232
 
aria_page_checksum                                 ON
 
aria_pagecache_age_threshold                       300
 
aria_pagecache_buffer_size                         134217728
 
aria_pagecache_division_limit                      100
 
aria_recover                                       NORMAL
 
aria_repair_threads                                1
 
aria_sort_buffer_size                              134217728
 
aria_stats_method                                  nulls_unequal
 
aria_sync_log_dir                                  NEWFILE
 
aria_used_for_temp_tables                          ON
 
auto_increment_increment                           1
 
auto_increment_offset                              1
 
autocommit                                         ON
 
automatic_sp_privileges                            ON
 
back_log                                           50
 
basedir                                            /usr
 
big_tables                                         OFF
 
binlog_annotate_row_events                         OFF
 
binlog_cache_size                                  32768
 
binlog_checksum                                    NONE
 
binlog_direct_non_transactional_updates            OFF
 
binlog_format                                      STATEMENT
 
binlog_optimize_thread_scheduling                  ON
 
binlog_stmt_cache_size                             32768
 
bulk_insert_buffer_size                            16777216
 
character_set_client                               utf8
 
character_set_connection                           utf8
 
character_set_database                             latin1
 
character_set_filesystem                           binary
 
character_set_results                              utf8
 
character_set_server                               latin1
 
character_set_system                               utf8
 
character_sets_dir                                 /usr/share/mysql/charsets/
 
collation_connection                               utf8_general_ci
 
collation_database                                 latin1_swedish_ci
 
collation_server                                   latin1_swedish_ci
 
completion_type                                    NO_CHAIN
 
concurrent_insert                                  ALWAYS
 
connect_timeout                                    5
 
datadir                                            /data/mysql-1/
 
date_format                                        %Y-%m-%d
 
datetime_format                                    %Y-%m-%d                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             %H:%i:%s
 
deadlock_search_depth_long                         15
 
deadlock_search_depth_short                        4
 
deadlock_timeout_long                              50000000
 
deadlock_timeout_short                             10000
 
debug_no_thread_alarm                              OFF
 
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                            2
 
engine_condition_pushdown                          OFF
 
error_count                                        0
 
event_scheduler                                    OFF
 
expensive_subquery_limit                           100
 
expire_logs_days                                   3
 
external_user
 
extra_max_connections                              1
 
extra_port                                         0
 
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                                   /data/mysql-1/mysqld.log
 
group_concat_max_len                               1024
 
have_compress                                      YES
 
have_crypt                                         YES
 
have_csv                                           YES
 
have_dynamic_loading                               YES
 
have_geometry                                      YES
 
have_innodb                                        YES
 
have_ndbcluster                                    NO
 
have_openssl                                       DISABLED
 
have_partitioning                                  YES
 
have_profiling                                     YES
 
have_query_cache                                   YES
 
have_rtree_keys                                    YES
 
have_ssl                                           DISABLED
 
have_symlink                                       YES
 
identity                                           0
 
ignore_builtin_innodb                              OFF
 
ignore_db_dirs
 
in_transaction                                     0
 
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                           1
 
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                            268435456
 
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_doublewrite                                 ON
 
innodb_doublewrite_file
 
innodb_fake_changes                                OFF
 
innodb_fast_checksum                               OFF
 
innodb_fast_shutdown                               1
 
innodb_file_format                                 Barracuda
 
innodb_file_format_check                           ON
 
innodb_file_format_max                             Barracuda
 
innodb_file_per_table                              ON
 
innodb_flush_log_at_trx_commit                     2
 
innodb_flush_method                                O_DIRECT
 
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                               134201344
 
innodb_import_table_from_xtrabackup                0
 
innodb_io_capacity                                 400
 
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                               536870912
 
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                             1000
 
innodb_open_files                                  400
 
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_simulate_comp_failures                      0
 
innodb_spin_wait_delay                             6
 
innodb_stats_auto_update                           1
 
innodb_stats_method                                nulls_equal
 
innodb_stats_on_metadata                           OFF
 
innodb_stats_sample_pages                          8
 
innodb_stats_update_need_lock                      1
 
innodb_strict_mode                                 ON
 
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_fallocate                               OFF
 
innodb_use_global_flush_log_at_trx_commit          ON
 
innodb_use_native_aio                              ON
 
innodb_use_stacktrace                              OFF
 
innodb_use_sys_malloc                              ON
 
innodb_use_sys_stats_table                         OFF
 
innodb_version                                     5.5.40-MariaDB-36.1
 
innodb_write_io_threads                            4
 
insert_id                                          0
 
interactive_timeout                                28800
 
join_buffer_size                                   8388608
 
join_buffer_space_limit                            2097152
 
join_cache_level                                   2
 
keep_files_on_create                               OFF
 
key_buffer_size                                    33554432
 
key_cache_age_threshold                            300
 
key_cache_block_size                               1024
 
key_cache_division_limit                           100
 
key_cache_segments                                 0
 
large_files_support                                ON
 
large_page_size                                    0
 
large_pages                                        OFF
 
last_insert_id                                     0
 
lc_messages                                        en_US
 
lc_messages_dir                                    /usr/share/mysql
 
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                                          /data/mysql-1/mysqld.err
 
log_output                                         FILE
 
log_queries_not_using_indexes                      OFF
 
log_slave_updates                                  OFF
 
log_slow_filter                                    admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
 
log_slow_queries                                   ON
 
log_slow_rate_limit                                1
 
log_slow_verbosity                                 query_plan
 
log_warnings                                       2
 
long_query_time                                    1.000000
 
low_priority_updates                               OFF
 
lower_case_file_system                             OFF
 
lower_case_table_names                             0
 
master_verify_checksum                             OFF
 
max_allowed_packet                                 1073741824
 
max_binlog_cache_size                              18446744073709547520
 
max_binlog_size                                    104857600
 
max_binlog_stmt_cache_size                         18446744073709547520
 
max_connect_errors                                 100000
 
max_connections                                    1500
 
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                                 1073741824
 
max_prepared_stmt_count                            16382
 
max_relay_log_size                                 0
 
max_seeks_for_key                                  4294967295
 
max_sort_length                                    1024
 
max_sp_recursion_depth                             0
 
max_tmp_tables                                     32
 
max_user_connections                               0
 
max_write_lock_count                               4294967295
 
metadata_locks_cache_size                          1024
 
min_examined_row_limit                             0
 
mrr_buffer_size                                    262144
 
multi_range_count                                  256
 
myisam_block_size                                  1024
 
myisam_data_pointer_size                           6
 
myisam_max_sort_file_size                          9223372036853727232
 
myisam_mmap_size                                   18446744073709551615
 
myisam_recover_options                             BACKUP
 
myisam_repair_threads                              1
 
myisam_sort_buffer_size                            536870912
 
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
 
old                                                OFF
 
old_alter_table                                    OFF
 
old_mode
 
old_passwords                                      ON
 
open_files_limit                                   30000
 
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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
 
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                                           /data/mysql-1/mysqld.pid
 
plugin_dir                                         /usr/lib/mysql/plugin/
 
plugin_maturity                                    unknown
 
port                                               3306
 
preload_buffer_size                                32768
 
profiling                                          OFF
 
profiling_history_size                             15
 
progress_report_time                               5
 
protocol_version                                   10
 
proxy_user
 
pseudo_slave_mode                                  OFF
 
pseudo_thread_id                                   8
 
query_alloc_block_size                             8192
 
query_cache_limit                                  131072
 
query_cache_min_res_unit                           4096
 
query_cache_size                                   8388608
 
query_cache_strip_comments                         OFF
 
query_cache_type                                   ON
 
query_cache_wlock_invalidate                       OFF
 
query_prealloc_size                                8192
 
rand_seed1                                         0
 
rand_seed2                                         0
 
range_alloc_block_size                             4096
 
read_buffer_size                                   2097152
 
read_only                                          ON
 
read_rnd_buffer_size                               1048576
 
relay_log                                          relay-bin
 
relay_log_index                                    relay-bin.index
 
relay_log_info_file                                relay-log.info
 
relay_log_purge                                    ON
 
relay_log_recovery                                 OFF
 
relay_log_space_limit                              0
 
replicate_annotate_row_events                      OFF
 
replicate_do_db
 
replicate_do_table
 
replicate_events_marked_for_skip                   replicate
 
replicate_ignore_db
 
replicate_ignore_table
 
replicate_wild_do_table
 
replicate_wild_ignore_table                        mysql.general_log,mysql.slow_log,localdata.%,lb_health.%
 
report_password
 
report_port                                        3306
 
report_user
 
rowid_merge_buff_size                              8388608
 
rpl_recovery_rank                                  0
 
secure_auth                                        OFF
 
secure_file_priv
 
server_id                                          3632211764
 
skip_external_locking                              OFF
 
skip_name_resolve                                  OFF
 
skip_networking                                    OFF
 
skip_replication                                   OFF
 
skip_show_database                                 OFF
 
slave_compressed_protocol                          OFF
 
slave_exec_mode                                    STRICT
 
slave_load_tmpdir                                  /data/mysql-1/tmp
 
slave_max_allowed_packet                           1073741824
 
slave_net_timeout                                  3600
 
slave_skip_errors                                  OFF
 
slave_sql_verify_checksum                          ON
 
slave_transaction_retries                          10
 
slave_type_conversions
 
slow_launch_time                                   2
 
slow_query_log                                     ON
 
slow_query_log_file                                /data/mysql-1/slow_queries.log
 
socket                                             /tmp/mysql-1.sock
 
sort_buffer_size                                   8388608
 
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                                        0
 
sync_frm                                           ON
 
sync_master_info                                   0
 
sync_relay_log                                     0
 
sync_relay_log_info                                0
 
system_time_zone                                   GMT
 
table_definition_cache                             2048
 
table_open_cache                                   2048
 
thread_cache_size                                  16
 
thread_concurrency                                 10
 
thread_handling                                    one-thread-per-connection
 
thread_pool_idle_timeout                           60
 
thread_pool_max_threads                            500
 
thread_pool_oversubscribe                          3
 
thread_pool_size                                   24
 
thread_pool_stall_limit                            500
 
thread_stack                                       262144
 
time_format                                        %H:%i:%s
 
time_zone                                          SYSTEM
 
timed_mutexes                                      OFF
 
timestamp                                          1413399710.331742
 
tmp_table_size                                     16777216
 
tmpdir                                             /data/mysql-1/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.40-MariaDB-1~wheezy-log
 
version_comment                                    mariadb.org                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          binary        distribution
 
version_compile_machine                            x86_64
 
version_compile_os                                 debian-linux-gnu
 
wait_timeout                                       600
 
warning_count                                      0

When it crashes and restarts, it ends up with:

                   Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry '2775-974' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 8785

And if interesting, the slave ends up with:

Database changed
 
MariaDB [test]> select count(*) from test;
 
+----------+
 
| count(*) |
 
+----------+
 
|      620 |
 
+----------+
 
1 row in set (0.00 sec)

And the master of course is fine, the query completes:

MariaDB [test]> select count(*) from test;
 
+----------+
 
| count(*) |
 
+----------+
 
|      670 |
 
+----------+
 
1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2014-10-16 ]

Still no luck reproducing it. I'm also using Debian 7 64-bit, our 5.5.40 packages.

Is it a development server (as in non-production)?
Would you be able to run a debug binary and produce a coredump and a decent stack trace?
Just in case you would, I've uploaded the binary to ftp://ftp.askmonty.org/public/mdev-6871/ – it's built on the same machine as the release binaries, hopefully it will work smoothly.
Please make sure you're running the server with --core-file, and ulimit -c is set to unlimited.
If you get a coredump, please upload it to ftp://ftp.askmonty.org/private/.

On a separate note, is there a reason why you're using mysql-multi? Is there more than one server running on the same machine?

Does it always crash on the same value (like, from your counts above, it looks like it failed to insert 50 rows, is it always the case)?

Comment by Eric Webster [ 2014-10-17 ]

I used the debug binaries and uploaded the core file as you requested.

There is not more than one instance on this particular machine, but I have others where that is the case. We've just standardized on mysqld_multi.

Yes, it always crashes with the same count.

Comment by Elena Stepanova [ 2014-10-22 ]

Thanks a lot.

Stack trace from the debug coredump, 5.5 tag mariadb-5.5.40

 
#3  <signal handler called>
 
#4  __memcpy_ssse3 () at ../sysdeps/x86_64/multiarch/memcpy-ssse3.S:2648
 
#5  0x0000000000ae173b in _mi_balance_page (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, key=key@entry=0x7f46dd49eac6 "", curr_buff=0x7f46dd49e288  <incomplete sequence \362>, curr_buff@entry=0x7f470b80d8b0 "", father_buff=father_buff@entry=0x7f470b80ebb0 "\200\250", father_key_pos=0x7f470b80ec44 "", father_key_pos@entry=0x7f470b80ec58 "", father_page=father_page@entry=3072) at storage/myisam/mi_write.c:835
 
#6  0x0000000000ae2792 in _mi_insert (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, key=key@entry=0x7f46dd49eac6 "", anc_buff=anc_buff@entry=0x7f470b80d8b0 "", key_pos=0x7f470b80db60 "", key_buff=key_buff@entry=0x7f470b80e690 "", father_buff=father_buff@entry=0x7f470b80ebb0 "\200\250", father_key_pos=father_key_pos@entry=0x7f470b80ec58 "", father_page=father_page@entry=3072, insert_last=insert_last@entry=0 '\000') at storage/myisam/mi_write.c:581
 
#7  0x0000000000ae2c8e in w_search (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, comp_flag=comp_flag@entry=131137, key=key@entry=0x7f46dd49eac6 "", key_length=key_length@entry=8, page=19456, father_buff=father_buff@entry=0x7f470b80ebb0 "\200\250", father_keypos=0x7f470b80ec58 "", father_page=father_page@entry=3072, insert_last=0 '\000', insert_last@entry=1 '\001') at storage/myisam/mi_write.c:421
 
#8  0x0000000000ae2c42 in w_search (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, comp_flag=comp_flag@entry=131137, key=key@entry=0x7f46dd49eac6 "", key_length=key_length@entry=8, page=3072, father_buff=father_buff@entry=0x0, father_keypos=father_keypos@entry=0x0, father_page=father_page@entry=0, insert_last=insert_last@entry=1 '\001') at storage/myisam/mi_write.c:417
 
#9  0x0000000000ae2d92 in _mi_ck_real_write_btree (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, key=key@entry=0x7f46dd49eac6 "", key_length=key_length@entry=8, root=root@entry=0x7f46ddc342c8, comp_flag=131137) at storage/myisam/mi_write.c:295
 
#10 0x0000000000ae2e81 in _mi_ck_write_btree (info=info@entry=0x7f46dd49de60, keynr=keynr@entry=0, key=key@entry=0x7f46dd49eac6 "", key_length=key_length@entry=8) at storage/myisam/mi_write.c:276
 
#11 0x0000000000ae31ee in _mi_ck_write (info=info@entry=0x7f46dd49de60, keynr=keynr@entry=0, key=key@entry=0x7f46dd49eac6 "", key_length=8) at storage/myisam/mi_write.c:247
 
#12 0x0000000000ae38b0 in mi_write (info=0x7f46dd49de60, record=0x7f46dd41c578 "\377\203\r") at storage/myisam/mi_write.c:121
 
#13 0x0000000000ab0bf0 in ha_myisam::write_row (this=0x7f46dd47d078, buf=0x7f46dd41c578 "\377\203\r") at storage/myisam/ha_myisam.cc:852
 
#14 0x00000000006fcf09 in handler::ha_write_row (this=0x7f46dd47d078, buf=0x7f46dd41c578 "\377\203\r") at sql/handler.cc:5205
 
#15 0x00000000007b964d in Rows_log_event::write_row (this=this@entry=0x7f46ddc75220, rli=rli@entry=0x7f46f2d9a850, overwrite=false) at sql/log_event.cc:10046
 
#16 0x00000000007b9855 in Write_rows_log_event::do_exec_row (this=0x7f46ddc75220, rli=0x7f46f2d9a850) at sql/log_event.cc:10205
 
#17 0x00000000007ad5fd in Rows_log_event::do_apply_event (this=0x7f46ddc75220, rli=0x7f46f2d9a850) at sql/log_event.cc:8575
 
#18 0x000000000053fc63 in apply_event (rli=0x7f46f2d9a850, this=0x7f46ddc75220) at sql/log_event.h:1256
 
#19 apply_event_and_update_pos (ev=ev@entry=0x7f46ddc75220, thd=thd@entry=0x7f46ddc16060, rli=rli@entry=0x7f46f2d9a850) at sql/slave.cc:2658
 
#20 0x0000000000541791 in exec_relay_log_event (rli=0x7f46f2d9a850, thd=<optimized out>) at sql/slave.cc:2818
 
#21 handle_slave_sql (arg=0x7f46f2d99060) at sql/slave.cc:3677
 
#22 0x00007f470b4d0b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#23 0x00007f4709bd4e6d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Elena Stepanova [ 2014-10-22 ]

sophomeric

Does the crash also happen if you execute the same create/insert directly on the slave? (Obviously it doesn't happen on master, but slave and master have minor differences in configuration, so it would be good to rule out their relation to the problem).

Comment by Eric Webster [ 2014-10-22 ]

No, it works fine if executed on the slave directly.

Comment by Elena Stepanova [ 2014-10-27 ]

I see, thanks.

------------------
The immediate reason of the crash is quite clear from the coredump:

(gdb) frame 5
 
#5  0x0000000000ae173b in _mi_balance_page (info=info@entry=0x7f46dd49de60, keyinfo=keyinfo@entry=0x7f46ddc34048, 
    key=key@entry=0x7f46dd49eac6 "", curr_buff=0x7f46dd49e288  <incomplete sequence \362>, curr_buff@entry=0x7f470b80d8b0 "", 
    father_buff=father_buff@entry=0x7f470b80ebb0 "\200\250", father_key_pos=0x7f470b80ec44 "", 
    father_key_pos@entry=0x7f470b80ec58 "", father_page=father_page@entry=3072)
 
    at /home/buildbot/mariadb-5.5.40/storage/myisam/mi_write.c:835
 
835	      memcpy((uchar*) buff+2+length,father_key_pos,(size_t) k_length);
 
(gdb) list
 
830	    {						/* Move keys -> buff */
 
831	
832	      bmove_upp((uchar*) buff+new_right_length,(uchar*) buff+right_length,
 
833			right_length-2);
 
834	      length=new_right_length-right_length-k_length;
 
835	      memcpy((uchar*) buff+2+length,father_key_pos,(size_t) k_length);
 
836	      pos=curr_buff+new_left_length;
 
837	      memcpy((uchar*) father_key_pos,(uchar*) pos,(size_t) k_length);
 
838	      memcpy((uchar*) buff+2,(uchar*) pos+k_length,(size_t) length);
 
839	    }
 
(gdb) p length
 
$6 = 4294967276
 
(gdb) p new_right_length
 
$7 = 1024
 
(gdb) p right_length
 
$8 = 1030
 
(gdb) p k_length
 
$9 = 14

Nothing like this happens when I run the same test. My values are
length = 460
new_right_length = 572
right_length = 102
k_length = 10

I have no idea so far what ca cause the difference. I find the last value especially confusing (although maybe this difference is unrelated to the problem) – why would key length be different?.. Same version, same engine, same system and architecture, presumably same server options...

Comment by Elena Stepanova [ 2014-10-27 ]

sophomeric,

If possible, could you please run one more exercise?
With the same debug binary, please start the slave with --debug option, and then run the same test as before. The slave will create file /tmp/mysqld.trace. After the slave crashes, please upload the file to the same FTP. The file size will probably be some ~50 Mb.
If /tmp/ location is not suitable, you can change it by giving a value to the debug option: --debug=d:t:i:o,/tmp/mysqld.trace (replace /tmp/mysqld.trace with the desired location).

Thanks!

Comment by Michael Widenius [ 2014-10-29 ]

I am now looking into this. I have not been able to repeat it yet.
Having a dbug trace, as Elena described above, should enable me to find the problem (in most cases it does in cases like this).

One reason this works differently on the master and slave is that on the master, the first multi-value insert is optimized to use create-keys-by-sorting while on the slave the rows are inserted row by row. In other words, on the master we never execute
_mi_balance_page()

If you can't get us a debug trace, could you at least see if it fails on your master if you break the multi-value insert into two pieces:

INSERT INTO `test` VALUES (2775,974);
INSERT INTO test values (2775,975),(2775,976)....

If yes, this is MyISAM issue. If not, then this may be a replication issue.

Regards,
Monty

Comment by Eric Webster [ 2014-10-29 ]

I have not yet had the chance to get the debug trace, but the crash did not happen if the table was InnoDB.

Comment by Michael Widenius [ 2014-10-30 ]

I have studied this a bit more:

  • The crash happens in primary key
    (Which is strange as you said that this only happens if secondary key is used)
  • As all primary keys are inserted in increasing order in your test, we should never call _mi_balance_page() for primary key.

So something is very strange. This sounds almost like a compiler bug...
I will try to force a crash on my side by modifying the source to also call mi_balance_page for primary keys, but if that doesn't succeed, getting a debug trace would be the best way for me to be able to find the issue.

Comment by Michael Widenius [ 2014-10-30 ]

I have studied this a bit more:

  • The crash happens in primary key
    (Which is strange as you said that this only happens if secondary key is used)
  • As all primary keys are inserted in increasing order in your test, we should never call _mi_balance_page() for primary key.

So something is very strange. This sounds almost like a compiler bug...
I will try to force a crash on my side by modifying the source to also call mi_balance_page for primary keys, but if that doesn't succeed, getting a debug trace would be the best way for me to be able to find the issue.

Comment by Eric Webster [ 2014-10-31 ]

I've uploaded MDEV-6871.trace.gz to your FTP.

Comment by Michael Widenius [ 2014-11-09 ]

I looked at the trace file, but it doesn't show any failure. It ends with something that looks like a normal shutdown, not with a crash.
Is the trace really from a slave that crashed?

Comment by Eric Webster [ 2014-11-10 ]

It was, but when it crashes it restarts so the normal shutdown was probably me legitimately stopping it at the end. I've uploaded MDEV-6871.trace.bz2 with another trace from it crashing.

Comment by Michael Widenius [ 2014-11-18 ]

I looked at MDEV-6871.trace.bz2, but this is also shut down normally with a kill command.
The first trace you sent us, was 152M compressed. This could not come from a restart, so something is strange.

When you run the tests with --debug, did you check that you get the error "mysqld got signal ...' in the error log?

I am now back from travelling, so if I can get a new stack trace that shows the problem I can look at it straight away.

Comment by Eric Webster [ 2014-11-19 ]

I do get that error yes. I wonder if when mysqld_safe restarts it if it overwrites the debug file or appends to it? I did the same test and crash with --no-auto-restart on mysqld_safe so that the trace file MDEV-6871-2014-11-19-2.trace.bz2 should be the final crash.

Comment by Michael Widenius [ 2014-11-20 ]

The last trace captures "almost" the error, but it may be good enough for me to get an idea of what's going on.
I will investigate this on Friday.

When I say almost, the problem is that the last part of the log was not flushed before it crashed, so I lose a few K of data at the end.
Hopefully I will not need the last part. I will know soon.

If you have a time over you could fix this by running the slave with
--debug=:d:t:i:O,/tmp/mysqld.trace

This will flush the trace after each write; It will make the slave a bit slower but it will ensure that everything is in the log.

Thanks a lot for the help so far!

Comment by Eric Webster [ 2014-11-20 ]

MDEV-6871-dtio-trace.bz2 has been uploaded as you requested.

Comment by Michael Widenius [ 2014-11-26 ]

Found the bug, thanks to the latest trace file!

You have enabled the, not needed and not recommended option, --skip-external-locking=0. When this option is enabled, you get a core dump. If not, then things works.

This option was before used to protect against multiple servers using the same MyISAM files, something we supported a LONG time ago, but not really anymore.

Of course this should not cause a core dump; I will fix this ASAP.

However, in any case you should remove this option from your config files as it's not likely to be useful and it actually causes a notable performance degradation.

Thanks a lot for all the trace files!

Comment by Michael Widenius [ 2014-12-15 ]

Problem was that repair() did lock and unlock tables, which leaved already locked tables in wrong state.
This affected both MyISAM and Aria.
Fix pushed into 5.5 tree

Generated at Thu Feb 08 07:15:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.