Fun with Bugs #100 - On MySQL Bug Reports I am Subscribed to, Part XXXIV

I delayed this post #100 in the "Fun with Bugs" series for few weeks - the previous one was published 4 weeks ago. The idea was to make it the last one, and for this I needed something to celebrate. Two days ago proper event happened, we have MySQL Bug #100000 reported! Here it is:

• Bug #100000 - "Provide an index hint that only affects the choice of index for NL join". This nice feature request was added by former optimizer developer in MySQL, Øystein Grøvlen. Hundreds of other feature requests are waiting for the attention both from the MySQL Verification Team and from developers, so good to see a feature request getting the number that nobody ever forget!

Actually Øystein Grøvlen created several interesting bug report during that day:

• Bug #99994 - "Index range scan is chosen where table scan takes 40% less time". Clear and simple bug report that relies on the world sample database.
• Bug #99995 - "Histogram is not used for filtering estimate when index is disabled".
• Bug #99996 - "Prefer histogram over index statistics when eq_range_index_dive_limit is exceeded". This was verified as a feature request.
• Bug #99997 - "Range estimates are usually off by a factor of 2 for large ranges". It was declared a duplicate of older bug report I am also subscribed to, Bug #73386 - "For ranges, innodb doubles estimates, or caps estimates to half the table". See also this MariaDB bug report, MDEV-19424 - "InnoDB's records_in_range estimates are capped at about 50%", and links from it for a lot of related discussions. Let's wait and see what vendor resolves this faster...
  The other report, Bug #99998 - "For large ranges, the range estimate will never exceed 50%", is probably also a duplicate of the same old bug.
• Bug #99999 - "EXPLAIN FORMAT=TREE does not show cost/rows for semijoin materialization". Yet another nice and clear bug report.

So, hardly anyone else had a chance to get that #100000 filed. As far as I can see, all these were reported during a very short period of 2 minutes, from "26 Jun 7:57" till "26 Jun 7:58"! Not sure how to do this without some automation or at least all the details ready for quick copy/pasting!

Now back to some older bugs I've subscribed to over last 4 weeks:

• Bug #99791 - "MySQL 8 orphaned table due to unchecked non-existent row format check." As reported by Marc Reilly, tables created in versions < MySQL 8 which use row_format COMPRESSED or REDUNDANT, where row_format is not set explicitly in the Table DDL allow users to create un-prefixed indexes on fields which exceed the maximum column size of 767 bytes. Upgrading to MySQL 8 do nothing with these tables, but as soon as new index is added and reboot happens, such a table becomes inaccessible. What a surprise!
• Bug #99794 - "MySQL 57 client is inefficient at bulkloads/binlog replay". In this bug report  Marc Reilly basically asks to back port the fix from MySQL 8.0.13.
• Bug #99800 - "ps_truncate_all_tables() does not work in super_read_only mode". This regression bug was reported by Lalit Choudhary.
• Bug #99805 - "mysql async client is incomplete". There is no way to determine file descriptor state (should it block on read or write), so it is impossible to use it in asynchronous contexts without busy looping. This bug report by Domas Mituzas was used in one discussion as an argument that MySQL bugs database still gets proper attention from MySQL engineers. It's truly so.
• Bug #99892 - "initialize with innodb_page_size=4096 gets "Specified key was too long" errors". This is a regression vs 5.7 (without a tag). As Mark Callaghan found out, one can not initialize MySQL 8 instance without errors with such a small innodb_page_size.
• Bug #99924 - "The record per key value from InnoDB is not suitable when n_diff is zero". As reported by Ze Yang, due to lack of locking when server reads the innodb_rec_per_key, the n_diff value may be 0 (not set) while the table->stat_n_rows is > 0. As a result (see great comment by Øystein Grøvlen), if a table object is opened during the recalculation of statistics, the rec_per_key for a column/index may be quite misleading. It will be interpreted as all rows have the same value, and the index will probably not be chosen for any non-covering scans. There is a patch suggested (to set rec_per_key to 1 or 10 in such case), as well as other suggestion to set the value REC_PER_KEY_UNKNOWN. Useful reading!
• Bug #99933  - "In-memory hash join will only use two-thirds of join buffer". Yet another bug report related to hash joins from Øystein Grøvlen, with a fix suggested. See also his Bug #99934 - "Hash join adds columns to the hash table that is not needed." There is a lot of work ahead to improve the implementation of this new feature in MySQL 8.
• Bug #99935 - "innodb_doublewrite_files is not correct when innodb_buffer_pool_size > 1G". Just 2 files are created instead of 16 according to the manual. This bug was reported by Satya Bodapati.
• Bug #99943 - "Hash join does not work for Semijoin and Antijoin". This bug report from Tibor Korocz was "Verified", but later comments suggest that it's more like wrong expectations/interpretation of cases when the feature has to be used (it is supposed to be used instead of BNL, but not instead of semijoin materialization and subquery materialization). Let's wait and see how it ends up...
• Bug #99966 - "Switching to use NUMA-SMART Counter for statistical counters". Great bug report from Krunal Bauskar, with a patch suggested. I hope to get a NUMA system one day myself to understand the challenges and performance problems there better.  

