Disappearing Ground Units

[Bughunter]

Second Foundationer - As I understood from previous reports the missing objects were gone from the db. If it is as you say you may have found a different bug and should post the db and exactly which units doesn't load.

[skoormit]

Well, add this to the pile of clues: my fuel keeps disappearing from Luna.
I moved all my shipyards to Luna, as well as a refuelling station.
Then I used a tanker to transport several million units of fuel to Luna.

Sometime later, I built a ship and was told it had no fuel.
I looked at the Luna colony summary. Zero fuel available.

This has happened several times now. I can't figure out what precedes it, though I do often go a long time between saves, with long stretches of idle time.

I'm in year 12.
I haven't noticed anything else missing.

[Froggiest1982]

Well, add this to the pile of clues: my fuel keeps disappearing from Luna.
I moved all my shipyards to Luna, as well as a refuelling station.
Then I used a tanker to transport several million units of fuel to Luna.

Sometime later, I built a ship and was told it had no fuel.
I looked at the Luna colony summary. Zero fuel available.

This has happened several times now. I can't figure out what precedes it, though I do often go a long time between saves, with long stretches of idle time.

I'm in year 12.
I haven't noticed anything else missing.

I saw a fuel bug on the bug posts related to missing MSP to replenish ships that causes the lost of fuel. Might be that? Steve fixed it already for 1.9 release

[Steve Walmsley (OP)]

if you took out the return from the inner catch block, then it would have the advantage that if it fails to insert any single record, then only that one record is lost. If you logged the information about the failing record(s), you could track down the bug more easily.

Yes, good point. That was my intention - copy paste problem

[Steve Walmsley (OP)]

Well, add this to the pile of clues: my fuel keeps disappearing from Luna.
I moved all my shipyards to Luna, as well as a refuelling station.
Then I used a tanker to transport several million units of fuel to Luna.

Sometime later, I built a ship and was told it had no fuel.
I looked at the Luna colony summary. Zero fuel available.

This has happened several times now. I can't figure out what precedes it, though I do often go a long time between saves, with long stretches of idle time.

I'm in year 12.
I haven't noticed anything else missing.

I saw a fuel bug on the bug posts related to missing MSP to replenish ships that causes the lost of fuel. Might be that? Steve fixed it already for 1.9 release

Yes, missing fuel bug happens when you are out of MSP and try to replenish - fixed now.

[SpikeTheHobbitMage]

One thing I'd like to point out (and I'm sorry if I'm getting annoying) is that the delete operation is outside of the transaction.  This means that if the insert is interrupted and gets rolled back, the delete still gets committed.

I'd move the AuroraDB.Close(); to the finally section of the try/catch

Shouldn't the 'using' clause take care of that?

Good advice is never annoying

Now I am torn. If I prevent the delete then I could end up with a table that is out of sync with all the others, which might cause harder to find bugs. With the try/catch on the record level, would I not be better deleting and then only losing any individual records that fail?

Then I shall endeavor to give good advice. 

You will need to fully suppress any row-level exceptions unless you intend to abort the whole save, because the using clause on the transaction will rollback if it sees one.  Returning from inside the loop has the same effect.  Maybe log those errors to a text file instead?  Remember to open in append mode. Edit: Actually, just remove the return after showing the popup so the save can continue.

Another issue is that if a non-recoverable error does occur or Aurora crashes during the save, only the current table gets rolled back.  That is guaranteed to leave the database in an inconsistent state with the current setup.  The solution to that problem is to start the transaction in the function that calls the save functions.  Just pass the connection in as an argument so they can all share it.  This has the added bonus of speeding up saves by a factor of about 7x.

[Graymane]

I haven't had this happen to me but I just saw something occur when I saved a game that had been running all night and thought it might be pertinent.

After I clicked save there was a point in time where I could close the game window yet the save hadn't completed yet.   This in and of itself may not be a problem with the way SQLite works in C#, but I have to ask, are you using multiple transactions?  If so, is the ground unit data being saved last? 

If the answer to both of those questions is true, then the following might be relevant:

"In SQLite, only one transaction is allowed to have changes pending in the database at a time.  Because of this, calls to BeginTransaction and the Execute methods on SqliteCommand may time out if another transaction takes too long to complete.  (Microsoft C# docs)"

Hope this helps,

PS: If this reply is unwelcome, please accept my apologies.

I am using one transaction for each of seventy-eight different table inserts.  The ground formations aren't last but they are in the last ten so you might be on to something. 

I open a connection and then close for each table as well.  Am I better using a single open/close and a single transaction for all inserts?

Hi Steve, I am most definitely not a C# or Sqlite or linq expert, but what is the intention of opening and closing a connection every time?  In other languages/DBs it is common to use connection pooling.   Second, is your intention that each of those 78 inserts are independent from each other?  If one fails should they all fail or not?  I would group those 78 into groups that must all succeed or fail together.   Otherwise you can leave a DB in an inconsistent state.   In a regular DB setup, that would be much faster as well.   I assume you are doing an in-memory DB or at least only your aurora process is updating it.   If that is the case, using connection pooling and optimizing your transaction might not have much performance impact.   I'd profile it.

[Steve Walmsley (OP)]

You will need to fully suppress any row-level exceptions unless you intend to abort the whole save, because the using clause on the transaction will rollback if it sees one.  Returning from inside the loop has the same effect.  Maybe log those errors to a text file instead?  Remember to open in append mode.

Another issue is that if a non-recoverable error does occur or Aurora crashes during the save, only the current transaction gets rolled back.  That is guaranteed to leave the database in an inconsistent state with the current setup.  The solution to that problem is to start the transaction in the function that calls the save functions.  Just pass the connection in as an argument so they can all share it.  This has the added bonus of speeding up saves by a factor of about 7x.

OK, I need to clarify my understanding here

Let's assume that as suggested the transaction is setup in the function that calls all the separate save function. BeginTransaction needs the DB connection, so that would also need to be in the 'master save' function. The various SQLiteCommands for the deletes and the insert also need the DB connection but that could be passed to each function.

So it would look like

Using Connection
{
    Using Transaction
    {
         SaveFunction #1 (Connection)
        {
             Delete Table
             SQLiteCommand
             {
                   Insert Rows & log errors       
              }
         }

         SaveFunction #2 (Connection)
        {
             Delete Table
             SQLiteCommand
             {
                   Insert Rows & log errors   
              }
         }
    }
}

My understanding from what you described is that in this scenario, either the whole save is successful or the save doesn't happen at all. Any error for any record in any table prevent the save taking place, although I would retain the error log of the failed save. Is that correct?

[Steve Walmsley (OP)]

Hi Steve, I am most definitely not a C# or Sqlite or linq expert, but what is the intention of opening and closing a connection every time?  In other languages/DBs it is common to use connection pooling.   Second, is your intention that each of those 78 inserts are independent from each other?  If one fails should they all fail or not?  I would group those 78 into groups that must all succeed or fail together.   Otherwise you can leave a DB in an inconsistent state.   In a regular DB setup, that would be much faster as well.   I assume you are doing an in-memory DB or at least only your aurora process is updating it.   If that is the case, using connection pooling and optimizing your transaction might not have much performance impact.   I'd profile it.

That's OK, this is my first time using SQLite   As usual, I write these programs to learn new stuff.

The different functions were added over time rather than being part of an original, thought out architecture. So yes they were intended to be separate, but that was based on the knowledge I had at the time. This discussion might change how I handle it.

I don't have an in-memory DB as such. I load the data into collection hierarchies because there is a lot more in the program than just the database fields. Then I covert back to the relational structure for saving.

[Graymane]

Let me give a simple example, if you have a function that saves fleets and another that save ships and there are links between the 2, i. e. , the ships have a fleet id and the fleet have ship ids and you have separate transactions, then if saved the fleet successfully but the ships save fails, it can cause problems.   In that case, you could end up with your fleets pointing to ships that don't exist or vice versa.   If they were all in the same transaction, you avoid that problem and they are all successful or fail as a group.

[Steve Walmsley (OP)]

Let me give a simple example, if you have a function that saves fleets and another that save ships and there are links between the 2, i. e. , the ships have a fleet id and the fleet have ship ids and you have separate transactions, then if saved the fleet successfully but the ships save fails, it can cause problems.   In that case, you could end up with your fleets pointing to ships that don't exist or vice versa.   If they were all in the same transaction, you avoid that problem and they are all successful or fail as a group.

