Mysql 8.0 added system to reserverd words

[jhavens1566]

Therefore system needs backticks around it.

Github link is here: ​https://github.com/MythTV/mythtv/pull/188

This commit is for datadirect.cpp in libmythtv

Without it the following error occurs:

2019-11-19 23:06:44.154754 E DB Error (programrating insert): Query was: INSERT IGNORE INTO programrating ( chanid, starttime, system, rating) VALUES (?, ?, ?, ?) Bindings were: :CHANID=2007, :RATING="TV-G", :START=2019-12-07T11:37:00.000Z, :SYS="VCHIP" No error type from QSqlError? Strange... 2019-11-19 23:07:06.312067 E Error preparing query: INSERT IGNORE INTO programrating ( chanid, starttime, system, rating) VALUES (:CHANID, :START, :SYS, :RATING) 2019-11-19 23:07:06.312079 E Driver error was [2/1064]: QMYSQL3: Unable to prepare statement Database error was: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system, rating) VALUES (?, ?, ?, ?)' at line 1

[Gary Buhrmaster]

As the internal schedules direct (datadirect) grabber has been removed in master (soon(ish) to be v31), you may be better off converting to one of the xmltv based grabbers now, rather than later, which provides a way forward today, and is what you will need to do anyway in the not too distant future. The wiki and/or the email archives have instructions of various detail (and lessons learned) as to how to do so.

[jhavens1566]

I see what you are saying, the funny thing is I am using xmltv and was still seeing this error. It turns out the culprit was in programdata.cpp. There is a new commit in the PR that fixes this issue. Now I can run mythfilldatabase without a hitch, I'm using the tv_grab_zz_sdjson_sqlite grabber.

[Bill Meek]

I'd say dbchecks.cpp is affected too.

[Gary Buhrmaster]

Looks like mythgame, too.

[jhavens1566]

Yes this has many fingers and trying to find them all might be a process. I made a few more commits on the PR but should I leave this to someone more familiar with the project to find them all?

[Gary Buhrmaster]

Running:

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('system') AND TABLE_SCHEMA='mythconverg';

Results in:

+----------------+
| TABLE_NAME     |
+----------------+
| gamemetadata   |
| programrating  |
| recordedrating |
+----------------+

showing the minimum set of tables names that should be reviewed and will need to be considered for code changes.

[Gary Buhrmaster]

btw, mythweb, in modules/tv/includes/programs.php, references the system column from the programrated table, so it looks to need changes too.

[Peter Bennett]

There is a report in the mailing list of a bunch of other issues MythTV has with MySQL 8

​http://lists.mythtv.org/pipermail/mythtv-users/2019-November/401704.html

[Bill Meek <billmeek@…>]

In 4a108b981/mythtv:

Error: Processor CommitTicketReference failed

GIT backend not available

[Bill Meek <billmeek@…>]

In f1859d9b5/mythtv:

Error: Processor CommitTicketReference failed

GIT backend not available

[Bill Meek <billmeek@…>]

In 08ebf057c/mythtv:

Error: Processor CommitTicketReference failed

GIT backend not available