Wednesday, July 31, 2013

Fun with Bugs #20 - welcome MySQL 5.6.13!

MySQL 5.6.13 is released today! Installation is in progress right now, so I had not checked anything yet personally in reality, but we have release notes to study.

I'll base my quick review on my older posts devoted to known bugs in MySQL 5.6.12 in three main areas: InnoDB, optimizer and replication. All quoted text below is taken from the release notes.

Let's start with InnoDB. From my "top 10" list I only see the following bug fixed in 5.6.13:
  • Bug #69316. "Performance; InnoDB: A code regression introduced in MySQL 5.6 negatively impacted DROP TABLE and ALTER TABLE performance. This could cause a performance drop between MySQL Server 5.5.x and 5.6.x. (Bug #16864741, Bug #69316)"
Not much, really. But at least Bug #69623 is fixed (so you can use multi-file shared tablespace again) as expected and there are many other paragraphs in release notes speaking about InnoDB. Maybe it's only me having priorities not 100% corresponding to Oracle ones in this case.

Now, let's move to replication. From my "top 10" replication bugs in 5.6.12 I see the following fixed:
  • Bug #69369. "Replication: The condition leading to the issue fixed in Bug #16579083 continued to raise an error even though the condition itself no longer cause the issue to occur. (Bug #16931177, Bug #69369)."
  • Bug #69341 . "Replication: When rpl_semi_sync_master_timeout was set to an extremely large value, semi-synchronous replication became very slow, especially when many sessions were working in parallel. It was discovered that the code to calculate this timeout was inside the wait loop itself, with the result that an increase in the value of rpl_semi_sync_master_timeout caused repeated iterations. This fix improves the method used to calculate wakeup times, and moves it outside of the wait loop, so that it is executed one time only. (Bug #16878043, Bug #69341)"
  • Bug #69096 - this actually fixed a minor remaining problem of referencing non-existent session variable, GTID_NEXT_LIST. The real problem was already fixed in 5.6.12 (see Bug #69045). 
That's all. But 2 serious bugs I cared about (among many others replication related fixes, I see 10 or so) is not bad.

Finally, regression bugs in optimizer. The following are fixed in 5.6.13:
  • Bug #69471. "When selecting a union of an empty result set (created with WHERE 1=0 or WHERE FALSE) with a derived table, incorrect filtering was applied to the derived table. (Bug #69471, Bug #16961803)"
  • Bug #69410. "For queries with ORDER BY ... LIMIT, the optimizer could choose a nonordering index for table access. (Bug #69410, Bug #16916596)"
  • Bug #68897. "Some LEFT JOIN queries with GROUP BY could return incorrect results. (Bug #68897, Bug #16620047)".
3 out of 10 is good, but many optimizer regressions remain.

Installation finished, time for real life checks (and more posts based on results):

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


MySQL 5.6.13 looks promising enough from the first sight,and I surely hope new MySQL Cluster 7.3 release based on it (instead of a really much worse 5.6.11) is coming soon.

Tuesday, July 30, 2013

What is the problem with "To be fixed later" bug status?

Bug #69842 was actively discussed on Facebook recently. Mostly not it's technical content - people do agree that InnoDB probably needs separate doublewrite buffer(s) for every possible InnoDB page size. It's more about bugs processing approaches, so I have to say something about this.

The story was simple enough. I've mentioned this bug in my previous post and yesterday my dear friend Sinisa added some comments and set status to "To be fixed later". This had made bug reporter (who co-incidentally is a "small data engineer" at Facebook) unhappy, because (let me quote):
"...bugs can still be closed as "to be fixed later", which means even it may affect other users (it affect us, at least), nobody will see that as an open issue MySQL has - especially that some of us would like to be fixed sooner rather than later."
This story had got a happy end actually. James Day stepped in and set status "Verified" for this report as a feature request. But question remains: was setting status to "To be fixed later" a proper action in this case, what does this status mean and does it lead to any problems?

Note in any case that "To be fixed later" status had been used in public bugs database for 8+ years at least (for me it was just always there), this is NOT a recent evil Oracle invention. Let me quote my old post explaining both this and "Won't fix" status (also mentioned in that recent discussion):
  • To be fixed later - it means that while the problem exists and verified, there is no way to fix it in current GA or development versions, or fix needs serious changes in software or data format, or serious development efforts and thus requires long term planning. Usually it means "To be fixed never".
  • Won't fix - developers just do not want to fix this. This is more a "feature" than a "bug", even if user thinks differently. Users often ask for some things that, while make sense for them and their use case, may be against SQL standard or have acceptable workarounds in frames of current implementation etc.
