MySQL Support Engineer's Chronicles, Issue #10

As promised, I am trying to write one blog post in this series per week. So, even though writing about InnoDB row formats took a lot of time and efforts this weekend, I still plan to summarize my findings, questions, discussions, bugs and links I've collected over this week.

I've shared two links this week on Facebook that got a lot of comments (unlike links to my typical blog posts). The first one was to Marko Mäkelä's blog post at MariaDB.com, "InnoDB Quality Improvements in MariaDB Server". I do not see any comments (or any obvious way to comment) there, but the comments I've got at Facebook were mostly related to the statement that  

"We are no longer merging new MySQL features with MariaDB..."

noted in the text by Mark Callaghan and to the idea that "InnoDB" is a trademark of Oracle, so using it to refer to a fork (that is incompatible with the "upstream" InnoDB in too many ways since MariaDB 10.1 probably) is wrong, as stated by Matt Lord and Sunny Bains. People in the comments mostly agree that a new name makes sense (there are more reasons to give it now anyway than in the case of XtraDB by Percona), and we had a lot of nice and funny suggestions on Slack internally (FudDB was not among them, this is a registered trademark of Miguel Angel Nieto for many years already). We shell see how this may end up, but I would not be surprised by a new name announced soon. I suggest you to read comments in any case if you have a Facebook account, many of them are interesting.

The second link was to Kaj Arnö's post at mariadb.org, "On Contributions, Pride and Cockiness". It's worth checking just because of Monty's photo there. Laurynas Biveinis stated in the comments that any comparison of number of pull requests (open and processed) is meaningless when development model used by other parties is different (closed, with contributions coming mostly via bug reports in case of Oracle, or all changes, external and internal, coming via pull requests in case of Percona). MariaDB uses a mix of a kind, where some contributions from contractors come via pull requests, while engineers from MariaDB Corporation work on GitHub sources of MariaDB Server directly. Anyway (meaningless statistics aside), MariaDB seems to be the easiest target for contributions from Community at the moment, and nobody argued against that. My followers also agreed that the same workflow for internal and external contributions is a preferred development model in ideal world.

This kind of public discussions of (serious and funny) MySQL-related matters on Facebook (along with public discussions on MySQL bugs) make me think the way I use my Facebook page is proper and good for the mankind.

Now back to notes made while working on Support issues. This week I had to explain one case when MariaDB server was shut down normally (but unexpectedly for DBA):

2019-05-22 10:37:55 0 [Note] /usr/libexec/mysqld (initiated by: unknown): Normal shutdown

This Percona blog post summarizes different ways to find a process which sent a HUP/KILL/TERM or other signal to the mysqld process. I've used SystemTap-based solution like suggested in that blog post in the past successfully. In this context I find this summary of the ways to force MySQL to fail useful. for all kinds of testing. SELinux manual is also useful to re-read at times.

This week I've spent a lot of time and some efforts trying to reproduce the error (1942 and/or 1940 if anyone cares) on Galera node acting as an async replication slave. These efforts ended up with a bug report, MDEV-19572. Surely the idea to replicate MyISAM tables outside of mysql database to Galera cluster is bad at multiple levels, but why the error after running for a long time normally? In the process of testing I was reading various remotely related posts, so checked this and that... I also hit other problems in the process. Like this crash that happened probably while sending some signal to the node unintentionally:

190523 17:19:46 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.2.23-MariaDB-log
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=3
max_threads=153
thread_count=65544
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467240 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x49000
/home/openxs/dbs/maria10.2/bin/mysqld(my_print_stacktrace+0x29)[0x7f6475eb5b49]
/home/openxs/dbs/maria10.2/bin/mysqld(handle_fatal_signal+0x33d)[0x7f64759d50fd]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7f6473887330]
/home/openxs/dbs/maria10.2/bin/mysqld(+0xb3b817)[0x7f6475ebc817]
/home/openxs/dbs/maria10.2/bin/mysqld(+0xb3b9e6)[0x7f6475ebc9e6]
/home/openxs/dbs/maria10.2/bin/mysqld(+0xb3bb8a)[0x7f6475ebcb8a]
/home/openxs/dbs/maria10.2/bin/mysqld(lf_hash_delete+0x61)[0x7f6475ebcfa1]
/home/openxs/dbs/maria10.2/bin/mysqld(+0x601eed)[0x7f6475982eed]
include/my_atomic.h:298(my_atomic_storeptr)[0x7f6475983464]
sql/table_cache.cc:534(tdc_delete_share_from_hash)[0x7f6475811f17]
sql/table_cache.cc:708(tdc_purge(bool))[0x7f64759351ea]
sql/sql_base.cc:376(close_cached_tables(THD*, TABLE_LIST*, bool, unsigned long))[0x7f64757c9ec7]
nptl/pthread_create.c:312(start_thread)[0x7f647387f184]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f6472d8c03d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

