[Question][Bitbucket] Conversion from collation utf8mb4_0900_ai_ci into utf8mb3_general_ci impossible about incubator-devlake HOT 2 CLOSED

Hey @mieliespoor! I'm here to help you with any bugs, questions, or contributions you have. Let's work together to solve this issue.

The error you're encountering is related to a collation conversion issue in MySQL. The latest beta version (1.0.0-beta9) of incubator-devlake includes changes that could affect MySQL collation settings and potentially cause such errors. Specifically, the migration scripts 20230316_modify_character_set.go and 20240325_modify_commit_character_type.go include ALTER TABLE statements to convert tables to the utf8mb4 character set and utf8mb4_bin collation, which might be causing the issue ^[1][2][3].

To resolve this, ensure that your MySQL database and tables are consistently using the utf8mb4 character set and utf8mb4_bin collation. You can configure these settings in your docker-compose.yml file as follows:

version: "3"
services:
  mysql:
    image: mysql:8
    volumes:
      - mysql-storage:/var/lib/mysql
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: admin
      MYSQL_DATABASE: lake
      MYSQL_USER: merico
      MYSQL_PASSWORD: merico
      TZ: UTC
    command: --character-set-server=utf8mb4
      --collation-server=utf8mb4_bin
      --skip-log-bin

Additionally, you can use a migration script to convert existing tables to the correct character set and collation:

package migrationscripts

import (
	"net/url"

	"github.com/apache/incubator-devlake/core/context"
	"github.com/apache/incubator-devlake/core/errors"
)

type modifyCharacterSet struct{}

func (*modifyCharacterSet) Up(basicRes context.BasicRes) errors.Error {
	dbUrl := basicRes.GetConfig("DB_URL")
	if dbUrl == "" {
		return errors.BadInput.New("DB_URL is required")
	}
	u, err1 := url.Parse(dbUrl)
	if err1 != nil {
		return errors.Convert(err1)
	}
	if u.Scheme == "mysql" {
		err := basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_projects CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;`)
		if err != nil {
			return err
		}
		// Repeat for other tables as needed
	}
	return nil
}

func (*modifyCharacterSet) Version() uint64 {
	return 20230316220071
}

func (*modifyCharacterSet) Name() string {
	return "sonarqube modify character set"
}

This should help avoid collation conversion errors in your CI pipeline ^[4][5][6][7].

To continue the conversation, mention @dosu.

from incubator-devlake.

I think this was because some migration didn't properly run. I was in the position where I could delete all the tables in the db and start over, so I simply did that. I think this database was in some unrecoverable state in any case.

As I won't be able to add much to this going forward, I will be closing this.

from incubator-devlake.