Opened 7 years ago

Last modified 6 years ago

#11480 new Bug Report - General

SQL syntax errors prevents listing of ROMs

Reported by: contactme@… Owned by:
Priority: minor Milestone: unknown
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@… 7 years ago.

Download all attachments as: .zip

Change History (8)

Changed 7 years ago by contactme@…

Attachment: mythfrontend.log added

comment:1 Changed 6 years ago by contactme@…

Any news on this issue?

comment:2 Changed 6 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 6 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 6 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 6 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 6 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 6 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.

Note: See TracTickets for help on using tickets.