|
|
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
|
|
|
The valgrind failure looks like this:
==31934== Invalid read of size 8
|
==31934== at 0x897CE6: Item_func_from_unixtime::get_date(st_mysql_time*, unsigned long long) (item_timefunc.cc:1925)
|
==31934== by 0x89BEA8: Item_func::get_arg0_date(st_mysql_time*, unsigned long long) (item_func.h:154)
|
==31934== by 0x897A71: Item_func_date_format::val_str(String*) (item_timefunc.cc:1864)
|
==31934== by 0x7FBA52: Item::save_in_field(Field*, bool) (item.cc:6015)
|
==31934== by 0x6EE9AA: update_virtual_fields(THD*, TABLE*, enum_vcol_update_mode) (table.cc:6568)
|
==31934== by 0x90A3B8: rr_sequential(READ_RECORD*) (records.cc:480)
|
==31934== by 0x6D1616: mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, enum_duplicates, bool, unsigned long long*, unsigned long long*) (sql_update.cc:692)
|
==31934== by 0x6174CE: mysql_execute_command(THD*) (sql_parse.cc:2848)
|
==31934== by 0x61FA21: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5799)
|
==31934== by 0x6130A2: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1079)
|
item_timefunc.cc:1925 is:
thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)sec);
|
and the problem is with the contents of the `thd` pointer.
Item_func_from_unixtime has THD *thd as a local variable. It is set in Item_func_from_unixtime::fix_length_and_dec.
When I run Elena's test case, I see that Item_func_from_unixtime::fix_length_and_dec is called for the INSERT statement, but not for the UPDATE statement. When executing UPDATE, we try to refer to the THD of the first connection (which is already gone), and crash.
|
|
This stack trace shows where Item_func_from_unixtime::thd is set when the table is opened:
Breakpoint 3, Item_func_from_unixtime::fix_length_and_dec (this=0x7fff8c00c798) at /home/psergey/dev2/5.5/sql/item_timefunc.cc:1903
|
(gdb) wher
|
#0 Item_func_from_unixtime::fix_length_and_dec (this=0x7fff8c00c798) at /home/psergey/dev2/5.5/sql/item_timefunc.cc:1903
|
#1 0x0000000000839a03 in Item_func::fix_fields (this=0x7fff8c00c798, thd=0x2f8ad10, ref=0x7fff8c0096e8) at /home/psergey/dev2/5.5/sql/item_func.cc:231
|
#2 0x000000000083975c in Item_func::fix_fields (this=0x7fff8c009658, thd=0x2f8ad10, ref=0x7fff8c009778) at /home/psergey/dev2/5.5/sql/item_func.cc:204
|
#3 0x00000000008603e3 in Item_str_func::fix_fields (this=0x7fff8c009658, thd=0x2f8ad10, ref=0x7fff8c009778) at /home/psergey/dev2/5.5/sql/item_strfunc.cc:119
|
#4 0x00000000006e384d in fix_vcol_expr (thd=0x2f8ad10, table=0x7fff8c008c70, vcol_field=0x7fff8c00bd68) at /home/psergey/dev2/5.5/sql/table.cc:2120
|
#5 0x00000000006e3e81 in unpack_vcol_info_from_frm (thd=0x2f8ad10, mem_root=0x7fff8c0094b0, table=0x7fff8c008c70, field=0x7fff8c00bd68, vcol_expr=0x7fff8c00db20, error_reported=0x7ffff7f8201c) at /home/psergey/dev2/5.5/sql/table.cc:2305
|
#6 0x00000000006e494a in open_table_from_share (thd=0x2f8ad10, share=0x7fff8c00d618, alias=0x7fff8c005d48 "t1", db_stat=39, prgflag=44, ha_open_flags=16, outparam=0x7fff8c008c70, is_create_table=false) at /home/psergey/dev2/5.5/sql/table.cc:2538
|
#7 0x00000000005b55b2 in open_table (thd=0x2f8ad10, table_list=0x7fff8c007318, mem_root=0x7ffff7f82620, ot_ctx=0x7ffff7f825e0) at /home/psergey/dev2/5.5/sql/sql_base.cc:3186
|
#8 0x00000000005b7afc in open_and_process_table (thd=0x2f8ad10, lex=0x2f8df80, tables=0x7fff8c007318, counter=0x7ffff7f8274c, flags=0, prelocking_strategy=0x7ffff7f82780, has_prelocking_list=false, ot_ctx=0x7ffff7f825e0, new_frm_mem=0x7ffff7f82620) at /home/psergey/dev2/5.5/sql/sql_base.cc:4537
|
#9 0x00000000005b8b4f in open_tables (thd=0x2f8ad10, start=0x7ffff7f82700, counter=0x7ffff7f8274c, flags=0, prelocking_strategy=0x7ffff7f82780) at /home/psergey/dev2/5.5/sql/sql_base.cc:5053
|
#10 0x00000000005b99e7 in open_and_lock_tables (thd=0x2f8ad10, tables=0x7fff8c007318, derived=true, flags=0, prelocking_strategy=0x7ffff7f82780) at /home/psergey/dev2/5.5/sql/sql_base.cc:5658
|
#11 0x00000000005ac39f in open_and_lock_tables (thd=0x2f8ad10, tables=0x7fff8c007318, derived=true, flags=0) at /home/psergey/dev2/5.5/sql/sql_base.h:524
|
#12 0x00000000005f77b7 in mysql_insert (thd=0x2f8ad10, table_list=0x7fff8c007318, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /home/psergey/dev2/5.5/sql/sql_insert.cc:761
|
#13 0x00000000006179ac in mysql_execute_command (thd=0x2f8ad10) at /home/psergey/dev2/5.5/sql/sql_parse.cc:2973
|
#14 0x000000000061fa22 in mysql_parse (thd=0x2f8ad10, rawbuf=0x7fff8c00d508 "INSERT INTO t1 (a) VALUES (1411416000),(1411417000)", length=51, parser_state=0x7ffff7f834f0) at /home/psergey/dev2/5.5/sql/sql_parse.cc:5799
|
The problem is that TABLE* objects are cached and re-used, also across THDs.
|
|
Looking at the problem from another angle:
So, a virtual columns function uses Item_func_from_unixtime, which calls
thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)sec);
|
which essentially means that the value of the function depends on the timezone in the current THD! Can this be allowed at all? I recall, partitioning functions had quite severe limitations that ensured that value of partitioned function does not depend on the current user's environment.
|
|
MariaDB [j26]> create table t10 (a int) partition by hash(FROM_UNIXTIME(a)) partitions 3;
|
ERROR 1564 (HY000): This partition function is not allowed
|
|
|
Discussed with sanja and igor on the optimizer call. The conclusion is that functions that depend on the environment should not be allowed in virtual columns. That is, FROM_UNIXTIME in its current form should not be allowed to be used in virtual column definition. (maybe we should have a variant of FROM_UNIXTIME that requires one to explicitly specify the timezone? That could be allowed)
|
|
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. 
|
|
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?
|
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
|
|
If you need me to explain my special use case, ping me on IRC. That should be easier
|
|
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.
|
mariadb.error.stacktrace