Disappearing Ground Units

[Eretzu]

I am a software developer, but not any kind of expert (and still quite junior).

Opening only one connection that is then reused is should be faster, but not sure if it is that much faster that it would be useful change.

If saving is only time when data is stored to db, are transactions actually needed?
As far as I understand, the point of transactions is to make make a sequence of edits to database atomic and then be reversible if something goes wrong at any point of sequence. But single insert should be atomic by itself so it should not need transaction as there is no worry of other actors.

Of course as I might be wrong, I would be interested on why transactions are needed.

[skoormit]

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?

If these 78 inserts are occurring serially (not in parallel), you aren't facing the problem described here, which occurs when two different transactions want to modify the database at the same time (one transaction has to wait on the other to finish, and it will only wait so long before giving up).

Using a single open/close for a single transaction comprising all 78 inserts would reduce connection overhead (possible significantly), and would make the 78 inserts succeed or fail as a unit (which may not be desired--do you want to rollback all of the changes whenever one of them fails?).

[Steve Walmsley (OP)]

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?

If these 78 inserts are occurring serially (not in parallel), you aren't facing the problem described here, which occurs when two different transactions want to modify the database at the same time (one transaction has to wait on the other to finish, and it will only wait so long before giving up).

Using a single open/close for a single transaction comprising all 78 inserts would reduce connection overhead (possible significantly), and would make the 78 inserts succeed or fail as a unit (which may not be desired--do you want to rollback all of the changes whenever one of them fails?).

Each insert is a separate function and the functions are called in sequence. Unless there is some asynchronous function in SQLite that continues to execute the transaction after I close the DB connection, they will be happening in sequence.

However, the lack of an error is concerning me. I have had database insert failure errors in the past, but never one without an error popup. Also, given there are so many inserts, why is only this one affected? Plus, formations will not be a particularly large table compared to some others. It is very odd, but the only consistent factor seems to be saves after leaving the game open a long time.

[skoormit]

It is very odd, but the only consistent factor seems to be saves after leaving the game open a long time.

Some thoughts:
Does SQLLite have a connection pool? If so, maybe something is timing out and/or limiting the number of connections.

Can you change the order of the inserts? If so, does the error still occur for the same insert (or is it linked to the particular spot in line, like, say, the 53rd insert)?

[Hastermain]

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?

If these 78 inserts are occurring serially (not in parallel), you aren't facing the problem described here, which occurs when two different transactions want to modify the database at the same time (one transaction has to wait on the other to finish, and it will only wait so long before giving up).

Using a single open/close for a single transaction comprising all 78 inserts would reduce connection overhead (possible significantly), and would make the 78 inserts succeed or fail as a unit (which may not be desired--do you want to rollback all of the changes whenever one of them fails?).

Each insert is a separate function and the functions are called in sequence. Unless there is some asynchronous function in SQLite that continues to execute the transaction after I close the DB connection, they will be happening in sequence.

However, the lack of an error is concerning me. I have had database insert failure errors in the past, but never one without an error popup. Also, given there are so many inserts, why is only this one affected? Plus, formations will not be a particularly large table compared to some others. It is very odd, but the only consistent factor seems to be saves after leaving the game open a long time.

Not a programmer, but given that there been a few reports of planets or even entire systems (as in their bodies, but not the fleets in the system, in my case) disappearing, maybe it's more than one that's being affected?

[Kristover]

Ships have disappeared as well.  It happened to me after leaving a game untended for a  couple of hours.  I remember seeing it happen to somebody else in one of the bug threads.

[skoormit]

...the lack of an error is concerning me.

The lack of an error makes me think that the database doesn't think there is an error.
Which makes me think that in these cases you are sending an empty data set to insert.
I'm assuming the program deletes the data already in the table and then inserts all the current data (from the program's internal representation).

If so, what could cause this particular data set (ground units) to be cleared? (Or other data sets, if others sometimes disappear in this manner.)
Inadvertently re-initializing the collection?
Loop iteration off by one?

...the only consistent factor seems to be saves after leaving the game open a long time.

