Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.8(EOL)
-
None
Description
Timezone initialization has a logic which presumes that (certain) tables have ascending indexes, and if it's not so, initialization fails – either with an error on a non-debug build, or with a debug assertion.
ALTER TABLE mysql.time_zone_transition_type DROP PRIMARY KEY; |
ALTER TABLE mysql.time_zone_transition_type ADD PRIMARY KEY (`Time_zone_id`,`Transition_type_id` DESC); |
|
|
--source include/restart_mysqld.inc
|
|
|
SET @@time_zone='Japan'; |
|
preview-10.8-MDEV-13756-desc-indexes 383b51d6 non-debug |
2021-12-31 19:42:43 3 [ERROR] Error while loading time zone description from mysql.time_zone_transition table: bad transition type id
|
mysqltest: At line 6: query 'SET @@time_zone='Japan'' failed: ER_UNKNOWN_TIME_ZONE (1298): Unknown or incorrect time zone: 'Japan' |
|
preview-10.8-MDEV-13756-desc-indexes 383b51d6 debug |
mariadbd: /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/tztime.cc:2007: Time_zone* tz_load_from_open_tables(const String*, TABLE_LIST*): Assertion `ttid >= tmp_tz_info.typecnt' failed.
|
211231 19:44:21 [ERROR] mysqld got signal 6 ;
|
|
|
#7 0x00007fe1e0fe2662 in __GI___assert_fail (assertion=0x556d94e6f867 "ttid >= tmp_tz_info.typecnt", file=0x556d94e6f1b8 "/data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/tztime.cc", line=2007, function=0x556d94e6f6b0 "Time_zone* tz_load_from_open_tables(const String*, TABLE_LIST*)") at assert.c:101
|
#8 0x0000556d938afe9c in tz_load_from_open_tables (tz_name=0x7fe1c0017c58, tz_tables=0x7fe1dc1a4f90) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/tztime.cc:2007
|
#9 0x0000556d938b192d in my_tz_find (thd=0x7fe1c0000db8, name=0x7fe1c0017c58) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/tztime.cc:2344
|
#10 0x0000556d93993741 in Sys_var_tz::do_check (this=0x556d96557740 <Sys_time_zone>, thd=0x7fe1c0000db8, var=0x7fe1c0017cb0) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sys_vars.ic:2205
|
#11 0x0000556d9339f3d9 in sys_var::check (this=0x556d96557740 <Sys_time_zone>, thd=0x7fe1c0000db8, var=0x7fe1c0017cb0) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/set_var.cc:246
|
#12 0x0000556d933a2671 in set_var::check (this=0x7fe1c0017cb0, thd=0x7fe1c0000db8) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/set_var.cc:810
|
#13 0x0000556d933a1e71 in sql_set_variables (thd=0x7fe1c0000db8, var_list=0x7fe1c00060d8, free=true) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/set_var.cc:738
|
#14 0x0000556d935e2463 in mysql_execute_command (thd=0x7fe1c0000db8, is_called_from_prepared_stmt=false) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_parse.cc:5034
|
#15 0x0000556d935f63bc in mysql_parse (thd=0x7fe1c0000db8, rawbuf=0x7fe1c0017b80 "SET @@time_zone='Japan'", length=23, parser_state=0x7fe1dc1a6500) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_parse.cc:8028
|
#16 0x0000556d935cd73f in dispatch_command (command=COM_QUERY, thd=0x7fe1c0000db8, packet=0x7fe1c000b989 "SET @@time_zone='Japan'", packet_length=23, blocking=true) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_parse.cc:1894
|
#17 0x0000556d935ca930 in do_command (thd=0x7fe1c0000db8, blocking=true) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_parse.cc:1402
|
#18 0x0000556d93927261 in do_handle_one_connection (connect=0x556d98a22238, put_in_cache=true) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_connect.cc:1418
|
#19 0x0000556d93926a68 in handle_one_connection (arg=0x556d98a22238) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/sql/sql_connect.cc:1312
|
#20 0x0000556d9420bdb4 in pfs_spawn_thread (arg=0x556d98a1f728) at /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/storage/perfschema/pfs.cc:2201
|
#21 0x00007fe1e14acea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#22 0x00007fe1e10abdef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Attachments
Issue Links
- is caused by
-
MDEV-13756 Implement descending index: KEY (a DESC, b ASC)
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Component/s | OTHER [ 10125 ] | |
| Component/s | Documentation [ 10903 ] | |
| Component/s | GIS [ 10105 ] | |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Assignee | Alexey Botchkov [ holyfoot ] | Elena Stepanova [ elenst ] |
| Resolution | Not a Bug [ 6 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Resolution | Not a Bug [ 6 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | Open [ 1 ] |
| Link |
This issue is caused by |
| Component/s | Time zones [ 10108 ] | |
| Component/s | OTHER [ 10125 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | N/A [ 14700 ] | |
| Affects Version/s | 10.8 [ 26121 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.7 [ 24805 ] | |
| Description |
_Quite possibly it just needs a note in documentation (or maybe such a note even exists already, but I couldn't find any)_
A geometry column can be a part of a normal (non-spatial) index. It is created without any warnings. However, it seems to be ignored in cases when an index on a binary column with the same contents and queries would be used. {code:sql} --source include/have_sequence.inc create or replace table t (a point, key(a)); insert into t select POINT(seq,seq+1) from seq_1_to_100; explain select * from t where a > PointFromText('POINT(22 22)'); explain select * from t order by a; create or replace table t (a binary(25), key(a)); insert into t select POINT(seq,seq+1) from seq_1_to_100; explain select * from t where a > PointFromText('POINT(22 22)'); explain select * from t order by a; # Cleanup drop table t; {code} With a geometry column: {code:sql|title=10.2 059797ed} MariaDB [test]> explain select * from t where a > PointFromText('POINT(22 22)'); +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | ALL | a | NULL | NULL | NULL | 100 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.001 sec) MariaDB [test]> explain select * from t order by a; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100 | Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.000 sec) {code} With a binary column: {code:sql} MariaDB [test]> explain select * from t where a > PointFromText('POINT(22 22)'); +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t | range | a | a | 26 | NULL | 80 | Using where; Using index | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.001 sec) MariaDB [test]> explain select * from t order by a; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | index | NULL | a | 26 | NULL | 100 | Using index | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.000 sec) {code} |
Timezone initialization has a logic which presumes that (certain) tables have ascending indexes, and if it's not so, initialization fails -- either with an error on a non-debug build, or with a debug assertion.
{code:sql} ALTER TABLE mysql.time_zone_transition_type DROP PRIMARY KEY; ALTER TABLE mysql.time_zone_transition_type ADD PRIMARY KEY (`Time_zone_id`,`Transition_type_id` DESC); --source include/restart_mysqld.inc SET @@time_zone='Japan'; {code} {noformat:title=preview-10.8- 2021-12-31 19:42:43 3 [ERROR] Error while loading time zone description from mysql.time_zone_transition table: bad transition type id {noformat} {code:sql} mysqltest: At line 6: query 'SET @@time_zone='Japan'' failed: ER_UNKNOWN_TIME_ZONE (1298): Unknown or incorrect time zone: 'Japan' {code} {noformat:title=preview-10.8- mariadbd: /data/src/preview-10.8- 211231 19:44:21 [ERROR] mysqld got signal 6 ; #7 0x00007fe1e0fe2662 in __GI___assert_fail (assertion=0x556d94e6f867 "ttid >= tmp_tz_info.typecnt", file=0x556d94e6f1b8 "/data/src/preview-10.8- #8 0x0000556d938afe9c in tz_load_from_open_tables (tz_name=0x7fe1c0017c58, tz_tables=0x7fe1dc1a4f90) at /data/src/preview-10.8- #9 0x0000556d938b192d in my_tz_find (thd=0x7fe1c0000db8, name=0x7fe1c0017c58) at /data/src/preview-10.8- #10 0x0000556d93993741 in Sys_var_tz::do_check (this=0x556d96557740 <Sys_time_zone>, thd=0x7fe1c0000db8, var=0x7fe1c0017cb0) at /data/src/preview-10.8- #11 0x0000556d9339f3d9 in sys_var::check (this=0x556d96557740 <Sys_time_zone>, thd=0x7fe1c0000db8, var=0x7fe1c0017cb0) at /data/src/preview-10.8- #12 0x0000556d933a2671 in set_var::check (this=0x7fe1c0017cb0, thd=0x7fe1c0000db8) at /data/src/preview-10.8- #13 0x0000556d933a1e71 in sql_set_variables (thd=0x7fe1c0000db8, var_list=0x7fe1c00060d8, free=true) at /data/src/preview-10.8- #14 0x0000556d935e2463 in mysql_execute_command (thd=0x7fe1c0000db8, is_called_from_prepared_stmt=false) at /data/src/preview-10.8- #15 0x0000556d935f63bc in mysql_parse (thd=0x7fe1c0000db8, rawbuf=0x7fe1c0017b80 "SET @@time_zone='Japan'", length=23, parser_state=0x7fe1dc1a6500) at /data/src/preview-10.8- #16 0x0000556d935cd73f in dispatch_command (command=COM_QUERY, thd=0x7fe1c0000db8, packet=0x7fe1c000b989 "SET @@time_zone='Japan'", packet_length=23, blocking=true) at /data/src/preview-10.8- #17 0x0000556d935ca930 in do_command (thd=0x7fe1c0000db8, blocking=true) at /data/src/preview-10.8- #18 0x0000556d93927261 in do_handle_one_connection (connect=0x556d98a22238, put_in_cache=true) at /data/src/preview-10.8- #19 0x0000556d93926a68 in handle_one_connection (arg=0x556d98a22238) at /data/src/preview-10.8- #20 0x0000556d9420bdb4 in pfs_spawn_thread (arg=0x556d98a1f728) at /data/src/preview-10.8- #21 0x00007fe1e14acea7 in start_thread (arg=<optimized out>) at pthread_create.c:477 #22 0x00007fe1e10abdef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 {noformat} |
| Summary | Regular BTREE indexes on geometry columns are allowed but ignored | Timezone tables cannot have descending indexes |
| Comment |
[ I've realized it's not specific to geometry, but to the prefix index, which is created implicitly for the geometry column. The result would be the same for a binary column with a prefix index.
] |
| Assignee | Elena Stepanova [ elenst ] | Sergei Golubchik [ serg ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Fix Version/s | 10.8.1 [ 26815 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Testing [ 10301 ] | Closed [ 6 ] |