dosubot[bot] commented on issue #7528: URL: https://github.com/apache/incubator-devlake/issues/7528#issuecomment-2134671806
<!-- Greeting --> Hey @mieliespoor! I'm here to help you with any bugs, questions, or contributions you have. Let's work together to solve this issue. <!-- Answer --> 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 <sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/sonarqube/models/migrationscripts/20230316_modify_character_set.go)</sup><sup>[[2]](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/sonarqube/models/migrationscripts/20240508_modify_commit_character_type.go)</sup><sup>[[3]](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/sonarqube/models/migrationscripts/20240325_modify_commit_character_type.go)</sup>. 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: ```yml 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: ```go 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 <sup>[[4]](https://github.com/apache/incubator-devlake/blob/main/devops/releases/lake-v0.15.0/docker-compose.yml)</sup><sup>[[5]](https://github.com/apache/incubator-devlake/blob/main/devops/releases/lake-v0.14.0/docker-compose.yml)</sup><sup>[[6]](https://github.com/apache/incubator-devlake/blob/main/devops/releases/lake-v0.17.0/docker-compose.yml)</sup><sup>[[7]](https://github.com/apache/incubator-devlake/blob/main/devops/releases/lake-v0.19.0/docker-compose.yml)</sup>. <!-- Dosu Comment Footer --> <details open> <summary> #### Details </summary> **About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. </details> *To continue the conversation, mention @dosu.* -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@devlake.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
