[DLingard89]

Hi, sorry if this is posted wrong etc but i'm being plagued by one of my RPIs...

My set us is:

Linkstation NAS > SQL > Network > Living room Pi / Bedroom Pi

Both Pis were 2B using the latest OSMC to connect to the SQLDB, both working fine.

One pi then started playing up, the living room one would not start playing a TV show episode without stalling for 5 minutes (actually 5 minutes) or spinning the working symbol then doing nothing. Naturally I upgraded it to a PI3, and still have the same problem. I then tried different variations of cables, wireless etc to no avail...

i have pastebinned the log here http://pastebin.com/EUyHHFWp

thanks in advance guys

[popcornmix]

Debug enabled log please. Enable debug. Reboot. Play one file that shows the issue then upload the log.

Code:

21:40:08  34.518719 T:1646261232 WARNING: Unable to query optimizer_switch: 'MyMusic56' (1)
21:40:08  34.566227 T:1733329904 WARNING: Unable to query optimizer_switch: 'MyVideos99' (1)
21:40:08  34.670753 T:1646261232 WARNING: Unable to query optimizer_switch: 'MyMusic56' (1)
21:40:09  34.841820 T:1724679152 WARNING: Unable to query optimizer_switch: 'MyVideos99' (1)

Looks like some MySQL issue. Perhaps the debug log will have more clues.

[Milhouse]

As far as the SQL issues are concerned...

Code:

21:40:09  34.841820 T:1724679152 WARNING: Unable to query optimizer_switch: 'MyVideos99' (1)

Looks like some MySQL issue. Perhaps the debug log will have more clues.

Those are just warnings, and it means the version of MySQL server in use doesn't support the "optimizer_switch" system variable. Since this was back ported to v5.1, the version of MySQL being used by the Linkstation must be quite old. These can be ignored (perhaps the log level shouldn't be WARNING, but INFO - one way to stop it would be to upgrade to a version of MySQL released in the last 10 years...)

These are errors however:

Code:

21:40:21  47.179508 T:1646261232   ERROR: SQL: [MyVideos99] Undefined MySQL error: Code (1030)
                                            Query: select * from episode_view  ORDER BY dateAdded desc, idEpisode desc LIMIT 10
21:40:21  47.180344 T:1646261232   ERROR: GetEpisodesByWhere failed

There can be many reasons for error 1030, one of them being the file system being full, or a permission issue in relation to the MySQL files (wrong file ownership), but could also be related to the version of MySQL.

A debug enabled log would reveal the MySQL server version being used (edit: this will only be reported by Kodi 17+).

[DLingard89]

Thanks for your help guys - I have tried a few more things:

Increasing the tmp_table_size & max_heap_table_size to 2G
Running the query select * from episode_view ORDER BY dateAdded desc, idEpisode desc LIMIT 10 in mysql workbench

Neither work, (i get error code 126 regarding the temp view)

SO i tried running
select * from episode_view LIMIT 10

which returns results

Now if i add any kind of order command into that string it fails! Im assuming this is the problem but god knows what to do about it...I have also tried to repair the episodes table, with no luck.

Will post debug log next but need to make it smaller :S


also if i run select * from movie_view ORDER BY dateAdded desc LIMIT 10

it returns result no problem - leading me to assume wither the episodes table itself is so huge it isnt working, or its corrupt somehow?

[Milhouse]

What version of MySQL are you using (this won't be reported in your debug log as you're using Kodi 16.x).

[DLingard89]

5.0.70 according to workbench, I dont think I can upgrade though as its built into a buffalo nas

[Milhouse]

Regarding your errors, MySQL uses /tmp as a scratchpad when having to sort results etc., and (most likely) you're running out of space ie. MySQL is filling the whole of /tmp with on-disk temporary tables.

See: http://stackoverflow.com/questions/20110...-for-table

While you can in theory avoid creating on-disk temporary tables by increasing the size of max_heap_table_size (as you have done), the engine used for in-memory temporary tables does not support BLOB/TEXT columns and the Kodi tables and views use TEXT columns a lot, so you will always be creating on-disk temporary tables regardless of your max_heap_table_size setting.

