Solved Moved DB to MySQL sometimes very slow
#1
I have 2 kodi boxes ( 1 upstairs 1 down)  and recently we wanted to share the watched status between both boxes.
Setup MySQL 8 on the same server (Windows Server 2019) thats hosting all the media.
Every thing works great apart from every now and again (~ 1 out of 3) the down stairs box takes an age before you can view the media.

Its just an annoyance more than any thing as in the end it does load.
I'd rather it not though Smile

both boxes are similar spec intel 8100, 8GB ram, win 10, kodi 19.3 both worked fine no issues before MySQL shared Db

Debug log of it working normally  https://paste.kodi.tv/foxizifago.kodi

Degug log with delay of ~60 seconds https://paste.kodi.tv/turepodoqe.kodi

Both boxes do an automatic scan on startup, I usually wait for this to pop up as this is when I can view the media. Which is normally ~5 seconds after kodi starts yet, sometimes hangs for ~60 seconds or so.

Any pointers would be much appreciated.
Reply
#2
May be related or not, you have database corruption, on both local sqlite and mysql.

There are lots of lines like this "DatabaseUtils::GetSortFieldList: unknown field 36" from the videodatabase (mysql)

On the local databases, there are many similar to that:
SQL: [ViewModes6.db] SQLite error SQLITE_CORRUPT (database disk image is malformed)
Query: select * from view where window = 10500 and path='?/'

You can try to salvage your current video database with an export library, delete the video databases (all versions), start Kodi and import library.

The ViewModes6.db should be recreated after you delete it while Kodi is not running.

Backups are important when messing with the data in case things don't go as planned.
Always read the Kodi online-manual, the FAQ and search the forum before posting.
Do not e-mail Kodi Team members directly asking for support. Read/follow the forum rules (wiki).
For troubleshooting and bug reporting please make sure you read this first.
Reply
#3
thank you for the reply.

I have just tried...... quit kodi and deleted ViewModes6.db. Sure enough on restart it was recreated and all the SQL errors have gone.

We will see how it goes.

But in the meantime i'm looking into using mysqlcheck.exe to repair the database.

