0
Fixed

SQL error 1064 affecting music metadata

roleohibachi 5 months ago updated by Vlad R 4 days ago 3

On a fresh install of FileRun on an Ubuntu 20.04 LXC container, I am unable to view mp3 files "by artist", etc, even when I manually command the indexing of metadata for the music folder.

system/data/temp/mysql.error.log shows one entry each time the indexer runs, each of which is very similar to:

/?module=music (POST)
2020-06-23T15:33:44+00:00 - SELECT v.val,MAX(v.id) as id,COUNT(v.id) as count FROM df_modules_metadata_files AS f LEFT JOIN df_paths AS p ON f.pid = p.id LEFT JOIN df_modules_metadata_values AS v ON v.file_id = f.id LEFT JOIN df_modules_metadata_fields AS fields ON v.field_id = fields.id WHERE ((v.field_id = 20) AND (p.path LIKE '/path/to/home/%') AND (p.path NOT LIKE '/path/to/home/.filerun.trash/%')) GROUP BY `val` ASC LIMIT 0, 500
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'ASC LIMIT 0, 500' at line 1


/var/log/apache2/error.log has one entry per failed file:

[Tue Jun 23 12:06:28.051501 2020] [php7:warn] [pid 563] [client 192.168.0.147:47728] PHP Warning:  preg_match(): Compilation failed: escape sequence is invalid in character class at offset 16 in /var/www/html/system/classes/getid3/module.tag.id3v1.php on line 71, referer: <a href="http://example.com/?module=metadata&section=index&path=%2FROOT%2FHOME%2Ftrial%20music%2FTest" class="redactor-autoparser-object">http://example.com/?module=metadata§ion=index&path=%2FROOT%2FHOME%2Ftrial%20music%2FTest</a>


The offending line in module.tag.id3v1.php:

if (preg_match('#^[\\x00-\\x40\\xA8\\B8\\x80-\\xFF]+$#', $value)) ...

On the PHP end, my config (/etc/php/7.4/apache2/conf.d/filerun.ini) contains the recommended values, and also:

display_errors          = On
log_errors              = On
error_log = "/tmp/filerun_php_error.log"

PHP errors are generated to /tmp/systemd-private-*-apache2.service-*/tmp/filerun_php_error.log that match verbatim the errors in /var/log/apache2/error.log.


Some other features appear not to work, such as the image editing plugin; this may or may not be related. No errors appear related to those features, so I'm treating it as a separate issue for now.


Can anyone advise regarding further troubleshooting? I'm posting this in 'bugs', but I think it's 50/50 whether it's my own misconfiguration causing the issue. 

+1
Started

Please try to replace "system/classes/vendor/FileRun/Media/Music.php" with this file and let me know if the problem persists.

Your updated Music.php fixed the issue. Thank you Vlad. Hopefully this will make it easier for others in the future!