MySql 5?

[AndrewT]

I would hope so but I cannot say for certain. If you're worried about it then I would visit the developer's website to double check.

I will say that we've had only one customer report problems with their scripts as a result of the MySQL 5 upgrades that we've now completed on 8 servers. These particular scripts had been custom developed by them as well so it wasn't anything publicly available.

[Frank Hagan]

My server converted this morning, and all looks fine for my customer's sites. They are running one or more of the following (for the most part, the most recent version, but there's one one of them that's a minor version behind):

phpBB
SMF
Joomla
WordPress
The Next Generation of Genealogy Sitebuilding
Gallery
Goldmine
phpLD (phpLinksDirectory)
Associate-O-Matic (Amazon Store script)

[Pedja]

Well, there is an incompatibility that may show up after upgrade to MySQL5.

If you get something like ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause' then you are facing a MySQL5 bug.

If you have two or more tables in WHERE clause, then put parentheses around all of them, for example, if query

SELECT * FROM t1, t3 LEFT JOIN t2 ON (t2.a = t3.b);

returns an error:

ERROR 1054 (42S22): Unknown column 't3.b' in 'on clause'

then change query to

SELECT * FROM (t1, t3) LEFT JOIN t2 ON (t2.a = t3.b);

and it will work.

[exe]

Oscommerce seems to have a compatibility issue, returning following error:

1054 - Unknown column 'p.products_id' in 'on clause'

select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '36'

Have tried some fixes suggested by Oscommerce forums but to no avail so far…

[AndrewT]

You might want to see Pedja's response right above yours and see if that fixes your problem as well by changing it to:

select count(*) as total from (products_description pd, products p) left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '36'

[exe]

Have managed to get all the functions going but the “Manufacturers” (had to edit index.php, advanced_search_result.php and default.php ) however when any one of the “Manufacturers” is called I still get:

1054 - Unknown column 'p2c.products_id' in 'where clause'

select count(*) as total from products p, products_description pd, manufacturers m left join specials s on p2c.products_id = s.products_id where p.products_status = '1' and pd.products_id = p2c.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '10'

[exe]

Ok, found it, had to edit one more line in default.php

[earth-friendly]

My server is scheduled to be upgraded to MySQL 5 this weekend. I am using OSCMax (a heavily modded version of OsCommerce). Does anyone have any experience with any issues I need to be aware of?

I saw Pedja's post, about putting parentheses around columns in a WHERE clause, so I'll keep that in mind. Also, I looked at the reference page that Andrew pointed to, but it is pretty overwhelming.

I did a dump of my database, in case any upgrades need to be done on the sql.

Thanks!

-Lori-

[AndrewT]

We will have backups of all databases from 24-48 hours prior to the upgrade as well just in case; as has been done for all of the other upgrades. Very few customers experience problems with the MySQL upgrade. Given the number of those that have reported problems this would come out to less than one half of a percent of customers having experienced a problem.

Having said that, it never hurts to ask the developers to find out if your script(s) will have any problems. I'm not aware of any particular incidents with OSCMax or even OsCommerce for that matter.

[earth-friendly]

Thanks Andrew!

Will you be able to give us a time estimate as to when the upgrades will occur on Sunday? I want to post a message letting my customers know that there will be a temporary outage. And, I want to know when I should upgrade the AddHandler in my .htaccess file. What will be the behavior with the current
AddHandler after the upgrade?
AddHandler application/x-httpd-php .php .php3 .html .htm

Can I make the change now, from x-httpd-php to x-httpd-php4, or will that break things?

[AndrewT]

Unfortunately I cannot be any more specific aside from that these will occur on Sunday. There are a lot of different factors that will play into when your server will be upgraded exactly on Sunday.

Do not change your AddHandlers from php to php4 now as they will not work. By default it will be this:

AddHandler application/x-httpd-php4 .php4 .php .php3 .php2 .phtml
AddHandler application/x-httpd-php5 .php5