[MDEV-13679] crash when using SEQUENCE as column default Created: 2017-08-30  Updated: 2018-08-31  Resolved: 2017-12-22

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.3.1
Fix Version/s: 10.3.3

Type: Bug Priority: Major
Reporter: Axel Schwenke Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-10139 Support for SEQUENCE objects Closed
Relates
relates to MDEV-13016 Testing SEQUENCE object phase 2 Closed

 Description   

The following simple test case crashes the MariaDB server for the INSERT statement.

create sequence s1;
 
create table t1 (c1 bigint primary key default next value for s1, c2 int);
 
insert into t1 (c2) values (42);

MariaDB built from source (released 10.3.1 tarball). Same behaviour in release and debug build. Here is the stack trace from the debug build:

#0  0x00007fbaaeaad0c1 in __pthread_kill (threadid=<optimized out>, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
 
#1  0x000055646e7c4285 in my_write_core (sig=11) at /mnt/local/temp/mariadb-10.3.1/mysys/stacktrace.c:481
 
#2  0x000055646e045203 in handle_fatal_signal (sig=11) at /mnt/local/temp/mariadb-10.3.1/sql/signal_handler.cc:299
 
#3  <signal handler called>
 
#4  0x000055646e0d09c1 in Item_func_nextval::val_int (this=0x7fb9ec02e0d0) at /mnt/local/temp/mariadb-10.3.1/sql/item_func.cc:6780
 
#5  0x000055646e06ac48 in Item::save_int_in_field (this=0x7fb9ec02e0d0, field=0x7fb9ec02c230, no_conversions=false) at /mnt/local/temp/mariadb-10.3.1/sql/item.cc:6460
 
#6  0x000055646df5d6c4 in Type_handler_int_result::Item_save_in_field (this=0x55646f412130 <type_handler_longlong>, item=0x7fb9ec02e0d0, field=0x7fb9ec02c230, no_conversions=false) at /mnt/local/temp/mariadb-10.3.1/sql/sql_type.cc:2457
 
#7  0x000055646e06acf3 in Item::save_in_field (this=0x7fb9ec02e0d0, field=0x7fb9ec02c230, no_conversions=false) at /mnt/local/temp/mariadb-10.3.1/sql/item.cc:6470
 
#8  0x000055646dea4fd5 in TABLE::update_default_fields (this=0x7fb9ec027ba0, update_command=false, ignore_errors=false) at /mnt/local/temp/mariadb-10.3.1/sql/table.cc:7521
 
#9  0x000055646dd25355 in fill_record (thd=0x7fb9ec000b00, table_arg=0x7fb9ec027ba0, fields=..., values=..., ignore_errors=false, update=false) at /mnt/local/temp/mariadb-10.3.1/sql/sql_base.cc:7965
 
#10 0x000055646dd25813 in fill_record_n_invoke_before_triggers (thd=0x7fb9ec000b00, table=0x7fb9ec027ba0, fields=..., values=..., ignore_errors=false, event=TRG_EVENT_INSERT) at /mnt/local/temp/mariadb-10.3.1/sql/sql_base.cc:8098
 
#11 0x000055646dd65e4c in mysql_insert (thd=0x7fb9ec000b00, table_list=0x7fb9ec0135f0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /mnt/local/temp/mariadb-10.3.1/sql/sql_insert.cc:958
 
#12 0x000055646dd98c74 in mysql_execute_command (thd=0x7fb9ec000b00) at /mnt/local/temp/mariadb-10.3.1/sql/sql_parse.cc:4688
 
#13 0x000055646dda2fda in mysql_parse (thd=0x7fb9ec000b00, rawbuf=0x7fb9ec013508 "insert into t1 (c2) values (42)", length=31, parser_state=0x7fba94c46660, is_com_multi=false, is_next_command=false) at /mnt/local/temp/mariadb-10.3.1/sql/sql_parse.cc:7930
 
#14 0x000055646dd9119b in dispatch_command (command=COM_QUERY, thd=0x7fb9ec000b00, packet=0x7fb9ec00add1 "insert into t1 (c2) values (42)", packet_length=31, is_com_multi=false, is_next_command=false) at /mnt/local/temp/mariadb-10.3.1/sql/sql_parse.cc:1819
 
...



 Comments   
Comment by Elena Stepanova [ 2017-09-02 ]

A variation of presumably the same problem – using NEXTVAL as a value for a virtual column:

create sequence s;
 
create table t (i int, v int as (nextval(s)));
 
insert into t (i) values (1);
 
select * from t;
 
 
 
# Cleanup
 
drop table t;
 
drop sequence s;

10.3 016c35a7f2f4a

 
#3  <signal handler called>
 
#4  0x00005631f37c009f in Item_func_nextval::val_int (this=0x7fd05411faf0) at /data/src/10.3/sql/item_func.cc:6780
 
#5  0x00005631f3758b1e in Item::save_int_in_field (this=0x7fd05411faf0, field=0x7fd05411f918, no_conversions=false) at /data/src/10.3/sql/item.cc:6460
 
