[Starscream]

I made a fresh install of my whole KODI-System and got suprised: 19.5 isn´t able to create MyMusic82 on a fresh MariaDB-Environment.

If you start KODI the 1st time, you will notice the following Log-Entries:

2022-12-30 15:43:23.410 T:8100 INFO <general>: Contents of special://profile/advancedsettings.xml are...
<advancedsettings>
[...]
<musicdatabase>
<type>mysql</type>
<host>xx.xx.xx.xx</host>
<port>3306</port>
<user>xxx</user>
<pass>*****</pass>
</musicdatabase>
</advancedsettings>

Later the following extract:

2022-12-30 15:37:18.583 T:5164 INFO <general>: MYSQL: Connected to version 10.6.10-MariaDB
2022-12-30 15:37:18.586 T:5164 ERROR <general>: Unable to open database: MyMusic82 [1049](Unknown database 'MyMusic82')
2022-12-30 15:37:18.591 T:5164 ERROR <general>: Unable to open database: MyMusic81 [1049](Unknown database 'MyMusic81')
[...]
2022-12-30 15:37:18.802 T:5164 ERROR <general>: Unable to open database: MyMusic32 [1049](Unknown database 'MyMusic32')
2022-12-30 15:37:18.809 T:5164 INFO <general>: creating version table
2022-12-30 15:37:18.825 T:5164 INFO <general>: create artist table
2022-12-30 15:37:18.839 T:5164 INFO <general>: create album table
[...]
2022-12-30 15:37:19.041 T:5164 INFO <general>: create removed_link table
2022-12-30 15:37:19.054 T:5164 INFO <general>: CMusicDatabase::CreateAnalytics - creating indices
2022-12-30 15:37:19.732 T:5164 INFO <general>: create triggers
2022-12-30 15:37:19.966 T:5164 INFO <general>: Create native MySQL/MariaDB functions
2022-12-30 15:37:19.967 T:5164 ERROR <general>: SQL: [MyMusic82] Undefined MySQL error: Code (1728)
Query: DROP FUNCTION IF EXISTS udfFirstNumberPos

2022-12-30 15:37:19.967 T:5164 ERROR <general>: CDatabase::CreateDatabase unable to create database:0

If you restart KODI, you will get this messages:

2022-12-30 15:43:23.999 T:6092 ERROR <general>: SQL: [MyMusic82] The table does not exist
Query: SELECT artistview.* FROM artistview WHERE (EXISTS (SELECT 1 FROM album_artist WHERE album_artist.idArtist = artistview.idArtist)) AND (artistview.strArtist != '') ORDER BY artistview.idArtist

2022-12-30 15:43:24.000 T:6092 ERROR <general>: CMusicDatabase::GetArtistsByWhere failed

And so on...

Is someone able to confirm this behaviour?

To do so: just stop KODI, rename all of your old "MyMusicXX" tables (maybe the right time to remove the old stuff before MyMusic82?) and restart KODI.
This is maybe just a job for the developers ;-)

[Starscream]

The detailed error was:

DEBUG <general>: Mysql execute: DROP FUNCTION IF EXISTS udfFirstNumberPos

The MariaDB reported Error Code 1728.

A quick search brought me to this solution:
Open a shell on your "SQL-Server"
Execute the following statement:

mysql_upgrade -u user -ppwd

(where "user" ist your administration user of the MySQL/MariaDB-instance (root, admin, ...) and "pwd" is the password of this account (notice the "-p" in front of the password!).

This will upgrade all components of your RDBMS.

But attention: at your own risk!

[Klojum]

Is someone able to confirm this behaviour?

Next time, kindly provide the full debug kodi.log file via a pastebin instead of pasting a couple of snippets into the forum that appear to be interesting.

SQL: [MyMusic82] The table does not exist

Missing database views may be the result of insufficient database user rights when (re)creating the video and/or music databases and their respective views.
It also isn't the first time that MariaDB has its own quirks. Good to know that an update was able to solve your situation.

[Starscream]

Unfortunately it was not the update which solved the problem.

I had installed KODIs 19.5 and it crashed the MyMusic.
What i had not seen was the fact, that i had an update on MariaDB in the night before.
So it was not a user related access right which broke my whole system, it just was an weired "update feature" which is well known by the developers of MariaDB (there is also a ticket regarding a "mysql_upgrade after an update").

So i asked just for a confirmation of the behaviour - MyVideo worked well.
I thought that it would not be a problem to just delete the MyMusic on developers side an restart the testing instance of KODI.

If i was sure about a real KODI-problem, i had posted the whole stuff.

Anyway: i also looked inside your DB-related sources (i am not a c++-coder, just a sql native, but able to read the code) and noticed, that you integrated the sql statements inside the c++-code, which is not the best idea i guess :-)
imho it could be a good idea, to place the statements outside of the code like you do it with all other ressources.
advantages:
- you would have an dedicated IDE for the SQL stuff
- modifying SQL withour compiling
- dedicated statements for different RDBMS
- documentation of the RDBMS structure would be more easy
- DB-changes should be easier
- maybe the chance to integrate the other databases (Addons, Epg, Textures, TV and ViewModes)?
- no more "cxx"-fields :-)

if someone of the c++-team is ready to go and just need some help for the sql stuff, i would be glad to give some support to the project.