April 2017 - discuss - lists.mariadb.org

[Maria-discuss] Inconsistent Query Results from Dependent Subquery
by Sam Vaughan 27 Apr '17

27 Apr '17

I'm seeing some odd behaviour on MariaDB 10.1.22 that's got me scratching my head. My production servers (a master and two slaves) are returning incorrect query results, but when I run the same query on the same data elsewhere I always get the correct results, even on a VM with matching OS, H/W arch and MariaDB version. The problem is triggered by using "ORDER BY x DESC LIMIT 1" in a dependent subquery on a single table. The query is being run on a small InnoDB table that very rarely changes. Each row contains a date, an integer identifier, a sequence number and a handful of applicable settings that are all integers. Unfortunately I can't create a simplified reproducible test case, but the following example shows the sort of query I'm working with. Imagine a table that looks like this: CREATE TABLE `settings` ( `date` date NOT NULL, `id` int(11) NOT NULL, `seq` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY `date_id_seq` (`date`,`id`,`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; The table is designed to store a sequence of values belonging to something identified by the ID. Each sequence of values has a date stamp, and can be replaced at a later date by a new sequence of values: +------------+----+-----+-------+ | date | id | seq | value | +------------+----+-----+-------+ | 2011-11-01 | 1 | 0 | 6 | | 2011-11-01 | 1 | 1 | 3 | | 2011-11-01 | 1 | 2 | 9 | | 2011-11-01 | 2 | 0 | 6 | | 2011-11-01 | 2 | 1 | 1 | | 2011-11-01 | 3 | 0 | 6 | | 2011-11-01 | 3 | 1 | 8 | | 2012-04-01 | 1 | 0 | 4 | | 2012-04-01 | 1 | 1 | 3 | | 2013-06-01 | 3 | 0 | 5 | +------------+----+-----+-------+ The query is asking for all the IDs that had a particular first value set as of a particular date, in this case a first value of 6 as of '2012-07-01': SELECT s1.id FROM settings s1 WHERE s1.seq = 0 and s1.value = 6 and s1.date = ( SELECT s2.date FROM settings s2 WHERE s2.id = s1.id and s2.date <= '2012-07-01' ORDER BY s2.date DESC LIMIT 1 ); This query correctly returns IDs 2 and 3, and not ID 1 since the data for '2012-04-01' overrides the data for '2011-11-01'. In my equivalent situation, my production servers are returning 1, 2 and 3. Everywhere else I try to run it I get just 2 and 3 as expected. The table in question changes rarely, so it's trivial to show that the test system I've set up is working on the same data as the production systems. I've selected the entire table to out-files and diffed them, I've restored the test system from a mysqldump backup of production, and I've even run XtraBackup and prepared it with --export, then followed the instructions to import the table files into my test system just in case there's something weird in the production table's .ibd file. No matter what I do, I can't reproduce the problem anywhere but on my production systems. Testing has shown that minor changes to the query will fix the result set. Changing it to use "max()" instead of "order/limit" fixes it. Removing the date test from the where clause also fixes it. I don't want to just change all my queries like this to use MAX() and be done with it, because this weirdness will always be in the back of my mind. Is my query insufficiently specific? Have my queries of this type only ever returned the right results by accident until I moved to MariaDB recently? I haven't tried adding an index to the date column yet, because it too might make the problem vanish and I'll be none the wiser. I'd rather work out how to make the problem reproducible then dig in further to expose the cause. I thought that my InnoDB file could have been corrupt, but preparing the XtraBackup didn't detect it. Perhaps I've triggered a bug in MariaDB that needs very special conditions to appear? That seems unlikely but I'm at a bit of a loss now. Any suggestions anyone might have would be very gratefully received! Kind regards, Sam

1 0

[Maria-discuss] MariaDB Server maintenance policy for 10.0
by Colin Charles 27 Apr '17

27 Apr '17

Hi! I just read: https://mariadb.org/about/maintenance-policy/ It says the 5 year boundary date for support ending for 10.0 is 31 Mar 2019, and later goes on to specify why 5.5 has a later EOL, "The MariaDB Foundation may decide to maintain selected releases beyond the normal 5 years if they are in exceptionally widespread use. As MariaDB 5.5 is included in multiple prominent Linux distributions with a lifespan beyond 2017, the Foundation is committed in maintaining it until 2020." Is SUSE Enterprise Linux Server 12, with MariaDB Server 10.0, not considered a “prominent Linux distribution”? SLES12 released in 2014, comes with MariaDB Server 10.0, and requires at least 10 years of support (https://www.suse.com/releasenotes/x86_64/SUSE-SLES/12/) Just checking to see if the maintenance policy above is in error Thanks -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

3 4

Re: [Maria-discuss] Are some MyISAM settings used for Aria
by Federico Razzoli 25 Apr '17

25 Apr '17

Hi Jocelyn, The article you linked is quite old, so I wanted to check the source code to find if something changed. I didn't investigate much but I searched in MariaDB's master branch via GitHub. The search strings were 'read_rnd_buffer_size' and 'read-rnd-buffer-size'. I found nothing except for some test files and the variable initialization at server startup. I repeated the same searches in MySQL master branch, and again, I found only tests and initialization. Can you explain this? The variable is unused now? Federico -------------------------------------------- Lun 24/4/17, jocelyn fournier <jocelyn.fournier(a)gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] Are some MyISAM settings used for Aria A: "Sales" <info(a)smallbusinessconsultingexperts.com> Cc: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Lunedì 24 Aprile 2017, 09:16 Hi Steve, Le 24 avr. 2017 3:28 AM, "Sales" <info(a)smallbusinessconsultingexperts.com> a écrit : On Apr 23, 2017, at 4:12 PM, jocelyn fournier <jocelyn.fournier(a)gmail.com> wrote: Hi Steve, Le 23/04/2017 à 19:32, Sales a écrit : On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M read_rnd_buffer_size and join_buffer_size are not MyISAM specific settings, did you try to modify/not modify only those ones ? HTH, Jocelyn Really? So, the doc is wrong? https://mariadb.com/kb/en/ mariadb/server-system- variables/#read_rnd_buffer_ size Says, MyISAM. So, are you saying that is mistaken? If so, that would make sense At least according to https://www.percona.com/blog/2007/07/24/what-exactly-is-read_rnd_buffer_siz… it's not. BTW it has been updated in the mysql manual to mention it optimizes mrr for any engine :https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_rnd_buffer_size MariaDB KB would need an update as well. Jocelyn _______________________________________________ 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 4

[Maria-discuss] Are some MyISAM settings used for Aria
by Sales 24 Apr '17

24 Apr '17

On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M Looking in the information_schema, we have one entire schema of tables still using MyISAM (third party system), and, this schema is not used in any daily processing. The only other MyISAM tables are in the mysql schema. So, nothing else, not one table. The way I understand aria, it should be used instead of MyISAM even for tmp tables given our settings. We have a long running set of programs that do things over night to manage products, many millions of them, ends up processing at least 50 million rows of various tables, thousands of mysql statements. This process has no MyISAM as noted. When I disable the settings above for MyISAM, a significant runtime increase is noted, I’ve repeated this 5 times now over a few week period. The increase is 20%, pretty significant, and that 20% never occurs when not changing settings. So, it would seem unlikely that I am just that unlucky. I am curious as to why those settings for MyISAM would have an impact? There are certainly tmp tables in the process (or internally created tmp tables) and the rest is innodb. Any good potential reason I am missing? I have not really looked into aria much before, read most of the mariadb doc. Maybe I missed something key, it appears likely I did! We have the following somewhat corresponding aria settings: aria_pagecache_buffer_size = 256M aria_sort_buffer_size = 256M Steve

2 3

[Maria-discuss] R: Are some MyISAM settings used for Aria
by Federico Razzoli 24 Apr '17

24 Apr '17

Steve, aria_used_for_temp_tables only affects internally created temporary tables, not the temp tables created explicitly by applications. So I suggest: * check if your applications create MyISAM (temporary) tables * use the slow log (long_query_time=0, min_examine_rows=0) and pt-query-digest to check which queries are slower when you comment those settings Federico -------------------------------------------- Dom 23/4/17, Sales <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: [Maria-discuss] Are some MyISAM settings used for Aria A: maria-discuss(a)lists.launchpad.net Data: Domenica 23 Aprile 2017, 19:32 On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M Looking in the information_schema, we have one entire schema of tables still using MyISAM (third party system), and, this schema is not used in any daily processing. The only other MyISAM tables are in the mysql schema. So, nothing else, not one table. The way I understand aria, it should be used instead of MyISAM even for tmp tables given our settings. We have a long running set of programs that do things over night to manage products, many millions of them, ends up processing at least 50 million rows of various tables, thousands of mysql statements. This process has no MyISAM as noted. When I disable the settings above for MyISAM, a significant runtime increase is noted, I’ve repeated this 5 times now over a few week period. The increase is 20%, pretty significant, and that 20% never occurs when not changing settings. So, it would seem unlikely that I am just that unlucky. I am curious as to why those settings for MyISAM would have an impact? There are certainly tmp tables in the process (or internally created tmp tables) and the rest is innodb. Any good potential reason I am missing? I have not really looked into aria much before, read most of the mariadb doc. Maybe I missed something key, it appears likely I did! We have the following somewhat corresponding aria settings: aria_pagecache_buffer_size = 256M aria_sort_buffer_size = 256M Steve _______________________________________________ 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 3

Re: [Maria-discuss] Are some MyISAM settings used for Aria
by Federico Razzoli 24 Apr '17

24 Apr '17

Once you see which queries are slower, you can find which changes affected the query. Execution plans are not expected to change when you resize a buffer, but you can measure the effect of resizing the buffers. SHOW GLOBAL STATUS LIKE 'Handler%' can also help you to measure WHICH PART of a query becomes slower/fast after changing a variable. Usually those buffers should be left on their default values. If you really need to change them for some queries (which is not common) the application should do it at session level to avoid impacting the whole workload. Federico -------------------------------------------- Dom 23/4/17, Sales <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: Re: [Maria-discuss] Are some MyISAM settings used for Aria A: "Federico Razzoli" <federico_raz(a)yahoo.it> Cc: maria-discuss(a)lists.launchpad.net Data: Domenica 23 Aprile 2017, 22:59 > On Apr 23, 2017, at 3:00 PM, Federico Razzoli <federico_raz(a)yahoo.it> wrote: > > Steve, > > aria_used_for_temp_tables only affects internally created temporary tables, not the temp tables created explicitly by applications. > So I suggest: > * check if your applications create MyISAM (temporary) tables > * use the slow log (long_query_time=0, min_examine_rows=0) and pt-query-digest to check which queries are slower when you comment those settings > > Federico > Yes, the default engine for tables is innodb as evidenced by default_storage_engine, so, those are not using MyISAM. The default_tmp_storage_engine defaults to nothing, so, according to the doc, this means it uses default_storage_engine, which means innodb again. I was merely pointing out that all temp tables are not using MyISAM, or at least should not be. Have used the slow query log and Percona toolkit with various length settings and did not give much benefit sadly. It showed some queries running longer, but, that did not help too much as far as why since the queries were using optimal query paths. We have many long running queries and that’s fine as these are calculations and mass imports from suppliers, pricing, etc. We’ve just been unable to make sense of the why of it. A second potential hint is mariadb 10.1 has performed slower than mariadb 5.5 on the same machine, and thus far, we’ve been unable to isolate why. It’s not slower by a lot, maybe 5%. The word MyISAM does not exist anywhere in our code. Simple grep -ri shows this. It’s puzzling to me. Kind of makes me want to explicitly set default_tmp_storage_engine just to see if any effect. I am just checking if anything might still use MyISAM despite these settings. It’s not a big deal as the settings don’t use a tremendous amount of memory by any means, but, was hoping to just size MyISAM all the way down. And then became curious as to why this might be. Thanks!

1 0

[Maria-discuss] MariaDB Galera Cluster 5.5.55 and Connector/J 2.0.0 now available
by Daniel Bartholomew 22 Apr '17

22 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.55 Stable (GA) and MariaDB Connector/J 2.0.0 Release Candidate (RC). See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.55 - Release Notes: https://mariadb.com/kb/en/mgc-5555-rn/ - Changelog: https://mariadb.com/kb/en/mgc-5555-cl/ - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.55 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 Connector/J 2.0.0 - Release Notes: https://mariadb.com/kb/en/mcj-200-rn/ - Changelog: https://mariadb.com/kb/en/mcj-200-cl/ - Downloads: https://downloads.mariadb.org/connector-java/2.0.0/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ - - 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/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

Re: [Maria-discuss] MySQL Workbench Fork?
by Federico Razzoli 21 Apr '17

21 Apr '17

Looks like a lot of people want to use GUIs. Which is absolutely legit, but frankly, I don't agree that any database GUI should be considered enterprise software. Selecting checkboxes and selecting from drop-down menus is much slower, and much more error-prone, than writing SQL queries. I recognize that they allow you to avoid learning ALTER TABLE syntax, which potentially turns a person with no specific skill into a database architect. Is this an enterprise feature? Also, you never really know what you are doing. Some examples? * You read the database size. Where did the GUI read this value? I want to see system tables and file sizes, not values with vague names. * Ok, you just issued an SQL statement. But first, the GUI transparently ran tons of SET statements. What you did is what you wanted to do, or is it the random result of GUI's authors overactive creativity? :) Please, don't misunderstand me. I'm not saying that GUIs are useless or badly designed. But I wouldn't call them enterprise software, and I would not suggest them in a professional environment. Cheers, Federico -------------------------------------------- Gio 20/4/17, <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: Re: [Maria-discuss] MySQL Workbench Fork? A: maria-discuss(a)lists.launchpad.net, "Zach Lym" <zachlym(a)indolering.com> Data: Giovedì 20 Aprile 2017, 21:51 Totally agree. It's an necessary part for us. So many features missing. Tired of their warning too about mariadb. Steve Fatula On Apr 20, 2017, 2:13 PM -0500, Zach Lym <zachlym(a)indolering.com>, wrote: I convinced my boss to use MariaDB for a new project but it appears that the workbench is incompatible with new features of MariaDB. I've looked at the alternatives listed on the website, but they are all either proprietary or Windows only. I just can't sell my boss on switching to MariaDB without workbench. Free software isn't free, but I think Workbench is a big driver of commercial and enterprise use. It's easy to kvetch from the outside, but I feel as though MariaDB has just lost some ground. Has management considered forking Workbench? Thank you, -Zach Lym _______________________________________________ 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 _______________________________________________ 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

1 0

[Maria-discuss] innodb table single column index max length
by butler 18 Apr '17

18 Apr '17

we know the mysql have a limit that the max index length on a single column is 767.I want know how the 767 come from ? why not 799 or 800 etc.

2 2

[Maria-discuss] MariaDB 10.3.0 Alpha and 5.5.55 Stable now available
by Daniel Bartholomew 18 Apr '17

18 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.3.0 Alpha and MariaDB 5.5.55 Stable (GA). See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.3.0 - Release Notes: https://mariadb.com/kb/en/mdb-1030-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1030-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.3.0 About MariaDB 10.3: - https://mariadb.com/kb/en/what-is-mariadb-103/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB 5.5.55 - Release Notes: https://mariadb.com/kb/en/mdb-5555-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5555-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.55 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ 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/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0