So, that's it, my very last post in the "Fun with Bugs" series that started more than 7 years ago. The series where I listed most of the interesting bug reports I keep an eye on, since Bug #2. It was a long way with a lot of fun and a lot of (rarely appreciated) work in the process, but now my watch has ended. I am not going to try to micro manage MySQL bugs processing any more and finally let the MySQL Verification Team do their job without my regular attention. Good luck!

Percona had recently started to blog about bugs, so I am sure they will keep an eye and share lists of important bugs on a regular basis. They should really care more than I do these days.

Fun with Bugs #74 - On MySQL Bug Reports I am Subscribed to, Part XI

For some reason the Committee of FOSDEM 2019 MySQL, MariaDB & Friends Devroom of all my talks submitted picked up the one on how to create a useful MySQL bug report, so I have no options but continue to write about MySQL bugs, as long and MySQL Community wants and even prefers to listen and read about them... That's what I do, with pleasure.

Today I'll continue my series of posts about community bug reports I am subscribed to with the review of bugs reported since October 1, 2018, starting from the oldest and skipping those MySQL 8 regression ones I've already commented on:

• Bug #92609 - "Upgrade to 8.0.12 fails". This bug reported by Frederic Steinfels is about upgrade from MySQL 5.7 that leads to crash. Nice workaround was found:
  

  "The work around is delete all .TRG file (or move them to out side of mysql data folder) then update. After success we can re-create the trigger."

  The bug is really fixed in MySQL 8.0.14 according to the last comment, but for some reason it is still "Verified". Probably will be closed when MySQL 8.0.14 is released.
• Bug #92631 - "importing dump from mysqldump --all-databases breaks SYS schema due to routines". This bug affecting MySQL 5.7 (and not 8.0) was reported by Shane Bester. Workaround is actually documented in the manual - add sys schema explicitly while dumping and dump it separately, then re-create.
• Bug #92661 - "SELECT on key partitioned enum reading all partitions instead of 1". Interesting corner case found by Frederic Steinfels. MariaDB 10.3.7 also seems affected:
  

  MariaDB [test]> explain partitions select id from product where outdated='0';
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  | id   | select_type | table   | partitions | type | possible_keys | key      |
  key_len | ref   | rows | Extra                    |
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  |    1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated |
  1       | const |    2 | Using where; Using index |
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  1 row in set (0.002 sec)
  
  MariaDB [test]> explain partitions select id from product where outdated='1';
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  | id   | select_type | table   | partitions | type | possible_keys | key      |
  key_len | ref   | rows | Extra                    |
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  |    1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated |
  1       | const | 2048 | Using where; Using index |
  +------+-------------+---------+------------+------+---------------+----------+-
  --------+-------+------+--------------------------+
  1 row in set (0.002 sec)

