[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mythtv-dev
Subject:    Re: [mythtv] Collective MySQL wisdom please
From:       Gary Buhrmaster <gary.buhrmaster () gmail ! com>
Date:       2022-03-18 4:23:41
Message-ID: CAMfXtQweaAQPtDZs6WfgbZgFqwp2bBy7-t6_Z+YCHgbytDp4aQ () mail ! gmail ! com
[Download RAW message or body]

On Sat, Mar 12, 2022 at 11:20 AM Roland Ernst <rcrernst@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.
_______________________________________________
mythtv-dev mailing list
mythtv-dev@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-dev
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
[prev in list] [next in list] [prev in thread] [next in thread]