[MDEV-25772] Virtual Column with CURDATE function cause suddenly entire MariaDB crash Created: 2021-05-25  Updated: 2022-01-06  Resolved: 2022-01-06

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.3.28, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Massimiliano Cuttini Assignee: Aleksey Midenkov
Resolution: Duplicate Votes: 0
Labels: crash, virtual_columns
Environment:

CentOS

Attachments: Text File mariadb-crash-log.txt     Text File mariadb-crash-log.txt    
Issue Links:
Duplicate
is duplicated by MDEV-24176 Server crashes after insert in the ta... Closed
is duplicated by MDEV-26619 CURDATE() functions results in lost c... Closed
Relates
relates to MDEV-26437 Server crashes in Item_args::walk_args Closed

 Description   

I see that MariaDb is restarting continuosly after I inserted the new column "is_available".
This is a virtual column on:

`date_availability_start` < curdate() and `date_availability_end` > curdate()

It crash with a 30% of possibility if I run just:

SELECT * FROM `products_gas` LIMIT 0, 25

So every 3 refresh the server is restarting.
I decided to drop the column and now it works back again.
I'll attach the log to this.

CREATE TABLE `products_gas` (
 
  `product_gas_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `provider_id` int(11) unsigned NOT NULL,
 
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `product_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `market_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `customer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.customer_type'))) VIRTUAL,
 
  `offer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.offer_type'))) VIRTUAL,
 
  `product_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
 
  `parsed_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `is_parsed` tinyint(1) GENERATED ALWAYS AS (`date_parsed` is not null) VIRTUAL,
 
  `is_available` tinyint(1) GENERATED ALWAYS AS (`date_availability_start` < curdate() and `date_availability_end` > curdate()) VIRTUAL,
 
  `is_deleted` tinyint(1) GENERATED ALWAYS AS (`date_deleted` is not null) VIRTUAL,
 
  `date_created` datetime NOT NULL,
 
  `date_parsed` datetime DEFAULT NULL,
 
  `date_availability_start` datetime DEFAULT NULL,
 
  `date_availability_end` datetime DEFAULT NULL,
 
  `date_deleted` datetime DEFAULT NULL,
 
  PRIMARY KEY (`product_gas_id`),
 
  KEY `provider_id` (`provider_id`),
 
  KEY `product_code` (`product_code`),
 
  KEY `market_type` (`market_type`),
 
  KEY `customer_type` (`customer_type`),
 
  KEY `offer_type` (`offer_type`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



 Comments   
Comment by Roland [ 2021-09-13 ]

I can reproduce this on 10.6.4 using the official docker image.

I'm trying to create a column automatically marking users in a forum invisible if they haven't logged in in a year and never made a post:

ALTER TABLE u_users ADD COLUMN `invisible` INT GENERATED ALWAYS AS (CASE WHEN (posts < 1 AND last_online < date_sub(curdate(), interval 1 year)) THEN 1 ELSE 0 END) VIRTUAL AFTER `last_consent`;

The column is created just fine and the database happily returns it as part of SELECT queries. Once I try to use it in a condition like a WHERE clause, however, the database reliably crashes.

mariadb-crash-log.txt

Comment by Alice Sherepa [ 2021-09-14 ]

Thank you!
I repeated on 10.2-10.6
This is probably some variation of MDEV-24176 (and MDEV-26437)

--source include/have_innodb.inc
 
 
 
CREATE TABLE `products_gas` (
 
  `product_gas_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `provider_id` int(11) unsigned NOT NULL,
 
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `product_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `market_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `customer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.customer_type'))) VIRTUAL,
 
  `offer_type` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`parsed_raw`,'$.offer_type'))) VIRTUAL,
 
  `product_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
 
  `parsed_raw` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `is_parsed` tinyint(1) GENERATED ALWAYS AS (`date_parsed` is not null) VIRTUAL,
 
  `is_available` tinyint(1) GENERATED ALWAYS AS (`date_availability_start` < curdate() and `date_availability_end` > curdate()) VIRTUAL,
 
  `is_deleted` tinyint(1) GENERATED ALWAYS AS (`date_deleted` is not null) VIRTUAL,
 
  `date_created` datetime NOT NULL,
 
  `date_parsed` datetime DEFAULT NULL,
 
  `date_availability_start` datetime DEFAULT NULL,
 
  `date_availability_end` datetime DEFAULT NULL,
 
  `date_deleted` datetime DEFAULT NULL,
 
  PRIMARY KEY (`product_gas_id`),
 
  KEY `provider_id` (`provider_id`),
 
  KEY `product_code` (`product_code`),
 
  KEY `market_type` (`market_type`),
 
  KEY `customer_type` (`customer_type`),
 
  KEY `offer_type` (`offer_type`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
 
 
insert into products_gas(`product_gas_id`,`provider_id`,`product_name`,`product_code`,`market_type`,`product_raw`,`parsed_raw`,`date_created`,`date_parsed`,`date_availability_start`,`date_availability_end`,`date_deleted`) values
 
(1,1,'1','1','1','1','1','2021-09-05 08:38:23','2021-09-05 08:38:23','2021-09-11 08:38:23','2021-09-11 08:38:23','2021-09-06 08:38:23'),(2,2,'2','2','2','2','2','2021-09-13 08:38:23','2021-09-06 08:38:23','2021-09-01 08:38:23','2021-09-04 08:38:23','2021-09-14 08:38:23');
 
 
 
SELECT * FROM `products_gas` where is_available=1;
 
SELECT * FROM `products_gas` LIMIT 0, 25;

on 10.3:

10.3 46cb16388a865d9561ba23401

 
Version: '10.3.32-MariaDB-debug-log'  
210914 10:53:06 [ERROR] mysqld got signal 11 ;
 
 
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x55e242502d6e]
 
sigaction.c:0(__restore_rt)[0x7f67f9f703c0]
 
sql/item_cmpfunc.cc:751(Arg_comparator::compare_datetime())[0x55e2425c379c]
 
sql/item_cmpfunc.h:102(Arg_comparator::compare())[0x55e24260369e]
 
sql/item_cmpfunc.cc:1803(Item_func_lt::val_int())[0x55e2425d0f81]
 
sql/sql_type.cc:3288(Type_handler_int_result::Item_val_bool(Item*) const)[0x55e24229f01a]
 
sql/item.h:1220(Item::val_bool())[0x55e241affcd4]
 
sql/item_cmpfunc.cc:5077(Item_cond_and::val_int())[0x55e2425efe72]
 
sql/item.cc:6890(Item::save_int_in_field(Field*, bool))[0x55e242583997]
 
sql/sql_type.cc:2593(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x55e24229caa6]
 
sql/item.cc:6900(Item::save_in_field(Field*, bool))[0x55e242583b7b]
 
sql/table.cc:8006(TABLE::update_virtual_fields(handler*, enum_vcol_update_mode))[0x55e2420a5fe8]
 
sql/handler.cc:2866(handler::ha_rnd_next(unsigned char*))[0x55e242518a6c]
 
sql/records.cc:485(rr_sequential(READ_RECORD*))[0x55e242913673]
 
sql/records.h:70(READ_RECORD::read_record())[0x55e241c0059e]
 
sql/sql_select.cc:20781(join_init_read_record(st_join_table*))[0x55e241ecd4bc]
 
sql/sql_select.cc:19842(sub_select(JOIN*, st_join_table*, bool))[0x55e241ec671c]
 
sql/sql_select.cc:19385(do_select(JOIN*, Procedure*))[0x55e241ec4a70]
 
sql/sql_select.cc:4142(JOIN::exec_inner())[0x55e241e57c75]
 
sql/sql_select.cc:3937(JOIN::exec())[0x55e241e555f2]
 
sql/sql_select.cc:4346(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55e241e590da]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e241e2f77b]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e241da0d9b]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55e241d8edd6]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e241daaaf8]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e241d819d5]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55e241d7e518]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55e24214e18f]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55e24214da49]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e2437794e9]
 
