Problems following server MySql5 upgrade

[The Mort]

I have had a shopping cart osCommerce 2.2 Milestone 2 working with just a couple of contributions on - 'Product Attributes Option Type' and 'How did you hear' with no problems until the server upgraded to MySql5. I backed up the database and installed the newest version of osc and the two contribution latest versions and reinstalled the backup but it didn't work properly, the server support said that they didn't think that you could do it using the old database on a new osc version and I got the impression that I had to do the various upgrades manually, this I set off to do but jibbed half way through because if I had made an error it would be back to the beginning- not a lot of fun.

 

I then tried using the latest versions and setting up a new database and exporting the various tables from the original db to the new db, this seemed to work but the attribute options weren't showing up in the product info pages.

 

I am now at the end of my tether with this cart but still need to get it back up and running can anyone with a much greater knowledge than me point me in the right direction please on getting this site from 2.2ms to 2.2rc2a so that it works with the MySql5 but still uses the old database information.

 

Thanks

[MrPhil]

no problems until the server upgraded to MySql5. I backed up the database and installed the newest version of osc and the two contribution latest versions and reinstalled the backup but it didn't work properly

 

Are you getting an error 1054? If so, it's a known incompatibility with MySQL v5. MySQL has been updated to bring it into conformance with SQL standards. Unfortunately, this breaks existing code which uses JOINs. The fix is simple, but tedious: go through all the PHP code looking for SQL JOIN statements (usually LEFT JOINs, but IIRC there may be others). If there is a list of tables between the FROM and the JOIN (separated by commas), you need to wrap that list in parentheses (). There are many, many posts on this -- just search for 1054. I don't know why the developers didn't do this some time before RC2a... AFAIK it won't break MySQL 4, and it's a serious problem that's broken a lot of installations.

 

If it's some other error, please describe what you're seeing, and any error codes.

 

the server support said that they didn't think that you could do it using the old database on a new osc version

 

There were some changes to the database layout between 2.2 MS2 and RC2a. Isn't there a tool to update the database? If not, you'll have to compare the layouts yourself and manually update the tables.

[The Mort]

I did get the 1.54 error at the very beginning and solved it with the 'left join' fix but there were other problems after that.

 

I have also been through all the database tables manually and made sure they are the same.

 

When I export the table information from one database to the other I am quite often getting 'error 1062 - duplicate entry ....', but there seems to be the same amount of pages copying over.

 

 

I think that perhaps the best way is start off with a new database altogether and re-input the store manually. I can keep the old database with the customers and orders on if we need to refer to them and send and e-mail out to everyone that if they order again they will need to reregister.

 

Unless anyone else has an idea.