| Bug #14021 | error -1 from storage engine on CREATE INDEX or OPTIMIZE TABLE | ||
|---|---|---|---|
| Submitted: | 14 Oct 2005 3:22 | Modified: | 21 Aug 2006 8:27 |
| Reporter: | Dan Udey | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.13-rc-standard-log | OS: | MacOS (Mac OS X Tiger 10.4.2) |
| Assigned to: | CPU Architecture: | Any | |
[14 Oct 2005 14:36]
Valeriy Kravchuk
Thank you for a problem report.
Please, send the my.cnf file content and df -k command results (as it is an InnoDB table, we have to check, is it enough space to on the filesystem where tablespace is located etc.).
Is there anything strange in the error log for the appropriate period?
By the way, I was not able to repeat on 5.0.13-rc-nt with the following actions:
mysql> CREATE TABLE `memtest` (
-> `test` int(11) NOT NULL auto_increment,
-> `hash` char(32) NOT NULL,
-> PRIMARY KEY (`test`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.55 sec)
mysql> insert into memtest(hash) values (md5('abc'));
Query OK, 1 row affected (0.11 sec)
mysql> select * from memtest;
+------+----------------------------------+
| test | hash |
+------+----------------------------------+
| 1 | 900150983cd24fb0d6963f7d28e17f72 |
+------+----------------------------------+
1 row in set (0.01 sec)
mysql> insert into memtest(hash) select md5(hash) from memtest;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into memtest(hash) select md5(hash) from memtest;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
...
mysql> insert into memtest(hash) select md5(hash) from memtest;
Query OK, 262144 rows affected (37.83 sec)
Records: 262144 Duplicates: 0 Warnings: 0
mysql> select count(*) from memtest;
+----------+
| count(*) |
+----------+
| 524288 |
+----------+
1 row in set (4.40 sec)
mysql> create index hashes on memtest(test);
Query OK, 524288 rows affected (1 min 14.42 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> show create table memtest;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| memtest | CREATE TABLE `memtest` (
`test` int(11) NOT NULL auto_increment,
`hash` char(32) NOT NULL,
PRIMARY KEY (`test`),
KEY `hashes` (`test`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)
By the way, it is not good to create 2 indexes (keys) on 1 column, I think. Isn't it a mistake you made occationally?
[15 Nov 2005 0:00]
Bugs System
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".
Description: On a table containing 444571 MD5 hashes, I recently attempted to add an index, but every attempt returned an error -1 from InnoDB. I've discovered that several commands seem to trigger this, though I cannot determine what causes it. I have checked disk space and permissions and all is ok, other tables can be created fine, I can retrieve the data from the table without problem, and a 'CREATE TABLE foo SELECT * FROM memtest;' succeeded without incident. I can NOT, however, INSERT data into the database. This is why I am marking this a 'Critical' bug. This is a test database made to experiment with inserting large queries from PHP into MySQL. It was originally created as a MEMORY table (hence the name), then ALTER TABLE was used to change it to a MyISAM table and more rows were added, and then it was changed to an InnoDB table, which it is now, about two weeks ago. Enclosed is a transcript from the MySQL command-line client showing various commands and their results. mysql> \u test Database changed mysql> show create table memtest\G *************************** 1. row *************************** Table: memtest Create Table: CREATE TABLE `memtest` ( `test` int(11) NOT NULL auto_increment, `hash` char(32) NOT NULL, PRIMARY KEY (`test`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> create index hashes on memtest(test); ERROR 1030 (HY000): Got error -1 from storage engine mysql> show warnings; +-------+------+-------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------+ | Error | 1030 | Got error -1 from storage engine | | Error | 1025 | Error on rename of './test/memtest' to './test/#sql2-bf2-3' (errno: -1) | +-------+------+-------------------------------------------------------------------------+ mysql> create index hashes on memtest(test,hash); ERROR 1030 (HY000): Got error -1 from storage engine mysql> alter table memtest add column foo int not null default 0; ERROR 1030 (HY000): Got error -1 from storage engine mysql> insert into memtest (hash) values ('cfcd208495d565ef66e7dff9f98764da'); ERROR 1030 (HY000): Got error -1 from storage engine mysql> optimize table memtest; +--------------+----------+----------+----------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------------------------------+ | test.memtest | optimize | error | Got error -1 from storage engine | | test.memtest | optimize | status | Operation failed | +--------------+----------+----------+----------------------------------+ mysql> check table memtest extended; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | test.memtest | check | status | OK | +--------------+-------+----------+----------+ bash-3.00# ps auxw | grep mysql mysql 3058 0.1 2.8 62160 22096 ?? S 10:56PM 0:06.31 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local mysql 2970 0.0 0.8 39564 6620 ?? SNs 10:55PM 0:00.26 /System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/Met root 3039 0.0 0.1 27800 704 ?? S 10:56PM 0:00.04 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/l root 3363 0.0 0.0 29864 0 p3 R+ 11:18PM 0:00.00 grep mysql bash-3.00# pwd /usr/local/mysql/data bash-3.00# ls -ld test drwx------ 17 mysql wheel 578 Oct 13 22:56 test/ bash-3.00# cd test bash-3.00# ls -l total 32352 -rw-rw---- 1 mysql wheel 8694 Oct 5 18:43 attendee.frm -rw-rw---- 1 mysql wheel 0 Oct 9 08:25 datetest.MYD -rw-rw---- 1 mysql wheel 1024 Oct 9 08:25 datetest.MYI -rw-rw---- 1 mysql wheel 8564 Oct 9 08:25 datetest.frm -rw-rw---- 1 mysql wheel 65 Sep 28 09:30 db.opt -rw-rw---- 1 mysql wheel 8740 Oct 5 18:43 equip_drop.frm -rw-rw---- 1 mysql wheel 16449127 Oct 13 22:48 hashestwo.MYD -rw-rw---- 1 mysql wheel 1024 Oct 13 22:48 hashestwo.MYI -rw-rw---- 1 mysql wheel 8590 Oct 13 22:48 hashestwo.frm -rw-rw---- 1 mysql wheel 8590 Oct 6 15:20 memtest.frm -rw-rw---- 1 mysql wheel 8688 Oct 5 18:43 period.frm -rw-rw---- 1 mysql wheel 8686 Oct 5 18:43 raid.frm -rw-rw---- 1 mysql wheel 0 Sep 28 09:41 test.MYD -rw-rw---- 1 mysql wheel 1024 Sep 28 09:41 test.MYI -rw-rw---- 1 mysql wheel 8560 Sep 28 09:33 test.frm How to repeat: I do not know how to reproduce this bug nor what causes it, and I am not aware at what point this bug occurred. The last restart of this machine was two days before this bug was noticed, the last insert of data was about two days before that.