1.8 PersistenceException Cannot read list of genres for medi

I've just updated a 1.7 installation to v1.8 and the library rescan is filling up the log file with an error for each media item. The error looks like this:

  Code:

017-01-29 00:28:28,089 WARN  [MediaFileIndexer] Cannot update file myfile.mp4 in library because of an unexpected error. Message: Cannot read list of genres for media item 10316
org.serviio.db.dao.PersistenceException: Cannot read list of genres for media item 10316
        at org.serviio.library.dao.GenreDAOImpl.getGenresForMediaItem(GenreDAOImpl.java:291)
        at org.serviio.library.local.service.GenreService.updateGenresForMediaItem(GenreService.java:91)
        at org.serviio.library.local.service.VideoService.updateVideoInLibrary(VideoService.java:259)
        at org.serviio.library.local.indexing.MediaFileIndexer.updateMediaFile(MediaFileIndexer.java:150)
        at org.serviio.library.local.indexing.DefaultLibraryMonitor.fileUpdated(DefaultLibraryMonitor.java:65)
        at org.serviio.library.local.indexing.pipe.PipeManager$PipeProcessingConsumer.lambda$run$27(PipeManager.java:175)
        at java.util.Optional.ifPresent(Optional.java:159)
        at org.serviio.library.local.indexing.pipe.PipeManager$PipeProcessingConsumer.run(PipeManager.java:169)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY lower(genre.name)' at line 1
        at sun.reflect.GeneratedConstructorAccessor14.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
        at org.serviio.library.dao.GenreDAOImpl.getGenresForMediaItem(GenreDAOImpl.java:288)
        ... 8 more


I'm running Oracle JDK 1.8.0_121 on CentOS 7.3 x64, and the database is MariaDB 10.1.20. Perhaps there's a bug in the SQL query used?

[zip]

what is MariaDB? Serviio uses Derby (enbedded), and possibly mySQL.

what is MariaDB? Serviio uses Derby (enbedded), and possibly mySQL.

https://mariadb.org/about/

MariaDB is a MySQL fork which uses the same wire protocol and SQL syntax. I've successfully used it on Serviio 1.6 and 1.7, which is why I've been surprised to see those error messages.

Upon further investigation, there is at least one SQL query which looks suspicious.

I have extracted the contents of the serviio.jar file (jar xf serviio.jar) and then I have run

  Code:

strings org/serviio/library/dao/GenreDaoImpl.class

and in the output I can see

  Code:

,Retrieving list of genres for media item %s
SELECT genre.id as id, genre.name as name FROM genre, genre_item WHERE genre.id = genre_item.genre_id AND genre_item.media_item_id = ?ORDER BY lower(genre.name)
,Cannot read list of genres for media item %s

The above query is missing a space between the question mark and the ORDER keyword, if for example the media_item_id value is 1000 then the query would become

  Code:

SELECT genre.id as id, genre.name as name FROM genre, genre_item WHERE genre.id = genre_item.genre_id AND genre_item.media_item_id = 1000ORDER BY lower(genre.name)

which is not valid SQL.

[zip]

Thanks, will create a bug ticket.

https://bitbucket.org/xnejp03/serviio/i ... -sql-query