Fields too small in MySQL Database (Solution included)

tuxpoldo commented 5 years ago

Bug report

Failure to scrape Series on Systems with MySQL Database

Describe the bug

This behaviour was observed on Systems working with MySQL Database: when trying to scrape some TV series with metadata.tvshows.themoviedb.org the data is not written correctly to the database. This happens with some series like "Game of Thrones".

Expected Behavior

The metadata is scraped correctly

Actual Behavior

A broken (Empty) TV Series is created

Possible Fix

The Kodi log file shows that an error occurs when the information would be stored in the table tvshow:

2019-03-17 23:40:02.140 T:140430353423744   ERROR: SQL: [xbmc_de_videodb116] Undefined MySQL error: Code (1406)
                                            Query: UPDATE tvshow SET c00='Game of Thrones',c01='Seven noble families fight fo
r control of the mythical land of Westeros. Friction between the houses leads to full-scale war. All while a very ancient evi
l awakens in the farthest north. Amidst the war, a neglected military order of misfits, the Night\'s Watch, is all that stand
s between the realms of men and icy horrors beyond.',c02='',c04='7813',c05='2011-04-17',c06=<absolute_gigantic_amount_of_data> ...

Series like "Game of Thrones" are well documented in The Movie Database and users have provided a huge amount of posters, fan art, etc. Because of this, the amount of data returned by the TMDb API is quite big. A huge part of the response will be stored in the field tvshow.c06 - the type of this field is TEXT.

According to the MySQL Documentation, the maximum length of data in a field of type TEXT is 2^16 ( = 65536 Bytes). This seems to be too short for some TV Series.

After changing the type of the field to LONGTEXT everything works fine.

Maybe it is a good idea to check if other fields in other tables may have the same problem.

To Reproduce

Steps to reproduce the behavior:

Configure a Kodi System with MySQL Database (in my case MySQL 5.7)
Use metadata.tvshows.themoviedb.org as TV Series scraper
Try to scrape a TV Series source containing the directory Game of Thrones.

Debuglog

The debuglog can be found here:

MacOS: https://paste.kodi.tv/orufoyiser.kodi
Linux: https://paste.kodi.tv/dupudeveti.kodi

Your Environment

Used Operating system:

[X] Linux
[X] OSX
Operating system version/name: LibreELEC (official): 9.0.1, kernel: Linux x86 64-bit version 4 .19.23
Kodi version: Kodi (18.1 Git:18.1-Leia). Platform: Linux x86 64-bit
Operating system version/name: OS X 10.14.3, kernel: Darwin x86 64-bit version 18.2.0
Kodi version: Kodi (18.1 Git:20190217-8cfdc895f3). Platform: OS X x86 64-bit

note: Once the issue is made we require you to update it with new information or Kodi versions should that be required. Team Kodi will consider your problem report however, we will not make any promises the problem will be solved.

DaVukovic commented 5 years ago

I remember seeing this issue mentioned at the forums some time ago.

If you think you have a solution for that, feel free to PR it ;). Then we could discuss your solution.

MilhouseVH commented 5 years ago

What is the purpose of storing a huge text blob in the database? Is it parsed at a later date? If not then truncate it at some more reasonable length, or don't store it at all.

DaVukovic commented 5 years ago

@Milhouse IIRC that field is for storing the fanart links (thumbnail URLs) and if there are too many (or too long) links and the given value exceeds, you will end up with that error.

I've seen something similar with the Star Wars movies.

tuxpoldo commented 5 years ago

OK - I finally found where the specific table is created:

https://github.com/xbmc/xbmc/blob/c7bac9713a508b54c91e4c57d2d33edc1c94975a/xbmc/video/VideoDatabase.cpp#L130-L137

This seems to me a very generic approach. In order to fix this specific case, it would be enough to change the type of c06 to LONGTEXT.

But this file is not database specific. Currently Kodi natively supports SQLite and MySQL/MariaDB. The implementation in VideoDatabase.cpp uses basic SQL and makes no difference between the two databases. All string fields in the database seems to be created with type TEXT. Unfortunately the size limit of this type is very different between SQLite (See https://www.sqlite.org/limits.html -> 10^9) and MySQL (2^16). And much more unfortunately, SQLite does not know the type LONGTEXT. Because of this it is not possible in my opinion to fix the code without a huge modification (e.g. make the database creation and update maintenance specific to the native database implementation).

I think, that it would be better to let the person responsible of the architecture of the database part to decide how to handle differences in the database engines.

As a workaround I would suggest to provide an SQL-Script that modifies the database (with some ALTER TABLE statements) in order to fix this kind of problems.

tuxpoldo commented 5 years ago

Probably the better solution is not to store such huge amount of data in one single field...

KarellenX commented 5 years ago

Similar report on the forum... https://forum.kodi.tv/showthread.php?tid=340933

yol commented 5 years ago

You can rewrite the SQL in the DB backend. This is already done for other small differences between MySQL and SQLite.

domschl commented 5 years ago

Changing the mySql field tvshow.c06 to LONGTEXT as recommended by @tuxpoldo fixed the scrapper problems for me. It would be nice, if the field uses LONGTEXT for all Kodi installations by default.

jouster1974 commented 5 years ago

Changing the mySql field tvshow.c06 to LONGTEXT as recommended by @tuxpoldo fixed the scrapper problems for me. It would be nice if the field uses LONGTEXT for all Kodi installations by default.

Sorry to jump all over your post...but having recently had a single film fall foul of this issue, and karellen from the Kodi forums kindly point me in the right direction, I am wondering if there is an idiot's guide to changing the filed you've mentioned

I'm using KODI 17.6 and MySQL 5.5 if that makes any difference to you...

Happy to read up and have a tinker if required to get things sorted....I never thought having a single film out of 5000+ not scraping would be such an issue for me

TIA

emdean commented 5 years ago

+1

I just hit this issue. Again it was the thumbnails column "c06" was blown out on the "tvshow" table in MariaDB. I worked around it by changing my information provider for my TV shows (basically changed the data stored in the table to avoid to too long issue). I didn't muck around with the MariaDB structure as I feel this should be managed programatically by Kodi and I don't want the mess things up for it.

Using MEDIUMTEXT would probably be sufficient for MariaDB given that stores 16MB of text. LONGTEXT is designed for 4GB of text which may be overkill.

Klojum commented 5 years ago

Using MEDIUMTEXT would probably be sufficient for MariaDB given that stores 16MB of text. LONGTEXT is designed for 4GB of text which may be overkill.

Kodi shouldn't even need to store all the URL links for posters, fanart, thumbs, etcetera. It only needs to store the graphics that have been selected, or those which are default. Should the Kodi user wish to change that current selection, then the whole list of fanart links can be retrieved from the provider. But storing the whole bucket-load of links to external fanart into the Kodi database seems pretty pointless to me.

rmrector commented 5 years ago

@Klojum agreed. At most cache that list for a bit somewhere else.

KarellenX commented 5 years ago

Reported to MariaDB Developers by forum member sniferx1

Links and reply can be found here... https://forum.kodi.tv/showthread.php?tid=129821&pid=2875797#pid2875797

KarellenX commented 4 years ago

Instructions for users to fix the issue locally as provided by a member https://forums.thetvdb.com/viewtopic.php?p=165908#p165908

1) Download and install Dbeaver - https://dbeaver.io/download/

2) Within DBeaver, set up a "New Database Connection" (in my case it was a MariaDB connection, I filled in my Server Host which is my Unraid server @192.168.0.2, provide root access credentials if required, test and save the connection.

3) Once connected then to your SQL Database, click on SQL Editor/New SQL Editor

4) In the pane that opens on the right hand side, paste in the following commands separately : ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL; ALTER TABLE MyVideos116.movie MODIFY COLUMN c08 mediumtext DEFAULT NULL NULL;

5) Execute the pasted statement by using control + enter (or click the little orange arrow beside your pasted command). That's the database change made

6) Within Kodi, I had to fully refresh the affected source (TV in my case). I did this by viewing the available sources, hitting "change content" on my TV source. I then set it to use TV Maze instead of TVDB. I then clicked ok, and chose "no" when Kodi asked if I'd like to do a library refresh. I then repeated these steps, but changed the source back to using TVDB instead of TV Maze...and then clicked "yes" when Kodi asked about a library refresh - voila, that should be it, everything should scrape now.

Klojum commented 4 years ago

For the record, any MySQL/MariaDB client tool that can make a connection to Kodi's video database, will do. Such as MySQL Workbench, PHPMyAdmin, HeidiSQL, Dbeaver, dbForge, et cetera. It's the database server that has to do the 'hard work'. Kodi's local SQLite database files require a specific SQLite database client tool.

I'm sure the field length workaround for field c06 can be fixed for MyVideos117+. A more elegant solution would be the rewrite of the whole "let's grab every fan art URL" routine, but I understand the implication of that.

Enrico2 commented 4 years ago

Wow I've been trying to figure this one out for hours... Thanks for that sql statement!

klaernie commented 4 years ago

Maybe a wild suggestion, but wouldn't it generally be better from a reliability perspective to split the update statement into multiples? Than the failing update of the c06 column wouldn't be such a huge problem and only lead to missing thumbnails instead of an entirely missing show.

Rouzax commented 4 years ago

Instructions for users to fix the issue locally as provided by a member https://forums.thetvdb.com/viewtopic.php?p=165908#p165908

Download and install Dbeaver - https://dbeaver.io/download/

Within DBeaver, set up a "New Database Connection" (in my case it was a MariaDB connection, I filled in my Server Host which is my Unraid server @192.168.0.2, provide root access credentials if required, test and save the connection.

Once connected then to your SQL Database, click on SQL Editor/New SQL Editor

In the pane that opens on the right hand side, paste in the following command: ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL;

Execute the pasted statement by using control + enter (or click the little orange arrow beside your pasted command). That's the database change made

Within Kodi, I had to fully refresh the affected source (TV in my case). I did this by viewing the available sources, hitting "change content" on my TV source. I then set it to use TV Maze instead of TVDB. I then clicked ok, and chose "no" when Kodi asked if I'd like to do a library refresh. I then repeated these steps, but changed the source back to using TVDB instead of TV Maze...and then clicked "yes" when Kodi asked about a library refresh - voila, that should be it, everything should scrape now.

Will this or can this alteration become an issue when updating Kodi in the future?

onkelbeh commented 4 years ago

Hi,

found this too after some weeks in trouble and 1 hour of log surfing. I can confirm that changing the field type of MyVideos116.tvshow to MEDIUMTEXT solved this for me.

Thanks a lot for your work and this great piece of software.

PS: This occured for me scraping "The Simpsons" from TMDB, using Kodi 18.8 (OS X and OSMC) with MariaDB 10.4.13 on Gentoo Linux.

DaveTBlake commented 4 years ago

What is the purpose of storing a huge text blob in the database? Is it parsed at a later date? If not then truncate it at some more reasonable length, or don't store it at all.

Kodi shouldn't even need to store all the URL links for posters, fanart, thumbs, etcetera. ... But storing the whole bucket-load of links to external fanart into the Kodi database seems pretty pointless to me.

Probably the better solution is not to store such huge amount of data in one single field.

I concer with all the above. The correct fix is to control the size of data that gets written to the db, not to bloat the fields to store meaningless data.

Limits should be applied in core, but since the text is xml simple truncation won't suffice and perhaps easier to simply reject data that is too big. The other obvious place to handle this issue is in the scrapers that creates the list of URLs.

nonobio commented 4 years ago

Hello,

Thanks a lot, it worked for me !

I use Kodi 18.8 on Windows 7 and MariaDB database on Synology NAS and i had this issue.

I used HeidiSQL to execute the code and it worked :+1: Bye

tazeat commented 4 years ago

Ran in to this with The Simpson's as well, it was driving me crazy why I couldn't update the show properly anymore

KarellenX commented 4 years ago

The Movie Database Python... https://kodi.wiki/view/Add-on:The_Movie_Database_Python
TMDb TV Shows ... https://kodi.wiki/view/Add-on:TMDb_TV_Shows https://forum.kodi.tv/showthread.php?tid=351546

Both the above are the new generation of Python scrapers. Both are available from the Official Kodi repository.

These two scrapers limit the number of artwork URL's that are scraped so users do not experience this issue.

thesmiley1 commented 4 years ago

...

Both the above are the new generation of Python scrapers. Both are available from the Official Kodi repository.

These two scrapers limit the number of artwork URL's that are scraped so users do not experience this issue.

Are these poised to be official replacements for would-be-legacy scrapers? Are there any other sources of official information about them?

DaveTBlake commented 4 years ago

Are these poised to be official replacements for would-be-legacy scrapers? Are there any other sources of official information about them?

Yes. https://kodi.wiki/view/Add-on:The_Movie_Database_Python https://forum.kodi.tv/showthread.php?tid=357232

1uknowleast commented 3 years ago

I'm running into this issue with a movie set

What would be the best way to correct it?

KarellenX commented 3 years ago

@1uknowleast

Your post has nothing to do with this bug report.

Please ask your question on the forum.

Do not post your log in the forum Do not provide a snippet which is useless. PROVIDE THE FULL LOG posted at https://paste.kodi.tv/

jbogers commented 3 years ago

@KarellenX , the report of @1uknowleast is very relevant. It is exactly the same issue, but now applies to the 'movie' table instead of the 'tvshow' table. The root cause is the same: The database field is too short and/or Kodi is not limiting the data size it puts into the database.

To fix this, you can use the same fix as the one you posted: https://github.com/xbmc/xbmc/issues/15768#issuecomment-564677726 But instead of running ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL; you (also) need to run: ALTER TABLE MyVideos116.movie MODIFY COLUMN c08 mediumtext DEFAULT NULL NULL;

DaveTBlake commented 3 years ago

@1uknowleast did not provide what version of Kodi he is using, what scraper and version he used or a debug log (which would give both). Instead posted an overly long snippet for a git post. The forum is the better place for that kind of "what can I do about it" post.

The root cause is the same: The database field is too short and/or Kodi is not limiting the data size it puts into the database.

Or that the scraper is not selecting the best but reasonably limited number of images results to return. I know that some scrapers have been updated to do that, rather than just store an unuseable number of URLs

DaVukovic commented 3 years ago

@jbogers You are correct. it's the same (or at least a very similar) issue. But if you read the whole report in here, you might see, that we now have scrapers which limit the amount of data to catch to a reasonable amount. Fiddling around with the database will solve this issue as well and as Karellen mentioned, it has been reported to the MariaDB developers (not sure what the outcome is, tbh).

Anyway...we decided to limit the amount. So, Dave and Karellen, are also correct that this report (which might probably could have been closed already) is kind of useless because of the missing info. And if he's using an older version of the scraper or a version which doesn't limit the amount of data, then one should read this whole section in here.

It's already mentioned how to solve it at the database, it's also already mentioned to use a different scraper and any support on how to do this or that is better discussed at the forums. This section in here is for bug reports only and not to give support how to achieve workarounds (even if they are already mentioned ;) ).

jbogers commented 3 years ago

No problem. I encountered the issue as well (with the same movie, which is what led me here). @DaVukovic I encountered the issue on a fairly standard installation of Kodi 18.9. The only 'custom' thing is the usage of a remote (and shared) MySQL database.

So I do not agree that this issue could already have been closed. It is still present (at least for movies).

As for the complete version information, I tested on 2 machines before I made the changes from the workaround.

Kodi machine 1 (Raspberry Pi 3): LibreELEC (official): 9.2.6 (kernel: Linux 4.19.127) Kodi 18.9 (18.9.0) Git:newclock5_18.9-Leia Scraper: 'The Movie database' 5.2.5

Kodi machine 2 (Intel x64 system): Windows 10 (kernel: Windows NT 10.0.18363) Kodi 18.9 (18.9.0) Git:20201023-0655c2c718 Scraper: 'The Movie database' 5.2.5

1uknowleast commented 3 years ago

Apologies for the poor report. Sdaly, I no longer have any logs to post.

I was using Kodi 18.9 with the The Movie Database Scraper 5.2.5, I had this issue occur on a Windows 10, LibreELEC install, and on a Nvidia Shield. I resolved the issue by switching to the "The Movie Database Python 1.3.1" scraper.

DaveTBlake commented 3 years ago

