Advertisement:

Author Topic: Because of MySQL 5.6 to 5.7 Upgrade ?  (Read 578 times)

Offline halimzhz

  • Newbie
  • *
  • Posts: 5
Because of MySQL 5.6 to 5.7 Upgrade ?
« on: September 07, 2019, 04:29:03 PM »
Dear All,

I'm a web hosting company and today one of my client reported about he's SMF forum giving an error:

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.

For your information i upgrade my MySQL from 5.6 to 5.7 few weeks ago and i dont do a test for this client forum, but i know when the server using MySQL 5.6, this client doesnt have any problem, what i found from Settings.php, the SMF version is 1.1

So is it possible to say the problem is because of MySQL 5.7 is not compatible with SMF 1.1 ? I did test with database connection and its not an issue with database username and password.

I hope somebody can advice on how to solve this problem. Thank you so much


Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,570
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #1 on: September 07, 2019, 04:39:08 PM »
I don't think that would break it. Issues occur if you upgrade your PHP version past 5.6
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline lurkalot

  • Support Specialist
  • SMF Hero
  • *
  • Posts: 1,449
  • Gender: Male
  • Tinyportal Support
    • guitaristguild on Facebook
    • Tinyportal on GitHub
    • @GuitaristGuild on Twitter
    • Guitarist Guild
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #2 on: September 07, 2019, 05:04:52 PM »
I don't think that would break it. Issues occur if you upgrade your PHP version past 5.6

According to the requirements, it say's "SMF 1.1 will not work on PHP 5.5 and may not work correctly on PHP 5.4"

https://wiki.simplemachines.org/smf/SMF1.1:Requirements_and_recommendations

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,673
    • StoryBB/StoryBB on GitHub
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #3 on: September 07, 2019, 05:05:41 PM »
SMF 1.1 uses the old MySQL adapter which won’t work properly on MySQL 5.7. (PHP version is not MySQL version.)

Since 1.1 is not getting any fixes or patches, our advice would be to get your client to upgrade to a version that didn’t originally come out 13 years ago, I.e. upgrade to 2.0.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline halimzhz

  • Newbie
  • *
  • Posts: 5
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #4 on: September 07, 2019, 06:47:46 PM »
Hi,

Thanks for all respond, that make me remind something when i compile PHP 5.3 with mysqlnd, that could be the issue, but i just advice the client to upgrade the SMF to the latest one, btw is there any issue if i directly upgrade SMF 1.1 to 2.1 instead of follow the step by upgrade to 2.0 and next is 2.1? Any experience on that ?

Thanks for advice

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,673
    • StoryBB/StoryBB on GitHub
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #5 on: September 08, 2019, 04:30:02 AM »
We don’t recommend that upgrade path at this time, we recommend 1.1 to 2.0 first. Note that 2.1 is not yet finished.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline halimzhz

  • Newbie
  • *
  • Posts: 5
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #6 on: September 08, 2019, 07:36:15 AM »
Hi,

Thanks for reply, now i'm doing upgrade, but i'm stuck at:

Database Changes
Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing upgrade script 1 of 3.
Executing: "Creating new tables and inserting default data..." (1 of 8 - of this script)

!!
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 'IGNORE TABLE smf_settings DROP PRIMARY KEY, ADD PRIMARY KEY (variable(30))' at line 1


I cant click Continue, please advice what should i do ? TQ so much


Offline halimzhz

  • Newbie
  • *
  • Posts: 5
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #7 on: September 08, 2019, 08:54:19 AM »
Hi,

Never mind, i found some of tables is corrupted and i restore from backup belong to MySQL 5.6, now i'm facing new error:

Converting other date columns...done
Checking for an old table...done
Creating "message_icons"...done
Inserting "message_icons"...done
Creating "package_servers"...done
Inserting "package_servers"...done
Updating flood control log...done
!!
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 'IGNORE TABLE smf_log_floodcontrol CHANGE COLUMN ip ip char(16) NOT NULL default ' at line 1


Now i'm lose on how to fix that, please help. TQ

 

Online shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,434
    • sbulen on GitHub
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #8 on: September 08, 2019, 10:46:50 AM »
In your forum root folder right now there should be a file named "upgrade_1-1.sql".

Open that file up and change all instances of "ALTER IGNORE TABLE" to "ALTER TABLE".   There should be 6 of them.

Hope this helps.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline halimzhz

  • Newbie
  • *
  • Posts: 5
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #9 on: September 08, 2019, 02:57:50 PM »


Thanks you so much, now problem solved.!!!



Online shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,434
    • sbulen on GitHub
Re: Because of MySQL 5.6 to 5.7 Upgrade ?
« Reply #10 on: September 08, 2019, 03:04:56 PM »
Good to hear!  I'll mark this one solved.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp