How to resolve the error : COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' mysql verision 8.0.0 (docker,django,datagrip)

Question

I am trying to use datagrip for my database management. I can create a connection but when the data of the database I get this error:

[42000][1253] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET
 'latin1'. COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

I have looked only for solutions to this but non of them seem to work. A few of them involved changing the /etc/mysql/my.cnf file it showed the changes that should work but didn't have any effect.

my.cnf:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[client]
loose-default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

the result:

show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


show variables like '%collation%'

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

Answer 1

It could be two issues here.

1. Table you're trying to get data from is created in different collation. So, SHOW CREATE TABLE show help to unveil if it is.

2. Your local machine and client are working in another collation. Setting connectionCollation on Advanced tab should help.

Also, you can explicitly define collation by setting SET NAMES utf8; as startup script on data source' Options tab: