Got your message, thanks.
I have been working on trying to figure this out for myself, and while I have not got it working, I have done a lot of trying, and I did manage to isolate a single small album that is having “Soci Exception handled: Cannot convert data to std::tm” errors and will not display the tracklist of the album.
metadata_items;
id 420816, 420817, 420818
but for the life of me, I do not see any invalid dates for these tracks in this table.
| id |
library_section_id |
parent_id |
metadata_type |
guid |
media_item_count |
title |
title_sort |
original_title |
studio |
rating |
rating_count |
tagline |
summary |
trivia |
quotes |
content_rating |
content_rating_age |
index |
absolute_index |
duration |
user_thumb_url |
user_art_url |
user_banner_url |
user_music_url |
user_fields |
tags_genre |
tags_collection |
tags_director |
tags_writer |
tags_star |
originally_available_at |
available_at |
expires_at |
refreshed_at |
year |
added_at |
created_at |
updated_at |
deleted_at |
tags_country |
extra_data |
hash |
audience_rating |
changed_at |
resources_changed_at |
| 420816 |
11 |
420815 |
10 |
com.plexapp.agents.plexmusic://gracenote/track/106062914-FF56DEDBF3D070AFFB51C512BFACC107/106062915-BF2F620786D0EF3B5952337E3ECFD39A?lang=en |
1 |
Thou Art That |
Thou Art That |
By the End of Tonight |
|
|
4300 |
|
|
|
|
|
|
1 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2018-10-27 10:33:18 |
|
2008-05-02 15:59:04 |
2018-10-17 22:05:51 |
2018-10-27 10:33:18 |
|
|
ma%3AchapterSource= |
a9de0721db45c0566a9b828015a74568ff1d0234 |
|
21991982 |
20420998 |
| 420817 |
11 |
420815 |
10 |
com.plexapp.agents.plexmusic://gracenote/track/106062914-FF56DEDBF3D070AFFB51C512BFACC107/106062916-46BAD87D151C33CE9A5FCF3E896ECDA1?lang=en |
1 |
What Must and Shall Be |
What Must and Shall Be |
By the End of Tonight |
|
|
3859 |
|
|
|
|
|
|
2 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2018-10-27 10:33:18 |
|
2008-05-02 15:59:04 |
2018-03-27 18:59:09 |
2018-10-27 10:33:18 |
|
|
ma%3AchapterSource= |
987047234018bf5f433e9d0609f727e82df8c139 |
|
21991983 |
20421000 |
| 420818 |
11 |
420815 |
10 |
com.plexapp.agents.plexmusic://gracenote/track/106062914-FF56DEDBF3D070AFFB51C512BFACC107/106062917-4C9D8D327643F1127168D81C05C3A659?lang=en |
1 |
JQ10 |
JQ10 |
By the End of Tonight |
|
|
3167 |
|
|
|
|
|
|
3 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2018-10-27 10:33:18 |
|
2008-05-02 15:59:04 |
2018-10-17 22:05:51 |
2018-10-27 10:33:18 |
|
|
ma%3AchapterSource= |
d9af91d38916c2114a19ee1d6b21e560cf6a9911 |
|
21991985 |
20421001 |
here is these tracks itunes xml
<dict>
<key>Track ID</key><integer>45613</integer>
<key>Name</key><string>Thou Art That</string>
<key>Artist</key><string>By the End of Tonight</string>
<key>Album Artist</key><string>By the End of Tonight</string>
<key>Album</key><string>My Mom Caught Me in My Room Beat Boxin'</string>
<key>Kind</key><string>MPEG audio file</string>
<key>Size</key><integer>9146368</integer>
<key>Genre</key><string>Math Rock</string>
<key>Total Time</key><integer>340349</integer>
<key>Track Number</key><integer>1</integer>
<key>Rating</key><integer>50</integer>
<key>Year</key><integer>2006</integer>
<key>Date Modified</key><date>2009-01-17T13:22:01</date>
<key>Date Added</key><date>2008-05-02T20:59:04</date>
<key>Bit Rate</key><integer>214</integer>
<key>Sample Rate</key><integer>44100</integer>
<key>Track Type</key><string>File</string>
<key>File Folder Count</key><integer>-1</integer>
<key>Library Folder Count</key><integer>-1</integer>
<key>BPM</key><string>-1</string>
<key>Location</key><string>file://localhost/data/media/audio/MP3/B/By%20the%20End%20of%20Tonight/2006%20-%20My%20Mom%20Caught%20Me%20in%20My%20Room%20Beat%20Boxin'%20%5BMP3%5D/01%20-%20By%20the%20End%20of%20Tonight%20-%20Thou%20Art%20That.mp3</string>
</dict>
<key>45614</key>
<dict>
<key>Track ID</key><integer>45614</integer>
<key>Name</key><string>What Must and Shall Be</string>
<key>Artist</key><string>By the End of Tonight</string>
<key>Album Artist</key><string>By the End of Tonight</string>
<key>Album</key><string>My Mom Caught Me in My Room Beat Boxin'</string>
<key>Kind</key><string>MPEG audio file</string>
<key>Size</key><integer>8820736</integer>
<key>Genre</key><string>Math Rock</string>
<key>Total Time</key><integer>375849</integer>
<key>Track Number</key><integer>2</integer>
<key>Play Count</key><integer>2</integer>
<key>Rating</key><integer>60</integer>
<key>Year</key><integer>2006</integer>
<key>Date Modified</key><date>2009-01-17T13:22:02</date>
<key>Date Added</key><date>2008-05-02T20:59:04</date>
<key>Play Date UTC</key><date>2010-04-29T04:17:19</date>
<key>Bit Rate</key><integer>186</integer>
<key>Sample Rate</key><integer>44100</integer>
<key>Track Type</key><string>File</string>
<key>File Folder Count</key><integer>-1</integer>
<key>Library Folder Count</key><integer>-1</integer>
<key>BPM</key><string>-1</string>
<key>Location</key><string>file://localhost/data/media/audio/MP3/B/By%20the%20End%20of%20Tonight/2006%20-%20My%20Mom%20Caught%20Me%20in%20My%20Room%20Beat%20Boxin'%20%5BMP3%5D/02%20-%20By%20the%20End%20of%20Tonight%20-%20What%20Must%20and%20Shall%20Be.mp3</string>
</dict>
<key>45615</key>
<dict>
<key>Track ID</key><integer>45615</integer>
<key>Name</key><string>JQ10</string>
<key>Artist</key><string>By the End of Tonight</string>
<key>Album Artist</key><string>By the End of Tonight</string>
<key>Album</key><string>My Mom Caught Me in My Room Beat Boxin'</string>
<key>Kind</key><string>MPEG audio file</string>
<key>Size</key><integer>11360256</integer>
<key>Genre</key><string>Math Rock</string>
<key>Total Time</key><integer>442984</integer>
<key>Track Number</key><integer>3</integer>
<key>Rating</key><integer>50</integer>
<key>Year</key><integer>2006</integer>
<key>Date Modified</key><date>2009-01-17T13:22:02</date>
<key>Date Added</key><date>2008-05-02T20:59:04</date>
<key>Bit Rate</key><integer>204</integer>
<key>Sample Rate</key><integer>44100</integer>
<key>Track Type</key><string>File</string>
<key>File Folder Count</key><integer>-1</integer>
<key>Library Folder Count</key><integer>-1</integer>
<key>BPM</key><string>-1</string>
<key>Location</key><string>file://localhost/data/media/audio/MP3/B/By%20the%20End%20of%20Tonight/2006%20-%20My%20Mom%20Caught%20Me%20in%20My%20Room%20Beat%20Boxin'%20%5BMP3%5D/03%20-%20By%20the%20End%20of%20Tonight%20-%20JQ10.mp3</string>
</dict>
I have also tried a couple different ways to find invalid datetime data in the various columns of data, with no apparent luck.
I don’t know if these queries simply don’t work as expected, or that I have not found the right table/columns, or that there is not any invalid timedate data in the actual database and the time conversion errors are happening outside the database.
queries used to (attempt to) identify invalid timedate data
select count(*) from (SELECT "_rowid_",* FROM "main"."media_items" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_items" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_items" WHERE datetime("deleted_at","+0 days") IS NOT datetime("deleted_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_items" WHERE datetime("begins_at","+0 days") IS NOT datetime("begins_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_items" WHERE datetime("ends_at","+0 days") IS NOT datetime("ends_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_part_settings" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_part_settings" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_parts" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_parts" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_parts" WHERE datetime("deleted_at","+0 days") IS NOT datetime("deleted_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_streams" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."media_streams" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_clusters" WHERE datetime("starts_at","+0 days") IS NOT datetime("starts_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_clusters" WHERE datetime("ends_at","+0 days") IS NOT datetime("ends_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime("last_viewed_at","+0 days") IS NOT datetime("last_viewed_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_views" WHERE datetime("viewed_at","+0 days") IS NOT datetime("viewed_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_views" WHERE datetime("originally_available_at","+0 days") IS NOT datetime("originally_available_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("originally_available_at","+0 days") IS NOT datetime("originally_available_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("available_at","+0 days") IS NOT datetime("available_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("expires_at","+0 days") IS NOT datetime("expires_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("refreshed_at","+0 days") IS NOT datetime("refreshed_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("added_at","+0 days") IS NOT datetime("added_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("created_at","+0 days") IS NOT datetime("created_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("updated_at","+0 days") IS NOT datetime("updated_at"));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime("deleted_at","+0 days") IS NOT datetime("deleted_at"));
---
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("originally_available_at")) != datetime(strftime("originally_available_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("available_at")) != datetime(strftime("available_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("expires_at")) != datetime(strftime("expires_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("refreshed_at")) != datetime(strftime("refreshed_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("added_at")) != datetime(strftime("added_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("created_at")) != datetime(strftime("created_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("updated_at")) != datetime(strftime("updated_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_items" WHERE datetime(julianday("deleted_at")) != datetime(strftime("deleted_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime(julianday("last_viewed_at")) != datetime(strftime("last_viewed_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime(julianday("created_at")) != datetime(strftime("created_at")));
select count(*) from (SELECT "_rowid_",* FROM "main"."metadata_item_settings" WHERE datetime(julianday("updated_at")) != datetime(strftime("updated_at")));
One thing that I did find through all my googling is, that your database columns with TYPE: datetime probably should have CHECK constraints put in the schema so that there will be a hard block preventing the addition of any invalid sql timedate data.
CREATE TABLE MyLittleTable (
MyDate CHECK (date(MyDate,'+0 days') IS MyDate)
);
hope the above is helpful, and again thanks for the help.
