Bug #77748 Assertion failure when previously unused time zone is used in function/trigger

• Submitted: 16 Jul 2015 20:14
• Modified: 20 Aug 2015 21:05
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.7.9-git
• OS: Any
• CPU Architecture: Any

[16 Jul 2015 20:14] Dmitry Lenev

Description:
Usage of CONVERT_TZ() function or setting of @@time_zone system variable
inside of stored function or trigger leads to assertion failure if time
zone used by CONVERT_TZ/set for connection was not used since server
start up and its definition needs to be loaded from time zone tables.

The assertion failure occurs due to fact that we call trans_commit_stmt()
in sub-statement (which should not happen) while trying to finalize
attachable transaction which was used to read time zone definition.

Abbreviated stacktrace:

#3  0x00007ffff69c1c32 in __GI___assert_fail (
    assertion=0x1ef5ba5 "! thd->in_sub_stmt", 
    file=0x1ef5840 "sql/transaction.cc", 
    line=446, 
    function=0x1ef5f10 <trans_commit_stmt(THD*)::__PRETTY_FUNCTION__> "bool trans_commit_stmt(THD*)") at assert.c:101
#4  0x0000000001529bdb in trans_commit_stmt (thd=0x7fff84012420)
    at sql/transaction.cc:446
#5  0x00000000013c069d in THD::Attachable_trx::~Attachable_trx (
    this=0x7fff84e1cb60, __in_chrg=<optimized out>)
    at sql/sql_class.cc:261
#6  0x00000000013ca986 in THD::end_attachable_transaction (this=0x7fff84012420)
    at sql/sql_class.cc:3823
#7  0x00000000013af1b9 in close_trans_system_tables (thd=0x7fff84012420)
    at sql/sql_base.cc:10083
#8  0x00000000015339dd in my_tz_find (thd=0x7fff84012420, name=0x7fff84e19280)
    at sql/tztime.cc:2359
#9  0x0000000001303565 in Item_func_convert_tz::get_date (this=0x7fff84e19618, 
    ltime=0x7fffde67c970, fuzzy_date=1)
    at sql/item_timefunc.cc:2283
#10 0x0000000000e519ca in Item::save_date_in_field (this=0x7fff84e19618, field=
    0x7fff840ce7f8) at sql/item.cc:526
#11 0x0000000001308b56 in Item_datetime_func::save_in_field (
    this=0x7fff84e19618, field=0x7fff840ce7f8, no_conversions=false)
    at sql/item_timefunc.h:733
#12 0x00000000013779d3 in sp_eval_expr (thd=0x7fff84012420, 
    result_field=0x7fff840ce7f8, expr_item_ptr=0x7fff84e198c8)
    at sql/sp.cc:2730
#13 0x000000000138ba11 in sp_rcontext::set_return_value (this=0x7fff84e08bf0, 
    thd=0x7fff84012420, return_value_item=0x7fff84e198c8)
    at sql/sp_rcontext.cc:157
#14 0x0000000001384503 in sp_instr_freturn::exec_core (this=0x7fff84e19818, 
    thd=0x7fff84012420, nextp=0x7fffde67cc70)
...

How to repeat:
CREATE FUNCTION f1() RETURNS DATETIME RETURN CONVERT_TZ('2002-01-01 00:00:00', 'UTC', 'No-such-time-zone');
# Observe assertion failure
SELECT f1();

[16 Jul 2015 20:19] Dmitry Lenev

Posted by developer:
 
MySQL Server versions prior to 5.7 should not be affected since they do not use attachable transactions
mechanism for reading from time zone tables.

[20 Aug 2015 21:05] Paul DuBois

Noted in 5.7.9, 5.8.0 changelogs.

An attempt to use a previously unused time zone with CONVERT_TZ()
could produce warnings or errors or (in debug builds) raise an
assertion if GTIDs were enabled but the binary log was not enabled.

In debug builds, an attempt to use a previously unused time zone with
CONVERT_TZ() or as the value of the time_zone system variable inside
a stored program could raise an assertion.