yes they are because *any* tables are created this way for years also in production for around 10000 tables and that is why this scares me to replace MySQL with MariaDB for now Am 23.10.2013 17:38, schrieb Jocelyn Fournier:
I suspect the DELAY_KEY_WRITE to be guilty in your case, are all the crashed table created with DELAY_KEY_WRITE=1 ?
Le 23/10/2013 17:24, Reindl Harald a écrit :
this seems to happen daily on different tables of the same type which in fact have one record and not for other tables because all the *_meta are from the same self developed CMS
rh_meta | CREATE TABLE `rh_meta` ( `mid` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `mlastcheck` int(10) unsigned NOT NULL DEFAULT '0', `mcounter` mediumint(7) unsigned NOT NULL DEFAULT '0', `mecardcount` mediumint(7) unsigned NOT NULL DEFAULT '0', `mheisenews` text COLLATE latin1_german1_ci NOT NULL, `mheisetimestamp` int(10) unsigned NOT NULL DEFAULT '0', `mnewsticker_timeout` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '', `mnewsticker_wait` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '', `mstartseite` text COLLATE latin1_german1_ci NOT NULL, `mstartseite_en` text COLLATE latin1_german1_ci NOT NULL, `mstartseite_link` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '', `pz_start1_titel` text COLLATE latin1_german1_ci NOT NULL, `pz_start2_titel` text COLLATE latin1_german1_ci NOT NULL, `pz_start3_titel` text COLLATE latin1_german1_ci NOT NULL, `pz_start4_titel` text COLLATE latin1_german1_ci NOT NULL, `pz_start1_bild` text COLLATE latin1_german1_ci NOT NULL, `pz_start2_bild` text COLLATE latin1_german1_ci NOT NULL, `pz_start3_bild` text COLLATE latin1_german1_ci NOT NULL, `pz_start4_bild` text COLLATE latin1_german1_ci NOT NULL, `pz_start1_text` text COLLATE latin1_german1_ci NOT NULL, `pz_start2_text` text COLLATE latin1_german1_ci NOT NULL, `pz_start3_text` text COLLATE latin1_german1_ci NOT NULL, `pz_start4_text` text COLLATE latin1_german1_ci NOT NULL, `pz_start_metatext` text COLLATE latin1_german1_ci NOT NULL, `mfailedlogins` mediumint(7) unsigned NOT NULL DEFAULT '0', `mlocktime` int(10) unsigned NOT NULL DEFAULT '0', `m_trans_cleanup` int(10) unsigned NOT NULL DEFAULT '0', `m_trans_move` tinyint(1) unsigned NOT NULL DEFAULT '0', `m_trans_replace` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`mid`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=0 DELAY_KEY_WRITE=1
131023 10:37:54 [Note] Slave: connected to master 'replication@*****:3306',replication resumed in log 'bin.000060' at position 424439 131023 11:30:02 [ERROR] mysqld: Table './hvb/andersentag_meta' is marked as crashed and should be repaired 131023 11:30:02 [Warning] Checking table: './hvb/andersentag_meta' 131023 11:30:02 [ERROR] hvb.andersentag_meta: 1 client is using or hasn't closed the table properly 131023 11:30:03 [ERROR] mysqld: Table './hvb/buchwoche_meta' is marked as crashed and should be repaired 131023 11:30:03 [Warning] Checking table: './hvb/buchwoche_meta' 131023 11:30:03 [ERROR] hvb.buchwoche_meta: 1 client is using or hasn't closed the table properly
Am 22.10.2013 15:43, schrieb Reindl Harald:
i see randomly such messages in the mysql_error.log there was no hard shutdown or killed process and so i am somehow out of ideas
131022 11:30:05 [ERROR] mysqld: Table './musikarchiv/rh_meta' is marked as crashed and should be repaired 131022 11:30:05 [Warning] Checking table: './musikarchiv/rh_meta'
the only time i can remember such issues was http://lists.mysql.com/mysql/226361 but in the current situation i see nothing similar in the logs or any hint
there are two instances (port 3306 / 3307) with to different systemd-units and configurations to replicate my home-machine over openvpn to the office and my office-workstation at home
below the whole configuration, logfile and build-options while MySQL 5.5.34 in production is using practically the same RPM-SPEC without issues ________________________________________________________________________________________________
[root@srv-rhsoft:~]$ cat /etc/my.cnf [mysqld] socket = /var/lib/mysql/mysql.sock datadir = /mnt/data/db/mysql-srv pid-file = /mnt/data/db/mysql-srv/mysql.pid tmpdir = /tmp character-set-server = latin1 collation-server = latin1_german1_ci default-time-zone = "Europe/Vienna" default-storage-engine = myisam lower_case_table_names = 1 log-error = /var/log/mysql_error.log log_warnings = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql_slow_query.log long_query_time = 1 log_queries_not_using_indexes = 0 general_log = 0 general_log_file = /var/log/mysql_query.log port = 3306 old_passwords = 0 local-infile = 0 sql-mode = STRICT_ALL_TABLES
ssl-ca = /etc/mysql-ssl/ca.crt ssl-cert = /etc/mysql-ssl/server.pem ssl-key = /etc/mysql-ssl/server.pem ssl-cipher = EECDH+ECDSA+AESGCM:EECDH+aRSA+AESGCM:EECDH+ECDSA+SHA384:EECDH+ECDSA+SHA256:EECDH+aRSA+SHA384:EECDH+aRSA+SHA256:EECDH:EDH+aRSA:HIGH:!3DES:!aNULL:!DSS:!eNULL:!EXP:!MD5:!PSK:!RC4:!SRP:!SSLv2:!LOW:!MEDIUM
delay-key-write = ALL concurrent_insert = 2 open-files-limit = 500000 myisam-recover-options = "FORCE" myisam_use_mmap = 1 performance_schema = 0
wait_timeout = 86400 interactive_timeout = 86400
max_allowed_packet = 250M max_connections = 300 max_tmp_tables = 100 max_connect_errors = 200 max_delayed_threads = 30
flush_time = 0
query_cache_limit = 150K query_cache_min_res_unit = 2K query_cache_size = 128M query_cache_type = 1
table_cache = 5000 table_definition_cache = 512 thread_cache_size = 100 tmp_table_size = 256M max_heap_table_size = 256M
key_buffer_size = 128M sort_buffer_size = 128K myisam_sort_buffer_size = 15M join_buffer_size = 1M preload_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128K
innodb_buffer_pool_size = 64M innodb_buffer_pool_instances = 1 innodb_purge_threads = 1 innodb_max_purge_lag = 200000 innodb_max_dirty_pages_pct = 60 innodb_additional_mem_pool_size = 3M innodb_log_file_size = 80M innodb_log_buffer_size = 2M innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 10 innodb_flush_log_at_trx_commit = 2 innodb_support_xa = 1 innodb_lock_wait_timeout = 50 innodb_table_locks = 0 innodb_checksums = 0 innodb_file_format = barracuda innodb_file_per_table = 1 innodb_open_files = 300 innodb_io_capacity = 600 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_adaptive_flushing_method = keep_average innodb_flush_method = ALL_O_DIRECT transaction-isolation = READ-COMMITTED
server-id = 1 log-bin = /mnt/data/db/mysql-srv/bin.log log-bin-index = /mnt/data/db/mysql-srv/bin.index expire_logs_days = 21 max_binlog_size = 256M binlog-format = ROW binlog_stmt_cache_size = 256K binlog_cache_size = 256K sync_binlog = 30
low-priority-updates skip-federated skip-symbolic-links skip-name-resolve safe-user-create skip-partition slave_compressed_protocol
[mariadb] aria_pagecache_buffer_size = 32m aria_sort_buffer_size = 32m aria_page_checksum = 0 key_cache_segments = 8 thread_handling = one-thread-per-connection thread_pool_idle_timeout = 60
[client] socket = /var/lib/mysql/mysql.sock port = 3306 user = root ssl-ca = /etc/mysql-ssl/ca.crt ssl-cert = /etc/mysql-ssl/client.pem ssl-key = /etc/mysql-ssl/client.pem ssl-cipher = EECDH+ECDSA+AESGCM:EECDH+aRSA+AESGCM:EECDH+ECDSA+SHA384:EECDH+ECDSA+SHA256:EECDH+aRSA+SHA384:EECDH+aRSA+SHA256:EECDH:EDH+aRSA:HIGH:!3DES:!aNULL:!DSS:!eNULL:!EXP:!MD5:!PSK:!RC4:!SRP:!SSLv2:!LOW:!MEDIUM
[mysqladmin] socket = /var/lib/mysql/mysql.sock port = 3306 user = root
[mysqld_safe] socket = /var/lib/mysql/mysql.sock port = 3306 log-error = /var/log/mysql_error.log ________________________________________________________________________________________________
31022 9:02:20 [Note] Event Scheduler: Purging the queue. 0 events 131022 9:02:20 [Note] Error reading relay log event: slave SQL thread was killed 131022 9:02:20 [Note] Slave I/O thread killed during or after a reconnect done to recover from failed read 131022 9:02:20 [Note] Slave I/O thread exiting, read up to log 'bin.000058', position 952251 131022 9:02:21 InnoDB: Starting shutdown... 131022 9:02:22 InnoDB: Shutdown completed; log sequence number 2638210237 131022 9:02:22 [Note] /usr/libexec/mysqld: Shutdown complete 131022 9:02:27 [Note] /usr/libexec/mysqld: Normal shutdown 131022 9:02:27 [Note] Event Scheduler: Purging the queue. 0 events 131022 9:02:28 InnoDB: Starting shutdown... 131022 9:02:29 InnoDB: Shutdown completed; log sequence number 14738980420 131022 9:02:29 [Note] /usr/libexec/mysqld: Shutdown complete 131022 9:03:01 InnoDB: The InnoDB memory heap is disabled 131022 9:03:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 131022 9:03:01 InnoDB: Compressed tables use zlib 1.2.7 131022 9:03:01 InnoDB: Using Linux native AIO 131022 9:03:01 InnoDB: Initializing buffer pool, size = 50.0M 131022 9:03:01 InnoDB: Completed initialization of buffer pool 131022 9:03:02 InnoDB: highest supported file format is Barracuda. 131022 9:03:02 InnoDB: The InnoDB memory heap is disabled 131022 9:03:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins 131022 9:03:02 InnoDB: Compressed tables use zlib 1.2.7 131022 9:03:02 InnoDB: Using Linux native AIO 131022 9:03:02 InnoDB: Initializing buffer pool, size = 64.0M 131022 9:03:02 InnoDB: Completed initialization of buffer pool 131022 9:03:02 InnoDB: highest supported file format is Barracuda. 131022 9:03:11 InnoDB: Waiting for the background threads to start 131022 9:03:11 InnoDB: Waiting for the background threads to start 131022 9:03:12 Percona XtraDB (http://www.percona.com) 5.5.33a-MariaDB-31.1 started; log sequence number 2638210237 131022 9:03:12 [Note] Plugin 'FEDERATED' is disabled. 131022 9:03:12 [Note] Plugin 'FEEDBACK' is disabled. 131022 9:03:12 [Note] Plugin 'partition' is disabled. 131022 9:03:12 [Note] Server socket created on IP: '127.0.0.1'. 131022 9:03:12 Percona XtraDB (http://www.percona.com) 5.5.33a-MariaDB-31.1 started; log sequence number 14738980420 131022 9:03:12 [Note] Plugin 'FEDERATED' is disabled. 131022 9:03:12 [Note] Plugin 'FEEDBACK' is disabled. 131022 9:03:12 [Note] Plugin 'partition' is disabled. 131022 9:03:14 [Note] Slave SQL thread initialized, starting replication in log 'bin.000058' at position 952251, relay log './mysql-relay-bin.000140' position: 952529 131022 9:03:14 [Note] Server socket created on IP: '0.0.0.0'. 131022 9:03:14 [Note] Slave I/O thread: connected to master 'replication@10.0.0.99:3306',replication started in log 'bin.000058' at position 952251 131022 9:03:14 [Note] Event Scheduler: Loaded 0 events 131022 9:03:14 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.33a-MariaDB' socket: '/var/lib/mysql/mysql_replication.sock' port: 3307 thelounge 131022 9:03:14 [Note] Event Scheduler: Loaded 0 events 131022 9:03:14 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.33a-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 thelounge 131022 11:30:05 [ERROR] mysqld: Table './musikarchiv/rh_meta' is marked as crashed and should be repaired 131022 11:30:05 [Warning] Checking table: './musikarchiv/rh_meta' 131022 11:30:05 [ERROR] musikarchiv.rh_meta: 1 client is using or hasn't closed the table properly 131022 11:30:07 [ERROR] mysqld: Table './lounge_panel/cms1_meta' is marked as crashed and should be repaired 131022 11:30:07 [Warning] Checking table: './lounge_panel/cms1_meta' 131022 11:30:07 [ERROR] lounge_panel.cms1_meta: 1 client is using or hasn't closed the table properly ________________________________________________________________________________________________
[builduser@testserver:~]$ cat .rpmrc optflags: x86_64 -m64 -O2 -march=corei7 -mtune=corei7 -fopenmp -mmmx -msse2 -msse3 -msse4.1 -msse4.2 -maes -mfpmath=sse -pipe -fomit-frame-pointer -finline-functions -finline-limit=60 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=8 -D_FORTIFY_SOURCE=2
export CFLAGS="%{optflags} -O3 -fno-stack-protector -fstack-protector --param=ssp-buffer-size=8 -fPIC -fomit-frame-pointer -fno-exceptions -ffixed-ebp -fwrapv -fno-strict-aliasing -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE" export CXXFLAGS="$CFLAGS" export FFLAGS="$CFLAGS" export FCFLAGS="$CFLAGS" export LDFLAGS="-Wl,-z,now -Wl,-z,relro,-z,noexecstack -pie" export SH_LDFLAGS="$LDFLAGS"
cmake . -DBUILD_CONFIG=mysql_release \ -DFEATURE_SET="community" \ -DCMAKE_INSTALL_PREFIX="%{_prefix}" \ -DINSTALL_INCLUDEDIR=include/mysql \ -DINSTALL_LAYOUT=RPM \ -DINSTALL_LIBDIR="%{_lib}/mysql" \ -DINSTALL_MANDIR=share/man \ -DINSTALL_MYSQLSHAREDIR=share/mysql \ -DINSTALL_MYSQLTESTDIR=share/mysql-test \ -DINSTALL_PLUGINDIR="%{_lib}/mysql/plugin" \ -DINSTALL_SBINDIR=libexec \ -DINSTALL_SCRIPTDIR=bin \ -DINSTALL_SQLBENCHDIR= \ -DINSTALL_SUPPORTFILESDIR=share/mysql \ -DMYSQL_DATADIR="%{_sharedstatedir}/mysql" \ -DMYSQL_UNIX_ADDR="%{_sharedstatedir}/mysql/mysql.sock" \ -DWITH_XTRADB_STORAGE_ENGINE=ON \ -DWITH_PARTITION_STORAGE_ENGINE=ON \ -DWITH_ARCHIVE_STORAGE_ENGINE=OFF \ -DWITH_BLACKHOLE_STORAGE_ENGINE=OFF \ -DWITH_SPHINX_STORAGE_ENGINE=OFF \ -DENABLED_PROFILING=OFF \ -DENABLE_DTRACE=OFF \ -DENABLE_DEBUG_SYNC=OFF \ -DWITHOUT_TOKUDB=ON \ -DWITH_VALGRIND=OFF \ -DWITH_SAFEMALLOC=OFF \ -DWITH_EMBEDDED_SERVER=OFF \ -DWITH_FEEDBACK=OFF \ -DWITH_LIBWRAP=OFF \ -DWITH_QUERY_CACHE_INFO=OFF \ -DWITH_READLINE=OFF \ -DUSE_NEW_READLINE_INTERFACE=ON \ -DENABLED_LOCAL_INFILE=ON \ -DNOT_FOR_DISTRIBUTION=ON \ -DWITH_FAST_MUTEXES=ON \ -DWITH_ATOMIC_OPS=smp \ -DWITHOUT_DYNAMIC_PLUGINS=ON \ -DWITH_PIC=ON \ -DWITH_JEMALLOC=system \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DCOMPILATION_COMMENT="thelounge"