I was not able so far top find the exact some backtrace in any known MariaDB bug, so one day I'll have to try to reproduce this crash as well.

I try to check some MariaDB ColumnStore issues from time ot time, for a change, and this week I ended up reading this KB page while trying to understand how much we can control placement of data there.

Finally, for the records, this is the way to "fix" InnoDB statistics if needed (and the need is real as you can find out from Bug #95507 - "innodb_stats_method is not honored when innodb_stats_persistent=ON" reported by my colleague Sergei Petrunia):

update mysql.innodb_index_stats set last_update=now(), stat_value=445000000 where database_name='test' and table_name='t1' and index_name='i1' and stat_name='n_diff_pfx01';

I like to return to familiar nice places and topics, like Regent's Canal or MySQL bugs...

The last bug not the least, MySQL bugs. This week I've subscribed to the following (already "Verified") interesting bug reports (besides the one mentioned above):

• Bug #95484 - "EXCHANGE PARTITION works wrong/weird with different ROW_FORMAT.". Jean-François Gagné found out that there is a way to have partitions with different row_format values in the same InnoDB table, at least in MySQL 5.7. So why is this not supported officially? See also his Bug #95478 - "CREATE TABLE LIKE does not honour ROW_FORMAT.". It's a week of ROW_FORMAT studies for me, for sure!
• Bug #95462 - "Data comparison broke in MySQL 8.0.16". It's common knowledge how much I like regression bugs. MySQL 8.0.16 introduced a new one, reported by
  Raman Haran, probably based on some good and valid intentions. But undocumented changes in behavior in GA versions are hardly acceptable, no matter what are the intentions.

That's all for now. Some more links to MySQL bugs from me are always available on Twitter.

Fun with Bugs #51 - My Bug Reports that Oracle doesn't Want to Fix

This week I noticed (yet another) customer issue related to the output produced by mysqladmin debug command (or when mysqld process gets SIGHUP signal). I mean the output generated by the mysql_print_status() function. In this issue the content of the output was misinterpreted. I've seen this in the past more than once, and requested to document the output properly, but it never happened for a reason that there is an internal feature request to put this information elsewhere, in Performance Schema or Information Schema. The bug ended up with "Won't fix" status.

Surely I complained in a comment and on Facebook, and then decided to check if there are any other my bug reports and documentation request that Oracle explicitly decided not to fix after accepting the fact that there is a problem.

I've ended up with the following short list:

• Bug #69399 - "Inconsistency in crash report". Here I've got a perfect reason to keep things as they are currently implemented. Functions called from signal handlers must be async signal safe, and time() is like that, but it always outputs in UTC. It would be great to print time in UTC in some messages as well, so that timezone difference is 100% clear, but it's truly not a big deal.
• Bug #71300 - "Manual does not explain that statement/abstract/* instruments appeared in 5.6.15". This change in naming that happened in 5.6.15 is, indeed, explained in the manual, even if there is no highlighted statements about incompatible change etc. I can live with that.
• Bug #71303 - "Manual page on P_S build configuration does not provide enough details". I really missed the details at that time on how to instrument individual buffer's mutexes/rwlocks, after getting a hint during my talk that I had no chance to see real most important waits in Bug #68079  with Performance Schema without recompiling it properly. I've got a useful comment at the end of the bug report, but I truly do not understand why this detail ("The only way to enable it is by removing the line which defines PFS_SKIP_BUFFER_MUTEX_RWLOCK in storage/innobase/include/sync0sync.h. Seems to be no compiler flags to enable or disable the above mentioned symbol.") was not added to the small enough page as a note.
• Bug #71304 - "Manual does not provide enough details about automatic sizing of P_S parameters ". Here my suggestions were refused. Go figure yourself, check the output of mysqld --verbose --help 2>/dev/null | grep performance | grep "\-1" to find out what parameters are auto-sized and go read the code to find out how exactly, if you care. They don't.
• Bug #71274 - "Manual does not provide enough details about background threads in P_S.threads". All I've got in reply is: "The purpose of the page is to describe the table structure, not enumerate the (subject to change) set of background threads." You may be satisfied with this remark, but I am not.
• Bug #71346 - "Manual does not provide details on mysqladmin debug output". As you can check here, even for MySQL 8 the command is still there, but all we have about the output is: "Tell the server to write debug information to the error log. Format and content of this information is subject to change. This includes information about the Event Scheduler."
• Bug #75366 - "mysql_install_db requires explicit --lc-messages-dir if non-default PREFIX used". This was reported at early MySQL 5.7.x development stage, and I've got a recommendation to use mysqld --initialize instead. I do so now, but sometimes some related problem still happen, see Bug #84173 and Bug #80351. I think that even deprecated commands must be properly documented, including any incompatible changes in behavior, options, binaries location etc, until they still exist in GA and supported versions of MySQL.
• Bug #78822 - "Materialized table from semijoin may change join order and lead to bad plan". In MySQL 5.7 the problem must be fixed, and for 5.6 the following obvious workaround was suggested: set optimizer_switch="semijoin=off";
• Bug #80601 - "Manual is wrong/not clear while explaining kill flag check for ALTER TABLE".  Even though it was stated that "this is an implementation detail subject to change", some clarifications happened in the manual.

To summarize, out of 310 bug reports I've created since 2005, Oracle decided not to fix just 9, and in many cases provided proper explanations about the reasons to do this, or made some changes in the manual. The remaining cases all are related to MySQL manual and mostly happened in 2014, when nice people found a way to shut me up (temporary) on the topic of MySQL bugs...

When kill flag is checked for SELECT? Part II

In the previous part I've stopped at the moment when we entered JOIN:exec() - most checks for kill flag happen somewhere there, during query execution. We know the list of functions that checks this flag during query execution:

sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()
end_unique_update()
end_write_group()
remove_dup_with_compare()
remove_dup_with_hash_index()

but we do not know when exactly each of them is called. So, let me try to show what happens inside JOIN::exec (some code paths and checks are not considered for simplicity, we care about SELECT, but not EXPLAIN SELECT etc). I've included statements that change thread status and highlighted parts of code with the same thread status with different background colors and, as usual, functions that eventually may check kill flag are highlighted with bold:

JOIN::exec()
  thd_proc_info(thd, "executing");
  get_schema_tables_result()
  /* Create a tmp table if distinct or if the sort is too complicated */
  if (need_tmp)
  {
    thd_proc_info(thd, "Copying to tmp table");
    do_select()
    change_to_use_tpm_fileds()
    change_refs_to_tmp_fields()
    JOIN::make_simple_join()
    create_tmp_table()
    thd_proc_info(thd, "Creating sort index");
    create_sort_index()
    thd_proc_info(thd, "Copying to group table");
    JOIN::make_sum_func_list()
    setup_sum_funcs()
    do_select()
    end_read_record()
    change_to_use_tpm_fileds()
    if (curr_join->select_distinct && ! curr_join->group_list)
    {
      thd_proc_info(thd, "Removing duplicates");
      remove_duplicates()
    }
    calc_group_buffer()
    count_filed_types()
  } 
  /* let's ignore case of procedure entirely */
  /* simplification, few ifs ignored */
  make_group_fileds()
  init_items_ref_array()
  setup_copy_fields()
  JOIN::set_itmes_ref_array()
  JOIN::make_sum_func_list()
  prepare_sum_aggregators()
  setup_sum_funcs()
  if (curr_join->group_list || curr_join->order)
  {
     thd_proc_info(thd, "Sorting result");
     make_cond_for_table()
     create_sort_index()
  }
  send_result_set_metadata()
  thd_proc_info(thd, "Sending data");
  do_select()

As you can see, do_select() function may be called in different places and more than once if temporary table is used. Let's check what this function does:

do_select()
  /* Set up select_end */
  end_select=setup_end_select_func()
  if (join->tables)
  {
    join->join_tab[join->tables-1].next_select= end_select;
    join_tab=join->join_tab+join->const_tables;
  }
  ...
  sub_select()
  ...
  join->result->send_eof()

 So, basically do_select() determines  how to do next select step and then calls sub_select():
 
sub_select()
  if (end_of_records)
     return (*join_tab->next_select)(join,join_tab+1,end_of_records);
  ...
  rc= evaluate_join_record(join, join_tab, error);
  while (rc == NESTED_LOOP_OK)
  {
    error= info->read_record(info);
    rc= evaluate_join_record(join, join_tab, error);
  }
  if (rc == NESTED_LOOP_NO_MORE_ROWS &&
      join_tab->last_inner && !join_tab->found)
    rc= evaluate_null_complemented_join_record(join, join_tab);

Here we can call one of functions for the next select step or call evaluate_join_record() in a loop. evaluate_join_record() is one of the functions that checks kill flag before doing read work.

Most of other functions that check kill flag are called when we are done with nested loop join and already found a dataset for GROUP BY and ORDER BY processing (in a temporary table). This is how end_select function is determined:

/*
Rows produced by a join sweep may end up in a temporary table or be
sent to a client. Setup the function of the nested loop join algorithm
which handles final fully constructed and matched records.
*/

11382 Next_select_func setup_end_select_func(JOIN *join)
11383 {
11384  TABLE *table= join->tmp_table;
11385  TMP_TABLE_PARAM *tmp_tbl= &join->tmp_table_param;
11386  Next_select_func end_select;
11387
11388  /* Set up select_end */
11389  if (table)
11390  {
11391    if (table->group && tmp_tbl->sum_func_count &&
11392    !tmp_tbl->precomputed_group_by)
11393    {
11394      if (table->s->keys)
11395      {
11396        DBUG_PRINT("info",("Using end_update"));
11397        end_select=end_update;
11398      }
11399      else
11400      {
11401        DBUG_PRINT("info",("Using end_unique_update"));
11402        end_select=end_unique_update;
11403      }
11404    }
11405    else if (join->sort_and_group && !tmp_tbl->precomputed_group_by)
11406    {
11407      DBUG_PRINT("info",("Using end_write_group"));
11408      end_select=end_write_group;
11409    }
11410    else
11411    {
11412      DBUG_PRINT("info",("Using end_write"));
11413      end_select=end_write;
11414      if (tmp_tbl->precomputed_group_by)
11415      {
11416        /*
11417        A preceding call to create_tmp_table in the case when loose
11418        index scan is used guarantees that
11419        TMP_TABLE_PARAM::items_to_copy has enough space for the group
11420        by functions. It is OK here to use memcpy since we copy
11421        Item_sum pointers into an array of Item pointers.
11422        */
11423        memcpy(tmp_tbl->items_to_copy + tmp_tbl->func_count,
11424          join->sum_funcs,
11425          sizeof(Item*)*tmp_tbl->sum_func_count);
11426        tmp_tbl->items_to_copy[tmp_tbl->func_count+tmp_tbl->sum_func_count]= 0;
11427      }
11428    }
11429  }
11430  else
11431  {
11432    /*
11433    Choose method for presenting result to user. Use end_send_group
11434    if the query requires grouping (has a GROUP BY clause and/or one or
11435    more aggregate functions). Use end_send if the query should not
11436    be grouped.
11437    */
11438    if ((join->sort_and_group ||
11439      (join->procedure && join->procedure->flags & PROC_GROUP)) &&
11440      !tmp_tbl->precomputed_group_by)
11441      end_select= end_send_group;
11442    else
11443      end_select= end_send;
11444  }
11445  return end_select;
11446 } 

So, one of the functions that check for kill flag is used whenever we need to use temporary table to process GROUP BY and/or ORDER BY:

end_write()
end_update()
end_unique_update()
end_write_group()
Only 3 functions remain. Where sub_select_cache() is used (by the way, it calls sub_select() also, so may lead to more checks of kill flag in the process)?  It is used whenever you see "Using join buffer" in the EXPLAIN results for the query. This is determined at the optimization stage:

JOIN::optimize()
  make_join_readinfo()
...
 6868  for (i=join->const_tables ; i < join->tables ; i++)
 6869  {
...
 6875    tab->next_select=sub_select; /* normal select */
 6876
 6877    /*
 6878    Determine if the set is already ordered for ORDER BY, so it can
 6879    disable join cache because it will change the ordering of the results.
 6880    Code handles sort table that is at any location (not only first after
 6881    the const tables) despite the fact that it's currently prohibited.
 6882    We must disable join cache if the first non-const table alone is
 6883    ordered. If there is a temp table the ordering is done as a last
 6884    operation and doesn't prevent join cache usage.
 6885    */
...
 6896    switch (tab->type) {
...
 6915    case JT_ALL:
 6916    /*
 6917    If previous table use cache
 6918    If the incoming data set is already sorted don't use cache.
 6919    */
 6920    if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
 6921    tab->use_quick != 2 && !tab->first_inner && !ordered_set)
 6922    {
 6923      if ((options & SELECT_DESCRIBE) ||
 6924        !join_init_cache(join->thd,join->join_tab+join->const_tables,
 6925          i-join->const_tables))
 6926      {
 6927        tab[-1].next_select=sub_select_cache; /* Patch previous */
 6928      }
 6929    }
...

Read the manual for more details of join buffer usage. Basically it is used to reduce number of reads of inner tables in joins that are scanned entirely (see JT_ALL above).

Remaining two functions that check kill flag during query execution are called in remove_duplicates():

14327 static int

14328 remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having)

14329 {

...

14360  entry->file->info(HA_STATUS_VARIABLE);

14361  if (entry->s->db_type() == heap_hton ||

14362      (!entry->s->blob_fields &&

14363       ((ALIGN_SIZE(reclength) + HASH_OVERHEAD) * entry->file->stats.records <

14364    thd->variables.sortbuff_size)))

14365    error=remove_dup_with_hash_index(join->thd, entry,

14366                     field_count, first_field,

14367                     reclength, having);

14368  else

14369    error=remove_dup_with_compare(join->thd, entry, first_field, offset,

14370                  having);

14371 

14372  free_blobs(first_field);

14373  DBUG_RETURN(error);

14374 }

The remove_duplicates() itself may be called in JOIN::exec() and it was highlighted in the beginning of this post.

Now the picture is almost complete, we know when kill flag is checked during query execution also. What I still miss is some nice text (or simple pseudo code that fits into one screen) for the manual to replace that oversimplified statement that we had started with. I have to think about this and present in the final Part III, so... to be continued.

When kill flag is checked for SELECT? Part I

Manual describes this briefly:

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

Complete, correct and useful answer is more complex though. Here is correct answer, but not very useful. So, kill flag is checked in the following functions related to SELECT statement processing:

make_join_statistics()
best_extension_by_limited_search()
find_best()
sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()
end_unique_update()
end_write_group()
remove_dup_with_compare()
remove_dup_with_hash_index() 

Continue reading if you are interested in the process of getting correct answer and would like to know how to make it also complete and useful eventually.

Let's just use grep to search for thd->killed usage in source code (of version 5.5.30) that implements SELECT and then check functions with these lines:

[openxs@chief mysql-5.5]$ grep -n 'thd->kill' sql/sql_select.cc
3136:  DBUG_RETURN(join->thd->killed || get_best_combination(join));
5463:  if (thd->killed)  // Abort
5602:  if (thd->killed)
11604:  if (join->thd->killed)          // If aborted by user
11807:  if (join->thd->killed)                  // Aborted by user
12052:    if (join->thd->killed)
12915:  if (join->thd->killed)                  // Aborted by user
12968:  if (join->thd->killed)                  // Aborted by user
13048:  if (join->thd->killed)                  // Aborted by user
13095:  if (join->thd->killed)
14394:    if (thd->killed)
14523:    if (thd->killed)

Line 3136 is in the make_join_statistics() function, that is, flag is checked in the process of query optimization also:

3122  /* Find an optimal join order of the non-constant tables. */

3123  if (join->const_tables != join->tables)

3124  {

3125  optimize_keyuse(join, keyuse_array);

3126  if (choose_plan(join, all_table_map & ~join->const_table_map))

3127  goto error;

3128  }

3129  else

3130  {

3131  memcpy((uchar*) join->best_positions,(uchar*) join->positions,

3132  sizeof(POSITION)*join->const_tables);

3133  join->best_read=1.0;

3134  }

3135  /* Generate an execution plan from the found optimal join order. */

3136  DBUG_RETURN(join->thd->killed || get_best_combination(join));

  
Line 5463 is in the best_extension_by_limited_search() function, so again flag is checked in the process of query optimization:

5451 static bool

5452 best_extension_by_limited_search(JOIN *join,

5453  table_map remaining_tables,

5454  uint idx,

5455  double record_count,

5456  double read_time,

5457  uint search_depth,

5458  uint prune_level)

5459 {

5460  DBUG_ENTER("best_extension_by_limited_search");

5461 

5462  THD *thd= join->thd;

5463  if (thd->killed) // Abort

5464  DBUG_RETURN(TRUE);

5465 

5466  DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx,

5467  "SOFAR:"););

