[rickm]

I've been running Kodi for about 8 years, starting with Helix. About a month ago I upgraded from Leia to Matrix, and a few days ago from Matrix to Nexus.

Everything works fine, including two remote Android boxes sharing the Kodi mysql database and accessing video files with smb. However, the MyVideos121.db file isn't in the location ~/.kodi/userdata/database where all the older MyVideosXX.db files are. Also, at some unknown point, the Matrix MyVideos119.db file has been truncated to zero bytes. Yet, all the data for MyVideos119 and MyVideos121 is still visible and looks fine when viewed directly in the mariadb using phpmyadmin. MyVideos121 is nowhere to be found. I even ran a search overnight on my entire filesystem. 

I see directories called, for example, /var/lib/mysql/MyVideos121. It has many files in it, including large .ibd files. The old directories going back to MyVideos107 have .ibd files too, but curiously, these .ibd files have recent timestamps on the as if they were created just recently. Those old databases are still in mariadb. They haven't been dropped.

Upgrading first to Matrix and then to Nexus was done as part of major release upgrades on my Debian linux server (releases buster to bullseye to bookworm). Both php (now v8.2) and mariadb (now v10.11.3) were upgraded to newer versions during these system upgrades.

I don't see any mention of database changes here on kodi.tv, so I'm worried I may have somehow changed the mariadb configuration. I have no idea what changed, or if this is harmful or somehow undesirable in terms of performance, safety, convenience, etc.

Rick

[pkscout]

If you're using the MariaDB option, then the databases are in MariaDB and will be stored however you set that up.  Unless you changed the default MariaDB setup, that is NOT ~/.kodi/userdata/database.  The only reason you'd see files there is if you ever disabled the MariaDB option in advanced settings or have a profile that uses local storage instead of MariaDB.  You've done something to your setup to either not use MariaDB or you somehow broke your MariaDB setup.  A full set of debug logs might help figure out what you did.

[rickm]

Thanks for helping!

I've uploaded a kodi debug log ***here***

I still see the MyVideos121 database getting updated when viewing with phpmyadmin, and I can list it from the command line with mysql -B -s -D MyVideos121 .

The only mention of mariadb in the kodi log that I see is:

