[nitro322]

Some quick background: I've been significantly modifying and cleaning up my music over the last few months, and Kodi's not handled the changes very well.  I had a lot of ghost entries in the library that no longer existed, albums that wouldn't be added, etc.  No amount of cleaning the database and scanning seemed to fix it, so I decided to blow away and start over now that my clean-up is mostly complete.

Problem now is that I can't get kodi to successfully re-build my music library.  I've had two primary issues.

First attempt:

I went the straightforward route - remove all music sources (removing all songs when prompted), then cleanup library, then re-add the source and let it scan.  It performed a full scan w/ scraping, taking close to 2 hours to complete.  However, when trying to view my music it would tell me that it needs to do another scan to read all tags.  Repeated this every time for no reason I could determine.  Upon reviewing my library it looked like everything was scraped, and if I viewed all songs it show thousands of songs as expected, but if I navigated my library via Genre, Artist, and then Album, most albums were empty.  Even songs that displayed when viewing all Songs would not display when viewing their Album.  Very odd.

I recently had some weird database issues with old TV episodes not being cleaned up, so I tried manually deleting all rows in the music database tables and running through the above process again.  Same results.

Second attempt:

This time I decided to drop the music database entirely and start from scratch.  My thinking here (and per the documentation) is that kodi will create it's database automatically if it doesn't already exist, so I figured if there was any kind of weird corruption issue this might help.  It does create the database, but not completely - the views aren't re-created.  Now I get ERRORs in the log for every album saying songview doesn't exist:
 

Code:

2023-01-20 23:26:27.878 T:31726   ERROR <general>: SQL: [kodi_music82] The table does not exist
                                                   Query: SELECT * FROM songview where strPath='/home/media/data/music/Lossless/Classical/Aubrey Hilliard/Piano Classics/' ORDER BY strFileName

I'm pretty certain I'm not running into a permission issue here.  I gave it wide-open access:
 

Code:

MariaDB [mysql]> show grants for 'kodi_admin';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for kodi_admin@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `kodi_admin`@`%` IDENTIFIED BY PASSWORD '*XXXXX'                                    |
| GRANT ALL PRIVILEGES ON `kodi_videos%`.* TO `kodi_admin`@`%`                                                       |
| GRANT ALL PRIVILEGES ON `kodi_music%`.* TO `kodi_admin`@`%`                                                        |
+--------------------------------------------------------------------------------------------------------------------+

I even enabled general query logging in mariadb.  I see all of the create statements for the database, tables, indexes, etc., but it doesn't try to create any views.  It does insert songs into the database during the scan:
 

Code:

           128 Query    INSERT INTO song (idSong, dateNew, idAlbum, idPath, strArtistDisp, strTitle, iTrack, iDuration, strReleaseDate, strOrigReleaseDate, iBPM, iBitrate, iSampleRate, iChannels, strDiscSubtitle, strFileName, dateAdded,  strMusicBrainzTrackID, strArtistSort, iTimesPlayed, iStartOffset, iEndOffset, lastplayed, rating, userrating, votes, comment, mood, strReplayGain) VALUES (NULL, NULL, 1, 4, 'Aubrey Hilliard', 'Turkish Rondo', 1, 257, '1998', '1998', 0, 616, 44100, 2,'', '01-Turkish Rondo.flac', '2022-11-12 14:42:07' ,NULL,NULL,0,0,0,NULL, 0.0, 0, 0,'', '', '-3.330,1.000,-5.320,0.985')

After the scan if I run a 'select * from song;' it reports 26490 rows, which should cover my whole collection.  But even now Kodi tells me that my music library is empty.


I'm stumped at this point.  Can anyone recommend how to proceed from here?  I don't care about preserving any music data whatsoever - just want to start from scratch as far as kodi is concerned and scan a new music library.

I'll provide a debug log in the next post.  Need to capture logging from a much smaller source, otherwise it's far too big.

[nitro322]

Debug log

My method here was:
1. Remove all music sources and clean music database
2. Enable debug logging and exit kodi
3. Drop kodi_music82
4. Launch kodi - let it re-create the database
5. Add a source containing a single artist and album - allow it to scan
6. After completion, exit kodi

As above, despite inserting the tracks into the database, kodi still thinks my library is empty.

[Klojum]

From your log:

Code:

2023-01-20 23:54:22.455 T:721 ERROR <general>: Unable to open database: kodi_music82 [1049](Unknown database 'kodi_music82')

It still cannot open your database. Is Kodi redirecting to your local SQLite database? It doesn't look like it though.

Also, some reading material with a possible explanation: https://bobcares.com/blog/mysqldump-got-...-database/

