Kodi Community Forum
MySQL corrupted? - 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 corrupted? (/showthread.php?tid=369575)

Pages: 1 2

MySQL corrupted? - pavichokche - 2022-09-07

I use an always on PC with a Kodi instance that has my library scraped and stored in a mysql database. Other instances of kodi on my local network connect to that database and access the library centrally.
After I updated my main Kodi instance's library to use the TVDBv4 information provider for TV shows I'm having massive delays on my client devices. The information provider change did a library clean and re-scanned all shows. The recently added episodes widget never properly loads in the Confluence/xonfluence skins, and it can take over a minute for my list of shows to load so that I can watch something.

I then reverted the library scraping back to the previous TVDB scraper and the problem persists. I don't know what to do now, do I delete and re-create my library database? I have a debug log but it doesn't seem too useful.

RE: TheTVDB.com Official TV Scraper - Karellen - 2022-09-07

(2022-09-07, 14:39)pavichokche Wrote: The information provider change did a library clean and re-scanned all shows.
I suppose the big question is why you decided to rescrape the library. It is not necessary to rescrape your library when changing scrapers, just answer no when it is offered.

I can't think of a reason why rescraping your library would affect client devices. Did you restart them? Maybe @Klojum might know why.

RE: TheTVDB.com Official TV Scraper - pavichokche - 2022-09-07

(2022-09-07, 21:06)Karellen Wrote:
(2022-09-07, 14:39)pavichokche Wrote: The information provider change did a library clean and re-scanned all shows.
I suppose the big question is why you decided to rescrape the library. It is not necessary to rescrape your library when changing scrapers, just answer no when it is offered.

I can't think of a reason why rescraping your library would affect client devices. Did you restart them? Maybe @Klojum might know why.
Good question. I just assumed that a) it might cause problems once the current version becomes deprecated and b) it might just be more proper to update everything when switching.
I have tried many things, including completely uninstalling clients, using default skin(s), and also undoing the switch to the v4 scraper by switching back to the one I was using previously and re-scraping.
It's very strange. It's like something in the database got corrupted somehow.

RE: MySQL corrupted? - Karellen - 2022-09-07

@pavichokche

I've split you into your own thread as I am fairly certain it is not scraper related.

Can you provide a Debug Log from one of your clients that captures the problems you describe.

And, no, changing scrapers does not affect any operations in Kodi. Scrapers are only used for scraping, they are not active doing anything else that requires you to keep the same scraper. Your library does not depend on the same scraper being active.

RE: TheTVDB.com Official TV Scraper - HeresJohnny - 2022-09-07

(2022-09-07, 21:06)Karellen Wrote: It is not necessary to rescrape your library when changing scrapers, just answer no when it is offered.

I'm a long time user and I haven't heard that before. Indeed I would doubt that's general knowledge. Kodi should not offer to rescrape when changing the scraper then.

However, rescraping the same files with different scrapers may indeed lead to SQL performance problems if the same object remains in the database with several different IDs. That kind of duplication may lead to severe slowdowns in subsequent scraper runs. The only way to fix this currently is to remove the duplicates from several affected tables (a daunting task) or to delete the whole database and start from scratch.

Kodi really needs some work when it comes to removing deprecated database entries but then again, MySQL has never been officially supported.

RE: MySQL corrupted? - Karellen - 2022-09-07

(2022-09-07, 22:06)HeresJohnny Wrote: 'm a long time user and I haven't heard that before. Indeed I would assume that's not general knowledge.
That is surprising coming from your experience. It is in step 7 here... https://kodi.wiki/view/Changing_Scrapers
If you think it needs rewording, feel free to change it, or give me the wording and I will add it.

There is no connection between a scraper and a library entry. This is how Kodi scrapes:
  1. Kodi scans your sources using videoinfoscanner.
  2. When it finds a new movie, it then searches for nfo files and local artwork.
  3. When those are not found, the scraper is activated
  4. The scraper searches for the title and downloads the metadata and passes it onto Kodi
  5. Kodi grabs the data and adds it to the library.
  6. There is no link between a scraper and a library entry. Removing, changing, deleting or adding a new scrapers has absolutely no affect on the library entries.


MySQL corrupted? - HeresJohnny - 2022-09-07

Your experience is certainly larger than mine so I hesitate to contradict you. I would like to point out, though, that changing a scraper and acknowledging the following Kodi prompts will lead to Kodi announcing that the source will be removed and everything scraped again. Of the exact inner workings how much is removed or left behind I have no detailed knowledge.

RE: MySQL corrupted? - Karellen - 2022-09-07

(2022-09-07, 22:44)HeresJohnny Wrote: I would like to point out, though, that changing a scraper and acknowledging the following Kodi prompts will lead to Kodi announcing that the source will be removed and everything scraped again.
That is the case if you choose Yes to Do you want to refresh information for all items within this path?. That source is wiped and scraped again.
Had the OP chosen No, then nothing would have happened to the library and the new scraper would just sits there waiting to be used for the next scrape.

As for MySQL, I am not a user, so I have no experience with it. I don't know what is happening which is why I pinged klojum.
In SQL, all the entries would be removed except from the path and files tables because they would be reused for the "Refresh".

RE: MySQL corrupted? - Klojum - 2022-09-08