Yes, I am aware of that issue. The load handles it by checking for links before loading anything, so the program doesn't end up with conflicting information once loaded. A ship won't load unless the parent fleet is already loaded for example, or a fleet won't load unless the parent race and current system are already loaded. The load order is set up to check all those dependencies. However, that doesn't solve the problem of the reason something failed to save in the first place.

I am a little nervous of getting into a situation when a player can't save anything at all because of a problem, even a minor issue that won't affect anything significant. He can't play the game and there is no db to check post-error. It may be better to just fail the individual problem records and highlight those problems.

[SpikeTheHobbitMage]

You will need to fully suppress any row-level exceptions unless you intend to abort the whole save, because the using clause on the transaction will rollback if it sees one.  Returning from inside the loop has the same effect.  Maybe log those errors to a text file instead?  Remember to open in append mode.

Another issue is that if a non-recoverable error does occur or Aurora crashes during the save, only the current transaction gets rolled back.  That is guaranteed to leave the database in an inconsistent state with the current setup.  The solution to that problem is to start the transaction in the function that calls the save functions.  Just pass the connection in as an argument so they can all share it.  This has the added bonus of speeding up saves by a factor of about 7x.

OK, I need to clarify my understanding here

Let's assume that as suggested the transaction is setup in the function that calls all the separate save function. BeginTransaction needs the DB connection, so that would also need to be in the 'master save' function. The various SQLiteCommands for the deletes and the insert also need the DB connection but that could be passed to each function.

So it would look like

Using Connection
{
    Using Transaction
    {
         SaveFunction #1 (Connection)
        {
             Delete Table
             SQLiteCommand
             {
                   Insert Rows & log errors       
              }
         }

         SaveFunction #2 (Connection)
        {
             Delete Table
             SQLiteCommand
             {
                   Insert Rows & log errors   
              }
         }
    }
}

My understanding from what you described is that in this scenario, either the whole save is successful or the save doesn't happen at all. Any error for any record in any table prevent the save taking place, although I would retain the error log of the failed save. Is that correct?

Exactly correct.  A transaction should always leave the database in a consistent state.  The other more complicated option is to try to recover failed rows, but again with the same goal.  Edit: One thing to check here is that C# isn't issuing a rollback if an insert fails.  If it does, the solution is to wrap each insert in a savepoint, but I'm not quite certain how to do that in C#.  I'll need to get back to you if that is case.

One other thing to check:  Does that popup in the middle of the insert loop actually work, or does it hang?  If it hangs, that could explain the problem people reported of saving, leaving, and coming back to find Aurora frozen with a broken database.

[Steve Walmsley (OP)]

One other thing to check:  Does that popup in the middle of the insert loop actually work, or does it hang?  If it hangs, that could explain the problem people reported of saving, leaving, and coming back to find Aurora frozen with a broken database.

Based on the current code, not the proposed code, if there is an error in an object such as a null value, an error pops up and the transaction is aborted. Because the transaction is at the table level, the data in the target table is deleted and no records are inserted. The program carries on to the next table.

I've always had that happen for every insert error over several years of dev work.

[SpikeTheHobbitMage]

One other thing to check:  Does that popup in the middle of the insert loop actually work, or does it hang?  If it hangs, that could explain the problem people reported of saving, leaving, and coming back to find Aurora frozen with a broken database.

Based on the current code, not the proposed code, if there is an error in an object such as a null value, an error pops up and the transaction is aborted. Because the transaction is at the table level, the data in the target table is deleted and no records are inserted. The program carries on to the next table.

I've always had that happen for every insert error over several years of dev work.

Fair enough, though I'm still concerned about whatever is causing that hang.  Would Aurora be able to recover if the bad rows were stripped out without wiping the whole table?

Edit:  If you do go with dropping rows, maybe give the user the option of aborting the save and quitting so they can resume from their last good savepoint.

[Omnivore]

The one thing that seems clear about this issue is that you need more information to track down the root cause.  Would you consider adding error logging to the next release so that you would have a concise and accurate report on what occurred around the time of the error? 

Part of the problem in a bug of this nature is human perception and rather coarse capabilities, a key clue might easily go unnoticed or be forgotten, the granularity of the snapshots you are able to look at (before and after databases) is evidently insufficient in this case and could possibly be likewise lacking in tracking later issues.

I'm retired so not very active but I spent some time talking to a few friends who are still on the job and there was a unanimous consensus on the need for logging in cases like these.

Hope this helps