Query returns empty set Mysql 5.1.49 but returns something on 5.0-ish_问答_开发者

I've done an awful lot of searching and troubleshooting to try and solve this myself.

My situation is that I've updated a host to Debian Squeeze (MySQL 5.1.49) from Debian Lenny (5.0-ish) and a previously working query that returned the expected result now returns an empty set.

So here goes on the background. In order to see if the data was problematic, I did a mysqldump of the database. I then copied that to my development machine (Mac OS X 10.6.8 with MySQL installed via Homebrew) and set up the database there. The query returned the results expected!

The code is based on an open source application, so I went back and did a clean install and database bootstrap on a Lenny host. I confirmed that the query returns what is expected.

I also did a fresh install of the application on a clean installed (not upgraded host) on Squeeze. The fresh install on Squeeze of the open source application works as expected.

I did a mysqldump from the fresh install of the application on Lenny. I then, on the clean Squeeze host, created a new database and loaded the Lenny dump into it. The query no longer returned the expected result. The same situation as the database upgraded through to Squeeze.

Here's a description of how things stand with the data.

First the tables:

mysql> describe roles;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | int(11)     | NO   | PRI | NULL    | auto_increment |
| name              | varchar(40) | YES  |     | NULL    |                |
| authorizable_type | varchar(30) | YES  |     | NULL    |                |
| authorizable_id   | int(11)     | YES  |     | NULL    |                |
| created_at        | datetime    | YES  |     | NULL    |                |
| updated_at        | datetime    | YES  |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

mysql> describe roles_users;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| user_id    | int(11)  | YES  | MUL | NULL    |       |
| role_id    | int(11)  | YES  | MUL | NULL    |       |
| created_at | datetime | YES  |     | NULL    |       |
| updated_at | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

You guessed it, these are from an authorization plugin circa Rails 2.1ish. Now in use by an application that has been upgraded to Rails 2.3.5 at this point.

I've verified that the descriptions for the tables are the same on both hosts.

Next the data:

mysql> select * from roles where id = 1;
+----+------------+-------------------+-----------------+---------------------+---------------------+
| id | name       | authorizable_type | authorizable_id | created_at          | updated_at          |
+----+------------+-------------------+-----------------+---------------------+---------------------+
|  1 | site_admin | Basket            |               1 | 2009-05-27 00:14:22 | 2009-05-27 00:14:22 |
+----+------------+-------------------+-----------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from roles_users where role_id = 1 and user_id = 1;
+---------+---------+---------------------+---------------------+
| user_id | role_id | created_at          | updated_at          |
+---------+---------+---------------------+---------------------+
|       1 |       1 | 2009-05-27 00:14:22 | 2009-05-27 00:14:22 |
+---------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

Again, both hosts return the same result.

Here comes the query as expected to return (constructed by the authorization plugin to verify a user has the correct role on correct object in the system):

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

And here it is again, on the Debian Squeeze host, returning the empty set:

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
Empty set (0.00 sec)

Any ideas? Anyone else experience this?

Update for results requested from comment:

Here's without the LIMIT 1:

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 ));
Empty set (0.00 sec)

Here's dropping the roles_users.user_id = 1 from the where clause:

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles`.`id` = 1 ;
+----+
| id |
+----+
|  1 |
...
3 rows in set (0.00 sec)

Here's the above query, but showing the roles_users.user_id in the select:

mysql> SELECT `roles`.id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles`.`id` = 1 ;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
...
3 rows in set (0.00 sec

and finally here's dropping the roles.id from where clause, but keeping the roles_users.user_id:

mysql> SELECT `roles`.id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles_users`.user_id = 1;                                                                             
+----+---------+
| id | user_id |
+----+---------+
...
|  1 |   开发者_如何转开发    1 |
...
9 rows...

I've found the root cause that makes this issue happen on Debian Squeeze MySQL 5.1.49; the query will return an empty set when the roles_users table has more than one distinct user_id for a role_id.

I.e. if I have this:

mysql> SELECT `roles_users`.role_id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id order by role_id;
+---------+---------+
| role_id | user_id |
+---------+---------+
|       1 |       1 |
|       2 |       1 |
|       3 |       1 |
|       4 |       1 |
|       5 |       1 |
|       6 |       2 |
+---------+---------+
6 rows in set (0.01 sec)

I get what I expect for this:

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1) AND  ((`roles_users`.user_id = 1)) LIMIT 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

but when I have this (an addition user_id that has role_id 1):

mysql> SELECT `roles_users`.role_id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id order by role_id;
+---------+---------+
| role_id | user_id |
+---------+---------+
|       1 |       1 |
|       1 |       5 |
|       2 |       1 |
|       3 |       1 |
|       4 |       1 |
|       4 |       5 |
|       5 |       1 |
|       6 |       2 |
+---------+---------+
8 rows in set (0.00 sec)

I get this:

mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
Empty set (0.00 sec)

So there are two possibilities.

Either the query, that works with that data set (more than one user_id with the same role_id) was always invalid and MySQL 5.0.51a-24+lenny4 worked with it anyway even though it shouldn't have OR there is a bug in MySQL 5.1.49-3 (Debian).

I'm going to report on the forum for bugs at MySQl and see what response I get.

To fix the problem upgrade to 5.1.58-1~dotdeb.1 on Squeeze. I used dotdeb because there wasn't a Squeeze backports later version of MySQL.

Here are my basic steps (as root):

cp /etc/mysql/my.cnf to temp location NECESSARY ONLY IF you have custom configuration
aptitude purge libmysqlclient-dev libmysqlclient16 mysql-client mysql-client-5.1 mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1
set up /etc/apt/sources.list.d/dotdeb.list as per http://www.dotdeb.org/instructions/
aptitude update
wget http://www.dotdeb.org/dotdeb.gpg
cat dotdeb.gpg | apt-key add -
aptitude update
be prepared with your previous mysql root user's password
aptitude install libmysqlclient libmysqlclient-dev mysql-server mysql-common mysql-client libmysql-ruby1.8 mytop apache2-threaded-dev

NECESSARY ONLY IF you have custom configuration as mentioned above: