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?

Borne

What happens if you try SELECT * FROM tasks LIMIT 4000;? – Leatherback

Same result: 2344 rows in set (0,02 sec) – Borne

What do you get when you run select SQL_NO_CACHE count(*) from tasks ? – Limacine

This might not be relevant, but the version that you posted is for the MySQL command line client and not for the server. Try SELECT version(). – Gallegos

select SQL_NO_CACHE count(*) from tasks returns 3057 rows. select SQL_NO_CACHE * from tasks returns 2344 rows. – Borne

SELECT version() shows version 5.1.51 as well. – Borne

Are your tables MYISAM? I ask because of this article: mysqlperformanceblog.com/2007/04/10/count-vs-countcol – Limacine

Can you show us your schema? – Snapback

If it is a MyISAM table try running a REPAIR TABLE tbl_name. – Gallegos

Thank you @Vatev, REPAIR TABLE solved the issue! – Borne

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]

Dialect

Looks like the short answer is already in the comments. But I missed it initially so this might help someone. – Dialect

Now it is @ReinstateMonica3167040 ;) – Borne