Recently, a very strange MySQL problem occurs. Using different select statements to query all datasets produces different records. Select * gets four records, and select field gets three records.
For details, refer to the following query results:
[SQL]
Mysql> select * from table_myisam;
+ ---------- + ------- + ----------- + ------ +
| Datetime | uid | content | type |
+ ---------- + ------- + ----------- + ------ +
| 1 | uid_1 | content_1 | 1 |
| 2 | uid_2 | content_2 | 1 |
| 4 | uid_4 | content_4 | 1 |
| 3 | uid_3 | content_3 | 1 |
+ ---------- + ------- + ----------- + ------ +
4 rows in set (0.00 sec)
Mysql> select uid from table_myisam;
+ ------- +
| Uid |
+ ------- +
| Uid_1 |
| Uid_2 |
| Uid_4 |
+ ------- +
3 rows in set (0.00 sec)
Only three rows of records are obtained by using the select uid, and the records with uid = 'uid _ 3' are lost. I was puzzled. Later I used the check table as a reminder to find the problem.
[SQL]
Mysql> check table table_myisam;
+ -------------------- + ------- + ---------- + --------------------------------------------------------- +
| Table | Op | Msg_type | Msg_text |
+ -------------------- + ------- + ---------- + --------------------------------------------------------- +
| Qitai. table_myisam | check | warning | 1 client is using or hasn' t closed the table properly |
| Qitai. table_myisam | check | warning | Size of indexfile is: 2049 shocould be: 2048 |
| Qitai. table_myisam | check | error | Found 3 keys of 4 |
| Qitai. table_myisam | check | error | upt |
+ -------------------- + ------- + ---------- + --------------------------------------------------------- +
The reason for inconsistent query data is that the index file of table_myisam is damaged, and the corresponding index file table_myisam.MYI is inconsistent with the data file table_myisam.MYD. Select * does not need to traverse each index. You only need to obtain the first record and access it in the order of the linked list. Therefore, the current index corruption does not affect the use of select. The select uid needs to traverse all index items, so it only gets the damaged state and three index records.
The solution is to use repair table to repair table indexes.
[SQL]
Mysql> repair table table_myisam;
+ -------------------- + -------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ -------------------- + -------- + ---------- +
| Qitai. table_myisam | repair | status | OK |
+ -------------------- + -------- + ---------- +
1 row in set (0.00 sec)
After the repair, you can see that the table status changes to normal using check table. You can obtain four records using select * and select uid.
[SQL]
Mysql> check table table_myisam;
+ -------------------- + ------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ -------------------- + ------- + ---------- +
| Qitai. table_myisam | check | status | OK |
+ -------------------- + ------- + ---------- +
1 row in set (0.00 sec)