Likely not a bug for Marko to fix, but assigned to Marko first to see why InnoDB produces "errno: 168 "Unknown (generic) error from engine" (differs from other engines).
Roel, if you had read the error or warning messages, you would have noticed the reason why InnoDB returns an error. With the following test, I am getting the following output:
--source include/have_innodb.inc
|
--error ER_CANT_CREATE_TABLE
|
CREATE TABLE t5 ENGINE=InnoDB SELECT NULL UNION SELECT NULL; |
SHOW WARNINGS;
|
|
10.5 193725b81ed813d0318c1fa82de284c337246d9e |
CREATE TABLE t5 ENGINE=InnoDB SELECT NULL UNION SELECT NULL;
|
ERROR HY000: Can't create table `test`.`t5` (errno: 168 "Unknown (generic) error from engine")
|
SHOW WARNINGS;
|
Level Code Message
|
Warning 1005 Error creating table 'test/t5' with column 'NULL'. Please check its column type and try to re-create the table with an appropriate column type.
|
Error 1005 Can't create table `test`.`t5` (errno: 168 "Unknown (generic) error from engine")
|
Warning 1030 Got error 168 "Unknown (generic) error from engine" from storage engine InnoDB
|
I do not think that InnoDB ever supported the NULL data type. What is its practical use case?
By the way, with that old revision of 10.5, the test with ENGINE=MEMORY would fail like this:
CREATE TABLE t5 ENGINE=MEMORY SELECT NULL UNION SELECT NULL; |
ALTER TABLE t5 ADD INDEX (`PRIMARY`); |
|
10.5 193725b81ed813d0318c1fa82de284c337246d9e |
mysqltest: At line 2: query 'ALTER TABLE t5 ADD INDEX (`PRIMARY`)' failed: 1072: Key column 'PRIMARY' doesn't exist in table
|
So, it appears that the bug did not exist in 10.5.1.
Side note: I believe that PRIMARY is supposed to be a reserved index name, only allowed for the primary key. Indeed, with that same revision of the 10.5 server, the following does work (silently creating an index PRIMARY_2 for the column name PRIMARY):
CREATE TABLE t5 (`PRIMARY` INT, `SECONDARY` INT) ENGINE=MEMORY; |
ALTER TABLE t5 ADD INDEX (`PRIMARY`); |
ALTER TABLE t5 ADD PRIMARY KEY (`SECONDARY`); |
SHOW CREATE TABLE t5; |
DROP TABLE t5; |
I am only getting an error but no crash with the mariadb-10.5.2, mariadb-10.5.4, mariadb-10.5.8 release tags. I do not know the code well enough to say when such regression might have been introduced. I used the following .test file:
CREATE TABLE t5 ENGINE=MEMORY SELECT NULL UNION SELECT NULL; |
ALTER TABLE t5 ADD INDEX (`PRIMARY`); |
DROP TABLE t5; |
Until the 10.5.8 release, the test would fail as follows:
|
10.5 |
mysqltest: At line 2: query 'ALTER TABLE t5 ADD INDEX (`PRIMARY`)' failed: 1072: Key column 'PRIMARY' doesn't exist in table
|
Roel, there is no release tag mariadb-10.5.9 yet. Recent development versions of the 10.5 currently identify themselves as 10.5.9, but that version has not been released yet. With the currently latest 10.5 commit, I do see the crash:
|
10.5 139c85aafd4e4938f95843d44a455265a49b572e |
mysqltest: At line 2: query 'ALTER TABLE t5 ADD INDEX (`PRIMARY`)' failed: 2013: Lost connection to MySQL server during query
|
…
|
mariadbd: /mariadb/10.5m/sql/field.h:1155: const uchar* Field::ptr_in_record(const uchar*) const: Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' failed.
|
I am setting this to Blocker status, because the crash was introduced after the most recent release. Roel did not identify the exact commit where this was broken (or any particular commit in the 10.5 series where this is repeatable), so I spent some extra effort to identify the culprit. Actually, it could have been trivially answered by git blame in this case. I verified that the following commit added the failing assertion:
commit 30894fe9a9024d4dfe85f7fc93cf702040a9ef67
|
Author: Nikita Malyavin <nikitamalyavin@gmail.com>
|
Date: Tue Sep 22 20:17:02 2020 +1000
|
|
|
Add DBUG_ASSERT in Field::ptr_in_record
|
|
1. Subtracting table->record[0] from record is UB (non-contiguous buffers)
|
2. It is very popular to use move_field_offset, which changes Field::ptr,
|
but leaves table->record[0] unchanged. This makes a ptr_in_record result
|
incorrect, since it relies on table->record[0] value.
|
The check ensures the result is within the queried record boundaries.
|
It might not actually be a culprit, but a messenger that notices some wrongdoing. Possibly, that assertion will have to be adjusted for the NULL data type.
marko Thank you! I was unaware that a single command can produce multiple warnings/errors at once. Noted!
|
10.6.0 9118fd360a3da0bba521caf2a35c424968235ac4 (Debug) |
10.6.0>CREATE TABLE t5 ENGINE=InnoDB SELECT NULL UNION SELECT NULL;
|
ERROR 1005 (HY000): Can't create table `test`.`t5` (errno: 168 "Unknown (generic) error from engine")
|
10.6.0>SHOW WARNINGS\G
|
*************************** 1. row ***************************
|
Level: Warning
|
Code: 1005
|
Message: Error creating table 'test/t5' with column 'NULL'. Please check its column type and try to re-create the table with an appropriate column type.
|
*************************** 2. row ***************************
|
Level: Error
|
Code: 1005
|
Message: Can't create table `test`.`t5` (errno: 168 "Unknown (generic) error from engine")
|
*************************** 3. row ***************************
|
Level: Warning
|
Code: 1030
|
Message: Got error 168 "Unknown (generic) error from engine" from storage engine InnoDB
|
3 rows in set (0.000 sec)
|
nikitamalyavin Marko mentioned this may be a blocker?
Looks like the blocker flag was previously missed. Set now. Also, this new testcase and different stack.
SET SESSION storage_engine=MEMORY; |
CREATE TABLE t SELECT NULL UNION SELECT NULL; |
CREATE TABLE t2 AS SELECT * FROM t; |
Leads to:
|
10.6.1 f8665314d4ba190679001b81bb7d9fd7a38fc0f6 (Debug) |
mysqld: /test/10.6_dbg/sql/field.h:1155: const uchar* Field::ptr_in_record(const uchar*) const: Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' failed.
|
|
10.6.1 f8665314d4ba190679001b81bb7d9fd7a38fc0f6 (Debug) |
Core was generated by `/test/MD100521-mariadb-10.6.1-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
|
Program terminated with signal SIGABRT, Aborted.
|
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
[Current thread is 1 (Thread 0x1532001ae700 (LWP 1656158))]
|
(gdb) bt
|
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#1 0x000015321523d859 in __GI_abort () at abort.c:79
|
#2 0x000015321523d729 in __assert_fail_base (fmt=0x1532153d3588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55d92be8fa98 "l_offset >= 0 && table->s->rec_buff_length - l_offset > 0", file=0x55d92be4bc5c "/test/10.6_dbg/sql/field.h", line=1155, function=<optimized out>) at assert.c:92
|
#3 0x000015321524ef36 in __GI___assert_fail (assertion=assertion@entry=0x55d92be8fa98 "l_offset >= 0 && table->s->rec_buff_length - l_offset > 0", file=file@entry=0x55d92be4bc5c "/test/10.6_dbg/sql/field.h", line=line@entry=1155, function=function@entry=0x55d92be8fa60 "const uchar* Field::ptr_in_record(const uchar*) const") at assert.c:101
|
#4 0x000055d92b47c5ba in Field::ptr_in_record (record=<optimized out>, this=0x1531bc044868) at /test/10.6_dbg/sql/field.h:1155
|
#5 Column_definition::Column_definition (this=0x1531bc016910, thd=0x1531bc000db8, old_field=<optimized out>, orig_field=0x1531bc044868) at /test/10.6_dbg/sql/field.cc:10827
|
#6 0x000055d92b189c9d in Create_field::Create_field (orig_field=0x1531bc044868, old_field=0x1531bc016830, thd=0x1531bc000db8, this=0x1531bc016910) at /test/10.6_dbg/sql/field.h:5638
|
#7 select_create::create_table_from_items (this=this@entry=0x1531bc015f90, thd=0x1531bc000db8, items=items@entry=0x1532001ac770, lock=lock@entry=0x1532001ac768, hooks=hooks@entry=0x1532001ac7b0) at /test/10.6_dbg/sql/sql_insert.cc:4422
|
#8 0x000055d92b18a40f in select_create::prepare (this=0x1531bc015f90, _values=<optimized out>, u=<optimized out>) at /test/10.6_dbg/sql/sql_insert.cc:4664
|
#9 0x000055d92b23d146 in JOIN::prepare (this=this@entry=0x1531bc016090, tables_init=tables_init@entry=0x1531bc014de8, conds_init=conds_init@entry=0x0, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=skip_order_by@entry=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x1531bc0147c8, unit_arg=0x1531bc005120) at /test/10.6_dbg/sql/my_json_writer.h:367
|
#10 0x000055d92b254f06 in mysql_select (thd=thd@entry=0x1531bc000db8, tables=0x1531bc014de8, fields=@0x1531bc014920: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1531bc014da0, last = 0x1531bc014da0, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2201171004160, result=0x1531bc015f90, unit=0x1531bc005120, select_lex=0x1531bc0147c8) at /test/10.6_dbg/sql/sql_select.cc:4841
|
#11 0x000055d92b25523e in handle_select (thd=thd@entry=0x1531bc000db8, lex=lex@entry=0x1531bc005058, result=result@entry=0x1531bc015f90, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.6_dbg/sql/sql_select.cc:446
|
#12 0x000055d92b2acb62 in Sql_cmd_create_table_like::execute (this=<optimized out>, thd=0x1531bc000db8) at /test/10.6_dbg/sql/sql_table.cc:12255
|
#13 0x000055d92b1daa4b in mysql_execute_command (thd=thd@entry=0x1531bc000db8) at /test/10.6_dbg/sql/sql_parse.cc:5986
|
#14 0x000055d92b1c102b in mysql_parse (thd=thd@entry=0x1531bc000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1532001ad410) at /test/10.6_dbg/sql/sql_parse.cc:8018
|
#15 0x000055d92b1cfe5d in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x1531bc000db8, packet=packet@entry=0x1531bc00b799 "CREATE TABLE t2 AS SELECT * FROM t", packet_length=packet_length@entry=34, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1333
|
#16 0x000055d92b1d3250 in do_command (thd=0x1531bc000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
|
#17 0x000055d92b32ddec in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55d92f6a3ff8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
|
#18 0x000055d92b32e3f1 in handle_one_connection (arg=arg@entry=0x55d92f6a3ff8) at /test/10.6_dbg/sql/sql_connect.cc:1312
|
#19 0x000055d92b7e0cee in pfs_spawn_thread (arg=0x55d92f58d848) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
|
#20 0x000015321574b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#21 0x000015321533a293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Bug confirmed present in:
MariaDB: 10.5.10 (dbg), 10.6.1 (dbg)
Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.38 (dbg), 10.2.38 (opt), 10.3.29 (dbg), 10.3.29 (opt), 10.4.19 (dbg), 10.4.19 (opt), 10.5.10 (opt), 10.6.1 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.34 (dbg), 5.7.34 (opt), 8.0.24 (dbg), 8.0.24 (opt)
The last testcase above does not produce any warnings nor errors (except for the notion of '@@storage_engine' being deprecated in the future on 10.6).
Nothing that urgent. The assertion begins failing in the new versions since it was added just recently.
The reason I've added it is a proof that `move_field_offsets` concept is broken.
marko FYI.
The Field_null class has special treatment on move_field_offset – it does nothing. Therefore, field->ptr is left lying in share->record[0] (default field value).
So Marko was right, something around NULL should be changed
The noop move_field_offset was Added by serg in 8de6199b164fe6616db056465b75b318bd5f8a4a ten years ago:
lp:743017 Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro rewrite get_innobase_type_from_mysql_type() to use types as reported by the Field objects, instead of relying on ad-hoc assumptions.
I have checked the tests altered by that commit with move_field_offset commented out – and nothing failed.
I think, Field_null::move_field_offset should be just removed
marko, Roel didn't anybody notice that following fails:
SET @@default_storage_engine=MEMORY; |
CREATE TABLE t1 SELECT NULL UNION SELECT NULL; |
SHOW CREATE TABLE t1; |
ALTER TABLE t1 ADD INDEX (`NULL`); |
with completely different stack trace:
#0 0x000055e2df0a1397 in hp_rec_hashnr (keydef=0x55e2e289b8c8, rec=0x55e2e289acf8 "\375\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245@\255\211\342\342U") at ../storage/heap/hp_hash.c:303
|
#1 0x000055e2df0a6479 in hp_write_key (info=0x55e2e2893518, keyinfo=0x55e2e289b8c8, record=0x55e2e289acf8 "\375\245\245"...) at ../storage/heap/hp_write.c:349
|
#2 0x000055e2df0a5960 in heap_write (info=0x55e2e2893518, record=0x55e2e289acf8 "\375\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245@\255\211\342\342U") at ../storage/heap/hp_write.c:52
|
#3 0x000055e2df09d58c in ha_heap::write_row (this=0x55e2e27a48e0, buf=0x55e2e289acf8 "\375\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245@\255\211\342\342U") at ../storage/heap/ha_heap.cc:239
|
#4 0x000055e2debc7c85 in handler::ha_write_row (this=0x55e2e27a48e0, buf=0x55e2e289acf8 "\375\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245@\255\211\342\342U") at ../sql/handler.cc:7154
|
#5 0x000055e2de8e3fd3 in copy_data_between_tables (thd=0x55e2e2693e78, from=0x55e2e28af428, to=0x55e2e289b0d8, create=..., ignore=false, order_num=0, order=0x0, copied=0x7ff29c0486d8, deleted=0x7ff29c0486d0, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7ff29c0495b0) at ../sql/sql_table.cc:11520
|
#6 0x000055e2de8d99df in mysql_alter_table (thd=0x55e2e2693e78, new_db=0x55e2e26988d8, new_name=0x55e2e2698cf8, create_info=0x7ff29c04a928, table_list=0x55e2e26a5a88, alter_info=0x7ff29c04a858, order_num=0, order=0x0, ignore=false, if_exists=false) at ../sql/sql_table.cc:10870
|
#7 0x000055e2de9abd42 in Sql_cmd_alter_table::execute (this=0x55e2e26a6248, thd=0x55e2e2693e78) at ../sql/sql_alter.cc:539
|
#8 0x000055e2de7b9b88 in mysql_execute_command (thd=0x55e2e2693e78) at ../sql/sql_parse.cc:6055
|
#9 0x000055e2de7a5ae0 in mysql_parse (thd=0x55e2e2693e78, rawbuf=0x55e2e26a59a0 "ALTER TABLE t1 ADD INDEX (`NULL`)", length=33, parser_state=0x7ff29c04d4b0, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:8099
|
#10 0x000055e2de7a1c86 in dispatch_command (command=COM_QUERY, thd=0x55e2e2693e78, packet=0x55e2e27a0699 "ALTER TABLE t1 ADD INDEX (`NULL`)", packet_length=33, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:1891
|
#11 0x000055e2de7a6b15 in do_command (thd=0x55e2e2693e78) at ../sql/sql_parse.cc:1370
|
#12 0x000055e2de99f30e in do_handle_one_connection (connect=0x55e2e2648048, put_in_cache=true) at ../sql/sql_connect.cc:1410
|
#13 0x000055e2de99f04a in handle_one_connection (arg=0x55e2e265cfa8) at ../sql/sql_connect.cc:1312
|
#14 0x000055e2defc0549 in pfs_spawn_thread (arg=0x55e2e25aa4f8) at ../storage/perfschema/pfs.cc:2201
|
#15 0x00007ff2a246c299 in start_thread () from /usr/lib/libpthread.so.0
|
#16 0x00007ff2a2195053 in clone () from /usr/lib/libc.so.6
|
Patch for review is https://github.com/MariaDB/server/commit/ce289840131b670727d979685fd2650ffa11c50b
nikitamalyavin Can you please also add the crashing testcases to the patch? Thank you
Thanks for the reminder, Roel.
Tests included: https://github.com/MariaDB/server/commit/44bd3bba055e75cf54f1e9f4e6bf0995cdc4f409