Tag Archives: mysql

YaBB SE 1.5.5 MySQL Unknown Column Fix

For retro purposes, I wanted to make an old instance of YaBB SE 1.5.4 a read-only version to look back with friends to get some kicks and laughs. I first upgraded from YaBB SE 1.5.4 to YaBB SE 1.5.5c. This was a small project try bring this up on a modern operating system, primarily around the MySQL versioning. First I had to stand up a temporary virtual machine running CentOS 4.5 Linux. Followed by that, I was able to successfully to restore the backups that I had to the virtual machine as it was in the early 2000’s. An issue occurred when trying to run MySQL 4.x queries on a MySQL 5.x version. PHP did not pose the problem even though all functions within YaBB SE were built for the 4.x version of PHP. Below are my findings to get the MySQL SQL 4.x queries to work properly on MySQL 5.x instance. Please refer to the line number for the corresponding files.

Error:
Unknown column ‘m.ID_MEMBER’ in ‘on clause’
File: /home/www/Sources/MessageIndex.php
Line: 269

Original Code:

$result = mysql_query("
			SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
			FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
				LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
				LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
				LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
				LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
			WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
				AND m.ID_MSG=t.ID_LAST_MSG
				AND m2.ID_MSG=t.ID_FIRST_MSG
			ORDER BY $stickyOrder m.posterTime DESC") or database_error(__FILE__, __LINE__);

Modified Code:

$result = mysql_query("
			SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
			FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
				LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
				LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
				LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
				LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
			WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
				AND m.ID_MSG=t.ID_LAST_MSG
				AND m2.ID_MSG=t.ID_FIRST_MSG
			ORDER BY $stickyOrder m.posterTime DESC") or database_error(__FILE__, __LINE__);

Error:
Unknown column ‘b.ID_LAST_TOPIC’ in ‘on clause’
File: /home/www/Sources/Recent.php
Line: 45

Original Code:

$request = mysql_query("
	SELECT m.posterTime, m2.subject, m.ID_TOPIC, t.ID_BOARD, m.posterName, t.numReplies, t.ID_FIRST_MSG
	FROM {$db_prefix}boards AS b, {$db_prefix}categories AS c
		LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC=b.ID_LAST_TOPIC)
		LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG=t.ID_LAST_MSG)
		LEFT JOIN {$db_prefix}messages AS m2 ON (m2.ID_MSG=t.ID_FIRST_MSG)
	WHERE c.ID_CAT=b.ID_CAT
		AND (FIND_IN_SET('$settings[7]', c.memberGroups) != 0 OR c.memberGroups='' OR '$settings[7]' LIKE 'Administrator' OR '$settings[7]' LIKE 'Global Moderator')
	ORDER BY m.posterTime DESC
	LIMIT 1;") or database_error(__FILE__, __LINE__);

Modified Code:

$request = mysql_query("
	SELECT m.posterTime, m2.subject, m.ID_TOPIC, t.ID_BOARD, m.posterName, t.numReplies, t.ID_FIRST_MSG
	FROM ({$db_prefix}boards AS b, {$db_prefix}categories AS c)
		LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC=b.ID_LAST_TOPIC)
		LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG=t.ID_LAST_MSG)
		LEFT JOIN {$db_prefix}messages AS m2 ON (m2.ID_MSG=t.ID_FIRST_MSG)
	WHERE c.ID_CAT=b.ID_CAT
		AND (FIND_IN_SET('$settings[7]', c.memberGroups) != 0 OR c.memberGroups='' OR '$settings[7]' LIKE 'Administrator' OR '$settings[7]' LIKE 'Global Moderator')
	ORDER BY m.posterTime DESC
	LIMIT 1;") or database_error(__FILE__, __LINE__);
Tagged , , ,