PHP problem with setting global time of a mysql connection

[softlab]

Hi.

I have a problem with setting the timezone in my connection to mysql from php. The php version of my site was 5.2.17 and all was ok. But, if I set php version to 8.1.17 from Plesk, to make the site use a not-outdated version of php, the execution of this instruction

mysqli_query($conn, "SET GLOBAL time_zone='Europe/Rome'");

gives the following error:

Fatal error: Uncaught mysqli_sql_exception: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

How can I solve this?

Thanks

[wolstech]

So, PHP 7 and 8 are not code compatible with 5.x. If your site was made for 5.2, odds are the highest version you're going to be able to run it on is 5.6 without things breaking or needing to be completely rewritten.

[wolstech]

If you want to go to a higher version than 5.6, you'll likely need to either upgrade the software version by version until you get to a version that supports 7 or 8 (if such a path exists), or throw your site out and start over.

[softlab]

Thanks for responses.

It sounds me strange, because on my PC the same software on a server which runs PHP 8.2.0 doesn't have any problem. Could it be related to the fact that on Heliohost the DB is MariaDB, while on my PC is MySql?

The other fact is that, if I comment the instruction that causes the problem, the site functions correctly.

Thanks

[wolstech]

It could be. I also found online that this command changes the entire server's timezone, which obviously you can't do since you're one of several thousand users. The server is permanently set to UTC. It's erroring because you're indeed not allowed to do that here.

Since the site works without it, just comment it out, and if you need time offsets, implement them in PHP.

[softlab]

Thanks for this last explanation, I'll do as you suggested

[Kairion]

Hi @softlab,

  

14 hours ago, softlab said:

mysqli_query($conn, "SET GLOBAL time_zone='Europe/Rome'");

This code is setting the time zone for the entire server, which is not allowed on shared hosting such as HelioHost like wolstech explained.

Could you please try the following code and let us know if it works for you?:

mysqli_query($conn, "SET time_zone='Europe/Rome'");

This one is used for changing the time zone in your current session only, not on the entire server and thus should work.

[Haradion]

Sort of a side note: with that big of a version jump, you might consider running a static analyzer like Phan to see what it picks up in terms of deprecated/broken functionality, etc. It'll probably pick up a number of other issues, some of which will be false positives, but there are various techniques to try to weed some of those out. The most relevant option for this use case will be backward_compatibility_checks.