As for problems, Domas later claimed that "To be fixed later" bugs are not visible in searches later. I'd say it depends on how you search. Click here to get the list of all (234 at the moment of writing) bugs having "To be fixed later" status. The status is "terminating" though, and thus the bug in this status is not visible when you search for "Active" bugs. Just try to search for "truncate" among active bugs using bugs database search, and you will NOT find Bug #68184, while the problem of TRUNCATE being surprisingly slow for InnoDB table when buffer pool is big (while DROP is fixed long time ago) is known and important to solve. On the other hand, simple Google search for site:bugs.mysql.com truncate slow shows this bug to me on the first page of results. Note that by default bugs database does NOT include feature requests (S4 bugs) into the search, so current status of bug report from Facebook may be not much better for searches actually.

So, depending on the way you search, having bug with status "To be fixed later" may be a problem (but only if you are not experienced in MySQL bugs search). Any other problems? One is mentioned in my quote above: I made a statement that "To be fixed later" used to mean "To be fixed never". Is this a true statement or a bad joke? Both, to some extent.

Let me explain this. Ideally status "To be fixed later" should be set (for a bug report or feature request that was originally "Verified"!) by a development manager or key developer (like Marko in case of InnoDB) who checked all planned bug fixes for current GA versions, all planned bug fixes and new features for the version currently in development and, based on available resources and priorities, made a decision (as this is his job) to NOT include the fix in any version that is currently under development. Ideally, at the same time, worklog is to be created for this feature and made public, so that people can contribute with ideas, questions and express their need for this new feature (or bug fix).

So, can we even suspect anything as ideal as above in case of bug #69842? Obviously we can not. Even if we assume that Sinisa has the power of making decisions on further InnoDB development, hardy he spent enough time on Monday to make sure this does NOT fit into InnoDB plans for MySQL 5.7 (that is still at very early stage of development. He had not referenced any worklog created (and this is not so easy if at all possible in Oracle it seems to create new public worklogs). Surely bug reporter have some reasons to think that in this case "To be fixed later" may be equal to "To be fixed never"... I may be wrong here, but then I am sure somebody will explain this in public and give some insights on the huge amount of work entire MySQL organization in Oracle did before he sent his comments :)

Another potential problem with "To be fixed later" bugs, even if they had got this status with good reasons and after following the ideal procedure described above, is the following: who and when reviews bugs in these status again? They should be reviewed at least when development of new version starts, that is, recently when work started on 5.7 all old "To be fixed later" bugs had to be reviewed and some of them had to get some comments and status "Verified" back, if they are now considered for a new feature, or status "To be fixed later" re-established for 2 more years (assuming new GA release every 2 years based on recent Oracle habits and statements). Please, check the list of "To be fixed later" bugs and try to find any evidence of something like this happened any time since 2011.

I know for sure only one iteration like this happened, and I made it myself back in 2007 or so maybe, probably even before MySQL AB joined Sun (some time before 5.1 GA). I worked with Trudy (and maybe Peter G.), main MySQL architects of that times, and was able to re-open some of "To be fixed later" bugs, not many. I am not sure if anybody tried to repeat this again, especially recently in Oracle. Let me stay corrected...

Friday, July 26, 2013

Fun with Bugs #19 - waiting for MySQL 5.6.13 and some real fun?

I feel like MySQL 5.6.12 was released ages ago, while in reality it was on June 3, less than 2 months ago. No wonder I feel so, after writing several posts about bugs fixed and not fixed in it... Anyway, we still have to wait for MySQL 5.6.13 for a week or even two probably and in the meantime I decided to write new post for this series based on good old idea of making a digest of my recent bugs-related posts at Facebook. I know, it's boring and annoying (same as waiting for the release of 5.6.13).

Let's start with Bug #69846 - "ICP does not work on UNIQUE indexes". Based on my quick tests presented there I'd say that ICP (index condition pushdown) actually does not work for InnoDB clustered key (being it explicit PRIMARY KEY or just the first UNIQUE KEY), and this is probably by design, as such index is "equal" to InnoDB table itself. I'd still prefer to see this explained in the manual. Bug is still "Open", so Oracle engineers may have different opinion.