2023-08-25 14:11:48.299 T:2138635    info <general>: MYSQL: Connected to version 10.11.3-MariaDB-1
2023-08-25 14:11:48.300 T:2138635    info <general>: Running database version MyMusic82
2023-08-25 14:11:48.300 T:2138635   debug <general>: connect replacing configured host 127.0.0.1 with resolved host 127.0.0.1
2023-08-25 14:11:48.300 T:2138635    info <general>: Running database version MyVideos121
2023-08-25 14:11:48.301 T:2138635    info <general>: Running database version TV40
2023-08-25 14:11:48.301 T:2138635    info <general>: Running database version Epg16`

In my advancedsettings.xml I have:

advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>127.0.0.1</host>
    <port>3306</port>
...etc...

I scrapped some files this morning and went looking for files with new timestamps. I only found:

/var/lib/mysql/aria_log*
/var/lib/mysql/MyVideos121/streamdetails.ibd
/home/rickm/.kodi/userdata/Database/ViewModes6.db, Textures13.db, Addons33.db

I've been looking at all the mysql/mariadb config files I can find and the only odd thing is that in some "50-server.cnf" files (I have various old ones renamed to ".OLDx" still around) the "datadir" is sometimes commented out, sometimes not. It's currently commented out, but the old setting looks like the default anyway: /var/lib/mysql

[pkscout]

I scrapped some files this morning and went looking for files with new timestamps. I only found:

/var/lib/mysql/aria_log*
/var/lib/mysql/MyVideos121/streamdetails.ibd
/home/rickm/.kodi/userdata/Database/ViewModes6.db, Textures13.db, Addons33.db

I've been looking at all the mysql/mariadb config files I can find and the only odd thing is that in some "50-server.cnf" files (I have various old ones renamed to ".OLDx" still around) the "datadir" is sometimes commented out, sometimes not. It's currently commented out, but the old setting looks like the default anyway: /var/lib/mysql

I'm not understanding the problem.  You say you're seeing the changes in PHPmyAdmin, and there is a file being updated in the MariaDB location.  That's how it should be.  The other databases you mentioned as having updates aren't directly related to video or music and are local SQLite databases (the ones in your Kodi directory).  They are used by other parts of Kodi.  When you setup MariaDB, the only databases that are setup there are the MyMusic and MyVideo databases.  All other databases Kodi uses are local, use SQLite, and are stored in your Kodi folders.  That includes Addons33.db, Epg16.db, Textures13.db, TV40.db, ViewModes6.db.  The numbers get bumped when major changes are made during major Kodi version updates.

From what you are saying, it looks like everything is working as expected.

[rickm]

I'm not understanding the problem.  You say you're seeing the changes in PHPmyAdmin, and there is a file being updated in the MariaDB location.  That's how it should be.

The problem is that the MyVideos121.db database file is nowhere to be found on disk. I still have old dB files going back for years, such as MyVideos99, etc. They are still visible in phpmyadmin, as is of course MyVideos121. I even ran a "find / " that found all the old databases but not MyVideos121.db.

Also, somewhere during the upgrade from Matrix to Nexus, the MyVideos119 file got truncated to 0 bytes. I thought I had lost everything but it all seems to still work fine. I can also see MyVideos119 with phpmyadmin, so that data seems to have gotten moved somewhere.

I think there's a command that detects what files a running process has open. I should give that a try when I get home.

[pkscout]

I'm not understanding the problem.  You say you're seeing the changes in PHPmyAdmin, and there is a file being updated in the MariaDB location.  That's how it should be.

The problem is that the MyVideos121.db database file is nowhere to be found on disk. I still have old dB files going back for years, such as MyVideos99, etc. They are still visible in phpmyadmin, as is of course MyVideos121. I even ran a "find / " that found all the old databases but not MyVideos121.db.

If you are using MariaDB there is not, there never has been, nor will there ever be a file called MyVideos121.db.  MariaDB stores the database stuff in a FOLDER called MyVideos121.  You already posted above that you see that.  That is the folder where are the MariaDB information for that database lives.  You will only have a FILE called MyVideos121.db if you are using Kodi's normal SQLite database, and those will be stored in the Kodi userdata/Database folder.

What you see in PHPMyAdmin is the MariaDB stuff.  It has ABSOLUTELY NOTHING to do with the files in your Kodi database folder.  The two are not related in any way.   They are completely separate.

[rickm]

I'm not understanding the problem.  You say you're seeing the changes in PHPmyAdmin, and there is a file being updated in the MariaDB location.  That's how it should be.

The problem is that the MyVideos121.db database file is nowhere to be found on disk. I still have old dB files going back for years, such as MyVideos99, etc. They are still visible in phpmyadmin, as is of course MyVideos121. I even ran a "find / " that found all the old databases but not MyVideos121.db.

If you are using MariaDB there is not, there never has been, nor will there ever be a file called MyVideos121.db.  MariaDB stores the database stuff in a FOLDER called MyVideos121.  You already posted above that you see that.  That is the folder where are the MariaDB information for that database lives.  You will only have a FILE called MyVideos121.db if you are using Kodi's normal SQLite database, and those will be stored in the Kodi userdata/Database folder.

What you see in PHPMyAdmin is the MariaDB stuff.  It has ABSOLUTELY NOTHING to do with the files in your Kodi database folder.  The two are not related in any way.   They are completely separate.

Thanks! This seems to be the answer to my question. Either by design, or something I did, the upgrade of my Debian system from buster to bullseye to bookworm which in turn upgraded Kodi from leia to matrix to nexus also switched Kodi from sqlite to mariadb. 

So, is the large "/var/lib/mysql/MyVideos121/streamdetails.ibd" file actually the disk file where mariadb stores the kodi data?

Lastly, is there any preference or reason for kodi usage to favour either sqlite or mariadb? I haven't noticed a difference. I mostly use kodi from 2 networked android boxes that share the kodi database on the linux server.

[pkscout]

The problem is that the MyVideos121.db database file is nowhere to be found on disk. I still have old dB files going back for years, such as MyVideos99, etc. They are still visible in phpmyadmin, as is of course MyVideos121. I even ran a "find / " that found all the old databases but not MyVideos121.db.

If you are using MariaDB there is not, there never has been, nor will there ever be a file called MyVideos121.db.  MariaDB stores the database stuff in a FOLDER called MyVideos121.  You already posted above that you see that.  That is the folder where are the MariaDB information for that database lives.  You will only have a FILE called MyVideos121.db if you are using Kodi's normal SQLite database, and those will be stored in the Kodi userdata/Database folder.

What you see in PHPMyAdmin is the MariaDB stuff.  It has ABSOLUTELY NOTHING to do with the files in your Kodi database folder.  The two are not related in any way.   They are completely separate.

Thanks! This seems to be the answer to my question. Either by design, or something I did, the upgrade of my Debian system from buster to bullseye to bookworm which in turn upgraded Kodi from leia to matrix to nexus also switched Kodi from sqlite to mariadb. 

So, is the large "/var/lib/mysql/MyVideos121/streamdetails.ibd" file actually the disk file where mariadb stores the kodi data?

Lastly, is there any preference or reason for kodi usage to favour either sqlite or mariadb? I haven't noticed a difference. I mostly use kodi from 2 networked android boxes that share the kodi database on the linux server.

I've never used the Debian build, but I find it kind of hard to believe they would have repackaged it to use a local MariaDB instance.  Normally the only way to use MariaDB is to manually add the entries you have to advancedsettings.xml.  In addition, there is no "upgrade" path from the default SQLite databases to MariaDB.  So unless they also wrote an entire routine to transfer all that data, you would end up with an empty Kodi library.  Again, I find that hard to believe, but you'd have to ask the Debian folks about that.

The way MariaDB stores stuff, every table is a file.  The folder, as I indicated above, is Videodb121.  That's the "database."  streamdetails is one of dozens of tables, so you should be seeing dozens of different files in that folder.

As for advantages, the primary advantage is if you have multiple Kodi instances throughout the house.  With MariaDB they can all share the same database of music and videos.

[rickm]

OK, since I do have multiple instances around the house and mariadb is required for that, then everything is fine and I won't worry about how I got to where I am now.

Thanks for all your help!