Opened 8 months ago

Closed 7 months ago

Last modified 7 months ago

#13516 closed Bug Report - General (fixed)

Mysql 8.0 added system to reserverd words

Reported by: jhavens1566 Owned by: Bill Meek
Priority: minor Milestone: 31.0
Component: MythTV - General Version: Master Head
Severity: medium Keywords: mysql datadirect.cpp
Cc: Ticket locked: no

Description

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

Change History (12)

comment:1 Changed 8 months ago by 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.

comment:2 Changed 8 months ago by 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.

comment:3 Changed 8 months ago by Bill Meek

I'd say dbchecks.cpp is affected too.

comment:4 Changed 8 months ago by Gary Buhrmaster

Looks like mythgame, too.

comment:5 Changed 8 months ago by 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?

comment:6 Changed 8 months ago by 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.

comment:7 Changed 8 months ago by 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.

comment:8 Changed 8 months ago by Bill Meek

Component: MythTV - MythfilldatabaseMythTV - General
Milestone: needs_triage31.0
Owner: set to Bill Meek
Status: newaccepted
Version: UnspecifiedMaster Head

comment:9 Changed 8 months ago by 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

comment:10 Changed 7 months ago by Bill Meek <billmeek@…>

Resolution: fixed
Status: acceptedclosed

In 4a108b981/mythtv:

mythweb: Care for MySQL v8.0 use of "system" as a reserverd word

Change system to system in all queries.

Thanks to jhavens1566 and Gary Buhrmaster

Fixes: #13516

comment:11 Changed 7 months ago by Bill Meek <billmeek@…>

In f1859d9b5/mythtv:

DB: Care for MySQL v8.0 use of "system" as a reserverd word

Change system to system in all queries.

Thanks to jhavens1566 and Gary Buhrmaster

Fixes: #13516
(cherry picked from commit 4a108b98172739a335d2e8a280643f7c8de5d280)

comment:12 Changed 7 months ago by Bill Meek <billmeek@…>

In 08ebf057c/mythtv:

DB: Care for MySQL v8.0 use of "system" as a reserverd word

Add: system to system changes for datadirect (that doesn't
exist in v31-Pre.

Thanks to jhavens1566

Fixes #13516

Note: See TracTickets for help on using tickets.