Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below are the parameters which I changed.

[mysqld]
key_buffer_size = 298M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 50
query_cache_size = 32M
query_cache_limit = 1M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

and uncommented these variables for InnoDB.In default file, it was commented even INNODB was active.

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_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 = 1127M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Now, to verify whether InnoDB is available or not, I ran show engines command. But, InnoDB is not listed in that. Then, I ran command below command:

mysql> show variables like '%innodb%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_innodb           | NO    |
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

I am unable to find where I did the mistake? Can you please help?

Adding the way, issue has been resolved But don't know the way is correct or not!:

Checking error log certainly helped here. On the basis of error log:

InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
InnoDB: 133760 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 128000 pages!
InnoDB: Could not open or create data files.
Then, I commented - #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 

After this, I got below error message in error log file:

InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
140430 17:13:37 [ERROR] Plugin 'InnoDB' init function returned error.
140430 17:13:37 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Then, I commented #innodb_log_file_size = 256M By default #innodb_log_file_size = 5M.

Now, I again started the mysql service and found that Innodb has been loaded as default engine.

share|improve this question
    
And in "show engines;" ? –  Maxime FOUILLEUL Apr 30 at 20:49
    
Updated my question with show engines details... –  ursitesion Apr 30 at 20:52
2  
check error log, it will tell why InnoDB fails to start –  akuzminsky Apr 30 at 21:03
    
Thanks, I reviewed my log files which became very helpful for me to anayze the issue. I am posting my activities in my answer now. –  ursitesion Apr 30 at 21:22

1 Answer 1

up vote 0 down vote accepted

You found in the error log that InnoDB refuses to start if the config file sets the innodb_log_file_size to a size different from the physical size of the ib_logfile* files in your data directory.

The reason for this is that the log file are used during crash recovery. If InnoDB tries to initialize, but the log files are suspiciously the wrong size, it's possible they're the wrong files. InnoDB decides not try to crash recovery using the wrong log files, because applying the wrong files could irreparably corrupt your data. So believe it or not, shutting down the storage engine is the best outcome.

You can add another line to your config file to make mysqld refuse to start if InnoDB fails to start:

innodb=force

In MySQL 5.6, they added more flexibility so that InnoDB resizes the log files if you change the config file entry. Here's a blog that explains this in more detail: http://www.mysqlperformanceblog.com/2013/09/05/my-favorite-secret-mysql-5-6-feature/

A couple of other tips

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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