Opened 11 years ago

Closed 2 years ago

#11480 closed Bug Report - General (Trac EOL)

SQL syntax errors prevents listing of ROMs

Reported by: contactme@… Owned by: rcrdnalor
Priority: minor Milestone: needs_triage
Component: Plugin - MythGame Version: 0.26
Severity: medium Keywords:
Cc: Ticket locked: no

Description

When setting up a new emulator, I run in the following problem.

When searching for ROMs the following error shows up:

DB Error (MSqlQuery):
Query was:
INSERT INTO gamemetadata (system, romname, gamename, genre, year, gametype, rompath, country, crc_value, diskcount, display, plot, publisher, version, fanart, boxart, screenshot) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, '1', '1', ?, ?, ?, ?, ?, ?)
Bindings were:
:BOXART=NULL, :COUNTRY="Unbekannt", :CRC32="98bd4147", :FANART=NULL,
:GAMENAME="Mario Kart 64", :GAMETYPE="N64", :GENRE="Unbekannt N64",
:PLOT="Unbekannt", :PUBLISHER="Unbekannt", :ROMNAME="Mario Kart 64.v64",
:ROMPATH="/mnt/data/mythtv/games/emulators/n64/roms", :SCREENSHOT=NULL,
:SYSTEM="Mupen64plus", :VERSION="0", :YEAR="19xx"
Driver error was [2/1048]:
QMYSQL3: Unable to execute statement
Database error was:
Column 'fanart' cannot be null
2013-04-08 17:58:06.611280 N  MythGame:GAMEHANDLER: Update gametype N64

Furthermore a SQL synthax error pops up when listing the games (Media->Games->All Games->Mupen64plus). It looks like this error breaks mythgame. At least no games show up:

2013-04-08 18:22:16.141371 E  Error preparing query: select distinct  from gamemetadata where system in ('Mupen64plus') and trim(system)=:SYSTEM and  display = 1  order by ;
2013-04-08 18:22:16.141389 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 'from gamemetadata where system in ('Mupen64plus') and trim(system)=? and  displa' at line 1

The database is populated:

+-------+-------------+------------------------------------------------------------------+--------------------------------------------------------------+---------------+------+-----------+----------+-------------------------------------------+------------+--------+-----------+--------+----------+-----------+-----------+-----------+---------+---------+---------+
| intid | system      | romname                                                          | gamename                                                     | genre         | year | publisher | favorite | rompath                                   | screenshot | fanart | plot      | boxart | gametype | diskcount | country   | crc_value | inetref | display | version |
+-------+-------------+------------------------------------------------------------------+--------------------------------------------------------------+---------------+------+-----------+----------+-------------------------------------------+------------+--------+-----------+--------+----------+-----------+-----------+-----------+---------+---------+---------+
|   137 | Mupen64plus | Mario Kart 64.v64                                                | Mario Kart 64                                                | Unbekannt N64 | 19xx | Unbekannt |     NULL | /mnt/data/mythtv/games/emulators/n64/roms |            |        | Unbekannt |        | N64      |         1 | Unbekannt | 98bd4147  | NULL    |       1 | 0       |
+-------+-------------+------------------------------------------------------------------+--------------------------------------------------------------+---------------+------+-----------+----------+-------------------------------------------+------------+--------+-----------+--------+----------+-----------+-----------+-----------+---------+---------+---------+

Anny suggestions how to fix this?

Cheers, Bjoern

Attachments (1)

mythfrontend.log (8.3 KB) - added by contactme@… 11 years ago.

Download all attachments as: .zip

Change History (11)

Changed 11 years ago by contactme@…

Attachment: mythfrontend.log added

comment:1 Changed 11 years ago by contactme@…

Any news on this issue?

comment:2 Changed 11 years ago by Jonatan Lindblad

It sounds like the settings "Game display order" and/or "Favorite display order" are empty.

Have you visited the "General Settings" screen for MythGame??

comment:3 Changed 11 years ago by contactme@…

Yes, I did:

"Game display" was set to "system gamename" and "Favorite display order" was set to "gemename"

I changed it to just "gamename" deleted all metadata, but it didn't help.

comment:4 Changed 11 years ago by Jonatan Lindblad

Ok, and you did press "Finish" on that screen?

Please also execute the following command in mysql:

select * from settings where value like 'Game%TreeLevels?';

Thanks

comment:5 Changed 11 years ago by Bjoern Olausson <contactme@…>

Yes, I did press "Finish"

mysql> use mythconverg; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from settings where value like 'Game%TreeLevels';
+-------------------+----------+------------+
| value             | data     | hostname   |
+-------------------+----------+------------+
| GameAllTreeLevels | gamename | enterprise |
| GameFavTreeLevels | gamename | enterprise |
+-------------------+----------+------------+
2 rows in set (0.00 sec)

mysql>

comment:6 Changed 11 years ago by Raymond Wagner

Keywords: mythgames MySQL syntax error removed
Milestone: unknown0.27
Owner: set to Raymond Wagner
Priority: majorminor
Status: newaccepted

comment:7 Changed 11 years ago by Raymond Wagner

Milestone: 0.27unknown
Owner: Raymond Wagner deleted
Status: acceptednew

The first issue should not be happening as far as I can tell. Both INSERT queries in the plugin bind QString values to the artwork, and even if empty, it should be inserting an empty string, not NULL. There may be some strange behavior with QSqlQuery where it interprets empty strings as NULLs. I'll have to look into that further.

The second issue is caused by a real nasty mess of code (GameUI::getFillSql) used to generate a MySQL query. That whole thing should probably just get rewritten, and that's not going to happen for 0.27.

comment:8 Changed 4 years ago by rcrdnalor

Some updates and own observations:

Once I added a PC-Game in the configuration of Mythgame in mythfrontend, and do a 'scan for new games' and tick the setting-box for 'In depth Game Scan' for new games, do a scan for new games again, the mysql errors disappeared.

You need to follow the wiki pages on

https://www.mythtv.org/wiki/MythGame
and
https://www.mythtv.org/wiki/Configuring_MythGame_Emulation

I can now play the game 'tuxracer extreme' linked from the directory /usr/games/scripts without sql errors.

I still believe, that this issue belongs to #12254 as well and can be solved, once the settings for mythgame are initialized correctly.

comment:9 Changed 4 years ago by Stuart Auchterlonie

Milestone: unknownneeds_triage
Owner: set to rcrdnalor
Status: newassigned

comment:10 Changed 2 years ago by Stuart Auchterlonie

Resolution: Trac EOL
Status: assignedclosed

We have moved all bug tracking to github [1]

If you continue to have this issue, please open a new issue at github, referencing this ticket.

[1] - https://github.com/MythTV/mythtv/issues

Note: See TracTickets for help on using tickets.