Bug #28897 UUID() returns non-unique values when query cache is enabled
Submitted: 5 Jun 2007 14:05 Modified: 18 Jun 2007 17:06
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.41, 5.0.44-BK OS:Any
Assigned to: Damien Katz CPU Architecture:Any
Tags: qc

[5 Jun 2007 14:05] Philip Stoev
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
[5 Jun 2007 17: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 20: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 1: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 14: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 18: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 20: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 21: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 4:50] Bugs System
Pushed into 5.0.44
[16 Jun 2007 4:51] Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 17: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.)