nptl/pthread_create.c:478(start_thread)[0x7f67f9f64609]
 
x86_64/clone.S:97(__GI___clone)[0x7f67f9e8b293]
 
 
 
Query (0x62b000000290): SELECT * FROM `products_gas` LIMIT 0, 25

on 10.2:

10.2 c7184c470eec1777cd6ea

 
#3  <signal handler called>
 
#4  0x0000556b91056393 in Item::val_temporal_packed (this=0x7ff9980150b8, f_type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item.h:1531
 
#5  0x0000556b9106074b in Arg_comparator::compare_temporal (this=0x7ff9980a6208, type=MYSQL_TYPE_DATETIME) at /10.2/src/sql/item_cmpfunc.cc:797
 
#6  0x0000556b90caeaff in Arg_comparator::compare_datetime (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:105
 
#7  0x0000556b91075556 in Arg_comparator::compare (this=0x7ff9980a6208) at /10.2/src/sql/item_cmpfunc.h:87
 
#8  0x0000556b91063c63 in Item_func_lt::val_int (this=0x7ff9980a6148) at /10.2/src/sql/item_cmpfunc.cc:1860
 
#9  0x0000556b91034e53 in Item::val_bool (this=0x7ff9980a6148) at /10.2/src/sql/item.cc:112
 