This problem is not platform specific, but impacts MySQL and MariaDB client/server based setups when scraped with the old XML scrapers e.g. The Movie Database Scraper 5.2.5 and a rediculously large number of image URLs are returned (as can happens with TV shows with many seasons or I guess popular movies )

I resolved the issue by switching to the "The Movie Database Python 1.3.1" scraper.

Yes, the newer Python scrapers limit the number of URLs stored to avoid this issue. And those using the default SQLite databases will not have any problem.

xadox-1st commented 3 years ago

@KarellenX , the report of @1uknowleast is very relevant. It is exactly the same issue, but now applies to the 'movie' table instead of the 'tvshow' table. The root cause is the same: The database field is too short and/or Kodi is not limiting the data size it puts into the database.

To fix this, you can use the same fix as the one you posted: #15768 (comment) But instead of running ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL; you (also) need to run: ALTER TABLE MyVideos116.movie MODIFY COLUMN c08 mediumtext DEFAULT NULL NULL;

Solved it for me, also. Thx for the tip.

realcopacetic commented 3 years ago

I've just switched to a MariaDB set up and am running fresh installs of Kodi V19.0 on all devices with Default TMDB TV Shows scraper and am seeing this issue persisting for The Walking Dead even with the limiters in the new scrapers. Interestingly it occurs even when I use an NFO with no artwork links present. I assumed it would load this first and then skip the online scrape but it looks like it queries online first and then overwrites with the NFO values. I'm guessing unless you set to Local Art Only

KarellenX commented 3 years ago

@realcopacetic

Can you provide a debuglog that captures that issue. The logic that limits the artwork might need a tweak. thanks.

Tartifless commented 3 years ago

Is there a plan to adapt TVDB scrapper also ?

Hitcher commented 3 years ago

Is there a plan to adapt TVDB scrapper also ?

Unlikely from us as that's going to be maintained by TVDB team themselves in the near future.

Jieiku commented 3 years ago

This is still an issue, kodi: Build: 19.1 (19.1.0) Git: 20210509-85e05228b4 Compiled: 2021-05-09

My Scraper is themoviedb.org Tv shows

I could not for the life of me get The Simpsons to properly index. After searching google for: "kodi" "simpsons" "themoviedb" I found this thread.

I did not use any of the tools listed above, my system is a linux system so I just did it from the command line:

sudo mysql
SHOW DATABASES;
USE MyVideos119;
ALTER TABLE MyVideos119.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL;
\q
sudo reboot

After rebooting I found the empty show listing in the Files view for the simpsons and refreshed it, it properly indexed! YAY!

nonobio commented 3 years ago

Hi,

I just upgraded Kodi 18.9 to Kodi 19.1 with a new MariaDB MySQL database: the issue is still here with TMDb TV Shows 1.4.7 scraper; at least with "The Simpsons".

Before applying the fix again, I just tried to scan the TV Show with another scraper: "The Movie Database" 3.5.13, and it worked: artworks are downloaded :).

Bye

KarellenX commented 3 years ago

@nonobio

I just upgraded Kodi 18.9 to Kodi 19.1 with a new MariaDB MySQL database: the issue is still here with TMDb TV Shows 1.4.7 scraper; at least with "The Simpsons".

Can you provide a debuglog so @pkscout and I can check it. pkscout recently updated the scraper to fix that issue, so we need to check why it is still occurring.

I just tried to scan the TV Show with another scraper: "The Movie Database" 3.5.13, and it worked:

It is not possible that it worked with the XML scraper

nonobio commented 3 years ago

Hi,

@KarellenX

To be sure, I deleted the show from my library (content: none, delete from library: yes). Then I deleted all jpeg and nfo files and .actors folders with my "The Simpsons" directory.

And I started Kodi, it found The Simpsons with TMDb TV Shows..., and: it worked! :)

Maybe it was caused by my files presents in my folder following an "separed file" library exportation, or something else...

Bye

gibxxi commented 2 years ago

