| Bug #93728 | mysqld crash after using alter table with SPATIAL key | ||
|---|---|---|---|
| Submitted: | 22 Dec 2018 3:12 | Modified: | 22 Dec 2018 17:01 |
| Reporter: | rui xu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
| Version: | 5.7.18 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | ddl SPATIAL key drop primary key debug | ||
[22 Dec 2018 11:36]
Peter Laursen
The test case here does not crash my 5.7.24 64-bit mysqld on Windows (non-debug build). -- Peter -- not a MySQL/Oracle person
[22 Dec 2018 16:57]
MySQL Verification Team
miguel@luz:~/dbsd $ ./57c
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-debug Source distribution BUILD: 2018-DEC-11
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql 5.7 > CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL, SPATIAL KEY idx2(c2 ASC), SPATIAL KEY idx3(c3 DESC), SPATIAL KEY idx4(c4 ASC), SPATIAL KEY idx5(c5 ASC), KEY idx6(c4(10)))ENGINE=InnoDB;
Query OK, 0 rows affected (3,58 sec)
mysql 5.7 > INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
Query OK, 1 row affected (0,13 sec)
Query OK, 1 row affected (0,13 sec)
mysql 5.7 > alter table tab drop primary key, add column new_uniq_col int NOT NULL AUTO_INCREMENT FIRST, add unique index uniq_idx(new_uniq_col); Query OK, 0 rows affected (3,83 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.7 > delete from tab;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.7 >
2018-12-22T16:54:44.468923Z 0 [Note] Event Scheduler: Loaded 0 events
2018-12-22T16:54:44.469268Z 0 [Note] /home/miguel/dbsd/5.7/bin/mysqld: ready for connections.
Version: '5.7.26-debug' socket: '/tmp/mysql57.sock' port: 3357 Source distribution BUILD: 2018-DEC-11
2018-12-22T16:55:40.657491Z 2 [ERROR] InnoDB: Record in index `idx2` of table `test`.`tab` was not found on update: TUPLE (info_bits=0, 2 fields): {[32] $@ $@ $@ $@(0x0000000000000400000000000000040000000000000004000000000000000400),[4] (0x00000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x090E06090D050D00)}
2018-12-22T16:55:40.657567Z 2 [Note] InnoDB: GIS MBR INFO: 10 and 10, 10, 10
Root page no: 4
2018-12-22 14:55:40 0x7f18300de700 InnoDB: Assertion failure in thread 139741862160128 in file row0upd.cc line 2286
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:55:40 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68261 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f17d0000dd0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f18300dde18 thread_stack 0x40000
/home/miguel/dbsd/5.7/bin/mysqld(my_print_stacktrace+0x47)[0x555a758e61d1]
/home/miguel/dbsd/5.7/bin/mysqld(handle_fatal_signal+0x3f3)[0x555a74e97b0c]
/usr/lib/libpthread.so.0(+0x123c0)[0x7f183c8623c0]
/usr/lib/libc.so.6(gsignal+0x10f)[0x7f183c32ed7f]
/usr/lib/libc.so.6(abort+0x125)[0x7f183c319672]
/home/miguel/dbsd/5.7/bin/mysqld(+0x1acc01f)[0x555a75b8301f]
/home/miguel/dbsd/5.7/bin/mysqld(+0x1a4cf7d)[0x555a75b03f7d]
/home/miguel/dbsd/5.7/bin/mysqld(+0x1a4d358)[0x555a75b04358]
/home/miguel/dbsd/5.7/bin/mysqld(_Z7row_updP10upd_node_tP9que_thr_t+0x41a)[0x555a75b061de]
/home/miguel/dbsd/5.7/bin/mysqld(_Z12row_upd_stepP9que_thr_t+0x268)[0x555a75b0655e]
/home/miguel/dbsd/5.7/bin/mysqld(+0x19eac3f)[0x555a75aa1c3f]
/home/miguel/dbsd/5.7/bin/mysqld(_Z20row_update_for_mysqlPKhP14row_prebuilt_t+0x7c)[0x555a75aa1fd2]
/home/miguel/dbsd/5.7/bin/mysqld(_ZN11ha_innobase10delete_rowEPKh+0x25d)[0x555a7593e161]
/home/miguel/dbsd/5.7/bin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x1cc)[0x555a74f25a80]
/home/miguel/dbsd/5.7/bin/mysqld(_ZN14Sql_cmd_delete12mysql_deleteEP3THDy+0x1809)[0x555a757b4661]
/home/miguel/dbsd/5.7/bin/mysqld(_ZN14Sql_cmd_delete7executeEP3THD+0x17f)[0x555a757b76f9]
/home/miguel/dbsd/5.7/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x2e25)[0x555a75582def]
/home/miguel/dbsd/5.7/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x592)[0x555a75588b44]
/home/miguel/dbsd/5.7/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xc3a)[0x555a7557dab6]
/home/miguel/dbsd/5.7/bin/mysqld(_Z10do_commandP3THD+0x51d)[0x555a7557c941]
/home/miguel/dbsd/5.7/bin/mysqld(handle_connection+0x1ee)[0x555a756bde7a]
/home/miguel/dbsd/5.7/bin/mysqld(pfs_spawn_thread+0x173)[0x555a75da3f46]
/usr/lib/libpthread.so.0(+0x7a9d)[0x7f183c857a9d]
/usr/lib/libc.so.6(clone+0x43)[0x7f183c3f2b23]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f17d0005af0): delete from tab
Connection ID (thread ID): 2
Status: NOT_KILLED
[22 Dec 2018 17:01]
MySQL Verification Team
Thank you for the bug report.
[24 Dec 2018 8:50]
MySQL Verification Team
Severity of S6 is misleading here. Problem exists in release builds, it doesn't matter that they don't assert, there's still corruption reported.
[24 Dec 2018 12:18]
MySQL Verification Team
Changing severity for corruption detected in release build.
[21 Jan 2019 6:41]
Jie Zhou
Check table will also show the spatial index is corrupt.
root@(none) 02:36:26>use test
Database changed
root@test 02:36:29>CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL, SPATIAL KEY idx2(c2 ASC), SPATIAL KEY idx3(c3 DESC), SPATIAL KEY idx4(c4 ASC), SPATIAL KEY idx5(c5 ASC), KEY idx6(c4(10)))ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
root@test 02:36:34>INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
root@test 02:36:38>alter table tab drop primary key, add column new_uniq_col int NOT NULL AUTO_INCREMENT FIRST, add unique index uniq_idx(new_uniq_col);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@test 02:36:43>check table tab;
+----------+-------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+-------+----------+-------------------------------------------------------+
| test.tab | check | Warning | InnoDB: Index 'idx2' contains 1 entries, should be 2. |
| test.tab | check | Warning | InnoDB: Index 'idx3' contains 1 entries, should be 2. |
| test.tab | check | Warning | InnoDB: Index 'idx4' contains 1 entries, should be 2. |
| test.tab | check | Warning | InnoDB: Index 'idx5' contains 1 entries, should be 2. |
| test.tab | check | error | Corrupt |
+----------+-------+----------+-------------------------------------------------------+
5 rows in set (0.00 sec)
Description: innodb data will be corrupt after doing alter table operation on table with SPATIAL INDEX, this will cause mysqld crash(debug version, release version error log) when doing things on this table afterwards How to repeat: #1.create table CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL, SPATIAL KEY idx2(c2 ASC), SPATIAL KEY idx3(c3 DESC), SPATIAL KEY idx4(c4 ASC), SPATIAL KEY idx5(c5 ASC), KEY idx6(c4(10)))ENGINE=InnoDB; #2. insert two rows INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); #3. alter table alter table tab drop primary key, add column new_uniq_col int NOT NULL AUTO_INCREMENT FIRST, add unique index uniq_idx(new_uniq_col); #4. do others things like delete delete from tab; ### debug version mysqld coredump #6 0x0000000001d43dd7 in ut_dbg_assertion_failed ut0dbg.cc:67 #7 0x0000000001cbecbe in row_upd_sec_index_entry row0upd.cc:2274 #8 0x0000000001cbf099 in row_upd_sec_step row0upd.cc:2354 #10 0x0000000001cc12d5 in row_upd_step row0upd.cc:3180 #11 0x0000000001c590fd in row_update_for_mysql_using_upd_graph row0mysql.cc:2540 release version will have such error log [ERROR] InnoDB: Record in index `idx2` of table `test`.`tab` was not found on update: TUPLE (info_bits=0, 2 fields): {[32] $@ $@ $@ $@(0x0000000000000400000000000000040000000000000004000000000000000400),[4] (0x00000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x090E06090D050D00)} Suggested fix: 1. should this alter operation using inplace ddl or copy algorithm? 2. if inplace chosen is ok, is this innodb SPATIAL INDEX inplace ddl updating bug?