Mysql 5 + Article Manager - NOT Compatible in RC2

[red_fraggle]

Stock oscmax RC2 store. We have upgraded server to Mysql 5 from Mysql 4 and everything except the articles manager works. The following code is displayed on the home page in the location the articles/reviews info box used to be in. Does anyone have a fix for this?

Error:

1054 - Unknown column 'a.authors_id' in 'on clause'

select a.articles_id from articles a, articles_to_topics a2t left join topics_description td on a2t.topics_id = td.topics_id left join authors au on a.authors_id = au.authors_id, articles_description ad where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a.articles_status = '1' and a.articles_id = ad.articles_id and ad.language_id = '1' and td.language_id = '1' and a.articles_date_added > SUBDATE(now( ), INTERVAL '365' DAY)

[TEP STOP]

In reading some articles here, i have found that this is fixed in the following patch: osCMax-2.0RC2-update051112

However, there is no link to download this patch that i canf ind. Can someone link this patch for me please.

sincerely,

[michael_s]

The patch is in this thread:
osCMax 2.0RC2 Security Patch/Update 051112

[red_fraggle]

thanks michael

[red_fraggle]

I installed the patch per instrucitons by overwriting all files.
I ran the sql query which completed with no errors

ALTER  TABLE whos_online MODIFY  COLUMN last_page_url VARCHAR( 255  )  NOT  NULL ;# Affected rows: 1
 ALTER  TABLE customers MODIFY  COLUMN customers_default_address_id INTEGER;# Affected rows: 629
 ALTER  TABLE customers_basket MODIFY  COLUMN final_price DECIMAL( 15, 4  ) ;# Affected rows: 297

However, the articles and reviews module still displays the following sql error on the homepage where the info box should be:

1054 - Unknown column 'a.authors_id' in 'on clause'

select a.articles_id from articles a, articles_to_topics a2t left join topics_description td on a2t.topics_id = td.topics_id left join authors au on a.authors_id = au.authors_id, articles_description ad where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a.articles_status = '1' and a.articles_id = ad.articles_id and ad.language_id = '1' and td.language_id = '1' and a.articles_date_added > SUBDATE(now( ), INTERVAL '365' DAY)

[TEP STOP]

Also, i have made sure that an author exists by creating one with the authors tool in the admin for this add-on, and i have even disabled ALL articles (using red button beside each article). The error still displays. Keep in mind no changes were made to this store, other than an upgrade of the server from Mysql4 to Mysql5.

sincerely,

[bkpie]

Did you look at this thread 1054 - Unknown column 'a.authors_id' in 'on clause'

[bkpie]

To make it easier for those having this problem I have made the changes that mindtwist found and I am uploading 2 stock oscmax files from your templates content. Articles.tpl and Articles_new.tpl This should fix the error asked about above.

[plorimer]

Hi

I have a similar problem with a V old OSC site I inherited

The following query (on advanced search) works on MySQL3.23 and 4.xx but fails on V5 with 1054 - Unknown column 'p.products_id' in 'on clause'

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '182') where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 45) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 55)

anybody know the mySQL5 equivalent to the above query?

[jpf]

Yes - It is not compatible.... with MySQL 5!

You need MySQL v3.x or v4.x and php 4.x

If you want Version 5 compatible - then use RC4-SVN version

[michael_s]

This should work on mysql5:

select count(distinct p.products_id) as total from ((((products p) left join manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c) left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id) left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '182') where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 45) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 55)

[plorimer]

Excellent. Not tried it yet but was along the same lines as what I was trying. Seemed to get into parenthesis madness somewhere along the way.

Thanx a lot