Kodi Community Forum

Full Version: Too many fanart images for scrapers: Data too long for MySQL tables (error 1406)
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
I stumbled over a potential bug in the The Movie Database Scraper.
If I add a directory with tv shows to the library, only some of them are scraped.
Shows with many fanart images on tvdb cause the scraper to try to insert all these images into a field of the MyVideos database, which is refused by the mySQL server.

I use Kodi 18.0 with addons up to date: E.g. The Movie Database version 3.1.0. The database is not shared with other clients.

I configured the tv shows folder from the Netflix addon to contain tv shows, and set up the scraper "The Movie Database", which I think was set as default. I activated the settings "get Artwork from themoviedb.org" and "get extra artwork from fanart.tv".
After manually updating the information from the folder of a tv show ("The Walking Dead"), the information window stays empty (no description, no posters or artwork).
In the log I get
Quote:19:03:19.587 T:140703594206144   ERROR: SQL: [MyVideos116] Undefined MySQL error: Code (1406)
Query:  Query: UPDATE tvshow SET c00='The Walking Dead',c01= .... c06='<thumb aspect=\"poster\" preview ... \" preview=\"w780/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg\">original/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg</thumb></fanart>',c12='21682',c13='18',c14='AMC',c15='',c16='', userrating = NULL, duration = NULL WHERE idShow=142
19:05:29.582 T:140703594206144   ERROR: ExecuteQuery - failed to execute query
19:05:29.593 T:140703594206144   DEBUG: Mysql rollback transaction

I shortened the line, but is has over 100000 (one hundred thousand!) characters, since there are numerous preview images listed up. Error 1406 is "Data too long for column".
I uploaded the whole log.

Once I remove the options "get (extra) artwork...", scraping works fine.
This error was also described before for the universal movie scraper in this forum and in a German Kodi forum.
The error occurs only for shows with very much fanart, e.g. "Family Guy", "Rick and Morty", "Walking Dead". Other shows were scraped successfully.

Since no message is given in Kodi, it took me some time to figure this out.
I suggest to reduce the maximum number of fanart images and to check the length of the queries for the scrapers. Since already the scrapers "The Movie Database" and "Universal Movie Scraper" seem to be affected, this could be a general problem.
(2019-02-16, 21:02)schapplm Wrote: [ -> ]I stumbled over a potential bug in the The Movie Database Scraper.
If I add a directory with tv shows to the library, only some of them are scraped.
Shows with many fanart images on tvdb cause the scraper to try to insert all these images into a field of the MyVideos database, which is refused by the mySQL server.

I use Kodi 18.0 with addons up to date: E.g. The Movie Database version 3.1.0. The database is not shared with other clients.

[...]
In the log I get
Quote:19:03:19.587 T:140703594206144   ERROR: SQL: [MyVideos116] Undefined MySQL error: Code (1406)
Query:  Query: UPDATE tvshow SET c00='The Walking Dead',c01= .... c06='<thumb aspect=\"poster\" preview ... \" preview=\"w780/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg\">original/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg</thumb></fanart>',c12='21682',c13='18',c14='AMC',c15='',c16='', userrating = NULL, duration = NULL WHERE idShow=142
19:05:29.582 T:140703594206144   ERROR: ExecuteQuery - failed to execute query
19:05:29.593 T:140703594206144   DEBUG: Mysql rollback transaction

I shortened the line, but is has over 100000 (one hundred thousand!) characters, since there are numerous preview images listed up. Error 1406 is "Data too long for column".  
I recently upgraded my Kodi installation to a centralized file storage and database (Kodi 18.1 and MariaDB in my case). Since Kodi switched to TMDB for TV series, I started to re-scrape my TV series and ran into the same problem with shows like Doctor Who. The show remained empty and I had to dig through the debug logs to find out what was going wrong.

I have found that I can get around the problem by increasing the size of the 'c06' column in the 'tvshow' table above its 'text' type 65535 character limit:
ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL;

I do not know if this might have some side effects in the future when the database is migrated to a newer schema. This currently makes me really hesitant to go through with all the scraping if I may have to do it again at some point.