#10 0x0000556b9106e449 in Item_cond_and::val_int (this=0x7ff9980a6d58) at /10.2/src/sql/item_cmpfunc.cc:5092
 
#11 0x0000556b91046aaa in Item::save_in_field (this=0x7ff9980a6d58, field=0x7ff9980393c0, no_conversions=false) at /10.2/src/sql/item.cc:6429
 
#12 0x0000556b90eb36b9 in TABLE::update_virtual_fields (this=0x7ff998035300, h=0x7ff998035f08, update_mode=VCOL_UPDATE_FOR_READ) at /10.2/src/sql/table.cc:7793
 
#13 0x0000556b910265f8 in handler::ha_rnd_next (this=0x7ff998035f08, buf=0x7ff998036eb8 "3\374\001") at /10.2/src/sql/handler.cc:2675
 
#14 0x0000556b91199dd7 in rr_sequential (info=0x7ff998015a40) at /10.2/src/sql/records.cc:492
 
#15 0x0000556b90e0e137 in join_init_read_record (tab=0x7ff998015978) at /10.2/src/sql/sql_select.cc:19821
 
#16 0x0000556b90e0be76 in sub_select (join=0x7ff9980130f8, join_tab=0x7ff998015978, end_of_records=false) at /10.2/src/sql/sql_select.cc:18892
 
#17 0x0000556b90e0b43a in do_select (join=0x7ff9980130f8, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#18 0x0000556b90de4f4b in JOIN::exec_inner (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3651
 
#19 0x0000556b90de43f2 in JOIN::exec (this=0x7ff9980130f8) at /10.2/src/sql/sql_select.cc:3446
 
#20 0x0000556b90de55cc in mysql_select (thd=0x7ff998000d90, tables=0x7ff998012948, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff9980130d8, unit=0x7ff998004988, select_lex=0x7ff9980050d8) at /10.2/src/sql/sql_select.cc:3849
 
#21 0x0000556b90dd9720 in handle_select (thd=0x7ff998000d90, lex=0x7ff9980048c8, result=0x7ff9980130d8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
 
#22 0x0000556b90da3d86 in execute_sqlcom_select (thd=0x7ff998000d90, all_tables=0x7ff998012948) at /10.2/src/sql/sql_parse.cc:6271
 
#23 0x0000556b90d9a8fa in mysql_execute_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#24 0x0000556b90da7b42 in mysql_parse (thd=0x7ff998000d90, rawbuf=0x7ff998012708 "SELECT * FROM `products_gas` LIMIT 0, 25", length=40, parser_state=0x7ff9e301c560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#25 0x0000556b90d95d9d in dispatch_command (command=COM_QUERY, thd=0x7ff998000d90, packet=0x7ff998008b61 "", packet_length=40, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#26 0x0000556b90d94898 in do_command (thd=0x7ff998000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#27 0x0000556b90ef0661 in do_handle_one_connection (connect=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1336
 
#28 0x0000556b90ef03c6 in handle_one_connection (arg=0x556b94a37f10) at /10.2/src/sql/sql_connect.cc:1241
 
#29 0x0000556b91719ee8 in pfs_spawn_thread (arg=0x556b94a1b1d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#30 0x00007ff9ed1fb609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#31 0x00007ff9ecdd6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

shorter test:

CREATE TABLE t1 (d1 datetime , v_d1 tinyint(1)  AS (d1 < curdate()));
 
insert into t1 (d1) values ('2021-09-11 08:38:23'),('2021-09-01 08:38:23');
 
 
 
SELECT * FROM t1 where v_d1=1;
 
SELECT * FROM t1;

Comment by Alice Sherepa [ 2021-09-16 ]

from MDEV-26619

CREATE TABLE t1 ( c int , vc tinyint(4) AS (if(curdate() BETWEEN '2020-01-01' AND '2020-01-02',1,0))) ;
 
 
 
SELECT vc FROM t1;
 
SELECT vc FROM t1;


#3  <signal handler called>
 
#4  0x000055ea3d5a4e91 in Item_args::walk_args (this=0x7f25bc035320, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134
 
#5  0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035298, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420
 
#6  0x000055ea3d5a4ead in Item_args::walk_args (this=0x7f25bc0356d0, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4134
 
