mod = reserved word in mysql 4.1

[zinco]

Found this at mx portal site: http://www.mx-system.com/forum/viewtopic.php?t=5337
Seems its gonna be a problem with any scripts that have the word "mod" in them.
http://dev.mysql.com/doc/mysql/en/Reserved_words.html

This is what I did for Mx Portal with PHPBB2. The same idea applies to any php scripts that uses SQL and has either "mod" or "mod.something" in the sql block. Mod is a reserved word in MySql 4.1 now and apparently was not in 4.0.
__________________________________________________ _____________________
Posted: 08 Oct 2004 09:49 Post subject: Reply with quote
Nobody's care so i've debugged myself Wink

For those who have the same problem (i'm sure there are ...), you have to modify the following (maybe more but for the moment everything seems working) :

index.php, lines 111 & 114
mx_menu_nav.php, lines 231 & 233
mx_multiple_blocks.php, lines 50 & 52
admin/admin_mx_module.php, lines 233 & 234

Change the mx_module table alias in query from mod to mod2 for example.

example on index.php :

Quote:
$sql = "SELECT blk.block_id, blk.auth_view_group, blk.auth_edit_group, bct.column_id, module_path, function_file, auth_view
FROM " . COLUMN_BLOCK_TABLE . " bct,
" . BLOCK_TABLE . " blk,
" . FUNCTION_TABLE . " fnc,
" . MODULE_TABLE . " mod2
WHERE blk.function_id = fnc.function_id
AND blk.block_id = bct.block_id
AND fnc.module_id = mod2.module_id
ORDER BY column_id, block_order";


+
_________________
can people hear shouting in cyberspace ?

http://www.legeek.tk
Base de données d'images inspi JDR :
http://legeek.free.fr/inspic

[zinco]

Hey that worked for my site. :)

[agueviv]

zinco, what exactly you did? what php sofware are you using?

regards...

[zinco]

I am using MX Portal with PHPBB2 but this would apply to any php files that use MySql. I open and edit them with HTML Kit.
http://www.mx-system.com/forum/viewtopic.php?t=5337
http://www.chami.com/html-kit/

[zinco]

It is definitely a problem with the word mod. Any php scripts that use the word "mod" or "mod.something" will mess things up.
SELECT mod.mid
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )

The php script sends and sql command like SELECT and SQL encounters the word "mod" and doesn't know what to do with it because it is a reservered MySql 4.1 word.

Start by figuring out which file is causing the error. Like if it is your home page it is most likely index.php where the problem is. Other pages could be affected too if they have mod in there files.
Open the php file and change "mod" where it is all by itself and "mod.anything" to "mod2" and "mod2.anything". Only change it in the parts that involve SQL Queries.....$sql=
This is the best I understand it so far. note- mod_module would not need a change because it is one word. Why mod.something does I do not know.

else
{
$sql = "SELECT blk.block_id, bct.column_id, module_path, function_file, auth_view
FROM " . COLUMN_BLOCK_TABLE . " bct,
" . BLOCK_TABLE . " blk,
" . FUNCTION_TABLE . " fnc,
" . MODULE_TABLE . " mod2
WHERE blk.function_id = fnc.function_id
AND blk.block_id = bct.block_id
AND fnc.module_id = mod2.module_id
ORDER BY column_id, block_order";
// AND bct.column_id = " . $column_rows[$column]['column_id'] . "

117 mx_message_die(GENERAL_ERROR, "Could not query modules information", "", __LINE__, __FILE__, $sql);
Usually the error will tell you what file and at what line the error ocurred at. For instance my error was occuring at line 117 of index.php and right above that was the sql block that was causing it. I use HTML Kit to edit php files.

[zinco]

If you give me a link to page you are having trouble with I may be able to help further. I am not a coder or expert but have gotten good at trouble shooting, searching and tracking things down.......because I have had too!!!!