• Bug #92809 - "Inconsistent ResultSet for different Execution Plans". The full test case is not public and it took a lot of arguing until this bug (reported by Juan Arruti) was finally "Verified". Based on the workaround, setting optimizer_switch='materialization=off', this feature of MySQL optimizer is still problematic.
• Bug #92850 - "Bad select+order by+limit performance in 5.7". As Sveta Smirnova demonstrated, there are still cases when FORCE INDEX hints really needed to help optimizer to use proper plan, even in somewhat obvious case of single table and single proper index... What a surprise!
• Bug #92882 - "MTS not replication crash-safe with GTID and all the right parameters." As  Jean-François Gagné proved, at least statement-based multi-threaded replication with GTIDs is not safe in case of OS crash. Good that there is an easy enough workaround: stop slave; reset slave; start slave; See also his Bug #93081 - "Please implement a better relay log recovery." that refers to several more known problems with relay log recovery.
• Bug #92949 - "add auto_increment column as PK cause RBR replication inconsistent". This probably should never happen in production (adding primary key while data are already active changed concurrently), but still this is a nice corner case reported by Fungo Wang.
• Bug #92996 - "ANALYZE TABLE still locks tables 10 years later". Domas Mituzas is trying hard to escalate this well known problem of blocking queries if ANALYZE TABLE was executed at some wrong time (when long running query against the table was in progress). The problem was resolved this year in Percona Server, see this blog post. See also my MDEV-15101 (fix is planned for version 10.4 in MariaDB).
• Bug #93033 - "Missing info on partitioned tables in I_S.INNODB_COLUMNS after upgrade to 8.0". Yet another regression in MySQL 8 vs 5.7 was reported by Alexey Kopytov.
• Bug #93049 - "ORDER BY pk, otherdata should just use PK". There is no reason to use filesort, as Domas Mituzas kindly noted. MariaDB is also, unfortunately, affected.
• Bug #93083 - "InnoDB: Failing assertion: srv_read_only_mode || trx->in_depth > 0". This Severity 6 bug (only debug binaries are directly affected) was reported by Ramesh Sivaraman from Percona QA. At least it was verified instantly (I've subscribed to double check what happens to bugs with low severity levels).

I'll stop for now. More detailed review of remaining bugs reported in November is coming soon.

Sheep are everywhere in East Sussex and bugs are everywhere in MySQL. Not that many, but still they are visible.

To summarize my conclusions from this list:

1. Sometimes it takes too much efforts to force proper bug report processing. I had written more about this here.
2. Having materialization=on in optimizer_switch in MySQL 5.7+ may cause wrong results. Take care to double check.
3. There still cases of single table SELECTs where optimizer can do a much better job. FORCE INDEX helps, sometimes.
4. Multi-threaded statement-based replication in MySQL 5.6 and 5.7 is not crash safe, even with GTIDs, relay_log_info_repository-TABLE and relay_log_recovery=ON. A lot of improvements in relay log recovery are needed.
5. Oracle engineers still care to document workarounds in active public bug reports. This is great!
6. Percona still fixes some really important and annoying bugs way before other vendors.
7. MySQL 8 is different in many small details, so regressions are to be expected.

MySQL Support People - Those Who Joined Oracle to Work on MySQL

In this post I'd like to mention my colleagues from MySQL Support who had joined the team when Oracle already acquired Sun (and MySQL as a part of it), in 2010-2012. There were several really great engineers among them:

