MySQL row does not persist, but primary key with auto-increment is incremented

auto-incrementlogsMySQL

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, the row vanishes (despite no other queries being executed).

This table has an integer ID as a primary key, with auto-increment set, and the primary key is auto-incremented as expected for new rows. This leads me to believe there is not some kind of transactional rollback (and my table is MyISAM, so that shouldn't be possible anyways) or anything else that is somehow reverting the database to a previous state.

What logs should I be checking to see what is going on here? The contents of my '/var/log/mysql/error.log' are below, but I don't see anything unusual.

120815 21:01:01 [Note] Plugin 'FEDERATED' is disabled.
120815 21:01:02  InnoDB: Initializing buffer pool, size = 8.0M
120815 21:01:02  InnoDB: Completed initialization of buffer pool
120815 21:01:03  InnoDB: Started; log sequence number 0 44233
120815 21:01:03 [Note] Event Scheduler: Loaded 0 events
120815 21:01:03 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'      port: 3306  (Ubuntu)
120815 21:01:04 [ERROR] /usr/sbin/mysqld: Table './appname/users' is marked as crashed and should be repaired
120815 21:01:04 [Warning] Checking table:   './appname/users'
120815 21:10:34 [Note] /usr/sbin/mysqld: Normal shutdown

120815 21:10:34 [Note] Event Scheduler: Purging the queue. 0 events
120815 21:10:34  InnoDB: Starting shutdown...
120815 21:10:39  InnoDB: Shutdown completed; log sequence number 0 44233
120815 21:10:39 [Note] /usr/sbin/mysqld: Shutdown complete

I noted the 'crashed' mark on the appname/users table, but mysqlcheck suggests the table is OK.

Any thoughts?

Best Answer

The disappearing rows are a common behavior of mysqlcheck (or myisam check), because it tries to repair the corrupted blocks. Other engines (eg. Oracle) have more advanced repairing algorithms, that can actually preserve your data.

Corruption usually appears when there's a problem with the disk (space, partition headers etc), so start by checking them. Then check the integrity of your data files, and make sure they can expand correctly.

PS: When runing mysqlcheck, you're using the --auto-repair argument?

Related Solutions

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Best Answer

Related Solutions

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Related Question