Scan fail due to track number being -1

[devillemereuil]

Expected behavior

Scan for audio file should be running OK.

Actual behavior

Scan from the web interface stalls at a particular entry. When run using CLI, it stops with the following error (anonymised):

 [Doctrine\DBAL\Exception\DriverException]                                                                                                                                            
  An exception occurred while executing 'INSERT INTO `oc_audioplayer_tracks` (`user_id`,`title`,`number`,`artist_id`,`album_id`,`length`,`file_id`,`bitrate`,`mimetype`,`genre_id`,`y  
  ear`,`folder_id`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)' with params ["user", "title", -1, 162, 46, "4:25", 1344, 128000, "audio\/mpeg", 20, 0, 1273]:                              
  SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'number' at row 1 

Of course, the actual track number is not -1, but is simply missing.

Steps to reproduce the behavior

1. Difficult to reproduce, but only triggered by tracks lacking a track number in the metadata (which I can't share here for obvious reason...). No other player had this issue (not even the old Owncloud one).
2. The track number is most likely the culprit, because setting it to a given value make the scan run OK for this particular track (could also be that setting track number up overwrite whatever is compromised the meta-data, but no other player has an issue with the tracks...).

Server configuration

Operating system:
Ubuntu 16.04

Database:
MySQL Ver 14.14 Distrib 5.7.17

PHP version:
PHP 7.0

ownCloud/Nextcloud version: (see /status.php)
Nextcloud 11.0

Updated from an older ownCloud/Nextcloud or fresh install:
Fresh install

Audio Player version: (see /settings/apps)
1.4.1

Where did you install Audio Player from:
Nextcloud interface

Are you using external storage, if yes which one: local/smb/sftp/...
No

Are you using encryption: yes/no
No

Logs

ownCloud/Nextcloud log

ownCloud/Nextcloud log (`/data/[owncloud|nextcloud].log`).

Nothing related to the issue (only one line due to a mistake in the admin setting at install).

[Rello]

Hello,
the behavior is expected because -1 is not a valid track number.
It can not be stored in an unsigned filed
https://github.com/Rello/audioplayer/blob/master/appinfo/database.xml#L233-L236

but you are writing that it is not like that in the ID3-Tags.
Can you please let me know via an external ID3-Viewer what the actual content of the field track number (TRCK) is?

thank you...

[sam50]

Hi, I'm also having this problem. It was present on 1.4.0 and php5.5 now I checked on 1.4.1 with php5.6
here is mp3 file:

$mp3info ./other/baba_yetu.mp3
File: ./other/baba_yetu.mp3
Title:   Baba Yetu                      Track: 
Artist:  VA
Album:   Unknown                        Year:  -1
Comment:                                Genre:  [255]

So there is no Track number but it looks like it still tries to insert -1

the log file says:

{"reqId":"zlIZBdvOlWqfPTzbUbrA","remoteAddr":"172.17.0.1","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `oc_audioplayer_tracks` (`user_id`,`title`,`number`,`artist_id`,`album_id`,`length`,`file_id`,`bitrate`,`mimetype`,`genre_id`,`year`,`folder_id`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)' with params [\\\"sam\\\", \\\"Baba Yetu\\\", -1, 43, 22, \\\"4:00\\\", 140, 192019, \\\"audio\\\\\\\/mpeg\\\", 5, 0, 17]:\\n\\nSQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'number' at row 1\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(116): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(175): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO `oc...', Array)\\n#2 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/legacy\\\/db\\\/statementwrapper.php(74): Doctrine\\\\DBAL\\\\Statement->execute(Array)\\n#3 \\\/var\\\/www\\\/nextcloud\\\/apps\\\/audioplayer\\\/controller\\\/scannercontroller.php(789): OC_DB_StatementWrapper->execute(Array)\\n#4 \\\/var\\\/www\\\/nextcloud\\\/apps\\\/audioplayer\\\/controller\\\/scannercontroller.php(650): OCA\\\\audioplayer\\\\Controller\\\\ScannerController->writeTrackToDB(Array)\\n#5 [internal function]: OCA\\\\audioplayer\\\\Controller\\\\ScannerController->scanForAudios(NULL, NULL, NULL)\\n#6 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(160): call_user_func_array(Array, Array)\\n#7 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(90): OC\\\\AppFramework\\\\Http\\\\Dispatcher->executeController(Object(OCA\\\\audioplayer\\\\Controller\\\\ScannerController), 'scanForAudios')\\n#8 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/AppFramework\\\/App.php(111): OC\\\\AppFramework\\\\Http\\\\Dispatcher->dispatch(Object(OCA\\\\audioplayer\\\\Controller\\\\ScannerController), 'scanForAudios')\\n#9 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/AppFramework\\\/Routing\\\/RouteActionHandler.php(47): OC\\\\AppFramework\\\\App::main('ScannerControll...', 'scanForAudios', Object(OC\\\\AppFramework\\\\DependencyInjection\\\\DIContainer), Array)\\n#10 [internal function]: OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler->__invoke(Array)\\n#11 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/private\\\/Route\\\/Router.php(293): call_user_func(Object(OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler), Array)\\n#12 \\\/var\\\/www\\\/nextcloud\\\/lib\\\/base.php(998): OC\\\\Route\\\\Router->match('\\\/apps\\\/audioplay...')\\n#13 \\\/var\\\/www\\\/nextcloud\\\/index.php(48): OC::handleRequest()\\n#14 {main}\",\"File\":\"\\\/var\\\/www\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":116}","level":3,"time":"2017-02-09T19:19:47+00:00","method":"POST","url":"\/index.php\/apps\/audioplayer\/scanforaudiofiles","user":"sam","version":"9.1.3.2"}

