mySQL woes

Simorgh · 4 November 2016 03:02

Hi all.

Thank you for a great post.

https://discourse.osmc.tv/t/wiki-install-and-configure-mysql-server-and-clients/19170

I did install MySQL on my RPi3 running latest OSMC.
did the following:

and changed the bind-address to 0.0.0.0 in my.cnf file, restarted mysql… and everything went well.

I scanned my database and it took a while to bring all the Movie information from Universal Movie Database and it was all done.

Now on my computer I wanted to test if the mysql is set up OK or not, (before I set the other two RPi’s in the house to bring the movie info through mysql), I deleted the MyVideos99.db (KODI on the computer) and add this to advancedsettings.xml

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>10.0.1.120</host>
    <port>3306</port>
    <user>osmc</user>
    <pass>osmc</pass>
  </videodatabase> 
  <!-- 
  
  <musicdatabase>
    <type>mysql</type>
    <host>10.0.1.120</host>
    <port>3306</port>
    <user>osmc</user>
    <pass>osmc</pass>
  </musicdatabase>
  
  -->
  <videolibrary>
    <importwatchedstate>false</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

But what happens now is that the kodi on the computer is scanning the movies list that is pointed to my RPi3 running the mysql server, and is bringing the movie information from Universal Movie Database.
Shouldn’t it just read from the RPi3’s MyVideosXX.db ?
Is it not this the whole point with the mysql to not scan everytime for every in my case RPi’s?

Thank you for your time and any help if possible.

Regards

fzinken · 4 November 2016 03:12

No need to do that it will just be ignored when you have MYSQL configured.

Well not from your MyVideosXX.db but from your MYSQL database.

So to troubleshoot I suggest you first start on the Pi that has your MYSQL server running.

Enable debug logging
Browse a bit thru your library (e.g. play a movie shortly)
Upload logs with grab-logs -A and share the URL

You also manually can check if the databases are correctly created. Log into OSMC with ssh and run mysql -h localhost -u osmc -p
when connected that run show databases;

Simorgh · 4 November 2016 03:57

Thank you for the reply.

on server side, or client side?

this command gives me this:

Enter password:
ERROR 1045 (28000): Access denied for user ‘osmc’@‘localhost’ (using password: YES)

this works though:

mysql -u root -p

…

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| MyVideos99 |
| mysql |
| performance_schema |
±-------------------+
4 rows in set (0.00 sec)

Should I clean my library and configure my advancedsettings.xml file even on the server side like it says here:
http://youresuchageek.blogspot.fr/2012/09/howto-xbmc-share-and-sync-your-media.html

On that website it says:

If you want to re-create it from scratch (my advice), do:

In XBMC, Go to Video and delete each source you have previously set, say Yes when XBMC asks if you want to purge these media from your Library

Go to Parameters > Video and Clean your Library

Configure your mysql settings by editing your advancedsettings.xml (~/.xbmc/userdata/advancedsettings.xml) and adding:

Adapt “xxx.xxx.xxx.xxx” with your main IP Address:

<videodatabase>
    <type>mysql</type>
    <host>xxx.xxx.xxx.xxx</host>
    <port>3306</port>
    <user>xbmc</user>
    <pass>xbmc</pass>
</videodatabase>

<musicdatabase>
    <type>mysql</type>
    <host>xxx.xxx.xxx.xxx</host>
    <port>3306</port>
    <user>xbmc</user>
    <pass>xbmc</pass>
</musicdatabase>

Restart XBMC

Go in Video and re-set your Media Sources using Samba/NFS shares instead of Local paths

This is very important, right now any source you want to add in your Media Library must be done using Samba shares, files paths must use UNC patchs to be able to be shared.

It is getting a bit confusing, but I like challenges.

Thank you in advance

fzinken · 4 November 2016 04:08

As mentioned let’s start on the OSMC box that is your server/client. So enable debug logging on your OSMC machine that you have configured to update the database first time.

Sure, all machines that use Kodi and that share a database need that configuration. That your OSMC machine also acts as a MYSQL server doesn’t mean that Kodi directly uses it.

Actually I guess that is your simple issue, you sofar only have configured it on your PC. Means that when after you configured it on your PC it started to scrap into you Database. Now if you configure it on OSMC it will directly use the database.

Just a reminder depending on your file sharing you might also have to look into path substitution.

Simorgh · 4 November 2016 14:27

Thank you @fzinken for your help and time.

I did enable debugging, or at least part of it was already enabled.

Then on my server side (RPi3 running osmc) removed my Video Sources and had it remove/clean the library, which took some time. (+45 minutes)

Checked the privileges for user osmc in mysql:

