Currently the developers are putting their own money into JC2-MP to keep the servers online.

Please take a few seconds of your time and disable your AdBlock plugin for our website.

Ad revenue is not going to developers, it is used purely for covering our hosting costs.

 

You are also free to donate, which removes all ads from our website!

Patch 0.3 was just released! Full changelog here: https://t.co/4A50m6IKen

2 years ago

Advertisement
October 19, 2019, 06:16:48 am

Author Topic: SQL Database Image Error  (Read 1201 times)

Dev_34

  • Full Member
  • ***
  • Posts: 158
    • View Profile
SQL Database Image Error
« on: March 09, 2016, 09:11:39 pm »
I've been having an issue with the SQL database of my server where a certain error occurs when scripts interact with the database.

The error is:
Code: [Select]
Runtime Error: Error when executing SQL command: database disk image is malformed

I've looked online a little and can't seem to find out why this would occur.
When opened in a sqlite database browser, the data seems to be fine and present, but for certain records/rows/players, it throws this error.

While the database is largely functional, when this disk image error happens there is a chance for the server to crash and the database to rollback, which is very problematic since we store a lot of player data.

Does anyone more familiar than me with SQL know what causes this error and how to fix or work around it?

----- EDIT -----

I've done some research on the issue and have found a solution.

Executing this line of code on the database:
Code: [Select]
PRAGMA integrity_check;
returned an error, meaning the database was corrupt.

In order to fix the issue, I downloaded SQLShell(http://software.clapper.org/sqlshell/)

I then dumped the database by executing this in SQLShell with the database loaded:
Code: [Select]
.mode insert
.output dumped_db.sql
.dump
.exit

This resulted in a text file with approximately 150,000 CREATE and INSERT statements necessary to re-create the database.

I then created a new database using my database browsing tool and the database dump. (http://sqlitebrowser.org/)

I executed the database dump's SQL commands on the new database.
Strangely, I found several records with unidentifiable characters and many duplicate columns in several different tables with 'UNIQUE' columns.

After getting rid of duplicate records and removing any garbage data (which sqlitebrowser detected), the database seems to be working fine, according the integrity_check PRAGMA.

After learning that there is apparently a whole API for creating back-ups of SQLite databases during run-time, I believe the corruption may have started with my nonchalant making of back-ups while the server was running and the database was being written to.

I hope this information can be of use to anyone who experiences these issues in the future and I advise server owners to be cautious when making back-ups.
I now make back-ups when the server is offline.
« Last Edit: March 11, 2016, 04:21:28 am by Dev_34 »