| Bug #28897 | UUID() returns non-unique values when query cache is enabled | ||
|---|---|---|---|
| Submitted: | 5 Jun 2007 16:05 | Modified: | 18 Jun 2007 19:06 |
| Reporter: | Philip Stoev | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 5.0.41, 5.0.44-BK | OS: | Any |
| Assigned to: | Bugs System | Target Version: | |
| Tags: | qc | ||
[5 Jun 2007 19:06]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.44-BK on Linux:
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading 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.0.44-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 999424 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)
mysql> create table a (b char);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values ('c');
Query OK, 1 row affected (0.00 sec)
mysql> select UUID(), b from a;
+--------------------------------------+------+
| UUID() | b |
+--------------------------------------+------+
| 76fbe7a6-6326-102a-a5db-000c299a6965 | c |
+--------------------------------------+------+
1 row in set (0.00 sec)
mysql> select UUID(), b from a;
+--------------------------------------+------+
| UUID() | b |
+--------------------------------------+------+
| 76fbe7a6-6326-102a-a5db-000c299a6965 | c |
+--------------------------------------+------+
1 row in set (0.00 sec)
mysql> set global query_cache_size=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select UUID(), b from a;
+--------------------------------------+------+
| UUID() | b |
+--------------------------------------+------+
| 7f9167ba-6326-102a-a5db-000c299a6965 | c |
+--------------------------------------+------+
1 row in set (0.00 sec)
mysql> select UUID(), b from a;
+--------------------------------------+------+
| UUID() | b |
+--------------------------------------+------+
| 8045c1ce-6326-102a-a5db-000c299a6965 | c |
+--------------------------------------+------+
1 row in set (0.00 sec)
[12 Jun 2007 22:13]
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/28606 ChangeSet@1.2533, 2007-06-12 16:12:47-04:00, dkatz@damien-katzs-computer.local +4 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Changed UUID() from non-special function in lex.h to a special one parsed by sql_yacc.yy and set Lex->safe_to_cache_query to 0.
[13 Jun 2007 3:47]
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/28620 ChangeSet@1.2533, 2007-06-12 21:46:50-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Removed the ability to cache queries with UUID().
[13 Jun 2007 16:25]
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/28674 ChangeSet@1.2533, 2007-06-13 10:24:33-04:00, dkatz@damien-katzs-computer.local +3 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Removed the ability to cache queries with UUID().
[13 Jun 2007 20:05]
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/28694 ChangeSet@1.2533, 2007-06-13 14:05:14-04:00, dkatz@damien-katzs-computer.local +3 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Removed the ability to cache queries with UUID().
[13 Jun 2007 22:26]
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/28699 ChangeSet@1.2533, 2007-06-13 14:23:25-04:00, dkatz@damien-katzs-computer.local +3 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Removed the ability to cache queries containing "UUID()".
[13 Jun 2007 23:25]
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/28706 ChangeSet@1.2495, 2007-06-13 17:25:16-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #28897 UUID() returns non-unique values when query cache is enabled Removed the ability to cache queries with UUID() and UUID_SHORT().
[16 Jun 2007 6:50]
Bugs System
Pushed into 5.0.44
[16 Jun 2007 6:51]
Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 19:06]
Paul DuBois
Noted in 5.0.44, 5.1.20 changelogs. Queries that used UUID() were incorrectly allowed into the query cache. (This should not happen because UUID() is non-deterministic.)
Description: Hello, The UUID() function is not listed in the SQL functions that disable the query cache and indeed queries containing UUID() are cached which in turns defeats the prupose of the function -- to always provide a unique value. While the UUID() function may be used in contexts where the query cache does not kick in, e.g. if the query is just "SELECT UUID()" without any tables, nevertheless the correct semantics should be preserved under all circumstances. Therefore I am labeling this as a S2 bug. How to repeat: mysql> create table test.a (b char); mysql> insert into a values ('c'); mysql> select UUID(), b from a; +--------------------------------------+------+ | UUID() | b | +--------------------------------------+------+ | 1a44814a-5fce-102a-927e-000c29e05c93 | c | +--------------------------------------+------+ 1 row in set (0.00 sec) mysql> select UUID(), b from a; +--------------------------------------+------+ | UUID() | b | +--------------------------------------+------+ | 1a44814a-5fce-102a-927e-000c29e05c93 | c | +--------------------------------------+------+ 1 row in set (0.00 sec) The two values are identical even though they should not be. We create a table and populate it in order to force the query cache to kick in. Suggested fix: UUID() is one of the non-special functions defined in a list in lex.h. Instead, it needs to have its own definition in sql_yacc.yy and this branch must set lex->safe_to_cache_query=0