SQL syntax errors prevents listing of ROMs

[contactme@…]

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

[contactme@…]

Any news on this issue?

[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?

[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.

[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

[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>

[Raymond Wagner]

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.

[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.

[Stuart Auchterlonie]

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