CODEWITHSUNDEEP
mysqlinnodb
Shawn
Shawn

Reputation: 34229

Mysql 'Got error -1 from storage engine' error

I have a myism table 'test' which holds some out-dated data, now I want to recreate the table, all columns the same except that I changed the storage from myism to innodb. The dumped sql I used to recreate the table is like:

drop table test;
create table test ( ... )
engine=innodb

insert into test(...) values(...)

that's where I got the error "Got Error -1 from storage engine", I have googled around, most of the results focus on corrupted innodb tables. While for my case I don't think it's broken, it's just something I missed at the drop and create statements.

Another thing is that is after a executed the above sql, all that's left for table test is a file named file.frm, I guess innodb table needs some other stuff to run on but not sure what.

How can I fix this problem? And I probably need to do more tasks of this kind, what's the correct procedure to drop myism table and recreate them as innodb ones?

Thanks.

Upvotes: 28

Views: 52338

Answers (6)

Carlos Morales
Carlos Morales

Reputation: 1

CREATE TABLE IF NOT EXISTS banners ( id int(255) NOT NULL AUTO_INCREMENT, user int(255) NOT NULL DEFAULT '0', KEY type (type) )
engine=myisam DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

this code change to

CREATE TABLE IF NOT EXISTS banners ( id int(255) NOT NULL AUTO_INCREMENT, user int(255) NOT NULL DEFAULT '0', KEY type (type) )
engine=INNODB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Upvotes: 0

TheAlbear
TheAlbear

Reputation: 5585

I had this with an SQL import on Azure and needed to change

ENGINE=MyISAM with ENGINE=InnoDB

Upvotes: 14

Grwww
Grwww

Reputation: 91

To discover what the error code is for any system error code on a unix or linux system, look in errno.h. On my mac, I can do:

$ grep 28 /usr/include/sys/errno.h
#define ENOSPC 28 /* No space left on device */

On other operating systems, such as linux, there will be some other layers because of machine layer sub includes. But, you should be able to look through those files and find these error definitions.

Alternatively, use the 'man' command to look for "intro" or other manual pages under section '2', the operating system section.

Upvotes: 0

HCS420
HCS420

Reputation: 29

Go to /etc/my.cnf

Comment line innodb_force_recovery=1

Save the file and restart mysql

Upvotes: 0

Shawn
Shawn

Reputation: 34229

OK. I found the solution. The issue was caused by innodb_force_recovery parameter in my.cnf, that was set to 4.

To solve the problem, set to 0 or completely remove this parameter from my.cnf

If you check error log, during query, mysql will write in human readable language that: It won't let you change anything in table until innodb recovery mode is enabled, exactly next message:

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

Please refer to: http://bugs.mysql.com/bug.php?id=30225

Upvotes: 62

deimosaffair
deimosaffair

Reputation: 107

A common cause for the -1 error is a full disk. I have different small VMs for testing purposes, and innodb just keeps filling them up (and I keep forgetting about it). 

$df -ah

If it shows you the disk at 100%, that's the origin of the -1 right there ;)

Upvotes: 9

Related Questions