- discuss - lists.mariadb.org

[Maria-discuss] Unable to DROP table, Data Dict problem
by Martin Langhoff 20 Jan '15

20 Jan '15

Hi Folks, We run a very large infra on MariaDB/RHEL6.x/RHEV/NetApp; thousands of VMs, each running MariaDB and very busy. MariaDB is 5.5.32. We recently had some NFS problems which led to minor DB corruption. Luckily, the corruption was isolated to a table holding cache data, and in fact one that is empty in most of our instances. That's the good news. The bad news is that the data dict is really unhappy about that table. - Cannot drop it, we get a nasty stack trace at [1] - Stopping MariaDB, removing the table by hand, restarting MariaDB, attempting to re-create the table hits a data dict error [2]. On disk I can see it created an ibd file, but no frm file. Following the instructions at http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html does not help. I have straced the process, and it does not seem to even try to create the frm file, so it really looks like it's internal state (data dict), not errors from further down the stack. OS / kernel logs are clean. Is there any reasonable method to cure this "in place" with minimal disruption? (These are large DBs, dump restore is more than awkward.) Oh and careful with NFSv4 :-/ thank you! martin 1 - Stack trace when trying to drop table Version: '5.5.32-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server InnoDB: Error: tablespace id is 47073 in the data dictionary InnoDB: but in file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 in file fil0fil.c line 778 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 150119 17:14:45 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see http://kb.askmonty.org/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 5.5.32-MariaDB-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=187 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 182265 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0xa92ee60 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f5ef9531d78 thread_stack 0x48000 ??:0(my_print_stacktrace)[0xa8631e] ??:0(handle_fatal_signal)[0x6cdd0b] ??:0(??)[0x30a7c0f500] ??:0(??)[0x30a78328a5] ??:0(??)[0x30a7834085] ??:0(fil_node_open_file)[0x8f3add] ??:0(fil_node_prepare_for_io)[0x8f3be5] ??:0(fil_space_get_flags)[0x8f7420] ??:0(fil_space_get_zip_size)[0x8f7529] ??:0(dict_drop_index_tree)[0x8d64e6] ??:0(row_upd_clust_step)[0x86d84d] ??:0(row_upd_step)[0x86e22e] ??:0(que_run_threads)[0x9577a8] ??:0(que_eval_sql)[0x957ea6] ??:0(row_drop_table_for_mysql)[0x85ae6a] ??:0(ha_innobase::delete_table(char const*))[0x8424e3] ??:0(ha_delete_table(THD*, handlerton*, char const*, char const*, char const*, bool))[0x6d5233] ??:0(mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, bool))[0x5ff928] ??:0(mysql_rm_table(THD*, TABLE_LIST*, char, char))[0x60001d] ??:0(mysql_execute_command(THD*))[0x58ad90] ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58e369] ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x58f790] ??:0(do_handle_one_connection(THD*))[0x647cdf] ??:0(handle_one_connection)[0x647d7c] ??:0(??)[0x30a7c07851] ??:0(??)[0x30a78e811d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f5db800afa8): is an invalid pointer Connection ID (thread ID): 2 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off 2 - Error when attempting to recreate table 150119 17:15:22 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 150119 17:15:22 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './moodle_sand/mdl_tag_correlation.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. -- martin.langhoff(a)gmail.com - ask interesting questions - don't get distracted with shiny stuff - working code first ~ http://docs.moodle.org/en/User:Martin_Langhoff

1 1

[Maria-discuss] 10.0.x: [Warning] Unsafe statement written to the binary log using statement format
by Reindl Harald 19 Jan '15

19 Jan '15

uhm - binlog-format = ROW since at least 5 years everywhere how does 10.0.x come to the crazy idea it is "BINLOG_FORMAT = STATEMENT"? 150119 22:07:52 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves. Statement: repair table `mysql`.`column_stats`, `mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`event`, `mysql`.`func`, `mysql`.`general_log`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`index_stats`, `mysql`.`ndb_binlog_index`, `mysql`.`plugin`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`proxies_priv`, `mysql`.`roles_mapping`, `mysql`.`servers`, `mysql`.`slow_log`, `mysql`.`table_stats`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`

