| Bug #87518 | Server asserts when using n-gram full text searching with LIMIT | ||
|---|---|---|---|
| Submitted: | 23 Aug 2017 19:06 | Modified: | 23 Aug 2017 19:50 |
| Reporter: | Yura Sorokin (OCA) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S6 (Debug Builds) |
| Version: | 5.7.19, 8.0.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Aug 2017 19:07]
Yura Sorokin
Possibly a duplicate of Bug #85835
[23 Aug 2017 19:17]
Yura Sorokin
Fix for 5.7
Attachment: bug87518_5.7.diff (application/octet-stream, text), 4.03 KiB.
[23 Aug 2017 19:18]
Yura Sorokin
Also affects 8.0.2
[23 Aug 2017 19:50]
MySQL Verification Team
Thank you for the bug report. Not repeatable with most recent source server:
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.20-debug Source distribution 2017-AUG-15
Copyright (c) 2000, 2017, 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 > CREATE DATABASE d5;
Query OK, 1 row affected (0.00 sec)
mysql 5.7 > USE d5
Database changed
mysql 5.7 > CREATE TABLE t1 (
-> FTS_DOC_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
-> contents TEXT COLLATE utf8mb4_bin,
-> FULLTEXT KEY fx_contents (contents) WITH PARSER ngram
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (2.19 sec)
mysql 5.7 >
mysql 5.7 > INSERT INTO t1 VALUES (1, 'xyz mnt'), (2, 'xyz mnt'), (3, 'xyz');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.7 >
mysql 5.7 > # The query completes with or without bug
mysql 5.7 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE);
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
| 2 | xyz mnt |
| 3 | xyz |
+------------+----------+
3 rows in set (0.07 sec)
mysql 5.7 >
mysql 5.7 > # The query returns wrong result set with the bug
mysql 5.7 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE) LIMIT 1;
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
+------------+----------+
1 row in set (0.00 sec)
mysql 5.7 >
mysql 5.7 > # The query crashes with the bug
mysql 5.7 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz mnt' IN BOOLEAN MODE) LIMIT 5;
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
| 2 | xyz mnt |
| 3 | xyz |
+------------+----------+
3 rows in set (0.00 sec)
mysql 5.7 >
c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.3-rc-debug Source distribution 2017-AUG-15
Copyright (c) 2000, 2017, 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 8.0 > CREATE DATABASE d5;
Query OK, 1 row affected (0.09 sec)
mysql 8.0 > USE d5
Database changed
mysql 8.0 > CREATE TABLE t1 (
-> FTS_DOC_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
-> contents TEXT COLLATE utf8mb4_bin,
-> FULLTEXT KEY fx_contents (contents) WITH PARSER ngram
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (4.34 sec)
mysql 8.0 >
mysql 8.0 > INSERT INTO t1 VALUES (1, 'xyz mnt'), (2, 'xyz mnt'), (3, 'xyz');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 8.0 >
mysql 8.0 > # The query completes with or without bug
mysql 8.0 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE);
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
| 2 | xyz mnt |
| 3 | xyz |
+------------+----------+
3 rows in set (0.08 sec)
mysql 8.0 >
mysql 8.0 > # The query returns wrong result set with the bug
mysql 8.0 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE) LIMIT 1;
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
+------------+----------+
1 row in set (0.00 sec)
mysql 8.0 >
mysql 8.0 > # The query crashes with the bug
mysql 8.0 > SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz mnt' IN BOOLEAN MODE) LIMIT 5;
+------------+----------+
| FTS_DOC_ID | contents |
+------------+----------+
| 1 | xyz mnt |
| 2 | xyz mnt |
| 3 | xyz |
+------------+----------+
3 rows in set (0.00 sec)
[25 Dec 2018 2:14]
Shaohua Wang
The reason you can't reproduce it because of the following fix: commit 48e07ec3935ab229c8ec5aede71ae77ede65a789 Author: Aakanksha Verma <aakanksha.verma@oracle.com> Date: Wed Aug 2 18:12:02 2017 +0530 but it's totally wrong: it makes the original commit not work. Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT Problem: ======== Given query in bug page involves limit clause query and it also doesn't involve ranking, it leads to build whole doc_id tree. If there is 1 million records involved then it easily exceeds the result cache limit. Solution: ======== optimization can be done for limit clause query when (1) There is no ranking involved. (2) Only FTS Union operations involved. Reviewed-by: Shaohua Wang <shaohua.wang@oracle.com> Reviewed-by: Jimmy Yang <jimmy.yang@oracle.com> RB: 12588
[25 Dec 2018 3:24]
Shaohua Wang
The original patch by Thiru has two issues: 1. should consider duplication and deleted doc id when counting n_docs; 2. should not apply limit optimization on phrase search with parser.
[25 Dec 2018 3:25]
Shaohua Wang
Fix for 5.7 from Tencent TXSQL Team
Attachment: fts_query_with_limit_parser.diff (application/octet-stream, text), 6.93 KiB.
[10 Jun 2021 12:14]
zhijun long
It also affects mysql 5.7.29. 2021-06-10 17:50:13 0x7fd24f88d700 InnoDB: Assertion failure in thread 140541254227712 in file fts0que.cc line 3568 InnoDB: Failing assertion: ret == 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. 09:50:13 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=16777216 read_buffer_size=262144 max_used_connections=131 max_threads=0 thread_count=115 connection_count=115 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 50739 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fd264a11000 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 = 7fd24f88b510 thread_stack 0x40000 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x3b)[0xf13c4b] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x489)[0x7cedd9] /lib64/libpthread.so.0(+0xf700)[0x7fd385873700] /lib64/libc.so.6(gsignal+0x37)[0x7fd3842584e7] /lib64/libc.so.6(abort+0x148)[0x7fd384259bd8] /usr/local/mysql/bin/mysqld[0x7a1347] /usr/local/mysql/bin/mysqld(_Z9fts_queryP5trx_tP12dict_index_tjPKhmPP12fts_result_ty+0x1c1b)[0x120a82b] /usr/local/mysql/bin/mysqld(_ZN11ha_innobase11ft_init_extEjjP6String+0x20f)[0xf5513f] /usr/local/mysql/bin/mysqld(_ZN15Item_func_match11init_searchEP3THD+0x495)[0x896ab5] /usr/local/mysql/bin/mysqld(_Z12init_ftfuncsP3THDP13st_select_lex+0x40)[0xc514d0] /usr/local/mysql/bin/mysqld(_ZN4JOIN8optimizeEv+0x2c87)[0xca51a7] /usr/local/mysql/bin/mysqld(_ZN13st_select_lex8optimizeEP3THD+0x692)[0xced082] /usr/local/mysql/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x20a)[0xced2fa] /usr/local/mysql/bin/mysqld[0x794bfc] /usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x39e1)[0xcacb01] /usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3fd)[0xcafc8d] /usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x1102)[0xcb0ea2] /usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x1e7)[0xcb25c7] /usr/local/mysql-5.7.29-2-linux-x86_64/lib/plugin/threadpool.so(_Z26threadpool_process_requestP3THD+0xf3)[0x7fd2473b6b63] /usr/local/mysql-5.7.29-2-linux-x86_64/lib/plugin/threadpool.so(+0x5b2e)[0x7fd2473b7b2e] /lib64/libpthread.so.0(+0x7e15)[0x7fd38586be15] /lib64/libc.so.6(clone+0x6d)[0x7fd384323f9d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7fd2649e1038): is an invalid pointer Connection ID (thread ID): 84 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.
[1 Mar 2022 2:41]
Baolin Huang
8.0.25 met this crash, stack is 2022-02-26T13:52:01.100207+08:00 5808 [ERROR] [MY-013183] [InnoDB] Assertion failure: fts0que.cc:3285:ret == 0 thread 139878537733888 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/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 05:52:01 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7f3630546000 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 = 7f38028d841f thread_stack 0x40000 /path/to/mysql/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x2269e7e] /path/to/mysql/mysqld(handle_fatal_signal+0x2eb) [0x124627b] /lib64/libpthread.so.0(+0xf100) [0x7f3819cc4100] /lib64/libc.so.6(gsignal+0x37) [0x7f38186345f7] /lib64/libc.so.6(abort+0x148) [0x7f3818635ce8] /path/to/mysql/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31e) [0x25dee7e] /path/to/mysql/mysqld(fts_query(trx_t*, dict_index_t*, unsigned int, unsigned char const*, unsigned long, fts_result_t**, unsigned long long)+0x2388) [0x27803e8] /path/to/mysql/mysqld(ha_innobase::ft_init_ext(unsigned int, unsigned int, String*)+0x200) [0x23edb90] /path/to/mysql/mysqld(Item_func_match::init_search(THD*)+0x581) [0x140f3f1] /path/to/mysql/mysqld(init_ftfuncs(THD*, Query_block*)+0x40) [0x1018ac0] /path/to/mysql/mysqld(JOIN::optimize()+0x944) [0x10ac174] /path/to/mysql/mysqld(Query_block::optimize(THD*)+0xa7) [0x110bb77] /path/to/mysql/mysqld(Query_expression::optimize(THD*, TABLE*, bool)+0x6b) [0x117b37b] /path/to/mysql/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x2e) [0x110a4be] /path/to/mysql/mysqld(Sql_cmd_dml::execute(THD*)+0x5af) [0x1114f0f] /path/to/mysql/mysqld(mysql_execute_command(THD*, bool)+0xa35) [0x10b66d5] /path/to/mysql/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x4df) [0x10ba3ef] /path/to/mysql/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x22f8) [0x10bcc78] /path/to/mysql/mysqld(do_threadpool_command(connection_t*, COM_DATA*, enum_server_command)+0x6e) [0x2caba4e] /path/to/mysql/mysqld(threadpool_process_request_unix(THD*, connection_t*, int*)+0x16d) [0x2cabcad] /path/to/mysql/mysqld() [0x2cac214] /path/to/mysql/mysqld() [0x2cacbb6] /path/to/mysql/mysqld() [0x2850e51] /lib64/libpthread.so.0(+0x7dc5) [0x7f3819cbcdc5]
Description: Running NGRAM FULL TEXT SEARCH select queries with LIMIT clause may result in incorrect result set / server assertion in 'fts_query_calculate_ranking()'. *************************************** #0 0x00007f1ce1c17741 in pthread_kill () from /lib64/libpthread.so.0 #0 0x00007f1ce1c17741 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000001836ef0 in my_write_core (sig=6) at /mnt/hgfs/repos/percona-server/mysys/stacktrace.c:249 #2 0x0000000000e7d8b2 in handle_fatal_signal (sig=6) at /mnt/hgfs/repos/percona-server/sql/signal_handler.cc:220 #3 <signal handler called> #4 0x00007f1cdffab1d7 in raise () from /lib64/libc.so.6 #5 0x00007f1cdffac8c8 in abort () from /lib64/libc.so.6 #6 0x0000000001ab7aee in ut_dbg_assertion_failed (expr=0x2133a7a "ret == 0", file=0x21338a8 "/mnt/hgfs/repos/percona-server/storage/innobase/fts/fts0que.cc", line=3559) at /mnt/hgfs/repos/percona-server/storage/innobase/ut/ut0dbg.cc:67 #7 0x0000000001bf0391 in fts_query_calculate_ranking (query=0x7f1cd811d4a0, ranking=0x7f1c901a2838) at /mnt/hgfs/repos/percona-server/storage/innobase/fts/fts0que.cc:3559 #8 0x0000000001bf09d0 in fts_query_prepare_result (query=0x7f1cd811d4a0, result=0x7f1c900b68c8) at /mnt/hgfs/repos/percona-server/storage/innobase/fts/fts0que.cc:3729 #9 0x0000000001bf0b57 in fts_query_get_result (query=0x7f1cd811d4a0, result=0x0) at /mnt/hgfs/repos/percona-server/storage/innobase/fts/fts0que.cc:3772 #10 0x0000000001bf169f in fts_query (trx=0x7f1cd9de18c0, index=0x7f1c9014a8c0, flags=9, query_str=0x7f1c900361e0 "xyz mnt", query_len=7, result=0x7f1cd811f9e8, limit=5) at /mnt/hgfs/repos/percona-server/storage/innobase/fts/fts0que.cc:4116 #11 0x000000000188b692 in ha_innobase::ft_init_ext (this=0x7f1c9019ea00, flags=9, keynr=1, key=0x7f1c9003e438) at /mnt/hgfs/repos/percona-server/storage/innobase/handler/ha_innodb.cc:9397 #12 0x000000000188b860 in ha_innobase::ft_init_ext_with_hints (this=0x7f1c9019ea00, keynr=1, key=0x7f1c9003e438, hints=0x7f1c9003f110) at /mnt/hgfs/repos/percona-server/storage/innobase/handler/ha_innodb.cc:9437 #13 0x0000000000f9cf51 in Item_func_match::init_search (this=0x7f1c9003e2f8, thd=0x7f1c9002c420) at /mnt/hgfs/repos/percona-server/sql/item_func.cc:7817 #14 0x00000000014890f7 in init_ftfuncs (thd=0x7f1c9002c420, select_lex=0x7f1c9003d5d0) at /mnt/hgfs/repos/percona-server/sql/sql_base.cc:9910 #15 0x00000000014f45b3 in JOIN::optimize_fts_query (this=0x7f1c9003f130) at /mnt/hgfs/repos/percona-server/sql/sql_optimizer.cc:10733 #16 0x00000000014db2b7 in JOIN::optimize (this=0x7f1c9003f130) at /mnt/hgfs/repos/percona-server/sql/sql_optimizer.cc:503 #17 0x00000000015522f4 in st_select_lex::optimize (this=0x7f1c9003d5d0, thd=0x7f1c9002c420) at /mnt/hgfs/repos/percona-server/sql/sql_select.cc:1009 #18 0x0000000001550a50 in handle_query (thd=0x7f1c9002c420, lex=0x7f1c9002e718, result=0x7f1c9003ed90, added_options=0, removed_options=0) at /mnt/hgfs/repos/percona-server/sql/sql_select.cc:164 #19 0x0000000001506a7c in execute_sqlcom_select (thd=0x7f1c9002c420, all_tables=0x7f1c9003e6a8) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:5158 #20 0x00000000015002fe in mysql_execute_command (thd=0x7f1c9002c420, first_level=true) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:2795 #21 0x0000000001507902 in mysql_parse (thd=0x7f1c9002c420, parser_state=0x7f1cd8121590) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:5574 #22 0x00000000014fd1d4 in dispatch_command (thd=0x7f1c9002c420, com_data=0x7f1cd8121cf0, command=COM_QUERY) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:1461 #23 0x00000000014fc0c6 in do_command (thd=0x7f1c9002c420) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:999 #24 0x000000000162c436 in handle_connection (arg=0x4f50f80) at /mnt/hgfs/repos/percona-server/sql/conn_handler/connection_handler_per_thread.cc:300 #25 0x0000000001cada81 in pfs_spawn_thread (arg=0x4f714d0) at /mnt/hgfs/repos/percona-server/storage/perfschema/pfs.cc:2188 #26 0x00007f1ce1c12dc5 in start_thread () from /lib64/libpthread.so.0 #27 0x00007f1ce006d76d in clone () from /lib64/libc.so.6 How to repeat: In debug serveer run the following fragment: CREATE TABLE t1 ( FTS_DOC_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY, contents TEXT COLLATE utf8mb4_bin, FULLTEXT KEY fx_contents (contents) WITH PARSER ngram ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; INSERT INTO t1 VALUES (1, 'xyz mnt'), (2, 'xyz mnt'), (3, 'xyz'); # The query completes with or without bug SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE); # The query returns wrong result set with the bug SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz' IN BOOLEAN MODE) LIMIT 1; # The query crashes with the bug SELECT * FROM t1 WHERE MATCH(contents) AGAINST ('xyz mnt' IN BOOLEAN MODE) LIMIT 5;