Pretty miffed by this entire saga. I would of thought (hoped) that the Kodi team would of fixed this between v18 and v19, and it appears, even to my untrained eye, that nothing has been done, when a simple change to the code that creates the database columns to make them "MEDIUMTEXT" instead of "TEXT" would of fixed this for everyone, and been the easiest solution for all concerned, especially since the TVDB scrapers aren't being maintained so a fix via that route isn't going to be forthcoming any time soon.

I can't use "TMDB" or "TVMaze" scrapers for my library as they both have limited coverage for shows I possess. Neither do I want to have to go cherry-picking different scrapers for individual shows to get around that issue. For me: One media category, one scraper.

What causes my frustration more than anything else is that i've been fighting with this for three weekends in a row, using up a massive amounts of my free time, analysing & troubleshooting issues with my MariaDB servers, NAS devices, NFOs, TV Show structure, practically everything connected with Kodi, and getting absolutely nowhere, simply because I didn't know that this issue was still present in Kodi v19 Matrix, or didn't draw the connection - until now.

This could of been mitigated easily. Perhaps there is a reason why the databases cannot be changed, if there is, I'd like to hear it. But changing the scraper(s) that are maintained AND modifying the database code would of covered all the bases in my view. I doubt I'm in a minority with regards having Kodi crash incessantly while scraping TV shows into the library on MySQL/MariaDB servers, judging by some of the forum posts I've read today.

KarellenX commented 2 years ago

@gibxxi

especially since the TVDB scrapers aren't being maintained

I am not aware of this problem with TVDB scrapers. TVDB don't have hundreds of images for a show, nor does the scraper use fanart.tv which adds more artwork links.

Which show(s) is/are causing you the trouble? TVDB are currently developing their own scraper, so now would be a good time to advise them to limit the amount of artwork scraped.

I doubt I'm in a minority with regards having Kodi crash incessantly while scraping TV shows into the library on MySQL/MariaDB servers

I am not aware of this issue causing crashing. The only problem that occurs here is that the tv show refuses to be added to the library. No crashing involved.

Provide a debuglog and lets see if there is something else causing a problem.

As for your other comments, yes on the surface it does seem to be a simple fix, but maybe there are consequences to this change. I have no idea. Maybe a developer can let us know.

gibxxi commented 2 years ago

I did an entire library scan (3 times as I have 3x systems) on SQLite, and had no issues whatsoever. Repeat the same scan on MariaDB and had Kodi CTD 3 times in succession using the same scraper: TheTVDB (new). It might be the case that i'm way off base here, and that my issue is unrelated.

I've just manually changed my databases with the fixes listed above and am attempting to re-scrape again for the umpteenth time via TVDB (I've been hammering their server so much over the past few weeks, they must love me - not). I will post here again my results.

The idea for me is to broadly use MariaDB for all my installs, with local (SQLite) databases as a fallback option, should I need to shut down the MariaDB server(s) for an extended period. While the SQLite Dbs will go out of date fairly quickly due to lack of use, they won't be empty, so a simple update will bring them back up to speed in short order.

I'm also not using old imported databases. This round of troubleshooting is with fresh installs. If I should fail to get to the root cause of this problem, I will submit a debug log as requested. I am not actively seeking support here. I want to identify and solve / work around the issue myself, but thanks for your reply.

Oh and just to be clear - I'm not using any banned addons that could be causing this. I don't use anything of that nature and never have.

KarellenX commented 2 years ago

Ok.

Head over to the forum with the debuglog and start a new thread. Don't want to clutter up this PR with possibly unrelated problem.

Also, consider using nfo files so you are not hammering TVDB with all the API requests.

gibxxi commented 2 years ago

Already using NFOs, but it seems that the scraper is ignoring them in some cases as it's still grabbing the wrong show. But again, that's a separate issue. Signing out for now.

WizardKnight commented 1 year ago

This is still an issue in kodi 20. Can someone update the field type to resolve this issue?

Thorarin commented 1 year ago

I've run into this issue as well with Kodi 20.2 on the movie table (c08 column) in combination with TMDB scraper.

KarellenX commented 1 year ago

Then you are using the wrong scrapers. All the newer Python scrapers have fixed this issue. If you are still having a problem, then you are using one of the older, unmaintained scrapers. TMDB TV Shows or TVDB v4 TheMovie Database Python scraper are the ones you should be using.

xbmc / xbmc