Corrupt database file

[kxxu]

Import MySQL data into AliSQL. During queries, some data files were found to be corrupted. The query statement is as follows:
root[database]> select count() from h_record where time > '2025-04-01' and time < '2025-04-10';
+----------+
| count() |
+----------+
| 3061512 |
+----------+
1 row in set (0.02 sec)

root[database]> select count() from h_record where time > '2025-04-11' and time < '2025-04-12';
ERROR 7501 (HY000): [DuckDB] IO Error: Corrupt database file: computed checksum 7085340976184504599 does not match stored checksum 6666362943038018993 in block at location 359883091968.
root[database]> select count() from h_record where time > '2025-04-12' and time < '2025-04-13';
ERROR 7501 (HY000): [DuckDB] IO Error: Corrupt database file: computed checksum 7085340976184504599 does not match stored checksum 6666362943038018993 in block at location 359883091968.

How does this kind of problem occur, and how to resolve or prevent it?

[p1p1bear]

@kxxu

This issue is very likely related to DuckDB itself.

The current DuckDB version we are using is v1.3.2. Based on the error message:

[DuckDB] IO Error: Corrupt database file: computed checksum ... does not match stored checksum ...

it appears that DuckDB detected a checksum mismatch in the underlying database file/block during query execution.

We also found similar reports in the DuckDB upstream repository.

So at the moment, this looks more likely to be a DuckDB-side corruption / checksum validation issue, rather than a normal SQL-layer issue in AliSQL itself.

As for resolution or prevention, you may want to:

1. Check whether the data file was fully and correctly imported/generated.
2. Verify whether there were any abnormal events during import or write, such as process crash, disk issue, or incomplete file sync.
3. Review the related DuckDB upstream issues to see whether there is already a known cause or workaround.

We are also planning to upgrade the DuckDB version we depend on in the near future. After that, we can check again whether the issue still exists in the newer version.

For now, you may first refer to the upstream issues in the link above and see whether any of them helps resolve your problem.

Thank you. :)

[kxxu]

@p1p1bear Do I need to delete the duckdb.db file and reimport the data if I want to rebuild the dataset? Attempts to delete corrupted data failed, as an error will be triggered whenever a corrupted data block is read.

[p1p1bear]

@p1p1bear Do I need to delete the duckdb.db file and reimport the data if I want to rebuild the dataset? Attempts to delete corrupted data failed, as an error will be triggered whenever a corrupted data block is read.

In general, the duckdb.db file should not be deleted directly.

You may want to try initializing a new instance and then re-importing the data there.

If the duckdb.db file is removed directly, it can lead to metadata inconsistency. For example, SHOW TABLES may still list the tables on the AliSQL side, while the actual corresponding data no longer exists in DuckDB.

So if you need to rebuild the dataset, creating a fresh instance/environment and importing the data again would be the safer approach.