Error with MySQL DB when migrating to v4.35.0

[shinsenter]

Issue

Impacted version: 4.35.0

Deployment mode: Docker image : gitbucket/gitbucket:4.35.0 with MySQL driver

version: '3'
services:
  gitbucket:
    image: gitbucket/gitbucket:4.35.0
    container_name: gitbucket
    ports:
      - "8080:8080"
    volumes:
      - "/root/.gitbucket:/gitbucket"
    environment:
      GITBUCKET_DB_URL: jdbc:mysql://mysql:3306/gitbucket?useUnicode=true&characterEncoding=utf8
      GITBUCKET_DB_USER: root
      GITBUCKET_DB_PASSWORD: hogehoge

Problem description:
MySQL Error when migrating to v4.35.0, table engine ENGINE=InnoDB.
v4.34.0 was working very well.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-08-03 06:46:13 0x7fe76c2af700 Error in foreign key constraint of table gitbucket/#sql-1_3:
 FOREIGN KEY (`USER_NAME`, `REPOSITORY_NAME`) REFERENCES REPOSITORY (`USER_NAME`, `REPOSITORY_NAME`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

Above error occured with this line of migration script:
https://github.com/gitbucket/gitbucket/blob/master/src/main/resources/update/gitbucket-core_4.35.xml#L27

  <addForeignKeyConstraint constraintName="IDX_WEB_HOOK_FK0" baseTableName="WEB_HOOK" baseColumnNames="USER_NAME, REPOSITORY_NAME" referencedTableName="REPOSITORY" referencedColumnNames="USER_NAME, REPOSITORY_NAME"/>

Running other migration scripts with referencedTableName also failed.

[takezoe]

Hmm... It's pretty weird. USER_NAME, REPOSITORY_NAME is the primary key of REPOSITORY table and it's referred from many tables. I think it should exist. Also, the migration test is working with MySQL 5.7 that I suspect you use based on the error message:

gitbucket/src/test/scala/gitbucket/core/GitBucketCoreModuleSpec.scala

Lines 29 to 50 in cf0f896

	Seq("8.0", "5.7").foreach { tag =>
	test(s"Migration MySQL $tag", ExternalDBTest) {
	val container = new MySQLContainer() {
	override val container = new org.testcontainers.containers.MySQLContainer(s"mysql:$tag") {
	override def getDriverClassName = "org.mariadb.jdbc.Driver"
	}
	// TODO https://jira.mariadb.org/browse/CONJ-663
	container.withCommand("mysqld --default-authentication-plugin=mysql_native_password")
	}
	container.start()
	try {
	new Solidbase().migrate(
	DriverManager.getConnection(s"${container.jdbcUrl}?useSSL=false", container.username, container.password),
	Thread.currentThread().getContextClassLoader(),
	new MySQLDatabase(),
	new Module(GitBucketCoreModule.getModuleId, GitBucketCoreModule.getVersions)
	)
	} finally {
	container.stop()
	}
	}
	}

[shinsenter]

@takezoe
My setup is described in #2858

[TheBenjiManCan]

This happened to me just today.

It failed on the statement

ALTER TABLE gitbucket.WEB_HOOK ADD CONSTRAINT IDX_WEB_HOOK_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES gitbucket.REPOSITORY (USER_NAME, REPOSITORY_NAME);

In my case it was due to migration of gitbucket to a newer server, and the database there having a different set of COLLATE defaults that resulted in a column definition mismatch between the WEB_HOOK and REPOSITORY tables
.... FOREIGN KEY definitions require that the column defs be exactly the same. ... note the difference

MariaDB [gitbucket]> show create table gitbucket.REPOSITORY;

| REPOSITORY | CREATE TABLE `REPOSITORY` (
  `USER_NAME` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `REPOSITORY_NAME` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

and

MariaDB [gitbucket]> show create table gitbucket.WEB_HOOK;

| WEB_HOOK | CREATE TABLE `WEB_HOOK` (
  `HOOK_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(100) NOT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

My fix (bit of a hack, but quick and worked !!) was to

1. use a previous backup (mysqldump) of the database .... one not tainted by the failed upgrade procedure.
2. use sed (or a text editor) to remove all refs to COLLATE with utf8mb4_unicode_ci
   sed -i 's+COLLATE.utf8mb4_unicode_ci++g' DBFILE
3. blow away the database and reimport from the edited backup file
   mysqladmin -f drop gitbucket && mysqladmin create gitbucket && cat DBFILE | mysql gitbucket
4. restart gitbucket ..... and the updates were now successfully applied.

Trying to recover with the existing database, sorry, can't help you there.

[takezoe]

In my case it was due to migration of gitbucket to a newer server, and the database there having a different set of COLLATE defaults that resulted in a column definition mismatch between the WEB_HOOK and REPOSITORY tables

Ah, I see... That makes sense. But do you think if we should do something on GitBucket side to cover such cases?