h2 database error, how to fix it

[raindylong]

Issue

Impacted version: 4.32

Deployment mode: explain here how you use GitBucket :
with an http frontend (nginx, httpd, ...)

Problem description:

java -version
java version "9.0.4"
Java(TM) SE Runtime Environment (build 9.0.4+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.4+11, mixed mode)

The gitbucket java process crash , restart it , there are some H2 database error , see the next content:

org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "platformcheck"; SQL statement:
select "GROUP_NAME", "USER_NAME", "MANAGER" from "GROUP_MEMBER" where "GROUP_NAME" = ? order by "USER_NAME" [22018-199]
2019-09-30 23:52:06 jdbc[3]: exception

How to fix it? Thanks a lot.

[takezoe]

If your H2 database file is broken, the recover tool might be able to fix it.
http://www.h2database.com/html/advanced.html#using_recover_tool

In our experience, H2 database file is broken by JVM crash or other reason easily. So, we recommend using an external database instead of the default H2 database if you use GitBucket for an important purpose. GitBucket supports MySQL and PostgreSQL, and PostgreSQL is my recommendation.
https://github.com/gitbucket/gitbucket/wiki/External-database-configuration

[raindylong]

Thanks @takezoe , now the problem is the export function crash every time (http code 500)

[raindylong]

It seems that we can use h2.jar driver to export/import the database file , thanks .

[raindylong]

It's ok now to export sql file from the admin console , but there are no CREATE command in the SQL file, just DELETE & INSERT command.
How should I do next ?
ELETE FROM WATCH; DELETE FROM PAGES; DELETE FROM ISSUE_NOTIFICATION; DELETE FROM ACCOUNT_WEB_HOOK_EVENT; DELETE FROM ACCOUNT_EXTRA_MAIL_ADDRESS; DELETE FROM PULL_REQUEST; DELETE FROM ISSUE_COMMENT; DELETE FROM ISSUE_LABEL; DELETE FROM PROTECTED_BRANCH_REQUIRE_CONTEXT; DELETE FROM ISSUE; DELETE FROM RELEASE_ASSET; DELETE FROM WEB_HOOK_EVENT; DELETE FROM MILESTONE; DELETE FROM COMMIT_COMMENT; DELETE FROM COLLABORATOR; DELETE FROM DEPLOY_KEY; DELETE FROM GIST_COMMENT; DELETE FROM PROTECTED_BRANCH; DELETE FROM RELEASE_TAG; DELETE FROM WEB_HOOK; DELETE FROM ACTIVITY; DELETE FROM PRIORITY; DELETE FROM LABEL; DELETE FROM ISSUE_ID; DELETE FROM COMMIT_STATUS; DELETE FROM REPOSITORY; DELETE FROM ACCOUNT_FEDERATION; DELETE FROM SSH_KEY; DELETE FROM ACCOUNT_WEB_HOOK; DELETE FROM GROUP_MEMBER; DELETE FROM NOTIFICATIONS_ACCOUNT; DELETE FROM GIST; DELETE FROM ACCESS_TOKEN; DELETE FROM GPG_KEY; DELETE FROM ACCOUNT;

[takezoe]

Did your GitBucket work properly after recovering H2 database? If no, that means your database hasn't been fully recovered.

[raindylong]

Did your GitBucket work properly after recovering H2 database? If no, that means your database hasn't been fully recovered.

Hi @takezoe, it's ok now after recover the H2 database file, every rep seems ok. So I export the SQL at the admin console, but there are no DDL part in the SQL file, just DELETE & INSERT part.

[takezoe]

Ah, exporting only DELETE & CREATE statements is expected behaviour. After exporting data, you need to configure your GitBucket to use the external database. Tables will be created by GitBucket automatically on the new database at the first bootstrapping after configurating the external database. Then you can import your data.

See Data migration section in the following page for detailed operations:
https://github.com/gitbucket/gitbucket/wiki/External-database-configuration#data-migration

[raindylong]

@takezoe , now it's working under mysql , love you.