Rescuing script for corrupted world because of bad manipulation

[MetaNomad]

Hi everyone,

While building my pixelated empire, a power outage struck. Bam! No more lights, no more computer, and worst of all, an abrupt interruption of Minetest mid-game.

EDIT : No, it was not due by the power outage, but because of a bad configuration of my backup tool, see the discussion on the ticket

When I restarted, everything seemed normal, so I continued building, exploring, and digging. But horror struck a few days later when I discovered that my map.sqlite file was corrupted. The worst part? Minetest allows you to keep playing with a corrupted database, so I didn't notice anything and continued building...

For those who find themselves in the same mess, here's how I managed to repair my database with a lifesaving Python script. It covers two scenarios: with a sane backup and without a non-corrupted backup.


EDIT : It will be useful if the command

Code: Select all

$sqlite3 map.sqlite "PRAGMA integrity_check;"

return errors like theses :

Code: Select all

On tree page 99135 cell 249: Rowid 23819277 out of order
On tree page 99135 cell 239: Rowid 23819048 out of order
On tree page 99135 cell 239: 2nd reference to page 131356
On tree page 2459 cell 7: Rowid 23819647 out of order
...
Error: stepping, database disk image is malformed (11)

Which means that some entries are duplicated. The script will be helpful to determinate which one of the duplicated is better to keep, in order to save the maximum of building that have be done on the world.


Here's a walktru for Debian users. Adapt it if you're using other system

Create dedicated directory:

Code: Select all

mkdir -p ~/Minetest/recovery
cd ~/Minetest/recovery

Backup (better safe than sorry):

Code: Select all

cp ~/minetest_directory/worlds/your_world/map.sqlite 
~/minetest_directory/worlds/your_world/map.sqlite.backup
cp ~/minetest_directory/worlds/your_world/map.sqlite ~/Minetest/recovery/

Install necessary tools (if not already installed):

Code: Select all

sudo apt-get install sqlite3 python3

Create SQL dumps:

Code: Select all

sqlite3 ~/path_to_corrupted_world/map.sqlite .dump > dump_broken.sql
sqlite3 ~/path_to_backup/map.sqlite .dump > dump_old.sql

If you don't have a non-corrupted backup, create an empty dump_old.sql file instead:

Code: Select all

touch dump_old.sql

Create a file clean_sql_dump.py with the following content:

Code: Select all

import re
from collections import Counter

def parse_sql_dump(file_path):
    data_dict = {}
    with open(file_path, 'r') as file:
        for line in file:
            match = re.match(r"INSERT INTO blocks VALUES\((\-?\d+),X\'(.*?)\'\);", line)
            if match:
                pos, data = match.groups()
                if pos in data_dict:
                    data_dict[pos].add(data)
                else:
                    data_dict[pos] = {data}
    return data_dict

def get_char_pairs(s):
    return [s[i:i+2] for i in range(len(s) - 1)]

def calculate_pair_similarity(data1, data2):
    pairs1 = get_char_pairs(data1)
    pairs2 = get_char_pairs(data2)
    counter1 = Counter(pairs1)
    counter2 = Counter(pairs2)
    common_pairs = sum((counter1 & counter2).values())
    total_pairs = len(pairs1) + len(pairs2) - common_pairs
    return common_pairs / total_pairs if total_pairs > 0 else 0

def calculate_complexity(data):
    pairs = get_char_pairs(data)
    return len(set(pairs))

def create_cleaned_dump(broken_data, old_data, output_file):
    with open(output_file, 'w') as file:
        file.write("""PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `blocks` (
    `pos` INT PRIMARY KEY,
    `data` BLOB
);
""")
        for pos, data_set in broken_data.items():
            if pos in old_data:
                old_data_set = old_data[pos]
                to_keep = []
                for data in data_set:
                    max_similarity = 0
                    for old_data_value in old_data_set:
                        similarity = calculate_pair_similarity(data, old_data_value)
                        if similarity > max_similarity:
                            max_similarity = similarity
                    if max_similarity < 0.5:
                        to_keep.append(data)
                if not to_keep:
                    max_complexity = 0
                    most_complex_data = None
                    for data in data_set:
                        complexity = calculate_complexity(data)
                        if complexity > max_complexity:
                            max_complexity = complexity
                            most_complex_data = data
                    to_keep = [most_complex_data]
            else:
                max_complexity = 0
                most_complex_data = None
                for data in data_set:
                    complexity = calculate_complexity(data)
                    if complexity > max_complexity:
                        max_complexity = complexity
                        most_complex_data = data
                to_keep = [most_complex_data]
            
            for data in to_keep:
                file.write(f"INSERT INTO blocks VALUES({pos},X'{data}');\n")

        file.write("COMMIT;\n")

def main():
    broken_file = 'dump_broken.sql'
    old_file = 'dump_old.sql'
    output_file = 'dump_cleaned.sql'
    
    broken_data = parse_sql_dump(broken_file)
    old_data = parse_sql_dump(old_file)
    
    create_cleaned_dump(broken_data, old_data, output_file)
    print(f"Cleaned SQL dump created as '{output_file}'.")

if __name__ == "__main__":
    main()

Then, run the script:

Code: Select all

python3 clean_sql_dump.py

Recreate the database:

Code: Select all

sqlite3 new_map.sqlite < dump_cleaned.sql
cp new_map.sqlite ~/minetest_directory/worlds/your_world/map.sqlite

⚠️ Please note that this script was tested in a specific context (duplicated entries) and may not work for all cases of database corruption. Use it at your own risk, and make sure to back up your files before making any changes.

I hope this solution helps others who face a corrupted database. Feel free to use, share, improve this script.