Cannot connect using MySQL client due to error: Illegal mix of collations

[theetrain]

Hello,

I typically connect to DoltDB using VSCode SQLTools without issue. Recently, when I connected after a DoltDB upgrade I came across this error:

Request connection/GetChildrenForTreeItemRequest failed with message: ER_UNKNOWN_ERROR: Illegal mix of collations (utf8mb3_general_ci) and (utf8mb4_0900_bin)

I haven't set explicit charsets to my tables and have relied on defaults. In an attempt to overcome this, I deleted my database and set an explicit charset on all my tables, like this formulas table for example:

CREATE TABLE IF NOT EXISTS formulas (
  id INT NOT NULL AUTO_INCREMENT,
  selector json,
  calculation json,
  PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin;

Despite setting this, the issue persists. Somewhat related, I noticed the following SQL isn't compatible (that or I'm writing it incorrectly):

ALTER TABLE formulas
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_bin;

I've confirmed this error exists on DoltDB ❌ v0.53.2 and ❌ v0.54.2, and I'm able to connect error-free on ✅ v0.52.10.

My workaround is to shell into my Docker container running dolt and perform queries via dolt sql -q "<query>" that still works.

[timsehn]

Thanks for the report. @Hydrocharged our collation expert will look at this today.

[timsehn]

Also @jennifersp I'm fairly certain this has to do with this PR:

dolthub/go-mysql-server#1566

Looks like VSCode SQLTools doesn't like how we did information_schema collation.

[Hydrocharged]

@jennifersp I think this may be a little more complicated than the last one. My first guess is that this is dealing with character set repertoire.
https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html
We currently have zero support for this right now, but it’s related to the work I’m doing right now with general collation coercion support. I’ll be able to confirm this a little later.

[bpf120]

@theetrain We'd love to learn more about your use case if you want to share. You can email me or swing by our Discord

[Hydrocharged]

I was incorrect regarding my assumption. This is not a character set repertoire issue, but a collation coercion issue instead. It will still take a bit before proper coercion support has landed, so I linked a PR to work around this particular issue in the meantime.