IMO it's like what @HeresJohnny said, "Kodi really needs some work when it comes to removing deprecated database entries". Database corruption via data rot is not impossible, certainly when SDcards in Android/RPi-like boxes are involved. Data corruption due to software mishaps is a slim possibility.

Right now, not everything is being removed when deleting a movie or tv show/episode directly from the Kodi GUI, let alone when video entries are also having to be removed with their video files are being deleted manually. Currenly, video database entries are being from only a couple of database tables via 'triggers'. But a lot of junk is being left in other tables, and not all is harmless apparently when for example having to a refresh of a TV show while linked files are still in the path table. I don't know if things already have/will be improved for Kodi 20, but for TV shows, upto 18 SQL tables are involved (excluding the fixed local Textures13 database for the thumbnails cache).

Now, I'm guilty as well for letting things slide when it comes to pushing others (personal life gets in the way, and I'm sadly not a Python/C++ programmer but I have some SQL knowledge) to make Kodi clean up better after itself. But some of these things should be Kodi basics and necessities. Using triggers may be a correct and fast way, but Kodi's current video database layout isn't optimal, so additional cleanup routines are necessary as already indicated for the 18 and more tables. And they should be relatively easy to implement. Also, if the Clean Video Library routine with extra tasks takes say 5-6 times longer but does it more properly, I'm all for it. Then at least the database will be without junk and the thumbnail cache should not grow as much or should even shrink.

I'm sure that fixes for HDR and other new video techniques can take their precious time, but this is about basic data(base) handling. It's not rocket science. And mind you, this is not only a MySQL/MariaDB thing so the 'experimental' part is off the table, this applies just as well to Kodi's default SQLite database files.

RE: MySQL corrupted? - pavichokche - 2022-09-08

Thank you for moving my query to a dedicated place
The debug log was actually linked in the text in my first post. Here it is again, but as I said I don't think it shows much:
https://pastebin.com/pZxiQG9p

RE: MySQL corrupted? - pavichokche - 2022-09-12

So in the end I tried quite a few things, but what I think ended up actually working was deleting (DROP) all the databases that I could from the mysql database that Kodi is interacting with. Dropping just the myvideos119 one didn't work, there would still be VERY noticeable lag in loading the shows list and opening a show/season, once all the videos were scrubbed.

Also, I don't know how, but all of my WATCHED statuses were saved and never lost, so I didn't even need the WatchedList add-on. I even disabled it at one point. Could someone explain how that was, since I would like to have a proper understanding? In the past, like when I first set up this mysql database years ago, I would lose all watched statuses when removing the library from kodi.

RE: MySQL corrupted? - pavichokche - 2022-09-15

It was Trakt. I had set up the Trakt extension and it had all of my watched history. So it automagically was restoring my library watched status every time I blew away and re-created my library. Wonderful stuff

RE: MySQL corrupted? - jbinkley60 - 2022-09-16

(2022-09-12, 15:31)pavichokche Wrote: So in the end I tried quite a few things, but what I think ended up actually working was deleting (DROP) all the databases that I could from the mysql database that Kodi is interacting with. Dropping just the myvideos119 one didn't work, there would still be VERY noticeable lag in loading the shows list and opening a show/season, once all the videos were scrubbed.

Also, I don't know how, but all of my WATCHED statuses were saved and never lost, so I didn't even need the WatchedList add-on. I even disabled it at one point. Could someone explain how that was, since I would like to have a proper understanding? In the past, like when I first set up this mysql database years ago, I would lose all watched statuses when removing the library from kodi.

Since you run an always on PC and share your library across clients, you might be interested in a different way to accomplish many of the same capabilities with less complexity, more automation and a simpler approach to maintaining database integrity.  The Mezzmo solution I am referencing rebuilds your Kodi database each day thus eliminating issues with partial data being left behind in tables and automates deletions from Kodi.  Watched list handling and many other things are all automatic with no other addons required.  You also have the flexibility of clients not all having to be on the same version of Kodi, the ability to rebuild a Kodi client on the fly and so much more.  Performance logs are available to let you know what is going on and track performance should things ever get slow or if you are just curious how things are doing.  Sync logs tell you what is happening real time with all background sync processes and duplicate logging is available if you have multiple copies of the same movie / TV show in your library.  I am just highlighting some of the many features.

Anyway, a bit slightly off topic but just raising awareness of a different approach to this and something which may interest you.  I maintain the addon and have run this solution for years. 

Happy to answer questions.


Jeff

RE: MySQL corrupted? - Klojum - 2022-09-16

So Trakt was the problem, and still the all-curing Mezzo advertisement comes along... This is getting annoying IMO.

RE: MySQL corrupted? - pavichokche - 2022-09-16

(2022-09-16, 11:11)Klojum Wrote: So Trakt was the problem, and still the all-curing Mezzo advertisement comes along... This is getting annoying IMO.
No no, I don't think Trakt caused ANY problems. In fact it was a great convenience. I was just asking a follow-up question as to why/how were my watched statuses persisting after blowing away my whole library, and the answer to that was Trakt. The original problem I think was somehow triggered by the switchover and re-scrape of my library between TVDB and TVDBv4 scrapers. All is resolved now, and honestly seeing how quickly trakt can restore my watched history, in the future I'll just start by blowing away the library and seeing if that helps.

And @jbinkley60 thanks, I'll keep that in mind if my current implementation is no longer satisfactory.