MySQL Bugs: #76922: #1030 - Got error -1 from storage engine

Bug #76922	#1030 - Got error -1 from storage engine
Submitted:	4 May 2015 8:08	Modified:	5 May 2015 13:24
Reporter:	Simone Mura	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.43	OS:	Linux (ubuntu 12.04.)
Assigned to:		CPU Architecture:	Any
Tags:	Got error

Description:
Today i've changed innodb size:
in my.cfn:

innodb_log_file_size = 512M

After i stop server and i've move mysql log and start server.

Now i've this error with joomla db in sql insert but i tested myphpadmin and i've the same error:
#1030 - Got error -1 from storage engine

In the same server, i've wordpress and other web site but i don't have error.

How to repeat:
Always, you can access http://cepka.ucm.ac.mz/

Hello Simone Mura,

Thank you for the report.
I followed steps from https://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html and didn't notice any issues after re-sizing redo log file size. I suspect that you have most likely restarted server with innodb_forced_recovery > 0 and trying DML operations on Innodb tables which is resulting in cryptic error message(5.6 latest build has proper error message - most likely fixed after Bug #69907).

Could you please provide complete error log, steps you performed for re-sizing innodb log file size?

Thanks,
Umesh

// 

## 5.5.43 - error message is not clear

scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-5.5.43 --datadir=/export/umesh/server/binaries/mysql-5.5.43/76922
bin/mysqld --basedir=/export/umesh/server/binaries/mysql-5.5.43 --datadir=/export/umesh/server/binaries/mysql-5.5.43/76922 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.5.43/76922/log.err 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.43:  bin/mysql -uushastry -p -S/tmp/mysql_ushastry.sock  test                                                                             
.
Your MySQL connection id is 1
Server version: 5.5.43-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
.
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 select '' from t1;
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

..
.

mysql> insert into t1 select '' from t1;
Query OK, 256 rows affected, 256 warnings (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 256

## -- restart with innodb_forced_recovery > 0

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.43: bin/mysqld --basedir=/export/umesh/server/binaries/mysql-5.5.43 --datadir=/export/umesh/server/binaries/mysql-5.5.43/76922 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.5.43/76922/log.err --innodb_force_recovery=3 2>&1 &
[1] 31976
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.43: 150505 14:49:10 [Note] bin/mysqld (mysqld 5.5.43-enterprise-commercial-advanced) starting as process 31976 ...

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.43:  bin/mysql -uushastry -p -S/tmp/mysql_ushastry.sock  test                                                                             
.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.43-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
.
mysql> insert into t1 select '' from t1;
ERROR 1030 (HY000): Got error -1 from storage engine
mysql>
mysql> show errors;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1030 | Got error -1 from storage engine |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1 |
| Error   | 1030 | Got error -1 from storage engine                     |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

// 5.6.26 - with proper error message

scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-5.6.26 --datadir=/export/umesh/server/binaries/mysql-5.6.26/76922
bin/mysqld --basedir=/export/umesh/server/binaries/mysql-5.6.26 --datadir=/export/umesh/server/binaries/mysql-5.6.26/76922 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.6.26/76922/log.err 2>&1 &

// Create schema/insert data

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=720877 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 select '' from t1;
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

.
.

mysql> insert into t1 select '' from t1;
Query OK, 128 rows affected, 128 warnings (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 128

// restart with innodb_forced_recovery > 0, try to insert data into innodb tables

mysql> insert into t1 select '' from t1;
ERROR 1881 (HY000): Operation not allowed when innodb_forced_recovery > 0.
mysql> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1881 | Operation not allowed when innodb_forced_recovery > 0. |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 257 |
| Error   | 1881 | Operation not allowed when innodb_forced_recovery > 0. |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)

Thanks Umesh,
the problem was innodb_forced_recovery = 1.

obrigado :)
Simone

Thank you for confirming.
Verifying this for the proper error message(which is done in 5.6 builds).

Thanks,
Umesh