Cheers,
L.
I've just encounted this problem myself. Thanks @lcg_, your fix works.
I believe the database schema is only updated in major Kodi versions.
So you can export the library to .nfo files before upgrading, then re-scrape (with the fix applied to the new database if necessary).
(2019-02-24, 12:23)lcg_ Wrote: [ -> ]
(2019-02-16, 21:02)schapplm Wrote: [ -> ]I stumbled over a potential bug in the The Movie Database Scraper.
If I add a directory with tv shows to the library, only some of them are scraped.
Shows with many fanart images on tvdb cause the scraper to try to insert all these images into a field of the MyVideos database, which is refused by the mySQL server.

I use Kodi 18.0 with addons up to date: E.g. The Movie Database version 3.1.0. The database is not shared with other clients.

[...]
In the log I get
Quote:19:03:19.587 T:140703594206144   ERROR: SQL: [MyVideos116] Undefined MySQL error: Code (1406)
Query:  Query: UPDATE tvshow SET c00='The Walking Dead',c01= .... c06='<thumb aspect=\"poster\" preview ... \" preview=\"w780/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg\">original/ywHdL2ip9OaClGmHJ7mKb2CPoft.jpg</thumb></fanart>',c12='21682',c13='18',c14='AMC',c15='',c16='', userrating = NULL, duration = NULL WHERE idShow=142
19:05:29.582 T:140703594206144   ERROR: ExecuteQuery - failed to execute query
19:05:29.593 T:140703594206144   DEBUG: Mysql rollback transaction

I shortened the line, but is has over 100000 (one hundred thousand!) characters, since there are numerous preview images listed up. Error 1406 is "Data too long for column".   
I recently upgraded my Kodi installation to a centralized file storage and database (Kodi 18.1 and MariaDB in my case). Since Kodi switched to TMDB for TV series, I started to re-scrape my TV series and ran into the same problem with shows like Doctor Who. The show remained empty and I had to dig through the debug logs to find out what was going wrong.

I have found that I can get around the problem by increasing the size of the 'c06' column in the 'tvshow' table above its 'text' type 65535 character limit:
ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL;

I do not know if this might have some side effects in the future when the database is migrated to a newer schema. This currently makes me really hesitant to go through with all the scraping if I may have to do it again at some point.

Cheers,
L. 
I think I'm having this issue.  I'm using Library Integration Tool to add movies from an addon to the Kodi library.  LIT creates a folder and matching strm file in the kodi library, but when I update the library only some of the movies are added even though there's a folder and strm file for the missing movies.  I've come to figure out that it has to do with the size of the strm files.  If they're over 4KB in size, they are not read correctly by Kodi and therefore not imported.  If I edit the strm file and remove some actor data to bring it below 4KB, then it imports just fine.  How do use that ALTER TABLE command to fix it?
(2019-05-24, 01:46)eric859 Wrote: [ -> ][...] How do use that ALTER TABLE command to fix it?
You need to run this as an SQL command for your database to alter the table. In my case that's MariaDB, in a standard Kodi installation it is a local SQLite. I like to use DBeaver for tinkering with SQL databases, it works for both.

Cheers,
L.
Github report with workaround... https://github.com/xbmc/xbmc/issues/15768
The same bug also affects some block buster movies to be scraped into the library. Example with Universal Movie scraper and Star Wars: Episode III:

