Bug #12056 ERROR 1030 (HY000): Got error 127 from storage engine - after ALTER TABLE
Submitted: 20 Jul 2005 13:59 Modified: 21 Jul 2005 18:09
Reporter: Dino Tsoumakis Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 OS:Linux (Suse Linux Ent. 8 (2.4.19-4GB)
Assigned to: CPU Architecture:Any

[20 Jul 2005 13:59] Dino Tsoumakis
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.
[21 Jul 2005 15:43] Dino Tsoumakis
I found out that this seems to have something to do with disk space. On the system I tried, there are only 5GB left. But this should be enough. At the time the temp-files are created, there are still 3GB left.
I tried to delete some old files so that I have now 6GB free. And now it worked fine for me.
So I'm not sure about, whether this is a bug or not.
[21 Jul 2005 18:09] Jorge del Conde
Hi!

I was unable to reproduce this bug using a 1GB table and 2 GB left in my FC4 machine.

Can you please give us a reproducible test-case that repeats this behaviour ?

Thanks!