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
I'm pretty sure that's not a normal Coppermine query in the first place. What mod or plugin is this?
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
Update your gallery. Coppermine 1.3 is no longer supported.
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
Moving to outdated cpg1.3.x support board. Don't!