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__);

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.