search

mbdavid / LiteDB

LiteDB - A .NET NoSQL Document Store in a single data file
http://www.litedb.org
MIT License
8.51k stars 1.24k forks source link

[BUG] Cannot insert duplicate key in unique index '_id'. The duplicate value is '{"$date":"2006-04-02T07:00:00.0000000Z"}'. #2357

Open dwooten70 opened 1 year ago

dwooten70 commented 1 year ago

Version LiteDB version = 5.0.17 OS = Windows 10 64 bit .NET framework version = .NET Framework 4.8

Describe the bug My database uses a DateTime instead of an auto-numbered ID as the unique primary key. Note: My data is hourly so the DateTime is always unique. I have inserted 6720 records into the database and I add them 20 days (or 480 records) at a time (20 days x 24 hours = 480 records) using InsertBulk. The last record in the database is for "3/22/2006 23:00:00". When I go to insert the records from 3/23/2006 to 4/11/2006, I get the following error message:

Cannot insert duplicate key in unique index '_id'. The duplicate value is '{"$date":"2006-04-02T07:00:00.0000000Z"}'.

Note: I have checked the data thoroughly and this datetime is not in the existing data in the database and it is not duplicated in the bulk data being added.

Code to Reproduce I have attached a very simple Visual Studio project, a LiteDB database and a json file with new data to be added that can be used to replicate the error.

Expected behavior I expected the data in the NewData.json file to be added to the AEBN.db file without error.

Additional context In the sample code, just change the paths to the NewData.json and AEBN.db files to wherever you put them on your computer then you should be able to run the sample program and reproduce the error. LiteDBBugReplicator.zip NewData.zip AEBN.zip

dwooten70 commented 11 months ago

For the developers and those who might encounter a similar issue, I have determined the root cause of this issue. LiteDB seems to have a problem with dates when it comes to daylight savings time (DST). 4/2/2006 was when the shift was made to DST in the year 2006. LiteDB seems to automatically be shifting my times to adjust for DST when my DateTimes are being stored. I see no way to prevent this. My DateTimes are created as "unspecified" kind and I have even tried "UTC" and "local" but none of these resolved the issue. To work around the issue, I am storing the "long ticks" for the DateTime instead of the DateTime itself. This is a little cumbersome as I have to convert then convert back and it makes visualization with LiteDB Studio harder. However, it is a valid work-around to prevent LiteDB from shifting the time for my DateTime values. Hope this helps someone.