December 2015 - discuss - lists.mariadb.org

Re: [Maria-discuss] enforce_storage_engine galera cluster
by Federico Razzoli 22 Dec '15

22 Dec '15

Sounds like a bug. But, bug or not bug, I suggest you set sql_mode = 'NO_ENGINE_SUBSTITUTION'. Changes to non-InnoDB tables are NOT replicated. (There seems to be a sort of support for MyISAM, but it's unreliable and needs to be manually enabled on all nodes) Federico -------------------------------------------- Mar 22/12/15, Michele Tota <michele.tota(a)lnf.infn.it> ha scritto: Oggetto: Re: [Maria-discuss] enforce_storage_engine galera cluster A: maria-discuss(a)lists.launchpad.net Data: Martedì 22 dicembre 2015, 12:22 Thanks guys, as Ian has pointed out, in recent releases sql_mode contains by default NO_ENGINE_SUBSTITUTION so in our deployment we set sql_mode to blank to gracefully use InnoDB when MyISAM table creation is issued. To be clearer on the problem we're experiencing, I'll paste some console output. On the first node we have: MariaDB [galera_test]> SHOW VARIABLES WHERE Variable_name='enforce_storage_engine' OR Variable_name='sql_mode'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | enforce_storage_engine | InnoDB | | sql_mode | | +------------------------+--------+ 2 rows in set (0.00 sec) MariaDB [galera_test]> CREATE TABLE table_1(id INTEGER) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) MariaDB [galera_test]> CREATE TABLE table_2(id INTEGER) ENGINE=MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [galera_test]> SHOW WARNINGS; +-------+------+-------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------+ | Note | 1266 | Using storage engine InnoDB for table 'table_2' | +-------+------+-------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [galera_test]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'galera_test'; +------------+--------+ | TABLE_NAME | ENGINE | +------------+--------+ | table_1 | InnoDB | | table_2 | InnoDB | +------------+--------+ 2 rows in set (0.00 sec) Please note that in table_2 creation we obtain a warning (not an error), and table_2 is correctly created using InnoDB, and until this point, everything is working great. When we check the same table on another cluster node, we have: MariaDB [galera_test]> SHOW VARIABLES WHERE Variable_name='enforce_storage_engine' OR Variable_name='sql_mode'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | enforce_storage_engine | InnoDB | | sql_mode | | +------------------------+--------+ 2 rows in set (0.00 sec) MariaDB [galera_test]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'galera_test'; +------------+--------+ | TABLE_NAME | ENGINE | +------------+--------+ | table_1 | InnoDB | | table_2 | MyISAM | +------------+--------+ 2 rows in set (0.00 sec) MariaDB [galera_test]> SHOW TABLE STATUS WHERE Name = 'table_2'; +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | table_2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0 | NULL | 2015-12-22 11:08:34 | 2015-12-22 11:08:34 | NULL | latin1_swedish_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) As you can note, creating a MyISAM table (table_2) on the first node produces a correct InnoDB table, but replication of the same table on the other cluster nodes happens using MyISAM engine. It seems like engine enforcing is used during creation but not during replication.... On 21/12/2015 20:34, Ian Gilfillan wrote: On 21/12/2015 20:10, Nirbhay Choubey wrote: On Mon, Dec 21, 2015 at 12:47 PM, Michele Tota <michele.tota(a)lnf.infn.it> wrote: Hello Nirbhay, thanks for your reply. We have the 10.1.9 mariadb version; the bug that your refers is similar but in our case the enforcing works properly on the node used for the initial write, while replicated writes on other nodes generate tables using another engine. I tried the following on 10.1.9 : Version: Server version: 10.1.9-MariaDB-debug-log Source distribution Node 1: MariaDB [test]> SET @@enforce_storage_engine=INNODB; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> CREATE TABLE t1(i INT) ENGINE=INNODB; Query OK, 0 rows affected (0.07 sec) MariaDB [test]> CREATE TABLE t2(i INT) ENGINE=MYISAM; ERROR 1286 (42000): Unknown storage engine 'MyISAM' Node 2: MariaDB [test]> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) Do you get different results? Or your's is a different scenario altogether? Best, Nirbhay From the sounds of it, the sql_mode option NO_ENGINE_SUBSTITUTION is set differently in Nirbhay and Michele's scenarios, so the MyISAM table is converted to InnoDB with a warning in one, and fails in the other. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

3 2

[Maria-discuss] enforce_storage_engine galera cluster
by Michele Tota 22 Dec '15

22 Dec '15

Hi, we have a problem using enforce_storage_engine variable set to InnoDB. We have three servers in cluster (multi masters). When we issue create table with engine=MyISAM the engine is correctly enforced to InnoDB only on the node we are connected to. On the other nodes the replicated table is created as MyISAM. This is our servers configuration, the same on all nodes: ... enforce_storage_engine='InnoDB' wsrep_on=ON sql_mode='' ... How can we enforce InnoDB on all nodes? Thanks, Michele

3 5

[Maria-discuss] Announcing release for MariaDB 5.5 and 10.0 on CentOS Linux 6 and 7 x86_64 SCL
by Honza Horak 21 Dec '15

21 Dec '15

I am pleased to announce the immediate availability of versions 5.5 and 10.0 of the MariaDB server on CentOS Linux 6 and 7 x86_64, delivered via a Software Collection (SCL) built by the SCLo Special Interest Group (https://wiki.centos.org/SpecialInterestGroup/SCLo). QuickStart ---------- You can get started in three easy steps (example of MariaDB 10.0): $ sudo yum install centos-release-scl $ sudo yum install rh-mariadb100 $ scl enable rh-mariadb100 bash At this point you should be able to use MariaDB just as a normal application. An examples of commands run might be: $ service rh-mariadb100-mariadb start $ mysql In order to view the individual components included in this collection, including additional subpackages plugins, you can run: $ sudo yum list rh-mariadb100\* About Software Collections -------------------------- Software Collections give you the power to build, install, and use multiple versions of software on the same system, without affecting system-wide installed packages. Each collection is delivered as a group of RPMs, with the grouping being done using the name of the collection as a prefix of all packages that are part of the software collection. The collection rh-mariadb100 delivers version 10.0 of the MariaDB server (with daemon called rh-mariadb100-mariadb) and related client tools. The collection mariadb55 delivers version 5.5 of the MariaDB server (with daemon called mariadb55-mariadb) and related client tools. Both the collections include no client library libmysqlclient.so and client applications are advised to use the client library available in mysql-libs package from base system. For more on the MariaDB, see https://mariadb.org. The SCLo SIG in CentOS ---------------------- The Software Collections SIG group is an open community group co-ordinating the development of the SCL technology, and helping curate a reference set of collections. In addition to the MariaDB collections being released here, we also build and deliver other databases, web servers, and language stacks including multiple versions of MySQL, PostgreSQL, MongoDB Apache HTTP Server, NodeJS, Ruby, Python and others. Software Collections SIG release was announced at https://lists.centos.org/pipermail/centos-announce/2015-October/021446.html You can learn more about Software Collections concepts at: http://softwarecollections.org You can find information on the SIG at https://wiki.centos.org/SpecialInterestGroup/SCLo ; this includes howto get involved and help with the effort. We meet every second Wednesday at 16:00 UTC in #centos-devel (ref: https://www.centos.org/community/calendar), for an informal open forum open to anyone who might have comments, concerns or wants to get started with SCL's in CentOS. Enjoy! Honza SCLo SIG member

1 0

[Maria-discuss] MariaDB 10.0.23 now available
by Daniel Bartholomew 19 Dec '15

19 Dec '15

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.23. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.23 - Release Notes: https://mariadb.com/kb/en/mdb-10023-rn - Changelog: https://mariadb.com/kb/en/mdb-10023-cl - Downloads: https://downloads.mariadb.org/mariadb/10.0.23 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ 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/ 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

3 2

[Maria-discuss] New Question: Slow query log and long_query_time
by AskMonty KB 18 Dec '15

18 Dec '15

Hello, A new question has been asked in "Slow Query Log" by yu.d.sun_32615. Please answer it at http://mariadb.com/kb/en/slow-query-log-and-long_query_time/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- In my understanding if we enable slow query log, MySQL will capture all SQL statements that take more than long_query_time seconds to execute into the log. If so below testing puzzled me: Server version: 10.0.21-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r4144 === in the mysql CLI ================== ip-10-74-2-218$ root@localhost[galeratest] MYSQL> select now(); +---------------------+ | now() | +---------------------+ | 2015-12-18 11:12:50 | +---------------------+ 1 row in set (0.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> show variables like 'long%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> select count(*) from test_table; +----------+ | count(*) | +----------+ | 13 | +----------+ 1 row in set (0.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> select sleep(11); +-----------+ | sleep(11) | +-----------+ | 0 | +-----------+ 1 row in set (11.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> select count(*) from test_table; +----------+ | count(*) | +----------+ | 13 | +----------+ 1 row in set (0.00 sec) ip-10-74-2-218$ root@localhost[galeratest] MYSQL> ==== from the slow query log ================== # Time: 151218 11:13:12 # User@Host: root[root] @ localhost [] # Thread_id: 125 Schema: galeratest QC_hit: No # Query_time: 0.000163 Lock_time: 0.000075 Rows_sent: 1 Rows_examined: 13 SET timestamp=1450465992; select count(*) from test_table; # Time: 151218 11:13:48 # User@Host: root[root] @ localhost [] # Thread_id: 125 Schema: galeratest QC_hit: No # Query_time: 11.002494 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1450466028; select sleep(11); # Time: 151218 11:14:10 # User@Host: root[root] @ localhost [] # Thread_id: 125 Schema: galeratest QC_hit: No # Query_time: 0.000159 Lock_time: 0.000073 Rows_sent: 1 Rows_examined: 13 SET timestamp=1450466050; select count(*) from test_table; ================ Question - why the 'select count(*) from test_table' statement is captured while it seems right that select sleep(11) got captured and sleep(5) did not? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/slow-query-log-and-long_query_time/

1 0

[Maria-discuss] New mariadb.org in the works
by Otto Kekäläinen 18 Dec '15

18 Dec '15

Hello! We are about to renew the mariadb.org website. The idea is to replace the current mariadb.org and blog.mariadb.org with one single site that includes both the basic information about the software project and the MariaDB Foundation, as well as the blog. Work in progress is visible at https://new.mariadb.org/ Please send us your feedback!

1 1

[Maria-discuss] Amazon Aurora: based on MySQL or MariaDB?
by Lukas Lehner 17 Dec '15

17 Dec '15

Hi https://aws.amazon.com/rds/aurora/ Did Amazon write everything on their own? Is it based on MySQL or MariaDB? Lukas

3 2

[Maria-discuss] CONNECT version maturity
by Federico Razzoli 17 Dec '15

17 Dec '15

Hi all In this page: https://mariadb.com/kb/en/mariadb/connect/ I see that CONNECT 1.03.0007 (that is in MariaDB 10.1.5) is gamma-quality, but 1.04.0003 (that is in 10.1.9) is beta. I am confused. Is this a documentation mistake? Or maybe many new bugs were found? Regards Federico

2 1

[Maria-discuss] New Question: Does MariaDB Galera Cluster 10.0.21 version support gmcast.segment?
by AskMonty KB 17 Dec '15

17 Dec '15

Hello, A new question has been asked in "MariaDB Galera Cluster" by yu.d.sun_32615. Please answer it at http://mariadb.com/kb/en/does-mariadb-galera-cluster-10021-version-support-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, Server version: 10.0.21-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r4144 ip-10-74-2-218$ root@localhost[(none)] MYSQL> set global wsrep_provider_options="gmcast.segment=1"; ERROR 1210 (HY000): Incorrect arguments to SET I also tried to put wsrep_provider_options="gmcast.segment=1" in the my.cnf but cannot start the instance: 151217 7:59:58 [ERROR] WSREP: Unrecognized parameter '"gmcast.segment' 151217 7:59:58 [ERROR] WSREP: wsrep::init() failed: 7, must shutdown 151217 7:59:58 [ERROR] Aborting Denis -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/does-mariadb-galera-cluster-10021-version-support-…

1 0

[Maria-discuss] max key length in import
by Warren H. Prince 17 Dec '15

17 Dec '15

I’m considering moving from MySql to MariaDB so I did a dump today and attempted an import. It failed with the “Specified key was too long; max key length is 767 bytes” error. I dealt with this in MySql and knew to change file format to Barracuda. I zapped the partial import, did a fresh mysql_install_db, restarted the service and tried again to no avail. To confirm my settings immediately after the failure: MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_file_%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | | innodb_file_per_table | ON | +--------------------------+—————+ Does anyone have any suggestions? The dump is 795M compressed so it’s difficult to find the exact place where the problem is (I used zcat for the import). My distribution is: 10.0.22-MariaDB running on Ubuntu 15.04 Thanks in advance.

2 2