DB Design issue - (MySQL) "Numeric value out of range"

[abalg]

While trying to scan my music collection I've hit an error - in the UI it is just "stuck" and does not do anything anmore of course

sudo -u web1 php8.0 occ music:scan --all

Start scan for xxxxx
Found 3646 music files to scan
Scanning /xxxxx/files/Musik/......................flac

In ExceptionConverter.php line 119:

An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'length' at row 1

In Exception.php line 30:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'length' at row 1

In Statement.php line 101:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'length' at row 1

I was able to fix it myself - the change below fixed the issue:

You should change the MySQL table layout - I've hit an error where It stopped scanning as the field "length" exceeded its maximum allowd value for an INT - also to have the artist_id and album_id as signed INT values did not seem to make much sense to me so I've changed them as well - (unsigned gives double the range to be used - and the INT(10) or INT(11) you've been using has no influence on the size of an INT field - it is just about how many figures to display - but it does not allow to store bigger or smaller INT values.

I did an:

ALTER TABLE nextcloud.oc_music_tracks
CHANGE COLUMN artist_id artist_id INT(11) UNSIGNED NULL DEFAULT NULL ,
CHANGE COLUMN album_id album_id INT(11) UNSIGNED NULL DEFAULT NULL ,
CHANGE COLUMN length length VARCHAR(32) NULL DEFAULT NULL ;

Fixed the issue for me

[paulijar]

Thanks for the report and sorry for the late reply.

I believe that it's something else than the DB schema which is the root cause here. The length field is supposed to contain the song duration in seconds. The size of the MySQL integer type should be 32 bits so as unsigned that would have the maximum value of 4294967295. That many seconds is equal to more than 136 years and I don't believe that you have that long songs.

So the real question is, what kind of data gets extracted from the song where this problem happens. You could maybe find this from nextcloud.log when the problem has happened. Or you could share the problematic song with me and I can take a look. You can find my email address from my GitHub profile.

You are right that it doesn't make sense for the fields artist_id and album_id to be signed, especially as they refer to the id field in the tables oc_music_artists and oc_music_albums and those are unsigned. But it doesn't really matter since even the signed 32-bit integer has the maximum value of 2147483647. For the album ID to reach that limit, you would need to scan that many unique albums. To put that into context, the MusicBrainz database aiming to catalogue all the recordings in the world, currently contains some 3.7 million albums. So you could have 500 users each owning the entire catalogue of MusicBrainz and still not hit the limit. And if each of those ~2 billion albums contained only 1 MB worth of songs, then you would need 2 petabytes of disk space to store all that. Not to mention that both the Music app and the Nextcloud server would probably break down under the load for other reasons way before reaching that kind of numbers of files.

[paulijar]

@abalg So any chance you could share the file causing the problem with me, or check yourself, what kind of length values get stored to your DB? My best guess is that you now have a length which is something like -1 which doesn't fit the unsigned integer, but it would be nice to get some kind of confirmation before doing any code changes.

[paulijar]

The newly released Music v1.9.1 now limits the value of track length and a few other numeric fields to the legal range before storing them to the DB. This should fix the reported problem.

Also, unsigned integer type is now used consistently on all DB foreign keys.