Found solution for Magento – “SQLSTATE[HY000]: General error” on installation

Posted by shuron
January 4, 2009
I wrote already in the past about eCommerce System. I play with Magento till now from time to time. Unfortunately I had some problems with the installation and I could not find  help in the web. Therefore maybe this post  can be useful for some of you how is dealing whis Magento too.

The Problem

Every time on  the installation of Magento i saw following  exception. 

Error in file: ".... app/code/core/Mage/Core/sql/core_setup/mysql4-install-0.8.0.php" - SQLSTATE[HY000]: General error: 1005 Can't create table './db203484_1/#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): Mage_Core_Model_Resource_Setup->_modifyResourceDb('install', '', '0.8.11')
#2 .../app/code/core/Mage/Core/Model/Resource/Setup.php(154): Mage_Core_Model_Resource_Setup->_installResourceDb('0.8.11')
#3 .../app/code/core/Mage/Core/Model/Resource/Setup.php(120): Mage_Core_Model_Resource_Setup->applyUpdates()
#4 .../app/code/core/Mage/Core/Model/Config.php(215): Mage_Core_Model_Resource_Setup::applyAllUpdates()
#5 .../app/code/core/Mage/Core/Model/App.php(236): Mage_Core_Model_Config->init(Array)
#6 .../app/Mage.php(424): Mage_Core_Model_App->init('', 'store', Array)
#7 .../app/Mage.php(443): Mage::app('', 'store', Array)
#8 .../index.php(52): Mage::run()
#9 {main}
I tried installation with version 1.1.3, 1.1.4, 1.1.6 and 1.1.8 nothing helped… You can find many threads on Magentocommere Forum to this issue. Some hints may help you once but do not solve the problem. I guess I don’t have solved 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. We talk here about MySQL Database.  So Magento begins with SQL-Statement that are holded in the mysql4-install-0.8.0.php. This file is also pintet in the Exception message as the causes of the problem. I analysed the Statement and this came out.. 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 following Statement.: 
SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')
SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS,0)
An the last row causes #1231 – Variable ‘foreign_key_checks’ can’t be set to the value of ‘0’ Exception which is finely identified by me as  the cause of such installation problems!

Solution

I did’n spend much time to find out why the Varibale 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 you can drop the whole the database and create new one, then the installation works. It seems to set all the before settings back and installtion works fine on “vergin-Databases”. If you  feel experienced in MYSQL and has an Idea how to overcome this problem without creating new Databases please make your comment ! ;)
Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • BlinkList
  • MisterWong.DE
  • Slashdot
  • StumbleUpon
  • Technorati
  • NewsVine
  • Reddit
  • Yigg
  • HackerNews
  • LinkedIn
  • Webnews.de
  • Yahoo! Buzz
CMS, Software, Web Development

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments
Comment by Sergio on 2009-02-13 @ 7.53 am

I am having this same problem, but I’m not sure I am getting it right. What’s the file to edit and what should i edit?

Thanks a lot for your help!

Comment by shuron on 2009-02-13 @ 10.03 am

A could’ solve the problem by editing any file.
Check if you try to install Magento in already used Database (DB-Schema). Try to use new one (create new one if you can) and then install Magento. This should help.

Comment by march hare on 2009-05-07 @ 11.47 am

thanks Shuron… please keep posting your thought on magento.. very helpful.

Comment by Jay on 2009-06-06 @ 4.09 am

Thanks very much. This sent me in the right direction.

Using phpmyadmin I selected all tables and did a ‘repair’ function which is found in the ’structure’ tab. It cured the problem instantly.

Thanks so much.

Jay

Comment by marcM on 2009-08-18 @ 3.13 pm

Thanks for the post. I was getting the same error and found it was due to some missing packages. For reference here’s where I finally found a solution. Thx

http://www.magentocommerce.com/boards/viewreply/89448/

Comment by Nash on 2009-12-04 @ 2.20 pm

I am unable to repair the tables. I am getting the error

The storage engine for the table doesn’t support repair

Comment by Ana on 2010-02-03 @ 10.57 am

Tks a lot, it worked just fine after dropping and recreating the database. Keep on the good work!
Ana

Leave a comment

(required)

(required)