| Bug #28921 | Queries contaning UDF functions are cached | ||
|---|---|---|---|
| Submitted: | 6 Jun 2007 10:57 | Modified: | 23 Jun 2007 7:56 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.44-BK, 5.0.37 | OS: | Any |
| Assigned to: | Damien Katz | CPU Architecture: | Any |
| Tags: | qc | ||
[6 Jun 2007 11:05]
Philip Stoev
udf cache test case
Attachment: udf_cache.test (text/plain), 663 bytes.
[6 Jun 2007 11:06]
Philip Stoev
udf cache result case
Attachment: udf_cache.result (text/plain), 429 bytes.
[11 Jun 2007 13:27]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with your test case and latest 5.0.44-BK on Linux. I've got:
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/udf_cache.result Mon Jun 11 15:10:18 2007
--- r/udf_cache.reject Mon Jun 11 15:10:36 2007
***************
*** 0 ****
--- 1,16 ----
+ set GLOBAL query_cache_size=1355776;
+ drop table if exists t1;
+ CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+ flush status;
+ create table t1 (a char);
+ select metaphon('MySQL') from t1;
+ metaphon('MySQL')
+ show status like 'Qcache_hits';
+ Variable_name Value
+ Qcache_hits 0
+ select metaphon('MySQL') from t1;
+ metaphon('MySQL')
+ show status like 'Qcache_hits';
+ Variable_name Value
+ Qcache_hits 1
+ drop table if exists t1;
So, query chache is used. It is a bug.
[18 Jun 2007 21:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/29040 ChangeSet@1.2494, 2007-06-18 17:55:12-04:00, dkatz@damien-katzs-computer.local +3 -0 Bug #28921 Queries containing UDF functions are cached Fixed runtime to no longer allow the caching of queries with UDF calls.
[19 Jun 2007 0:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/29044 ChangeSet@1.2520, 2007-06-18 20:08:48-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #28921 Queries containg UDF functions are cached Additional edits to the 5.0 ChangeSet|1.2519 that are necessary for the fix to work with the new code structure in 5.1.
[19 Jun 2007 1:11]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/29045 ChangeSet@1.2520, 2007-06-18 21:11:10-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #28921 Queries containg UDF functions are cached Additional edits to the 5.0 ChangeSet|1.2519 that are necessary for the fix to work with the new code structure in 5.1.
[22 Jun 2007 18:07]
Bugs System
Pushed into 5.1.20-beta
[22 Jun 2007 18:09]
Bugs System
Pushed into 5.0.46
[23 Jun 2007 7:56]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.0.46 and 5.1.20 changelogs.
Description: Hello, The manual section 5.13.1 says: "A query also is not cached [if] it refers to user-defined functions (UDFs) or stored functions" However this does not appear to be true, which can be demonstrated using udf_example.so, even though the problem is not in the UDF examples themselves nor in the particular functions being mentioned in the test case. How to repeat: create table a (b char); CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so'; mysql> select lookup('www.nytimes.com') from a; +---------------------------+ | lookup('www.nytimes.com') | +---------------------------+ | 199.239.136.200 | +---------------------------+ mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 5 | +---------------+-------+ mysql> select lookup('www.nytimes.com') from a; +---------------------------+ | lookup('www.nytimes.com') | +---------------------------+ | 199.239.136.200 | +---------------------------+ 1 row in set (0.08 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 6 | +---------------+-------+ In addition, running strace on the mysqld during those queries shows that the second query does not cause the DNS system to be engaged at all. metaphon() also exhibits the same behavoir. Even though it can be argued that it is OK to cache metaphon(), in my humble opinion it is not OK to cache lookup() -- if the DNS server was unavailable at the time of the first query, the query cache will sort of record that the domain is not resolvable and will keep returning stale data. Suggested fix: Make queries containing UDF functions non-cacheable. In the future, it would be really nice to enable the function itself to specify if it is cacheable or not. Right now the function can specify if the function is const_item, however this is for a different purpose and does not help this particular issue -- lookup() does not set const_item and is cached anyway.