[sam50]

Also as a suggestion, maybe we could have a try..catch around DB insert and just move on to next file if current fails for any reason?

[devillemereuil]

OK, so here's what I found. @sam50 got me the idea to use mp3info to compare my problematic file with his file and here's what I got (again I removed unrelevant information):

File: Title.mp3
Title:                                  Track: 
Artist:  Artist
Album:   Album                          Year:  -1
Comment:                                Genre: Other [12]

Could it be that it's not "Track Number" but "Year" that is being set to -1? I checked with other files with no Year information and mp3info correctly leaves the field empty, so there's definitely something going on here.

Weird that other players never catched it... I guess they silently went over the file and ignored it or something.

EDIT: Still, something is weird if the scan is switching "Track Number" and "Year", isn't it?
EDIT2: I just checked, just saving any change on the tags using my local player (Clementine) indeed overwrite the Year field and leaves it empty, so it might be that by changing the track numbers I solved the issue, just not as I thought I did.

[Rello]

I will try to debug with a local file on my side.
both cases indicate a wrong entry in the year tag - but this still does not explain why this goes into the track then.

thank you for your valuable feedback

[devillemereuil]

It could also be the file is somehow corrupted and mp3info shows this a the year being -1. Clementine, for example, shows the Year as being "0" (i.e. empty) in its GUI.

[Rello]

hello,
I could not reproduce the error. does not make sense in my opinion.
something at your ID3 tags seems to be broken.

Can you open the files with a text-editor and copy-past the first lines with the ID3 information in plain text here? perhaps this gives a clue.

[devillemereuil]

Hi,

I was too quick in drawing my conclusions actually. Removing the -1 in "Year" doesn't solve the problem.

Here are the three first lines of the file:

ID3������vTCON�������OtherPRIV������PeakValue�����PRIV������AverageLevel�= ��PRIV���'��WM/MediaClassPrimaryID��}`�#��K��H�*(D�PRIV���)��WM/MediaClassSecondaryID�����������������TRCK�������-1TALB�������Mambo Number 5TPE1���	���Lou BegaTPOS�������TBPM�������TCOM�������TIT1�������TOPE�������USLT�������XXX�TPE2�������TCMP�������0

I can actually share it with you if you have a private canal where we can do that.

[Rello]

Hello,
the file from @devillemereuil is a wrong ID3 file. it has a -1 in the track number

this breaks the scanner because -1 is actually a valid result of the type casting (int) but not for the unsigned database filed.
I will adjust the code that negative values will be ignored and not break the scanner

[sam50]

Yeah, my file also has -1, just opened the file itself. Waiting for fix, thanks.

[Rello]

Hello,
can you tell me if you are using MySQL in strict-mode?

https://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html

[devillemereuil]

I'm using Ubuntu's default. How can we test if MySQL is in strict-mode?

[sam50]

I have default mysql 5.7 so global mode is:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Not sure if NC sets session mode

[Rello]

scanner was adjusted

• values for year & tracknumber are normalized to a positive integer
• strings for e.g. titles, artist, genre are truncated to DB size

[devillemereuil]

Good to hear! Thank you for this! ;)