forum.coppermine-gallery.net

Support => cpg1.3.x Support => Older/other versions => cpg1.3 Miscellaneous => Topic started by: Perry on February 21, 2008, 12:22:36 pm

Title: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Perry on February 21, 2008, 12:22:36 pm

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

Title: Re: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Nibbler on February 21, 2008, 12:47:52 pm

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

Title: Re: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Perry on February 21, 2008, 12:58:04 pm

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

Title: Re: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Nibbler on February 21, 2008, 01:27:09 pm

Update your gallery. Coppermine 1.3 is no longer supported.

Title: Re: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Perry on February 21, 2008, 01:29:11 pm

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

Title: Re: Query ERROR after upgrade MYSQL 4 to version 5
Post by: Joachim Müller on February 21, 2008, 05:36:45 pm

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