5468 

5469  /*

5470  'join' is a partial plan with lower cost than the best plan so far,

5471  so continue expanding it further with the tables in 'remaining_tables'.

5472  */

5473  JOIN_TAB *s;

 

Line 5602 is in the find_best() function:

 

5596 static bool

5597 find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,

5598  double read_time)

5599 {

5600  DBUG_ENTER("find_best");

5601  THD *thd= join->thd;

5602  if (thd->killed)

5603  DBUG_RETURN(TRUE);

5604  if (!rest_tables)

5605  {

5606  DBUG_PRINT("best",("read_time: %g record_count: %g",read_time,

5607  record_count));

5608 

5609  read_time+=record_count/(double) TIME_FOR_COMPARE;

...

 

So, again it is related to the process of finding optimal join order at the query optimization stage. All these cases are not mentioned in the manual.

Line 11604 is finally related to query execution (reading rows). It is in the sub_select_cache() function:

11592 enum_nested_loop_state

11593 sub_select_cache(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)

11594 {

11595  enum_nested_loop_state rc;

11596 

11597  if (end_of_records)

11598  {

11599  rc= flush_cached_records(join,join_tab,FALSE);

11600  if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS)

11601  rc= sub_select(join,join_tab,end_of_records);

11602  return rc;

11603  }

11604  if (join->thd->killed) // If aborted by user

11605  {

11606  join->thd->send_kill_message();

11607  return NESTED_LOOP_KILLED; /* purecov: inspected */

11608  }

...

Line 11807 is at the beginning of the evaluate_join_record() function:

11794 static enum_nested_loop_state

11795 evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,

11796  int error)

11797 {

11798  bool not_used_in_distinct=join_tab->not_used_in_distinct;

11799  ha_rows found_records=join->found_records;

11800  COND *select_cond= join_tab->select_cond;

11801  bool select_cond_result= TRUE;

11802 

11803  if (error > 0 || (join->thd->is_error())) // Fatal error

11804  return NESTED_LOOP_ERROR;

11805  if (error < 0)

11806  return NESTED_LOOP_NO_MORE_ROWS;

11807  if (join->thd->killed) // Aborted by user

11808  {

11809  join->thd->send_kill_message();

11810  return NESTED_LOOP_KILLED; /* purecov: inspected */

11811  }

 

Line 12052 is in the records reading loop (finally) in the flush_cached_records() function (that you see called above on line 11599 in sub_select_cache()):

12036  /* read through all records */

12037  if ((error=join_init_read_record(join_tab)))

12038  {

12039  reset_cache_write(&join_tab->cache);

12040  return error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;

12041  }

12042 

12043  for (JOIN_TAB *tmp=join->join_tab; tmp != join_tab ; tmp++)

12044  {

12045  tmp->status=tmp->table->status;

12046  tmp->table->status=0;

12047  }

12048 

12049  info= &join_tab->read_record;

12050  do

12051  {

12052  if (join->thd->killed)

12053  {

12054  join->thd->send_kill_message();

12055  return NESTED_LOOP_KILLED; // Aborted by user /* purecov: inspected */

12056  }

...

12093  } while (!(error=info->read_record(info)));

Line 12915 is in the end_write() function:

12908 static enum_nested_loop_state

12909 end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12910  bool end_of_records)

12911 {

12912  TABLE *table=join->tmp_table;

12913  DBUG_ENTER("end_write");

12914 

12915  if (join->thd->killed) // Aborted by user

12916  {

12917  join->thd->send_kill_message();

12918  DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */

12919  }

 

Line 12968 is in the end_update():  

 

12957 static enum_nested_loop_state

12958 end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12959  bool end_of_records)

12960 {

12961  TABLE *table=join->tmp_table;

12962  ORDER *group;

12963  int error;

12964  DBUG_ENTER("end_update");

12965 

12966  if (end_of_records)

12967  DBUG_RETURN(NESTED_LOOP_OK);

12968  if (join->thd->killed) // Aborted by user

12969  {

12970  join->thd->send_kill_message();

12971  DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */

12972  }

 

Line 13048 is in the end_unique_update() (it's getting boring, isn't it, a lot of similar looking code is similar named functions):

13038 static enum_nested_loop_state

13039 end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

13040  bool end_of_records)

13041 {

13042  TABLE *table=join->tmp_table;

13043  int error;

13044  DBUG_ENTER("end_unique_update");

13045 

13046  if (end_of_records)

13047  DBUG_RETURN(NESTED_LOOP_OK);

13048  if (join->thd->killed) // Aborted by user

13049  {

13050  join->thd->send_kill_message();

13051  DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */

13052  }

Line 13095 is from something similar also, end_write_group() function (note that comment is placed differently though):

13087 static enum_nested_loop_state

13088 end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

13089  bool end_of_records)

13090 {

13091  TABLE *table=join->tmp_table;

13092  int idx= -1;

13093  DBUG_ENTER("end_write_group");

13094 

13095  if (join->thd->killed)

13096  { // Aborted by user

13097  join->thd->send_kill_message();

13098  DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */

13099  }

We are almost done. Line 14394 is finally closely related to something manual described, "GROUP BY loop" in the remove_dup_with_compare() function:

14377 static int remove_dup_with_compare(THD *thd, TABLE *table, Field **first_field,

14378  ulong offset, Item *having)

14379 {

14380  handler *file=table->file;

14381  char *org_record,*new_record;

14382  uchar *record;

14383  int error;

14384  ulong reclength= table->s->reclength-offset;

14385  DBUG_ENTER("remove_dup_with_compare");

14386 

14387  org_record=(char*) (record=table->record[0])+offset;

14388  new_record=(char*) table->record[1]+offset;

14389 

14390  file->ha_rnd_init(1);

14391  error=file->rnd_next(record);

14392  for (;;)

14393  {

14394  if (thd->killed)

14395  {

14396  thd->send_kill_message();

14397  error=0;

14398  goto err;

14399  }

...

14446  file->position(record); // Remember position

14447  }

14448  }

14449  if (!found)

14450  break; // End of file

14451  /* Restart search on next row */

14452  error=file->restart_rnd_next(record,file->ref);

14453  }

14454 

14455  file->extra(HA_EXTRA_NO_CACHE);

14456  DBUG_RETURN(0);

14457 err:

14458  file->extra(HA_EXTRA_NO_CACHE);

14459  if (error)

14460  file->print_error(error,MYF(0));

14461  DBUG_RETURN(1);

14462 }

 

