Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.37
-
None
-
None
-
CentOS 5.6 x86_64
Description
4 bytes emoji unicode character string search query on UTF8 table
could make mariadb performance down, if there's a lot of empty string('') index key.
Test case
CREATE TABLE test( |
id int AUTO_INCREMENT, |
fd varchar(20), |
primary key(id), |
index ix_fd(fd) |
)engine=innodb default charset=UTF8; |
|
INSERT INTO test(id, fd) VALUES (null, ''), (null, 'matt'), (null, 'pitt'), (null, 'lee'), (null, 'kim'); |
# Run 20 times below query
|
INSERT INTO test SELECT * FROM test; |
|
SELECT * FROM test WHERE fd=*some_4bytes_unicode_char*; |
ref emoji unicode character from below site
http://apps.timwhitlock.info/emoji/tables/unicode
------------------------------------------------------------
4bytes emoji unicode character can't convert to utf8,
But mariadb doesn't check string conversion function's return code. (Check stack trace below)
And Xtradb will search empty string key from ix_fd index.
If ix_fd index has a lot of empty string key, then innodb will read every empty string key's record and pass it to mariadb engine.
But mariadb will check it with original 4 bytes emoji character. (evaluate_join_record()
function of sql_select.cc)
So, client can't get anything from the query, but query takes a lot of time.
iF there's many same type of query, MariaDB server would be busy to read useless record from the table.
Stack trace of copy of use input (4 bytes emoji string)
Thread [18] 21629 [core: 1] (Suspended : Step)
|
Field_varstring::store() at field.cc:6,586 0x6d989c ==> RETURN 2
|
copy_inner() at sql_select.h:1,662 0x5cc27c
|
copy() at sql_select.h:1,552 0x5cc27c
|
create_ref_for_key() at sql_select.cc:8,102 0x5cc27 ==> Ignore return value on "sql_select.cc:8102 tmp.copy();"
|
get_best_combination() at sql_select.cc:7,775 0x5d820f
|
make_join_statistics() at sql_select.cc:3,799 0x5ed818
|
JOIN::optimize() at sql_select.cc:1,218 0x5f0522
|
mysql_select() at sql_select.cc:3,065 0x5f943b
|
handle_select() at sql_select.cc:319 0x5f943b
|
execute_sqlcom_select() at sql_parse.cc:4,689 0x59dcad
|
<...more frames...>
|
==> Suggest
on tmp.copy() code line of sql_select.cc::create_ref_for_key(), MariaDB should check return value of copy().
and if there's some conversion error, MariaDB should throw ERROR not WARNING. or throw ERROR when STRICT_ALL_TABLES sql_mode is on.
Attachments
Issue Links
- relates to
-
MDEV-8613 Full table scan for WHERE indexed_varchar_column <=> 'bad-character'
- Closed