Archive for the ‘Sql’ Category

I recently decided to upgrade a Magento Site from version 1.2.1 to version 1.3.2.4 and the upgrade went smoothly, so I thought. When trying to enable the new caching features(Flat products and Categories) added in ver 1.3, I was unable to build the flat catalog for the categories and products. I kept getting “Flat Catalog Category rebuild error”. This was very disappointing as this was a major reason to do the upgrade.

Well not to fear, the problem was that the hosting company I used when installing Magento did not allow the “innodb” storage engine. This meant that the tables were using the  MyISAM  storage engine instead. This was the root of the problem. Keep reading to get the solution.

Luckily the Magento Team has released a Database Repair Tool. (Download here)
If you need to use the Database Repair Tool Through trial and error I found it is MUCH better to run it before updating versions. If you do it after updating(“to just fix the flat catalog error”) I have ran into other errors I believe to have been caused by updating a site with a corrupted database.
The instructions are easy to follow and worked perfectly, but here is the gist of things. You create a fresh install of Magento(same version as your site). You put the repair tool script in the web root and fill in the connection info for the damaged database and the fresh installation database. Then the script fixes broken foreign keys, storage engine types, etc.

After running the repair tool I was able to build the Flat catalog categories and products tables.
Problem Solved.

If you have ever tried to move a Site to a new domain or server then you have probably had to dig through the database and change any instances of the old domain name to the new one. THe following sql will help you do a find and replace in the database. This is great for moving a developement site to production. Any way here it is.

UPDATE <tableName> SET <columnName> = replace(<columnName>, 'http://www.old-domain.com','http://www.new-domain.com');

Modify this to your needs.