Running a rails migration overwrites my charset. Any ideas why?

Question

I have everything set to utf8mb4 in my DB :

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+```

My database.yml [ I added encoding/collation after it was created ]. But I did however update all my tables and database to utf8mb4.

development:
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_general_ci

When I run any rails db:migrate command, the export always looks something like this for every table :

-/*!50503 SET character_set_client = utf8mb4 */;
+/*!40101 SET character_set_client = utf8 */;

I can check any of those individual tables and see that they are indeed utf8mb4 ( but only on some rows. Perhaps that's a problem? ) :

mysql> show full columns from ab_experiment_buckets;
+--------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field              | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id                 | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| foobar_id   | int(11)      | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| foobar_nam        | varchar(255) | utf8mb4_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| foobar_index       | int(11)      | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| foobar_probability | float        | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| foobar_value       | text         | utf8mb4_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| created_at         | datetime     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at         | datetime     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| foobar_is_default         | tinyint(1)   | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
+--------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

Answer 1

Do you have something like this?

  def configure_charsets
    response.headers["Content-Type"] = "text/html; charset=UTF-8"
    suppress(ActiveRecord::StatementInvalid) do
      ActiveRecord::Base.connection.execute 'SET NAMES utf8mb4'
    end
  end

and/or

dataSource:
    dbCreate: "update"
    url: "jdbc:mysql://localhost:8889/mydbname?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true"

The "UTF-8" above is deliberate; it should not be "utf8mb4" and vice versa.

Do you use rake db:reset? It may cause trouble.

Trouble with UTF-8 characters; what I see is not what I stored may help with a specific symptom.