Bugzilla – Bug 4474
Error executing 'INSERT INTO Playlists ... foreign key constraint fails
Last modified: 2011-11-06 23:23:23 UTC
Trying to browse Music library, some branches cause the following trap: [17:05:17.0818] Slim::Schema::throw_exception (206) Error: Carp::Clan::__ANON__(): Error executing 'INSERT INTO playlist _track (playlist, position, track) VALUES (?, ?, ?)': DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`slimserver/playlist_track`, CONSTRAINT `playlist_track_ibfk_1` FOREIGN KEY (`track`) REF ERENCES `tracks` (`id`) ON DELETE CASCADE) at /opt/SlimServer_v2006-11-05/CPAN/DBIx/Class/Storage/DBI.pm line 771. [17:05:17.0829] Slim::Schema::throw_exception (206) Backtrace: frame 0: Slim::Utils::Log::logBacktrace (/opt/SlimServer_v2006-11-05/Slim/Schema.pm line 206) frame 1: Slim::Schema::throw_exception (/opt/SlimServer_v2006-11-05/CPAN/DBIx/Class/Schema.pm line 606) frame 2: DBIx::Class::Schema::txn_do (/opt/SlimServer_v2006-11-05/Slim/Schema/Playlist.pm line 46) frame 3: (eval) (/opt/SlimServer_v2006-11-05/Slim/Schema/Playlist.pm line 39) frame 4: Slim::Schema::Playlist::setTracks (/opt/SlimServer_v2006-11-05/Slim/Player/Playlist.pm line 788) frame 5: Slim::Player::Playlist::modifyPlaylistCallback (/opt/SlimServer_v2006-11-05/Slim/Control/Request.pm line 164 3) frame 6: Slim::Control::Request::notify (/opt/SlimServer_v2006-11-05/Slim/Control/Request.pm line 737) frame 7: Slim::Control::Request::checkNotifications (/opt/SlimServer/slimserver.pl line 424) frame 8: main::idle (/opt/SlimServer/slimserver.pl line 398) frame 9: main::main (/opt/SlimServer/slimserver.pl line 921) I have no idea though whats wrong with the libraries. It was created by iTunes and stored on a linux server. The problem is reproducible with with at least one specific branche (country). I could provide additional information as required. Environment: OS: Debian GNU/Linux 3.1 Perl: Summary of my perl5 (revision 5 version 8 subversion 4) configuration: Platform: osname=linux, osvers=2.6.15.6, archname=i386-linux-thread-multi uname='linux ernie 2.6.15.6 #1 thu mar 16 13:11:55 est 2006 i686 gnulinux ' config_args='-Dusethreads -Duselargefiles -Dccflags=-DDEBIAN -Dcccdlflags=-fPIC -Darchname=i386-linux -Dprefix=/usr -Dprivlib=/usr/share/perl/5.8 -Darchlib=/usr/lib/perl/5.8 -Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5 -Dvendorarch=/usr/lib/perl5 -Dsiteprefix=/usr/local -Dsitelib=/usr/local/share/perl/5.8.4 -Dsitearch=/usr/local/lib/perl/5.8.4 -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Dman1ext=1 -Dman3ext=3perl -Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Uusesfio -Uusenm -Duseshrplib -Dlibperl=libperl.so.5.8.4 -Dd_dosuid -des' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64', optimize='-O2', cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -I/usr/local/include' ccversion='', gccversion='3.3.5 (Debian 1:3.3.5-13)', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=4, prototype=define Linker and Libraries: ld='cc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt perllibs=-ldl -lm -lpthread -lc -lcrypt libc=/lib/libc-2.3.2.so, so=so, useshrplib=true, libperl=libperl.so.5.8.4 gnulibc_version='2.3.2' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E' cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib' Slimserver: SlimServer_v2006-11-05
is this JUST with 7.0a1 or with 6.5.1 as well?
Are you using a pre-existing installation of Mysql? What is the path to your music library?
*** Bug 4388 has been marked as a duplicate of this bug. ***
Please reopen if there is an update.
reopen? was this intended to be closed?
Yep. Somehow only some of the changes I tried to make got committed to each bug. I will patch these up.
*** Bug 6082 has been marked as a duplicate of this bug. ***
Created attachment 2374 [details] log file I suggest that this bug be reopened. I'm still seeing it in the most recent revs of SqueezeCenter 7. Attached is a log snippet showing two instances of the error. Note that each instance consists of two database errors being logged.
To the developers: Note the line in the log file containing: Error executing 'INSERT INTO playlist_track (playlist, position, track) VALUES (?, ?, ?)' Is there any way that the values being passed to this prepared SQL statement can be logged when a database error occurs? The only way anyone is going to figure out why the statement is failing is by knowing these values.
reopening and clearing target to flag it for a review.
Created attachment 2375 [details] log I've figured out what is happening. I turned on SQL debugging to be able to see the parameters being passed into the query. What is happening is that SC is trying to add a track into the playlist that no longer exists in the database. Apparently the track is being deleted and then added back into the database, no doubt having something to do with searching and updating the tracks table after looking for artwork for the track. The delete/add happens not when the track is added to the playlist, but when it is played and the artwork is displayed in the status pane. It's also happening in the Fishbone skin. The SQL constraint error looks like it may occur after a track has been played, then you try to add that same track into the playlist from the browse window. The track id in the browse window now references a record no longer in the database. See the log file. I've included the section where track number 2340 is deleted from the database and added back in, then the error when it tries to add 2340 to the playlist.
A couple of other things that I can see. Looking at the database, the track records that are added back into the database after being deleted don't always have a populated cover field. Some remain NULL, which means that the new record would be identical to the one deleted. And I can only sometimes trigger the constraint error by trying to play or add one of these non-existant records to the playlist. Most of the time the track just doesn't get added to the playlist, but no error occurs.
Andy: any idea what's going on here?
QA to try to come up with repro steps.
I think I see the same thing. However, I'm setting a milestone of 'future' for this bug since it doesn't seem to cause much disruption for the average user, and we're coming up on the 7.0 release.
Unassigned bugs cannot have a priority.