SQL syntax error - SMF 1.0.1 with database from 1.00

[nokonium]

I got the following in the search window

"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
File: /home/fhlinux198/k/keynshamweb.org.uk/user/htdocs/smf/Sources/Search.php
Line: 699

Note: It appears that your database may require an upgrade. Your forum files are currently at version SMF 1.0.1, whereas your database is at version SMF 1.00. It is recommended that you execute the latest version of upgrade.php."

Is it as simple as uploading one file and running it?

TIA

[Justyne]

did you manually update or use the package manager?

In any case you could download the update package upload upgrade.php and run it. That should fix this problem.

[_complex]

Also heres an fix.. i think..

http://www.simplemachines.org/community/index.php?topic=23874.msg193076#msg193076

http://www.simplemachines.org/community/index.php?topic=23874.msg193253#msg193253

[nokonium]

did you manually update or use the package manager?

Neither, it was a full clean install onto a new server but then we imported the database from the old server where it had been running SMF 1.0 Full

I hoped it would be simple, but I thought that I'd check before I attempted anything.

Thanks

[nokonium]

I have now run upgrade.php and changed line 626 and I'm still getting the error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
File: ....munged...../smf/Sources/Search.php
Line: 699

This is lines 695 to 718 inclusive

Code Select Expand

// Get the messages out for the callback - select enough that it can be made to look just like Display.
$messages_request = db_query("
SELECT
m.ID_MSG, m.subject, m.posterName, m.posterEmail, m.posterTime, m.ID_MEMBER,
m.icon, m.posterIP, m.body, m.smileysEnabled, m.modifiedTime, m.modifiedName,
a.filename, IFNULL(a.size, 0) AS filesize, a.ID_ATTACH, a.downloads,
first_m.ID_MSG AS first_msg, first_m.subject AS first_subject, first_m.icon, first_m.posterTime AS first_posterTime,
first_mem.ID_MEMBER AS first_member_id, IFNULL(first_mem.realName, first_m.posterName) AS first_member_name,
last_m.ID_MSG AS last_msg, last_m.posterTime AS last_posterTime, last_mem.ID_MEMBER AS last_member_id,
IFNULL(last_mem.realName, last_m.posterName) AS last_member_name,
t.ID_TOPIC, t.isSticky, t.locked, t.ID_POLL, t.numReplies, t.numViews,
b.ID_BOARD, b.name AS bName, c.ID_CAT, c.name AS cName
FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b, {$db_prefix}categories AS c, {$db_prefix}messages AS first_m, {$db_prefix}messages AS last_m
LEFT JOIN {$db_prefix}members AS first_mem ON (first_mem.ID_MEMBER = first_m.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS last_mem ON (last_mem.ID_MEMBER = first_m.ID_MEMBER)
LEFT JOIN {$db_prefix}attachments AS a ON (a.ID_MSG = m.ID_MSG)
WHERE m.ID_MSG IN (" . implode(', ', array_keys($context['topics'])) . ")
AND t.ID_TOPIC = m.ID_TOPIC
AND b.ID_BOARD = t.ID_BOARD
AND c.ID_CAT = b.ID_CAT
AND first_m.ID_MSG = t.ID_FIRST_MSG
AND last_m.ID_MSG = t.ID_LAST_MSG
ORDER BY FIND_IN_SET(m.ID_MSG, '" . implode(',', array_keys($context['topics'])) . "')
LIMIT " . count($context['topics']), __FILE__, __LINE__);

[fisha]

I'm getting the same error and have moded line 626 as described in other threads as linkted to above, but the problem remains.

The error is pointing to the fact that the database file is downlevel, but which database file?  and how do you update it?

[nokonium]

The way I understand it upgrade.php should have 'also' upgraded the MySQL database on your host server so that it can be read by the updated smf files and this isn't the case. 

[Oldiesmann]

Please make sure you're using the most recent version of Search.php... In my copy (SMF 1.0.1), lines 694-699 are as follows:

Code Select Expand

// Load the posters...
$request = db_query("
SELECT ID_MEMBER
FROM {$db_prefix}messages
WHERE ID_MEMBER != 0
AND ID_MSG IN (" . implode(', ', array_keys($context['topics'])) . ')', __FILE__, __LINE__);

[nokonium]

 

Just checked ......

How and why I posted code from RC2 I do not know.

However, I have downloaded and checked the server file and I do have the 1.0.1 version file in use. Sorry about that, a senior moment 

[Oldiesmann]

Try this...

Find

Code Select Expand

// Load the posters...
$request = db_query("
SELECT ID_MEMBER
FROM {$db_prefix}messages
WHERE ID_MEMBER != 0
AND ID_MSG IN (" . implode(', ', array_keys($context['topics'])) . ')', __FILE__, __LINE__);

Replace with this

Code Select Expand

// Load the posters...
$request = db_query("
SELECT ID_MEMBER
FROM {$db_prefix}messages
WHERE ID_MEMBER != 0
AND ID_MSG IN (" . implode(', ', array_keys($context['topics'])) . ")", __FILE__, __LINE__);

I don't know if that will fix it or not, but it might...

[nokonium]

Thanks, I'll try it.

[nokonium]

Thanks, I'll try it.

It works OK on my backup site, but not on the main site.

The backup site is in fact the original database, the main site uses a migrated database. Could it be a database error?

If so how would I fix that?

[[Unknown]]

SHOW CREATE TABLE smf_messages;

What is phpMyAdmin?

No changes were needed in the database from 1.0 to 1.0.1; I'm not sure what you mean, anyhow.

-[Unknown]

[nokonium]

Done that. What do you want me to do now?

[[Unknown]]

Done that. What do you want me to do now?

Umm.... I want the results of the query ^_^.

-[Unknown]

[nokonium]

Ah, right. I couldn't see an answer, it created a table.

[more fiddling]

This it?

CREATE TABLE `sidimessages` (
`ID_MSG` int(10) unsigned NOT NULL auto_increment,
`ID_TOPIC` mediumint( unsigned NOT NULL default '0',
`ID_BOARD` smallint(5) unsigned NOT NULL default '0',
`posterTime` int(10) unsigned NOT NULL default '0',
`ID_MEMBER` mediumint( unsigned NOT NULL default '0',
`subject` tinytext NOT NULL,
`posterName` tinytext NOT NULL,
`posterEmail` tinytext NOT NULL,
`posterIP` tinytext NOT NULL,
`smileysEnabled` tinyint(4) NOT NULL default '1',
`modifiedTime` int(10) unsigned NOT NULL default '0',
`modifiedName` tinytext,
`body` text,
`icon` varchar(16) NOT NULL default 'xx',
PRIMARY KEY  (`ID_MSG`),
UNIQUE KEY `topic` (`ID_TOPIC`,`ID_MSG`),
KEY `ID_TOPIC` (`ID_TOPIC`),
KEY `ID_BOARD` (`ID_BOARD`),
KEY `ID_MEMBER` (`ID_MEMBER`),
KEY `posterTime` (`posterTime`),
KEY `ipIndex` (`posterIP`(15),`ID_TOPIC`),
KEY `participation` (`ID_MEMBER`,`ID_TOPIC`)
) TYPE=MyISAM

[[Unknown]]

ALTER TABLE sidmessages
DROP INDEX ID_TOPIC;

If you find this:

Code Select Expand

// Load the posters...

And add before it:

Code Select Expand

print_r($context['topics']);

What does it show?  How do I modify files?

-[Unknown]

[nokonium]

Thanks [UNKNOWN]

but I'll have to try that later as I have to be at work in 30 minutes.

[Ross316]

I have exactly the same error message and have been following the suggestions in this thread and what i get after doing everything Unknown said is:

Code Select Expand

Array ( [] => Array ( [id] => 2 [relevance] => 25% [num_matches] => 0 [matches] => Array ( ) ) )

above the usual:

Code Select Expand

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
File: /var/www/html/forums/Sources/Search.php
Line: 699

I have noticed the error does not appear when the search finds nothing.

[[Unknown]]

Can you try the attached?

-[Unknown]