MySQL Library Not Showing, Updating or Cleaning

Solved by the post marked as solved. Also see this comment for more detail.

Hi there,

I’m at a loss in getting this resolved and read through tons of different forum posts, but I can’t seem to get my issue resolved. The OSMC forum seems to have the friendliest and most helpful community, so I thought that I would post it here in the hopes of finding the hopefully small issue that I am missing.

Background:
I have had my Raspberry Pi devices for about 5 years now and started using OSMC about a year ago and everything has been working perfectly. I decided to move from Sickrage to Medusa, but I saw that it is recommended to start with a fresh library and along with all the additions and removals from my library I decided to start completely fresh and do a clean install of OSMC and setting the rest (MySQL, NZBGet & Medusa) from scratch.

Setup
Raspberry Pi 3 running as my primary device in the lounge connected via WiFi. This has all the applications installed and does all the work.
Raspberry Pi 2 in the bedroom with just OSMC installed and connecting to MySQL on the primary device
External Hard Drive mounted on the Asus router with guest login (No username or password required)

Problem
I installed OSMC from scratch with the installer and updating to the latest version I installed MySQL, NZBGet & Medusa.
I then created folders for the network drive and configured fstab.
I setup MySQL following the guide on the Kodi wiki keeping with kodi as the name and password for the MySQL User.
I gave the Raspberry Pi a static IP address through the MyOSMC interface and ticked Wait for Network
I configured MySQL’s connection through the MyOSMC interface.
I then added the network drive as a library using the IP address and the import starts off OK and the TV Shows section is visible.

After some time the TV Shows section is no longer visible and I am unable to update the library or clean the library.
This makes me feel that the issue is definitely with MySQL’s setup / configuration and I am certain that I am missing something somewhere although I can’t figure out where I made a mistake.

I have also tried stopping and starting the MySQL service to see if it may be network related even though I found that OSMC waits for MySQL I thought I’d try it as the shares may not have been visible yet.

Before taking logs I have dropped the MyVideos database and restarted the Raspberry Pi, but I was still not able to update or clean the library.

Logs:
https://paste.osmc.tv/zofugayuqi

The logs has Debugging enabled and the following was done:

  • Restart the Raspberry Pi
  • Navigate to Media and perform Clean library
  • Navigate to Videos opening up the folder in the SMB share and selecting Update Library on the left

Some useful info:

Advancedsettings.xml (Automatically created by MyOSMC)

<advancedsettings>
	<videolibrary>
		<importwatchedstate>true</importwatchedstate>
		<importresumepoint>true</importresumepoint>
	</videolibrary>
	<videodatabase>
		<name>MyVideos</name>
		<host>192.168.0.42</host>
		<user>kodi</user>
		<pass>kodi</pass>
		<type>mysql</type>
		<port>3306</port>
	</videodatabase>
</advancedsettings>

/etc/fstab (Is the issue possibly here?)

/dev/mmcblk0p1  /boot    vfat     defaults,noatime,noauto,x-systemd.automount    0   0
//192.168.0.254/sda2/Series     /mnt/External/Series    cifs    guest,uid=1000,gid=1000,noauto,x-systemd.automount,rw,iocharset=utf8    0       0
//192.168.0.254/sda2/Movies     /mnt/External/Movies    cifs    guest,uid=1000,gid=1000,noauto,x-systemd.automount,rw,iocharset=utf8    0       0
//192.168.0.254/sda2/Downloads  /mnt/External/Downloads cifs    guest,uid=1000,gid=1000,noauto,x-systemd.automount,rw,iocharset=utf8    0       0
# rootfs is not mounted in fstab as we do it via initramfs. Uncomment for remount (slower boot)
#/dev/mmcblk0p2  /    ext4      defaults,noatime    0   0

I have also tried making use of another SD card and doing everything from scratch on there, but the issue remains.

As mentioned above I believe there is a mistake on my side with something that I missed compared to roughly a year ago when I set everything up previously and would appreciate it if somebody could take a look and either point me in the right direction or tell me what to do to resolve the issue.

I am not advanced in Linux, but I feel that I understand it, but I just need to be pointed in the right direction, so if you require something to be provided or uploaded, just point me at where I can get it or excuse me for asking for some more information.

Thanks in advance (hopefully)
Fred

Hi,

From the looks it the logs provided are from the pi3, with mysql on, is this correct?

8:32:58.688 T:1937765360 NOTICE: Running database version MyVideos107

Anyway if that is the case the database name is MyVideos107, I think if you update myadvancesettings.xml with this database name, you should be ok:

