I wrote already in the past about Magento eCommerce System. I’ve been playing with Magento until now. Unfortunately, I had some problems with the installation and I could not find any help on the web. Therefore invested time and finally figured it out for myself, maybe this post can help someone using Magento too.

The Problem

Every time during the installation of Magento shop I saw the following exception:

Error in file: ".... app/code/core/Mage/Core/sql/coresetup/mysql4-install-0.8.0.php" - SQLSTATE[HY000]: General error: 1005 Can't create table './db2034841/#sql-1dd4_17baf.frm' (errno: 121) 
Trace:

0 .../app/code/core/Mage/Core/Model/Resource/Setup.php(286): Mage::exception('Mage_Core', 'Error in file: ...')
1 ...app/code/core/Mage/Core/Model/Resource/Setup.php(166): MageCoreModelResourceSetup->_modifyResourceDb('install', '', '0.8.11')
2 .../app/code/core/Mage/Core/Model/Resource/Setup.php(154): MageCoreModelResourceSetup->_installResourceDb('0.8.11')
3 .../app/code/core/Mage/Core/Model/Resource/Setup.php(120): MageCoreModelResourceSetup->applyUpdates()
4 .../app/code/core/Mage/Core/Model/Config.php(215): MageCoreModelResourceSetup::applyAllUpdates()
5 .../app/code/core/Mage/Core/Model/App.php(236): MageCoreModel_Config->init(Array)
6 .../app/Mage.php(424): MageCoreModel_App->init('', 'store', Array)
7 .../app/Mage.php(443): Mage::app('', 'store', Array)
8 .../index.php(52): Mage::run()
9 {main}

I tried installation in versions 1.1.3, 1.1.4, 1.1.6 and 1.1.8 but nothing worked well enough.

You can find many forum threads regarding this issue. Some hints may help you once but do not solve the problem. I guess I don’t have solved the problem globally. But I found the cause of the problem so everybody can solve it on his own way.

Cause of the exception

During the installation routine, many DB-Tables should be created in your Database (MySQL). So Magento starts with SQL-Statement that could be found in mysql4-install-0.8.0.php. This file is also printed out in the Exception message.

So it looks like before any CREATE-Statement - Magento executes this:

SET SQL_MODE=''
SET @OLD_FOREIGN_KEY_CHECKS=@@
FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'

Then, after all, tables and indexes (defined in mysql4-install-0.8.0.php) are created, Magento executes the following Statement.:

SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')
SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS,0)

And the last row causes:

#1231 - Variable ‘foreign_key_checks’ can’t be set to the value of ‘0’

exception, that is finally identified as the problem cause.

Solution

I didn’t spend much time to find out why the variable SET FOREIGN_KEY_CHECKS can’t be set to ‘0’, because it looks like MySQL bug, however…

It’s maybe not the finest solution but it helps: If the whole database is dropped and recreated, then the installation works.

If you feel experienced in MYSQL and has an Idea how to overcome this problem without creating new Databases please make your comment ! ;)