#6  0x00005631f3641bc4 in Type_handler_int_result::Item_save_in_field (this=0x5631f4b486b0 <type_handler_longlong>, item=0x7fd05411faf0, field=0x7fd05411f918, no_conversions=false) at /data/src/10.3/sql/sql_type.cc:2493
 
#7  0x00005631f3758bc9 in Item::save_in_field (this=0x7fd05411faf0, field=0x7fd05411f918, no_conversions=false) at /data/src/10.3/sql/item.cc:6470
 
#8  0x00005631f3586475 in TABLE::update_virtual_fields (this=0x7fd05411e460, h=0x7fd05411f088, update_mode=VCOL_UPDATE_FOR_READ) at /data/src/10.3/sql/table.cc:7436
 
#9  0x00005631f37387bf in handler::ha_rnd_next (this=0x7fd05411f088, buf=0x7fd05411f808 "\375\001") at /data/src/10.3/sql/handler.cc:2599
 
#10 0x00005631f373a041 in handler::read_first_row (this=0x7fd05411f088, buf=0x7fd05411f808 "\375\001", primary_key=64) at /data/src/10.3/sql/handler.cc:2830
 
#11 0x00005631f34fd6f7 in handler::ha_read_first_row (this=0x7fd05411f088, buf=0x7fd05411f808 "\375\001", primary_key=64) at /data/src/10.3/sql/sql_class.h:6004
 
#12 0x00005631f34e635e in join_read_system (tab=0x7fd054015b70) at /data/src/10.3/sql/sql_select.cc:19399
 
#13 0x00005631f34e5efb in join_read_const_table (thd=0x7fd054000b00, tab=0x7fd054015b70, pos=0x7fd054016128) at /data/src/10.3/sql/sql_select.cc:19295
 
#14 0x00005631f34be9d5 in make_join_statistics (join=0x7fd0540152f8, tables_list=..., keyuse_array=0x7fd0540155e8) at /data/src/10.3/sql/sql_select.cc:4063
 
#15 0x00005631f34b5d3c in JOIN::optimize_inner (this=0x7fd0540152f8) at /data/src/10.3/sql/sql_select.cc:1528
 
#16 0x00005631f34b46f4 in JOIN::optimize (this=0x7fd0540152f8) at /data/src/10.3/sql/sql_select.cc:1123
 
#17 0x00005631f34bd79e in mysql_select (thd=0x7fd054000b00, tables=0x7fd054014be8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd0540152d8, unit=0x7fd0540047c0, select_lex=0x7fd054004f00) at /data/src/10.3/sql/sql_select.cc:3723
 
#18 0x00005631f34b1dbc in handle_select (thd=0x7fd054000b00, lex=0x7fd0540046f8, result=0x7fd0540152d8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
 
#19 0x00005631f347d26e in execute_sqlcom_select (thd=0x7fd054000b00, all_tables=0x7fd054014be8) at /data/src/10.3/sql/sql_parse.cc:6466
 
#20 0x00005631f34737ef in mysql_execute_command (thd=0x7fd054000b00) at /data/src/10.3/sql/sql_parse.cc:3731
 
#21 0x00005631f3480bd4 in mysql_parse (thd=0x7fd054000b00, rawbuf=0x7fd054014a08 "select * from t", length=15, parser_state=0x7fd06b732610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7921
 
#22 0x00005631f346e32e in dispatch_command (command=COM_QUERY, thd=0x7fd054000b00, packet=0x7fd054149c61 "select * from t", packet_length=15, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1819
 
#23 0x00005631f346cd90 in do_command (thd=0x7fd054000b00) at /data/src/10.3/sql/sql_parse.cc:1370
 
#24 0x00005631f35c3646 in do_handle_one_connection (connect=0x5631f5b74270) at /data/src/10.3/sql/sql_connect.cc:1418
 
#25 0x00005631f35c33d3 in handle_one_connection (arg=0x5631f5b74270) at /data/src/10.3/sql/sql_connect.cc:1324
 
#26 0x00005631f3a3b1dc in pfs_spawn_thread (arg=0x5631f5c0d250) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#27 0x00007fd077402494 in start_thread (arg=0x7fd06b733700) at pthread_create.c:333
 
#28 0x00007fd07557a93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by Axel Schwenke [ 2017-09-04 ]

This is a supported feature in Oracle since 12c:
https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1

also in PostgreSQL, since at least 8.0:
https://www.postgresql.org/docs/8.0/static/ddl-default.html

Comment by Michael Widenius [ 2017-12-22 ]

Fixed and pushed to 10.3

This has always been a known limitation of the original SEQUENCE work and was scheduled before the first Beta of 10.3.

The main problems to overcome was:

  • Having another table to be opened for write as part of a normal table opening
  • Handling alter table with sequences required notable code changes as we now have to
    open additional tables if one adds a DEFAULT sequence during ALTER TABLE
Generated at Thu Feb 08 08:07:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.