Bug #80601 Manual is wrong/not clear while explaining kill flag check for ALTER TABLE
Submitted: 3 Mar 2016 8:57 Modified: 4 Apr 2016 14:35
Reporter: Valeriy Kravchuk Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: alter, KILL, missing manual

[3 Mar 2016 8:57] Valeriy Kravchuk
Manual (http://dev.mysql.com/doc/refman/5.7/en/kill.html) says:

"During ALTER TABLE operations, the kill flag is checked before each block of rows are read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted."

So, it mentions "block of rows" and prompts users to ask questions like: 

"What is the size of that block?", or

"What constitutes the block of rows?"

There is no answer in the manual, neither in general nor for specific storage engine.

How to repeat:
Search the source code related to ALTER TABLE for the thd->killed flag checks. On current 5.7 code I see:

[openxs@fc23 mysql-server]$ grep -n 'thd->kill' sql/sql_table.cc
2442:      if (thd->killed)
10115:    if (thd->killed)
10341:            if (thd->killed)
[openxs@fc23 mysql-server]$

The first hit is in the mysql_rm_table_no_locks() function:

  Execute the drop of a normal or temporary table.

  @param  thd             Thread handler
  @param  tables          Tables to drop
  @param  if_exists       If set, don't give an error if table doesn't exists.
                          In this case we give an warning of level 'NOTE'
  @param  drop_temporary  Only drop temporary tables
  @param  drop_view       Allow to delete VIEW .frm
  @param  dont_log_query  Don't write query to log files. This will also not
                          generate warnings if the handler files doesn't exists

  @retval  0  ok
  @retval  1  Error
  @retval -1  Thread was killed
int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists,
                            bool drop_temporary, bool drop_view,
                            bool dont_log_query)

The second hit is in the copy_data_between_tables() function that is really executed when ALTER does a table copy (and reads rows):

static int
copy_data_between_tables(PSI_stage_progress *psi,
                         TABLE *from,TABLE *to,
                         List<Create_field> &create,
                         ha_rows *copied,
                         ha_rows *deleted,
                         Alter_info::enum_enable_or_disable keys_onoff,
                         Alter_table_ctx *alter_ctx)

The code is like this:

  10113   while (!(error=info.read_record(&info)))
  10114   {
  10115     if (thd->killed)
  10116     {
  10117       thd->send_kill_message();
  10118       error= 1;
  10119       break;
  10120     }
  10121     /* Return error if source table isn't empty. */
  10122     if (alter_ctx->error_if_not_empty)
  10123     {
  10124       error= 1;
  10125       break;
  10126     }
  10182     thd->get_stmt_da()->inc_current_row_for_condition();
  10183   }
  10184   end_read_record(&info);

and tracing in gdb shows that at least for InnoDB table the loop is executed for each row in the original table ALTERed, and on each iteration we start from checking the kill flag!

The third hit is in mysql_checksum_table() function:

bool mysql_checksum_table(THD *thd, TABLE_LIST *tables,
                          HA_CHECK_OPT *check_opt)

so is not directly relevant.

Suggested fix:
Document clearly that when ALTER requires a table copy it checkes kill flag before reading every next row from the table, or otherwise explain how read_record() function is implemented for different storage engines available in MySQL.
[9 Mar 2016 14:17] Miguel Solorzano
Thank you for the bug report.
[4 Apr 2016 14:35] Paul Dubois
I asked Dmitry L about this, who indicated that this is an implementation detail subject to change. So the manual isn't going to try to be more specific here.

On the other hand, this applies for ALTER TABLE operations that make a *copy*, so I'll revise the wording to include that:

ALTER TABLE operations that make a table copy check the kill flag
periodically for each few copied rows read from the original table.
If the kill flag was set, the statement is aborted and the temporary
table is deleted.
[5 Apr 2016 14:02] Paul Dubois
Addition to previous comment:

The KILL statement returns without waiting for confirmation, but the
kill flag check aborts the operation within a reasonably small amount
of time. Aborting the operation to perform any necessary cleanup also
takes some time.