2 1

[Maria-discuss] build minimized MariaDB 10.0.x
by Reindl Harald 19 Jan '15

19 Jan '15

https://mariadb.atlassian.net/browse/MDEV-7076 well, in general i try to only have Aria/XtraDB/MyISAM -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 \ -DWITH_MROONGA_STORAGE_ENGINE=OFF \ -DWITH_OQGRAPH_STORAGE_ENGINE=OFF \ -DWITH_SPIDER_STORAGE_ENGINE=OFF \ -DWITH_CASSANDRA_STORAGE_ENGINE=OFF \ but OOM in context of "mroonga" i want my ./configure --help back while in other aspects cmake is really cool there is no way i know to get a list of valid configure options :-( /usr/bin/ranlib libsql.a make[2]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15' /usr/bin/cmake -E cmake_progress_report /home/builduser/rpmbuild/BUILD/mariadb-10.0.15/CMakeFiles 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 make[2]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15' make[1]: *** [storage/mroonga/vendor/groonga/lib/CMakeFiles/libgroonga.dir/all] Error 2 make[1]: *** Waiting for unfinished jobs.... [ 99%] Built target sql make[1]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15'

2 3

[Maria-discuss] Spider - query end
by Nicolas Payart 12 Jan '15

12 Jan '15

Hi list, I am using Spider Storage Engine for sharding data and remote access to some innodb tables on 3 different MariaDB 10.0.14 instances. It performs quite well in production except that sometimes, some queries (a minority of them fortunately) do not respond and stay indefinitely in "query end" state in processlist. What can make a query stay indefinitely in "query end" state? Maybe a bug in Spider Storage Engine? Any advice is welcome! Some more details: db01 =# show full processlist\G *************************** 4. row *************************** Id: 1699242 User: np Host: 10.0.0.1:24220 db: db01 Command: Query Time: 155777 State: query end Info: SELECT s.id,s.title,sum(s.cpt) AS sumcpt FROM s INNER JOIN u ON u.id = s.u_id INNER JOIN d ON s.d_id = d.id WHERE u.str = 'abc123456' AND d.str = 'def456789' GROUP BY s.title ORDER BY sumcpt DESC LIMIT 0,10 Progress: 0.000 About tables used in query: s is a spider table pointing on 24 innodb shards over 3 remote servers (PARTITION BY LIST (mod(id,24))) u is a spider table referencing one remote innodb table (not sharded, millions rows) d is a spider table referencing one remote innodb table (not sharded, <100 rows) Spider config: (none) =# show variables like 'spider%'; +---------------------------------------+--------+ | Variable_name | Value | +---------------------------------------+--------+ | spider_auto_increment_mode | -1 | | spider_bgs_first_read | -1 | | spider_bgs_mode | -1 | | spider_bgs_second_read | -1 | | spider_bka_engine | | | spider_bka_mode | -1 | | spider_block_size | 16384 | | spider_bulk_size | -1 | | spider_bulk_update_mode | -1 | | spider_bulk_update_size | -1 | | spider_casual_read | 1 | | spider_conn_recycle_mode | 1 | | spider_conn_recycle_strict | 0 | | spider_connect_mutex | OFF | | spider_connect_retry_count | 1000 | | spider_connect_retry_interval | 1000 | | spider_connect_timeout | 10 | | spider_crd_bg_mode | 1 | | spider_crd_interval | 4 | | spider_crd_mode | 1 | | spider_crd_sync | 0 | | spider_crd_type | -1 | | spider_crd_weight | -1 | | spider_delete_all_rows_type | -1 | | spider_direct_dup_insert | 1 | | spider_direct_order_limit | -1 | | spider_dry_access | OFF | | spider_error_read_mode | -1 | | spider_error_write_mode | -1 | | spider_first_read | -1 | | spider_force_commit | 1 | | spider_general_log | OFF | | spider_init_sql_alloc_size | -1 | | spider_internal_limit | -1 | | spider_internal_offset | -1 | | spider_internal_optimize | -1 | | spider_internal_optimize_local | -1 | | spider_internal_sql_log_off | ON | | spider_internal_unlock | OFF | | spider_internal_xa | OFF | | spider_internal_xa_id_type | 0 | | spider_internal_xa_snapshot | 0 | | spider_local_lock_table | OFF | | spider_lock_exchange | OFF | | spider_log_result_error_with_sql | 0 | | spider_log_result_errors | 0 | | spider_low_mem_read | -1 | | spider_max_order | -1 | | spider_multi_split_read | -1 | | spider_net_read_timeout | 30 | | spider_net_write_timeout | 60 | | spider_ping_interval_at_trx_start | 3600 | | spider_quick_mode | -1 | | spider_quick_page_size | -1 | | spider_read_only_mode | -1 | | spider_remote_access_charset | | | spider_remote_autocommit | 0 | | spider_remote_default_database | | | spider_remote_sql_log_off | 1 | | spider_remote_time_zone | | | spider_remote_trx_isolation | -1 | | spider_reset_sql_alloc | 0 | | spider_same_server_link | ON | | spider_second_read | -1 | | spider_select_column_mode | -1 | | spider_selupd_lock_mode | -1 | | spider_semi_split_read | -1 | | spider_semi_split_read_limit | -1 | | spider_semi_table_lock | 1 | | spider_semi_table_lock_connection | -1 | | spider_semi_trx | ON | | spider_semi_trx_isolation | -1 | | spider_skip_default_condition | -1 | | spider_split_read | -1 | | spider_sts_bg_mode | -1 | | spider_sts_interval | -1 | | spider_sts_mode | -1 | | spider_sts_sync | 0 | | spider_support_xa | OFF | | spider_sync_autocommit | OFF | | spider_sync_time_zone | OFF | | spider_sync_trx_isolation | OFF | | spider_table_init_error_interval | 1 | | spider_udf_ct_bulk_insert_interval | -1 | | spider_udf_ct_bulk_insert_rows | -1 | | spider_udf_ds_bulk_insert_rows | -1 | | spider_udf_ds_table_loop_mode | -1 | | spider_udf_ds_use_real_table | -1 | | spider_udf_table_lock_mutex_count | 20 | | spider_udf_table_mon_mutex_count | 20 | | spider_use_all_conns_snapshot | OFF | | spider_use_consistent_snapshot | OFF | | spider_use_default_database | ON | | spider_use_flash_logs | OFF | | spider_use_handler | 0 | | spider_use_pushdown_udf | -1 | | spider_use_snapshot_with_flush_tables | 0 | | spider_use_table_charset | -1 | | spider_version | 3.2.11 | +---------------------------------------+--------+ 99 rows in set (0.00 sec) -- Nicolas Payart