What I noticed in the log
Quote:2024-04-30 13:15:20.005 T:7000    DEBUG <general>: CGUIMediaWindow::GetDirectory (smb://192.168.0.10/~TV~/)
2024-04-30 13:15:20.005 T:7000    DEBUG <general>:   ParentPath = []
2024-04-30 13:15:20.479 T:1996    DEBUG <CWebserver[8080]>: request received for /jsonrpc
2024-04-30 13:15:30.503 T:1888    DEBUG <CWebserver[8080]>: Skipped 13 duplicate messages..
2024-04-30 13:15:30.503 T:1888    DEBUG <CWebserver[8080]>: request received for /jsonrpc
2024-04-30 13:15:47.898 T:2764    DEBUG <general>: Skipped 1 duplicate messages..
2024-04-30 13:15:47.898 T:2764    DEBUG <general>: Thread JobWorker 2764 terminating (autodelete)
2024-04-30 13:16:24.690 T:7000    DEBUG <general>: Saving fileitems [smb://192.168.0.10/~TV~/]
What is going on here as there is a big jump in time  (64 seconds).
And the last line saving fileitems does that mean its trying to write back to smb://192.168.0.10/~TV~/ as the permissions are not set for it to be allowed. or perhaps im barking up the wrong tree Smile
Reply
#4
I looked a bit more into it and the "unknown field 36" error is not data corruption, but an unsupported use of "SortTitle" as list sorting for the music library. It's valid only for movies and episodes.
Maybe you have it in a playlist, custom node, somehow set it on a view.

Can't tell from here which gaps may or may not be related to your issue just by looking at the log, you have to tell us the timestamps that correspond to problems.
Always read the Kodi online-manual, the FAQ and search the forum before posting.
Do not e-mail Kodi Team members directly asking for support. Read/follow the forum rules (wiki).
For troubleshooting and bug reporting please make sure you read this first.
Reply
#5
MySQL is not used for music db only video, the music was left local.
But when I noticed the errors in the log regarding music,  the music source was removed, database cleaned and music.db deleted and recreated a week or so ago
Just checked and there are no playlists of any description

Ive just tried unchecking " scan on start up " and so far with a handfull of restarts seems OK, maybe the update service hanging for whatever reason. But time will tell.
Just have to rely on the other box to do library updates, or I may install kodi on the server purely for updating the database, assuming kodi can be run without a screen. ( its only accessed normally by VNC)

As for time stamps I did sync my clock with windows time and screens become populated at the same time as the "scanning for update bar" appears around the 2024-04-30 13:16:25 mark

thanks again for taking the time to look
Reply
#6
I don't know what's happening. Stating the obvious but there would be a delay if the server needs to spin up the drives.
You're also on a old version, it's possible that changes in the past few years took care of the issue.
Always read the Kodi online-manual, the FAQ and search the forum before posting.
Do not e-mail Kodi Team members directly asking for support. Read/follow the forum rules (wiki).
For troubleshooting and bug reporting please make sure you read this first.
Reply
#7
Thanks for the thoughts.
The Hard disks never spin down, beside the upstairs machine doesnt have this issue.
But... yeah at some point I will need to update to the latest Kodi.

I'm begining to think this has nothing to do with MySQL db and more to do with how the machine wakes from sleep.
To start with, the way my machine turns on / off is....
When suspend in the menu or sleep button on the remote is pressed, a background progam catches the request and runs a script to fully close Kodi then sleeps the machine.
To wake the machine, the button on the remote or "wake" from the Kore app (WOL) wakes the machine, runs a script and starts kodi again.

Scenarios the "hang" does / doesnt happen

Using Exit in the menu and starting kodi by double clicking the icon, hang never happens

Wake from sleep by.....
pressing button on remote. Hang never happens

Selecting "wake up" from the Kore app (WOL) is when the hang at startup happens.... but not every time.
Theres a relation (I think) to whether the other machine has been on.

I have had enough of this for now and leave it alone for a while, see if any more common occurances happen.

Thanks again
Reply
#8
Just an update......


The issue is caused by the network adapter more often than not hanging when resuming from sleep or hibernate.
You can get around this by unchecking "allow the computer to turn off the device to save power" in the power managment tab of your network card in device manager. Then everything works fine, ie. Kodi does not hang at "Preparing 0%"  for ~60 seconds when resuming.

But......
If you want to use the Kore app to control and wake your device you have to re-enable "allow the computer to turn off the device to save power" in order to enable "Allow the device to wake the computer"
Which will ultimately cause Kodi to hang again at "Preparing 0%" when resuming from sleep.

This is possibly a driver issuse causing the network to hang after resume.
I tried many different driver revisions without success, and after plenty of searches, found this issue has been around for many years.

I found after lots of testing that if you disable then re-enable the network adapter (in device manager) after resuming from sleep the issue goes away.

So...... Here how I fixed my machines.
Windoze 10 (21H2) has a utility called pnputil.exe which can easily restart a device.

Make a script containing.

Code:
@echo OFF
pnputil /restart-device "****device instance path****"
exit

You can get your "device instance path" from device manager..... double click your network card, goto the details tab and the property drop down box, find "device instance path"

Use task schedular, hibernate trigger, win7suspenandresume etc etc. to start the script after resume from sleep .
Must be run with administrative privileges.

Job done no more hanging after resume.

Hope this helps someone.
Reply
#9
Solved then.
Always read the Kodi online-manual, the FAQ and search the forum before posting.
Do not e-mail Kodi Team members directly asking for support. Read/follow the forum rules (wiki).
For troubleshooting and bug reporting please make sure you read this first.
Reply
#10
Yep.
Reply
#11
Thread marked solved.
Reply

Logout Mark Read Team Forum Stats Members Help
Moved DB to MySQL sometimes very slow0