<name>MyVideos107</name>

Thanks Tom.

Hi Tom,

Thanks for the reply.

Yes, it is a Pi3 with MySQL.

I always thought that it simply refers to the database version as mentioned here

I have made the change and rebooted, but I’m still not able to update the database and the TV Shows section is still not displaying.

Thanks,
Fred

Yeah you are right, @Tom_Doyle must have misread your logs.
<name>MyVideos</name> is correct.

But what doesn’t look so healthy is Oct 31 18:32:52 osmc mysql[372]: not closed cleanly..
I suggest you check your databases first. Either via command line or use phpmyadmin if you prefer GUI

Hi @fzinken,

Thank you.

That does however look like a line that went over to the next line if you at the below:

Oct 31 18:32:52 osmc mysql[372]: Starting MySQL database server: mysqld . . …
Oct 31 18:32:52 osmc mysql[372]: Checking for tables which need an upgrade, are corrupt or were
Oct 31 18:32:52 osmc mysql[372]: not closed cleanly…

Thanks,
Fred

True, any how checking your tables and their contents will narrow down what is going wrong.

Will do.
I don’t mind doing it through command line, just not too sure how.
I found the mysqlcheck command. Would that be OK?

As a side note, dropping the database and restarting also doesn’t work.

Thanks,
Fred

mysqlcheck --all-databases should be ok.

While I didn’t meant so much to run the check on the database but I ment going into the SQL database, checking if the tables are created and if they have any content.

Hi,

Apologies its been awhile sent I set up mysql and all I could remember was in phpmyadmin, the myvideo database has number on the end.

Again Sorry, hope you get this sorted.

Thanks Tom.

Perfect, thanks. I will look into this tonight. I forgot to turn the Pi back on this morning, so I don’t have access from the office.

Just to add to it. What should I do if it does not create the tables or if they don’t have any content. (Just to save me a step when checking it later)
I have a feeling that it might not be happening, especially seeing how dropping the database and trying to update does nothing.

No problem Tom. Any bit of help helps at this stage. I’m at a loss and willing to try anything even if I have to reload again. (I still think I’m missing something small)

Thanks,
Fred

Well if the tables are not created than we know that there is an issue and I think you then could enable the component based logging to give us more clues.
BTW just checked your logs again. Somehow strangely it looks like you are not doing fstab based SMB mounts which is good but the only source you have defined is still using SMB syntax and not the source added via /mnt/External/Series

HI,

I’d try it on a new sd card.

Thanks Tom.

I vaguely remember seeing a component based logging under the debug logging options. Do I just select it there and capture again?

If I remember correctly you cannot make use of a locally mounted source when sharing with MySQL. It needs to be a network share for the other devices to be able to connect to the media. Is there another way that I should do it?

I’ve tried another card that I have, not brand new though, but I have ordered a new one just in case.

Yes

Well you always can/should use the local folders if you use fstab based access. So you then would use path substitution to ensure all clients
http://kodi.wiki/view/path_substitution

Thanks so much for the help so far!

This is a bit confusing as I have never done it, so I just want to make sure I am understanding correctly:

  • Remove the SMB library and rather make use of the fstab mount as the library.
  • Add the below to the advancedsettings.xml for the clients only or also the main Pi? (I’m assuming clients only)
 <pathsubstitution>
  <substitute>
    <from>/mnt/External/Series/</from>
    <to>SMB://192.168.0.254/sda2/Series/</to>
  </substitute>
 </pathsubstitution>

Iirc, they must only match. If you are using fstab based mounts of the same name, to the same mount point on both systems, it will work fine.

Now that I think about it, it makes sense this way. As the location will stay the same and through the mount it will point to the same file on the network.
I will also give this a go as well.

Correct

You only would need this on any client that doesn’t support the fstab based mount. If you use the same fstab mount on all clients no substitute needed

This makes perfect sense! I’ll post back once I have tried this.

Thanks again for all the help!

Hi @fzinken & @ActionA,

I have done the following:

  • Disabled MySQL VideoDB in MyOSMC
  • Deleted the .kodi folder and rebooted
  • Dropped the MyVideos107 database and rebooted
  • Enabled MySQL VideoDB in OSMC
  • Set the /mnt/ location as the library

It is now busy scraping and creating the database again, so we are definitely getting somewhere. It did do this the first time after a clean install previously, but I’m keeping my fingers crossed.

I’m leaving it to scrape overnight and I will check back again tomorrow morning to see if everything is still OK. If it is I will then setup the fstab on the secondary device and connect it up again.

Thanks again for the help!

2 Likes