2 2

[Maria-discuss] future of SHOW ENGINE INNODB MUTEX in MariaDB
by Federico Razzoli 12 Jan '15

12 Jan '15

Hi! Morgan Tocker asked the community's opinions about SHOW ENGINE INNODB MUTEX: http://www.tocker.ca/2013/11/28/show-engine-innodb-mutex.html Despite these opinions, as Valerii Kravchuk notes on Facebook, the command disappeared in the latest 5.7 release. He also notes that the command has never been deprecated (!). Question is: will MariaDB preserve this command? Frankly, I don't know any other convenient way to obtain the same information... Regards, Federico

3 2

[Maria-discuss] The relay-log is not fluashed after the slave-relay-log.999999 showed
by Gmail 07 Jan '15

07 Jan '15

Hello, dear users. I am using MariaDB 10.0.14 at CentOS 6.5. And, as I mentioned at the title of this question, the relay-log is not flushed after the slave-relay-log.999999 showed when using "Salve_parallel_threads:10" setting. like showed blow. - binlog_format: ROW - Slave_parallel_threads:10 Everything are working fine except the "slave-relay-log.******" files continue to exist at the disk which will finally cause the disk full. If I change the value of Slave_parallel_threads setting from 10 to 0, the log will be flushed. Howevery "PK duplicate warning" error logs come next. Is there any setting should be used with Slave_parallel_threads setting? Any help will be great. Best regareds, - Jasung

2 2

[Maria-discuss] MariaDB Galera Cluster 5.5.41 now available
by Daniel Bartholomew 06 Jan '15