Another bug made my day actually, it's Bug #69842 from Domas. It does not matter that it is "Open" or that doublewrite buffer does not work efficiently with InnoDB page sizes smaller than 16K... Question is how to create proper Latin-based term for writing something 3, 5 ("quintuple-writing") or 17 times...

Optimizer in MySQL had always been a source of endless fun. It seems that for some cases it worked properly last time in 5.1 (if not 5.0). Check Bug #69833 for a recent example, "Bad interaction between MIN/MAX and "HAVING SUM(DISTINCT)": wrong results". Yes, it's a weird corner case with not much real life use, but still it's a regression bug. In some areas MySQL 5.1 was better than any 5.5 or 5.6 GA version from Oracle so far :)

Bug #62578 is still affecting customers who use MySQL 5.5.x. Recent checks show that 5.6.12 is NOT affected (neither Sinisa nor me were able to repeat it on 5.6.12), but mysql client of 5.5.32 still crashes when you reside terminal (or putty) window it runs in. Fix is well know and probably found its way to 5.6 long time ago, but why it is NOT in 5.5? Yes, I still remember talks about a policy to fix bugs in older GA versions only when customers ask and there is no risk or high efforts involved, but isn't this the case for this bug?

I've spent a lot of time trying to find similar known bug report for Bug #69825 reported by my colleague, but failed. It seems new problem, at least in public bugs database. Sounds simple, "InnoDB: Assertion failure in thread ... in file lock0wait.cc line 297", affects 5.6.12 and the only repeatable test case so far is based on customer confidential data (as far as I know), but still... Why it is still "Open" and got no comments? Had everybody in Oracle decided that GTID-based crash safe replication with information stored in InnoDB tables in mysql database just always work, so it must be some bug reporter fault? It's not the case, dear colleagues, it's not the case...

Now something simple and easy to verify by code review, Bug #69827. "Hardcoded libdir in cmake", so that lib64 on Linux is used only for x86_64. Who cares I've asked? But even if we do not take 64-bit PowerPC into account any more, some sources claim 64-bit ARM-based servers are coming soon... Should be easy fix actually (hacked in by many users already probably).

If you are going to upgrade to MySQL 5.6 and had forgotten that configuration file in basedir (if present) overrides settings in /etc/my.cnf, check Bug #68643 - "sql_mode is unkind in my.cnf created by mysql_install_db". Yes, as it silently includes STRICT_TRANS_TABLES. It's just a feature request for now, so hardly is going to change any time soon (if at all). Just take care about this, I warned you.

 Week started with funny bugs mostly. This one is open for more than 7 years and still scare people, Bug #18256. If session is killed you still may see messages like this in your mysql command line client:

ERROR 1053 (08S01) at line 1: Server shutdown in progress

Do not panic, please. Server may be perfectly alive, just your session no longer works.

On the weekend I still had fun with the manual and friends who are scared by Oracle requests for personal information. Check Bug #69805 I've reported (or http://falseisnotnull.wordpress.com/2013/07/21/mysql-not-found-errors-precedence-docs-bug/ for the whole story) and note that fine manual does not describe what was really implemented correctly. Let them now decide who was wrong, manual (I hope) or implementation of the feature as designed. Error handling in stored routines is still to complex and far from perfect even in MySQL 5.6 if you ask me, at least comparing to good old Oracle PL/SQL (this is what I really miss since 2005 sometimes...)

That's all for now. I've spent this week digging around and trying to fix things and protect strangers from dangers like this:


Hope this helps somebody.

Sunday, July 21, 2013

Fun with Bugs #18 - Feature Requests (Oldies but Goldies Part II)

In the previous "Fun with Bugs" posts I've mostly ignored feature requests. Users do file a lot of feature requests in MySQL bugs database, but until recently (when "Affects Me" button was introduced) there was no clear way to even try to influence the priority of the feature in development plans. There is still no way to see if the feature request has any priority. Surely, based on Oracle policies, nobody from Oracle will even try to give you a hint on when the "Verified" feature request is going to be implemented or what is its real internal priority...


