Jørn Wildt
Dealing with Zikula and other interesting stuff
Moving site, weird SQL error
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
I'm moving my postnuke 7.6.4 site to another server. On one Pagesetter page, I am getting a SQL error with no data/templates displayed:
Code
Error
SQL query: Documentation
SELECT DISTINCT nuke_pagesetter_pubdata2.pg_id, nuke_pagesetter_pubdata2.pg_pid, pg_author, UNIX_TIMESTAMP( pg_created ) AS pg_created, pg_approvalState, nuke_topics.pn_topicname, pg_revision, pg_online, pg_field3
FROM nuke_pagesetter_pubdata2
LEFT JOIN nuke_topics ON pg_topic = nuke_topics.pn_topicid
LEFT JOIN nuke_pagesetter_pubheader ON pg_tid =2
AND nuke_pagesetter_pubheader.pg_pid = nuke_pagesetter_pubdata2.pg_pid
LEFT JOIN nuke_topics AS AccessTopic ON AccessTopic.pn_topicid = pg_topic
JOIN nuke_topicaccess ON tpa_topic = pg_topic
AND tpa_module = 'pagesetter'
AND tpa_category = 'events'
AND tpa_access = 'read'
AND (
tpa_groupid >=0
AND nuke_group_membership.pn_gid IS NOT NULL
OR tpa_groupid <0
AND nuke_group_membership.pn_gid IS NULL
)
OR AccessTopic.pn_topicid IS NULL
LEFT JOIN nuke_group_membership ON ( nuke_group_membership.pn_gid = tpa_groupid
AND nuke_group_membership.pn_uid =0 )
WHERE (
pg_publishDate <= NOW( ) || pg_publishDate IS NULL
)
AND (
NOW( ) < pg_expireDate || pg_expireDate IS NULL
)
AND pg_online
AND pg_showInList
AND (
pg_language = 'eng'
OR pg_language = 'x_all'
)
AND (
pg_field4 > '2008-08-30'
)
AND NOT pg_indepot
ORDER BY pg_field4, pg_field5, pg_revision
LIMIT 0 , 30
MySQL said: Documentation
#1054 - Unknown column 'nuke_group_membership.pn_gid' in 'on clause'
That's the sql error displayed in PN copied/pasted into phpMyAdmin and run. I took the same query and ran it on the old server and it's OK. The old server is running php4.3x and Mysql 4.x. New server is running php5 and mysql5. I've also noticed that the page displays properly when I'm logged in on the new site, but not when I'm anonymous. It says "Unknown column 'nuke_group_membership.pn_gid' in 'on clause'" and the X mark in phpmyadmin pointing to the error is at the line "LEFT JOIN nuke_group_membership ON ( nuke_group_membership.pn_gid = tpa_groupid", yet it doesn't make sense to me for it to say that these are missing, as I've verified that the referenced tables and columns are indeed present in the database.
Any ideas?
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
I've discovered that this is an issue for any pubtype that has topicacess turned on. I'm not really using that feature anymore, but still it's kind of weird that it does this. All the tables are there, and topic access module is installed and enabled. And the query doesn't work directly in phpMyAdmin anyway, so I think it must be a MySQL 5.x issue(?).