| Bug #15973 | running a query using MyISAM tables returns 0 rows, then 1 row | ||
|---|---|---|---|
| Submitted: | 23 Dec 2005 20:11 | Modified: | 22 Jun 2006 15:18 |
| Reporter: | Matthew Wilson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.17/5.0.19 BK | OS: | Linux (Linux) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[23 Dec 2005 20:11]
Matthew Wilson
[23 Dec 2005 20:14]
Matthew Wilson
test case SQL file
Attachment: testcase.sql.gz (application/x-gzip, text), 6.62 KiB.
[23 Dec 2005 20:17]
Matthew Wilson
To execute the test case: [msw@localhost msw]$ echo -e 'drop database test;\ncreate database test character set latin1;\n' | mysql -u root mysql; mysql -u root test < testcase.sql Expected results (this is what you get when running Innodb): depNum item version timeStamps flavor -1 bash:runtime /localhost@rpl:linux/0-1-1 1135360246.229 1#x86|5#use:ssl depNum item version timeStamps flavor -1 bash:runtime /localhost@rpl:linux/0-1-1 1135360246.229 1#x86|5#use:ssl Actual results (this is what you get when running MySIAM): depNum item version timeStamps flavor -1 bash:runtime /localhost@rpl:linux/0-1-1 1135360246.229 1#x86|5#use:ssl In my configuration, I have no password for root. I use "default-table-type=innodb" in /etc/my.cnf when testing against Innodb.
[23 Dec 2005 20:33]
Matthew Wilson
mysqld.trace, starting at the query in question, when using MyISAM
Attachment: myisam-mysqld.trace.gz (application/x-gzip, text), 60.63 KiB.
[23 Dec 2005 20:33]
Matthew Wilson
mysqld.trace, starting at the query in question, when using InnoDB
Attachment: innodb-mysqld.trace.gz (application/x-gzip, text), 69.49 KiB.
[24 Dec 2005 0:08]
MySQL Verification Team
Thank you for the bug report. I edited the script provide converting to lowercase for to avoid errors with table not exists. MyISAM: Query OK, 1 row affected (0.00 sec) Empty set (0.03 sec) +--------+--------------+----------------------------+----------------+-----------------+ | depnum | item | version | timestamps | flavor | +--------+--------------+----------------------------+----------------+-----------------+ | -1 | bash:runtime | /localhost@rpl:linux/0-1-1 | 1135360246.229 | 1#x86|5#use:ssl | +--------+--------------+----------------------------+----------------+-----------------+ 1 row in set (0.02 sec) InnoDB: Query OK, 1 row affected (0.00 sec) +--------+--------------+----------------------------+----------------+-----------------+ | depnum | item | version | timestamps | flavor | +--------+--------------+----------------------------+----------------+-----------------+ | -1 | bash:runtime | /localhost@rpl:linux/0-1-1 | 1135360246.229 | 1#x86|5#use:ssl | +--------+--------------+----------------------------+----------------+-----------------+ 1 row in set (0.03 sec) +--------+--------------+----------------------------+----------------+-----------------+ | depnum | item | version | timestamps | flavor | +--------+--------------+----------------------------+----------------+-----------------+ | -1 | bash:runtime | /localhost@rpl:linux/0-1-1 | 1135360246.229 | 1#x86|5#use:ssl | +--------+--------------+----------------------------+----------------+-----------------+ 1 row in set (0.02 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.19-debug | +--------------+ 1 row in set (0.00 sec)
[24 Dec 2005 1:43]
Matthew Wilson
Aah, yes. I omitted an important part of my configuration: sql_mode=TRADITIONAL,NO_AUTO_VALUE_ON_ZERO lower_case_table_names=1 Thank you for the speedy verification.
[22 Jun 2006 15:18]
Sergey Vojtovich
Tested with mysql-5.0.17, mysql-5.0.19, mysql-5.0.23bk. I was able to repeat this problem with mysql-5.0.17, but not with mysql-5.0.19 and mysql-5.0.23bk. That means this problem was likely fixed in mysql-5.0.19. Please try newer mysql version. Feel free to reopen this bug if this problem still persists.
