- discuss - lists.mariadb.org

[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

[Maria-discuss] Best way to find out what function(s) generate the value of the VIRTUAL/PERSISTENT column.
by Abhishek Kumar Pandey 29 Dec '14

29 Dec '14

Hi, I need to find out the function(s) which generate the value of VIRTUAL/PERSISTENT column. According to documentation here- https://mariadb.com/kb/en/mariadb/documentation/sql-commands/data-definitio… I have to use *SHOW CREATE TABLE.. *to get the definition. It is doable of course but It involves painful parsing of returned create table statement to get the required function(s)( As I want to display them in GUI client.) This function(s) is not returned in *show full fields from... *and I can't find any relevant information in INFORMATION_SCHEMA also. So my question is- Is there any better way of getting this information other than using SHOW CREATE TABLE.. and do parsing? or is it the only way of accomplishing it? Regards, Abhishek.

1 0