Does it mean that there is no sense to make feature requests (or, for Oracle engineers, to process them and keep status in sync with reality)? No, it does not, IMHO. This is still one of few ways for a community user or even a customer to influence the future of MySQL. Even if Oracle will not start to act immediately in any obvious way based on your feature request, it will still be documented, reviewed by Oracle engineers, other community users and maybe even developers from other forks. Entire world will know what is missing (and accepted as missing by vendor, if feature request is "Verified"). Then, based on its status changes, entire world will know if anybody cares enough...




So, let me continue my previous post about oldest but still formally "Verified" bugs with a list of 12 oldest and still "Verified" feature requests:
  1. Bug #400 is the oldest and it remains "Verified" since good old days when Monty himself replied to MySQL feature requests. It's about backslash (\) usage as escape character before single and double quotes. I do not have any opinion about it, but still it's the oldest still "Verified" feature request.
  2. Bug #765 - "mysqlimport should read from stdin standard input". Maybe, it's a kind of a "Unix way" to have some option to do so...
  3. Bug #1118  - "Allow multiple concurrent locks with GET_LOCK()". This one from Dean Ellis I'd like to see implemented years ago. And it was implemented recently by former famous MySQL developer, Konstantin Osipov, who had made a BSD-licensed patch available here. Feature request is still just "Verified", bit this is how it may work: even if not Oracle, somebody can pick up useful feature request and implement it.
  4. Bug #1154 - "cannot rename temporary table". I really wonder why not to make RENAME working the same way as ALTER TABLE ... RENAME in this case. Nobody wants to touch old code and test cases maybe...
  5. Bug #1207 - this is a request for function to count the number of substring occurrences in a string, like PHP's substr_count(). Workarounds are more or less obvious, so this feature request may hang around not implemented for 10 more years easily...
  6. Bug #1214 - "Unique row identifier data type for MySQL (like MSSQL uniqueidentifier)". UUID() mostly works for this if MIXED/ROW-based replication is used, but still this concern expressed by James Day remains:

    "Decision needed for this bug is if UUID() should be our answer to a unique identifier feature request in 5.1 and later. It's very non-optimal for InnoDB primary key use because it puts the most rapidly changing parts of the time first."
    Check the bug report for different workarounds, but ultimate solution is not yet in place it seems.
  7. Bug #1275 - "Multiple uninstall options if upgrade a preexisting install in same directory". This is a Windows-specific feature request and I am not sure what's going on now with new installers. I had never really tried to upgrade in place in real life using .msi or new installer (if maybe to verify some bug report). It's surely better to install every version into its own directory and then play with datadir in my.ini to make upgraded MySQL work with data from previous version.
  8. Bug #1309 - "EXCEPT statement". One of the early requests to have all kind of relational operations requested by standard in MySQL. Of them we still have only UNION [ALL].
  9. Bug #1310 - "optionally add queries with lock times to slow_query log". I assumed this is already implemented, but I may be wrong...
  10. Bug #1327 - "I would like functions/SQL commands in the MySQL API/Language so that a client application can be notified when things happen in the database." Now I am not sure if we need to implement anything for this, but back in 2005 I thought there is something to consider here. Still "Verified" since that time.
  11. Bug #1341 - "InnoDB ibdata1 never shrinks after data is removed". Still the case. Setting innodb_file_per_table=1 and using separate tablespaces for undo in MySQL 5.6 partially solves the problem (by removing reasons for ibdata1 to grow much). But users still complain, for 10 years already...
  12. Bug #1343 - "index by date part of datetime field". I've suggested some kind of a workaround there back in 2005.
I think we had enough examples to understand that both reporting and reading feature requests is useful - you can get a workaround or even some kind soul providing a patch, even for the feature request that is still "Verified".


Thursday, July 18, 2013

Fun with Bugs #17 - Oldies but Goldies

I've just noted the date... 8 years ago I had my first official working day as a support engineer in bugs verification team of MySQL Support at MySQL AB. Why not to celebrate this anniversary with a blog post about bugs?

