Bug #78452 Alter table add virtual index hits assert in ha_innobase::inplace_alter_table
Submitted: 16 Sep 2015 11:41 Modified: 22 Oct 2015 5:32
Reporter: Olav Sandstå Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[16 Sep 2015 11:41] Olav Sandstå 
Description:
The following ALTER TABLE statement crashes the server:

ALTER TABLE employees 
  ADD COLUMN name VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) VIRTUAL,
  ADD INDEX name_idx(name);

The following assert in ha_innobase::inplace_alter_table() is hit:

  		ut_ad(ctx->need_rebuild() || ctx->new_table->n_v_cols);

The call stack:

#6  0x0000000001b44e6f in ut_dbg_assertion_failed (expr=0x2104ff8 "ctx->need_rebuild() || ctx->new_table->n_v_cols", file=0x21029c8 "/export/home2/tmp/olav/mysql2/perconalive/storage/innobase/handler/handler0alter.cc", line=5864) at /export/home2/tmp/olav/mysql2/perconalive/storage/innobase/ut/ut0dbg.cc:67
#7  0x000000000195fe98 in ha_innobase::inplace_alter_table (this=0x7f04a400bbb0, altered_table=0x7f04a4dcf230, ha_alter_info=0x7f04b35fa730) at /export/home2/tmp/olav/mysql2/perconalive/storage/innobase/handler/handler0alter.cc:5864
#8  0x0000000000f6fc83 in handler::ha_inplace_alter_table (this=0x7f04a400bbb0, altered_table=0x7f04a4dcf230, ha_alter_info=0x7f04b35fa730) at /export/home2/tmp/olav/mysql2/perconalive/sql/handler.h:3222
#9  0x0000000000f675b2 in mysql_inplace_alter_table (thd=0x7f04a4121cd0, table_list=0x7f04a4e79b10, table=0x7f04a41b16c0, altered_table=0x7f04a4dcf230, ha_alter_info=0x7f04b35fa730, inplace_supported=HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE, target_mdl_request=0x7f04b35fb0c0, alter_ctx=0x7f04b35f9c90) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_table.cc:7188
#10 0x0000000000f6c596 in mysql_alter_table (thd=0x7f04a4121cd0, new_db=0x7f04a4e7a098 "test", new_name=0x0, create_info=0x7f04b35fbb70, table_list=0x7f04a4e79b10, alter_info=0x7f04b35fbc60) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_table.cc:9250
#11 0x00000000016a3e41 in Sql_cmd_alter_table::execute (this=0x7f04a4e7b1a8, thd=0x7f04a4121cd0) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_alter.cc:326
#12 0x0000000000ee40e1 in mysql_execute_command (thd=0x7f04a4121cd0, first_level=true) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_parse.cc:4598
#13 0x0000000000ee61ab in mysql_parse (thd=0x7f04a4121cd0, parser_state=0x7f04b35fd5c0) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_parse.cc:5305
#14 0x0000000000edad4b in dispatch_command (thd=0x7f04a4121cd0, com_data=0x7f04b35fdd90, command=COM_QUERY) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_parse.cc:1251
#15 0x0000000000ed9a70 in do_command (thd=0x7f04a4121cd0) at /export/home2/tmp/olav/mysql2/perconalive/sql/sql_parse.cc:819
#16 0x00000000010005ef in handle_connection (arg=0x4277e90) at /export/home2/tmp/olav/mysql2/perconalive/sql/conn_handler/connection_handler_per_thread.cc:295

How to repeat:
Test case:

CREATE TABLE employees (
  data JSON
);

ALTER TABLE employees 
  ADD COLUMN name VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) VIRTUAL,
  ADD INDEX name_idx(name);

DROP TABLE employees;
[15 Oct 2015 11:46] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.9, 5.8.0 release, and here's the changelog entry:

A table-rebuilding ALTER TABLE operation that created an index on a
virtual column raised an assertion.
[18 Jun 2016 21:27] Omer Barnir 
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0