Strict mode & upgrading MariaDB using both 3.4.x and 4.5.x

[wimg]

Hi All,

There used to be some SQL strict mode problems in the past with upgrading versions, and I am wondering if that is still the case.

Basically, I am still running 3.4.8, and a test site with a copy of that board on 4.5.4, on the same server, different domains.

The upgrade from MariaDB 10.0 to 10.1 was rather painful and time-consuming, basically because there was a problem with the standard supplied cPanel setup on the server, but that was fixed last night.

Next problem is to upgrade from 10.1 to 10.2, but it specifically warns with a critical error regarding the use of strict mode by default when doing the upgrade. I therefore need to know whether both 3.4.8 and 4.5.4 are strict mode compatible., and I could not find a definite answer here so far, the latest answer dating back to June, but not specifically for upgrading.

If you do know the answer, please let me know here, as I need to be on a supported MariaDB version asap, amongst others because of continued support on ICS, and because it requires hours less work to get things converted from 3.4.8 with a newer, supported version of MariaDB.

Thank you very much in advance for your replies!

Kind regards, Wim

P.S.: I know about "sql_mode=NO_ENGINE_SUBSTITUTION", but that appears to have no effect on the critical upgrade error messages.

Edited November 1, 2020 by wimg

[Ryan Ashbrook]

AFAIK, 3.4.8 was mostly compatible with strict mode, though it's been a very long time.

For Invision Community 4, we develop with strict mode enabled (it's a requirement of our development standards and actually enforced when in development mode), so yes it is fully compatible.

[wimg]

Thank you, Ryan, much appreciated.

I'll create a full backup of teh server prior to upgrading MariaDB - unfortunately that is teh only way to migrate back if for soem reason it does not work. At least I have good hopes now that it will.

Thanks again!

Kindest regards, Wim

[wimg]

Just a heads-up:

It appeared all was working well with 3.4.8 and MariaDB 10.1, so we upgraded to 10.2, only to find that even with strict mode off, the ACP no longer worked with 10.2. And we also found that with 10.1, the old Classifieds by Andy stopped functioning wrt adding new classifieds. As a result we are trying to roll back to 10.0 for the time being, and back to the drawing board for the actual conversion and upgrade to 4.5.4, which does work fully actually, even on our board now, and the addition of replacements for all the extra stuff we have on 3.4.8 🙂

Kind regards, Wim

[wimg]

Update, FYI:

Going from MariaDB 10.0 to 10.1 you need default values in columns, which is why Classifieds stopped working (the Andy M. version), and going from 10.1 to 10.2 the ACP stopped working, because "rows" is a reserved word in 10.2, and it is used in the ACP in views that get created.

Kind regards, Wim

 

[bfarber]

3.x is not going to work too well on many modern backend software (such as MySQL). The reality is, we recommend upgrading which supports newer versions of MySQL (and variants) much better.

[wimg]

1 hour ago, bfarber said:

3.x is not going to work too well on many modern backend software (such as MySQL). The reality is, we recommend upgrading which supports newer versions of MySQL (and variants) much better.

That is exactly what I am trying to do. I hadn't tried a conversion in quite a while, as we were still losing data/content a year or so ago when doing an upgrade, but with 4.5.4 we are not. The idea was to do a step-by-step conversion/upgrade on a couple of test sites first, considering all the add-ons we were using which are no longer supported either, while bringing the database to more recent levels to help with testing the upgrades we need. That clearly did not work, and downgrading MariaDB is not an option it seems.

Currently I am stuck, because I do get errors on the utf8 conversion - "#" as start of a comment apparently is deprecated; it just won't run right now. I am now busy setting up a WAMP-stack to try and do the utf8 conversion locally, and reupload the database thereafter, provided MariaDB 10.2 did not mess too much with the database.

Kind regards, Wim

[Nathan Explosion]

Wouldn't a middle-ground option with MariaDB work better?

On 11/2/2020 at 10:26 PM, wimg said:

It appeared all was working well with 3.4.8 and MariaDB 10.1

Upgrade MariaDB from 10.0 to 10.1, - let the dust settle, ensure all good with 3.4.8
Upgrade 3.4.8 to 4.5.4 - let the dust settle, ensure all good with 4.5.4
Upgrade MariaDB to 10.2 (or later)

You can probably tell I am a huge fan of "one change at a time" - I've lost count of the amount of times I've cringed when a customer (in my day job) has said "We plan to apply OS updates, then upgrade SQL, then make a firewall change, then upgrade the software you specialise in" 😄

[wimg]

15 hours ago, Nathan Explosion said:

Wouldn't a middle-ground option with MariaDB work better?

Upgrade MariaDB from 10.0 to 10.1, - let the dust settle, ensure all good with 3.4.8
Upgrade 3.4.8 to 4.5.4 - let the dust settle, ensure all good with 4.5.4
Upgrade MariaDB to 10.2 (or later)

You can probably tell I am a huge fan of "one change at a time" - I've lost count of the amount of times I've cringed when a customer (in my day job) has said "We plan to apply OS updates, then upgrade SQL, then make a firewall change, then upgrade the software you specialise in" 😄

Well that is what I tried to do, but it backfired regardless. With some tweaking I am running the utf8 conversion right now, so with some luck I will be able to get going again in a couple of hours time. Fortunately I do not give up all that easily ;).

It is just that I am no expert at anything when it comes to Linux and related stuff, which doesn't help, but I do manage with a couple of deep dives every now and then. This month I won't have to do anything else anyway, so I just plod on, until everything works as I want it to :). Trying to keep down time to a minimum but with the extra complications we have been down for a day amd a half now, and another day at teh beginnign of the weekend. The utf comversion will run for several hours, and teh upgrade for about 14 hours or so, after which I have to do the configuration and addition of replacement add-ons. I reckon this will take me 4 to 5 days all in all :).

Ah well, it is one of those things. I do keep my users informed of the process, we have a facebook setup as well, so we will all survive. It is nothing like we had happening in 2014, with the update from 2.x to 3.x <ROFL>.

Kind regards, Wim

Edited November 5, 2020 by wimg

[wimg]

The utf-conversion went well, I also updated the gallery name in the database wherever it occurred, and renamed the gallery directory accordingly - we had problems previously with that, as the gallery directory name was "gallery" 🙂.

Currently running the upgrade, with add-on directories removed, and everything else that is no longer relevant,  but with add-on tables still intact - we'll see how that goes.

Kind regards, Wim

Edited November 5, 2020 by wimg