MySQL 5.0.12 Compatibility patch

[Bonk]

Hmmm... wondered why we weren't getting any new members lately...

I just tried to register on our forums to test an install of the visual warnings mod and got this:

Database Error
Please try again. If you come back to this error screen, report the error to an administrator.

Our last new member corresponds to the time of the update.

Any ideas?

Related error from the log:

Database Error: Field 'lngfile' doesn't have a default value
File: \forum\Sources\Register.php
Line: 357

Also:

Database Error: Data too long for column 'validation_code' at row 1
File: \forum\Sources\Reminder.php
Line: 98

[Bonk]

Perhaps this is because I'm running MySQL 5 in "strict" mode?

... in My.ini:

Code Select Expand


# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


I have temporarily fixed the registrations issue by filling out the "register_vars" array as follows:

Code Select Expand


// Some of these might be overwritten. (the lower ones that are in the arrays below.)
$register_vars = array(
'memberName' => "'$_POST[user]'",
'emailAddress' => "'$_POST[email]'",
'passwd' => '\'' . md5_hmac($_POST['passwrd1'], strtolower($_POST['user'])) . '\'',
'posts' => 0,
'dateRegistered' => time(),
'memberIP' => "'$user_info[ip]'",
'is_activated' => empty($modSettings['registration_method']) || (!isset($_POST['emailActivate']) && $user_info['is_admin']) ? 1 : 0,
'validation_code' => !empty($modSettings['registration_method']) && $modSettings['registration_method'] == 1 ? "'$password'" : "''",
'realName' => "'$_POST[user]'",
'personalText' => '\'' . addslashes($modSettings['default_personalText']) . '\'',
'im_email_notify' => 1,
'ID_THEME' => 0,
'ID_POST_GROUP' => 4,
'lngfile' => "'english'",
'im_ignore_list' => "''",
'websiteTitle' => "''",
'websiteUrl' => "''",
'location' => "''",
'ICQ' => "''",
'AIM' => "''",
'YIM' => "''",
'MSN' => "''",
'avatar' => "''",
'usertitle' => "''",
'secretQuestion' => "''",
'secretAnswer' => "''",
'additionalGroups' => "''",
'buddyList' => "''"
);


I'm not 100% on which of these fields are only present with mods installed or if they are all in the default config.

I have yet to address the reminders issue.

[Compuart]

Thanks for looking into it. Will go through the code to see if there are more potential problems for MySQL in strict mode.

[Bonk]

You're most welcome, least I could do.

Re: the reminder issue, in the code in Reminder.php the new random password generated is max 10 characters long and the validation_code field in the members table is set to varchar(10) but it should be a varchar(32) because the MD5 hash (32 characters) is what is stored there, not the 10 character password. So I just modified my members table accordingly.

This oversized string must have been acceptable to MySQL prior to v5.0.15 and strict settings, interesting....

I notice also in my db that the passwd field is varchar(64) when only varchar(32) is necessary.
http://www.php.net/manual/en/function.md5.php
Though I have not modified this in my db, pending your confirmation.

Also, note:
http://www.dynaverse.net/forum/index.php/topic,163360317.0.html

[Bonk]

On merging two threads today I got this error and both threads got eaten  :

Database Error
Out of range value adjusted for column 'ID_POLL' at row 1
File: \forum\Sources\SplitTopics.php
Line: 720

Note: It appears that your database may require an upgrade. Your forum files are currently at version SMF 1.0.5, whereas your database is at version SMF 1.0. It is recommended that you execute the latest version of upgrade.php.

Thankfully nothing critical was in those threads. (I suppose if I really had to I could extract them from a backup)

I think I'll leave this one to you!

[TekserMan]

I'm using apache 2.0.55, mysql 5.0.14, php5.1, smf 1.1 rc1 and mkportal 1.1 rc1 and when I activate boardnews and set it for my announcement board save and then go to the portal home I now get this error:

"ERROR: Database error.
Cannot execute the query: SELECT m.posterTime, m.subject, m.ID_TOPIC, m.ID_MEMBER, m.ID_MSG, m.ID_BOARD, IFNULL(mem.realName, m.posterName) AS posterName, t.ID_BOARD, t.numReplies, b.name AS bName, m.body, m.smileysEnabled, m.Icon, mem.avatar, av.filename FROM smf_topics AS t, smf_messages AS m, smf_boards AS b LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER) LEFT JOIN smf_attachments AS av ON (mem.ID_MEMBER = av.ID_MEMBER) WHERE m.ID_MSG = t.ID_FIRST_MSG AND m.ID_MSG >= 0 AND b.ID_BOARD IN (2) AND b.ID_BOARD = t.ID_BOARD ORDER BY m.ID_MSG DESC LIMIT 3
MySql Error returned: Unknown column 'm.ID_MEMBER' in 'on clause'
MySql Error code: 1054"

I tried to roll back by uninstalling the mods I installed in the order of last to first which were the mood mod, users online mod and then the compatibility patch. After uninstalling the patch I got another database error and put the patch back, I didn't trap the error message but the error blocked me from accessing the forum home. I can access the forum features with no problem but mkportal has problems with the database now so I guess it's time to re-install and leave the patch alone for now. just wanted to see if I can do something else before I delete evrything! 

Thanks
TekSerMan

[Bonk]

That would be an mkportal portal issue wouldn't it?

[TekserMan]

That would be an mkportal portal issue wouldn't it?

Not if mkportal was working fine before the patch was applied!

[^Raven^]

