Please update the mysql. It could solve this issue too, where adding an
index breaks a query.
Below mysql run describing the situation:
1. Execute query -> no results.
2. Remove index
3. Execute query -> Correct result found
4. Re-create index
5. Execute query again -> no results.
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.1.49-1ubuntu8.1 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id =
`units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id =
22));
Empty set (0.00 sec)
mysql> drop index index_units_users_on_unit_id on units_users;
Query OK, 330 rows affected (0.26 sec)
Records: 330 Duplicates: 0 Warnings: 0
mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id =
`units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id =
22));
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
| id | company_id | name | address_id | phone | fax | email | www
| valid_to | created_by_user_id | modified_by_user_id | created_at |
updated_at | receipt_number | lock_version | type |
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
| 72 | 22 | Company 1 | NULL | | | | | NULL
| NULL | NULL | 2009-11-03 07:45:55 |
2010-08-25 20:32:29 | 69760 | 21722 | NULL |
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
1 row in set (0.00 sec)
mysql> create index index_units_users_on_unit_id on units_users (unit_id);
Query OK, 330 rows affected (0.32 sec)
Records: 330 Duplicates: 0 Warnings: 0
mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id =
`units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id =
22));
Empty set (0.00 sec)
mysql>
** Tags added: query
--
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to mysql-5.1 in Ubuntu.
https://bugs.launchpad.net/bugs/706988
Title:
Major Bug in MySQL 5.1.49 up to 5.1.52
--
Ubuntu-server-bugs mailing list
Ubuntu-server-bugs@lists.ubuntu.com
Modify settings or unsubscribe at:
https://lists.ubuntu.com/mailman/listinfo/ubuntu-server-bugs