Your solutions are:

1. Increase the size of your /tmp partition (you might be able to do this by increasing the size in /etc/fstab, but ask a Buffalo expert so you don't make your system unbootable)
   
2. Reconfigure MySQL to use an alternate tmpdir filesystem that has more available space. MySQL may require multiple tmp tables to process certain queries (or concurrent queries) so your tmpdir partition should be at least a 2-3 multiples of your tmp_table_size. Change location by adding "tmpdir=/mysql_tmp" (example) to my.cnf. A non-tmpfs file system may result in reduced performance
   
3. Try MariaDB which can cache more data without creating on-disk temporary tables (apparently)
   

5.0.70 according to workbench, I dont think I can upgrade though as its built into a buffalo nas

Of the options above, #1 should be viable if you only need a little more space for /tmp - the default /tmp is probably quite tiny on your NAS (a quick Google suggests 10MB but I've no idea what model you have), but the more RAM you allocate to /tmp is less RAM for the OS. Try allocating 32MB and see if the queries then succeed. With option #2 performance may not be too bad - I have tmpdir on a ZFS file system based on spinning HDD running on an HP N36L and performance is fine - but having working queries that are a little slow is always preferable to completely broken queries.

Alternatively, if your options are really limited (and/or your NAS not very powerful), you might want to consider using an alternative and more flexible device for your backend database server. For example, a dedicated/headless Raspberry Pi 3 running Raspbian and MySQL/MariaDB version of your choice, configured however you see fit, should do the job as long as your library isn't too crazy big.

[DLingard89]

Thanks for your help once again, I have still been playing about but Im really at the limit of my skillset here:

I have enabled access to the linkstation, and this is the result of the df-h on the drive

root@LS-QVL836:~# df -h
Filesystem Size Used Available Use% Mounted on
/dev/md1 4.7G 904.7M 3.6G 20% /
udev 10.0M 188.0k 9.8M 2% /dev
/dev/ram1 15.0M 696.0k 14.3M 5% /mnt/ram
/dev/md0 969.2M 32.4M 936.8M 3% /boot
/dev/md2 10.9T 5.7T 5.1T 53% /mnt/array1

I was thinking that the allocation on udev and ram1 could do with being increased?

[Milhouse]

It's a little hard to say as you don't have a traditional /tmp mount point, so presumably your Buffalo is configured in such a way that /tmp exists as a symbolic link to another mount point/file system, presumably /mnt/ram. You'll need to confirm this for yourself by seeing what /tmp is pointing to ("ls -la /" - you might then see something like "/tmp -> /mnt/ram"), but increasing the size of /dev/ram1 could be your solution. However I don't think you have a lot of RAM (256MB?) available in your device (LS-QVL) so you may not be able to increase it by very much, maybe to 30MB - the less RAM the OS has, the worse it may perform as a NAS.

Edit: I don't think you need to change anything in relation to udev, just increase the size of /dev/ram1

[DLingard89]

Milhouse,

with your help I have made some progress!!

I tried a few more things: Dropped all old databases from earlier kodi versions etc, cleaned up redundant files
this didnt work

ran the command:

mount -t /dev/ram1 -o remount,size=30M /dev/ram1 /mnt/ram

and now in mysql workbench the query is successful.

Just wondering if this needs to be added to the boot process of the nas in order to redo it each time?

thanks again

[Milhouse]

Yes, you'll want to change this so that it is the default - I'd see if you can change the size of /dev/ram1 by editing /etc/fstab but if that doesn't work you'll need to ask someone more familiar with the OS on this NAS.

[DLingard89]

I know this is a little dated now but I have finally figured out what was wrong and fixed it. After looking through the logs it seemed that, a while ago I changed server, which in turn changed the IP of all of the files. While I did do a find and replace for all the files to the new IP it seems I had neglected to change the art pointer. So I did a full database find and replace from old to new IP and its now working perfectly...

SQL databases will be the death of me...