#7  0x000055ea3d5a52db in Item_func_or_sum::walk (this=0x7f25bc035648, processor=&virtual table offset 880, walk_subquery=true, arg=0x0) at /10.2/src/sql/item.h:4420
 
#8  0x000055ea3d71f2e3 in TABLE::mark_virtual_col (this=0x7f25bc175dc0, field=0x7f25bc176ac0) at /10.2/src/sql/table.cc:6807
 
#9  0x000055ea3d59aac4 in update_field_dependencies (thd=0x7f25bc000d90, field=0x7f25bc176ac0, table=0x7f25bc175dc0) at /10.2/src/sql/sql_base.cc:5312
 
#10 0x000055ea3d59b9f0 in find_field_in_table (thd=0x7f25bc000d90, table=0x7f25bc175dc0, name=0x7f25bc012798 "vc", length=2, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864) at /10.2/src/sql/sql_base.cc:5649
 
#11 0x000055ea3d59be24 in find_field_in_table_ref (thd=0x7f25bc000d90, table_list=0x7f25bc0128e8, name=0x7f25bc012798 "vc", length=2, item_name=0x7f25bc012798 "vc", db_name=0x0, table_name=0x0, ref=0x7f25bc0128a8, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0x7f25bc012864, register_tree_change=true, actual_table=0x7f2614437dc8) at /10.2/src/sql/sql_base.cc:5764
 
#12 0x000055ea3d59c978 in find_field_in_tables (thd=0x7f25bc000d90, item=0x7f25bc0127a0, first_table=0x7f25bc0128e8, last_table=0x0, ref=0x7f25bc0128a8, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /10.2/src/sql/sql_base.cc:6056
 
#13 0x000055ea3d8b2092 in Item_field::fix_fields (this=0x7f25bc0127a0, thd=0x7f25bc000d90, reference=0x7f25bc0128a8) at /10.2/src/sql/item.cc:5463
 
#14 0x000055ea3d59f84c in setup_fields (thd=0x7f25bc000d90, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f25bc013328, pre_fix=0x7f25bc005218, allow_sum_func=true) at /10.2/src/sql/sql_base.cc:7275
 
#15 0x000055ea3d648a69 in JOIN::prepare (this=0x7f25bc013008, tables_init=0x7f25bc0128e8, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f25bc0050d8, unit_arg=0x7f25bc004988) at /10.2/src/sql/sql_select.cc:807
 
#16 0x000055ea3d65351a in mysql_select (thd=0x7f25bc000d90, tables=0x7f25bc0128e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f25bc012fe8, unit=0x7f25bc004988, select_lex=0x7f25bc0050d8) at /10.2/src/sql/sql_select.cc:3827
 
#17 0x000055ea3d647720 in handle_select (thd=0x7f25bc000d90, lex=0x7f25bc0048c8, result=0x7f25bc012fe8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
 
#18 0x000055ea3d611d86 in execute_sqlcom_select (thd=0x7f25bc000d90, all_tables=0x7f25bc0128e8) at /10.2/src/sql/sql_parse.cc:6271
 
#19 0x000055ea3d6088fa in mysql_execute_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#20 0x000055ea3d615b42 in mysql_parse (thd=0x7f25bc000d90, rawbuf=0x7f25bc012708 "SELECT vc FROM t1", length=17, parser_state=0x7f2614439560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#21 0x000055ea3d603d9d in dispatch_command (command=COM_QUERY, thd=0x7f25bc000d90, packet=0x7f25bc008b61 "SELECT vc FROM t1", packet_length=17, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#22 0x000055ea3d602898 in do_command (thd=0x7f25bc000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#23 0x000055ea3d75e661 in do_handle_one_connection (connect=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1336
 
#24 0x000055ea3d75e3c6 in handle_one_connection (arg=0x55ea40fb2b80) at /10.2/src/sql/sql_connect.cc:1241
 
#25 0x000055ea3df87f18 in pfs_spawn_thread (arg=0x55ea40f95e40) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#26 0x00007f2619db4609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#27 0x00007f261998f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Nikita Malyavin [ 2021-10-01 ]

alice Thanks for the tests!

Comment by Nikita Malyavin [ 2021-10-01 ]

I have checked that the issue is not reproduced with midenok's patch https://github.com/MariaDB/server/commit/c91cc1697ea5325880987750f605caf0051690b7

Comment by Aleksey Midenkov [ 2021-10-01 ]

nikitamalyavin I guess you have to reassign such duplicates to the current assignee of the original fix.

Generated at Thu Feb 08 09:40:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.