Query ERROR after upgrade MYSQL 4 to version 5 Query ERROR after upgrade MYSQL 4 to version 5
 

News:

CPG Release 1.6.27
change DB IP storage fields to accommodate IPv6 addresses
remove use of E_STRICT (PHP 8.4 deprecated)
update README to reflect new website
align code with new .com CPG website
correct deprecation in captcha

Main Menu

Query ERROR after upgrade MYSQL 4 to version 5

Started by Perry, February 21, 2008, 12:22:36 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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!