Lets make IT better by sharing the solutions

Wednesday, April 15, 2009

Error 1033:Incorrect file information dbname.tablename.frm

Following is the outcome of 5 Hours non stop trouble shooting.
This is Mysql Error which comes due to incompetible size of datafile(ibdata1) and ib_logfile0,..
Incompatible means it is not as mentioned in my.cnf file of mysql(which locates in /etc/mysql/ when you install default in ubuntu). This incompatibility makes innodb engine disabled so all the tables created with innodb db engine will stop working.To verify this check the have_innodb variable.
Above i explained the reason of the error now i will explain the solution for this:

Step 1: Backup your existing my.cnf(/etc/mysql/my.cnf).
Step 2: Try finding the sample my.cnf in your system these are mostly present in example directory of mysql. you can choose any .cnf file preferabbly my-medium.cnf.
Step 3: copy this to the loction where my.cnf was present and rename this to my.cnf
Step 4: go to /var/lib/mysql folder and check the size of ibdata1 file and open my.cnf for editing.
Step 4: first uncomment all the innodb related variables in the file then increase the size of log files and poolsize.Carefully change the size of innodb_data_file_path variable
the size of existing datafile for me it is ibdata1 should match exactly.For example, my datafile ibdata1 is of 35M so for this innodb_data_file_path will be as shown below.
# Uncomment the following if you are using InnoDB tables
#innodb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:34M;ibdata2:500M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 100M
innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 25M
innodb_log_buffer_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Step 5: Save the settings and restart the Mysql.

---PRamod

No comments: