Timezone issue causes MySQL errors in media scanning

Need help? Post your questions here.

Moderator: moderators

Timezone issue causes MySQL errors in media scanning

Postby stukafox » Thu Dec 31, 2020 6:47 pm

Hello,

It appears that some hard-set value in the .war version of Subsonic is causing a failure to write media data to MySQL after scanning:

>tail -f /var/log/subsonic.log
(...)
[2020-12-31 10:14:22,458] ERROR MediaScannerService - Failed to scan media library.
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select id, path, name, artist, song_count, duration_seconds, cover_art_path, year, genre, play_count, last_played, comment, created, last_scanned, present, folder_id from album where name=?]; HOUR_OF_DAY: 2 -> 3; nested exception is java.sql.SQLException: HOUR_OF_DAY: 2 -> 3
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:106)

This error is caused when the call to MySQL and the timezone set inside MySQL don't agree and is usually the result of a timezone difference involving Daylight Savings Time not being correctly honored.

Being in the PST, I hard-set MySQL to run on America/Los_Angeles time:

mysql> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+---------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+---------------------+---------------------+
| America/Los_Angeles | America/Los_Angeles |
+---------------------+---------------------+
1 row in set (0.01 sec)

I also set my JDBC connector to hard-set the timezone the same way:

export CATALINA_OPTS="$CATALINA_OPTS -Dsubsonic.db='jdbc:mysql://localhost:3306/subsonic?user=****&password=*****!&characterEncoding=UTF-8&serverTimezone=America/Los_Angeles'"

However, it appears that sans digging through your no-longer-opensource code, there's no apparent fix.

Here's the base info:

Distributor ID: Ubuntu
Description: Ubuntu 20.10
Release: 20.10
Codename: groovy

mysql --version
mysql Ver 8.0.22-0ubuntu0.20.10.2 for Linux on x86_64 ((Ubuntu))

java -cp lib/catalina.jar org.apache.catalina.util.ServerInfo
Server version: Apache Tomcat/9.0.40
Server built: Nov 12 2020 15:35:02 UTC
Server number: 9.0.40.0
OS Name: Linux
OS Version: 5.8.0-33-generic
Architecture: amd64
JVM Version: 1.8.0_275-8u275-b01-0ubuntu1~20.10-b01
JVM Vendor: Private Build

Subsonic:
Subsonic Premium (expires December 8, 2021)
6.1.6 (build 0cfa60) – November 10, 2019 (this is the .war I downloaded from the subsonic site. I see it hasn't been updated in quite some time)
Apache Tomcat/9.0.40, java 1.8.0_275, MySQL, Linux (92.1 MB / 600.5 MB)

Thanks.
stukafox
 
Posts: 4
Joined: Mon Jun 22, 2020 7:17 pm

Return to Help

Who is online

Users browsing this forum: No registered users and 16 guests