Blog of (former?) MySQL Entomologist

Thursday, January 31, 2013

On InnoDB I/O threads states

I was asked today what different state values for InnoDB I/O threads really mean, these ones:

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
...

I tried to search the manual and Web in general and found no useful explanation (these verbose values should be self explanatory by design it seems). As question was asked, probably it's time to try to answer it...

These states for I/O threads are set by srv_set_io_thread_op_info() function. Quick search with grep on MySQL 5.5.29 source code tree gives us some hints, the rest we can try to find out via code review based on these hits:

[openxs@chief mysql-5.5]$ grep -rn srv_set_io_thread_op_info * 2>/dev/null
storage/innobase/srv/srv0srv.c:772:srv_set_io_thread_op_info(
storage/innobase/os/os0file.c:3494:             srv_set_io_thread_op_info(i, "not started yet");
storage/innobase/os/os0file.c:4370:             srv_set_io_thread_op_info(orig_seg, "wait Windows aio");
storage/innobase/os/os0file.c:4394:             srv_set_io_thread_op_info(orig_seg,
storage/innobase/os/os0file.c:4681:             srv_set_io_thread_op_info(global_seg,
storage/innobase/os/os0file.c:4691:     srv_set_io_thread_op_info(global_seg,
storage/innobase/os/os0file.c:4787:     srv_set_io_thread_op_info(global_segment,
storage/innobase/os/os0file.c:4805:     srv_set_io_thread_op_info(global_segment,
storage/innobase/os/os0file.c:4946:     srv_set_io_thread_op_info(global_segment, "consecutive i/o requests");
storage/innobase/os/os0file.c:4989:     srv_set_io_thread_op_info(global_segment, "doing file i/o");
storage/innobase/os/os0file.c:5010:     srv_set_io_thread_op_info(global_segment, "file i/o done");
storage/innobase/os/os0file.c:5062:     srv_set_io_thread_op_info(global_segment, "resetting wait event");
storage/innobase/os/os0file.c:5072:     srv_set_io_thread_op_info(global_segment, "waiting for i/o request");
storage/innobase/include/srv0srv.h:499:srv_set_io_thread_op_info(
storage/innobase/include/srv0srv.h:766:# define srv_set_io_thread_op_info(t,info)       ((void) 0)
storage/innobase/fil/fil0fil.c:4581:            srv_set_io_thread_op_info(segment, "native aio handle");
storage/innobase/fil/fil0fil.c:4593:            srv_set_io_thread_op_info(segment, "simulated aio handle");
storage/innobase/fil/fil0fil.c:4605:    srv_set_io_thread_op_info(segment, "complete io for fil node");
storage/innobase/fil/fil0fil.c:4622:            srv_set_io_thread_op_info(segment, "complete io for buf page");
storage/innobase/fil/fil0fil.c:4625:            srv_set_io_thread_op_info(segment, "complete io for log");

Those who are too lazy to pull source code can just use this beautiful online tool to review few files listed above and make their own conclusions.

Unfortunately code formatting is different for different calls, so we still miss some states above as strings. Let's review the files then. It all starts with os_aio_init() function on line of 3494 of os0file.c:

3493 for (i = 0; i < n_segments; i++) {

3494 srv_set_io_thread_op_info(i, "not started yet");

3495 }

It is clear what's going on here, you hardly will be able to catch this status in the output as it is set even before I/O threads are actually started.

Our next stop is line 4370, in os_aio_windows_handle() that, obviously, works only on Windows:

     /* NOTE! We only access constant fields in os_aio_array. Therefore

     we do not have to acquire the protecting mutex yet */

     ut_ad(os_aio_validate_skip());

     ut_ad(segment < array->n_segments);

     n = array->n_slots / array->n_segments;

     if (array == os_aio_sync_array) {

         WaitForSingleObject(

             os_aio_array_get_nth_slot(array, pos)->handle,

             INFINITE);

         i = pos;

     } else {

         srv_set_io_thread_op_info(orig_seg, "wait Windows aio");

         i = WaitForMultipleObjects((DWORD) n,

                        array->handles + segment * n,

                        FALSE,

                        INFINITE);

     }

So, "wait" here means that we do wait for completion. Later in the same function we are checking result:

4393 if (orig_seg != ULINT_UNDEFINED) {

4394 srv_set_io_thread_op_info(orig_seg,

4395 "get windows aio return value");

4396 }

4397

4398 ret = GetOverlappedResult(slot->file, &(slot->control), &len, TRUE);

That's all states that may be set in os_aio_windows_handle(). Next stop is line 4681 in an infinite loop in the os_aio_linux_handle() function:

     /* Loop until we have found a completed request. */

     for (;;) {

         ibool   any_reserved = FALSE;

         os_mutex_enter(array->mutex);

         for (i = 0; i < n; ++i) {

             slot = os_aio_array_get_nth_slot(

                 array, i + segment * n);

             if (!slot->reserved) {

                 continue;

             } else if (slot->io_already_done) {

                 /* Something for us to work on. */

                 goto found;

             } else {

                 any_reserved = TRUE;

             }

         }

...

         /* Wait for some request. Note that we return

         from wait iff we have found a request. */

         srv_set_io_thread_op_info(global_seg,

             "waiting for completed aio requests");

         os_aio_linux_collect(array, segment, n);
     }

 found:

     /* Note that it may be that there are more then one completed

     IO requests. We process them one at a time. We may have a case

     here to improve the performance slightly by dealing with all

     requests in one sweep. */

     srv_set_io_thread_op_info(global_seg,

                 "processing completed aio requests");

So state "waiting for completed aio requests" means that there is no pending request and no completed request, so we have nothing to do. When at least some AIO request is completed., state will be "processing completed aio requests". All these for real AIO on Linux.

In case of simulated AIO we have  os_aio_simulated_handle() function, where state is set on line 4787:

 restart:

     /* NOTE! We only access constant fields in os_aio_array. Therefore

     we do not have to acquire the protecting mutex yet */

     srv_set_io_thread_op_info(global_segment,

                   "looking for i/o requests (a)");

Later in the same function:

     /* Look through n slots after the segment * n'th slot */

     if (array == os_aio_read_array

         && os_aio_recommend_sleep_for_read_threads) {

         /* Give other threads chance to add several i/os to the array

         at once. */

         goto recommended_sleep;

     }

     srv_set_io_thread_op_info(global_segment,

                   "looking for i/o requests (b)");

So, here we have stages (a) and (b), before and after sleep, and here is the recommended_sleep label in the same function (before we do sleep state "waiting for i/o request" is set):  

 wait_for_io:

     srv_set_io_thread_op_info(global_segment, "resetting wait event");

     /* We wait here until there again can be i/os in the segment

     of this thread */

     os_event_reset(os_aio_segment_wait_events[global_segment]);

     os_mutex_exit(array->mutex);

 recommended_sleep:

     srv_set_io_thread_op_info(global_segment, "waiting for i/o request");

     os_event_wait(os_aio_segment_wait_events[global_segment]);

     if (os_aio_print_debug) {

         fprintf(stderr,

             "InnoDB: i/o handler thread for i/o"

             " segment %lu wakes up\n",

             (ulong) global_segment);

     }

     goto restart;

By the way, we end up at that wait_for_io label when there is no I/O to do by simulated AIO, so "resetting wait event" state is set for a very short period. 

Let's continue with os_aio_simulated_handle() back from line 4946:

     srv_set_io_thread_op_info(global_segment, "consecutive i/o requests");

     /* We have now collected n_consecutive i/o requests in the array;

     allocate a single buffer which can hold all data, and perform the

     i/o */

This state is set if we picked up some request and found there are consecutive requests that can be processed as an array. This state is set only while we are allocating the buffer, for real I/O there is another state, "doing file i/o":

     srv_set_io_thread_op_info(global_segment, "doing file i/o");

     if (os_aio_print_debug) {

         fprintf(stderr,

             "InnoDB: doing i/o of type %lu at offset %lu %lu,"

             " length %lu\n",

             (ulong) slot->type, (ulong) slot->offset_high,

             (ulong) slot->offset, (ulong) total_len);

     }

     /* Do the i/o with ordinary, synchronous i/o functions: */

     if (slot->type == OS_FILE_WRITE) {

         ret = os_file_write(slot->name, slot->file, combined_buf,

                     slot->offset, slot->offset_high,

                     total_len);

     } else {

         ret = os_file_read(slot->file, combined_buf,

                    slot->offset, slot->offset_high, total_len);

     }

     ut_a(ret);

     srv_set_io_thread_op_info(global_segment, "file i/o done");

When sync I/O is completed, state "file i/o done" is set, as you can see above.After we've done I/O and before next sleep and restart we copy data back to original several buffers, set/reset everything properly and call to os_aio_simulated_handle() is completed:

     /* We return the messages for the first slot now, and if there were

     several slots, the messages will be returned with subsequent calls

     of this function */

 slot_io_done:

     ut_a(slot->reserved);

     *message1 = slot->message1;

     *message2 = slot->message2;

     *type = slot->type;

     os_mutex_exit(array->mutex);

     os_aio_array_free_slot(array, slot);

     return(ret);

That's all, so it is either Windows native AIO (a couple of states only) or Linux native AIO (a couple of states only), or simulated AIO (and in this case many states are possible). Probably that's why nobody cared to explain details - in real production situations with native AIO there are few states to consider anyway.

Now, there are some lines to check in fil0fil.c also,  fil_aio_wait() function:

     if (srv_use_native_aio) {

         srv_set_io_thread_op_info(segment, "native aio handle");

 #ifdef WIN_ASYNC_IO

         ret = os_aio_windows_handle(segment, 0, &fil_node,

                         &message, &type);

 #elif defined(LINUX_NATIVE_AIO)

         ret = os_aio_linux_handle(segment, &fil_node,

                       &message, &type);

 #else

         ut_error;

         ret = 0; /* Eliminate compiler warning */

 #endif

     } else {

         srv_set_io_thread_op_info(segment, "simulated aio handle");

         ret = os_aio_simulated_handle(segment, &fil_node,

                           &message, &type);

     }

So, here decision is made (at compile time) on what functions to call from three described above and, for some short time, state reflects type of AIO that is going to be used.

When whatever AIO function called completes, we have this code running:

     srv_set_io_thread_op_info(segment, "complete io for fil node");

     mutex_enter(&fil_system->mutex);

     fil_node_complete_io(fil_node, fil_system, type);

     mutex_exit(&fil_system->mutex);

     ut_ad(fil_validate_skip());

     /* Do the i/o handling */

     /* IMPORTANT: since i/o handling for reads will read also the insert

     buffer in tablespace 0, you have to be very careful not to introduce

     deadlocks in the i/o system. We keep tablespace 0 data files always

     open, and use a special i/o thread to serve insert buffer requests. */

     if (fil_node->space->purpose == FIL_TABLESPACE) {

         srv_set_io_thread_op_info(segment, "complete io for buf page");

         buf_page_io_complete(message);

     } else {

         srv_set_io_thread_op_info(segment, "complete io for log");

         log_io_complete(message);

     }

Here state reflects type of I/O completed, data page or redo log.

That's all. I have to think what is the better way to summarize the states and possible transitions for each state. Maybe just a table, or something like a state machine represented somehow. I should think about this. So, to be continued...

Sunday, January 27, 2013

Fun with Bugs, Issue #3, January 2013

This week in my posts on Facebook I paid attention mostly to bugs in MySQL 5.6.x, as I really expected to see 5.6 GA announcement soon. Maybe some of the bugs were the reason to postpone release a bit. Anyway, let's start with serious bug reports and some trends I've noted.

I've noted that some of "Verified" bug reports somehow silently miss comments about their status in MySQL 5.6. Check Bug #68137, for example. It is repeatable on 5.5.31 (yet to be released, but it means 5.5.30 is cloned off and will be released soon) and 5.7.1. But 5.6.x is not mentioned anywhere. Does it mean 5.6.x is not affected? Surely, no. I hope it does NOT mean any attempt by Oracle to pretend that 5.6 is bugs free. Looks like Sveta had just forgotten to check or report result on this version when following her great bugs verification procedure.

I would say that the above was just a one time mistake, but then look at Bug #68145 that was verified by Sveta in less than 30 minutes after my last comment there (and "verification", at the level I can do it as community member busy with main job actually). It was verified on many versions again, 5.0.97, 5.1.69, 5.5.31, 5.7.1, but NOT on 5.6.x. This looks like a trend now, and I do not like it. There are bugs in 5.6.9, 5.6.10 and even 5.6.11, there is nothing bad about this if they are noted, processed and fixed fast. There is not (good) reason to skip checking on mentioning this version. I am monitoring carefully anyway, so nothing in public database will pass unnoticed and unknown to MySQL community.

Serious bug in InnoDB was reported (and verified on both 5.5.27, 5.6.9-rc and recent 5.6 code) this week, Bug #68148. After executing SET FOREIGN_KEY_CHECKS=0; you can easily drop index unsed by foreign key and then the tables becomes unusable (you can not even drop it with SQL statement) after server restart. As it is not a recent regression, I am not sure it would be easy to fix, so better just avoid DDL with the setting above.

When 5.6 is approaching GA it is really strange to see bugs in new/key features of MySQL 5.6 with unclear status. Check Bug #66916 (replication). We can only guess is it fixed in 5.6.10 or not. Check Bug #66865 (performance regression in 5.6 that is all about performance) reported by my colleague Vadim more than 4 months ago. It is still just "Open". So, is there a regression or not? Or it is just forgotten?

When bugs are forgotten and test cases are not added to the regression test suite (now we, in Community, can not check if they are added), we are doomed to see the same problems again and again. If you are Oracle customer, ask them what Bug #68178 is about (in a support request probably), what versions are affected and had it been previously reported and fixed. If you care what is it about (Oracle will probably just tell you to upgrade to the version with the fix eventually), it is about an easy way to crash MySQL server by sending many smart SELECT convert_tz(...) in a row. I hope 5.6 will NOT be released as GA before this bug is fixed.

Forgotten bug reports had always been a problem, but I tried to at least review all "Open" bug reports once in while. Now we can still see bug reports with clear, repeatable test case finally got from reporter (like Bug #67517), or bug that is just easy to verify by code review (like Bug #67386) open and having no visible attention for months. Some bugs just hang in other intermediate states for ages as well (after verification), like Bug #56267. Some are verified, but they look serious and still remain just in "Verified" status, like Bug #67638 (optimizer).

Chances are high for a casual reporter to never report again when he gets no reply for months. This bad trend continues, but with less bugs open reports there is chance to overcome this trend eventually.

Hardly any of new key features of 5.6 is bug free. For example, PERFORMANCE_SCHEMA, as it was recently found out (see Bug #68180) does not cover all execution paths. Comparing to bugs listed at the beginning of this post it may be not that serious, but I am a bit tired of getting new features in GA that are NOT implemented completely (as it happened to 5.0, 5.1 and 5.5 GA).

The last (but not the least) potential problem with 5.6 is related to the documentation. There are missing details and misleading statements there (see Bug #68097), sections like http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html#innodb-5-5 that are no longer relevand after recent funny bug fixes (see funny fix to Bug #63435 about innodb_version server variable) and misprints in release notes (like Bug #68181). Some sections are still work in progress it seems, like http://dev.mysql.com/doc/refman/5.6/en/online-ddl-partitioning.html. Some fixes (like Bug #67759) are not documented it seems. Maybe manual being not yet ready is the reason why MySQL 5.6 had not been declared GA yet?

Now about good trends. This week I've noted more MySQL engineers working on bugs processing. Long waiting Bug #68023 was finally verified by Matt Lord, who rarely worked on bugs not related to customer issues in the past. Look at his detailed research in Bug #67517 that I've noted (and "escalated") just today. Erlend Dahl had noted and verified awful Bug #68127 soon after my "escalation" and it was closed in 2 days (with the fix in 5.6.10, one of the reasons I though that it will be GA and announced really soon during the week)! Way to go, Oracle, really!

As a result, this week Oracle finally managed to decrease number of "Open" bug reports to the level smaller than during my last day in the company, August 31, 2012. Since December 2012 number of open bug reports is decreased by more than 170! New bug reports are processed fast, see Bug #68144 as one of recent examples. Verified in less that 3 hours (as well as two other bugs from Hartmut reported that day). Looks like it is not a problem now for Oracle to keep up with inflow of at least server bug reports (I'll write about Cluster and Workbench bugs in details in some of the following issues).

Another (IMHO good) trend - Oracle customers, at least those who did that before, do not hesitate to report bugs in public database (as I've suggested to do last year). See Bug #68165 as one of recent examples related to 5.6 and upgrade to it.

Finally, some fun. Make sure you check the oldest and still not closed MySQL bugs, Bug #2. 10 years passed and the problem is still far from solved! This is awful, really.

MariaDB also contributed some fun for us all with their version 10.0. Read Bug #68187 and then try to guess if Oracle is going to fix it any time soon. More fun in the next issue, I hope...

Sunday, January 20, 2013

Fun with Bugs, Issue #2, January 2013

Looks like Issue #1 was popular enough based on number of reviews, so let me continue and tell you what kinds of fun I had with MySQL bug reports between January 12 and today. Once again, this is a kind of digest for my bugs related posts on Facebook during this period.

I'd like to start with the latest open bug at the moment, Bug #68127. Looks like one can easily crash MySQL 5.6.9 by running few concurrent SELECT * from threads queries against PERFORMANCE_SCHEMA. As PERFORMANCE_SCHEMA is enabled by default in 5.6.9 (now even such an ignorant person as me knows this, thanks to readers of my previous posts), this is a serious problem IMHO. Comparing to this, some missing or unclear information in the manual, like those Bug #68097 or Bug #68089 I've reported are so minor...

Now, let me scroll Timeline back and check how the period started. Looks like it was mostly devoted to MySQL 5.6, and this is natural - it's approaching GA and should be the best release ever. That's why it's so sad so see regression bugs or incomplete features in it. Immediately before my previous "Fun with Bugs" post my former colleague Sveta Smirnova had published her review of 18 most important new troubleshooting features of MySQL 5.6. I've taken a quick look and ended up with a list of related bugs:

Bug #67830 - EXPLAIN for DELETE and UPDATE does not work as expected, still just "Verified"
Bug #57514 - delayed replication may not work as expected, still just "Verified"
Bug #67236 - problems with new host_cache table. This one is already "Closed".

Then there was a regression bug in optimizer, again in 5.6, Bug #68046. It was just in time for one internal discussion we had at Percona on long IN lists optimization. Make sure you check it if you are going to upgrade to 5.6 now or soon after GA and use long IN lists based on some old habits or previous experience with it as a solution for some query performance problems.

MySQL 5.6 will be under increasing attention, so I'd really appreciate for even less severe bugs like Bug #68118 to get proper attention from Oracle engineers and to be formally verified fast. Especially in cases like above, when old utilities with familiar names are rewritten (in Perl?) and now works differently than in current GA versions.

Optimizer had been a topic of my interest for years, so it was hard to skip the bug that actually does NOT affect 5.6 it seems, only 5.1.46+ and 5.5. I mean this one, Bug #68072. With proper indexes in place you can get wrong results for some queries when timediff() function is involved. What's funny is that without index the result will be correct. It's always great to choose performance vs correct results.

I have to admit that Oracle engineers did a great job in bugs processing during this period. Number of open bugs is now almost as low as during my last day in Oracle, and they really help to pinpoint cases when experienced engineers make mistakes when studying some problems and report bugs that are "false positives". It may take time to argue in such cases, but public discussion and double checking benefit all sides involved. See Bug #68075 as a great example of this. Even if bug still remains "Open", like Bug #68077, it's great to be able to read the analysis or opinion of Oracle engineers about the problem and potential solutions.

If you care about InnoDB scalability and want to see a case when MySQL 5.6 is better than 5.5, and understand why is it so and is MySQL 5.6 really that good and scalable in a real life, take a look at Bug #68079, especially at comments from Dimitri Kravtchuk there. I expect detailed study (if not a solution) from him soon, published at his great blog. Thanks to my former colleague Arnaud Adant the bug was not only formally verified soon, but he also made sure that Oracle's leading performance tuning expert is aware about it.

This was yet another great example of Oracle's care about public bugs database and problems reported there. They can do a lot. We, community users, should just remind them about bugs they miss for whatever reason. Short post about Bug #67124 - and now it is "Verified" and get a chance to have resources allocated and priority set so that more improvements will be done sooner.

I know Oracle does not care about other storage engines as much as about InnoDB, but still it would be nice to have some cases, like Bug #68086, at least clarified in the manual if not fixed. Lower level of care is demonstrated for bugs related to tools or connectors. And while I can consider these bugs funny (like in case of Bug #67994) they may not be easy to fix. Even more important in this case to have them formally verified, especially if verification itself looks easy.

Since my Issue #1 in this series I've got several notes from my new readers about problematic bug report. Bug #66819 was one of them (surely it is about InnoDB, it's all about InnoDB these days). My colleague Alexey Kopytov had studied root causes of infamous Bug #61104 in details there, and the bug was closed as fixed in versions 5.1.67, 5.5.29, 5.6.8, 5.7.0. Weixiang Zhai questioned that based on code review and then it was proved that not ALL cases are fixed. Oracle will work on the remaining in frames of new, internal bug report it seems, but in the meantime you should know that change buffer is still not yet entirely safe in 5.5.29 and it makes sense to still apply the same workaround as suggested in Bug #61104.

As you know, some bugs affect only debug builds. Does this mean they are not important to fix? No, having them prevents testing for other bugs properly, so they also have to be escalated. Check Bug #68116 from my colleague Laurynas for example. I hope it will not end up "Unsupported" only because his stack trace is from Percona Server...

By the way, MySQL 5.7 is already work in progress. We, in MySQL community, do not have access to source codes yet (as there were no official release), but I see 5.7 mentioned in bug reports more often now. Check this case, Bug #63178. I think that it is NOT acceptable to just close the bug with the fix only in 5.7.0 without any comments on why the problem was not fixed in earlier versions or access to source code of the fix. But who am I to suggest anything... On the positive side, the fact that somebody cared to close the bug and write 5.7.0 there probably means that there will be a formal release of 5.7.x soon. Let's hope...

I'd like to finish this post in a way similar to the previous one: if you have a valid support contract with Oracle, ask them what Bug #65664 is about and is it really fixed in MySQL 5.1.66. I have reasons to think it is not, unfortunately. Find out for yourself - this is your benefit as a customer after all, to be able to demand a definite reply. I can only assume or guess (or check the code) at the moment.

To be continued...

Saturday, January 19, 2013

Life cycle of a MySQL bug

I had already written once on how to report bugs to http://bugs.mysql.com to have high chances for the bug report to be noted and processed fast. But why this matters at all? Because chances are high that bug report will never be opened and read by any MySQL developer in Oracle while its status is just "Open"! This is a short answer.

If you are surprised (the story is different with, say, Percona Server bug reports, where just reported "New" bugs are often reviewed and commented on by many engineers) let me present more details on how bugs processing worked at least since 2005 when I joined MySQL AB and what important changes happened when MySQL became Oracle software.

I'd like to explain most important statuses for MySQL bug reports at bugs.mysql.com, to begin with (you can see them all and use for search). Here they are, with short explanations:

Open - this is a status of a bug that is just reported or had recently got a comment from reporter or any other user. Sometimes MySQL engineers can set status back to "Open" when they do not know how to proceed.
Closed - Usually it means that the bug is fixed. You should expect to see a comment at the end of report with short description of the problem and fix, and all versions that had got the fix. Original bug reporter can set status for his bug to "Closed" if he considers the problem was not a result of any bug in MySQL software, found that the problem is already fixed, or just does not care about the bug any more.
Duplicate - it means that there is another bug report about the same problem. Usually you should see a comment with bug number for the other report in public (or Oracle internal) bugs database. This status is usually set by MySQL engineer who processes bugs or by developer who started to work on this report but recognized familiar case.
Analyzing - it means that somebody wants or started to work on this report. Usually you should see engineer name in the "Assigned to:" filed in this case. This status is often used as a semaphore to show other engineers that there is no need to work on this bug.
Verified - one of the most important values for community (only "Closed is more important). It means that report is accepted by MySQL engineer as a real bug that this engineer is able to repeat (or explain MySQL developers how to repeat or what is the problem). So, the bug is confirmed internally.
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.
Can't repeat - bug reporter had NOT provided a complete, repeatable test case or the problem is not repeatable with current code or anywhere outside reporter's environment. Sometimes (rarely) it means that MySQL engineers had not tried hard enough to reproduce the problem.
No Feedback - when bugs spent 30 days in "Need Feedback" and had not got any comment from anybody, this status is set for it automatically. It means that original bug reporter does not care about the problem any more and is not going to reply to questions/requests made, and other community users also do not care enough to find the report and add missing detail. Reports in this status are usually ignored by everybody in MySQL Support and Development.
Need Feedback - bug reporter (or anybody else who cares) should provide additional information about the test case, expected results, environment or version used etc. Without this information MySQL engineer was not able to verify the bug or understand its impact and severity.
Not a Bug - the problem (if any) was a result of reporter's misunderstanding of the manual, the way MySQL server works, misconfiguration or other mistake that has nothing to do with any bugs in the code.
Unsupported - the problem reported happens only on unsupported OS (like HP-UX 11.10), with old and no longer supported MySQL software version (like MySQL server 3.23.58) or it happens on a custom build or fork (like Percona Server or MariaDB).

Note that I've used bold for some status values, I'd call them "final", usually any work on the bug report is completed when it is in final status. You can try to re-open bug in final status, send more comments there but usually the decision is final. Probably it makes sense to open new bug report and reference older one in "final" status there if you have anything useful to say or add, or can prove that the fix is not correct or not complete.

There are also other status values, generic ones (like "Active" - bugs that are not in final status), but they are just a short form for a set of status, and development-related values (like "In progress", "QA review" or "Patch queued") that you can see in some bug reports but that became obsolete and not really used since Oracle forced MySQL development to use internal bugs database that is used for all Oracle software.

So, with these basic status values in mind, let me explain the life cycle of a typical MySQL bug report.

Bug starts as "Open" and from this status it can go to "Analyzing", "Duplicate", "Verified", "To be fixed later", "Can't repeat", "Need Feedback", "Not a Bug" or "Unsupported". You should expect some public comment explaining status change for all changed but to "Analyzing".

Bug with status "Analyzing" can become back "Open" (if analysis led to nothing useful or just had not happened) or it can become "Duplicate", "Verified", "To be fixed later", "Can't repeat", "Need Feedback", "Not a Bug" or "Unsupported" based on the analysis.

Bug with status "Verified" can (and should!) eventually become "Closed", when it is fixed. But it can end up in "Need Feedback" or "Can't repeat" (usually it means incomplete test case, probably a mistake of engineer who verified it), "Duplicate" (developers know better, based on code insights, what fix really solved or is going to solve the route cause of the problem), "To be fixed later", "Won't fix" or even "Not a Bug" (again, rarely, but MySQL engineers who do bugs processing can also misunderstand the manual or test case).

Now the most important thing you should know about MySQL bugs processing the way it is done now in Oracle. When bug is "Verified" and(!) considered serious enough, it is copied to the Oracle internal bugs database and further processing, including status changes etc, is done there. All further comments to the public bug report are then copied to internal bug report automatically, but no comments or status changes in internal bug report are copied to public bug in any other way but by explicit action of some Oracle engineer.

You will see the next status change for the public bug report only when it will get some internal status that corresponds to "final" status above and(!) somebody will care to change its status. Usually this care is taken by members of MySQL Documentation team when they document bug fixes for the release (or soon after that). So, "Closed" bugs will eventually be closed at http://bugs.mysql.com, there is a procedure for that. For any other final status I am not sure about any procedure applied (ask Oracle on how it is done now, my knowledge is outdated anyway). So, you should expect that public and internal bugs databases are out of sync. Community can not know what happens with the bug until it is closed or get any other final status. maybe nothing happens or active development happens. We will never know, until somebody will take care to explain in public!

Bugs that were NOT verified and copied just never appears in Oracle internal bugs database, and busy MySQL developers may note them only occasionally, or when somebody (like Oracle customer or colleague) will ping them personally, or will blog about them. They are probably not allowed formally to work on a bug report until it appears in internal bugs database (correct me if I am wrong).

Coming back to life cycle, bug in "Can't repeat" status can be re-opened by adding comment, but, if I remember it correct, this may not happen automatically, so some MySQL engineer have to note recent comment and check it. Theoretically if test case or missing detail is provided the report can end up then in any status that "Open" bug can get.

"No feedback" is a really bad status for a bug. It means that nobody cares. If you care, add a comment and bug will get status "Open" immediately. As far as I remember any user not only original bug report or MySQL engineer) can add comment (there were different policies over time on this). Now with Oracle SSO login needed to add comments and all that Captcha checks everywhere spam in bugs database should be rare issue anyway.

"Need feedback" is the most important status for community users who care - check bugs with this status and add your comments, similar use cases, missing details etc. Do not rely on original reporter if you understand the problem and question asked - reply immediately!

Final statuses should be final, so I will not speculate now on what may happen or should not happen with report that got them. Maybe later. MySQL engineers with proper permissions can set any status anyway, I've tried to explain usual and expected changes only.

I hope now you understand better why I write about some bugs on Facebook or why I care about number of "Open" bug reports at http://bugs.mysql.com. If we (community users) want our bug reports to be really taken into account, we should make sure they are reviewed and either verified or get any other final status from Oracle. Without that they will be useful only as references to similar problems for people who search or for engineers outside Oracle, but they will not influence MySQL bug fixing or development in Oracle in any clear way. Oracle customers already know what to do...

Tuesday, January 15, 2013

How to use PERFORMANCE_SCHEMA to check InnoDB mutex waits

Summary

To set up InnoDB mutex waits monitoring via PERFORMANCE_SCHEMA in general with MySQL 5.6.9 you should do at least the following:

Start MySQL server with all mutex related instruments enabled at startup (performance_schema=ON by default on recent 5.6.x), like this:

mysqld_safe --performance_schema_instrument='wait/synch/mutex/innodb/%=on' &
Connect to server and set up proper consumers explicitly, like this:

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
Run your problematic load.
Check waits using whatever tables you need, like this:

mysql> select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;
+--------------------------------------------+------------+----------------+
| event_name                                 | count_star | sum_timer_wait |
+--------------------------------------------+------------+----------------+
| wait/synch/mutex/innodb/os_mutex           |      50236 |     2018647953 |
| wait/synch/mutex/innodb/rw_lock_list_mutex |       8299 |      316652455 |
| wait/synch/mutex/innodb/mutex_list_mutex   |       8378 |      300689235 |
| wait/synch/mutex/innodb/fil_system_mutex   |       1173 |       56603575 |
| wait/synch/mutex/innodb/buf_pool_mutex     |        818 |       34652947 |
+--------------------------------------------+------------+----------------+
5 rows in set (0.00 sec)

Sounds easy and should be obvious from the manual (but it was not, at least for me). Now, continue reading if you want to know why and how one can find these out...

Whole story

Let's assume you study some InnoDB performance or scalability problem (like Bug #68079) and would like to know what exact mutex waits happened in the process and how much time was spent on them. Surely you can use INNODB STATUS to see some really bad waits (if you are unlucky) and global wait-related counters, or SHOW ENGINE INNODB MUTEX to see waits that were long enough to force OS context switch:

mysql> show engine innodb mutex;
+--------+-------------------------+-----------------+
| Type   | Name                    | Status          |
+--------+-------------------------+-----------------+
| InnoDB | log0log.cc:737          | os_waits=6      |
| InnoDB | buf0buf.cc:1225         | os_waits=3      |
| InnoDB | combined buf0buf.cc:975 | os_waits=117391 |
| InnoDB | log0log.cc:799          | os_waits=124    |
+--------+-------------------------+-----------------+
4 rows in set (0.00 sec)

The output above is enough to understand what mutex waits were long enough to cause OS waits (context switches), but it does not show how many waits happened in total or how much time InnoDB has to wait. You can try to find out is there any way to get more details and manual can give you a hint that debug binaries will show you enough details... but debug binaries may have different scalability properties and, after all, who uses debug binaries in production :)

For this reason or for other (like a lot of buzz everywhere or, again, fine manual) you eventually will figure out that now one should use PERFORMANCE_SCHEMA to study performance-related problems. It is as easy as setting performance_schema option to ON/1 one may conclude based on the manual. Let's try:

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysqld_safe --no-defaults --performance-schema=ON &
[1] 31089

and then run our problematic test case with mutex waits. Now, when it is finished finally:

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysqlslap -uroot --concurrency=9 --create-schema=test --no-drop --number-of-queries=1000 --iterations=10 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category'
Benchmark
        Average number of seconds to run all queries: 7.230 seconds
        Minimum number of seconds to run all queries: 6.906 seconds
        Maximum number of seconds to run all queries: 7.997 seconds
        Number of clients running queries: 9
        Average number of queries per client: 111

You then try to check the waits, finally, why else you spenht so much time reading about PERFORMANCE_SCHEMA (and improvements to it in 5.6) everywhere:

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;
Empty set (0.00 sec)

Ups, no waits? How is this possible if SHOW ENGINE INNODB MUTEX shows increased counts? "Ah", (after reading the manual about instrumentation) one may say, we need to enable instruments and timing for them! Like this:

mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/synch/mutex/innodb/%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 45 Changed: 0 Warnings: 0

That's because by default not so many are enabled, none of those related to InnoDB mutex waits:

mysql> select * from performance_schema.setup_instruments where name like 'wait/synch/mutex/innodb%';
+-------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/commit_threads_m | NO | NO |
| wait/synch/mutex/innodb/commit_cond_mutex | NO | NO |
| wait/synch/mutex/innodb/innobase_share_mutex | NO | NO |
| wait/synch/mutex/innodb/autoinc_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_zip_mutex | NO | NO |
...
+-------------------------------------------------------+---------+-------+
45 rows in set (0.19 sec)

Yet another fine MySQL 5.6 manual page clearly says that we can enable them dynamically:

"Modifications to any of these tables affect monitoring immediately, with the exception of setup_actors. Modifications to setup_actors affect only foreground threads created thereafter."

So, we try our problematic test case again, check waits as explained above and... get zero rows again. After some more reading of the pages already mentioned somebody as ignorant as me starts to write posts at Facebook, get hints from nice former colleagues like Mark Leith (who actually had written a lot about the topic and influence PERFORMANCE_SCHEMA design and implementation even more) and finally notes that consumers are NOT set (and manual explains they should be):

mysql> select * from performance_schema.setup_consumers;                        +--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)

So, let's fix this and make a quick test if it works at all (before running long one again):

mysql> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> drop table t1;
Query OK, 0 rows affected (1.08 sec)

mysql> create table test.t1 (i int) engine = innodb;                           Query OK, 0 rows affected (1.48 sec)

mysql> insert into test.t1 values (1), (2), (3);                               Query OK, 3 rows affected (0.36 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;
+----------------------------------------+------------+----------------+
| event_name                             | count_star | sum_timer_wait |
+----------------------------------------+------------+----------------+
| wait/synch/mutex/innodb/trx_mutex      |        268 |       14017381 |
| wait/synch/mutex/innodb/trx_undo_mutex |         21 |        1690647 |
+----------------------------------------+------------+----------------+
2 rows in set (0.00 sec)

Yes, it works. Let's start test again and finally get all the benefits of improved MySQL 5.6 diagnostics:

mysql> exit
Bye
[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysqlslap -uroot --concurrency=12 --create-schema=test --no-drop --number-of-queries=1000 --iterations=10 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category'
Benchmark
        Average number of seconds to run all queries: 7.330 seconds
        Minimum number of seconds to run all queries: 6.926 seconds
        Maximum number of seconds to run all queries: 7.692 seconds
        Number of clients running queries: 12
        Average number of queries per client: 83

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 306
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;
+----------------------------------------+------------+----------------+
| event_name                             | count_star | sum_timer_wait |
+----------------------------------------+------------+----------------+
| wait/synch/mutex/innodb/trx_mutex      |        268 |       14017381 |
| wait/synch/mutex/innodb/trx_undo_mutex |         21 |        1690647 |
+----------------------------------------+------------+----------------+
2 rows in set (0.00 sec)

Now, where are my expected (at least buffer pool related, if somebody had forgotten) mutex waits?

Lucky I am, Mark Leith was reading my Facebook at the moment, so I've got a hint that some instruments can be enabled only at startup (try to find the list of them in the manual one day...) and, since 5.6.4, there are command line options to do that.

So, I have to restart server (imagine you are in production!) to get it working finally the way I've expected:

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysqld_safe --no-defaults --performance_schema=ON --performance_schema_instrument='wait/synch/mutex/innodb/%=on' &
[1] 32432
[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ 130115 11:11:23 mysqld_safe Logging to '/home/valerii.kravchuk/mysql-5.6.9-rc-linux-glibc2.5-x86_64/data/cisco1.office.percona.com.err'.
130115 11:11:23 mysqld_safe Starting mysqld daemon with databases from /home/valerii.kravchuk/mysql-5.6.9-rc-linux-glibc2.5-x86_64/data

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> exit
Bye
[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysqlslap -uroot --concurrency=12 --create-schema=test --no-drop --number-of-queries=1000 --iterations=10 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category'
Benchmark
        Average number of seconds to run all queries: 7.640 seconds
        Minimum number of seconds to run all queries: 6.874 seconds
        Maximum number of seconds to run all queries: 7.930 seconds
        Number of clients running queries: 12
        Average number of queries per client: 83

[valerii.kravchuk@cisco1 mysql-5.6.9-rc-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 123
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;
+--------------------------------------------+------------+----------------+
| event_name                                 | count_star | sum_timer_wait |
+--------------------------------------------+------------+----------------+
| wait/synch/mutex/innodb/os_mutex           |     244191 |    32147377962 |
| wait/synch/mutex/innodb/trx_sys_mutex      |      30180 |     4228391496 |
| wait/synch/mutex/innodb/buf_pool_mutex     |       4812 |      757141973 |
| wait/synch/mutex/innodb/mutex_list_mutex   |       8870 |      380601032 |
| wait/synch/mutex/innodb/rw_lock_list_mutex |       8304 |      363065843 |
+--------------------------------------------+------------+----------------+
5 rows in set (0.00 sec)

Then I decided to write this blog post with a quick "howto" summary on top. I hope it is useful for readers. This is the end of story for now.

Conclusions

It's great to have Oracle engineers reading your Facebook posts and replying (especially in a real time). Thank you, Mark Leith!
PERFORMANCE_SCHEMA in MySQL 5.6 can be used to study InnoDB mutex waits
MySQL 5.,6 manual is not clear enough about steps and details about instruments involved to do the study explained above.

I've made some other conclusions as well, but will share them later and maybe elsewhere.

Saturday, January 12, 2013

Fun with Bugs, Issue #1, January 2013

Maybe some of you noted that I write about MySQL bugs on Facebook almost every day. These posts are mostly random and for fun (and addressed usually to my former colleagues in Oracle), but some of bugs I had written about are really serious, coming from Percona and Oracle customers and may affect many MySQL users. Other bugs just show problems in current Oracle's treatment of public bugs database. So I decided to write a series of blog posts that will summarize my recent bugs related comments and highlight problems I consider serious. Being inspired by Sheldon Cooper I called this series "Fun with Bugs". This is issue #1 that will cover period from January 1 till January 10, 2013.

Let me start with bugs that I consider really serious. First of all, all MySQL Cluster users should know about a regression bug introduced in 7.2.9 (that was later removed from downloads without any comments that I've noted) and other recent versions, Bug #67928. I have good news for you. The bug is fixed and version 7.2.10 with the fix is released on January. For some time this bug was not mentioned in the Release Notes, but now we have a very detailed description. Bad news for those Cluster users that may use InnoDB temporary tables while working with cluster binaries - even in 7.2.10 Bug #64663 still happens.

Speaking about InnoDB, you all know about great new features and performance improvements in MySQL 5.6. But does it mean that there are no potentially serious InnoDB bugs in GA versions? Surely, no! Take a look at recently reported ones: Bug #68069 from Domas (no public comments from any Oracle engineer in the bug report at the moment!) or Bug #68022 from Mark Callaghan, Bug #68021 from my colleague Ovais (verified today after my numerous posts about it) or ages old and probably forgotten Bug #64432 from my colleague Laurynas. The worst (as it is a regression bug in 5.5.29) is probably Bug #68051 reported by Davi Arnaut.

Those who are interested in InnoDB internals (and efficiency of disk usage) should check these reports from Jeremy Cole: Bug #67963, Bug #68023 (both are still open at the moment). Good reading, even though hardly any of them will be fixed even in MySQL 5.7.

Users of all kinds of Red Hat Linux should be aware of Bug #67965 when installing recent RPMs. Oracle claims it's all OK and documented to conflict with native RPM, while others may say this is a regression that should be fixed once and forever.

I hope many of you noted that my former colleague Elena Stepanova (now she works for MariaDB) regularly send great bug reports with detailed, ready to use test cases. Recently she had reported some bugs in MySQL 5.6.9. Check Bug #68041 as a good example and be warned that MySQL 5.6 may give not only performance improvements, but also regressions, so you have to test how it works for your applications carefully.

I wanted to write something about more funny reports (that still make a lot of sense) like Bug #68035 (that makes people wonder if they really have recent Core i7 CPU or it is a joke) or Bug #68034 (should MySQL really warn about empty password at the command line or why this matters at all), but probably this blog post is already to long for anybody to still hope to find some fun in it.

So, let me just seriously ask all Oracle customers with valid MySQL support contract who use 5.5.29 and replication in production: please, ask Oracle (in a service request, probably) what Bug #68045 (don't click, it's private) is about and can you be affected. I've heard rumors somewhere that it is a regression bug, but only Oracle customers can get a clear confirmation.

I hope it was fun enough for the beginning. To be continued...

Monday, December 31, 2012

New Year Wishes for Customers of Oracle's MySQL Technical Support Services

Dear Oracle Customers,

I am sure you are mostly happy with MySQL software and support services that Oracle MySQL Support provides. For any experienced DBA MySQL software mostly works "as expected" and when any Oracle customer has a problem she can just call and get proper advice, workaround or solution, 24x7, in frames of a new support request.

In some (rare) cases though MySQL software does NOT work as expected and documented and you may think that you had actually found a new bug. Typical action in this case, encouraged by Oracle, is, again, to open a new support request and work with assigned engineer on a bug report. Engineer will tell you eventually if you hit a known bug, if there are any workarounds for your specific case and, if needed, will open, verify, escalate and monitor processing of the new bug based on your support request and use case. In 99.99% of cases (should be 100, by Oracle rules) this bug report will be created by Oracle support engineer in Oracle's internal bugs database that you can search and access, to some extent, via the same interface you use to work with your support requests or knowledge base articles.

I am sure this works good enough for most of you in most cases. But I am also sure that some of you had cases when the bug that you remember can not be found, or search is slow and produce too many hits (efficient searching in Oracle knowledge base used to be not that easy even for support engineers) or you do not get bug fix as fast as you expected and support request ends up closed with workaround and bug waiting for some future version or decision that nobody can tell you anything definite about, no matter how many times you ask.

All these problems can become easier to solve if you, customer of Oracle MySQL Technical Support, will try the following procedure instead:

1. When you suspect that you are hit by the bug in MySQL software, report it first at http://bugs.mysql.com. Surely you should not report security issues this way or provide any customer sensitive information there, but if without these sensitive details bug report still makes sense, just create it and tell the entire world about the problem found. Share the stack trace, wrong results case, undocumented or improperly documented behavior. You can do this via account that is not associated with your business or company name if you wish.

2. Then immediately open support request and inform Oracle about bug number for the bug report you had created. You can provide all missing details requested by Oracle engineers in frames of support request, but, please, share all substantial, nonsensitive and not security related information in public bug report as well, or ask Oracle engineer to do this for you. Basically, ask Oracle to process your bug report and inform community if it is accepted as a new bug, duplicate of a known bug or if any further testing/information is required. Some bug reports can be a result of your misunderstanding of the manual or improper use of some feature, but even in this case it would be nice to see public explanation why this is not a bug.

This procedure should not be used for bugs with clear security implications - for them you should probably inform only vendor and/or security related organizations. Otherwise, I think, it is almost as easy as just creating new support request (you will be asked to provide details about the problem anyway), but gives the following benefits to Oracle customer:

1. Bug report at http://bugs.mysql.com can be easily found later using search interface of public bugs database or just by using Google search for the site, like this:

site:bugs.mysql.com drop add index InnoDB

2. Bug report becomes visible to other customers, community members and MySQL support providers and developers outside of Oracle. You can get valid advice, workaround or even patch/bug fix faster as a result. Or, at least, you can get opinion of others and more reasons to escalate the bug for Oracle (if other customers and well known community users will see it and find out they are also affected).

Several Oracle (and, before that Sun's and MySQL AB's) customers had used this approach having valid support contract in place and I hardly remember any negative result of this over years. If you think that some articles in your support contract does NOT let you apply the procedure above, well, you can try to change these requirements while renewing it or signing new ones.

Why I care how Oracle customers report bugs, being not associated with Oracle for several months already, one may ask? That's because I've spent many years processing bugs at http://bugs.mysql.com and I still want to see it as a central location of all information related to MySQL bugs and feature requests. I think this is useful for all parties: Oracle MySQL support and development teams, Oracle customers, community users and alternative vendors of MySQL-related software and services.

Best wishes to you all in the New Year of 2013, year of MySQL 5.6 GA release and many interesting MySQL-related events! May your backup and restore strategy work as expected and you never need to report any bug in MySQL. But if you'll hit something that looks like a bug next year, please, try to remember this post and think twice on how to report it.