Advertisement:

Author Topic: Database Error when use search and showposts from profile.[MySQL BUG or?]  (Read 8477 times)

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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
Code: [Select]
// 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
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline pulpitfire

  • Sophist Member
  • *****
  • Posts: 1,132
  • Gender: Male
  • This is what the php parser looks like.
    • Pulpitfire Devotionals
did you try Admin/Forum Maintenance/Find and repair errors?

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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  :'(
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
I sorta doubt it's that query... what shows up in your error log?  (the database error should be logged in there...)

-[Unknown]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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  :-[
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
Code: [Select]
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 :-[ ]
« Last Edit: March 25, 2004, 07:39:58 PM by whiterabbit »
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Why does it work for me?

-[Unknown]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
 :( 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  ::)
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
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]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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]
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline reyals

  • Newbie
  • *
  • Posts: 3
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?

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
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]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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.
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
MySQL has always allowed reserved words to be used, have they changed this?

-[Unknown]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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.
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
 ! Changed some aliases not to use reserved keywords, although this is usually allowed...

-[Unknown]

Offline whiterabbit

  • Semi-Newbie
  • *
  • Posts: 64
  • Feed your head
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
One pill makes you larger, and one pill makes you small
And the ones that mother gives you, don't do anything at all