| Bug #55016 | Doesn't search but insert in query cache queries leading one-line comment | ||
|---|---|---|---|
| Submitted: | 6 Jul 2010 4:29 | Modified: | 6 Jul 2010 9:09 |
| Reporter: | Oleg Tsarev | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S5 (Performance) |
| Version: | Any | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Jul 2010 4:29]
Oleg Tsarev
[6 Jul 2010 4:38]
Valeriy Kravchuk
What exact version of MySQL serverr, x.y.z, and on what OS do you use? I do not see the problem with recent 5.1.49 on Mac OS X, for example: valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> show status like "Qcache_queries_in_cache"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.03 sec) mysql> show status like "Qcache_inserts"; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 0 | +----------------+-------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_hits"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql> # with comment first mysql> select * from t1; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.02 sec) mysql> # with comment first mysql> select * from t1; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_queries_in_cache"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_inserts"; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 1 | +----------------+-------+ 1 row in set (0.01 sec) mysql> show status like "Qcache_hits"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> # with comment first mysql> select * from t1; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_queries_in_cache"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_inserts"; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 1 | +----------------+-------+ 1 row in set (0.00 sec) mysql> show status like "Qcache_hits"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 2 | +---------------+-------+ 1 row in set (0.00 sec)
[6 Jul 2010 4:43]
Oleg Tsarev
I use 5.1 version. But i check source code, nothing changes in 5.5.
* file sql_cache.cc
* function int Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
/*
Skip '(' characters in queries like following:
(select a from t1) union (select a from t1);
*/
while (sql[i]=='(')
i++;
/*
Test if the query is a SELECT
(pre-space is removed in dispatch_command).
First '/' looks like comment before command it is not
frequently appeared in real life, consequently we can
check all such queries, too.
*/
if ((my_toupper(system_charset_info, sql[i]) != 'S' ||
my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
sql[i] != '/')
{
DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
goto err;
}
if (query_length > 20 && has_no_cache_directive(&sql[i+6]))
{
/*
We do not increase 'refused' statistics here since it will be done
later when the query is parsed.
*/
DBUG_PRINT("qcache", ("The statement has a SQL_NO_CACHE directive"));
goto err;
}
This is LOL.
I also can fill new bugs, to queries like:
"( SELECT * from t1 )"
"(() SELECT * from t1 )"
so one.
[6 Jul 2010 5:18]
Oleg Tsarev
Also, Valeriy: Your mysql client strip comments befoire send to server. Please run mysql as "msyql -c..." Thank you, Oleg.
[6 Jul 2010 7:04]
Valeriy Kravchuk
This is a duplicate of bug #40986 actually.
[6 Jul 2010 8:41]
Oleg Tsarev
No, this is NOT duplicate. Bug what you linked coverage only C-style comments /* ... */. As you can see in source code, only this comments work correctly. My example work with one-line comments: "# one-line comments (doesn't work) select..." "-- one-line comments (doesn't work) select..." "/* C-style comment (does wor!k) select..."
[6 Jul 2010 9:01]
Valeriy Kravchuk
Description of bug #40986 starts with: "SELECT statements with leading line comments (beginning with '#' or '--') are still not cached correctly by the Query Cache." So, it is about one-line comments. /* */ processing was fixed long time ago.
[6 Jul 2010 9:09]
Oleg Tsarev
Valeriy, Thank you, i understand.
