Several problems installing SMF 1.1 RC 1

[springfang]

First off, I have PHP 5.0.5 and MySQL 5.0.13-rc installed on my system.  I don't know if SMF is supposed to work on MySQL 5 or not, so maybe that is the simple answer...

Anyway, I frequently get errors such as:

Unknown column 'ml.ID_MEMBER' in 'on clause'
File: C:\webroot\apache\forum3\Sources\MessageIndex.php
Line: 471

The error above happens whenever I click on the "General Discussion" forum link.  I can fix this by manually changing the following query (in MessageIndex.php):

Code Select Expand


$result = db_query("
SELECT
t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky, t.ID_POLL,
" . ($user_info['is_guest'] ? "0" : "IFNULL(lt.logTime, IFNULL(lmr.logTime, 0))") . " AS isRead,
t.ID_LAST_MSG, ml.posterTime AS lastPosterTime, ml.modifiedTime AS lastModifiedTime,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.modifiedTime AS firstModifiedTime,
mf.subject AS firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS firstDisplayName,
LEFT(ml.body, 384) AS lastBody, LEFT(mf.body, 384) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys
FROM {$db_prefix}topics AS t, {$db_prefix}messages AS ml, {$db_prefix}messages AS mf
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
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 = $board AND lmr.ID_MEMBER = $ID_MEMBER)"). "
WHERE t.ID_BOARD = $board
AND ml.ID_MSG = t.ID_LAST_MSG
AND mf.ID_MSG = t.ID_FIRST_MSG
ORDER BY
" . (!empty($modSettings['enableStickyTopics']) ? 't.isSticky' . ($fake_ascending ? '' : ' DESC') . ',
' : '') . $_REQUEST['sort'] . ($ascending ? '' : ' DESC') . "
LIMIT $start, $maxindex", __FILE__, __LINE__);


to this:

Code Select Expand


$result = db_query("
SELECT
t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky, t.ID_POLL,
" . ($user_info['is_guest'] ? "0" : "IFNULL(lt.logTime, IFNULL(lmr.logTime, 0))") . " AS isRead,
t.ID_LAST_MSG, ml.posterTime AS lastPosterTime, ml.modifiedTime AS lastModifiedTime,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.modifiedTime AS firstModifiedTime,
mf.subject AS firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS firstDisplayName,
LEFT(ml.body, 384) AS lastBody, LEFT(mf.body, 384) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys
FROM {$db_prefix}messages AS ml
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER),
{$db_prefix}messages AS mf
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
,{$db_prefix}topics AS t
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 = $board AND lmr.ID_MEMBER = $ID_MEMBER)"). "
WHERE t.ID_BOARD = $board
AND ml.ID_MSG = t.ID_LAST_MSG
AND mf.ID_MSG = t.ID_FIRST_MSG
ORDER BY
" . (!empty($modSettings['enableStickyTopics']) ? 't.isSticky' . ($fake_ascending ? '' : ' DESC') . ',
' : '') . $_REQUEST['sort'] . ($ascending ? '' : ' DESC') . "
LIMIT $start, $maxindex", __FILE__, __LINE__);


You'll notice I had to rearrange the outer joins so that they matched up with the correct tables.  There are several places that I have been able to fix like this, but it is somewhat tedious, and surely this isn't how this is supposed to work?  Is this just happening because I'm using MySQL 5?

Also, my sessions don't seem to work.  I can't logout unless I manually clear my cookies (Firefox and IE), and I can't post topics or anything since it always says my session timed out.  I was wondering if this might also be somehow linked to MySQL 5.  Anyway, any help would be greatly appreciated!  Thanks!

springfang

[springfang]

Since no one seems to want to respond to this, I went ahead and installed MySQL 4.1.  Everything works fine now.  I really would have liked to have this on MySQL 5 since I now have about 4 different database servers running, but oh well.  In any case, I still think it's odd that the queries are all weird.  Any chance that these could be restructured with proper SQL syntax by the time 1.1 is released?

