MySQL issue with one device out of three

MySQL issue with one device out of three - Printable Version

+- 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 issue with one device out of three (/showthread.php?tid=361556)

MySQL issue with one device out of three - jul79 - 2021-03-20

Hello,

First of all I want to congratulate Kodi dev team for delivering such an amazing product, which I have been using flawlessly for years now! Laugh

So far I was using Kodi with the local, sqlite db, but since I have more devices I want to use with the same configuration, I converted to mysql and it works fine on two devices... but not the third one (which incidentally I want to be using more often than the others).

It works fine on:
- one OSMC install (Raspberry Pi 3) - Kodi 18.9
- my Debian main machine (buster) onto which I installed it to test: Kodi 17.6 (yes I know that's old, I could get a newer version, but the point is that it works and I don't plan to use it often if at all)

But it doesn't work on:
my Android 8.1.0 tablet - Kodi 19.0

I also see the following databases on mysql (for videos, I don't Kodi for music):

MyVideos107
MyVideos116
MyVideos119

I believe they are all linked to different versions of Kodi being installed on different devices?

First question: is it possible to just use ONE db over all devices with different versions of Kodi ?
The features I want are simple, the same videos, with the same watched status.

Second question: what exactly is preventing my Android tablet to read the database? what do I need to do?
Please see here the part of kodi.log that deals with databases:

https://paste.kodi.tv/quhebahopu.kodi

Thanks a lot in advance! Big Grin

RE: MySQL issue with one device out of three - Klojum - 2021-03-20

(2021-03-20, 20:04)jul79 Wrote: I also see the following databases on mysql

Those are the databases for the consequtive versions for Kodi 17, 18 and 19.

(2021-03-20, 20:04)jul79 Wrote: First question: is it possible to just use ONE db over all devices with different versions of Kodi ?

Yes. As long as the connected Kodi clients are using the same major version of Kodi. Kodi databases are not backward/forward compatible.

Quote:MYSQL: Connected to version 5.5.62-MariaDB

A bit of an old database version of MariaDB, but should still connect/work.

2021-03-20 18:33:34.155 T:4222    ERROR <general>: SQL: [MyVideos119] Undefined MySQL error: Code (1060) Query: ALTER TABLE path ADD allAudio bool

This can suggest a database user right's problem. If the current database user 'kodi' I assume (as you didn't provide the full kodi.log), then you need to doublecheck the privileges for this database user in the MariaDB server settings.

EDIT: Error Code: 1060. Duplicate column name. Hmm... I hope it's not a MariaDB 5.x incompatibility.
It could be a hiccup. What Kodi device/flavor are you using to update the database?

Just provide the full debug log (wiki), it'll avoid a lot of extra questions.

RE: MySQL issue with one device out of three - jul79 - 2021-03-20

(2021-03-20, 20:32)Klojum Wrote:
(2021-03-20, 20:04)jul79 Wrote: First question: is it possible to just use ONE db over all devices with different versions of Kodi ?
Yes. As long as the connected Kodi clients are using the same major version of Kodi. Kodi databases are not backward/forward compatible.

Quote:MYSQL: Connected to version 5.5.62-MariaDB
A bit of an old database version of MariaDB, but should still connect/work.

2021-03-20 18:33:34.155 T:4222 ERROR <general>: SQL: [MyVideos119] Undefined MySQL error: Code (1060) Query: ALTER TABLE path ADD allAudio bool
This can suggest a database user right's problem. If the current database user 'kodi' I assume (as you didn't provide the full kodi.log), then you need to doublecheck the privileges for this database user in the MariaDB server settings.

EDIT: Error Code: 1060. Duplicate column name. Hmm... I hope it's not a MariaDB 5.x incompatibility.
It could be a hiccup. What Kodi device/flavor are you using to update the database?

Just provide the full debug log (wiki), it'll avoid a lot of extra questions.

Thanks, I'll have to figure out a way to get Kodi 19.* on OSMC then...
Sorry for not providing the full kodi.log, here it is:

https://paste.kodi.tv/uzohulotaf.kodi

Regarding the version of mysql this is indeed a bit old, this is on my Synology NAS, I use the system package on it.

I am using "all" Kodi devices/flavours to update the database but I guess this is wrong...
Which device should I use to update? An older version of Kodi so that every other version can read? or a more recent version? in which case older installs may not work?
Does this mean I should not "set content" on the other Kodi installs?
How can I prevent other Kodi installs from updating the database, can I just allow them to read? (if so should I do it with mysql permissions or inside Kodi?)
Thanks a lot!

RE: MySQL issue with one device out of three - Klojum - 2021-03-20

(2021-03-20, 21:25)jul79 Wrote: I am using "all" Kodi devices/flavours to update the database but I guess this is wrong...
Which device should I use to update?
Does this mean I should not "set content" on the other Kodi installs?
I am using "all" Kodi devices/flavours to update the database but I guess this is wrong...

No. Each one (107,116,119) simply uses a specific Kodi version. You can run multiple databases on the database server, but each Kodi database version needs to be maintained separately.

(2021-03-20, 21:25)jul79 Wrote: How can I prevent other Kodi installs from updating the database, can I just allow them to read? (if so should I do it with mysql permissions or inside Kodi?)

You can opt in/out per Kodi client to delete Kodi library entries with enabling/disabling /Settings/Media/General/Allow renaming and deletion.

RE: MySQL issue with one device out of three - jul79 - 2021-03-20

(2021-03-20, 21:33)Klojum Wrote:
(2021-03-20, 21:25)jul79 Wrote: I am using "all" Kodi devices/flavours to update the database but I guess this is wrong...
Which device should I use to update?
Does this mean I should not "set content" on the other Kodi installs?
I am using "all" Kodi devices/flavours to update the database but I guess this is wrong...
No. Each one (107,116,119) simply uses a specific Kodi version. You can run multiple databases on the database server, but each Kodi database version needs to be maintained separately.

(2021-03-20, 21:25)jul79 Wrote: How can I prevent other Kodi installs from updating the database, can I just allow them to read? (if so should I do it with mysql permissions or inside Kodi?)
You can opt in/out per Kodi client to delete Kodi library entries with enabling/disabling /Settings/Media/General/Allow renaming and deletion.

Thanks, I will try to manage to have the same version over all devices, and then disable on all but one.