Simple Machines Community Forum

MySQL AB recently announced their fifth version of MySQL to be ready for production use. Version 5 comes with a lot of new features, like stored procedures, triggers, and views. As of MySQL 5.0.12, a few changes have been made in the precedence of table joins. This causes some pages of Simple Machines Forum to stop working.

Simple Machines is therefore happy to release a compatibility patch for both 1.0.5 and 1.1 RC1. If you or your provider are planning on upgrading their MySQL server to 5.0.12 or higher, you can easily patch your forum in one of the following ways:

• Use the package manager in your administration center - one click, and you're done.
  
• Download the modification file, attached to this message, and modify the files manually according to it.
  
• Download the modified files, also attached to this message and overwrite your current files with them.
  
  Regardless of whether this patch is applied or not, SMF will install without a problem on a MySQL 5.0.12 server and the package manager will remain accessible.
  
  Addition - 12th December 2005
  Since the first patch another problem with capatibility has been identified. If you had applied the previous patch, and now find sorting in the personal message centre does not work, run the modification attached to this post called smf_mysql_5.0.12_patch2. For all people patching for the first time simply use smf_mysql_5.0.12.

Thank you!  :)

and I thought MySQL 5 was bad because it gave me errors! :P
will try this one..
thanks

this might be related to mysql 5?
http://www.simplemachines.org/community/index.php?topic=55642.0
i tried the patch and still receive that error.

Very speedy release :)

Just out of curiosity, does this run any better than MySQL4, or have you not had time to run benchmarks or quick comparisons?

Hard to say. It's often a matter of configuration. But in general, newer versions have slightly better query optimizations and might be a little faster. As a downside, they'd probably consume more memory as well ;)

Quote from: Compuart on November 07, 2005, 01:25:48 PM
Hard to say. It's often a matter of configuration. But in general, newer versions have slightly better query optimizations and might be a little faster. As a downside, they'd probably consume more memory as well ;)

Thanks, I would try MySQL5 on this machine but memory useage is at 88% (256MB Ram) so ill need to wait until I upgrade a bit.

Do I or should I apply this patch to my existing forum running v.1.05?  If so, any risks?

The patch works for both 1.0.5 and 1.1 RC1. There are no risks if you do and there are no risks if you don't apply the patch (at least, if the patch doesn't conflict with modifications you might've already installed). However if/when your host decides to upgrade MySQL to version 5, several pages might stop working (but you'll still be able to apply the patch afterwards).

Quote from: Compuart on November 07, 2005, 01:54:34 PM
The patch works for both 1.0.5 and 1.1 RC1. There are no risks if you do and there are no risks if you don't apply the patch (at least, if the patch doesn't conflict with modifications you might've already installed). However if/when your host decides to upgrade MySQL to version 5, several pages might stop working (but you'll still be able to apply the patch afterwards).

Great, thanks.

Future versions will be compatible right starting with RC2?

Definitely :)

Is the patch backwards compatible?  As in, can I safely apply it even if my host has not (or does not plan to) upgrade to MySQL 5?

Yes, it should be safe for all versions of MySQL supported by SMF (> MySQL 3.23.28)

Update went like a charm! I love that Package Manager! :P

Quote from: Aäron on November 07, 2005, 02:21:24 PM
Update went like a charm! I love that Package Manager! :P

Ditto.  I was a little nervous, but did it anyway.   :)

I got this email from my server:

"There has been a problem with the database!
MySQL reported:"

Not sure what the problem is.  There is not other text.

Help!

The patch downloaded well but installation gave an error:
You cannot download or install new packages because the Packages directory or one of the files in it are not writable!

This came after this message:
FTP Information Required
Some of the files the package manager needs to modify are not writable. This needs to be changed by logging into FTP and using it to chmod or create the files and folders. Your FTP information may be temporarily cached for proper operation of the package manager.

You need to make the files writeable.  CHMOD them to 777

