[HeresJohnny]

I'm using your great add-on in SQL mode with a central database (MariaDB 104.7). However, whenever I set a movie manually as "unwatched" I get these errors:

xml:
2019-08-02 01:10:59.038 T:18446744073709551614  NOTICE: service.watchedlist: watch_user_changes: movie "Avengers: Endgame (2019)" changed playcount {1 -> 0} lastplayed {"2019-08-02 01:04:14" -> ""}. movieid=8359

2019-08-02 01:10:59.045 T:18446744073709551614  NOTICE: service.watchedlist: watch_user_changes: MySQL Database error (1292 (22007): Incorrect datetime value: '1970-01-01 01:00:00' for column `watchedlist`.`movie_watched`.`lastPlayed` at row 798) while updating movie Avengers: Endgame (2019)

2019-08-02 01:10:59.045 T:18446744073709551614  NOTICE: service.watchedlist: Notification. Watchedlist: Error: Writing the WL-database file (1292 (22007): Incorrect datetime value: '1970-01-01 01:00:00' for column `watchedlist`.`movie_watched`.`lastPlayed` at row 798)

Looks like the date format/value is not accepted by the DB. The error is something akin to https://bugs.mysql.com/bug.php?id=34214
If you reset the date/time to 1970-01-01 02:00:00 instead it will work.

[schapplm]

This looks very similar to a post from two years ago. Maybe the solution with the "strict mode" is also possible with MariaDB?
Also the reply to my github ticket on that matter suggested setting as watched again to resolve the issue.
I think the core of the problem is that the system time and/or timestamps of files is wrong. This should be fixed first.

EDIT: I was able to resolve the issue, at least with a workaround, by disabling strict mode in MySQL. Apparently strict mode is now implemented by default in MySQL Server 5.7. Disabling strict mode involved removing STRICT_TRANS_TABLES from the line
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" in my.ini

I added this as an issue on github and will try to reproduce and fix this. I am not sure why the timestamp is invalid, though. It comes from the Kodi database MyVideos/files/lastPlayed which has text as data format in mySQL (and not timestamp, for some reason). The value '1970-01-01 11:00:00' looks valid. A workaround for now could be to set the lastPlayed entry to NULL. 

[HeresJohnny]

I'm not sure what to make of your answer. However, I checked that system times are correct on the client and server computer and that file date is also within normal range.

[schapplm]

At some point, the wrong value of "1970-01-01 01:00:00" got into your Kodi database. Can you try to fix this in your Kodi database?
I don't think it makes sense to fix this in the addon because the error already happened before.
If you use phpmyadmin for your MariaDB, you can go to the database "MyVideosxxx" (with highest number xxx), table "files" and change the values for column "lastplayed" to valid ones.
If they are too many, try an SQL query like "UPDATE `lastplayed` NOW() WHERE `lastplayed` LIKE 1970-01-01 01:00:00 (you have to look for the correct syntax).

[HeresJohnny]

That seems to have fixed it, thanks!

[Calimero Oeuf]

This addon looks excellent but it doesn't fully working for me !

My database contains 597 movies
Watched : 379 movies
unwatched : 218 movies

Watchedlist only find 190 watched movies
I checked watchedlist.db with SQLiteDatabaseBrowserPortable and there are only 190 movies.
Where are the 189 others ?

I have some missings actors and moviedb notes and others and I can't refresh my movies until Watchedlist doesn't backup all movies correctly.

[Karellen]

@Calimero Oeuf

How did you originally add the movies to Kodi- nfo files or kodi scrapers?

[Calimero Oeuf]

I scraped all movies via xbmc long time ago then rescrapped them via kody.
andrecently i had to backup in nfo files to restore for windows and raspberry pi4

[Karellen]

For one of those movies that is not in the WatchedList, can you copy the nfo file contents to Kodi Paste Site, save and provide the link

[Calimero Oeuf]

Here are 2 watched movie not in watchedlist
https://paste.kodi.tv/ekohaceqab
https://paste.kodi.tv/lenorufuhi

[Karellen]

@Calimero Oeuf

Thanks. Looks like you do not have proper IMDB ID's for those two movies. You have...
<uniqueid type="unknown" default="true">1250777</uniqueid>

But you should have...
<uniqueid type="imdb" default="true">tt1250777</uniqueid>

I think TinyMediaManager used to do that.

You will need to re-scrape those movies to download the proper ID's (using the Kodi scrapers) or you could try Light IMDB add-on... https://forum.kodi.tv/showthread.php?tid=316342 If you use this, then in the settings page for the addon, ensure you enable "Set IMDB ratings as Default" or similar wording.

[Calimero Oeuf]

OK thanks, I will try .
I think I will have plenty of hours of re-scrap

it would have been better if watchedlist only use <title></title> <playcount></playcount> and <lastplayed></lastplayed>

[Karellen]

it would have been better if watchedlist only use <title></title> <playcount></playcount> and <lastplayed></lastplayed>

I don't think you really thought that through properly.

What would you do in this case...
https://www.themoviedb.org/movie/381288-...uage=en-US
https://www.themoviedb.org/movie/409583-...uage=en-US
https://www.themoviedb.org/movie/425636?language=en-US
https://www.themoviedb.org/movie/407223-...uage=en-US
and there are about another 20 to add to that list.

[schapplm]

I also thought of using "<title> (year)" as a unique id (which of course fails at the example above).
Then the language of the movie would decide about watched/unwatched. If I scrape in German, German titles are used, but sometimes the scraper falls back to English.

[Calimero Oeuf]

I understand but I only want to backup watched states and watched date from my database only (only local information) without checking other options

watchedlist database could be :
movie 1 , watched, date
movie 2 , watched, date
movie 3 , watched, date

and that's all

tested Light IMDb Ratings Update 4.0.0. but it doesn't update imdb id