Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Discussion corner for Developers of Serendipity.
Post Reply
DLange
Regular
Posts: 16
Joined: Fri Apr 11, 2008 1:16 am

Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by DLange »

The event_spamblock plugin silently stops updating the .htaccess file once a user upgrades from MySQL 5.6 to 5.7 because the latter now defaults to a "strict" mode that will make

Code: Select all

$q = "SELECT ip FROM {$serendipity['dbPrefix']}spamblock_htaccess WHERE timestamp > " . (time() - 86400*2) . " GROUP BY ip ORDER BY timestamp DESC LIMIT " . 20*$blocklist_chunksize;
fail with

Code: Select all

"ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column..."
This can be solved by:
  1. using ANY_VALUE(ip) which is MySQL 5.7+ only (this is the "technically correct" solution but it will make the query fail on 5.6 and below)
  2. prefixing the query removing the ONLY_FULL_GROUP_BY SQL_MODE (ugly but compatible to 5.6 and 5.7+)
Any preference?

There's also some fencing on the foreach iterating over the query results missing, I'll add that to a patch once you have given me indication of whether depreciating MySQL 5.6 support would be acceptable. Does s9y have a "minimum MySQL version" statement somewhere as of now?
DLange
Regular
Posts: 16
Joined: Fri Apr 11, 2008 1:16 am

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by DLange »

Actually https://riptutorial.com/mysql/example/26731/any-value-- warns against ANY_VALUE for apparently good reasons.
This did not survive a test on my blog for longer than an hour until it threw some empty values. For no apparent reason.
So ... seems the ugly solution is the way to go.
onli
Regular
Posts: 2822
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by onli »

Wider compatiblity is to be preferred. On the host I have my dev blog the MySQL version was 5.1. And that's a popular and reasonable german hoster (they are upgrading this year). So it's not like we can count on users being on current MySQL versions.

I assume prefixing the query would still work on older MySQL versions? If not this should be added after checking against the MySQL version.
DLange
Regular
Posts: 16
Joined: Fri Apr 11, 2008 1:16 am

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by DLange »

The problem is, as soon as you mention ONLY_FULL_GROUP_BY, this becomes MySQL / MariaDB specific. It would work down to ancient versions but I don't know what Postgres et. al. think of such SET statements. Not that anybody should run s9y with anything but MariaDB, but ... :-).

Also s9y doesn't use mysqli_multi_query so we'd have to adjust ONLY_FULL_GROUP_BY "globally" in serendipity_db_reconnect. All not so nice.

I may have a way to change the SQL statement that seems to survive both MySQL 5.6 and 5.7. I'll do a bit more testing and then send a PR.
onli
Regular
Posts: 2822
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by onli »

Great, looking forward to the PR :)

Just as an option, in other plugins and the core of serendipity we detect the used db engine and change the SQL query where required. Look at https://github.com/s9y/Serendipity/blob ... es.php#L91 for example.

Alternatively, now would be a good time to adress this in the core (since we are in an alpha phase), with the replacement patters in serendipity_db_schema_import for example. Just in case that it's something that needs to be adressed in all of s9y, if it is only in one plugin that might be overkill.

And just because I just struggled with that in a different context: Keep in mind that if ONLY_FULL_GROUP_BY is set with a global variable, on shared hosters users won't have the rights to change it.
onli
Regular
Posts: 2822
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by onli »

Thank you, merged!
DLange
Regular
Posts: 16
Joined: Fri Apr 11, 2008 1:16 am

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by DLange »

Thanks for the quick review, the merge and the mop up on the version number bump.
DLange
Regular
Posts: 16
Joined: Fri Apr 11, 2008 1:16 am

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by DLange »

This has not been added (backported) to 2.3.5 so it is still broken out there.
@thh, could get the commits from master (57cbaaa and d690a75) lined up for the next maintenance release?
(And @mentions don't work in phpBB :-))
thh
Regular
Posts: 419
Joined: Thu Oct 26, 2006 2:38 pm
Location: Stuttgart, Germany
Contact:

Re: Preferences for working around MySQL 5.6 -> 5.7 incompatibilities?

Post by thh »

DLange wrote: Mon Apr 27, 2020 4:41 pm This has not been added (backported) to 2.3.5 so it is still broken out there.
Oh, I'm sorry. Must have missed that commit. :-(
DLange wrote: Mon Apr 27, 2020 4:41 pm@thh, could get the commits from master (57cbaaa and d690a75) lined up for the next maintenance release?
(And @mentions don't work in phpBB :-))
Done. Thanks for your work!
Post Reply