maria-discuss team mailing list archive

[Reindl Harald <h.reindl@xxxxxxxxxxxxx>]

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"

Attachment: signature.asc
Description: OpenPGP digital signature