06 Jan '15

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.41 This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.41 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-5541-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-5541-changelog - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.41 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] Newbie Question - Multi-Master vs Cluster
by Stuart Elvish 06 Jan '15

06 Jan '15

Hi all, Up until now we have only directly managed stand alone MySQL instances. For clusters (MySQL) we have used a contractor to build and administrate the clusters. Our current product development requirements and good business practice (high availability of critical databases) now dictate that we move to a multi-server setup. Our goal is to setup at least two servers which will have the same data at all times and which are at separate sites so if the link between the sites goes down, the cell of servers at that site can keep operating. Our database stores text fields and blobs of audio recordings. We read data (SELECT with a WHERE) for 90-95% of our queries, some tables can be populated with hundreds of thousand of rows but normally far fewer. The software we are running which interacts with the database is smart enough to figure out if one of the database servers it is trying to reach is not responding and will hunt through a list of IP's until it successfully connects. I have a couple of questions as this will be our first cluster. (I don't think multi-master will meet our requirements based on the examples available online so I have chosen MariaDB 10 with Galera.) How often is complex management (manual intervention) required such as adding a node back into the cluster after communications between the nodes fails and then is restored? Is it normal, say 90% of the time, for syncing to re-establish automatically? What type of monitoring tools are advised? (Or is it as simple as automatically parsing logs to find errors?) The documented recommendation is 3 servers; we don't have the hardware at the moment so is it ok to start with 2 and add an additional database server at a later time? Alternatively, we could use 2 dedicated servers and 1 virtual server but the documentation states that the cluster only works as quickly as the slowest node. Is that only if you are running queries against the slowest node? Or if you are writing to the slowest node? I am assuming we can run the arbiter (garbd) on one of the existing data nodes. I am assuming we can use the SSL options in the configuration file to secure communication between the servers so if it was sniffed it would not be possible to decode the data. Does anyone have any comments on if this affects reliability? Thank you for any input you have on these questions. Please feel free to ask if you want clarification on anything.

1 0

[Maria-discuss] Oracle sets 'tone' and standard/level of discussion
by Peter Laursen 05 Jan '15

05 Jan '15

http://mysqlrelease.com/2014/12/official-mysql-repos-for-suse-linux/ "Regarding SUSE Linux Enterprise 12, SUSE unfortunately chose to not provide users with a mature and robust version of MySQL as part of the distro," https://www.suse.com/releasenotes/x86_64/SUSE-SLES/12/#fate-316482 "The MariaDB open source database replaces the MySQL database system." I also posted this comment (don't know it it will be approved) *“Regarding SUSE Linux Enterprise 12, SUSE unfortunately chose to not provide users with a mature and robust version of MySQL as part of the distro .. ”* *https://www.suse.com/releasenotes/x86_64/SUSE-SLES/12/#fate-316482 <https://www.suse.com/releasenotes/x86_64/SUSE-SLES/12/#fate-316482>“The MariaDB open source database replaces the MySQL database system.”* *Maybe rather Oracle should try to understand *WHY” almost all recent Linux distros (as well as most major web2 companies) have switched to MariaDB.* *One important reason is that the public bug-tracker for Oracle/MySQL is incomplete. This was the most stupid thing Oracle ever did to MySQL and it is completely unacceptable in the open-source world. Another reason is that patches that are released as strictly GPL cannot be included in Oracle/MySQL. MariaDB can freely use such patches – primarily from major web2 companies (Google,Facebook etc.) – and they are doing and we have only seen the beginning.* *@guys .. soon time to find another employer!* -- Peter -- Webyog

6 5

[Maria-discuss] Does MariaDB support 2 level replication and crash recover from such situation?
by 司同 30 Dec '14

30 Dec '14

Hi, all I am working on MariaDB with replication topology A->B->C, in which B is the slave of A and also the master of C, thus C contains data of both A and B. and A, B would be modified by application, while C be read only. now let B crash, I alter A to let it be the new master of C, and this time C would be modified by application. then after a period of time, B restart, this time I need let C be the master of B, and I need B has the same data with A and C, thus the topology change from A->B->C to A->C->B. Does this be possible? (I set skip-slave-start for all servers.) Regards.

3 2