Enable MySQL InnoDB Storage Engine Support in XAMPP Installation

ยปยปยปEnable MySQL InnoDB Storage Engine Support in XAMPP Installation
The XAMPP from Apache Friends is a collection of free open source apps that make it easy to install Apache distribution containing MySQL, PHP and Perl. There are four XAMPP distributions for Windows, Linux, Mac OS X and Solaris. To set up an Apache web server with XAMPP, simply download and extract XAMPP, and the Apache HTTPD web server is ready after one or two more auto-execute scripts. There are no changes to the Windows registry (unless you’re using Widows installer version of XAMPP) and it’s not necessary to edit any configuration files.

However, by default, InnoDB MySQL database storage engine is not enabled in the my.cnf configuration file. If a webmaster plans to use InnoDB features, such as one to many or many to many table relationships on foreign key constraints, or transaction-safe commands such as commit, rollback and crash recovery capabilities.

To enable the support of MySQL server on InnoDB storage engine, locate the “my.cnf” config file (normally in /installation_path/xampp/mysql/bin/ directory), and edit the my.cnf with any text editor such as vi.

Search and locate each of the following lines (except the lines in italic where they’re comments):

- Comment the following line to unskip and use InnoDB
skip-innodb 

- Uncomment the following options for InnoDB database if you are using InnoDB tables.
#innodb_data_home_dir = C:/xampp/xampp/mysql/data/ 
#innodb_data_file_path = ibdata1:10M:autoextend 
#innodb_log_group_home_dir = C:/xampp/xampp/mysql/data/ 
#innodb_log_arch_dir = C:/xampp/xampp/mysql/data/ 

- Uncomment the lines and set innodb_buffer_pool_size up to 50% - 80% of RAM for optimization of InnoDB databases, try not to memory usage too high.
#set-variable = innodb_buffer_pool_size=16M 
#set-variable = innodb_additional_mem_pool_size=2M 

- Uncomment the lines and set innodb_log_file_size to 25% of InnoDB buffer pool size for optimisation.
#set-variable = innodb_log_file_size=5M 
#set-variable = innodb_log_buffer_size=8M 
#innodb_flush_log_at_trx_commit=1 
#set-variable = innodb_lock_wait_timeout=50 

After modification, the code for each lines should look like this:

# skip-innodb 

innodb_data_home_dir = C:/xampp/xampp/mysql/data/ 
innodb_data_file_path = ibdata1:10M:autoextend 
innodb_log_group_home_dir = C:/xampp/xampp/mysql/data/ 
innodb_log_arch_dir = C:/xampp/xampp/mysql/data/ 

set-variable = innodb_buffer_pool_size=16M 
set-variable = innodb_additional_mem_pool_size=2M 

set-variable = innodb_log_file_size=5M 
set-variable = innodb_log_buffer_size=8M 
innodb_flush_log_at_trx_commit=1 
set-variable = innodb_lock_wait_timeout=50 

When InnoDB is not enable and MySQL uses MyISAM database storage engine instead, some errors that may be returned include:

Undefined GET property or variable in ‘A_Class’ class: A_Class_Object

Fatal error: Call to undefined method Object::Function() in /xampp/htdocs/wwwroot/a_page.php on line 87

By | 2016-12-09T08:41:16+00:00 December 9th, 2016|Categories: Databases|Tags: , |21 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.

Connect with LK through Tech Journey on Facebook, Twitter or Google+.

  • Iftekher Chowdhury

    I needed to activate innoDB for using activeCollab. Your article helped me a lot. It was very well written.Thanx.

  • sabine

    This helps me a lot. Thank you!

  • christian

    Very helpful, thank you!

  • Thanks, you saved me a lots of searching

  • I tried the above but whatever changes i made to my.cnf file in apachefriends folder, nothing was working. The following worked for me:

    1. If you have MYSQL SYSTEM TRAY MONITOR installed on your machine (it is bundled with MYSQL QUERY BROWSER), launch it – you will see a little icon on the notification task bar on your windows machine.

    2. Right click it and choose “Configure instance”.

    3. Select “Startup Variables” on the left item listings.

    4. Toggle to “InnoDB Parameters” tab. Select “Activate InnoDB”. You will notice all your settings are disabled.( to change your settings/uncomment as mentioned above, follow the next steps)

    5. At the footer of the InnoDB tab window you will see a label:

    Options file: C:WINDOWSmy.ini

    Section: mysqld

    So this is where my configuration file was located. This ini file took precedent over the apachefriends folder > my.cnf. Locate this file and simply follow the uncommenting procedure of this post and save.

    6. Stop the mysql service by right clicking on the Monitor tray again. Select “Shutdown Instance”.

    7. Right click again and Select “Start Instance”.

    8. Now you will see all your InnoDB variables settings. ๐Ÿ™‚

  • Thank you, your article was very comperhensive.

    It helped me alot.

  • Jimis

    For those who had my problem. If you couldnt find a my.cnf file, instead you find a shortcut looking icon named 'my'.

    This is the my.cnf file!!

    I couldnt right click 'open with', instead I opened it by opening a ext editor and file->open->my.

    Then follow above. ๐Ÿ™‚

  • Jimis

    Except my my.cnf was this: (it worked all the same)

    #skip-innodb

    innodb_data_home_dir = "C:/xampp/mysql/"

    innodb_data_file_path = ibdata1:10M:autoextend

    innodb_log_group_home_dir = "C:/xampp/mysql/"

    innodb_log_arch_dir = "C:/xampp/mysql/"

    innodb_buffer_pool_size = 16M

    innodb_additional_mem_pool_size = 2M

    innodb_log_file_size = 5M

    innodb_log_buffer_size = 8M

    innodb_flush_log_at_trx_commit = 1

    innodb_lock_wait_timeout = 50

  • I have configured your instruction. But, until my InnoDB database storage engine doesn't working. Also i restarted my mysql sever.

  • prabhu

    hi,

    i folowed the steps which you mentionen above, also i changed in my.cnf file and restarted the apachi and mysql. But i could not get innodb in the table type dropdown box.

  • Ruben-RY

    Excellent it worked!

    Thanks

  • Thanks for this post. It helped me out a lot.

  • Zelth

    Thanks for the post.

  • Paul

    Thank you. This helped me out. You're a star!

  • Yogi Yang

    Thanks for the tip. You saved my day.

  • thank you so much it's success

  • It seems that InnoDB is already enabled in versions of xampp (mysql) after the latest release. 1.7.1 onwards (2009 onwards).

    Which should save some hacking around ๐Ÿ™‚

  • Pingback: foreign key - MySQL Database answers()

  • denis

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE

    ) ENGINE=InnoDB DEFAULT CHARSET=cp1251' at line 4

  • Idin

    Thank you very much. It helped me out a lot.

  • mohideen, india

    Really, helped me, thank you very much.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!