Enable MySQL InnoDB Storage Engine Support in XAMPP Installation
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=2Mset-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
Related Articles
- Easily Set Up Web Server with XAMPP
- Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
- WordPress Permalinks Does Not Work in xampp Setup
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 3
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 5
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
- Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 4
- Change and Reset MySQL root Password
- MySQL Database Performance Tuning Best Practices Video Tutorial
- Check and Optimize MySQL Database Automatically with Crontab/Cron
Entries (RSS)
May 5th, 2009 05:49
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
May 1st, 2009 19:48
thank you so much it’s success
March 12th, 2009 18:42
Thanks for the tip. You saved my day.
December 8th, 2008 16:19
Thank you. This helped me out. You’re a star!
October 17th, 2008 09:58
Thanks for the post.
October 11th, 2008 03:47
Thanks for this post. It helped me out a lot.
September 18th, 2008 01:59
Excellent it worked!
Thanks
July 9th, 2008 20:13
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.
July 2nd, 2008 18:30
[...] Enable MySQL InnoDB Storage Engine Support in XAMPP Installation » My Digital Life Habilitando o InnoDB e permitindo o MySQL funcionar com recursos de relacionamento e orientação (tags: mysql innodb relacionamento) [...]
June 29th, 2008 16:55
I have configured your instruction. But, until my InnoDB database storage engine doesn’t working. Also i restarted my mysql sever.
May 25th, 2008 15:12
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
May 25th, 2008 15:02
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.
May 22nd, 2008 22:01
Thank you, your article was very comperhensive.
It helped me alot.
May 15th, 2008 03:25
[...] http://www.mydigitallife.info [...]
May 15th, 2008 03:16
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:\WINDOWS\my.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.
May 4th, 2008 23:08
Thanks, you saved me a lots of searching
April 24th, 2008 23:47
Very helpful, thank you!
April 12th, 2008 21:54
This helps me a lot. Thank you!
March 1st, 2008 03:08
I needed to activate innoDB for using activeCollab. Your article helped me a lot. It was very well written.Thanx.