Notes at mysql.com on changes in version 4.1

[zinco]

This is starting to get way over my head. We need a PHP/SQL expert to point us in the right direction.

[princess]

yah i tried figureing it out as well but its pretty difficult.. i guess we pay powweb to do their job. :)

[zinco]

See this thread.

http://forum.powweb.com/showthread.php?p=264421#post264421

This solution was applied to mx_portal with phpbb2 boards but i think it applies to all php scripts.

[zinco]

The solution I found worked on my mx-portal/ phpbb2 site.
mod is a reserved word in MySql 4.1 .......see this thread:
http://forum.powweb.com/showthread.php?t=45463

[princess]

i use invision and i did not find anyt mod on my index.php file :(

[Maelstrom]

This seems to suggest that I should rename any instance of "mod" to "mod2" in every php file for my board?

[djwtwo]

You wouldn't need to replace 'mod' with 'mod2' everywhere in PHP, just whereever it's used in building or interpreting queries to MySQL.

If you're using third-party PHP packages, I'd check their web sites for updated versions. If you wrote the PHP yourself, hopefully you already know what I'm talking about.

[zinco]

This seems to suggest that I should rename any instance of "mod" to "mod2" in every php file for my board?

That is what it seems to me. Luckily I only had 3 files to edit.

Princess can you post the exact error you are getting when you go to your site?

[princess]

mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1416'

mySQL 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.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Wednesday 12th of January 2005 01:21:28 PM

[Maelstrom]

mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1416'

mySQL 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.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Wednesday 12th of January 2005 01:21:28 PM


That is the exact error I am getting as well.

[agueviv]

me too...

[Xinil]

If mod is a reserved word, try putting "`" around your columns.

Example:

`mod.member_id`=`m.id`

[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.

[DonB]

Im getting that exact same error with my invision boards.

Anyone have any sugestions on where to start? What page do you think is causing the error? I opened index.php but couldnt find anything that looked like what you are talking about.
thanks

[acfishing]

Same problem here, it seems as if nobody knows how to fix us users with invision board. Is it just me or do we pay this company to host our sites and just make more problems for us that we can't solve, nor them for that matter?

[zinco]

Can you guys put links to the page that is giving the error?

[acfishing]

---------------------------


The error only shows when members log in


----------------------------


mySQL query error: SELECT mod.mid as is_mod, m.id, m.name, m.mgroup, m.password, m.email, m.allow_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track,
m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post,
m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators mod ON (mod.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.id='1'

mySQL 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.member_id=m.id OR mod.group_id=m.mgroup )
WHERE m.' at line 6
mySQL error code: 1064
Date: Wednesday 12th of January 2005 06:25:21 PM

[sfriedberg]

Example:

`mod.member_id`=`m.id`

Hmm, no, that should be `mod`.`member_id`, and `m`.`id`

Backticks around each SQL table or column name (between dots) that might be a reserved word, not around the entire thing (database.table.column).

mod_module would not need a change because it is one word. Why mod.something does I do not know.

The database servers were changed from mySQL 4.0 to mySQL 4.1. "mod" became an SQL reserved word in mySQL 4.1. So SQL queries that used "mod" as the name of a table or column now need to be tweaked. That means "mod.anything" or "anything.mod" or even just "mod" by itself. You can either change the name of the table or column, or put backticks around the existing name.

FYI, there are over 150 reserved words in mySQL's dialect of SQL. The only reason "mod" is causing a problem is that the previous major version didn't treat it as reserved, and the applications everyone's complaining about used it as a table name. Unfortunately, this change of treatment for "mod" is not mentioned in mySQL's "upgrading from 4.0 to 4.1" notes!

[DonB]

Ok thats fine information but unfortunatly I have no idea on what to do with it. I opened all of the php files that I could find and didnt find mod at all. Can someone who knows these things please be a bit more specific as to what files to change?

Thank you

[ticoroman]

Is it just me or do we pay this company to host our sites and just make more problems for us that we can't solve, nor them for that matter?

Hello everybody! I know it's hard when things doesn't work the way they are expected, but this one actually isn't PowWebs fault. You should install updated versions of your PHP-applications, or ask for help who ever made / sold your applications. These are third party applications and you can't expect support from Powweb for it...

[zinco]

Don post a link to the page where the error is occuring or post the whole text of the error in here. Sometimes it depends on how you configure your search whether it will show up. Searching for "mod" may not show "mod.something". The message should show the line number, right above that is where the error occured.

Unfortunately for me I have customized so much stuff I think it would be to much hassle to upgrade my mx portal and phpbb2. What php files are you using? Using free open source software does have its drawbacks. I am not sure but I think MySql is open source freeware. They did not even mention the mod thing in their upgrade notes.

Powweb could maybe do a little more research for potenial problems before doing an upgrade.