[MDEV-6789] segfault in Item_func_from_unixtime::get_date on updating table with virtual columns - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.3.12, 5.5.39, 10.0.14
Fix Version/s: 10.0.15, 5.5.41
Component/s: Virtual Columns
Labels:
- virtual_columns
Environment:
Debian Linux with kernel 3.14-1-amd64

Description

Hey,
since i added two virtual columns to a table querying that table cause some random segfaults.

The table looks like that:

CREATE TABLE

CREATE TABLE `tl_astars_reservation_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',
  `pid` int(10) unsigned NOT NULL DEFAULT '0',
  `resid` int(10) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `time_hr` int(10) unsigned AS (`time`/3600) VIRTUAL,
  `repeatEach` varchar(64) NOT NULL DEFAULT '',
  `recurrDayWise` varchar(64) NOT NULL DEFAULT '',
  `duration` int(10) unsigned NOT NULL DEFAULT '0',
  `seriesBegin` int(10) unsigned NOT NULL DEFAULT '0',
  `seriesBeginWeekdayHr` varchar(50) AS (DATE_FORMAT(FROM_UNIXTIME(`seriesBegin`), '%W')) VIRTUAL,
  `seriesEnd` int(10) unsigned NOT NULL DEFAULT '0',
  `onhold` char(1) NOT NULL DEFAULT '',
  `locked` char(1) NOT NULL DEFAULT '',
  `season` varchar(64) NOT NULL DEFAULT '',
  `specialprice` decimal(20,4) DEFAULT NULL,
  `paid` char(1) NOT NULL DEFAULT '',
  `weeks` varchar(64) NOT NULL DEFAULT '',
  `day` varchar(7) NOT NULL DEFAULT '0',
  `downpayment` decimal(20,4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  KEY `resid` (`resid`),
  KEY `locked` (`locked`)
) ENGINE=MyISAM AUTO_INCREMENT=9885 DEFAULT CHARSET=utf8

The new colums are the following ones:

`time_hr` int(10) unsigned AS (`time`/3600) VIRTUAL,
`seriesBeginWeekdayHr` varchar(50) AS (DATE_FORMAT(FROM_UNIXTIME(`seriesBegin`), '%W')) VIRTUAL,

You can find the entire stacktrace as an attachment. The IRC user `tanj` recommended to try the following command to fix the issue:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

But this did not solve the problem at all.
If you need more data, please contact me. I am happy to provide them.

Greetings
Leo

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

mariadb.error.stacktrace
7 kB
2014-09-25 18:07

Issue Links

relates to

MDEV-7113 difference between check_vcol_func_processor and check_partition_func_processor

Stalled

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-09-26 02:25 - edited

Test Case


--connect (con1,localhost,root,,test)
CREATE TABLE t1 (
   a INT(10) UNSIGNED NOT NULL DEFAULT '0',
   vcol VARCHAR(50) AS (DATE_FORMAT(FROM_UNIXTIME(a), '%W')) VIRTUAL
) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (1411416000),(1411417000);
--disconnect con1

--connection default
UPDATE t1 SET vcol = 1730019328;

Causes a crash or valgrind warnings (if it doesn't crash for you, try valgrind).

Crash on 10.0 (stack trace from revno 4418):

#3  <signal handler called>
#4  0x0000000000927c43 in Item_func_from_unixtime::get_date (this=0x7f865685e288, ltime=0x7f8660995290, fuzzy_date=0) at 10.0/sql/item_timefunc.cc:2000
#5  0x000000000087993a in Item::get_date_with_conversion (this=0x7f865685e288, ltime=0x7f8660995290, fuzzydate=0) at 10.0/sql/item.cc:252
#6  0x000000000092bbff in Item_func::get_arg0_date (this=0x7f865685e3e8, ltime=0x7f8660995290, fuzzy_date=0) at 10.0/sql/item_func.h:162
#7  0x00000000009279dc in Item_func_date_format::val_str (this=0x7f865685e3e8, str=0x7f865685e400) at 10.0/sql/item_timefunc.cc:1939
#8  0x0000000000888726 in Item::save_in_field (this=0x7f865685e3e8, field=0x7f865685e7e0, no_conversions=false) at 10.0/sql/item.cc:5985
#9  0x000000000075f323 in update_virtual_fields (thd=0x7f865972f070, table=0x7f86568a7c70, vcol_update_mode=VCOL_UPDATE_FOR_READ) at 10.0/sql/table.cc:6692
#10 0x000000000099b89f in rr_sequential (info=0x7f86609957b0) at 10.0/sql/records.cc:478
#11 0x0000000000743d8f in mysql_update (thd=0x7f865972f070, table_list=0x7f8656c14170, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f8660996288, updated_return=0x7f8660996280) at 10.0/sql/sql_update.cc:727
#12 0x000000000067d119 in mysql_execute_command (thd=0x7f865972f070) at 10.0/sql/sql_parse.cc:3303
#13 0x0000000000685837 in mysql_parse (thd=0x7f865972f070, rawbuf=0x7f8656c14088 "UPDATE t1 SET vcol = 1730019328", length=31, parser_state=0x7f8660996630) at 10.0/sql/sql_parse.cc:6415
#14 0x000000000067864c in dispatch_command (command=COM_QUERY, thd=0x7f865972f070, packet=0x7f865a7f6071 "UPDATE t1 SET vcol = 1730019328", packet_length=31) at 10.0/sql/sql_parse.cc:1307
#15 0x00000000006779f1 in do_command (thd=0x7f865972f070) at 10.0/sql/sql_parse.cc:1004
#16 0x0000000000794226 in do_handle_one_connection (thd_arg=0x7f865972f070) at 10.0/sql/sql_connect.cc:1379
#17 0x0000000000793f79 in handle_one_connection (arg=0x7f865972f070) at 10.0/sql/sql_connect.cc:1293
#18 0x0000000000cc9ece in pfs_spawn_thread (arg=0x7f8658b67bf0) at 10.0/storage/perfschema/pfs.cc:1860
#19 0x00007f8660582b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#20 0x00007f865ea7a20d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Show

Elena Stepanova added a comment - 2014-09-26 02:25 - edited Test Case --connect (con1,localhost,root,,test) CREATE TABLE t1 ( a INT(10) UNSIGNED NOT NULL DEFAULT '0', vcol VARCHAR (50) AS (DATE_FORMAT(FROM_UNIXTIME(a), '%W')) VIRTUAL ) ENGINE=MyISAM; INSERT INTO t1 (a) VALUES (1411416000),(1411417000); --disconnect con1 --connection default UPDATE t1 SET vcol = 1730019328; Causes a crash or valgrind warnings (if it doesn't crash for you, try valgrind). Crash on 10.0 (stack trace from revno 4418): #3 <signal handler called> #4 0x0000000000927c43 in Item_func_from_unixtime::get_date (this=0x7f865685e288, ltime=0x7f8660995290, fuzzy_date=0) at 10.0/sql/item_timefunc.cc:2000 #5 0x000000000087993a in Item::get_date_with_conversion (this=0x7f865685e288, ltime=0x7f8660995290, fuzzydate=0) at 10.0/sql/item.cc:252 #6 0x000000000092bbff in Item_func::get_arg0_date (this=0x7f865685e3e8, ltime=0x7f8660995290, fuzzy_date=0) at 10.0/sql/item_func.h:162 #7 0x00000000009279dc in Item_func_date_format::val_str (this=0x7f865685e3e8, str=0x7f865685e400) at 10.0/sql/item_timefunc.cc:1939 #8 0x0000000000888726 in Item::save_in_field (this=0x7f865685e3e8, field=0x7f865685e7e0, no_conversions=false) at 10.0/sql/item.cc:5985 #9 0x000000000075f323 in update_virtual_fields (thd=0x7f865972f070, table=0x7f86568a7c70, vcol_update_mode=VCOL_UPDATE_FOR_READ) at 10.0/sql/table.cc:6692 #10 0x000000000099b89f in rr_sequential (info=0x7f86609957b0) at 10.0/sql/records.cc:478 #11 0x0000000000743d8f in mysql_update (thd=0x7f865972f070, table_list=0x7f8656c14170, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f8660996288, updated_return=0x7f8660996280) at 10.0/sql/sql_update.cc:727 #12 0x000000000067d119 in mysql_execute_command (thd=0x7f865972f070) at 10.0/sql/sql_parse.cc:3303 #13 0x0000000000685837 in mysql_parse (thd=0x7f865972f070, rawbuf=0x7f8656c14088 "UPDATE t1 SET vcol = 1730019328", length=31, parser_state=0x7f8660996630) at 10.0/sql/sql_parse.cc:6415 #14 0x000000000067864c in dispatch_command (command=COM_QUERY, thd=0x7f865972f070, packet=0x7f865a7f6071 "UPDATE t1 SET vcol = 1730019328", packet_length=31) at 10.0/sql/sql_parse.cc:1307 #15 0x00000000006779f1 in do_command (thd=0x7f865972f070) at 10.0/sql/sql_parse.cc:1004 #16 0x0000000000794226 in do_handle_one_connection (thd_arg=0x7f865972f070) at 10.0/sql/sql_connect.cc:1379 #17 0x0000000000793f79 in handle_one_connection (arg=0x7f865972f070) at 10.0/sql/sql_connect.cc:1293 #18 0x0000000000cc9ece in pfs_spawn_thread (arg=0x7f8658b67bf0) at 10.0/storage/perfschema/pfs.cc:1860 #19 0x00007f8660582b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #20 0x00007f865ea7a20d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

5 older comments

Hide

Permalink

Leo Unglaub added a comment - 2014-10-01 21:01

So basicly, you are not going to fix the segfault, you are just forbidding to use some functions in virtual columns? In my opinion this is not the best solution, because it already works most of the time. It just crashes sometims. Also not having a FROM_UNIXTIME in virtual columns renders them pretty much useless for everyone who has to deal with a lot of PHP inserted timestamps.

Show

Leo Unglaub added a comment - 2014-10-01 21:01 So basicly, you are not going to fix the segfault, you are just forbidding to use some functions in virtual columns? In my opinion this is not the best solution, because it already works most of the time. It just crashes sometims. Also not having a FROM_UNIXTIME in virtual columns renders them pretty much useless for everyone who has to deal with a lot of PHP inserted timestamps.

Hide

Permalink

Sergei Petrunia added a comment - 2014-10-01 23:10

well, "crashes sometimes" is a total show stopper .

Leo Unglaub, can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME).

Are there any reasons to use a virtual column and not define a VIEW?

Show

Sergei Petrunia added a comment - 2014-10-01 23:10 well, "crashes sometimes" is a total show stopper . Leo Unglaub , can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME). Are there any reasons to use a virtual column and not define a VIEW?

Hide

Permalink

Leo Unglaub added a comment - 2014-10-02 16:28

well, "crashes sometimes" is a total show stopper .

You are absolutly right. What i meant was, that it is currently already possible, it just needs a bugfix instead of a removal.

can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT?

The server itself runs on UTC (GMT), the client is forced to set it's timezone according to it's needs. In my opinion this is also the time zone that should be used in virtual columns. As you explained it to me in IRC the thd pointer already contains the current connection state. This should also contain the timezone set by the user.

I am personally against adding some new functions that take a timezone as a param. This would only confuse most of the people, because they now have two functions doing the same. This would look to me like a Microsoft VB solution. (Lets translate method names to make it more simple, but in reallity they screwed it up bad)

Are there any reasons to use a virtual column and not define a VIEW?

Personally i am against using views for small manipulations on a table. Because if you manipulate the original table, you also have to update the view. Now you have two places you have to manipulate if you change the schema.

Greetings
Leo

Show

Leo Unglaub added a comment - 2014-10-02 16:28 well, "crashes sometimes" is a total show stopper . You are absolutly right. What i meant was, that it is currently already possible, it just needs a bugfix instead of a removal. can you shed more light on your use case? FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? The server itself runs on UTC (GMT), the client is forced to set it's timezone according to it's needs. In my opinion this is also the time zone that should be used in virtual columns. As you explained it to me in IRC the thd pointer already contains the current connection state. This should also contain the timezone set by the user. I am personally against adding some new functions that take a timezone as a param. This would only confuse most of the people, because they now have two functions doing the same. This would look to me like a Microsoft VB solution. (Lets translate method names to make it more simple, but in reallity they screwed it up bad) Are there any reasons to use a virtual column and not define a VIEW? Personally i am against using views for small manipulations on a table. Because if you manipulate the original table, you also have to update the view. Now you have two places you have to manipulate if you change the schema. Greetings Leo

Hide

Permalink

Leo Unglaub added a comment - 2014-10-02 16:31

If you need me to explain my special use case, ping me on IRC. That should be easier

Show

Leo Unglaub added a comment - 2014-10-02 16:31 If you need me to explain my special use case, ping me on IRC. That should be easier

Hide

Permalink

Heinz Wiesinger added a comment - 2014-11-13 13:55

I just ran into the same issue with one of my systems. Having the same use case, so "converting" PHP timestamps to human readable date/time values.

FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME).

My server is running UTC+1, which is also what I'd expect FROM_UNIXTIME to use. But having to specify the timezone explicitly seems to be good option as well.

Show

Heinz Wiesinger added a comment - 2014-11-13 13:55 I just ran into the same issue with one of my systems. Having the same use case, so "converting" PHP timestamps to human readable date/time values. FROM_UNIXTIME implies a timezone, which timezone is used at your system? Will it be ok for you if it was GMT? (or some other timezone that you would have to specify as an extra parameter to FROM_UNIXTIME). My server is running UTC+1, which is also what I'd expect FROM_UNIXTIME to use. But having to specify the timezone explicitly seems to be good option as well.

segfault in Item_func_from_unixtime::get_date on updating table with virtual columns

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Time Tracking

Agile