Does that include long-running, active play sessions, or do you mean just a long span of idle time?
I frequently have sessions that span multiple days, with (obviously) lots of very long idle time (10+ hours), and I have not encountered this error.
(That said, I also haven't done any GU stuff in C#, other than making militia to keep commanders busy, and a geo survey deployment.)

[skoormit]

Which makes me think that in these cases you are sending an empty data set to insert.

Or maybe you aren't sending the insert at all?
At a former place of employment, there was a tale of a legendary bug that for quite a long time had flummoxed a dev team using a dynamic SQL tool to build and execute very long SQL statements.
Very occasionally, the SQL statements would only partially complete.
Eventually they discovered that, for some obscure reason, a piece of string concatenation logic would, in rare circumstances, fail to insert a space between something aliased as "s" and the keyword "top". As a result, the word "stop" would be inserted instead, and the parsing engine, naturally enough, concluded that it should stop parsing this particular statement.

[Black]

My longest gamy is over 70 years old (1.7.3). I keep this game open on my computer in the background unattended for longer perionds of time (up to several hours). I did most basic things with ground units (over 700000 tons over several planets in several systems + civilian units on 30+ CMCs), but i did no STO weapons or combat.  And I never encountered this bug.

[Steve Walmsley (OP)]

Please can everyone who has encountered this bug tell me how long their campaign had been running (in game years) when the bug happened.

[Kristover]

In my game where the ships disappeared, I was about 55 years in and was playing pretty loose with ships all over the place.  In the game where my ground units disappeared, I was about 21 years in BUT I was also taking a lot of time to build up a massive ground force and had about 4 divisions of troops with about 12-15 subordinate battalions (my smallest unit) attached. I had JUST created my first set of STO units.

[MarcAFK]

Which makes me think that in these cases you are sending an empty data set to insert.

Or maybe you aren't sending the insert at all?
At a former place of employment, there was a tale of a legendary bug that for quite a long time had flummoxed a dev team using a dynamic SQL tool to build and execute very long SQL statements.
Very occasionally, the SQL statements would only partially complete.
Eventually they discovered that, for some obscure reason, a piece of string concatenation logic would, in rare circumstances, fail to insert a space between something aliased as "s" and the keyword "top". As a result, the word "stop" would be inserted instead, and the parsing engine, naturally enough, concluded that it should stop parsing this particular statement.

Er. Just a shot in the dark but what about STO and P?

[Ametsala]

Please can everyone who has encountered this bug tell me how long their campaign had been running (in game years) when the bug happened. 

Within the first two years.  It's hard to say exactly, but one of the commanders of the disappeared units was first assigned in January of the first year and promoted in September of the second year without being relieved, and then he was assigned in February of the third year, again without being relieved.

I'd been using a lot of 5 second increments due to fighting my unfriendly neighborhood extra terrestrials, though.

[RagnarVaren]

In my game where the ships disappeared, I was about 55 years in and was playing pretty loose with ships all over the place.  In the game where my ground units disappeared, I was about 21 years in BUT I was also taking a lot of time to build up a massive ground force and had about 4 divisions of troops with about 12-15 subordinate battalions (my smallest unit) attached. I had JUST created my first set of STO units.

Now that you mention it, the DB I looked at also had the bug occur with STO units, I can't remember the exact context though. Could be the bug has some connection with STOs where it fails to save any of the new units to the DB?
How exactly does the game save, does it copy the existing save game and then edit the existing entries and add new ones or does it dump the entire memory into a new DB file? If it's the former then it could be that it fails to do add the new entries for some reason perhaps in connection with STOs where after failing to add those entries it just refuses to add any new ones which would also be difficult to spot in practice because you'd have to reload the game to see that the units are missing.

Just brainstorming since I don't know how it's coded.

[Steve Walmsley (OP)]

Apart from system bodies, the game deletes the existing table and inserts the data from the program.

Otherwise, I would have to keep track of every object in the program that was changed at some point. I do that for system bodies, which has already led to several hard to find bugs, so I don't want to replicate that for every table.