• Jesper Krogh - with his 31 public bug reports, numerous blog posts (check also this one), ps_tools and presentations, Jesper is well known to MySQL community. He had joined Oracle as Senior MySQL Support Engineer in March, 2011 and is a Senior Principal Technical Support Engineer at Oracle now. Check one of his "Verified" bug reports for SYS schema, Bug #77853. His tutorial on Performance Schema from 2013 was a really great one!
• Santo Leto - he had joined Oracle in December 2010 (as contractor initially) and made a good career there as Principal Support Engineer and then, since April 2013, a team manager of EMEA MySQL Support team in Oracle. We worked closely together during his first year(s) in Oracle. Santo was a reliable SSC, good MySQL Cluster Support engineer, Windows guru of a kind, and a contributor to the MySQL Community, as one can conclude from his 54 public bug reports. Check the oldest his bug report that is still "Verified", Bug #33287. I'd never forget a terrible weekend shift he took in 2012 and managed to survive, when MySQL Cluster was failing for one major European telecom provider... I had to step in to help with all customer issues not related to that terrible incident (that made me think this software is really not supportable unless you can involve its developers, the idea I still deeply believe in). I can not tell you how good Santo was as a team manger, as I quit in August, 2012, well before he switched to management. Recently Santo also quit from Oracle and now he is a Global Manager, Customer Support at Neo Technology, creators of Neo4j.
• Arnaud Adant - he is one of the most brilliant MySQL Support engineers I've ever worked with. Really good skill set for the job and proper attitude, since day one. Arnaud joined Oracle as Senior Support Engineer in January, 2011, became Principal in 2012 and worked there till May, 2014. Then he left to do some Java development for some time, but now he is back to MySQL/MariaDB world as a DBA at Jump Trading LLC. In Oracle Arnaud was one of the core support providers for EMEA customers. He had always contributed to MySQL Community, and I see 17 public bug reports from him (probably he had several accounts and reported more), to say nothing about his great presentation and tutorial from MySQL Connect@Oracle OpenWorld conference. He also did some bugs processing, check my Bug #68079 that is still assigned to him formally :) I am really proud that I worked with him for more than 1.5 years and consider him my good friend in real life.
• Umesh Shastry - he had joined Oracle in May, 2011, when everything in MySQL was already done "Oracle way". We worked closely together since his first days and till my last day in Oracle, as he had always been interested in my work on bugs, experience and approaches to the job. Umesh had worked a lot as a key Support engineer for MySQL Cluster during EMEA hours and soon gained a well deserved respect from MySQL Cluster developers, customers and many "old-timer" colleagues in Support. After I left Oracle he started to work more and more on public bugs processing and eventually, in 2013, he ended up in my Bugs Verification Team, doing large part of my job. When Sveta Smirnova joined me in Percona, Umesh (who now is a Principal Technical Support Engineer) took over some of her job duties as well, and at the moment he is the most important person in the processes of community bugs verification and bug fixing in GA MySQL versions. Just trust me, he is the key to success for MySQL Community in everything related to bug fixing in Oracle. That's why I mention his name many times in each and every post in my "Fun with Bugs" series. Umesh had reported 13 public bugs for MySQL, but you should understand that his job is mostly to have as few non-processed bugs as possible.
• Anand (Ananadakumar S) - he had also joined Oracle in May, 2011 and now he is a Principal Support Engineer there. I can not find any public bug reports by him (as he mostly worked on usual MySQL customer issues in Oracle, based his previous production MySQL DBA experience), but check Bug #50285 for his comment. We started to work closely together probably only during my last months in Oracle, in 2012, so I had few time to work on proper habits towards bugs and MySQL Community with him. Still, I remember him as a reliable and useful tam member.
• Uma Bhat (Uma Hoysala) - she had also joined Oracle in May, 2011 and worked in MySQL Support as a Senior Support Engineer more or less till October 2015, when she switched to management role. She is now a Product Support Manager at Oracle. I can not find public bug reports, but check Bug #11696 for her comment.As with Anand, we started to work closely together with Uma on bugs, compiling from source and similar boring matters only during my last months in 2012. But bugs processing aside, Uma is well known for her community contributions to MySQL (check her blog posts here).
• Bhupinder Singh - he is a Principal Technical Support Analyst in Oracle now, probably still working mostly on MySQL Cluster customer issues. I think he joined MySQL Support some time before May, 2011. I see one public bug report from him here and one more from this account, so he seems to be interested in NDB cluster for a long time. We had not so many chances to work together as I tried my best to keep myself as far from MySQL Cluster as possible, and it was possible for me during weekdays and EMEA hours (when we both worked), thanks to hard work of my colleagues named here and in the previous post.
• Staffan Flink - he joined in January, 2012 as a Senior MySQL Support Engineer and was a very reliable colleague in that role. As far as I understand he left Oracle soon after me, in November, 2012, Now Staffan is a MySQL DBA / Operations Engineer at eTRAVELi. Staffan is a member of Sweden MySQL User Group, but otherwise I do not see any his public contributions to MySQL Community recently.