ERROR 1049 (42000): Unknown database 'mydatabasename'
You have set your own database name in the advancedsettings.xml file. What happens if you use take that out and use the default name?

Code:

Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

That's not good.

Code:

2023-01-20 23:54:50.235 T:722 ERROR <general>: SQL: [kodi_music82] The table does not exist
Query: select count(idSong) as NumSongs from songview

One or more views are missing.

[nitro322]

It still cannot open your database. Is Kodi redirecting to your local SQLite database? It doesn't look like it though.

That's because I dropped the database, so that's expected for the first time I restart kodi. As described in my original post, I'm trying to get kodi to recreate it. It does, and that message doesn't appear the second time I launch kodi after dropping the database, but it doesn't create the views that seem to be necessary for the library to function.

Also, some reading material with a possible explanation: https://bobcares.com/blog/mysqldump-got-...-database/

This doesn't appear to be related. There's no database because it was intentionally dropped.

ERROR 1049 (42000): Unknown database 'mydatabasename'
You have set your own database name in the advancedsettings.xml file. What happens if you use take that out and use the default name?

Good suggestion. Just tried, but get the same behavior as described above. Also missing views.

Code:

Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

That's not good.

This is very interesting. Had overlooked it. I wonder if kodi's using some syntax that has been changed or deprecated by mariadb. I see that they have both DROP FUNCTION and DROP FUNCTION UDF. The former is being called here, but I wonder if it's the latter that should be called instead?

Code:

2023-01-20 23:54:50.235 T:722 ERROR <general>: SQL: [kodi_music82] The table does not exist
Query: select count(idSong) as NumSongs from songview

One or more views are missing.

Right... that seems to be the core issue. Kodi will not create the views it needs on a fresh database (at least on my system). Do you know how to do that?


As a quick aside, I tried dropping the music mysql config altogether and restarted kodi. It scanned and scraped the sample album just fine and presented it in the library just as expected. So this is definitely some issue with its mysql/mariadb integration. I just don't know how to get past it.

Also, I do have a backup of the original database as described in that "first attempt" section, before I dropped it. It contains the views. I can restore that, but then I'm back to the first set of issues I described where the scanning doesn't seem to complete properly - it insists on rescanning every time I try to view my music, and the songs are not associated correctly with their parent albums and whatnot. But if that's an easier issue to solve than the missing views, I'm happy to go back to that.

[black_eagle]

OK, so the issue is this

2023-01-20 23:54:24.419 T:721 INFO <general>: Create native MySQL/MariaDB functions
2023-01-20 23:54:24.419 T:721 DEBUG <general>: Mysql execute: DROP FUNCTION IF EXISTS udfFirstNumberPos
2023-01-20 23:54:24.420 T:721 ERROR <general>: SQL: [kodi_music82] Undefined MySQL error: Code (1728) Query: DROP FUNCTION IF EXISTS udfFirstNumberPos
2023-01-20 23:54:24.420 T:721 ERROR <general>: CreateDatabase unable to create database:11
2023-01-20 23:54:24.421 T:721 DEBUG <general>: Mysql rollback transaction

These sort of errors generally occur where MySQL/MariaDB has been upgraded but mysql_upgrade has not been run. I.E., there has been a schema change also. This leaves the binaries upgraded but not necessarily the tables they operate on. Therefore I suggest that if you have upgraded MySQL/MariaDB at any point between using different Kodi versions, that you execute mysql_upgrade from a command line as a user with write access to the data directory (root or the MySQL user usually).

[nitro322]

These sort of errors generally occur where MySQL/MariaDB has been upgraded but mysql_upgrade has not been run. I.E., there has been a schema change also. This leaves the binaries upgraded but not necessarily the tables they operate on. Therefore I suggest that if you have upgraded MySQL/MariaDB at any point between using different Kodi versions, that you execute mysql_upgrade from a command line as a user with write access to the data directory (root or the MySQL user usually).

Thanks.  This was exactly it.  Klojum's post led me to explore that error code 1728 further, where I found this stack overflow post describing a similar problem and recommending the mysql_upgrade.  I have indeed upgraded this database multiple times and didn't realize that was required.  Oops.  Running it seems to have fixed my problem, and I now have a full scan and scrape running.  Will know for certain in a couple hours, but feeling good about it now.

Very much appreciate the tips.  Glad to have this working again.

[black_eagle]

Thanks.  This was exactly it.

Yeah, having lived around here for a while, I've seen this multiple times.  Really, mysql_upgrade should be triggered when the binaries get updated/upgraded but sadly it still remains a manual trigger to run.

[Klojum]

Thread marked solved.