#1  
Old 25th July 2006, 03:21
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
Default MySQL 5 to MySQL 4

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:

PHP Code:
[29-Apr-2006 02:29:23SQL error: /xcart/home.php
SELECT COUNT
(DISTINCT(xcart_products.productid)) FROM xcart_productsxcart_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=AND xcart_pricing.variantid 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:58SQL error: /xcart/product.php
SELECT xcart_classes
.*, xcart_class_options.*, xcart_class_lng.class as class_lngxcart_class_lng.classtext as classtext_lngxcart_product_options_lng.option_name as option_name_lng FROM xcart_class_optionsxcart_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:

Code:
FROM
   (xcart_products,
   xcart_pricing ,
   xcart_featured_products,
   xcart_products_categories,
   xcart_categories)
Reply With Quote
Sponsored Links
  #2  
Old 25th July 2006, 09:40
till till is online now
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,407
Thanks: 834
Thanked 5,496 Times in 4,326 Posts
Default

Does the coulumn productid exists in the table xcart_products?
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 25th July 2006, 18:22
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
Default

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.
Reply With Quote
  #4  
Old 26th July 2006, 09:40
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,744 Times in 2,577 Posts
Default

Have a look here: http://www.x-cart.com/xcart_manual/o...quirements.htm

It says:
Quote:
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.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #5  
Old 8th November 2006, 16:15
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
Default

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?
Reply With Quote
  #6  
Old 9th November 2006, 16:35
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,744 Times in 2,577 Posts
Default

I wouldn't do this without making backups of my databases...
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #7  
Old 10th November 2006, 00:52
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
Default

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.
Reply With Quote
  #8  
Old 10th November 2006, 15:48
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
Default

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.
Reply With Quote
  #9  
Old 10th November 2006, 16:59
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,744 Times in 2,577 Posts
Default

Quote:
Originally Posted by tristanlee85
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.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #10  
Old 11th November 2006, 00:45
tristanlee85 tristanlee85 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 199
Thanks: 3
Thanked 2 Times in 2 Posts
 
Default

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.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
postfix - MySQL connection problem epo Installation/Configuration 7 4th August 2006 20:49
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04
Failed at MySQL lhatle Installation/Configuration 4 29th November 2005 01:26
Problem installing ISPConfig, then with MySQL... ctroyp Installation/Configuration 7 26th September 2005 16:37
MySQL issue during install of ISPConfig ricbax Installation/Configuration 4 16th September 2005 09:41


All times are GMT +2. The time now is 11:24.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.