So, here they are, 12 oldest bugs in MySQL software that are still just "Verified" (it should mean they are accepted, but not yet fixed):
  1. Bug #2 - MySQL Connector/J doesn't make toast. I knew that Connector/J must be the most broken MySQL software (as I hate Hibernate). Now you can see how much it is broken, and nobody cares to fix it since 2002! This is a real shame...
  2. Bug #199 - Innodb autoincrement counter is lost on restart. This great report from Peter Zaitsev is still "Verified", since 2003. It became a documented "feature" in the process, but still "Verified" status means that over these years we all agree - it's broken by design! Let's hope MySQL 5.7 will fix it.
  3. Bug #363  - SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x. I spent some time verifying the original test case, and it seems in 5.6.12 it is no longer repeatable (but it seems more general things were discussed there:

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.6.12    |
    +-----------+
    1 row in set (0.01 sec)

    mysql> show session variables like 'sql_log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | sql_log_bin   | ON    |
    +---------------+-------+
    1 row in set (0.15 sec)

    mysql> select @@session.sql_log_bin;
    +-----------------------+
    | @@session.sql_log_bin |
    +-----------------------+
    |                     1 |
    +-----------------------+
    1 row in set (0.00 sec)
  4. Bug #1382 (and it's later duplicate, Bug #23385, reported by my friend Tonci) - Server returns only VARCHAR as type for any metadata sent to the client. Metadata server returns had always been a problem for all MySQL connectors. Still "Verified". Now you have "Affects Me" button at least...
  5. Bug #1396 - "Lost Data with delayed insert / mysqlhotcopy or lock and flush". It was declared a feature request to implement a workaround to make this more safe. Do not use INSERT DELAYED and/or mysqlhotcopy, what else?
  6. Bug #1939 - "Wrong case sensitivity for table names when in "ANSI" mode". We all know setting lower_case_table_names=1 is the ultimate solution, and hardly this is going to change while we still have .frm files, but the bug is still "Verified".
  7. Bug #1956 - "Parser allows naming of PRIMARY KEY". Still happens with 5.6.12, and surely in SHOW CREATE TABLE you will not see the name. "One day at a time, Penny, one day at a time".
  8. Bug #2122  - "changing hostname confuses master or slave". We all know since day one with MySQL replication that binary logs should get explicit names. Let me quote one comment form the bug report though:

    [13 Sep 2007 11:54] Lars Thalmann
    We are analyzing this and will fix it after 5.1 is released. It is, unfortunately, not likely that this will be fixed in 5.1, but we prioritize it for the next version.

    Still just "Verified".
  9. Bug #2261 - "Can't use @user_variable as FETCH target in stored procedure". So, what? Just scroll down and read last comment from the best MySQL architect of all times, famous writer and bug reporter of the past, Peter Gulutzan. I was drinking wine with him near the hotel entrance last time we met (2008 AFAIR)!
  10. Bug #2742 - "fields-escaped-by only allows single character separator!". Not sure what is the status and the real problem, but look at the bug reporter's name: "Are you mortal Then prepare to die." It's the last still active bug report from this famous personality who was well known during good old times of MySQL AB.
  11. Bug #2812. When executing SHOW VARIABLES in a prepared statement stmt->field_count is 0 instead of 2. Still "Verified" and you know what these mean in the bug report: "Triaged: D2 (Serious) / R4 (High) / E4 (High)". It means that bug was considered serious, but efforts to fix and risk to introduce regressions or other problems was estimated as high. Poor MySQL connectors' developers, really...
  12. Bug #3052 - "Rollback in stored procedure doesn't close cursor". Now read the final comment:

    [22 Aug 2006 15:02] Konstantin Osipov
    This is too big to be fixed in 5.0, will be fixed in 5.1

    You know what, it's still in 5.6.12:

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.6.12    |
    +-----------+
    1 row in set (0.00 sec)

    mysql> delimiter //
    mysql> create table t (s1 int) engine=InnoDB//
    Query OK, 0 rows affected (0.73 sec)

    mysql> insert into t values (55)//
    Query OK, 1 row affected (0.06 sec)

    mysql> create procedure p2 () begin
        ->   declare v int;
        ->   declare x cursor for select s1 from t;
        ->   open x;
        ->   rollback;
        ->   fetch x into v;
        ->   set @x = v;
        ->   end;//
    Query OK, 0 rows affected (0.24 sec)

    mysql> call p2()//
    Query OK, 0 rows affected (0.07 sec)
I can continue like that for days and 20 more posts... Reading (and processing) MySQL bug reports is a real fun! That's why I do it (with small breaks) for 8 years already and not going to stop. It's great reading no matter what are looking for - technical insights, workarounds, patches, hints, nice stories or just to something to laugh at.

Saturday, July 13, 2013

Fun with Bugs #16 - read the fine MySQL 5.6 manual...

