- discuss - lists.mariadb.org

[Maria-discuss] crashed tables (mariadb-5.5.33a)
by Reindl Harald 23 Oct '13

23 Oct '13

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"

1 1

[Maria-discuss] New Question: Using Connect with MySQL 5.5
by AskMonty KB 23 Oct '13

23 Oct '13

Hello, A new question has been asked in "Connect" by shlomisut: -------------------------------- Hi We're currently testing usage of MySQL Federated engine, used for read-only cached tables. The Connect engine looks like a very interesting option as it will allow us to use the index/condition push down, thus reducing the amount of network & total query time. Do you have any experience with using Connect as a MySQL engine? migrating to MariaDB is not possible at current time frames, though it is on our roadmap Thanks! Shlomi -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/using-connect-with-mysql-55/

1 0

[Maria-discuss] Binlog replication protocol in MySql 5.0.95, 5.1 and 5.5
by Vesa-Matti J Kari 21 Oct '13

21 Oct '13

Hello, We have a MySQL Version 5.0.95 setup that consists of 1 master server and 3 replication slave servers. Now we want to upgrade these servers to MySQL 5.5 via intermediate step MySQL 5.1. Can we upgrade one MySQL server at a time or do we have to take all servers down and upgrade all at once? In other words, is the binlog replication protocol compatible between all MySQL 5.0.95, 5.1 ja 5.5? Many thanks for the information. Regards, vmk -- ************************************************************************ Tietotekniikkakeskus / Helsingin yliopisto IT department / University of Helsinki ************************************************************************

1 0

[Maria-discuss] New Question: new stable release for MariaDB Client Library for "C"
by AskMonty KB 18 Oct '13

18 Oct '13

Hello, A new question has been asked in "MariaDB Client Library for C" by gghervase: -------------------------------- Hello, Can you please tell me if a new stable release for the mariadb native client will be done anytime soon? There are several bug fixes on trunk and I wonder when will they be released. For example, I can't ssl authenticate to the maridb 5.5.33 with the currently released client (Bad Handshake - response error 1043 from the server), but it works with your lastest code from trunk. Thank you very much. Best regards, Gabriel Ghervase -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/new-stable-release-for-mariadb-client-library-for-…

1 0

[Maria-discuss] New Question: Galera Cluster starting backup can't find innobackupex in the path
by AskMonty KB 16 Oct '13

16 Oct '13

Hello, A new question has been asked in "Galera" by tubaguy50035: -------------------------------- I am trying to get a Galera cluster running. I have the first node up and ready for connections. My second node, however, when trying to run wsrep_sst_xtrabackup gets the error "Can't find innobackupex in the path". As soon as I start the mysql service on the second node, I briefly see it as an incoming address on the first node, but then it goes away. Not sure what would be necessary to help me with this, so here's my whole wsrep specific section of my my.cnf: <<code>> binlog_format=ROW default_storage_engine=innodb innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 innodb_doublewrite=1 wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_provider_options="gcache.size=256M; gcache.page_size=128M" wsrep_cluster_address=gcomm://firstnode wsrep_cluster_name="Cluster" wsrep_node_address="thisnode" wsrep_node_name="thisnode" wsrep_sst_method=xtrabackup wsrep_sst_auth="root:password" wsrep_node_incoming_address=this node's IP wsrep_sst_receive_address=this node's IP wsrep_slave_threads=16 <</code>> -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/galera-cluster-starting-backup-cant-find-innobacku…

1 0

[Maria-discuss] ECDHE ciphers PHP->MariaDB
by Reindl Harald 15 Oct '13

15 Oct '13

Hi is it PHP'sd mysqli/mysqlnd or MySQL/MariaDB which is resposible that no ECDHE-ciphers are used? below ends in TLSv1 with DHE-RSA-AES128-SHA * MariaDB as well as PHP/Apache compiled with a ECDHE supporting OpenSSL, proven by https://www.ssllabs.com/ssltest/ as well as Postfix/Dovecot logs compiled against the same openssl-build * Fedora 19 with https://bugzilla.redhat.com/show_bug.cgi?id=319901#c108 home-made build $this->conn->ssl_set($this->ssl_key, $this->ssl_crt, $this->ssl_ca, NULL, 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:AES128-SHA');

1 0

[Maria-discuss] Issues using the connect engine with ODBC on Linux
by eric hernandez 15 Oct '13

15 Oct '13

Hi, I am trying to use the "Connect" engine to read data from Microsoft SQL Server 2008 R2 from a linux based MariaDB instance. I am getting the following error. create table tablea ( -> id bigint(20) NOT NULL, ) -> engine=connect table_type=ODBC -> block_size=10 -> tabname='tablea' -> Connection='DSN=MSSQLTestServer' ERROR 1105 (HY000): Unsupported table type ODBC I have the underlying linux box talking fine to the "MSSQLTestServer" via odbc through the iSQL command. You can see here where i connect via isql from the command line with no issue isql -v MSSQLTestServer sa $sanitized +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ So odbc is installed and working properly on the linux box. Do you have any advice on how to get past this error? Thanks, -Eric

3 2

[Maria-discuss] New Question: I want to install mariadb5.5.33(with tokudb plugin) without 'apt-get install'
by AskMonty KB 12 Oct '13

12 Oct '13

Hello, A new question has been asked in "MariaDB FAQ" by frances714: -------------------------------- I want to install mariadb5.5.33(with tokudb plugin) without 'apt-get install' on debain 6 squeeze(i686), but there is only mariadb5.5.30(with tokudb plugin) for x86_64 on tokudb.org. So is there any method to install the mariadb(with tokudb plugin) on my computer? Thank you so much! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/i-want-to-install-mariadb5533with-tokudb-plugin-wi…

1 0

[Maria-discuss] DELETE Operations in MariaDB 5.5.30 do not reduce size of table files
by Steve Afrin 09 Oct '13

09 Oct '13

To anyone who knows how the MyISAM engine works (or maybe this is independent of the DB engine), here’s my scenario: * I have a table with 180,000 rows * I delete 170,000 rows with a DELETE and subsequent COMMIT statement * The MYI table file is still the same size it was before the 170k rows were deleted I saw there’s an administrative PURGE command, but it looks like that is just for purging data from binary logs, if you’ve even turned on the option for keeping binary logs. In MariaDB 5.5.30 or later, how do I run a purge operation to compress the deleted rows of data out of my physical table files and cause the reindexing to happen for the data that is still in my tables? Thanks, Steve

2 1

[Maria-discuss] New Question: regarding seconds behind master variable in 10.0.4
by AskMonty KB 09 Oct '13

09 Oct '13

Hello, A new question has been asked in "MariaDB Documentation" by jaihind213: -------------------------------- hi we setup maria 10.0.4 for multisource replication. it was replicating from a percona 5.5 master. When we started replication, maria was a day behind its master. its working fine but we noticed this phenomenon: when we execute 'show all slaves status' consecutively the 'seconds_behind_master' flips from 0 to XXX and XXX to 0. XXX keeps decreasing though, which is good as its getting closer to the state of its master. But wanted to know why the 'seconds_behind_master' comes down to 0 then again sets to a positive number. Note: XXX is some positive number. Thanking you. vishnu rao -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/regarding-seconds-behind-master-variable-in-1004/

1 0