How do I use chmod? (http://www.simplemachines.org/community/index.php?topic=23690.0)

Now I get this error:

File './smf/smf_log_online.MYD' not found (Errcode: 13)
File: /forum/Sources/Subs.php
Line: 1347

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.2. It is recommended that you execute the latest version of upgrade.php.

Getting Errors From MySQL? (http://www.simplemachines.org/community/index.php?topic=2486.0)

Quote from: JayBachatero on November 07, 2005, 03:03:40 PM
Getting Errors From MySQL? (http://www.simplemachines.org/community/index.php?topic=2486.0)

My error is not listed there and now my site is hosed.  Please help!

Errcode 13: Can't create/write to file ...

You should contact your host about this. Possibly a permission problem during the upgrade of mySQL.

Quote from: JayBachatero on November 07, 2005, 03:03:40 PM
Getting Errors From MySQL? (http://www.simplemachines.org/community/index.php?topic=2486.0)

Why? Isn't that why it asked for FTP info?

Gijs, that is not a solution for your problem. You need to enter your ftp username and password in the packagemanager, or chmod your files to make them writable.

eh, I tried both:

- I entered the FTP username/passw, but then got the message "You cannot download or install new packages because the Packages directory or one of the files in it are not writable"

- then I chmodded the packages directory&files 777

guessed that the first item (entering the FTP username/passw) should have done it already?

hmm

I didn't get any attachments in my e-mail.
(That's fine though, probably got around spam nukers for that very reason :P)

Quote from: gijs on November 07, 2005, 03:47:14 PM
eh, I tried both:

- I entered the FTP username/passw, but then got the message "You cannot download or install new packages because the Packages directory or one of the files in it are not writable"

- then I chmodded the packages directory&files 777

guessed that the first item (entering the FTP username/passw) should have done it already?

hmm

Yeah, it should.. Are you sure your path is correct?

Try creating a dir called temp in the attachments dir and chmod it to 777

that worked! (never would have guessed this solution  :D)

thanx alot!

Quote from: gijs on November 07, 2005, 04:43:21 PM
that worked! (never would have guessed this solution  :D)

thanx alot!

You welcome.  Im guessing it was having problems creating the temp dir itself.

Quote from: RoarinRow on November 07, 2005, 03:00:53 PM
Now I get this error:

File './smf/smf_log_online.MYD' not found (Errcode: 13)
File: /forum/Sources/Subs.php
Line: 1347

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.2. It is recommended that you execute the latest version of upgrade.php.

Can anyone help here?  I backup up my database before this sql upgrade patch, but when I store I still get the similar message regarding the same file. subs.php

I can not get to the Admin section or any section of the forum.  I tried to use the upgrade.php, but that didn't work.  I did repairsettings.php and that didn't work either.

Any other suggestions?

I get this message after restoring:

Got error 127 from storage engine
File: /forum/Sources/Subs.php
Line: 1347

Quote from: RoarinRow on November 07, 2005, 05:00:02 PM

Quote from: RoarinRow on November 07, 2005, 03:00:53 PM
Now I get this error:

File './smf/smf_log_online.MYD' not found (Errcode: 13)
File: /forum/Sources/Subs.php
Line: 1347

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.2. It is recommended that you execute the latest version of upgrade.php.

Can anyone help here?  I backup up my database before this sql upgrade patch, but when I store I still get the similar message regarding the same file. subs.php

I can not get to the Admin section or any section of the forum.  I tried to use the upgrade.php, but that didn't work.  I did repairsettings.php and that didn't work either.

Any other suggestions?

I answered your post on the previous page. You should contact your host. There is a problem with your database, not with SMF.

As well as this one:

Incorrect key file for table 'smf_log_online'; try to repair it
File: /forum/Sources/Subs.php
Line: 1347

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.2. It is recommended that you execute the latest version of upgrade.php.

Hi,
i'm curious to know exactly what you modified because i've already done by myself some modifications from few days ago, but on many more files and occurences on (v 1.1.RC1) in /sources Directory +SSI.php

by the way i replaced
for all the "SELECT"
with "LEFT JOIN"
either there is "FROM XXX AS Y" or "FROM XXX, YYY AS Y"

by a "FROM (XXX AS Y)" or  "FROM (XXX, YYY AS Y)"

I saw that you juste replace "XXX, YYY AS Y" cases
is that the only change??
seems that
as i checked with winmerge

The problem is the order of joins:

FROM A, B
   LEFT JOIN C

was before MySQL 5.0.12 interpreted as:
FROM ((A, B)
   LEFT JOIN C)

but after MySQL 5.0.12 interpreted as:
FROM (A, (B
   LEFT JOIN C))

If there's only one table after the FROM it can only be interpreted in one way, so the parentheses are unnecessary.

How can I use the compatabilty patch, I have no access to my BBoard. www.windward-island.net/forum.
What's wrong. Can someone help me!
WKR
eclectic

Great News!!

Thank you man!

Quote from: eclectics on November 07, 2005, 08:27:00 PM
How can I use the compatabilty patch, I have no access to my BBoard. www.windward-island.net/forum.
What's wrong. Can someone help me!
WKR
eclectic

Try the PHP 4.4.0 compatibility (http://www.simplemachines.org/community/index.php?topic=41955.0) first.

thanks
correct!

Quote from: Compuart on November 07, 2005, 08:14:02 PM
The problem is the order of joins:

Code Select Expand

FROM A, B
   LEFT JOIN C

was before MySQL 5.0.12 interpreted as:

Code Select Expand

FROM ((A, B)
   LEFT JOIN C)

but after MySQL 5.0.12 interpreted as:

Code Select Expand

FROM (A, (B
   LEFT JOIN C))

If there's only one table after the FROM it can only be interpreted in one way, so the parentheses are unnecessary.

if my professor could explain things like you do, then programming wouldn't be so hard....

Quote from: JayBachatero on November 07, 2005, 02:59:51 PM
You need to make the files writeable.  CHMOD them to 777

How do I use chmod? (http://www.simplemachines.org/community/index.php?topic=23690.0)

Yes, made chmod:
You cannot download or install new packages because the Packages directory or one of the files in it are not writable!

I installed the patch too, was nervous, and it worked like a charm.  Thank you Package Manager.  Out of curosity, 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?

Quote from: AzaToth on November 07, 2005, 09:56:15 PM

Quote from: JayBachatero on November 07, 2005, 02:59:51 PM
You need to make the files writeable.  CHMOD them to 777

How do I use chmod? (http://www.simplemachines.org/community/index.php?topic=23690.0)

Yes, made chmod:

Code Select Expand

You cannot download or install new packages because the Packages directory or one of the files in it are not writable!

Try this:

http://www.simplemachines.org/community/index.php?topic=55616.msg389980#msg389980

Quote from: JayBachatero on November 07, 2005, 04:04:43 PM
Try creating a dir called temp in the attachments dir and chmod it to 777

This worked with this patch, but now I get the same error when trying to install the 'Users Online Today Mod 1.1.2' (and now I do have a temp dir in the attachm.-folder)

Our forums are running fine on MySQL 5.0.15 now, thanks again!  :)

Quote from: gijs on November 08, 2005, 05:41:23 AM

Quote from: JayBachatero on November 07, 2005, 04:04:43 PM
Try creating a dir called temp in the attachments dir and chmod it to 777

This worked with this patch, but now I get the same error when trying to install the 'Users Online Today Mod 1.1.2' (and now I do have a temp dir in the attachm.-folder)

Ive seen this happen a few times to other visitors.  Its something with your host.  Deleted it create it again and chmod it to 777.

I just realized i posted the wrong dir.  for future refference its Packages dir.

Thnx! Will install it ASAP.

Cheers!

I'm getting the error messages:

QuoteNotice: Only variables should be assigned by reference in /home/we******/public_html/boards/Sources/Subs.php on line 232

Notice: Undefined index: is_admin in /home/we******/public_html/boards/Sources/Security.php on line 556

Notice: Undefined index: permissions in /home/we******/public_html/boards/Sources/Security.php on line 563

Warning: in_array(): Wrong datatype for second argument in /home/we******/public_html/boards/Sources/Security.php on line 563
session_start(): Cannot send session cookie - headers already sent by (output started at /home/we******/public_html/boards/Sources/Subs.php:232)

I searched these forums and found someone with identical error messages whose question led me to here for the answer.  I downloaded the corrected files for my version of SMF and installed them in the correct directories over the original files via FTP.

I'm still getting the exact same error messages.

Anyone have any ideas?  The site owner is getting a bit impatient to get his forums back online.

Quote from: Mike A on November 08, 2005, 02:46:15 PM
I'm getting the error messages:

QuoteNotice: Only variables should be assigned by reference in /home/we******/public_html/boards/Sources/Subs.php on line 232

Notice: Undefined index: is_admin in /home/we******/public_html/boards/Sources/Security.php on line 556

Notice: Undefined index: permissions in /home/we******/public_html/boards/Sources/Security.php on line 563

Warning: in_array(): Wrong datatype for second argument in /home/we******/public_html/boards/Sources/Security.php on line 563
session_start(): Cannot send session cookie - headers already sent by (output started at /home/we******/public_html/boards/Sources/Subs.php:232)

I searched these forums and found someone with identical error messages whose question led me to here for the answer.  I downloaded the corrected files for my version of SMF and installed them in the correct directories over the original files via FTP.

I'm still getting the exact same error messages.

Anyone have any ideas?  The site owner is getting a bit impatient to get his forums back online.

PHP 4.4.0 Compatibility (http://www.simplemachines.org/community/index.php?topic=41955.0)

Thanks, Jay, for the almost instant and correct answer.  You are indeed "da man".

Quote from: Mike A on November 08, 2005, 02:54:03 PM
Thanks, Jay, for the almost instant and correct answer.  You are indeed "da man".

You welcome.

okay, solve this:

i go to package manager, click the link for the patch and get this:

An Error Has Occurred!
The package you are trying to download or install is either corrupt or not compatible with this version of SMF.

the forum *is* 1.0.5.

On the other one, 1.1 RC1 i don't even get to see the packages. Just a:

Latest Packages
For the package manager to function properly, please upgrade to the latest version of SMF.


wtf? ???

I had to manually mod files to get it to install on 1.0.5

On my 1.1RC1 test boards I could not install it via  Latest Packages at all, not with chmodding or creating a temp file. I eventually got it to go by getting hold of the actual update file, uploading it and then installing. Even then I had to manually edit Posts.php on one straight forward test and both Posts.php and Load.php for one with TinyPortal 0.75 installed.

Seem OK now.

Finally got my site up last night.  Good thing I backed up my db before installing this patch.

I had to go into MySQL and empty the db in question, which was the 'online' db.  I emptied it, then went to my host to retore the backup.

I had done a lot of changes throughout the day to get the site up again, so I spent most of the day tweaking it get to a stable environment again.

Quote from: Aäron on November 07, 2005, 02:21:24 PM
Update went like a charm! I love that Package Manager! :P

I cant even get access to the package manager and the upgrade files do not seem to solve it either.

I get this..........Not Acceptable
An appropriate representation of the requested resource /forum/index.php could not be found on this server.

Apache/1.3.33 Server at www.8thrallycross.co.uk Port 80

This is the only screen that seems to work now.

http://www.8thrallycross.co.uk/forum/index.php

Anyone have any ideas please??

if our server is not going to update mysql until the whole family comes out, or not at all, will it matter if we apply the patch or not? Will applying the patch hurt?

Thanks,
Dan

Quote from: ukr on November 08, 2005, 05:06:46 PM

Quote from: Aäron on November 07, 2005, 02:21:24 PM
Update went like a charm! I love that Package Manager! :P

I cant even get access to the package manager and the upgrade files do not seem to solve it either.

I get this..........Not Acceptable
An appropriate representation of the requested resource /forum/index.php could not be found on this server.

Apache/1.3.33 Server at www.8thrallycross.co.uk Port 80

This is the only screen that seems to work now.

http://www.8thrallycross.co.uk/forum/index.php

Anyone have any ideas please??

Most likely Having problems with mod_security? (http://www.simplemachines.org/community/index.php?topic=34270.0)

Quote from: misterdan on November 10, 2005, 08:52:44 AM
if our server is not going to update mysql until the whole family comes out, or not at all, will it matter if we apply the patch or not? Will applying the patch hurt?

Thanks,
Dan

No, it won't matter on an earlier version of MySQL. It doesn't hurt either, unless you have modifications installed that conflict with this mod, but then you'll receive a warning before the package manager proceeds.

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:

Quote
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:

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

Also:

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

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

... in My.ini:

# 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:

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

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

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

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

Quote
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!

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

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

Quote from: Bonk on November 11, 2005, 08:00:03 PM
That would be an mkportal portal issue wouldn't it?

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

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.

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!

Okay thanks for the info.

Quote from: Bonk on November 11, 2005, 06:44:01 PM
On merging two threads today I got this error and both threads got eaten  :(:

Quote
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:

$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:

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

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

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??

Hi I'll get one of the following error:

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

Quote from: ss4vegito7 on November 15, 2005, 08:32:12 PM
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.

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.

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!

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

Anyone?

@ ss4vegito

It seems like you didn't overwrite all the files when you downloaded the upgrade package.  Also you did not run upgrade.php.  You will need to download 1.1 RC1 again the upgrade package, not install nor update.  Then using your FTP client overwrite all the files, run upgrade.php.  Then apply this patch.

NOTICE: Doing this will remove any mods that you have installed.

I used the 'over write your files' method.  unzip/FTP works like a champ...

Now, if I can get that damned email thang worked out....

Thanks Jay, everything works now.  :D

You're welcome ss4vegito7

Hi,

I have been getting errors with my new SMF install.  I am using PHP 5.1.1 and MySQL 5.0.

When i try to go into my boards i get: Database error Please try again. If you come back to this error screen, report the error to an administrator.

When i try and 'find and repair' errors i get the following:

Unknown column 't.ID_TOPIC' in 'on clause'
File: c:\Inetpub\wwwroot\Sources\RepairBoards.php
Line: 1390

Could this be along the same lines as some of the issues posted here?  Must admit much of it went over my head.

Any help you can give would be great, thanks.

Just apply the patch found in the first post.  MySQL 5.0.12 Compatibility patch (http://www.simplemachines.org/community/index.php?topic=55616.0)

Yeah, got it and it worked a treat.

Thanks!

You're welcome.

On sorting PMs by "from" I get the following error:

QuoteUnknown column 'pm.ID_MEMBER_FROM' in 'on clause'
File: ***\forum\Sources\InstantMessage.php
Line: 175

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.

Wouldn't upgrade.php have run when I updated to 1.0.5 and won't running it again undo any MySQL 5 corrections made to the db?

I suspect this error is another MySQL 5 incompatibilty in the SMF code.

The code of InstantMessage.php at and before line 175:

// Load the messages up...
$request = db_query("
SELECT pm.ID_PM, pm.ID_MEMBER_FROM
FROM {$db_prefix}instant_messages AS pm" . ($context['folder'] == 'outbox' ? ($context['sort_by'] == 'name' ? "
LEFT JOIN {$db_prefix}im_recipients AS pmr ON (pmr.ID_PM = pm.ID_PM)" : '') : ", {$db_prefix}im_recipients AS pmr") . ($context['sort_by'] == 'name' ? ("
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = " . ($context['folder'] == 'outbox' ? 'pmr.ID_MEMBER' : 'pm.ID_MEMBER_FROM') . ")") : '') . "
WHERE " . ($context['folder'] == 'outbox' ? "pm.ID_MEMBER_FROM = $ID_MEMBER
AND pm.deletedBySender = 0" : "pm.ID_PM = pmr.ID_PM
AND pmr.ID_MEMBER = $ID_MEMBER
AND pmr.deleted = 0") . "
ORDER BY " . ($_GET['sort'] == 'pm.ID_PM' && $context['folder'] != 'outbox' ? 'pmr.ID_PM' : $_GET['sort']) . ($descending ? ' DESC' : ' ASC') . "
LIMIT $_GET[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);


Sorting by "subject" or "date" works fine only sorting by "from" gives this error...

NOTE: still running 1.0.5 here.

I get the same results Bonk and have notified the developers.

Stupid MySQL. Replace with:


// Load the messages up...
$request = db_query("
SELECT pm.ID_PM, pm.ID_MEMBER_FROM
FROM ({$db_prefix}instant_messages AS pm" . ($context['folder'] == 'outbox' ? ($context['sort_by'] == 'name' ? ")
LEFT JOIN {$db_prefix}im_recipients AS pmr ON (pmr.ID_PM = pm.ID_PM)" : ')') : ", {$db_prefix}im_recipients AS pmr)") . ($context['sort_by'] == 'name' ? ("
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = " . ($context['folder'] == 'outbox' ? 'pmr.ID_MEMBER' : 'pm.ID_MEMBER_FROM') . ")") : '') . "
WHERE " . ($context['folder'] == 'outbox' ? "pm.ID_MEMBER_FROM = $ID_MEMBER
AND pm.deletedBySender = 0" : "pm.ID_PM = pmr.ID_PM
AND pmr.ID_MEMBER = $ID_MEMBER
AND pmr.deleted = 0") . "
ORDER BY " . ($_GET['sort'] == 'pm.ID_PM' && $context['folder'] != 'outbox' ? 'pmr.ID_PM' : $_GET['sort']) . ($descending ? ' DESC' : ' ASC') . "
LIMIT $_GET[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);


Note the first post has also been updated. You can simply run the modification called smf_mysql_5.0.12_patch2 if you prefer.

Quote from: MikeMill on December 12, 2005, 01:59:22 PM
I get the same results Bonk and have notified the developers.

Thanks. I supect those joins are the issue as that was changed in MySQL 5, I just forget how right now...

Quote from: Grudge on December 12, 2005, 02:31:21 PM
Stupid MySQL. Replace with:

Code Select Expand


// Load the messages up...
$request = db_query("
SELECT pm.ID_PM, pm.ID_MEMBER_FROM
FROM ({$db_prefix}instant_messages AS pm" . ($context['folder'] == 'outbox' ? ($context['sort_by'] == 'name' ? ")
LEFT JOIN {$db_prefix}im_recipients AS pmr ON (pmr.ID_PM = pm.ID_PM)" : ')') : ", {$db_prefix}im_recipients AS pmr)") . ($context['sort_by'] == 'name' ? ("
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = " . ($context['folder'] == 'outbox' ? 'pmr.ID_MEMBER' : 'pm.ID_MEMBER_FROM') . ")") : '') . "
WHERE " . ($context['folder'] == 'outbox' ? "pm.ID_MEMBER_FROM = $ID_MEMBER
AND pm.deletedBySender = 0" : "pm.ID_PM = pmr.ID_PM
AND pmr.ID_MEMBER = $ID_MEMBER
AND pmr.deleted = 0") . "
ORDER BY " . ($_GET['sort'] == 'pm.ID_PM' && $context['folder'] != 'outbox' ? 'pmr.ID_PM' : $_GET['sort']) . ($descending ? ' DESC' : ' ASC') . "
LIMIT $_GET[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);


Coolness! Thanks bunches. That fixed my copy of 1.0.5 just fine. I can sort my PMs by sender again!  :D

I get this error when I click on a forum message:  HELP!


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 '' at line 1
File: /home/rrhs/www/forum/Sources/Display.php
Line: 546

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.2. It is recommended that you execute the latest version of upgrade.php. 

QuoteIt is recommended that you execute the latest version of upgrade.php.

Quote from: Wizard13335 on December 22, 2005, 04:29:05 PM

QuoteIt is recommended that you execute the latest version of upgrade.php.

I have tried to upload with the package manager.....the  patches (smf_1-0-5_update.zip and smf_1-1-rc1_upgradeand I keep getting this error  :

An Error Has Occurred!
The package you tried to upload either is not a valid package or has become corrupted. 



HELP!

I just went with the MySQL manual file upload option for 1.1. RC1.

Is there any way to tell that this update is "Good to go".  Like at the bottom it says the version of SMF.

Anything like that regarding the MySQL update?  Like "Currently running MySQL compatibility update 5.0.12" or some feedback from the site.

Also, in trying to get the package manager to work, I think my problem may have been the 'local path to SMF'.  How do I find this out for the future when there are other package updates?


Thanks

Hi all...

I need a little bit of help here and I hope I have one.

So I'll make as mach as possible simple. Since I changed my server because they shut me down first time ( don't know reason ) I changed to a VPS server. I'm happy with it but the only problem is that i receive some strange characters when someone Quote someone else and some old post from the previous server, just some of them was totally a mes. This problem was when I had characters like ë and Ç used before and instead of them now I can see Squares and and some other characters that I don't know how they comming.

One example:

This is problematic:

QuoteTermi ?gnostik딠i referohej nj렧rupi heterogjen i ardhur nga rryma filozofiko-fetare antike, - t렣il봠pat뮠nj렰벨apje렴렭adhe n렳hekujt II-III-t묠pas Krishtit, - n렰벧jith볩 t렶endosur n렺onat m렫ulturore t력pokes, - sic ishin Roma dhe Aleksandria. Disa prej k봹re sekteve gnostike, kan렮j련istori t렭ir뤯kumentuar, nd벳a t렴jerat t렬뮡 n련ije, - n렫uptimin q렮uk njihen as themeluesit e tyre.

After I change the View/Encoding/Western European ( Windows ) is OK

QuoteTermi "gnostikë" i referohej një grupi heterogjen i ardhur nga rryma filozofiko-fetare antike, - të cilët patën një përhapjeë të madhe në shekujt II-III-të, pas Krishtit, - në përgjithësi të vendosur në zonat më kulturore të epokes, - sic ishin Roma dhe Aleksandria. Disa prej këtyre sekteve gnostike, kanë një histori të mirëdokumentuar, ndërsa të tjerat të lëna në hije, - në kuptimin që nuk njihen as themeluesit e tyre.

So as you can see I saw to many this kind of mistakes and I'm tyrerd fixing them one by one and the problem is that they steel hapening if somone quoted someone else but not so many just a few small problems.

I really don't know what's the problem and I need help.

I saw that those problems now are stored to the database. Hope is not serious.  :(

Any halp ???

how and in what way is this patch important?

Quote from: Compuart on November 06, 2005, 09:19:38 PM
MySQL AB recently announced their fifth version of MySQL to be ready for production use. Version 5 comes with a lot of new features, like stored procedures, triggers, and views. As of MySQL 5.0.12, a few changes have been made in the precedence of table joins. This causes some pages of Simple Machines Forum to stop working.

...

Regardless of whether this patch is applied or not, SMF will install without a problem on a MySQL 5.0.12 server and the package manager will remain accessible.

Addition - 12th December 2005
Since the first patch another problem with capatibility has been identified. If you had applied the previous patch, and now find sorting in the personal message centre does not work, run the modification attached to this post called smf_mysql_5.0.12_patch2. For all people patching for the first time simply use smf_mysql_5.0.12.

Hi everyone...I'm getting this error when I try to install this MY SQL patch through the package manager:

2: copy(SSI.php~): failed to open stream: Permission denied
File: /home/compuhel/public_html/Sources/Subs-Package.php
Line: 1307

Also...for some odd reason I can't alter my chmod settings for my source/packages/temp directory or any files in it on my ftp...when I change the folder from 755 to 777 and try to delete it...it fails and switches right back to 755.

The same goes for it's contents...they fail  to delete and switch right back to 666.

I recently had a mod/theme conflict issue and my host and I re-installed SMF 1.05 overwriting all of my old files and deleted the old theme, and now I'm using the default theme.

Everything looks and works fine now..but my package manager doesn't seem to want to work.

I am a novice at this, so I have no clue what that error means...so speak slowly please and use small words....there is now a dumb person in the room...lol

Thanks!

Quote from: #1taz on January 02, 2006, 04:18:59 PM
Hi everyone...I'm getting this error when I try to install this MY SQL patch through the package manager:

2: copy(SSI.php~): failed to open stream: Permission denied
File: /home/compuhel/public_html/Sources/Subs-Package.php
Line: 1307

Also...for some odd reason I can't alter my chmod settings for my source/packages/temp directory or any files in it on my ftp...when I change the folder from 755 to 777 and try to delete it...it fails and switches right back to 755.

The same goes for it's contents...they fail  to delete and switch right back to 666.

I recently had a mod/theme conflict issue and my host and I re-installed SMF 1.05 overwriting all of my old files and deleted the old theme, and now I'm using the default theme.

Everything looks and works fine now..but my package manager doesn't seem to want to work.

I am a novice at this, so I have no clue what that error means...so speak slowly please and use small words....there is now a dumb person in the room...lol

Thanks!

http://www.simplemachines.org/community/index.php?topic=28393.0

Try running that :)

Nope..didn't help..it is still the same - but thanks!

EDIT: Fixed it...my chmod on my Subs-Package.php was set wrong...once I changed it to 777....everything went fine.

Thanks for everything everyone.

Oh and BTW....SSI.php~ wasn't even there.

How's that for wierd?

The latest version in Downloads section should have all patches applied at the time.

It isn't obvious to browse the forum just to install SMF.

I just upgraded MySQL from 4. to 5.0.18 and applied the patch ( smf_mysql_5_0_12.tar.gz ) to my SMF 1.0.5 board. Everything seems to working fine with the exception of sorting by date in personal messages. Sorting by author/topic works fine. So my question is, will applying the second patch ( smf_mysql_5_0_12_patch2.tar.gz ) fix it?

The only other mod installed is PHP 4.4.0 Compatibility Update.

P.S. this isn't a deal breaker, I'm not going to lose any sleep over it, just curious.

Many thanks to the SMF team, you make us admins look good!

 8) Hahahahaha ! I spend an hour figuring it out .. deleting package, installing package ..
It turns out to be a [ Proceed ] button that I didnt press ..

Works like a charm .. what a noob ..

can i install this patch also, when i only got php4.4? i will try php5.. but...i am not shure if this works

Quote from: 3-CXT on February 03, 2006, 11:05:26 AM
can i install this patch also, when i only got php4.4? i will try php5.. but...i am not shure if this works

This is a MySQL patch, not a PHP patch.

oh my fault.. thnks a lot

atm iam unsing smf rc2
mods:
Enchanced post template    1.0
Hide Post, 1.0.7
WoW Itemstats for SMF 1.0.5/6, 1.1 RC1/RC2 (ver. 1.23)
WoW Itemstats 1.21 German Translation

do i need this patch too?

Versionsinformation:
Forum Version: SMF 1.1 RC2 (ausführlicher)
Aktuelle SMF Version: SMF 1.1 RC2
PHP Version: 5.0.5-Debian-0.8~sarge1
MySQL Version: 5.0.18-Debian_3.dotdeb.1-log
Server Version: Apache/2.0.54 (Debian GNU/Linux) mod_jk2/2.0.4 PHP/5.0.5-Debian-0.8~sarge1 mod_ssl/2.0.54 OpenSSL/0.9.7e
GD Version: bundled (2.0.28 compatible)

I Get this error if iam trying to delete a hole topic:


Datenbankfehler: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
Datei: /****/Sources/RemoveTopic.php
Zeile: 355

This error is not related to the patch, but a configuration error of MySQL. Either make sure the mysql socket is in that directory and accessible or configure MySQL to point the socket to another path. For the latter, locate your mysql.sock file (typically it might be in /tmp/mysql.sock or /var/lib/mysql/mysql.sock) and adjust the MySQL configuration file my.cfg (probably in the /etc directory) accordingly:
[mysqld]
socket=/var/lib/mysql/mysql.sock

thx for the quick to respond, i will try it this evening.

Does this board run with MySQL 5.0.19 too? Are there any error or compatibility bug with MySQL 5.0.19?

Caladan.

This should fix 5.0.012 and up including .19.

Compuart, were you commentaiting this?

Quote from: theiceman on February 20, 2006, 06:12:18 AM
atm iam unsing smf rc2
mods:
Enchanced post template    1.0
Hide Post, 1.0.7
WoW Itemstats for SMF 1.0.5/6, 1.1 RC1/RC2 (ver. 1.23)
WoW Itemstats 1.21 German Translation

do i need this patch too?

Versionsinformation:
Forum Version: SMF 1.1 RC2 (ausführlicher)
Aktuelle SMF Version: SMF 1.1 RC2
PHP Version: 5.0.5-Debian-0.8~sarge1
MySQL Version: 5.0.18-Debian_3.dotdeb.1-log
Server Version: Apache/2.0.54 (Debian GNU/Linux) mod_jk2/2.0.4 PHP/5.0.5-Debian-0.8~sarge1 mod_ssl/2.0.54 OpenSSL/0.9.7e
GD Version: bundled (2.0.28 compatible)

I Get this error if iam trying to delete a hole topic:


Datenbankfehler: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
Datei: /****/Sources/RemoveTopic.php
Zeile: 355

I'm having the very same problem with 5.0.19.

EDIT: And here we go.... finally, after about 13hours of fighting: http://www.simplemachines.org/community/index.php?topic=63232.msg438732#msg438732 THX!

Quote from: Caladan Brood on March 29, 2006, 05:57:09 PM
Does this board run with MySQL 5.0.19 too? Are there any error or compatibility bug with MySQL 5.0.19?

Caladan.

I am having issues with 5.0.19 and this forum.

Quote from: Compuart on November 06, 2005, 09:19:38 PM
MySQL AB recently announced their fifth version of MySQL to be ready for production use. Version 5 comes with a lot of new features, like stored procedures, triggers, and views. As of MySQL 5.0.12, a few changes have been made in the precedence of table joins. This causes some pages of Simple Machines Forum to stop working.

I am a bit confused about this.  I just tried to install SMF 1.1 RC2 on my local server with MySQL 5.1.7, Apache 2.2 with PHP 5.1.2 and can not get the installer to go beyond the page where I enter all the server information.  It keeps just coming back to that same data entry page.

Do I need to install the MySQL 5 compatibility patch for RC2?  I am not getting any error messages.

No you do not need the MySQL Compatibility patch.

Make sure that your MySQL is configured properly.

Quote from: JayBachatero on April 29, 2006, 04:06:51 PM
No you do not need the MySQL Compatibility patch.

Make sure that your MySQL is configured properly.

As far as I can tell, I have my MySQL configured correctly.  Other software works with it just fine.

Quote from: JayBachatero on April 29, 2006, 04:06:51 PM
No you do not need the MySQL Compatibility patch.

This just in..  ;)  I went back to MySQL 5.0.20 and so far things seem to be working.  I have not tried any maintenance functions, but have setup several forums, some with sub-boards, and that much seems fine now.  I could not even get SMF 1.1 RC2 (or any other version) to install and run on my local server until now.

error fatal

QuoteFatal error: Using $this when not in object context in /home/shinji/public_html/modules/mod_smf_recent_topics.php on line 30

line 30

Quoteif ($this->_result = $database->query()) {

This doesn't seem to be related to the MySQL Compatibility issue.  It seems to be an issue with the joomla/mambo bridge.

I've this error during activation of board news function (in this case smf 1.1 rc2) in mkportal :

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 >= 756 AND b.ID_BOARD IN (2) AND b.ID_BOARD = t.ID_BOARD ORDER BY m.ID_MSG DESC LIMIT 1
MySql Error returned: Unknown column 'm.ID_MEMBER' in 'on clause'
MySql Error code: 1054

Please help me, I've MySQL 5.0.23-standard.

This this not related to the MySQL Compatibility.  It's an issue with MKPortal.

Does this mod need any modification in order to work with 1.1RC3?
If so, what needs to be changed?

No RC3 is MySQL 5.0.12 compatible.

Quote from: Jay The Code Monkey on August 31, 2006, 02:13:45 PM
No RC3 is MySQL 5.0.12 compatible.

That's even better news! Many thanks in advance!

Quote from: JayBachatero on August 31, 2006, 02:13:45 PM
No RC3 is MySQL 5.0.12 compatible.

I updated mysql on my server to 5.0 and my forum 1.1.1 is down with sql errors. Someone please help! I need a patch to fix it. :(


ETA: never mind! It came back up all by itself.