Database Error when use search and view recent posts from profile view.
I have no idea what went wrong. I just play with SMF for awhile, post, delete,
(many times) change group... etc.
And then I click search try to search for something.
I got this when a match occure.
Database Error
Please try again. If you come back to this error screen, report the error to an administrator.
So. I went back to my profile to see what were in those recent posts.
(action=profile;u=3;sa=showPosts)
Bamm... got the same error.
Database Error
Please try again. If you come back to this error screen, report the error to an administrator
....
I tried to track down the problem.
It's end at function boardsAllowedTo($permission) in Security.php
It error while executed this query when parameter $permission was post_reply_own
// Fetch boards that have the permission globally.
$request = db_query("
SELECT b.ID_BOARD
FROM {$db_prefix}boards AS b
LEFT JOIN {$db_prefix}board_permissions AS bp ON (bp.ID_BOARD = 0 AND bp.ID_GROUP IN (" . implode(', ', $groups) . ") AND bp.permission = '$permission')
LEFT JOIN {$db_prefix}moderators AS mod ON (mod.ID_MEMBER = $ID_MEMBER AND mod.ID_BOARD = b.ID_BOARD)
LEFT JOIN {$db_prefix}board_permissions AS mod_bp ON (mod_bp.ID_BOARD = 0 AND mod_bp.ID_GROUP = 3 AND mod_bp.permission = '$permission')
WHERE b.use_local_permissions = 0
AND bp.addDeny = 1
AND (!ISNULL(bp.ID_BOARD) OR (!ISNULL(mod_bp.ID_BOARD) AND !ISNULL(mod.ID_MEMBER)))", __FILE__, __LINE__);
All those error above did not happen to Admin because Admin
didn't require to run the query, so there's no problem.
I just realize that I never tried these feature with regular user before :P
....
Anybody have an idea what go wrong here? and How to fix it?
What could have cause this?
umm.. Might it be a BUG when use SMF with MySQL 4.1.1a alpha-nt ?
It happen on my test server at home:
--------
Apache Version Apache/2.0.48 (Win32) PHP/4.3.4
PHP Version 4.3.4
MySQL 4.1.1a alpha-nt
---------
Thanks..
-JZ-the WhiteRabbit
did you try Admin/Forum Maintenance/Find and repair errors?
Yes I already tried that and almost all functions in Forum Maintenance :-[
But nothing solved that.
I even did a new clean install to test it, yet it still has the problem on the first try :'(
I sorta doubt it's that query... what shows up in your error log? (the database error should be logged in there...)
-[Unknown]
Quote
http://localhost:8080/testboard/index.php?action=search2
Database 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 'mod ON (mod.ID_MEMBER = 2 AND mod.ID_BOARD = b.ID_BOARD)
LE
File: E:\inetpub\wwwroot\htdocs\testboard\Sources\Security.php
Line: 485
This was shown in my forum errorlog :-[
SELECT b.ID_BOARD
FROM sexyboard_smf_boards AS b
LEFT JOIN sexyboard_smf_board_permissions AS bp ON (bp.ID_BOARD = 0 AND bp.ID_GROUP IN (0, 4) AND bp.permission = 'post_reply_own')
LEFT JOIN sexyboard_smf_moderators AS mod ON (mod.ID_MEMBER = 2 AND mod.ID_BOARD = b.ID_BOARD)
LEFT JOIN sexyboard_smf_board_permissions AS mod_bp ON (mod_bp.ID_BOARD = 0 AND mod_bp.ID_GROUP = 3 AND mod_bp.permission = 'post_reply_own')
WHERE b.use_local_permissions = 0
AND bp.addDeny = 1
AND (!ISNULL(bp.ID_BOARD) OR (!ISNULL(mod_bp.ID_BOARD) AND !ISNULL(mod.ID_MEMBER)))
This is the query that sent to MySQL.
I open phpMyAdmin and [ correction: look at wrong table :-[ ]
Why does it work for me?
-[Unknown]
:( I wonder too.. that sql code did not run on phpMyAdmin either. It generate the same error :(
I think this might be an issue with MySQL 4.1.1 (windows only maybe :P )
because SMF run just fine on my Linux box (MySQL 4.0), and on my webserver ...
So, Anyone else use the same system as mine?
--------
Apache Version Apache/2.0.48 (Win32) PHP/4.3.4
PHP Version 4.3.4
MySQL 4.1.1a alpha-nt
---------
Do you have the same problem or just only my machine :'(
If so, it might be another issue beside the new MySQL password hash 41 bytes vs. the old 16 bytes ::)
Keep in mind that MySQL 4.1.1 is less stable than SMF. It's still alpha, this may well be a bug in it not SMF.
-[Unknown]
Yes.. I think so, too; therefore I mentioned above "MySQL BUG or " :D
I just have fun trying a new thing, and cause problems heheh.
Keep it on my (already problemed) windows machine.
And want to gather some information too. If anyone have this kind of problem,
so it can be noted as a known issue with MySQL 4.1.1 (win32)
Thanks -[Unknown]
I have the same problem too... I'm running on :
phpMyAdmin 2.5.6-rc1
MySQL 4.1.0-alpha
this was my error message :
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 'errors
FROM smf_log_errors' at line 1
File: /usr/home/pawz/public_html/forum/Sources/Errors.php
Line: 298
How to solve this?
You realize that it is this query that is failing, right? It's a perfectly good query.
SELECT COUNT(ID_ERROR) AS errors
FROM smf_log_errors;
Now, this obviously should work. I assume that you realize you are using an UNSTABLE version of MySQL, right?
SMF does not support 4.1.0-alpha or above. Maybe when they are beta, it will.... but right now, they are not even stable.
-[Unknown]
Sorry I forgot to come back to tell you. :-[
The errors caused by using a reserved word in MySQL.
I will try to explain why it was not effect MySQL 4.0 or lower.
If I understand correctly, :-[ MySQL 4.0 treats UPPER CASE syntax and
lower case distinctly (you cannot mix and match), but in version 4.1 and above it's no different.
So when it comes to the reserved words that would cause problems. I thinks :-\
How to fix it? Just add a number to those alias that you suspect it as a reserved word.
In my case is 'mod' that caused the error, so I just change it to 'mod1'.
I assume that in your case it is 'errors', so you just change it to errors1.
I thinks that should solve the problems.
Cheers,
Whiterabbit.
MySQL has always allowed reserved words to be used, have they changed this?
-[Unknown]
I'm not so sure about that prohibit either, that's why I didn't look at this case before.
It might be just a bug while parsing a query.
As you said it still alpha, something might change again :P
'cuz it will effect so many many queries in the world heheh including lots of mine :-[
But it solve the problem this time, so why not, just avoid using those reserved words.
FYI it has the same effect in MySQL 5 as well, so you might have to consider this case.
! Changed some aliases not to use reserved keywords, although this is usually allowed...
-[Unknown]
FYI... It looks like MySQL will treat those reserved words stricter in the new version, 4.1.1 and up,
in order to compile with SQL:2003 standard.
Normally, when using any reserved word in SQL, MySQL allow programmer to use it but suppose to be
placed in between ` ` backticks.
In older version 4.0 down, somehow it ignore that; therefore programmer can use the reserved words in plain.
I think, not anymore in 4.1.1 up :( from now on programmer need to strictly follow the rules.
Thus, which I thought it's a MySQL bug might not be a bug after all.
(except 'errors' and some others which was a bug in 4.1.0 because they isn't in the reserved word list
this bug had been fixed in 4.1.1 release)
I already tested by placing mod in backticks `mod` and the error gone. (see the error in my first post)
You might need to check your codes now, folk, so do I, to make them work with newer version of MySQL :(
Best practice and to make your code as close to the standard as possible just avoid using those reserved words.
http://dev.mysql.com/doc/mysql/en/Reserved_words.html
http://dev.mysql.com/doc/mysql/en/News-4.1.1.html
Whiterabbit