Query ERROR after upgrade MYSQL 4 to version 5

[Perry]

Hi All,

My hosting company has upgraded MYSQL from version 4 to version 5 today. After the upgrade I get the following error:

While executing query "SELECT *, a.title as album_name, p.filename as filename FROM cpg132_pictures as p, cpg132_albums as a LEFT JOIN cpg132_exif as e ON concat('albums/',p.filepath,p.filename) = e.filename WHERE p.aid = a.aid AND approved = 'YES'ORDER BY pid DESC  LIMIT 0 ,6" on 0

mySQL error: Unknown column 'p.filepath' in 'on clause'

I have no idea what to do. I hope someone can help me with this problem.

Perry

[Nibbler]

I'm pretty sure that's not a normal Coppermine query in the first place. What mod or plugin is this?

[Perry]

This is the query on line 112 in the function.php

$result = db_query("SELECT *, a.title as album_name, p.filename as filename FROM {$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a LEFT JOIN {$CONFIG['TABLE_EXIF']} as e ON concat('{$CONFIG['fullpath']}',p.filepath,p.filename) = e.filename WHERE p.aid = a.aid AND approved = 'YES'" .str_replace("aid","p.aid",$ALBUM_SET)."ORDER BY pid DESC $limit");


Are you sure this is not an original query ?

If it is not, do you have any idea why this query is not woking with mysql 5.0 and was working with mysql 4.x.x

Perry

[Nibbler]

Update your gallery. Coppermine 1.3 is no longer supported.

[Perry]

Seems to have fixed this one.

There seems to have been a change for the JOIN query in MYSQL 5.0

Orginal:

$result = db_query("SELECT *, a.title as album_name, p.filename as filename FROM {$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a LEFT JOIN {$CONFIG['TABLE_EXIF']} as e ON concat('{$CONFIG['fullpath']}',p.filepath,p.filename) = e.filename WHERE p.aid = a.aid AND approved = 'YES'" .str_replace("aid","p.aid",$ALBUM_SET)."ORDER BY pid DESC $limit");

CHANGED INTO:

$result = db_query("SELECT *, a.title as album_name, p.filename as filename FROM ({$CONFIG['TABLE_PICTURES']} as p, {$CONFIG['TABLE_ALBUMS']} as a ) LEFT JOIN {$CONFIG['TABLE_EXIF']} as e ON concat('{$CONFIG['fullpath']}',p.filepath,p.filename) = e.filename WHERE p.aid = a.aid AND approved = 'YES'" .str_replace("aid","p.aid",$ALBUM_SET)."ORDER BY pid DESC $limit");

See the bold ( and ). Works fine now

[Joachim Müller]

Moving to outdated cpg1.3.x support board. Don't!