Incorrect collation used for charset `utf8mb4`

[kzajaczkowski]

Describe the bug

This is about dbt-mysql in version 1.7.0a1.

Starting with mysql version 8, the default and recommended collation for charset utf8mb4 is utf8mb4_0900_ci_ai. When inspecting mysql.general_log after running any dbt command, the following entry appears:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'

This leads to errors of mismatched collations when server setting is different from utf8mb4_general_ci.

[2024-04-23, 20:00:13 UTC] {status.py:76} WARNING - Unexpected exception to yield table [table_name]: (pymysql.err.OperationalError) (1271, "Illegal mix of collations for operation 'UNION'")

Steps To Reproduce

Set up general_log:

SET global general_log = 1;
SET global log_output = 'table';

Run any dbt command, e.g. dbt run.

Check result int the dbt log:

   select *
   from mysql.general_log
   where argument like '%SET NAMES%'
   order by 1 desc
   limit 10;

Expected behavior

The last query shouldn't show

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'

but either nothing or

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ci_ai'

or last statement should be

SET NAMES utf8mb4

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

The output of dbt --version:

Core:
  - installed: 1.7.11
  - latest:    1.7.13 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

The operating system you're using: macOs Sonoma 14.4.1

The output of python --version: Python 3.11.6

Additional context

• This seems to be ok when using higher version of python connector (8.1).
• Would be super nice to be able to specify charset/collation in profiles.yml.
• Happy to provide a pull request.

[kzajaczkowski]

@mwallace582, thank you for reviewing and merging #173. Please let me know if there's something I could do to have a new alpha release of 1.7 that would include these changes.

[mwallace582]

I'll work on releasing 1.7.0. I don't think it's necessary to keep releasing alpha versions at this point.

[mwallace582]

I've just released 1.7.0. Let me know if you encounter any issues.