MySQL 5.6 has many new features and some of them change usual DBA procedures and formerly well known facts/limitations. It's important to have all these properly documented now, when MySQL 5.6 is GA for 6 months already and is supposed to be widely used in production. So, I think it's time to check what documentation problems still remain.

Here is the list of active bug reports in Server: Docs category for version 5.6, starting with recently reported:

  • Bug #69717 - "DML statements replicated via RBR are NOT logged in the general query log ". I had to report this while working on customer issue and trying to explain why there are no DML statements in slave's general query log. This feature was new in 5.1 and even somewhat documented at http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html, but I'd prefer to see this explained at http://dev.mysql.com/doc/refman/5.6/en/query-log.html also. Moreover, in 5.6 we may be able to log somethings useful, as my colleague Justin Swanhart noted:
    On 5.6 perhaps the general log should log the query event associated with the RBR if it is available?
    So, it seems this is not only about the manual, but also about re-opening good old feature request, Bug #14722 for 5.6+.
  • Bug #69711 - "Explain what operations lead to specific "Waiting for... lock" states", is for 5.5+ actually, where metadata locks were introduced, but it makes sense to mention it in context of 5.6, as I do not want to see major 5.6 new features not entirely documented 2 years from now. This may happen, as the bug report proves, if we'll just keep silence. Use "Affects Me" button at least, please.
  • Bug #69697 - "Manual has not enough details on how to use transportable tablespaces" is a good example of what was missing in 5.6 manual on one of cool new features. It happened so that actual current content of that page, with detailed explanations, was pushed the day before I reported the bug, but was not yet visible. Anyway, explanation of  messages in the error log in the process of "importing" InnoDB tablespace is still missing. Also note that many documentation requests are satisfied almost immediately these days. Check my Bug #69701 for a great example of how it works (and what problems you may get while using transportable tablespace feature). So, keep reporting bugs, even if this is just about the missing phrase in the manual, and let Oracle engineers do their part of the job!
  •  If you upgrade to 5.6 after using 5.5 for a long time on Linux, note that mysql_install_db does NOT read /etc/my.cnf any more. Check Bug #68807 and Bug #69677 for the details and some consequences and workaround. Again, nice to see Oracle MySQL engineers still using public bug database, as in this case. I think for documentation requests it's a must - community should really know what is NOT properly documented yet. This hardly can lead to any security problems...
  • Bug #69630 - "MYSQL_FIELD structure has incorrect information". Even though the bug is still "Open" and may end up without any fix even in the manual, I still think it's a useful reading for people who use MYSQL_FIELD.charsetnr content in their code. Set your expectations according to the reality, please.
  • Bug #69490 - "Memory usage of P_S". I hope you already know that PERFORMANCE_SCHEMA is enables in MySQL 5.6.x by default and that you will have to pay for this with performance decrease (in some cases) and additional memory used (in all cases, like 170K per connection in max_connections, see details in the bug). I'd like to see the details shared by Marc Alff in Bug #68514 clearly mentioned in the manual. I hope I am not alone...
  • Bug #69199 - "STATS_SAMPLE_PAGES not documented in "CREATE TABLE" page". It's clear what is missing, so let's hope it will be fixed soon. Nice new feature that users may not be even aware about.
  • Bug #69135 - it seems that manual should still become more clear on what exactly one should do in MySQL 5.6 to make replication really crash safe.
  • Bug #68097 - "Manual does not explain that some P_S instruments must be enabled at startup". I've reported this almost 7 months ago, when 5.6.9 was released. But now at least there is some work in progress it seems. They say it is not trivial to document...
  • Bug #67023 - "EXPLAIN FORMAT=json output not documented". It would be really nice to see side-by-pside comparison of traditional output and output in JSON format. For me it is still not clear if (or what) additional information one may get from JSON format (and I tried to figure out with simple examples, really). As you can see some MySQL 5.6 documentation requests are not lucky and may hang around in "Verified" state for many months...
  • Bug #66397 - "Docs info on installing on Windows is not updated". Documentation now clearly describes that you get MySQL Installer from dev.mysql.com while downloading .msi, but I do not see information about two kinds of installers (check http://dev.mysql.com/downloads/installer/5.6.html) or that one may find good old .msi packages on FTP mirrors (not sure, had not checked for a long time). Time to add missing details and close this bug maybe?
  • The last but not the least, tow documentation requests related to new MRR optimizer feature are still just "Verified", since 2011! Check Bug #62676 and Bug #60864. Add to this the fact that default setting mrr=on led to one of few performance regressions in MySQL 5.6 (I had written about it here) and you'll understand my request to have it disabled by default for upcoming Percona Server 5.6 GA better (not that it's going to be satisfied, who knows...)
