Server closes when attempting to update databases (Upgrade)

[PrincessNoodlelimbs]

Hi all

 

I'm having an issue with Upgrading to the latest (and several of the previous) version of Intersect. I'm trying to Upgrade from 0.6.2.460 to the latest version (0.7.1.98) - although once I encountered this error I tried the three versions previous to it, too, and had the same problem.

 

The problem description is pretty simple. I make a backup, create a new folder, drop the old folders into the new folder. Then I drop the Upgrade folders into the folder and select Replace the old files, just like any other Upgrade I've done in the past. It's always worked before. When I start up the new server, I get the usual "Your databases need to be updated" message, and I type READY and hit Enter. The server window displays the "Please Wait" message, pauses for a few seconds, then closes. That's it. When I try again, same thing except it closes immediately instead of pausing and displaying the "Please Wait" message. 

 

So basically, the server tries to update the databases as usual, but crashes pretty much immediately. This happens every time I try it.

 

Here's the server log file: https://www.ascensiongamedev.com/resources/filehost/d917915f6127922d8bae3e98438daebd.log

Edited January 24, 2023 by PrincessNoodlelimbs
Added latest server version

[panda]

Being completely honest I'm going to need to run the upgrade on your DB through the debugger because while I know what is failing I do not know why, but I also don't have time to do that right now (since I can't even build B7.1 on my computer right now, I will need to fix that first).

As an interim solution/test can you try upgrading to 0.7.1.74-beta instead? The log is complaining about something introduced in .75-.77 so I would expect .74 to work (or produce a different error).

Also just to be sure, have you only ever used the stock builds that we provide, or have you made source code modifications?

[PrincessNoodlelimbs]

Hey, happy to say that upgrading to 0.7.1.74-beta works fine. No server crash issues. 

 

And yeah, I've only ever used stock builds. 

[panda]

14 hours ago, PrincessNoodlelimbs said:

Hey, happy to say that upgrading to 0.7.1.74-beta works fine. No server crash issues. 

 

And yeah, I've only ever used stock builds. 

 

Can you check if you can upgrade beyond .74 now? I wonder if the way migrations are applied that a straight upgrade from 0.6 can't go to .77+.

[PrincessNoodlelimbs]

Tried upgrading to .98 from .74, server crashes on updating database again.

[panda]

18 minutes ago, PrincessNoodlelimbs said:

Tried upgrading to .98 from .74, server crashes on updating database again.

Can you upload the logs? (If they don't tell me enough I may also end up asking you to DM me a copy of your DBs but uploaded via something that allows you to delete the file, which the AGD file host does not.)

[PrincessNoodlelimbs]

Sure. Here's errors-Intersect Server.log: https://www.ascensiongamedev.com/resources/filehost/a1f6b517f3ddba2fcf25fd8e278dc126.log

 

And here's playerdbIntersect Server-2023_01_25-11_26_07_738.log: https://www.ascensiongamedev.com/resources/filehost/5e0807523b2ab6c09ad0b3fe576e11ab.log

 

I would upload Intersect Server-2023_01_25-11_26_07_738.log, but literally all it says is "[Error] Received unhandled exception from System.Threading.Thread."

[panda]

2 hours ago, PrincessNoodlelimbs said:

Sure. Here's errors-Intersect Server.log: https://www.ascensiongamedev.com/resources/filehost/a1f6b517f3ddba2fcf25fd8e278dc126.log

 

And here's playerdbIntersect Server-2023_01_25-11_26_07_738.log: https://www.ascensiongamedev.com/resources/filehost/5e0807523b2ab6c09ad0b3fe576e11ab.log

 

I would upload Intersect Server-2023_01_25-11_26_07_738.log, but literally all it says is "[Error] Received unhandled exception from System.Threading.Thread."

Ok, unfortunately the logs aren't giving me a hint as to why the SQLite migration is failing on your PlayerDB.

If you could send me a copy via a more private upload (honestly if you wanted you and the playerdb is under 20MB you could use email and send to my public email listed on GitHub, [email protected]) I can see if I can figure out what is going wrong.

[PrincessNoodlelimbs]

Silly question but it's been a while since I did anything with the database - you're looking for playerdata.db right? And do you want the one from the old install, or the new game file folder where I dropped the upgrade files? If so I'll go ahead and email it to you. (Sorry for the delay, a bunch of stuff happened over the past week including getting married so needless to say I was fairly busy)

Edited February 4, 2023 by PrincessNoodlelimbs
Clarification

[panda]

14 hours ago, PrincessNoodlelimbs said:

Silly question but it's been a while since I did anything with the database - you're looking for playerdata.db right? And do you want the one from the old install, or the new game file folder where I dropped the upgrade files? If so I'll go ahead and email it to you. (Sorry for the delay, a bunch of stuff happened over the past week including getting married so needless to say I was fairly busy)

Congrats!

I need the latest playerdb that isn't working when you try to upgrade it (so from 0.7.1.74) and if you want to also provide the older DB from before that's fine too, I can use that to make sure I fix the issue for both.

[PrincessNoodlelimbs]

Emailed them both as attachments. I used the playerdata.db file from each version, hopefully those are the right ones. Let me know if there's any problem with these.

[panda]

@PrincessNoodlelimbs So while it wasn't immediately clear as to what was happening, with some debugging it looked like there was a foreign key constraint violation, and when I checked it looks like it was legitimate.

You would not have happened to have deleted players from the database, would you? Because there were a bunch of rows in not just Player_Spells (which is modified in the migration that fails for your database) but all of the Player_* tables that referenced players that did not exist in the Players table.

[PrincessNoodlelimbs]

I think I did, yeah. I forget what the reason was - it happened a long time ago, I know there was some reason behind it but I couldn't tell you what it was now. It was a half-assed attempt to solve something the players were telling me about regarding log-in or something. Database creation and modification are the absolute farthest from my area of knowledge, I failed that class in tech college lol. So something like this was bound to happen the moment I decided to try to futz around with it. I think I'm just going to leave them alone from now on. Is there a way to fix it, perhaps?

[panda]

48 minutes ago, PrincessNoodlelimbs said:

I think I did, yeah. I forget what the reason was - it happened a long time ago, I know there was some reason behind it but I couldn't tell you what it was now. It was a half-assed attempt to solve something the players were telling me about regarding log-in or something. Database creation and modification are the absolute farthest from my area of knowledge, I failed that class in tech college lol. So something like this was bound to happen the moment I decided to try to futz around with it. I think I'm just going to leave them alone from now on. Is there a way to fix it, perhaps?

Because this issue is so weird (and happens in the middle of a step) I am not 100% confident in the fix (so if you want to run it, make a backup first and keep it in case someone finds an issue later) but I ran the following script:

DELETE FROM Player_Bank WHERE PlayerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Friends WHERE OwnerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Friends WHERE TargetId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Hotbar WHERE PlayerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Items WHERE PlayerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Quests WHERE PlayerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Spells WHERE PlayerId NOT IN (SELECT Id FROM Players);
DELETE FROM Player_Variables WHERE PlayerId NOT IN (SELECT Id FROM Players);

 

[PrincessNoodlelimbs]

I'd be happy to give it a shot, but how exactly would I run this script? I've never done that before.

[panda]

Just now, PrincessNoodlelimbs said:

I'd be happy to give it a shot, but how exactly would I run this script? I've never done that before.

You can run it with something like Sqlite Studio, or some sort of SQLite DB browser that is not read-only and allows you to run SQL queries in it.

[PrincessNoodlelimbs]

Hey, apparent success! I ran the script, then tried upgrading to 7.1.74 again. This time the migration completed - I'm assuming, since this time it spit out the expected 'Server Started' lines after it told me it was starting - and I was able to log into the game and open it in the editor.

 

I appear to still be having that 'lag when crossing map boundaries' issue, unfortunately, but the server migration did work.

[panda]

Only one of the map boundary issues has been fixed, but if you were on B6 until recently you never would have hit the fixed one since it was introduced and fixed in B7.1 months ago.

The longer term one has not been fixed (and I am not sure if anyone has figured out a root cause yet).