[Bug][Devlake] the devlake pipeline is partially success even after modifying the mysql with the utf8mb4 character and utf8mb4_bin collation

[Puvendhan]

Search before asking

• [X] I had searched in the issues and found no similar issues.

What happened

I am getting the same error as mentioned the github issue https://github.com/apache/incubator-devlake/issues/7528. I modified the mysql with the utf8mb4 character and utf8mb4_bin collation., however i am still seeing the error in the devlake pipeline that the pipeline is partially success.

Devlake is currently running on v1.0.0-beta11

What do you expect to happen

The devlake pipeline should be fully success.

How to reproduce

Just add a gitlab connection in the devlake ui and run the pipeline.

Anything else

No response

Version

v1.0.0-beta11

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[dosubot[bot]]

Hello, @Puvendhan! I'm here to assist you with your Devlake pipeline issue. I can help you solve bugs, answer questions, and become a contributor while you wait for a human maintainer.

To resolve the MySQL collation conversion errors from utf8mb4 to utf8mb3 in the DevLake pipeline running on version v1.0.0-beta11, you need to modify the collation settings in the migration script. The provided script already converts tables to utf8mb4. You need to change the collation to utf8mb3 instead.

Here is the modified code:

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 utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_issues CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_issue_code_blocks CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_hotspots CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_file_metrics CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE _tool_sonarqube_accounts CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE cq_projects CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE cq_issues CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE cq_issue_code_blocks CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
        err = basicRes.GetDal().Exec(`ALTER TABLE cq_file_metrics CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;`)
        if err != nil {
            return err
        }
    }
    return nil
}

This script changes the character set and collation of the specified tables to utf8mb3 with utf8mb3_bin collation ^[1].

To continue talking to Dosu, mention @dosu.

[klesh]

Try re-creating the whole db instead?

[Puvendhan]

Hi , i am getting the below error after changing the charset to utf8mb3. kindly suggest

[klesh]

@Puvendhan Why would you change the charset to utf8mb3? It won't work if I recall correctly. What did you expect to happen after doing so?