Error upgrading from SMF 1.1.21 to SMF 2.0.15

[Combert]

Hi

I tried to upgrade my forum and got this error part into the upgrade:

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 game_log_floodcontrol CHANGE COLUMN ip ip char(16) NOT NULL defaul' at line 1

A little Googling suggested that this had something to do with my MySQL version being new?

Does anyone know how to fix this?

Thank you.

[GigaWatt]

Ask if your host has SQL strict mode enabled. If it has, ask them to disable it.

You'll also have to change your PHP version during the upgrade. First upgrade to 2.0.13, then to 2.0.14 and afterwards to 2.0.15. You can upload a fresh set of files after the upgrade to 2.0.15 to remove the 2.0.14 and 2.0.15 patches from the mod list.

[Combert]

Thank you for your reply.

My host is using MySql 5.7. It seems to me that Alter Ignore is deprecated in that version.

Do you think your suggestion about Strict mode, and upgrading to 2.0.13, then 2.0.14, then 2.0.15 still work on MySQL 5,7?

My PHP is currently 5.6. I planned to witch to 7 after the upgrade. Or maybe i was actually running 7 during the upgrade because i had overwritten my htaccess with the new files (My host is defaulting to php 7 unless there is a line of code in htaccess).

What is the recommended php version to run during the upgrade?

[Combert]

My host says SQL mode is set to:

NO_ENGINE_SUBSTITUTION
NO_AUTO_CREATE_USER

[Kindred]

no version of SMF under 2.0.14 will run on php 7

MySQL 5.7 (subversions) had some issues where they defaulted to strict mode, even if settings said otherwise.

However, the core SMF tables normally install and upgrade just fine on mySQL 5.7.
I don't think your issue is strict mode... The only issues I have ever seen with strict mode are related to mods which don't define a default value (which strict mode requires)

However, again... yes, it seems that as of 5.7.4, they completely removed the ALTER IGNORE function which does produce an error.

However, I notice that your error is not actually on ALTER IGNORE and that the error which I see in the line that your quoted seems to be a missing "t" at the end of "defaul"

[Combert]

Yes I noticed the "defaul" too.

However looking in the sql file for 1.1 it seems this is the place where things go wrong;

---# Updating flood control log...
ALTER IGNORE TABLE {$db_prefix}log_floodcontrol
CHANGE COLUMN ip ip char(16) NOT NULL default '                ';

ALTER TABLE {$db_prefix}log_floodcontrol
DROP INDEX logTime;
---#

I don't know why it says "defaul". I can't find that anywhere.

[vbgamer45]

It's ok the error message gets cut off on mysql.
The issue is the alter ignore.
Change
alter ignore table
to
ALTER TABLE

[Combert]

It's ok the error message gets cut off on mysql.
The issue is the alter ignore.
Change
alter ignore table
to
ALTER TABLE

Thank you. That worked perfect.

[vbgamer45]

Glad you got it working.