Mysql "select * from" doesn't return all rows

Question

I'm used to PostgreSQL and don't understand this behaviour on MySQL.

This table (from SugarCRM) has 3057 rows:

mysql> SELECT  count(*) FROM tasks ;
+----------+
| count(*) |
+----------+
|     3057 |
+----------+

But when running SELECT * FROM tasks :

mysql> SELECT * FROM tasks ;
...
2344 rows in set (0,02 sec)

I'm using a fairly old version of MySQL, but the issue is I'm just trying to dump the database and restore to a new version.

# mysql --version
mysql  Ver 14.14 Distrib 5.1.51, for slackware-linux-gnu (x86_64) using  EditLine wrapper

Do you have any ideas?

Answer 1

Generally MyISAM table format is very reliable but tables can sometime get corrupted for various reasons like Hardware failures, mysqld process is killed while a write operation is underway, untimely shutdowns or bugs in the MySQL or MyISAM code. If you're using a very old version then bugs are likely.

Before repairing it is recommended that you backup. To repair

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]