Simple Machines Community Forum

Customizing SMF => SMF Coding Discussion => Topic started by: whiterabbit on March 25, 2004, 05:36:31 PM

Title: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 25, 2004, 05:36:31 PM
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
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: pulpitfire on March 25, 2004, 05:42:55 PM
did you try Admin/Forum Maintenance/Find and repair errors?
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 25, 2004, 06:15:58 PM
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  :'(
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on March 25, 2004, 06:32:31 PM
I sorta doubt it's that query... what shows up in your error log?  (the database error should be logged in there...)

-[Unknown]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 25, 2004, 06:43:28 PM
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  :-[
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 25, 2004, 07:08:38 PM

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 :-[ ]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on March 25, 2004, 08:17:56 PM
Why does it work for me?

-[Unknown]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 26, 2004, 12:31:50 AM
 :( 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  ::)
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on March 26, 2004, 12:35:20 AM
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]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on March 26, 2004, 12:49:08 AM
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]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: reyals on April 06, 2004, 09:23:49 AM
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?
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on April 06, 2004, 10:17:55 AM
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]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on April 18, 2004, 05:34:51 PM
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.
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on April 18, 2004, 06:58:28 PM
MySQL has always allowed reserved words to be used, have they changed this?

-[Unknown]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on April 18, 2004, 07:23:52 PM
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.
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: [Unknown] on April 18, 2004, 07:34:13 PM
 ! Changed some aliases not to use reserved keywords, although this is usually allowed...

-[Unknown]
Title: Re: Database Error when use search and showposts from profile.[MySQL BUG or?]
Post by: whiterabbit on April 19, 2004, 08:53:46 PM
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