Anyway, it's frustrating not to get any response here.  Even a simple "You're stupid.  We don't support MySQL 5" would have been nice since I wouldn't have wasted the last half-a-day waiting for a response.    In any case, thanks for the great work you have already done -- this definitely is a fantastic product!

[Elmacik]

smf was supposed to work fine with mysql 5x..
am i wrong?

[springfang]

Perhaps, but I certainly couldn't get it to work.  It seems like they have made the SQL structure more strict.  When I was getting the errors like this

Unknown column 'ml.ID_MEMBER' in 'on clause'
File: C:\webroot\apache\forum3\Sources\MessageIndex.php
Line: 471

I hacked the PHP code to display the query it was trying to use, and then I pasted the query directly into a mysql command-line query.  When I rearrange the outer joins as previously stated, then the query would run just fine.  At that point, I began manually editing the PHP files to have the outer joins in the place where MySQL wanted them.  In this manner, I slowly got SMF to be usable, with the exception of the PHP sessions never working.  I am not convinced that the session problem had anything to do with using version 5, but whatever was causing it went away after I switched to using MySQL 4.1 (could have had something to do with rebooting my computer, perhaps?).

springfang

edit: Heh, silly me... I just realized they aren't outer joins, but simply LEFT JOINS.  In any case, what I said before still applies.

[Oldiesmann]

Note the query... "... FROM ... {$db_prefix}messages AS ml ...".
Therefore, "Unknown column 'ml.ID_MEMBER'" means that the ID_MEMBER column is missing from the messages table.

Re-installing SMF should fix the problem.

Modifying all the queries like that will most likely cause more problems than they're fixing.

[springfang]

Note the query... "... FROM ... {$db_prefix}messages AS ml ...".
Therefore, "Unknown column 'ml.ID_MEMBER'" means that the ID_MEMBER column is missing from the messages table.

Re-installing SMF should fix the problem.

Modifying all the queries like that will most likely cause more problems than they're fixing.

No, that's NOT the problem.  The ID_MEMBER column IS in the messages table.  The problem is that the LEFT JOIN is "disconnected" from the table it is joining on.  Hence, simply moving the left join next to the table fixes the problem.  Please look again at the modifications I made:

Code Select Expand


FROM {$db_prefix}topics AS t, {$db_prefix}messages AS ml, {$db_prefix}messages AS mf
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
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 = $board AND lmr.ID_MEMBER = $ID_MEMBER)"). "


becomes...

Code Select Expand


FROM {$db_prefix}messages AS ml
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER),
{$db_prefix}messages AS mf
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
,{$db_prefix}topics AS t
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 = $board AND lmr.ID_MEMBER = $ID_MEMBER)"). "


See how I don't delete/rename/add anything at all?  I just moved the join.  That's it (well, I guess I DID add two commas).  Obviously the column is there.  Reinstalling SMF half a dozen times did NOT resolve this problem.  It is a fundamental problem in the SQL syntax.  Honestly, the old way is incorrect and I am surprised that MySQL 4 allows it at all (Oracle certainly doesn't).  The various JOIN syntaxes should immediately follow the tables they are associated with.  End of story.  I guess they decided to finally get this right in MySQL 5 -- like I said, Oracle doesn't allow this, and I doubt any other big-name DBMSs do, either.

Forgive me if I seem rude; I'm just somewhat annoyed by the response.  It seems as if I'm totally being blown off here.

I would be remiss, however, if I didn't make it clear that I am VERY happy and grateful for the SMF forum software.  I know how much time and energy goes into making something like this, and I am repeatedly impressed by all of the cool and thoughful features that have been included -- and it's open source!  Thanks!

springfang

[Oldiesmann]

Sorry. I was kind of tired last night and wasn't paying attention to the fact that you were using MySQL 5. We've never actually tried getting SMF to support MySQL 5 yet because it's not stable at the moment. Once MySQL 5 becomes stable we'll do our best to make sure SMF works with it.

[springfang]

Okay, thanks!  That is totally and completely understandable.

springfang