I have a working Percona database server running multiple databases with mostly InnoDB tables (maybe a few hundred tables total). Any table that exists is working fine, yet I cannot convert any existing MyISAM tables or even create new InnoDB tables in any schema (even new fresh ones).
In fact I can trigger the crash (Error Code: 2013. Lost conenction to MySQL server during query) as minimally as this:
create schema testschema;
use testschema;
create table testtable (
testfield int(10)
);
(InnoDB is the default engine, and I get the same result making the engine explicit, while specifying MyISAM creates the table successfully.
What gives?
Some diagnostics:
cat /proc/$(pgrep mysqld$)/limits
...
Max open files 8192 8192 files
...
Starting from the crash, (large) error log has these possibly notable lines
2016-12-19 19:06:43 7f3880047700 InnoDB: Assertion failure in thread 139880642672384 in file fsp0fsp.cc line 1899
...
23:06:43 UTC - mysqld got signal 6 ;
...
stack_bottom = 7f3880046ea0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8f4b9b]
/usr/sbin/mysqld(handle_fatal_signal+0x378)[0x647468]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f388cf34390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f388c2d8428]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f388c2da02a]
/usr/sbin/mysqld[0x55f90b]
...
/usr/sbin/mysqld[0x9bfcd4]
/usr/sbin/mysqld(_Z15ha_create_tableP3THDPKcS2_S2_P24st_ha_create_informationbb+0x1fe)[0x58b9be]
/usr/sbin/mysqld(_Z16rea_create_tableP3THDPKcS2_S2_P24st_ha_create_informationR4ListI12Create_fieldEjP6st_keyP7handlerb+0x1d3)[0x771033]
/usr/sbin/mysqld[0x72bb5e]
/usr/sbin/mysqld(_Z26mysql_create_table_no_lockP3THDPKcS2_P24st_ha_create_informationP10Alter_infojPb+0x114)[0x72c734]
/usr/sbin/mysqld(_Z18mysql_create_tableP3THDP10TABLE_LISTP24st_ha_create_informationP10Alter_info+0xdf)[0x72c98f]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5696)[0x6d9596]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x604)[0x6daa64]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19c2)[0x6dc542]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x245)[0x6a0bf5]
/usr/sbin/mysqld(handle_one_connection+0x39)[0x6a0c49]
/usr/sbin/mysqld(pfs_spawn_thread+0x140)[0x98da00]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f388cf2a6ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f388c3a982d]
...
Query (7f3804004c10): is an invalid pointer
Connection ID (thread ID): 76
Status: NOT_KILLED
...
161219 19:06:43 mysqld_safe Number of processes running now: 0
161219 19:06:43 mysqld_safe mysqld restarted
A restart occurs which performs rollback and ens successfully, though the log does mention this InnoDB: Last MySQL binlog file position 0 18814, file name /storage/logs/mysql/mysql-bin.000005 and there is no binlog set up (this path no longer exists either).
ignore_builtin_innodb......................... OFF
innodb_adaptive_flushing...................... ON
innodb_adaptive_flushing_lwm.................. 10
innodb_adaptive_hash_index.................... ON
innodb_adaptive_hash_index_partitions......... 1
innodb_adaptive_max_sleep_delay............... 150000
innodb_additional_mem_pool_size............... 8388608
innodb_api_bk_commit_interval................. 5
innodb_api_disable_rowlock.................... OFF
innodb_api_enable_binlog...................... OFF
innodb_api_enable_mdl......................... OFF
innodb_api_trx_level.......................... 0
innodb_autoextend_increment................... 64
innodb_autoinc_lock_mode...................... 1
innodb_buffer_pool_dump_at_shutdown........... OFF
innodb_buffer_pool_dump_now................... OFF
innodb_buffer_pool_filename................... ib_buffer_pool
innodb_buffer_pool_instances.................. 8
innodb_buffer_pool_load_abort................. OFF
innodb_buffer_pool_load_at_startup............ OFF
innodb_buffer_pool_load_now................... OFF
innodb_buffer_pool_populate................... OFF
innodb_buffer_pool_size....................... 805306368
innodb_change_buffer_max_size................. 25
innodb_change_buffering....................... all
innodb_checksum_algorithm..................... innodb
innodb_checksums.............................. ON
innodb_cleaner_lsn_age_factor................. high_checkpoint
innodb_cmp_per_index_enabled.................. OFF
innodb_commit_concurrency..................... 0
innodb_compression_failure_threshold_pct...... 5
innodb_compression_level...................... 6
innodb_compression_pad_pct_max................ 50
innodb_concurrency_tickets.................... 5000
innodb_corrupt_table_action................... assert
innodb_data_file_path......................... ibdata1:10M:autoextend
innodb_data_home_dir..........................
innodb_disable_sort_file_cache................ OFF
innodb_doublewrite............................ ON
innodb_empty_free_list_algorithm.............. backoff
innodb_fake_changes........................... OFF
innodb_fast_shutdown.......................... 1
innodb_file_format............................ Antelope
innodb_file_format_check...................... ON
innodb_file_format_max........................ Antelope
innodb_file_per_table......................... ON
innodb_flush_log_at_timeout................... 1
innodb_flush_log_at_trx_commit................ 1
innodb_flush_method...........................
innodb_flush_neighbors........................ 1
innodb_flushing_avg_loops..................... 30
innodb_force_load_corrupted................... OFF
innodb_force_recovery......................... 0
innodb_foreground_preflush.................... exponential_backoff
innodb_ft_aux_table...........................
innodb_ft_cache_size.......................... 8000000
innodb_ft_enable_diag_print................... OFF
innodb_ft_enable_stopword..................... ON
innodb_ft_max_token_size...................... 84
innodb_ft_min_token_size...................... 3
innodb_ft_num_word_optimize................... 2000
innodb_ft_result_cache_limit.................. 2000000000
innodb_ft_server_stopword_table...............
innodb_ft_sort_pll_degree..................... 2
innodb_ft_total_cache_size.................... 640000000
innodb_ft_user_stopword_table.................
innodb_io_capacity............................ 200
innodb_io_capacity_max........................ 2000
innodb_kill_idle_transaction.................. 0
innodb_large_prefix........................... OFF
innodb_lock_wait_timeout...................... 50
innodb_locking_fake_changes................... ON
innodb_locks_unsafe_for_binlog................ OFF
innodb_log_arch_dir........................... ./
innodb_log_arch_expire_sec.................... 0
innodb_log_archive............................ OFF
innodb_log_block_size......................... 512
innodb_log_buffer_size........................ 8388608
innodb_log_checksum_algorithm................. innodb
innodb_log_compressed_pages................... ON
innodb_log_file_size.......................... 50331648
innodb_log_files_in_group..................... 2
innodb_log_group_home_dir..................... ./
innodb_lru_scan_depth......................... 1024
innodb_max_bitmap_file_size................... 104857600
innodb_max_changed_pages...................... 1000000
innodb_max_dirty_pages_pct.................... 75
innodb_max_dirty_pages_pct_lwm................ 0
innodb_max_purge_lag.......................... 0
innodb_max_purge_lag_delay.................... 0
innodb_mirrored_log_groups.................... 1
innodb_monitor_disable........................
innodb_monitor_enable.........................
innodb_monitor_reset..........................
innodb_monitor_reset_all......................
innodb_old_blocks_pct......................... 37
innodb_old_blocks_time........................ 1000
innodb_online_alter_log_max_size.............. 134217728
innodb_open_files............................. 512
innodb_optimize_fulltext_only................. OFF
innodb_page_size.............................. 16384
innodb_print_all_deadlocks.................... OFF
innodb_purge_batch_size....................... 300
innodb_purge_threads.......................... 1
innodb_random_read_ahead...................... OFF
innodb_read_ahead_threshold................... 56
innodb_read_io_threads........................ 4
innodb_read_only.............................. OFF
innodb_replication_delay...................... 0
innodb_rollback_on_timeout.................... OFF
innodb_rollback_segments...................... 128
innodb_sched_priority_cleaner................. 19
innodb_show_locks_held........................ 10
innodb_show_verbose_locks..................... 0
innodb_sort_buffer_size....................... 1048576
innodb_spin_wait_delay........................ 6
innodb_stats_auto_recalc...................... ON
innodb_stats_method........................... nulls_equal
innodb_stats_on_metadata...................... OFF
innodb_stats_persistent....................... ON
innodb_stats_persistent_sample_pages.......... 20
innodb_stats_sample_pages..................... 8
innodb_stats_transient_sample_pages........... 8
innodb_status_output.......................... OFF
innodb_status_output_locks.................... OFF
innodb_strict_mode............................ OFF
innodb_support_xa............................. ON
innodb_sync_array_size........................ 1
innodb_sync_spin_loops........................ 30
innodb_table_locks............................ ON
innodb_thread_concurrency..................... 0
innodb_thread_sleep_delay..................... 10000
innodb_track_changed_pages.................... OFF
innodb_undo_directory......................... .
innodb_undo_logs.............................. 128
innodb_undo_tablespaces....................... 0
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_version................................ 5.6.22-71.0
innodb_write_io_threads....................... 4
