Description:
I have a very strange behavior on my development server with some tables of a database. Sometimes mysql returns an empty result set, that shouldn't be empty. Same query works on MySQL 5.0.44 / Linux without problems. This queries / results show the whole problem:
mysql> select * from seefahrtsbuch where user_id='4' order by user_id;
Empty set (0.00 sec)
mysql> select count(*) from seefahrtsbuch where user_id='4' order by user_id;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
As you can see, I get an empty result set if I query for:
a) * column
b) special line (where clause, but "where 1" works)
c) order by
How to repeat:
I'm not sure how the bug can be repeated, because it just appears on just one table. I used the following query to get the error:
select * from seefahrtsbuch where user_id='4' order by user_id;
And that's the stucture of the table
CREATE TABLE `seefahrtsbuch` (
`entry_id` int(10) NOT NULL auto_increment,
`user_id` int(10) NOT NULL,
`dienstgrad` varchar(100) default NULL,
`schiffsart` varchar(100) default NULL,
`imoNummer` varchar(100) default NULL,
`schiffsname` varchar(100) default NULL,
`unterscheidungssignal` varchar(100) default NULL,
`reederei` varchar(100) default NULL,
`registerhafen` varchar(100) default NULL,
`fahrgebiet` varchar(200) default NULL,
`start` varchar(200) default NULL,
`ende` varchar(200) default NULL,
PRIMARY KEY (`entry_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;
ALTER TABLE `seefahrtsbuch`
ADD CONSTRAINT `seefahrtsbuch_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
