[JellyHead]

Short version: With large music collections and the setup below, the Party mode play list is not kept at 10 items, it starts at 10 items and gradual reduces to zero items with no new items being added.

Setup
NAS: QNAP TS-210 with MySQL 5.1.36-log
Renderer: Raspberry Pi, OpenElec, XBMC Gotham 13.1, Library using smb to the NAS, advancedsettings.xml configured to point at the MySQL on the NAS
Controller: Yatse on Android tablet

Also occurs with XBMC Gotham 13.1 on Windows 8, when running there, this error is displayed in the GUI
“Party mode aborted. Could not get songs from database.”
Doing a web search for that leads to a number of people having the same problem, but no fixes.

Looking in the xbmc log file gives events of this form
ERROR: SQL: Undefined MySQL error: Code (126)
Query: select * from songview WHERE songview.idSong not in (4845,10282,8524,4890,2351,391,7574,1557,2113,9691) ORDER BY RAND() LIMIT 1

Running the SQL in phpMyAdmin (on the NAS), gives
#126 - Incorrect key file for table '/tmp/#sql_1c88_0.MYI'; try to repair it

Doing a web search for #126 mysql, leads to
temp table failed due to lack of /tmp free space
http://stackoverflow.com/questions/19003...-for-table

running this in phpMyAdmin
SHOW VARIABLES LIKE 'tmpdir';
gives
tmpdir=/tmp

so I SSH onto my NAS
df -h
tmpfs 31.7M /tmp
i.e. /tmp is small and is a ram disk

Looking at the SQL from the log file it looks like it could be optimised, I think the whole view excluding the "not in ()" items is being copied to /tmp, which is failing as it's too much data.
If only the idSong column was considered, then the temporary table will be much smaller and quicker to copy.

My first instinct would be to use a nested query, which I couldn't get to work, because of
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
But I did get these statements to work, also those two statements run quicker than the original as less data needs to be copied to /tmp.

Code:

-- Get the ID only of a song that is not in the current 10 party list
Set @songID=(select idsong from MyMusic46.songview where idsong not in (0,1,2,3,4,5,6,7,8,9) order by rand() limit 1);
-- get the whole row for the song
select * from MyMusic46.songview where idsong in (@songID)

Request:
Please update the get next random track SQL statements for the Party modes, to be similar to the above.



Investigations into workarounds

To get me working while the code is hopefully being updated, I did the following to put the MySQL tmpdir onto the HDD instead of the ramdisk, makes the party mode work at the cost of being a bit slower for any queries that need to use tmpdir.

Code:

mkdir /share/MD0_DATA/mysqltmp
vi /etc/config/my.cnf
tmpdir          = /share/MD0_DATA/mysqltmp
save and quit
/etc/init.d/mysqld.sh restart

different commands will be needed for other MySQL implementations, the gist of it being reconfigure the MySQL tmpdir to be in a location that has enough free space to cope with the temporary tables, and the restart MySQL.


Doing some analysis of the SQL queries using the profiling mode of phpMyAdmin
-- select * will be copying the whole of the view to tmpdir, i.e. slower
select * from MyMusic46.songview WHERE idSong not in (1,2,3,4,5,6,7,8,9,0) ORDER BY RAND() LIMIT 1
tmpdir on HDD: 4.4304s 72% Copying to Tmp table, 27% sorting
tmpdir on RamDisk: fail #126 error, i.e. not enough free space

-- just consider the idsong column
select idsong from MyMusic46.songview where idsong not in (1,4,6,7,8,9) order by rand() limit 1
tmpdir on HDD: 1.0880s 88% Copying to Tmp table, 11% sorting
tmpdir on RamDisk: 0.9988s

-- Then get all of the row, given the idSong found above
select * from MyMusic46.songview where idsong in (@songID)
0.0082s for both as tmpdir is not used

Doing some timings of using party mode music from Yatse
tmpdir location, music starts, add another song to the list
RamDisk, 71s, fail
HDD, 71s, 4s

I haven't investigated why it takes 71 seconds to start playing, could be something about using XBMC on a Raspberry Pi, or possibly more SQL queries that need optimisation.

The 4 seconds to add the next 10'th track to the party play list, matches up to the time that the unoptimised SQL takes to run.

[Milhouse]

Interesting analysis, though not entirely sure why you posted this in the Pi sub-forum considering it's a general/MySQL issue, and could be overlooked here.

This same error 126 has also cropped up recently with the tvshows view - there's another thread around here somewhere.

The 71s delay may also not be Pi related, and again is something that's been discussed before although I don't think there has been any conclusion as to the cause.

It looks like you've got the fail/4s the wrong way round in the third paragraph from the end of your post.

[JellyHead]

Which forum: I didn't see a MySQL one, so used the next best match I could find, I might re-post to another sub-forum if this post lets lost. Which sub-forum would be the best match for this?
Error 126: I did find some of those posts, but as I stated above, none with a resolution or workaround, which is why I posted as I had new information to add.
71s delay: I might do some investigations in to that later, and post on a new thread if I find something interesting.
fail/4s: Thanks for spotting that, I have edited the post.

[Milhouse]

"Kodi General Help and Support", ie. the forum above this one, simply because some developers may ignore the Pi sub-forum entirely if it's not a platform they use/test/develop for, but may still be taking an interest in MySQL.

Maybe a moderator can move this post so it gets more attention.

[Martijn]

Done