+- 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: Solved Rescan after Migrating SQL machines? (/showthread.php?tid=373730)
Rescan after Migrating SQL machines? - nolos - 2023-07-09
Hell all. I just moved my mariadb to another machine. Got everything done correctly but when I log back in to kodi with the new ip in the advanced settings, my library is empty. All my sources are obviously there since the files are still in the original location but it want's me to set the content for each of the folders I have. I don't really want to do that as it will rescan all of the media in those folders and I will lose all of my watched statuses I assume which negates the reason for exportin/importing my sql data over. Did I miss a step? Any thoughts?
python:2023-07-08 19:44:32.308 T:7 info <general>: Contents of special://profile/advancedsettings.xml are...
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.**</host> (new host IP is here)
<port>3306</port>
<user>*****</user>
<pass>*****</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>192.168.1.**</host> (new hoat IP is here)
<port>3306</port>
<user>*****</user>
<pass>*****</pass>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
<pathsubstitution>
<substitute>
<from>special://profile/favourites.xml</from>
<to>smb://USERNAME[email protected]/F/Media/favourites.xml</to>
</substitute>
</pathsubstitution>
</advancedsettings>
python:2023-07-08 19:44:32.663 T:17 info <general>: Running database version Addons33
2023-07-08 19:44:32.663 T:17 info <general>: Running database version ViewModes6
2023-07-08 19:44:32.663 T:17 info <general>: Running database version Textures13
2023-07-08 19:44:32.665 T:17 info <general>: MYSQL: Connected to version 10.11.4-MariaDB-log
2023-07-08 19:44:32.669 T:17 info <general>: Running database version MyMusic82
2023-07-08 19:44:32.674 T:17 info <general>: Running database version MyVideos121
2023-07-08 19:44:32.674 T:17 info <general>: Running database version TV40
2023-07-08 19:44:32.674 T:17 info <general>: Running database version Epg16
To recap, it seems to be connecting to the db just fine and everything was migrated over. It's just wanting me to set the content again (each Files folder says none now) which I think will rescan everything as new. If I change the advancedsettings file to point back to the original sql server then everything in the Shows and Movies section populates. The process I used was:
First: I had an empty host and restored from backup and both the DBs were then showing (MyMusic82 and MyMusic121) when I tried this was, I got errors in the kodi log saying it couldn't find the 2 DBs and it ultimately created new ones with the same name but empty so I dropped them
Second: I started with empty host and opened kodi so it would create the 2 DBs itself. I then closed kodi and just ran the backup script and had it insert all the rows to the existing DBs without dropping them first.
RE: Rescan after Migrating SQL machines? - Klojum - 2023-07-09
(2023-07-09, 03:20)nolos Wrote: when I log back in to kodi with the new ip in the advanced settings, my library is empty.
Kodi stores the FULL network address for all videos into its database, that includes IP addresses. So if your storage device gets a different network location, things tend to get awkward. This is one part of the Kodi database that hasn't been changed since the very beginning, but would be a recommended part for a change.
One option is to change/hack the IP addresses in your Kodi database directly into the new address. I'm just not sure how comfortable you are with running MySQL command queries. It is the quickest method, but you need to know what you're doing. This is an example where purely the IP address changes and the subsequent path is identical. These four database tables will need to be updated.
sql:UPDATE art SET url = replace(url, '192.168.1.101','192.168.2.202');
UPDATE episode SET c18 = replace(c18, '192.168.1.101','192.168.2.202');
UPDATE movie SET c22 = replace(c22, '192.168.1.101','192.168.2.202');
UPDATE path SET strPath = replace(strPath,'192.168.1.101','192.168.2.202');
Alternatively, you could use path substitution to reroute the IP address when viewing videos, but to me that's like putting a band aid onto the problem and not a real solution. I don't know if the same substitution still works okay if you need to write stuff (subtitles, fanart, nfo files, etc) into the new location.
(2023-07-09, 03:20)nolos Wrote: and I will lose all of my watched statuses
Watched statuses and resume timestamps should be exported by default by now in Nexus.
RE: Rescan after Migrating SQL machines? - nolos - 2023-07-09
Thanks Klojum. Maybe I'm not understanding correctly but all my videos are still in the same place. I just moved my sql server. I have my videos on an external drive (eg. smb://192.168.2.56/media/blurays/atomic blonde/atomic blonde.mkv) and thats the path I also have in the db. None of that changed.
RE: Rescan after Migrating SQL machines? - Klojum - 2023-07-09
Oops... I think the heat from this weekend's sun got the better of me.
Moving your SQL database shouldn't make a difference as to where it is running, as long as it is accessible for all Kodi clients.
Checking the contents of the new SQL database server with an external SQL client would be the 1st and simplest option.
Do you have PHPMyAdmin installed, or some other tool?
(2023-07-09, 03:20)nolos Wrote: I just moved my mariadb to another machine.How did you move your database content? Via a mysqldump or otherwise?
RE: Rescan after Migrating SQL machines? - nolos - 2023-07-09
Yes, I dumped it and then ran the sql statement on the new server after I let kodi create the 2 databases on there. I made sure it just inserted the data and didn't drop the newly created DBs first. So Kodi created the MyVideos121 and MyMusic82 DBs, then the sql file inserted all the recors into them. I can access all the data from a sql client and everything looks to be in there just fine. The kodi logs show it's connecting up...just not populating in kodi. It's almost like, even thought it would be fine, kodi has something that says "if anything changes in the system, then unset all the media". Does that make sense? Just to confirm, there isn't anywhere besides the advancedsettings.xml that references the sql IP right?
RE: Rescan after Migrating SQL machines? - Klojum - 2023-07-09
Just to make sure... Have the views and triggers also been (re)created on the new database server? Having full user rights for that on the MariaDB server is a must.
RE: Rescan after Migrating SQL machines? - nolos - 2023-07-09
Just figured it out. So, I have 20.1 on one pc and 20.2 on another. I have heidisql on the 20.2 pc. I opened kodi on the 20.1 pc to have it create the DBs. Then I went upstairs to my 20.2 pc, dumped the original DB and restored it to the new one. Well, I'm guessing 20.1 creates the DBs as myvideos121 and mymusic82...all lowercase and 20.2 uses MyVideos121 and MyMusic82. Things weren't jiving. I dropped everything. Had the 20.2 pc create the DBs (it made them as MyVideos121 and MyMusic82) and then I restored to that. Everything works now and both pcs are showing the files and statuses. Something must have changed between sub-versions on kodi. Either that or something's up with that heidiSQL program and it was lowercasing everything. I tried so many different ways that it's hard to tell the culprit. Anyway, thought I would let you know in case you want to look into that on the kodi side and see if the naming convention changed for the SQL database creations. Thanks for all your help. You got me digging in the right direction!
RE: Rescan after Migrating SQL machines? - jbinkley60 - 2023-07-10
(2023-07-09, 23:50)nolos Wrote: Just figured it out. So, I have 20.1 on one pc and 20.2 on another. I have heidisql on the 20.2 pc. I opened kodi on the 20.1 pc to have it create the DBs. Then I went upstairs to my 20.2 pc, dumped the original DB and restored it to the new one. Well, I'm guessing 20.1 creates the DBs as myvideos121 and mymusic82...all lowercase and 20.2 uses MyVideos121 and MyMusic82. Things weren't jiving. I dropped everything. Had the 20.2 pc create the DBs (it made them as MyVideos121 and MyMusic82) and then I restored to that. Everything works now and both pcs are showing the files and statuses. Something must have changed between sub-versions on kodi. Either that or something's up with that heidiSQL program and it was lowercasing everything. I tried so many different ways that it's hard to tell the culprit. Anyway, thought I would let you know in case you want to look into that on the kodi side and see if the naming convention changed for the SQL database creations. Thanks for all your help. You got me digging in the right direction!
Out of curiosity are both systems running the same operating system or different ? Some operating systems are case sensitive and some not. Typically MySQL database, table and column names are case sensitive on non-Windows systems but not case sensitive on Windows systems. So a general rule of thumb if you are running with both is to build things on your non-Windows system and your Windows systems should have no problems accessing MySQL. If they are both non-Windows then I'd lean towards the heidiSQL program or possible a difference in the MySQL builds themselves.
Thanks,
Jeff
Rescan after Migrating SQL machines? - Klojum - 2023-07-10
I would have thought that SQL was independent of OS shenanigans like uppercase/lowercase, but apparently it is still possible... Weird.
Anywho... Thread marked solved.