That's all, folks. Based on number of bug reports, MySQL 5.6 manual seems to be in good enough shape. Do you agree?

Sunday, July 7, 2013

Fun with Bugs #15 - Recent News and Hawthorne Effect Studies

Let me present a quick review of new and recently verified MySQL bug reports (mostly in 5.6.12, but not only). Surely I have to start with this request that many my colleagues had already mentioned in their blogs:

Bug #69558 - Put *all* know bugs into the public bug tracking system at bugs.mysql.com. We may argue on how and when this should apply to "security" bugs, but automatic bi-directional replication (even if delayed) with Oracle internals bugs database is what I was also asking for since we were forced to use it. Click on "Affects Me" button there and let's hope that some day Oracle will publish list of bugs that affect most of community users and may even try to take this into account while making decisions.

I have good news for everybody who was following MySQL 5.6 Performance Schema overhead discussions. Bug #68413 is now "Verified". So it is officially confirmed now that there are use cases when performance overhead from Performance Schema being ON with default settings is at least 10%. We all knew that, and there was work in progress to reduce overhead for months already, but for some reason it took a long time and notable efforts from several people (starting with original bug reporter, surely) to get the bug formally "Verified" instead of being formally ignored.

Now, let me remind Oracle engineers about some bugs that probably are fixed already, but still are NOT "Closed" in our public bugs database:
  • Bug #68022 - "DROP TABLE slow when it decompress compressed-only pages". As far as I understand it should be fixed in MySQL 5.5 at least, but it still hangs around "Verified".
  • Bug #56332  - "Performance regression in DROP TABLE performance post 5.0.77". It is probably "Won't fix" for 5.0.x that is no longer supported, was fixed in 5.5 long time ago, and (check Bug #69316) should be finally fixed (again) in upcoming MySQL 5.6.13 and 5.7.2.

Unfortunately we still see regressions in new releases from Oracle. One that everybody who uses more than one datafile for shared InnoDB tablespace should know about is Bug #69623 - "since 5.5.32 & 5.6.12, innodb cant start with own multi-file tablespace". See Bug #69573 also. I already had to deal with customer affected (that upgraded to Oracle's 5.5.32), so the problem for production environments is real.

Bug #69653, "Use of uninitialized pthread_getspecific() key in debug builds", may sound unimportant to you, but it is yet another evidence that there are things to improve in code review and analysis in Oracle.

Good old Bug #62255 ("DROP USER can't drop users with legacy upper case host name anymore") had got yet another duplicate this week. Better just name your hosts all lowercase to avoid problems like this.

Should I even remind you that it hardly makes sense to use ORDER BY while defining views if you do not add LIMIT clause at the same time? If I should, please, check Bug #69678 - it shows that you may get hit of a regression bug in this case. One more change in behavior in 5.6.12 vs 5.5, but IMHO it's just easier to avoid this kind of views.

Now some "personal" things to mention. I keep wondering is there any work in progress on Bug #68487, "MDL hash can still be concurrency bottleneck"? I am also going to abuse this blog post to remind about and old documentation request I've made, Bug #68097. I had not double checked, but are we sure that all details on enabling PERFORMANCE_SCHEMA instruments are now properly documented? Hardly to assume this, as bug is still just "Verified". I also have one bug to add to the list of replication problems in 5.6.12, Bug #69574 - Slave crashes when applying row-based binlog entries in cascading replication...

Finally, some background theory. While trying to understand if my MySQL bugs-related activity can be useful at least theoretically I started to read about observer's paradox and ended up on a page describing Hawthorne effect:
"The Hawthorne effect is a form of reactivity whereby subjects improve or modify an aspect of their behavior being experimentally measured simply in response to the fact that they know they are being studied"
So, I do study community bugs processing in Oracle and describe my findings in public, and as a result engineers involved may start to improve bugs processing, no matter what I actually noted during my study or report in my Facebook and blog posts (that many consider annoying). Just because they know that they are being studied... Sounds like a plan! Stay tuned.