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?

What do you get when you run `select SQL_NO_CACHE count(*) from tasks `? — Samsquanch, Jul 11 '14 at 14:42
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()`. — Vatev, Jul 11 '14 at 14:46
`select SQL_NO_CACHE count(*) from tasks` returns 3057 rows. `select SQL_NO_CACHE * from tasks` returns 2344 rows. — Maicon Vinicius Nunes, Jul 11 '14 at 14:48
Are your tables MYISAM? I ask because of this article: http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ — Samsquanch, Jul 11 '14 at 14:49
If it is a MyISAM table try running a `REPAIR TABLE tbl_name`. — Vatev, Jul 11 '14 at 14:52

score 5 · Accepted Answer · answered Jul 14 '14 at 14:23

5

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]

answered Jul 14 '14 at 14:23

Vikas

449
4
7

1

Looks like the short answer is already in the comments. But I missed it initially so this might help someone. – Vikas Jul 14 '14 at 14:25
1

Now it is @ReinstateMonica3167040 ;) – Maicon Vinicius Nunes Jan 05 '22 at 18:41

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

1 Answers1

Linked