That's all whom I remember at the moment of those who did Support engineer's job on a regular basis and contributed to MySQL Community in a way I can find. Please, correct me if I missed somebody (it happens all the time, but I have to rely on my memory only, and it's selective and faulty at times).

Next post will be devoted to pure MySQL Support managers, those who had rarely (if ever) worked on technical issues from customers, unless we, Support Engineers, made terrible mistakes... Stay tuned!

My Guidebook for Percona Live Amsterdam, 2015 - Part I

Unfortunately I am not going to Percona Live Amsterdam 2015 that starts next week. Somebody has to work and keep those customers happy who prefer to work as usual even during such a festival.

I am still asking myself: if I'd be able to go there, what tutorials and sessions I'd like to attend (besides those I'd present)? "All of them" is not a correct answer, as often great and useful sessions happens at the same time in different places. So, I decided to create a list of sessions for myself (as a reminder to check slides at least after they are published), and I'd like to share it.

I tried to pick up one session per slot. but for most slots this was liteally impossible even for myself - I just can not decide now what's more important for my very limited set of database-related interests. So, you will see 2 sessions from most time slots. I tried to add some comments about speakers and other reasons for my interest.

On September 21 we have tutorials, and I'd visit one of these two (they are both full day ones it seems):

• Advanced Percona XtraDB Cluster in a nutshell, la suite : Hands on tutorial not for beginners! - I've visited tutorials on PXC conducted by Frederic Descamps (aka LeFred) and other my colleagues more than once, and it's always great experience and easy way to study something new about Galera clusters. Be ready for a lot of work to do during this tutorial if you really want to follow it closely and get experience
• Query Optimization: From 0 to 10 (and up to 5.7) - Usually I try to keep myslef as far from clusters (including PXC/Galera) as possible, so chances are high that I'd visit this tutorial by my former colleague Jaime Crespo instead. He is a very experinced trainer who now works on real life problems as DBA, so it's quite possible that I'd study a trick or two or get a chance to discuss some real life query optimization issues and approaches.
  

No comments on reception @ Delirium Cafe, sorry.

September 22 starts with keynotes, and usually I am present at all of them. But even if I'd meet colleagues to talk to ASAP durings keynotes slot, I'd still like to visit at least this one, MySQL and MongoDB for web-scale data management., by Mark Callaghan from Facebook.I am following him as a writer, speaker and customer for many years already and I am really interested in his opinions on MongoDB. Really, why would a person like him decide to work on yet another database (after Informix, Oracle, MySQL, many more), especially MongoDB? I'd like to understand...

 The first sessions slot that day is really a problem for me. I'd like to visit these sessions:

• InnoDB: A journey to the core - I'd like to be there both because I am interested in InnoDB internals and because all previous sessions by Jeremy Cole and Davi Arnaut I had a chance to attend were just great.
• XtraDB 5.6 and 5.7: Key Performance Algorithms -on the other hand, I'd like to listen to my colleague Laurynas. I do not yet follow InnoDB changes in 5.7 really closely (if only new types of locks added) and even less I know about Percona's plans on XtraDB for 5.7. Good chance to find out.

 Next slot is again a problem:

• State of MySQL Group Replication - It was nice to listen to Nuno Carvalho last year at Oracle Open World. This is a nice feature that has a long way to go still to be ready to compete with Galera, but it may go far beyond it as well and become a foundation for new HA architectures for MySQL. Who knows... I'd like to find out what they were able to achieve over last year.
• Performance Schema and Sys Schema in MySQL 5.7 - at the same time, I'd really want to listen to Mark Leith, whom I named a "godfather of PERFORMANCE_SCHEMA" two years ago. I do not speak on this topic any more, but I am still intersted in proper instrumentation for MySQL.

After the lunch I'd go directly to attend Giuseppe Maxia's Pivot tables: analytics in pure SQL. I used to be a big fan of Oracle DBMS's analytical functions back in my Oracle-related days, till 2005. I always wonder about the ways to live without them in MySQL and still execute queries efficiently. Also, I just like to listen to Giuseppe no matter what he is talking about.

Next slot is also clear for me. I'd go to The highs and lows of semi-synchronous replication to find out more on Facebook's experience with semi-sync replication.

Then I'd have a problem again:

• MySQL 5.7: What Is New in the Optimizer? - I am always happy to meet my colleague since good old Sun's days Manyi Lu and find out what's going to happen with MySQL optimizer in 5.7 GA from
  Olav Sandstå. I know a lot about their plans since Oracle Open World 2014, but it would be useful to check the progress and details now when MySQL 5.7 GA is probably 100% feature complete.
• Solid State Storage for Your MySQL Databases: What You Need to Know to Optimize Performance -on the other hand, I'd like to listen to Peter Zaitsev explaining how to use SSDs efficiently for MySQL. These days I see that customers have a lot of hope on SSDs, but often are still faced with notable I/O performance problems. Maybe I just miss few things to check while trying to help them...

The last slot for the day is also intersting:

• Database Encryption on MariaDB 10.1 - Customers often ask about encryption for MySQL data, and it seems MariaDB finally has the answer that would make them happy. It's useful to check the details with Sergei Golubchik to set proper expectations to them. Blame me for whatever, but I like to meet MariaDB engineers. Most of them were my colleagues back in MySQL AB, and they still do their job well.
• Anatomy of a Proxy Server: MaxScale Internals -I had not cared much about this new technology until recently, but it becomes incresingly popular from what I read in different sources. Maybe it's time to study how it works andwhat it can provide.

Again, no comments on Community Dinner at Booking.com - I wish I'd be there... 

More on sessions I'd like to attend on September 23 in the next post that should be ready on Monday, September 21.