Way back in http://www.simplemachines.org/community/index.php?topic=55616.msg390313#msg390313 I asked what pages, in particular, would be affected in SMF if the host upgraded to MySQL 5.0.12 and we didn't install the patch?  However, I never received a response.  Does someone have an answer and if so please share.

[TekserMan]

I'm just gonna take a stab in the dark and say Bonk has the right idea " an mkportal portal issue" and the issue is in the combo of mysql 5.0 for me mysql 5.0.14! because I just took one of my forums down and re-installed it and after no patching or modding I created an announcement board and went into the portal cp and pointed mkportal to it for use with board news and then activated the boardnews block and tried to go to the portal home and the error kicked in so my apologies to all it's not the patch the patch is clean from what I can see! My problem comes evidently from mysql 5.0.14 being compatible with mkportal 1.1 rc1!

Well back to the drawing board!

[^Raven^]

Okay thanks for the info.

[Bonk]

On merging two threads today I got this error and both threads got eaten  :

Database Error
Out of range value adjusted for column 'ID_POLL' at row 1
File: \forum\Sources\SplitTopics.php
Line: 720

Note: It appears that your database may require an upgrade. Your forum files are currently at version SMF 1.0.5, whereas your database is at version SMF 1.0. It is recommended that you execute the latest version of upgrade.php.

Thankfully nothing critical was in those threads. (I suppose if I really had to I could extract them from a backup)

I think I'll leave this one to you!

OK, OK, I'll do it...   

This nested ternary conditional at line 676 in my SplitTopics.php:

Code Select Expand


$target_poll = count($polls) > 1 ? (int) $_POST['poll'] : (count($polls) == 1 ? $polls[0] : -1);

returns -1 if neither thread to be merged is a poll, but the db field that result is stored in is a bigint(20) unsigned, which cannot hold a negative number.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Again, interesting that MySQL used to allow this...

So to fix this, as I notice the default for ID_POLL in the topics table is zero (which I assume is interpreted as no poll elsewhere in the SMF code), I simply changed the conditional to:

Code Select Expand


$target_poll = count($polls) > 1 ? (int) $_POST['poll'] : (count($polls) == 1 ? $polls[0] : 0);


Which allowed me to merge some test threads successfully, I'll now test whether it will correctly split threads and whether it will handle polls properly.

[Bonk]

Yup, with this "fix", I can now merge and split topics and polls properly. 

[ss4vegito7]

I can't attach files after the update!!

Fatal error: Call to undefined function: createattachment() in /home/mike/public_html/forum/Sources/Post.php on line 1441

There are so many problems, is there a version of SMF that runs smoothly with MYSQL 5??

[DarkCloud14]

Hi I'll get one of the following error:

Code Select Expand

Database Error: MySQL server has gone away
File: /www/xxx/xxx/html/forum/Sources/RemoveTopic.php
Line: 330

Ok I've found a way how you can reproduce this and I only get this error
when I try to delete a Topic or a Board with the previous Topic in it....

Ok here's how you can reproduce this error (or how I can reproduce this error  )

1. Create a user e.g. Testing
2. Login as Testing and upload an avatar (right upload it not choosing a url or default one..)
3. Create a new Topic e.g. Test
4. Logout and Login as Admin user
5. Upload an avatar again (if you haven't done this already..)
6. Reply to the Topic of Testing...
7. Try to delete the Topic or the Board which includes that Topic.

There you've the error...

Ok just set the Admin avatar to no pic or to url/default avatar
and now you can delete the topic again (if you've tried to delete the Board
you must delete the Board cause the Topic isn't visible anymore...)


Can someone else try this and post if they've the same problem ?

THX

[Bonk]

There are so many problems, is there a version of SMF that runs smoothly with MYSQL 5??

SMF 1.0.5 with this patch and the additional fixes I posted above runs smoothly on MySQL 5.
(note also that SSI.php requires fixes to work with php 5.0.5)

Use templates that match the version, or build your own from the default and minimise the number of mods used, ensuring they are compatible with your version of SMF.

Also, do not attempt to integrate with so-called "portal" scripts, most are horribly limiting and make your site look like it was produced with a rubber stamp, learn php and build your own site.

P.S. your error does not appear to be MySQL related, but rather php related.

[ss4vegito7]

OK, i already patched it up but i will go through the thread and use the fixes u supplied.

Also, I know a lot of mods don't work anymore but this one is popular and alot of Mambo people use it. I posted the problem and PHP here.
http://www.simplemachines.org/community/index.php?topic=56346.0
Maybe someone could help me with that.

[ss4vegito7]

Search doesn't work either, I have Patched everything and updated things That I found in this thread. Things still don't work.


Search Error

Table 'mike_mambo1.smf_log_search_subjects' doesn't exist
File: /home/mike/public_html/forum/Sources/Search.php
Line: 910





Attachement error

Fatal error: Call to undefined function: createattachment() in /home/mike/public_html/forum/Sources/Post.php on line 1441



Registration Error

Fatal error: Call to undefined function: isreservedname() in /home/mike/public_html/forum/Sources/Subs-Members.php on line 604


I need help with these!

[misterdan]

when I try to download the mysql patch I recieve the following error:

"The package you are trying to download or install is either corrupt or not compatible with this version of SMF."

I am using SMF 1.1 RC1 and my server will not update mysql until the whole family comes out. Although, I'd like to update now so I don't have to worry about it later. Should I just download the patch from here and upload the files to my site?

Thanks,
Dan

[ss4vegito7]

Anyone?