mysql> show grants for osmc;
±-------------------------------------------------------------------------------------------------------------------------------+
| Grants for osmc@% |
±-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘osmc’@‘%’ IDENTIFIED BY PASSWORD ‘*8E44CD9F572EA1139FD00A97A85731AA7B0338D4’ WITH GRANT OPTION |
±-------------------------------------------------------------------------------------------------------------------------------+

Restarted the mysql service and are going to reboot and start adding the Video sources again. Going to report back.

How can I not share my Video list/paths through grab-logs?

Thank you in advance.

fzinken · 4 November 2016 14:31

Have you added the mysql settings into the adancedsettings on that machine?

Well if you really want to remove them (why?) then you would need to save the file locally first, edit it and then upload

Simorgh · 4 November 2016 14:35

yes I have

<videodatabase>
 <type>mysql</type>
 <host>10.0.1.120</host>
 <port>3306</port>
 <user>osmc</user>
 <pass>osmc</pass>
</videodatabase>

This ip is always going to be used by this machine. Set in the router.

fzinken · 4 November 2016 14:40

Ok, it’s ok if you want to use the same block on all.
While actually on the machine where the MYSQL server is local you could actually use

<videodatabase>
 <type>mysql</type>
 <host>127.0.0.1</host>
 <port>3306</port>
 <user>osmc</user>
 <pass>osmc</pass>
</videodatabase>

Then you on that machine don’t depend on the network to be up.

Also a reminder for all machines to tick “wait for network” in MyOSMC and as I mentioned earlier check possible path substitution

Simorgh · 4 November 2016 14:43

@fzinken Thank you so much for your time. I truly appreciate it.

I added my Movies now, add adding the folder not locally but through samba. Now it is scanning. How do I know that it’s saving it in sql database and not the old way?

Thanks

Simorgh · 4 November 2016 14:46

You are right. I will change the <host> for my server machine.
While it’s adding the movies, one by one, I did open the Movies tab and started a movie that is scraped. Now I am going to share the log

fzinken · 4 November 2016 14:47

Well it shouldn’t be scanning if it has already scanned them from the PC into the database.

To check what is going on I suggest you log in via ssh and run tail -f .kodi/temp/kodi.log

Simorgh · 4 November 2016 14:48

My RPi is the machine that is connected to the 2 hard drives which have all my videos.
PC machine was suppose to be my client test machine before I changed other RPi machines in the house.

fzinken · 4 November 2016 14:48

In the long run you might also want to install something that makes it easier to check the MYSQL database like phpmyadmin

Simorgh · 4 November 2016 14:51

root@SimorghOSMC:/home/osmc# grab-logs -A
Logs successfully uploaded.
Logs available at http://paste.osmc.io/dogecizame

fzinken · 4 November 2016 14:55

Looks good

10:30:55 29.551449 T:1956348848 DEBUG: Mysql execute: CREATE TABLE episode ( idEpisode integer primary key auto_increment , idFile integer,c00 text,c01 text,c02 text,c03 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c12 varchar(24),c13 varchar(24),c14 text,c15 text,c16 text,c17 varchar(24),c18 text,c19 text,c20 text,c21 text,c22 text,c23 text, idShow integer, userrating integer, idSeason integer) CHARACTER SET utf8 COLLATE utf8_general_ci

Simorgh · 4 November 2016 15:00

So if the scanning goes well. Then on the PC (client machine) I just need to add the sql tags in advancedsettings and it should not scan anymore, is that correct?
When I restarted, kodi took a long time to start, and when I went in the Videos tab, it acted like it was the first time I am using it. (confluence) first time use popped up. Is there something wrong in the advancedsettings that screwed things up?!

Thank you.

yknivag · 4 November 2016 15:07

I think it is taking time to start and looking like a first attempt because you are removing the old .db files. There is no need to do this, the MySQL entries in the advancedsettings.xml will always take priority over any local files. Maybe this is triggering your re-scanning every time?

Once you have one up and running on MySQL, on each other one add the advancedsettings.xml (or add the settings through the GUI in MyOSMC), tick the “wait for network” setting and then reboot it and it should automatically pick up the MySQL library with no re-scans.

fzinken · 4 November 2016 15:10

Yes assuming that they use the same sources or have the respective path substitution

Well that it takes a long time is normal as it would need to create the initial database structure.
While it should not have reset the skin or other settings as they are stored in guisettings.xml

Simorgh · 4 November 2016 15:12

I’ll wait for the Server machine to scan/scrape and then test it on a client machine like my PC, and then report back.

Thank you @fzinken and @yknivag
Appreciated.

Simorgh · 4 November 2016 17:08

@fzinken how do I install phpmyadmin on my RPi running osmc?
By the way, the database is still scraping. 1300 movies on the hard drives.