Kodi Community Forum
Blasting database, restore watched count - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Blasting database, restore watched count (/showthread.php?tid=366116)

Blasting database, restore watched count - elmuziko - 2021-12-22

So I've been having a colossal amount of KODI problems moving from Synology to unRAID. On a windows machine, everything is fine having altered the MySQL database with new paths, along with the advancedsettings.xml and sources.xml file. Sadly, the same advancedsettings.xml and sources.xml cause KODI to immediately exit on Android.

I thought I had it fixed by giving the MySQL KODI user god mode over all databases. (https://forum.kodi.tv/showthread.php?tid=366070). Sadly it would appear that turning my TV off and on again yields the same errors (pastebin in linked thread).

I recognise I'm fighting a losing battle, so am prepared to nuke the database.
From phpmyadmin I've exported 2680 entries on the files table WHERE playCount >=1 
SELECT * FROM `files` where playCount >= 1;

I'm not great at SQL. The path I believe I have to walk is to let the database re-build, then do another export of files, open both SQL files up in EXCEL and do a VLOOKUP for strFileName and update the playcount accordingly. Simply blasting the newly-built database with my export is just gonna naff up what with the idFile being different.

Is there an easier way to do this? Like, for instance, fixing the Android problem from the off. Or just an SQL command without the need for EXCEL?

Thank you

RE: Blasting database, restore watched count - Klojum - 2021-12-22

The best first step would have been to do a video library export _before_ hacking/changing paths in the database, as well as a full sqldump of your MySQL video database. You also probably found out that Windows and Android (Linux) paths work differently. You haven't shared the xml files you mentioned, but I'm guessing that paths are the problem.

If you are "ready to nuke the database", then exporting data is not a helpful thing. Once you start rescraping your video collection, differences in id values compared to the old database can/will(?) happen. Resulting in a basically corrupted/useless export of your 2680 >1 watched entries.

Your pathsub for the thumbnails isn't working because I think there is a missing slash char at the end of the from entry:
xml:
		<substitute>

			<from>special://masterprofile/Thumbnails/</from>

			<to>smb://KODITongue[email protected]/KODI/</to>

		</substitute>

Regarding the MySQL database: is it on the Synology or on the Windows machine?
And is it still intact or is it already hacked up with the new paths? No backups to restart the renaming process..?

RE: Blasting database, restore watched count - elmuziko - 2021-12-22

Thank you for replying. I do appreciate it it. 

I do have a backup database, however it's an export from MySQL as apposed to a KODI export.
You're suggesting drop existing DB, import old one with bad file links, use KODI to export... then? Edit the XML rather than the MySQL DB?

That was my fear. Foreign Keys won't match up. I could hack my way through it with EXCEL but it could be even more tedious thank just marking movies complete again. Although the purpose of this is to keep the watched list on movies since my DB is countless years in the making.

I've added the back slashes to substitute, thank you.
The SQL DB is on unRAID. It was Maria 10 on Synology, now 10.1 on unRAID.

This is my sources, advancedsettings.xml is in the pastebin. I dropped the music DB since I don't use it, that surely isn't the issue, no?
xml:
<sources>

    <programs>

        <default pathversion="1"></default>

    </programs>

    <video>

        <default pathversion="1"></default>

        <source>

            <name>HD</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/movies/HD/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Animation</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Animation/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Comedy</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Comedy/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Fantasy</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Fantasy/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Horror</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Horror/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Sci Fi</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Sci-Fi/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Drama</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Drama/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Documentaries</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/tv/Documentaries/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Ultra-HD</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/movies/Ultra-HD/</path>

            <allowsharing>true</allowsharing>

        </source>

        <source>

            <name>Travelling</name>

            <path pathversion="1">nfs://192.168.0.5/mnt/user/data/media/movies/Travelling/</path>

            <allowsharing>true</allowsharing>

        </source>

    </video>

    <music>

        <default pathversion="1">Music</default>

    </music>

    <pictures>

        <default pathversion="1"></default>

    </pictures>

    <files>

        <default pathversion="1"></default>

    </files>

    <games>

        <default pathversion="1"></default>

    </games>

</sources>


RE: Blasting database, restore watched count - Klojum - 2021-12-23

A backup is better than no backup, provided the backup is intact. Is that still the case? Moving a database from one server to another should also not make it lose certain portions of the database contents. So, changing paths to video locations will not alter view counts. Unless you really don't know what you're doing.

There is a Kodi wiki page addressing the changing of paths in the video database, did you have a look at it? https://kodi.wiki/view/HOW-TO:Update_Paths_In_MySQL
It's a little outdated and does need some patching up, but the basics still apply.

RE: Blasting database, restore watched count - elmuziko - 2021-12-23

So, I seem to have got it working again, though I'm on tentative hooks. 

Thank you for the DB link. That's how I was updating the SQL tables, only I never did art. Didn't realise links were in there also.
So I put 116 back in, loaded Kodi, it updated to 119, then I changed the paths. It worked on Windows, not on Android.
Then, I dropped 119, loaded 116, changed all the paths, and fired up Android. It worked. I've not opened Windows and quite frankly I don't want to. At least not at the moment. Windows is rarely used. I'm gonna take an export of 119 as it now so IF firing Windows does bork it, at least I have a working 119 db.

Thank you again for your help/patience. 
If this is a bug you want to root out I'm happy to provide you with debug logs and everything. Now I know how to get it working I can drop DB's as needed.