[mythtv] Collective MySQL wisdom please

[Gary Buhrmaster]

On Sat, Mar 12, 2022 at 11:20 AM Roland Ernst <rcrernst at gmail.com> wrote:
>
> It looks like one of the latest commits for MythWeb causes errors raised by MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
>
> Neither
>
> SELECT UNIQUE(people.name)
>
> nor
> SELECT DISTINCT people.name  ... ORDER BY credits.priority;
>
> works with MysQL 8.0 and probably earlier versions.
>
> Since I do not have the 'people' and 'credits' table populated, it is hard for me to test.
>
> Roland

Sorry for not fully testing.

I have no idea why I used UNIQUE() rather
than DISTINCT.  It was probably very late
and I was not fully thinking when I first wrote
the quick and dirty patch for my use case.

However, a MySQL change in later versions
of 5.7 (after 5.7.5) to include the
"ONLY_FULL_GROUP_BY" in the modes
(either directly, or indirectly) is the cause of
the ORDER BY error message.  I will note
that SQL-1999 Feature T301 allows this to
work as expected.  I would guess Oracle
is still planning to join the 21st century at
some point, we just don't know when.

SInce I am not paid millions of dollars (or
for that matter even a single dollar) to
promote (and ship) MySQL, and with
the one well known exception distros
now typically only ship MariaDB, and
every project that I care about has
moved to, or fully supports MariaDB as
the primary choice, I have not reliably
tested on MySQL for probably a decade
or so.

That said, this project continues to state
they believe in Oracle's vision first, and
other implementations are considered
to only be tolerated.  So a fix is needed.

I'll create a PR that supports both database
servers in another few days after I have
had some time to spin up a system where
I can test with MySQL 5.7 to make sure
there is not some other abnormal case
to be handled.

Thanks for the report.