I am starting to learn manipulating some long list of data from datalogs in mysql, around 300k entries, and it is being added by about 1k off entries everyday, it was working fine in a week then suddently I'm not able to write in the db. Here are the errors that I'm getting:

  1. I can do select queries but when I do insert and update I am getting this error:

    #1030 - Got error -1 from storage engine

  2. When I try to change the engine type from InnoDB to MyISAM, I'm getting this error:

    #1025 - Error on rename of './dbname/tablename' to './dbname/#sql2-500-37' (errno: -1

  3. I can create new tables

These are the things that I have checked:

  1. Any of my disk space is still more than 50%, I have checked it using df

  2. Last line in the MySQL error log (/var/log/mysql/error.log):

    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.
    
  3. Tried restarting server & mysql service

  4. I also tried doubling the values in the MySQL config file but it seems it doesn't help, here is my config file (/etc/mysql/my.cnf)

    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    
    bind-address            = 127.0.0.1
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    
    
    expire_logs_days        = 10 .cnf', otherwise they'll be ignored.
    max_binlog_size         = 100M
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M.cnf', otherwise they'll be ignored.
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer              = 16M
    
    !includedir /etc/mysql/conf.d/
    innodb_force_recovery = 0
    

My server info is:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.43-0ubuntu0.14.04.1 - (Ubuntu)
Protocol version: 10
User: sfere01@localhost
Server charset: UTF-8 Unicode (utf8)

Any other tips on how to further troubleshoot & resolve this kind of issue? What other things should I have checked or missed?

  • 1
    Hi, I don't see the innodb_force_recovery option in your my.cnf file. If it is there, you should remove it. – Craig Efrein Aug 20 '15 at 10:44
  • @CraigEfrein I updated my post again to reflect my config, I added that when I was troubleshooting innodb_force_recovery = 0, and I removed it and it looks like it is working – Carl Alberto Aug 22 '15 at 0:49
up vote 3 down vote accepted

You will notice in the errors this message

InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down

So please remove the innodb_force_recovery option from my.cnf

  • Thanks Craig! it seems that was the culprit, we have inserted that to troubleshoot an earlier issue that we had – Carl Alberto Aug 22 '15 at 23:33

I had this error and my issue was resolved by doing the following:

Replace ENGINE=MyISAM with ENGINE=InnoDB

Microsoft Reference

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.