MySQL 5 to MySQL 4

[tristanlee85]

I've got X-cart installed on my server and appearantly there are issues with the SQL queries when joining tables. It works fine on MySQL 4, but the issues started in 5.

I'm going to copy and paste my error log:

[29-Apr-2006 02:29:23] SQL error: /xcart/home.php
SELECT COUNT(DISTINCT(xcart_products.productid)) FROM xcart_products, xcart_pricing , xcart_products_categories LEFT JOIN xcart_products_lng ON xcart_products_lng.productid = xcart_products.productid AND xcart_products_lng.code = 'US' WHERE xcart_pricing.productid=xcart_products.productid AND xcart_pricing.quantity=1 AND xcart_pricing.variantid = 0 AND xcart_pricing.membership IN ('','') AND xcart_products_categories.productid=xcart_products .productid AND xcart_products_categories.categoryid='2' AND (xcart_products_categories.main='Y' OR xcart_products_categories.main!='Y') AND xcart_products.forsale='Y'
1054 : Unknown column 'xcart_products.productid' in 'on clause'
-------------------------------------------------
[29-Apr-2006 02:58:58] SQL error: /xcart/product.php
SELECT xcart_classes.*, xcart_class_options.*, xcart_class_lng.class as class_lng, xcart_class_lng.classtext as classtext_lng, xcart_product_options_lng.option_name as option_name_lng FROM xcart_class_options, xcart_classes LEFT JOIN xcart_product_options_lng ON xcart_class_options.optionid = xcart_product_options_lng.optionid AND xcart_product_options_lng.code = 'US' LEFT JOIN xcart_class_lng ON xcart_classes.classid = xcart_class_lng.classid AND xcart_class_lng.code = 'US' WHERE xcart_classes.productid = '34' AND xcart_classes.classid = xcart_class_options.classid AND xcart_class_options.avail = 'Y' AND xcart_classes.avail = 'Y'
1054 : Unknown column 'xcart_class_options.optionid' in 'on clause'

One guy stated that I have to put everything in brackets for MySQL 5. There are so many files to edit I figured going to MySQL 4 would be easier. Any suggestions? Here is what I was suggested to do:

FROM
(xcart_products,
xcart_pricing ,
xcart_featured_products,
xcart_products_categories,
xcart_categories)

[till]

Does the coulumn productid exists in the table xcart_products?

[tristanlee85]

Yes it does. From what I read, it's just the syntax of the 'JOIN' query that screws it up, but there are too many files to go through and change the syntax.

[falko]

Have a look here: http://www.x-cart.com/xcart_manual/online/system_requirements.htm

It says:
X-Cart is compatible with MySQL versions from 3.23 to 4.x (MySQL 5.x is not supported).

So you should switch back to MySQL 4.

[tristanlee85]

Without losing my current databases and all my information, is there an easy way to switch back to MySQL 4 without jepordizing my current ISPconfig setup?

[falko]

I wouldn't do this without making backups of my databases...

[tristanlee85]

Most definately. I wouldn't attempt something like that without backups. I just wasn't sure if anything screwy would go on with ISPconfig since it was installed onto a MySQL5 database and then will be working with a MySQL4 database. I figured it's be as easy as stopping the service, removing MySQL5, downloading MySQL4 and reinstalling. It'd be nice to be able to copy over the database files instead of needing to import everything again. That would take quite a long time.

[tristanlee85]

I've made a tarball of /var/lib/mysql which contains all of the database information. If I use YUM to remove MySQL, with the DB username and password information still be stored in /var/lib/mysql or is that information stored elsewhere? I'd hate to back up all of my databases and then not be able to gain access to them later on.

[falko]

with the DB username and password information still be stored in /var/lib/mysql or is that information stored elsewhere? I'd hate to back up all of my databases and then not be able to gain access to them later on.
The usernames and passwords are stored in the mysql.user table, so if you have a backup of /var/lib/mysql, then you also have that table in it. :)

[tristanlee85]

I think I'm going to pass on this. I try to remove MySQL and it wants to remove all of its other dependencies as well, such as dovecot, PHP stuff, etc. I don't really want to chance it.