MySQL Bugs: #48687: myisamchk -rq fails on tables with a single column

Bug #48687	myisamchk -rq fails on tables with a single column
Submitted:	11 Nov 2009 12:43	Modified:	11 Dec 2009 15:03
Reporter:	Andreas Andersen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.1.37-1ubuntu5	OS:	Linux (ubuntu 9.10)
Assigned to:		CPU Architecture:	Any
Tags:	MyISAMchk

Description:
In the manual, under http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html there are instructions for how to speed up inserts when loading large amounts of data into a MyISAM table, namely the procedure when using myisamchk while importing to turn of indices. This does not work on tables with just a single primary key column.

How to repeat:
1. Create a table with a single column
2. Turn off indices for the newly create table using myisamchk --keys-used=0 -rq
3. Insert two rows into this table (a single row will not do)
4. Activate the indices again using myisamchk -rq. The command will fail with the following message:
myisamchk: error: Couldn't fix table with quick recovery: Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table '/var/lib/mysql/test/t' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

Below is a bash script which does everything:
#!/bin/bash
echo "DROP TABLE IF EXISTS t;\
CREATE TABLE t (\
  id int(11) NOT NULL auto_increment,\
  PRIMARY KEY  (id)\
) ENGINE=MyISAM AUTO_INCREMENT=11371 DEFAULT CHARSET=latin1;" | mysql -u root -Dtest

echo "FLUSH TABLES"  | mysql -u root -Dtest
sudo myisamchk --keys-used=0 -rq /var/lib/mysql/test/t

echo "INSERT INTO t(id) VALUES(1), (2)" | mysql -u root -Dtest

sudo myisamchk -rq /var/lib/mysql/test/t
echo "FLUSH TABLES"  | mysql -u root -Dtest

Please, check with a newer version, 5.1.40 at least. I can not repeat this:

77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(id int auto_increment, primary key(id)) engine=MyISAM;
Query OK, 0 rows affected (0.40 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
77-52-7-73:6.0-codebase openxs$ bin/myisamchk -uroot --keys-used=0 -rq var/test/t1
myisamchk: --unpack can't be used with --quick or --sort-records
77-52-7-73:6.0-codebase openxs$ bin/myisamchk -uroot --keys-used=0 -rq var/test/t1.MYI
myisamchk: --unpack can't be used with --quick or --sort-records
77-52-7-73:6.0-codebase openxs$ bin/myisamchk -uroot --keys-used=0 var/test/t1.MYI
- recovering (with keycache) MyISAM-table 'var/test/t1.MYI'
Data records: 0
77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
77-52-7-73:6.0-codebase openxs$ bin/myisamchk -uroot -rq var/test/t1.MYI- recovering (with keycache) MyISAM-table 'var/test/t1.MYI'
Data records: 0
77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".