maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #01121
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
