MySQL database converted to MariaDB automatically

Question

Our company has a pretty old app built on php 5.6 and mysql. We recently decided to make some changes and change where its hosted. Though migration process with a tad bit of a pain, it went well for the most part.

The new host provider uses MariaDB instead of MySQL. The entire app works fine, except one part. This part is basically access profile data from the database. It gives the following error when we click on 'My Profile' -

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset asc, gmt asc' at line 1.

We don't know what version of MariaDB is needed because there's no way to tell. Everything worked fine when we were using MySQL.

Any feedback would be greatly appreciated. TIA.

We are not sure where to begin because the php code doesn't list any reference to MariaDB version.

Answer 1

I strongly suspect (because the syntax has asc) you have a column name called "offset". MariaDB added offset as a keyword in MariaDB 10.6.

What this means is the keyword needs to be quoted in the sql with backticks `.

Look for the word "offset" in your codebase and in the php code running SQL, that has offset asc, gmt asc change this to `offset` asc, gmt asc. There may be other uses of the word "offset" that also need quoting.

Answer 2

It is very unlikely that the error is a direct result of changing from MySQL to MariaDB. It may arise due to a change to a more recent version of either database (presumably the database was also "pretty old"). It can also arise due to logic errors arising from failed dependencies. But its probably because you are using a reserved word as an attribute name.

The last release of PHP 5.6 was over four years ago. Your implementation may contain significant vulnerabilties. I do hope that you are restricting public access to this if it is hosted on the internet.

You describe the migration as "painful" - but for the PHP and MySQL stuff this should be trivial. However it is clear from your question that you don't have a lot skills with PHP nor MySQL. If your company is running its own services, then you should really think about hiring stuff to support these or engaging a MSP.

Yes, you could learn how to do this yourself. If you already have a first degree in computer science or software engineering then it will take a couple of hours to get to a reasonable level of proficiency.

We can't tell you why the code is behaving this way because we cannot see the code nor the logs.