Code:
2020-03-25 00:24:27.520 T:6344 ERROR: SQL: [MyVideos116] Undefined MySQL error: Code (1406)
Query: UPDATE movie SET c00='Star Wars: Episode III - Revenge of the Sith',c01='Nearly three years have passed since the beginning of the Clone Wars. The Republic, with the help of the Jedi, take on Count Dooku and the Separatists. With a new threat rising, the Jedi Council sends Obi-Wan Kenobi and Anakin Skywalker to aid the captured Chancellor. Anakin feels he is ready to be promoted to Jedi Master. Obi-Wan is hunting down the Separatist General, Grievous. When Anakin has future visions of pain and suffering coming Padmé\'s way, he sees Master Yoda for counsel. When Darth Sidious executes Order 66, it destroys most of all the Jedi have built. Experience the birth of Darth Vader. Feel the betrayal that leads to hatred between two brothers. And witness the power of hope.',c02='Three years into the Clone Wars, the Jedi rescue Palpatine from Count Dooku. As Obi-Wan pursues a new threat, Anakin acts as a double agent between the Jedi Council and Palpatine and is lured into a sinister plan to rule the galaxy.',c03='The saga is complete.',c05='373343',c06='George Lucas',c08='<thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-53ae8e3b4dc1c.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-53ae8e3b4dc1c.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-55e8d46dc8a59.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-55e8d46dc8a59.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-548cb795a9eaa.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-548cb795a9eaa.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-55d1cdb299a4c.jpeg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-55d1cdb299a4c.jpeg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-52c8d73bdaaa7.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-52c8d73bdaaa7.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5400ca2714a55.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5400ca2714a55.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5b9de3d388792.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5b9de3d388792.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5ac23902d2d54.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5ac23902d2d54.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-53ae8e3b4dc1c.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-53ae8e3b4dc1c.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-55e8d46dc8a59.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-55e8d46dc8a59.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-548cb795a9eaa.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-548cb795a9eaa.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-55d1cdb299a4c.jpeg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-55d1cdb299a4c.jpeg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-52c8d73bdaaa7.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-52c8d73bdaaa7.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5400ca2714a55.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5400ca2714a55.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5b9de3d388792.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5b9de3d388792.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5ac23902d2d54.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5ac23902d2d54.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5229c4131fc78.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5229c4131fc78.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-52d84a00aaee0.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-52d84a00aaee0.jpg</thumb><thumb aspect=\"set.poster\" preview=\"https://assets.fanart.tv/preview/movies/10/movieposter/star-wars-collection-5561ca5e0d33d.jpg\">https://assets.fanart.tv/fanart/movies/10/movieposter/star-wars-collection-5561ca5e0d33d.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-5109f9372ca5e.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-5109f9372ca5e.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-52c7fee5a6d47.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-52c7fee5a6d47.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-52c82e0a33e8a.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-52c82e0a33e8a.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-52c82ff1eeee2.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-52c82ff1eeee2.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-58de004c9c292.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-58de004c9c292.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-52c82cc6e6128.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-52c82cc6e6128.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-55563b5f19acc.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-55563b5f19acc.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-5405f0cf0e6cb.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-5405f0cf0e6cb.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-540671073c2a3.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-540671073c2a3.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-5dfc4e2816e0a.jpg\">https://assets.fanart.tv/fanart/movies/10/moviebackground/star-wars-collection-5dfc4e2816e0a.jpg</thumb><thumb aspect=\"set.fanart\" preview=\"https://assets.fanart.tv/preview/movies/10/moviebackground/star-wars-collection-5db1c37f16ddc.jpg\">https://assets.f

Retrieved info is too long for the database. Solution is to set myvideos116.movie column c08 to "Mediumtext" instead of "Text".
(2020-03-25, 01:54)HeresJohnny Wrote: [ -> ]Retrieved info is too long for the database. Solution is to set myvideos116.movie column c08 to "Mediumtext" instead of "Text".

Note that the bug is only for MySQL and MariaDB DBMS. Default SQLite has no "Mediumtext" nor "Longtext".

scott s.
.
(2020-03-25, 01:54)HeresJohnny Wrote: [ -> ]Example with Universal Movie scraper and Star Wars: Episode III:
Obvious quote is obvious...
(2020-03-25, 18:14)Klojum Wrote: [ -> ]Obvious quote is obvious...

That's why they call me Captain Obvious. Still better than being asked for proof afterwards...
(2019-05-24, 01:46)eric859 Wrote: [ -> ]I think I'm having this issue.  I'm using Library Integration Tool to add movies from an addon to the Kodi library.  LIT creates a folder and matching strm file in the kodi library, but when I update the library only some of the movies are added even though there's a folder and strm file for the missing movies.  I've come to figure out that it has to do with the size of the strm files.  If they're over 4KB in size, they are not read correctly by Kodi and therefore not imported.  If I edit the strm file and remove some actor data to bring it below 4KB, then it imports just fine.  How do use that ALTER TABLE command to fix it?