Finally, line 14523 is in the loop in the remove_dup_with_hash_index() function:

14518  file->ha_rnd_init(1);

14519  key_pos=key_buffer;

14520  for (;;)

14521  {

14522  uchar *org_key_pos;

14523  if (thd->killed)

14524  {

14525  thd->send_kill_message();

14526  error=0;

14527  goto err;

14528  }

...

 

That's all great, but how these functions are related to each other? MySQL Internals manual will help us to get the general picture of how SELECT is processed:

 

handle_select()
   mysql_select()
     JOIN::prepare()
       setup_fields()
     JOIN::optimize()            /* optimizer is from here ... */
       optimize_cond()
       opt_sum_query()
       make_join_statistics()
         get_quick_record_count()
         choose_plan()
           /* Find the best way to access tables */
           /* as specified by the user.          */
           optimize_straight_join()
             best_access_path()
           /* Find a (sub-)optimal plan among all or subset */
           /* of all possible query plans where the user    */
           /* controls the exhaustiveness of the search.   */
           greedy_search()
             best_extension_by_limited_search()
               best_access_path()
           /* Perform an exhaustive search for an optimal plan */
           find_best()
       make_join_select()        /* ... to here */
     JOIN::exec()

 

In the diagram above I've highlighted optimizer part with different background and functions where kill flag is checked with bold.

 

It would be nice to see/describe JOIN::exec() in a way similar to above. I plan to do this in the second part of this post. To be continued...