← Back to team overview

maria-discuss team mailing list archive

Re: crashed tables (mariadb-5.5.33a)

 

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"
> 
> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
> 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm

Attachment: signature.asc
Description: OpenPGP digital signature


References