+- 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: MySql Database IP address changed (/showthread.php?tid=325605)
MySql Database IP address changed - sweetthdevil - 2017-12-18
Hello all,
My MySql database has changed IP address ( cannot changed it back to the old address due to new hardware restrictions)
Anyway, will change the address in the configuration files but do not want to loose the view status and the rest.
Is there a way to achieve that?
Thanks,
RE: MySql Database IP address changed - Klojum - 2017-12-18
Just change the ip address / hostname in the advancedsettings.xml
RE: MySql Database IP address changed - lovemachine - 2017-12-19
I think the OP is asking about the database path entries themselves (e.g. nfs://192.169.0.xxx/movies/terriblemovie.mkv in MyVideos107 or whatever). I have no idea myself, but just clarifying the question.
RE: MySql Database IP address changed - sweetthdevil - 2017-12-19
Changing the sources.xml passwords.xml mediasources.xml and advancedsettings.xml will give acces to the database.
But every files that were there prior to the IP address move will be located at the old address.
RE: MySql Database IP address changed - Klojum - 2017-12-19
So you have the mysql database and the multimedia collection on the same NAS/server...
There is a wiki page on updating paths inside the database, but the page is quite outdated... See: http://kodi.wiki/view/HOW-TO:Share_libraries_using_MySQL/Update_Paths_In_MySQL
I don't know if you are a little MySQL savvy, but it is one option. The other option is of course to simply start fresh and rescrape your entire collection using the new IP address.
RE: MySql Database IP address changed - Sionzris - 2019-12-12
Because I just recently did exactly that:
The guide mentioned above still works quite well but you just need to watch out a little and use method 1.
Copy and pasting the examples will not work. Things like "movieview" changed to "movie_view" etc. so take a good look at your search results. Basically all you do is search and replace. Make a backup beforehand and if bad comes to worse, you can always revert what you did. HeidiSQL is quite comfortable to use here.
P.s.: Sorry for reviving this, but it's still like the number 1 search on google.
Edit: To make it a little easier, here is some pseudo explanation:
update episode set c18 = replace(c18,'192.168.111.4','192.168.105.2');
means:
update -(Change)
episode -(the tab in your search result)
set c18 -(which column has the data you want to change?)
=
replace -(replace the current value with the new value)
(c18, -(repeat the column here with the data you want to change)
'192.168.111.4', -(the current part that needs to be changed)
'192.168.105.2'); -(the part you want to change it to)
P.s.: Don't be scared of the *complicated databaese*. It's basically an excel sheet and the export is nothing more than one big text file. It might actually be easier to just export to sql, open with an editor and search an replace. When done just import the edited file back in. (Haven't tested this but I don't see a reason why it shouldn't work)
RE: MySql Database IP address changed - Klojum - 2019-12-12
(2019-12-12, 01:52)Sionzris Wrote: The guide mentioned above still works quite well but you just need to watch out a little and use method 1.The 'movieview' may have been a typing error, but whoever created the wiki page probably didn't know that a 'view' in mysql does not need editing. A view is based on the contents of tables in a database, so whenever the table data content changes, the resultset of a view automatically changes as well. You only need to change/replace the data via the tables directly.
Copy and pasting the examples will not work. Things like "movieview" changed to "movie_view" etc. so take a good look at your search results.
The art table is a bit special as data regarding paths are stored a little bit differently. No clue why that is being done.
So I've removed most of the references to the views. I think a more compact wiki article on the subject would be good.
RE: MySql Database IP address changed - Sionzris - 2019-12-12
(2019-12-12, 09:25)Klojum Wrote:It repeats itself throughout the guide even for method 2. Pretty much everything that has a "_" in it, doesn't in the guide, so I figured this was a change in the database at some point.(2019-12-12, 01:52)Sionzris Wrote: The guide mentioned above still works quite well but you just need to watch out a little and use method 1.The 'movieview' may have been a typing error, but whoever created the wiki page probably didn't know that a 'view' in mysql does not need editing. A view is based on the contents of tables in a database, so whenever the table data content changes, the resultset of a view automatically changes as well. You only need to change/replace the data via the tables directly.
Copy and pasting the examples will not work. Things like "movieview" changed to "movie_view" etc. so take a good look at your search results.
The art table is a bit special as data regarding paths are stored a little bit differently. No clue why that is being done.
So I've removed most of the references to the views. I think a more compact wiki article on the subject would be good.
About the not needed changes in view: at least it doesn't hurt and it makes it easy to check that you didn't miss anything. A search result of 0 is the best confirmation for a user that is maybe in over his head because he just followed some guide on some website to set it up once. I replaced every IP with the new one *twice* (yeah I can't decide on my IP range) and the database is working fine. My OCD would be triggered in any case if I left some old IPs in there. Writing an updated guide for this is quite trivial though and shouldn't be much of a problem. Heck, it would probably be possible to write an addon to change the database IP for users. I don't know how to write addons though.
RE: MySql Database IP address changed - Sionzris - 2019-12-20
These were the querys I used, in case someone wants to save themselves some time:
Quote:UPDATE art SET url = replace(url,'192.168.105.2','192.168.111.111');
UPDATE episode SET c18 = replace(c18,'192.168.105.2','192.168.111.111');
UPDATE episode_view SET c18 = replace(c18,'192.168.105.2','192.168.111.111');
UPDATE episode_view SET strPath = replace(strPath,'192.168.105.2','192.168.111.111');
UPDATE files SET strFilename = replace(strFilename,'192.168.105.2','192.168.111.111');
UPDATE movie SET c22 = replace(c22,'192.168.105.2','192.168.111.111');
UPDATE movie_view SET c22 = replace(c22,'192.168.105.2','192.168.111.111');
UPDATE movie_view SET strPath = replace(strPath,'192.168.105.2','192.168.111.111');
UPDATE path SET strPath = replace(strPath,'192.168.105.2','192.168.111.111');