MySQL Database missing movie_view

Help and Support
1

I’m sorry if this is the wrong place for this, if it is, just let me know where I should post it and Ill repost there. Also, I’m quite the linux noob, so excuse me if my troubleshooting is missing something obvious. Thirdly, sorry if this is covered in another post somewhere. I used the search function and didnt see anything that covered this specifically

I’m using the Pi4 image at the top of this post on 2 different pi4s and have installed the latest patches. Creating an internal video database is fine, but when I create one on a mysql server I have hosted on a third pi I’m having problems. I can scan content and can see items being added to the MYSQL database from the progress bar in the gui and by querying the database directly, but I’m unable to view the library from my 2 OSMC nodes using the Movies shortcut. In the kodi.log I can see this error from when I attempt to browse ‘titles’ under Movies.

2021-06-12 16:03:13.004 T:979 ERROR <general>: SQL: [MyVideos119] The table does not exist
Query: select * from movie_view

I installed the mariadb sql client and connected to the database from the machine running OSMC and ran the failing query select * from movie_view, and got the error:

ERROR 1146 (42S02): Table 'MyVideos119.movie_view' doesn't exist

I ran a SHOW TABLES query and got the below output. There’s no movie_view table present

+-----------------------+
| Tables_in_MyVideos119 |
+-----------------------+
| actor                 |
| actor_link            |
| art                   |
| bookmark              |
| country               |
| country_link          |
| director_link         |
| episode               |
| files                 |
| genre                 |
| genre_link            |
| movie                 |
| movielinktvshow       |
| musicvideo            |
| path                  |
| rating                |
| seasons               |
| sets                  |
| settings              |
| stacktimes            |
| streamdetails         |
| studio                |
| studio_link           |
| tag                   |
| tag_link              |
| tvshow                |
| tvshowlinkpath        |
| uniqueid              |
| version               |
| writer_link           |
+-----------------------+

This is a brand new database (I dropped my previous one during a moment of poor judgement after dealing with some corruption I couldn’t figure out how to resolve after I initially upgraded. In hindsight this may have been a rash decision). I created another new database to test on and saw the same behaviour on that as well. I’ve also rebuilt the OSMC nodes a few times from scratch.

Does this look like a bug in how the database is written to or am I doing something wrong? Can anyone else replicate this with a new database? I’d appreciate any help.

Thanks.

2

Moved it to a new thread as it is unlikely related to the testing version you are using.

Which version of mysql are you using on that Pi?

3

Thanks!

Server version: 10.3.27-MariaDB-0+deb10u1 Raspbian 10

I think It was at 10.1 before when I initially started having problems. I even went so far as to uninstall and reinstall mariadb after I dropped the database.

4

Did you gave the user full rights GRANT ALL ON *.* not limited to any database?

5

Yeah, I followed the steps in this post when I reinstalled, substituting mariadb for mysql

6

I suggest you shutdown all clients, drop the database, aand then just start up one client to have the database created.

7

Thanks for your help mate.

I went through a bunch of other troubleshooting, then I imaged up a Pi3 I still had and connected to the same database server with the same credentials. It created it’s own database and has the exact same problem, so I think it’s got to be something else wrong with my database server. I’m sorry I should have thought of doing that originally but I got caught up on the Pi4.

I’ll keep testing and see about reinstalling mariadb again maybe, but if you have any other suggestions I’m all ears! :slight_smile:

8

in that referensed guide, this line has issues:

mysql -u root -p CREATE USER 'osmc'@'%' IDENTIFIED BY 'osmc’; GRANT ALL PRIVILEGES on *.* TO 'osmc'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; \q

second 'osmc there is a ´ instead of a ’

10

I’m not sure why this fixed it, but I’m posting it here just in case anyone else comes across this issue.

I still had the problem on a completely new server running a new install of mariadb configured using the above steps, and a fresh and updated single osmc instance, so nothing legacy at all.

Running the below command however fixed it, and content is now being written and read correctly:

sudo mysql_upgrade --force

It was already at 10.3.27 (the latest version for the pi as far as I can tell), so the version didnt change, but I’m guessing it did something to reconfigure the tables correctly?