Description:
When doing an ALTER TABLE on a big MyISAM table from an old 3.23.53 release, temp files are created, the indexes are recreated but then the temp files are deleted and the old table files remain the same.
Everytime I try the ALTER TABLE command, I get an "ERROR 1030 (HY000): Got error 127 from storage engine" error.
Before trying the statement I used myisamchk -r to make sure all files are fine.
There is no entry in the error log.
Now I can't execute an ALTER TABLE statement on this table. I have no workaround so far.
How to repeat:
I used a table with the following format:
mysql> show table status;
...
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| ResourceUsage | MyISAM | 7 | Dynamic | 5041466 | 155 | 786360240 | 4294967295 | 538050560 | 0 | NULL | 2004-12-23 15:14:13 | 2005-07-07 10:39:20 | 2004-12-23 15:14:21 | latin1_swedish_ci | NULL | | |
mysql> desc ResourceUsage;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| OrderId | int(11) | YES | MUL | NULL | |
| TestcaseId | int(11) | | | 0 | |
| ExecutionId | int(11) | | PRI | 0 | |
| TCTestDefinitionId | int(11) | | | 0 | |
| Timestamp | bigint(19) | | MUL | 0 | |
| Start | bigint(19) | | | 0 | |
| End | bigint(19) | | | 0 | |
| Duration | bigint(17) | | | 0 | |
| Interface | int(11) | | | 0 | |
| ResourceHost | varchar(100) | | MUL | | |
| ResourceIndex | int(11) | | | 0 | |
| ResourceType | varchar(100) | | MUL | | |
| Location | varchar(100) | | MUL | | |
| Msisdn | varchar(100) | | | | |
| Side | varchar(32) | | PRI | | |
| InterfaceInfo | varchar(100) | | | | |
+--------------------+--------------+------+-----+---------+-------+
16 rows in set (0.02 sec)
The db files are this:
-rw-rw---- 1 mysql daemon 784573360 Jul 20 12:51 ResourceUsage.MYD
-rw-rw---- 1 mysql daemon 371182592 Jul 20 13:05 ResourceUsage.MYI
-rw-rw---- 1 mysql daemon 9082 Jul 7 10:54 ResourceUsage.frm
I want to execute:
mysql> ALTER TABLE ResourceUsage ADD COLUMN InsertId bigint(20) NOT NULL AFTER InterfaceInfo;
mysql> show processlist;
...
| 1477 | root | localhost | calltab2 | Query | 1843 | Repair by sorting | ALTER TABLE ResourceUsage ADD COLUMN InsertId bigint(20) NOT NULL AFTER InterfaceInfo
Then temp files are created by mysql:
-rw-rw---- 1 mysql daemon 786256644 Jul 20 14:30 #sql-2ab_5c5.MYD
-rw-rw---- 1 mysql daemon 407198720 Jul 20 14:44 #sql-2ab_5c5.MYI
-rw-rw---- 1 mysql daemon 9216 Jul 20 14:14 #sql-2ab_5c5.frm
After a while I get:
ERROR 1030 (HY000): Got error 127 from storage engine
--------------
myisamchk shows up:
test:/var/lib/mysql/calltab2 # myisamchk /var/lib/mysql/calltab2/ResourceUsage.MYI
Checking MyISAM file: /var/lib/mysql/calltab2/ResourceUsage.MYI
Data records: 5040477 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check data record references index: 7
- check record links
test:/var/lib/mysql/calltab2 #
--------------
My /etc/my.cnf is:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
server-id = 1
skip-locking
skip-innodb
max_connections = 200
key_buffer_size = 128M
max_allowed_packet = 1M
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 1M
table_cache = 128
myisam_sort_buffer_size = 16M
thread_cache = 16
thread_stack = 128K
[mysqld_safe]
log-error=/var/lib/mysql/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 16M
write_buffer_size = 16M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 16M
write_buffer_size = 16M
[mysqlhotcopy]
interactive-timeout
Suggested fix:
Will reply to the email where the patch was submitted.