SQLite丢失了它的数据 [英] SQLite loses its data

问题描述

我使用SQLite3.0数据库创建了.Net C＃软件。在软件中，每天几乎每分钟插入一次数据。但非常罕见的时期（大约一个月一次），它失去了它从未有过的数据。



我的所有表都有标识列。为了检查丢失，我在每个表中写入新行的插入时间。当损失发生时，我观察到一小时的行丢失了。但是没有跳过身份值并继续进行。我检查了我的交易，但他们没事。



程序启动时创建并打开了一个连接。并且该连接在整个运行时使用而没有关闭并重新打开。在运行时，有许多数据库操作，如插入，更新，删除，选择。



这可能是损失的原因吗？

我应该为每个数据库操作打开和关闭连接吗？

解决方案

如果您的标识值没有显示间隙，则不会插入和删除行 - 这给我们留下了三个选择：



1）您的申请在缺失期间没有收到数据，因此无需插入任何内容。

2）您的应用程序没有尝试插入数据。

或

3）该小时的数据在某种程度上是错误的，SQLite拒绝它。



如果错误发生时没有任何与日期时间组合相同的东西（我假设你已经看过那个）那么你需要开始锻炼在什么阶段数据丢失。首先记录正在发生的事情：您需要数据（以及大量数据）来查找间歇性问题。



我首先要添加一些内容来记录原始输入数据 - 附加的文本或二进制文件应该没问题（以及一种机制，以便您可以丢弃不包含丢失时段的数据，以防止日志过满）

然后是你想要输出到SQL的数据的日志。

最后进行一次很好的检查以确保你没有吞下任何可能提供线索的异常，如果你也记录了那些（和我会记录你遇到的每个例外是安全的一面）



为每个日志条目添加一个时间戳是个好主意！



一旦你的日志至少覆盖了一个缺失期，你可以使用它们来识别数据丢失的最佳阶段 - 这应该可以帮助你定位问题。可能需要几次运行才能获得正确的信息，每次都要集中精力解决它丢失的问题 - 间歇性的问题从来都不容易找到！



好运气！

I created .Net C# software using SQLite3.0 database. In the software, data is inserted almost per minute, every day. But very rare times (about once in a month), it loses its data like it never was.

My all tables have identity columns. To check the loss, I writes inserted time of new row in every table. When the loss occurred, I observed rows of an hour was lost. But the identity values were not skipped and continued finely. I checked my transactions but they were fine.

There is one connection created and opened when the program started. And that connection is used whole runtime without close and reopen. At runtime, there are many db actions such as insert, update, delete, select.

Can it be a reason for the loss?
Should I open and close a connection for every db actions?

解决方案

If your Identity values don't show gaps, then rows aren't being inserted and deleted - which leaves us three options:

1) Your application is not receiving the data during the "missing" period, so there is nothing to insert.
2) Your application is not trying to insert the data.
or
3) The data for that hour is wrong in some way and SQLite is rejecting it.

If there is nothing that is common to the date time combination when the errors occur (and I assume you've looked at that) then you need to start working out at what stage the data is getting lost. Start by logging what is occurring: you need data (and lots of it) to find intermittent problems.

I'd start by adding something to record the "raw" input data - a text or binary file that gets appended should be fine (and a mechanism so that you can discard the data for days which don't contain missing periods to prevent the log getting too full)
Then a log of the data you are trying to output to SQL.
And finally a good check to make sure you aren't swallowing any exceptions that might be giving clues if you logged that as well (and I'd log every exception you catch to be on the safe side)

Adding a timestamp to each log entry is a damn good idea!

Once you have the logs covering at least one "missing" period, you can use them to identify at least at what stage the data is going missing - which should help you "target" the problem. It may take a couple of runs to get the right information, focussing in each time to work out where it's being lost - intermittent problems are never easy to find!

Good luck!

这篇关于SQLite丢失了它的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！