Bug #30225 ERROR 1030 (HY000): Got error -1 from storage engine

Submitted: 3 Aug 2007 9:40 Modified: 3 Aug 2007 15:51
Reporter: Alex Dantart Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.38 OS:Linux
Assigned to: CPU Architecture:Any
Tags: -1, 1030, engine, error, storage

[3 Aug 2007 9:40] Alex Dantart
Description:
It's impossible to insert data in innodb tables.

All started trying to install Gallery script (http://gallery.menalto.com/)
The error log of their installer told me:

Query: INSERT INTO fot_Schema (
 g_name,
 g_major,
 g_minor
) VALUES('Schema', 1, 2) failed. Got error -1 from storage engine

1030: Got error -1 from storage engine

--------------------------------------------------------

I tried to do it myself in command line, with the same error:

mysql> CREATE TABLE fot_Schema(
    ->  g_name varchar(128) NOT NULL,
    ->  g_major int(11) NOT NULL,
    ->  g_minor int(11) NOT NULL,
    ->  g_createSql text,
    ->  g_pluginId varchar(32),
    ->  g_type varchar(32),
    ->  g_info text,
    ->  PRIMARY KEY(g_name)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO fot_Schema (
    ->  g_name,
    ->  g_major,
    ->  g_minor
    -> ) VALUES('Schema', 1, 2);
ERROR 1030 (HY000): Got error -1 from storage engine

--------------------------------------------------------

And I tried to search in Google but no results... nothing !

In the official script page suggest Google:
http://gallery.menalto.com/node/65689

Here is unsolved:
http://bugs.mysql.com/bug.php?id=6921

This guy happens exactly the same, but nobody replied:
http://www.listsearch.com/MySQL/Message/index.lasso?363371

Other sites like vbulletin forums tells about upgrading to 4.1.11, but i got 4.1.15 installed:
http://www.vbulletin.com/forum/showthread.php?t=138463
http://www.codingforums.com/showthread.php?t=88742
http://bugs.mysql.com/bug.php?id=6624

I do not know what else to do :(

thank you

How to repeat:
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.15-Debian_1ubuntu5-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use database_test;
Database changed

mysql> create table test (field1 varchar(128) NOT NULL, field2 varchar(128) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test (field1, field2) VALUES ("a", "b");
ERROR 1030 (HY000): Got error -1 from storage engine
[3 Aug 2007 9:54] Sveta Smirnova
Thank you for the report.

But version 4.1.15 is quite old. Please upgrade to current version 4.1.23, try with it and say us result.
[3 Aug 2007 10:52] Alex Dantart
Upgraded to 5.0.22-Debian_0ubuntu6.06.3

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (field1 varchar(128) NOT NULL, field2 varchar(128) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into test (field1, field2) VALUES ("a", "b");
ERROR 1030 (HY000): Got error -1 from storage engine

The same with 5.0
[3 Aug 2007 11:07] Sveta Smirnova
Thank you for the feedback.

But version 5.0.22 is outdated too. Please use current version 5.0.45.
[3 Aug 2007 11:12] Alex Dantart
are you kidding me ? :D
It's the stable version on Ubuntu repository... PLEASE do not make me installing one from tarball manually...

So is this a problem that appear in ALL VERSIONS that are not the LAST one ? :P
[3 Aug 2007 11:21] Sveta Smirnova
Thank you for the feedback.

Version 5.0.22 has been released more than 1 year ago. Many bugs have been fixed since. Additionally I can not repeat described behaviour in my environment.

You don't need to install something manually. Use our Generic Linux packages accessible from http://dev.mysql.com/downloads/mysql/5.0.html
[3 Aug 2007 11:37] Alex Dantart
I'm playing with all versions this morning :P 
In the link you gave me there is 5.0.21
http://dev.mysql.com/downloads/mysql/5.0.html#ubuntu

I've searched the LAST one from ubuntu... 5.0.38

Now 5.0.38, the same

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (field1 varchar(128) NOT NULL, field2 varchar(128) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test (field1, field2) VALUES ("a", "b");
ERROR 1030 (HY000): Got error -1 from storage engine

any ideas ?
[3 Aug 2007 11:49] Alex Dantart
4.1.5, 5.0.21, 5.0.22, 5.0.38
all gives "ERROR 1030 (HY000): Got error -1 from storage engine"

Do not offend but... don't you think we should focus the error itself instead changing versions ?

It's what I told you some post above... that error appeared in all "not-last-last-last" different versions ?

Anyway, 5.0.38 is one of the "super" last ones... so ??
[3 Aug 2007 12:01] Sveta Smirnova
Thank you for the feedback.

Version 5.0.38 is not last.

But seems you are right: problem is in your environment.

Please provide output of SHOW ENGINE INNODB STATUS, your error log, configuration file and output of command `ls -la data_directory`
[3 Aug 2007 12:31] Alex Dantart
=====================================
070803 14:21:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 100, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 4358
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 22823, OS thread id 3018648464
MySQL thread id 12, query id 1298 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 4353, not started, process no 22823, OS thread id 3018783632
MySQL thread id 4, query id 1184 localhost debian-sys-maint Repair with keycache
REPAIR TABLE `suscripciones`.`emails_em8`
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
26 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 74133
Log flushed up to   0 74133
Last checkpoint at  0 71083
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17412350; in additional pool allocated 873984
Buffer pool size   512
Free buffers       489
Database pages     22
Modified db pages  13
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 21, created 1, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22823, id 2999286672, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

my.conf:
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

innodb_force_recovery = 4
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
#datadir                = /var/lib/mysql/
datadir         = /discos/sata2/bbdd/
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
old_passwords   = 1
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
#log            = /var/log/mysql.log
log             = /var/log/mysql/mysql.log
log-slow-queries        = /var/log/mysql/mysql-slow.log
log-bin                 = /var/log/mysql/mysql-bin.log
expire-logs-days        = 20
max_binlog_size         = 104857600
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

directory:

root@:# ls -al /var/lib/mysql/test
total 126064
drwx------  2 mysql mysql     4096 Aug  3 13:55 .
drwxr-xr-x 38 mysql mysql     4096 Aug  3 13:55 ..
-rw-rw----  1 mysql mysql     8790 Aug  3 11:13 fot_Schema.frm

error_log:
no log...
[3 Aug 2007 12:44] Sveta Smirnova
Thank you for the feedback.

Please provide output of `ls -la /discos/sata2/bbdd/`. Also please turn error log on, run problematic query and provide us error log.
[3 Aug 2007 12:51] Alex Dantart
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.
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.

dir:
total 93344
drwxr-xr-x 38 mysql mysql     4096 Aug  3 14:43 .
drwx------  2 mysql mysql     4096 Aug  3 13:28 test
[3 Aug 2007 12:54] Alex Dantart
By the way
I've tried with
innodb_force_recovery 3, 4 and 5
[3 Aug 2007 13:14] Alex Dantart
is normal that, when i create a innodb table, just .frm file is created ?
in myisam there is 3 files... even when you do not insert anything...
maybe is a clue :P
[3 Aug 2007 13:14] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about innodb-force-recovery for at http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
[3 Aug 2007 13:21] Alex Dantart
So, 
I have installed FROM ZERO (deleting everything) 4.1.5
I have installed FROM ZERO (deleting everything) 5.0.21
I have installed FROM ZERO (deleting everything) 5.0.22
I have installed FROM ZERO (deleting everything) 5.0.38

and in every version there is "ERROR 1030 (HY000): Got error -1 from storage engine" error when I try to insert data in innodb created table.

And you tell me it's my ERROR...

I have done in a FRESH UBUNTU INSTALL.
New COMPUTER, new ubuntu:

# apt-get install mysql-client
# apt-get install mysql-server
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database t;
Query OK, 1 row affected (0.00 sec)

mysql> use t;
Database changed

mysql> create table test (field1 varchar(10) NOT NULL, field2 varchar(10) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test (field1, field2) VALUES ("a", "b");
ERROR 1030 (HY000): Got error -1 from storage engine

And is my problem ?
What are you going to tell me ? to change motherboard ?

Please, read carefully... NEW COMPUTER, new ubuntu install from Internet
Just mysql-client and server install... NOTHING change

:)
[3 Aug 2007 13:36] Alex Dantart
It's a BUG of mysql my.cnf file
I have do a lot of tests, and finally I have found it

It's SOMETHING about my.cnf
It's a BUG of MYSQL, now works on each version I told, in each one

It's something JUST OF my.cnf, no versions, no directories, no permissions, no databases, no computer, no linux version... it's just a PROBLEM AND BUG of my.cnf

thank you for nothing
[3 Aug 2007 15:51] Alex Dantart
if someone has the same problem, email me... thank you
[7 Aug 2007 9:14] Sergei Golubchik
You wrote

  [3 Aug 14:31] Alex Dantart

  my.conf:

  [mysqld]
  innodb_force_recovery = 4

Later you wrote

  [3 Aug 14:51] Alex Dantart

  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.

InnoDB has *explicitly* told you that it won't work with innodb_force_recovery in my.cnf and asked you to remove this switch.
[17 Mar 2008 3:15] Bingxi Wu
innodb_force_recovery only for crash recovery mode.if the value greater than 0,You can changes the data.
[13 Jun 2008 15:15] Nicolas Pestana
Thanks Alex for these post, it has been helping resolve this problem (it's funny to read it too ;) )
[3 Sep 2008 22:13] Alexandre Almeida
Oh my God! You don't need to change any version. Just set you force recovery to 0. Force recovery it´s only for recovery operation!!!
[31 May 2016 6:51] ajit rew
I have Same problem Alex can you give me your email id
[9 Mar 2023 16:51] FRANCISCO SANCHEZ
is normal that, when i create a innodb table, just .frm file is created ?
in myisam there is 3 files... even when you do not insert anything...
maybe is a clue :P You can find a [url=https://www.everyword.eu/traductor-jurado-madrid]traductor jurado in Madrid[/url]  and a [url=https://tradupro.net/traductor-jurado-sevilla]Traductor jurado in Sevilla[/url] here.
[9 Mar 2023 16:53] FRANCISCO SANCHEZ
is normal that, when i create a innodb table, just .frm file is created ?
in myisam there is 3 files... even when you do not insert anything...
maybe is a clue :P You can find a https://www.everyword.eu/traductor-jurado-madrid traductor jurado in Madrid https://tradupro.net/traductor-jurado-sevilla
https://tradupro.pl/tlumacz-przysiegly-krakowie/
[9 Mar 2023 16:54] FRANCISCO SANCHEZ
I'm playing with all versions this morning :P 
In the link you gave me there is 5.0.21
http://dev.mysql.com/downloads/mysql/5.0.html#ubuntu

I've searched the LAST one from ubuntu... 5.0.38

Now 5.0.38, the same

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (field1 varchar(128) NOT NULL, field2 varchar(128) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test (field1, field2) VALUES ("a", "b");
ERROR 1030 (HY000): Got error -1 from storage engine

any ideas ?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.081 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.