When setting up connection to a remote MySQL server (on local network) via MyOSMC --> Networking --> MySQL, the existing advancedsettings.xml file was overwritten. Unfortunately, debugging was not enabled, so I don’t have a log to share.
osmc version: 2016.08-1
kodi version: 16.1
Recommendation: Make backup of existing advancedsettings.xml. Append existing advancedsettings.xml.
SQL is currently configured via advancedsettings.xml, hence why the add-on writes to it. If you can reliably replicate your settings being overwriten by My OSMC, I’m sure @Karnage will be interested in taking a look
Sam
You cant just append to the advancedsettings.xml file, the xml structure has to be honoured.
What was in the previous advancedsettings.xml file?
Thanks for the quick replies.
“Append” was the wrong choice of word. More correctly, advancedsettings.xml should be edited in such a way as to preserve the existing tag structure while adding the new MySQL relevant tags.
I don’t have a backup of the original advancedsettings.xml but it was similar to the following, copied from Kodi: HOW-TO Modify the video cache, Example 4. (I’ve solved the network buffering issues on my system so I no longer have need for the <network> tags, which is unfortunately why I don’t have a backup copy.)
<advancedsettings>
<network>
<buffermode>1</buffermode>
<cachemembuffersize>139460608</cachemembuffersize>
<readbufferfactor>20</readbufferfactor>
</network>
</advancedsettings>
The overwrite behavior occurred when I initially attempted to manually modify advancedsettings.xml as described here: MySQL Setting up Kodi, MySQL and advancedsettings.xml. I had not previously attempted to setup MySQL via the MyOSMC Networking settings addon, rather only by editing the .xml file directly. Upon reboot, the settings that were manually entered in the existing advancedsettings.xml did not take effect and kodi continued to use the local databases. After verifying the network MySQL server was correctly configured, I then edited MySQL settings via the MyOSMC settings addon as described above. After reboot, the new MySQL settings took effect and the network databases were being used. However, the previously settings were no longer present in advancedsettings.xml. The new advancedsettings.xml is as follows, without the previously entered <network> tags:
<?xml version=“1.0” encoding=“utf-8”?>
<advancedsettings>
<musicdatabase>
<name>MyMusic</name>
<host>192.168.X.X</host>
<user>[MySQL_USER]</user>
<pass>[MySQL_PW]</pass>
<type>mysql</type>
<port>3306</port>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
<videodatabase>
<name>MyVideos</name>
<host>192.168.X.X</host>
<user>[MySQL_USER]</user>
<pass>[MySQL_PW]</pass>
<type>mysql</type>
<port>3306</port>
</videodatabase>
</advancedsettings>
I have not yet tested if this can be recreated by disabling MySQL via the MyOSMC settings addon and then writing a new advancedsettings.xml file without MySQL tags, rebooting, then using the MyOSMC addon to create a new advancedsettings.xml.
Can it be confirmed that setting MySQL via the MyOSMC addon does, in fact, overwrite an existing advancedsettings.xml?
It shouldn’t do. Give it another go and see what happens.
Thanks. I do plan on testing. When I do, I’ll enable debugging and follow up.
What it does is read the xml file, parses all the structure into a dictionary, and then either changes the SQL details that are present in that or adds them in. It then writes all that dictionary back to an xml structure and into the file.
The final xml structure you posted is incorrect. The last tag should be </advancedsettings>.
I corrected the close tag in my previous post, thanks for catching it. I verified that the tag is correct in the working file on my system. I must have made a mistake transcribing it into the post.
I’m going to run a few tests in a bit to try to recreate the behavior. I should have a follow up shortly.
I was able to recreate the overwrite behavior, as described below. One comment first. I initially attempted to recreate the behavior using a test advancedsetting.xml that had clean formatting, without any comments. In that case, when configuring MySQL via MyOSMC addon, the new MySQL settings were properly incorporated. For my test case, below, I used a test xml file that included a number of comments (similar to the file I had that was originally overwritten but don’t have a backup of). I haven’t confirmed if the comments themselves cause the issue but the behavior does occur under the conditions described below.
Started with the following test advancedsettings.xml:
Enabled logging
Restarted mediacenter
Manually edited advancedsettings.xml to include MySQL settings:
Upon restart, MySQL settings were NOT enabled, per MyOSMC addon.
Then, I enabled and configured MySQL via MyOSMC addon.
Upon exit, the MySQL settings did take effect. However, all the previous network settings and comments had been stripped out. The final advancedsettings.xml was as shown here:
http://pastebin.com/Aq1vJKfq
Logs:
http://paste.osmc.io/uzoquzuvot
As an aside, using the addon to configure MySQL I noticed that it is necessary to type “MyVideos” and “MyMusic” over the preset placeholders with the same values. This has been observed before:
[https://discourse.osmc.tv/t/mysql-issue-on-october-update/9401/16?
Also, I noticed that MySQL credentials are stored in clear text in the log.
Comments will not be retained after parsing, and your change was bad xml; the videodatabase section was not closed. When the advancedsettings.xml file is borked and cannot be parsed, the addon treats it as if it is corrupted or empty and recreates a new one.
If you can access the log, you can also access advancedsettings.xml, so this isn’t really an issue.
Completely my mistake. I re-tested with valid xml (after running through a validator). The settings took effect either directly editing advancedsettings.xml or via MyOSMC addon. I confirm that comments were stripped when parsed by the addon, as stated. Thanks for showing me the error of my ways. Marked resolved.
Might it be useful to save a backup of the borked file, for troubleshooting, etc, rather than overwrite?
Has there been implementation of MySQL option files, so the db credentials don’t need to stored in advancedsettings.xml (and, by extension get passed to the logs, which in turn may or may not be posted publicly?) I know that most (all?) Kodi passwords (e.g. from sources) are automatically stored in passwords.xml but that only seems feasible to use with path substitution for network paths. Access to MySQL databases does not take the form of
PROTOCOL://DB_USER:DB_PASSWORD@SOME_NETWORK_SHARE/PATH_TO_SUBSTITUTE/
MySQL option files and usage for credentical security are described here:
http://dev.mysql.com/doc/refman/5.7/en/password-security-user.html
http://dev.mysql.com/doc/refman/5.7/en/user-names.html
http://dev.mysql.com/doc/refman/5.7/en/option-files.html
Perhaps I should start a separate thread?
Perhaps, but it would be overwritten every time, so it only saves you if you havent gone back into the addon twice.
Good point. Maybe if the script included a timestamp in the backup filename, so a new backup gets created every time the script runs? Something like:
#!/bin/sh
file_name=advancedsettings.xml
timestamp=$(date "+%Y%m%d%H%M%S")
backup_file=$file_name-bak_$timestamp
cp $file_name $backup_fileYep, but then you would end up with a huge long list of advancedsettings.xml files, none of which most users would ever need.
True. What about 1) copy to a defined backup directory, then 2) limiting the number of files in that directory? Similar to what’s described here:
Instead of running as a cron, it can be incorporated into the script to run every time a new advancedsettings.xml is generated.
IMO, in a production setup a user won’t likely be updating advancedsettings.xml too often. Even if once every week, a dedicated backup directory shouldn’t be too large or cumbersome to manage. At least by preserving the few most recent changes a user may find that useful to troubleshoot while testing new settings.
Sending a github PR would be great!
The backup module does that already, but I am not sure the script needs that extra burden, to be honest. If the advancedsettings.xml file is properly constructed (which it will be unless the user edits it themselves AND makes a mistake), then this problem wouldn’t arise.
Thanks for all the feedback. This thread has drifted from the original topic. I’ll shift over to the repo.