Change 365992 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] eventlogging_cleaner: force a cast to char for the uuid field

https://gerrit.wikimedia.org/r/365992

Change 365992 merged by Elukey:
[operations/puppet@production] eventlogging_cleaner: force a cast to char for the uuid field

https://gerrit.wikimedia.org/r/365992

Could that be that db1047 was using utf8 just for historic reasons and dbstore1002 got the template from the dbstore servers that uses binary?

In T170952#3451463, @Marostegui wrote:

Could that be that db1047 was using utf8 just for historic reasons and dbstore1002 got the template from the dbstore servers that uses binary?

This could be the explanation, historic reasons lead to this inconsistency.. what would be the best followup? Is it feasible/doable/worth to alter the databases to make the uniform?

Keep in mind that altering the database will not change the existing data, it will just change the default so the new tables will get the new defaults (binary).
I am not sure if it is worth altering all the tables there...that is probably for you to decide :)

In T170952#3451817, @Marostegui wrote:

Keep in mind that altering the database will not change the existing data, it will just change the default so the new tables will get the new defaults (binary).
I am not sure if it is worth altering all the tables there...that is probably for you to decide :)

Since the eventlogging_cleaner script works now I would be inclined to only fix the databases for the new tables, not altering the existing ones. From the eventlogging code it seems that uuid is supposed to be a char, not a binary, but if there is a reason not to follow this assumption we can change it. Any recommendation?

So what we can do is:

alter database log DEFAULT CHARACTER SET binary;

So the new tables will be binary at least, and if you feel some of the already existing tables need to be converted, that can be addressed later or on a one-by-one basis depending on the needs.

A char with binary collation is essentially a binary field- mysql translates it automaticaly if you explicitly or implicitly define somethin as CHAR charset BINARY. If a field should be a char and not a binary, it should be defined with an explicit charset on the definition (ASCII, utf8, utf8mb4, latin1...). Binary is the standard for mediawiki on WMF (standard means here default, not that it should not be changed)- so non-binary fields should be explicitly defined when needed.

From db1046:

MariaDB [log]> SELECT count(table_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='log' and not DATA_TYPE='binary' and COLUMN_NAME='uuid';
+-------------------+
| count(table_name) |
+-------------------+
|               384 |
+-------------------+
1 row in set (0.07 sec)

So after a chat with my team I discovered that we enforce utf-8 in the eventlogging mysql connection, so the master is having all uuids set to utf-8. The slaves are synced via eventlogging_sync so they rely on other parameters like the databases' default charset.

@Marostegui I'd just alter dbstore1002 log database to default to uft8 and close the task, what do you think?

Yes, I assume if not given, it will take the default from the database- changing it on the db everywhere should work for new creations. Do we need to alter any existing one?

In T170952#3518622, @elukey wrote:

From db1046:

MariaDB [log]> SELECT count(table_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='log' and not DATA_TYPE='binary' and COLUMN_NAME='uuid';
+-------------------+
| count(table_name) |
+-------------------+
|               384 |
+-------------------+
1 row in set (0.07 sec)

So after a chat with my team I discovered that we enforce utf-8 in the eventlogging mysql connection, so the master is having all uuids set to utf-8. The slaves are synced via eventlogging_sync so they rely on other parameters like the databases' default charset.

@Marostegui I'd just alter dbstore1002 log database to default to uft8 and close the task, what do you think?

+1 from my side!

Altered dbstore1002's default to match db1047:

MariaDB [(none)]> show create database log;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| log      | CREATE DATABASE `log` /*!40100 DEFAULT CHARACTER SET binary */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> ALTER DATABASE log DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 1 row affected (0.38 sec)

MariaDB [(none)]> show create database log;
+----------+--------------------------------------------------------------------------------------+
| Database | Create Database                                                                      |
+----------+--------------------------------------------------------------------------------------+
| log      | CREATE DATABASE `log` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Are you sure you want uft8 and not utf8mb4? You may be losing extended area characters (e.g. emojis).

In T170952#3561391, @jcrespo wrote:

Are you sure you want uft8 and not utf8mb4? You may be losing extended area characters (e.g. emojis).

I think he wanted to make it identical to db1047 (I could be wrong though!)

Yep I made it equal to db1047, but I am open to suggestions :)

Hm, for now I think this is good, so that the slaves match each other. If we want everything to be a different charset, we should change it as a different ticket.

In T170952#3562297, @Ottomata wrote:

Hm, for now I think this is good, so that the slaves match each other. If we want everything to be a different charset, we should change it as a different ticket.

Sounds good to me