Did you ever find a workaround for this issue? I have read all related threads on this issue and realized I was actually following you lol. I tried editing the MyVideos116.db to alter column c06 but it was of no use as SQLite does not support the command. I ended up renaming c06 and creating a new column with MEDIUMTEXT but it did not sort the issue. Apparently this issue is only related to mariaDB/MySQL.

Upon further investigation, I found that TEXT actually supports up to 2GB for SQLite so that doesn't seem to be the issue here. At this point, I really have no idea why these .strm files will not scrape. As you mentioned earlier, if you edit to decrease the .strm file size to 4kb or lower, they scrape fine.
(2020-04-30, 13:37)Pug3ter Wrote: [ -> ]Apparently this issue is only related to mariaDB/MySQL.

It is only a MySQL/MariaDB problem. SQLite doesn't know MEDIUMTEXT or other smaller text field types, it only knows TEXT for larger texts.
(2020-04-30, 13:37)Pug3ter Wrote: [ -> ]I really have no idea why these .strm files will not scrape.
Please provide a Debug Log of you trying to scrape these stream files. Also tell us the names of the files you are trying to scrape.
(2019-05-24, 01:46)eric859 Wrote: [ -> ]
(2019-02-24, 12:23)lcg_ Wrote: [ -> ]
(2019-02-16, 21:02)schapplm Wrote: [ -> ]I stumbled over a potential bug in the The Movie Database Scraper.
If I add a directory with tv shows to the library, only some of them are scraped.
Shows with many fanart images on tvdb cause the scraper to try to insert all these images into a field of the MyVideos database, which is refused by the mySQL server.

I use Kodi 18.0 with addons up to date: E.g. The Movie Database version 3.1.0. The database is not shared with other clients.

[...]
In the log I get

I shortened the line, but is has over 100000 (one hundred thousand!) characters, since there are numerous preview images listed up. Error 1406 is "Data too long for column".   
I recently upgraded my Kodi installation to a centralized file storage and database (Kodi 18.1 and MariaDB in my case). Since Kodi switched to TMDB for TV series, I started to re-scrape my TV series and ran into the same problem with shows like Doctor Who. The show remained empty and I had to dig through the debug logs to find out what was going wrong.

I have found that I can get around the problem by increasing the size of the 'c06' column in the 'tvshow' table above its 'text' type 65535 character limit:
ALTER TABLE MyVideos116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL;

I do not know if this might have some side effects in the future when the database is migrated to a newer schema. This currently makes me really hesitant to go through with all the scraping if I may have to do it again at some point.

Cheers,
L.  
I think I'm having this issue.  I'm using Library Integration Tool to add movies from an addon to the Kodi library.  LIT creates a folder and matching strm file in the kodi library, but when I update the library only some of the movies are added even though there's a folder and strm file for the missing movies.  I've come to figure out that it has to do with the size of the strm files.  If they're over 4KB in size, they are not read correctly by Kodi and therefore not imported.  If I edit the strm file and remove some actor data to bring it below 4KB, then it imports just fine.  How do use that ALTER TABLE command to fix it? 

After fighting with MySQL and trying to get "The Big Bang Theory" to import again.  Since the change to TheMovieDB.org, I found that I was unable to import JUST that single show.  All other content works just fine.  After applying the ALTER TABLE command to upgrade C06 to a MediumText, I found that I was able to scrape Season 1, 2, and 12.  So after that I decided to drop the show and alter C06 to LongText and scraped it again.  Success!
(2020-05-08, 03:04)MaestroChinook Wrote: [ -> ]and alter C06 to LongText and scraped it again. 

Type | Maximum length
-----------+-------------------------------------
TINYTEXT | 255 (2 8−1) bytes
TEXT | 65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB
LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB

16 MB for poster URLs isn't enough..?
How many posters are that, that you're never gonna look at?
Pages: 1 2