[MDEV-24160] date_format() mixed with if() in a computed column causes server segfault Created: 2020-11-07  Updated: 2020-11-11  Resolved: 2020-11-11

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.4.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jay Fenlason (Inactive) Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Fedora 32

Issue Links:
Duplicate
duplicates MDEV-24176 Server crashes after insert in the ta... Closed

 Description   

I have a table containing two date fields, a text field and a varchar(128) field. I did a

alter table event add full_event_name text as (concat(
 
  date_format(event_starts_on, '%Y-%m-%d '),                         
  if(event_starts_on <> event_ends_on, date_format(event_ends_on, 'to %Y-%m-%d '), ''),
 
  event_location_id, ' ',                                                              
  event_name));

After I did that, accessing the table (say, by doing a mysqldump of its database) causes the server to segfault. If I remove the date_format() calls or the if(), the server doesn't segfault.

 Comments   
Comment by Daniel Black [ 2020-11-07 ]

for completeness can you include the `show create table event` output before the table was altered.

Comment by Jay Fenlason (Inactive) [ 2020-11-07 ]

CREATE TABLE `event` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`event_name` varchar(128) NOT NULL,
`event_starts_on` date NOT NULL,
`event_ends_on` date NOT NULL,
`event_base_cost` int(10) unsigned NOT NULL CHECK (`event_base_cost` >= 0),
`event_ceus` int(10) unsigned DEFAULT NULL CHECK (`event_ceus` >= 0),
`event_notes` longtext DEFAULT NULL,
`event_type_id` int(11) NOT NULL,
`event_location_id` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `event_name` (`event_name`),
KEY `event_event_type_id_49279ad6_fk_eventtype_id` (`event_type_id`),
KEY `event_event_location_id_b7eff272_fk_event` (`event_location_id`),
CONSTRAINT `event_event_location_id_b7eff272_fk_event` FOREIGN KEY (`event_location_id`) REFERENCES `eventlocation` (`event_location`),
CONSTRAINT `event_event_type_id_49279ad6_fk_eventtype_id` FOREIGN KEY (`event_type_id`) REFERENCES `eventtype` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=454 DEFAULT CHARSET=latin1

Comment by Daniel Black [ 2020-11-07 ]

I'm having trouble reproducing this. Maybe its got some element of the data or FK tables related. Can you include the stack trace and a set of data that can reproduce this?

MariaDB [test]> flush tables;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> alter table event add full_event_name text as (concat(    date_format(event_starts_on, '%Y-%m-%d '),
 
              if(event_starts_on <> event_ends_on, date_format(event_ends_on, 'to %Y-%m-%d '), ''),    event_location_id, ' ',
 
                                                             event_name));
 
Query OK, 0 rows affected (0.004 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> flush tables;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select * from event;
 
+-----+------------+-----------------+---------------+-----------------+------------+---------------+---------------+-----------------------------+----------------------------------------------------------------+
 
| id  | event_name | event_starts_on | event_ends_on | event_base_cost | event_ceus | event_notes   | event_type_id | event_location_id           | full_event_name                                                |
 
+-----+------------+-----------------+---------------+-----------------+------------+---------------+---------------+-----------------------------+----------------------------------------------------------------+
 
| 454 | sunday     | 2020-11-08      | 2020-11-08    |              44 |         33 | oh what a day |            42 | somewhere under the rainbow | 2020-11-08 somewhere under the rainbow sunday                  |
 
| 455 | stresstime | 2020-11-04      | 2020-11-12    |              44 |         33 | yaya          |            42 | somewhere under the bridge  | 2020-11-04 to 2020-11-12 somewhere under the bridge stresstime |
 
+-----+------------+-----------------+---------------+-----------------+------------+---------------+---------------+-----------------------------+----------------------------------------------------------------+
 
2 rows in set (0.002 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.4.14-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> show create table event\G
 
*************************** 1. row ***************************
 
       Table: event
 
Create Table: CREATE TABLE `event` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `event_name` varchar(128) NOT NULL,
 
  `event_starts_on` date NOT NULL,
 
  `event_ends_on` date NOT NULL,
 
  `event_base_cost` int(10) unsigned NOT NULL CHECK (`event_base_cost` >= 0),
 
  `event_ceus` int(10) unsigned DEFAULT NULL CHECK (`event_ceus` >= 0),
 
  `event_notes` longtext DEFAULT NULL,
 
  `event_type_id` int(11) NOT NULL,
 
  `event_location_id` varchar(128) NOT NULL,
 
  `full_event_name` text GENERATED ALWAYS AS (concat(date_format(`event_starts_on`,'%Y-%m-%d '),if(`event_starts_on` <> `event_ends_on`,date_format(`event_ends_on`,'to %Y-%m-%d '),''),`event_location_id`,' ',`event_name`)) VIRTUAL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `event_name` (`event_name`),
 
  KEY `event_event_type_id_49279ad6_fk_eventtype_id` (`event_type_id`),
 
  KEY `event_event_location_id_b7eff272_fk_event` (`event_location_id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=456 DEFAULT CHARSET=latin1
 
1 row in set (0.000 sec)

Comment by Alice Sherepa [ 2020-11-09 ]

Please add error log and your .cnf file(s)

Comment by Alice Sherepa [ 2020-11-09 ]

dajt, Could you please take a look at MDEV-24176-does it look the same as yours? - I got it, while tried to reproduce this bug, but reported it separately - in case it is different.

Comment by Jay Fenlason (Inactive) [ 2020-11-11 ]

@Alice It looks to me like MDEV-24176 is probably the same bug, although I don't think I made any changes to the event table data when I found it. Did you notice that the second "INSERT INTO t1..." would throw an error if it didn't segfault first, because there are now three columns in the table and you only provided two values? But the server segfaults before it gets that far. And if you remove the mysqldump between the first insert and second, it doesn't segfault.

You've got a nice elegant reproducer there. Should we close this ticket and track the bug under MDEV-24176?

Comment by Alice Sherepa [ 2020-11-11 ]

@Jay, Thank you, I am glad to hear it!
Let's track it there, but in case you will get a problem even after the fix of MDEV-24176- please comment here and we will investigate it further.

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