wiki_freephile, 1, utf8_general_ci
</pre>
Note that this query is counting the '''columns''' which have an undesirable collation. (Recall that encoding/collation can be set at the server, database, table, and column levels.) So, I fixed start off by setting the defaults for each database:
<source lang="mysql">
ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY';
</source>
You can check the status of each '''table ''' too:
<source lang="mysql">
SET @DATABASE_NAME = 'wiki_freephile';
AND TABLE_COLLATION not in ('binary');
</source>
Then, down in the section below on changing engines, there's an example SQL query for generating '''<code>ALTER TABLE</code>''' statements to fix the table defaults<ref>https://mariadb.com/kb/en/setting-character-sets-and-collations/#table-level</ref>. These statements would be in the form:
<source lang="mysql">
ALTER TABLE table_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
</source>
In my case, there are 90-100 columns that have old latin1 character sets.
<source lang="mysql">
SET @DATABASE_NAME = 'wiki_greg';
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CHARACTER_SET_NAME,
COLLATION_NAME
FROM
information_schema.columns AS cols
WHERE
table_schema = @DATABASE_NAME
AND COLLATION_NAME NOT IN ('binary');
</source>
We can use the information schema to list them all out. And you can also inspect each TABLE, with e.g. <code>SHOW CREATE TABLE brokenlinks \G</code>
To set the mysql server default character set to binary <ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>, add the following to <code>/etc/mysql/my.cnf</code> (Debian/Ubuntu) or <code>/etc/my.cnf</code> (Fedora/Centos/RHEL) under the '''<nowiki>[mysqld]</nowiki>''' section:
*If you ever need to put the DB data into something Unicode, then be sure to use the utf8mb4 (Multi-byte 4) variety.
The *'''recommended* ''' charset/collation is 'binary BINARY'.
<source lang="mysql">
ALTER table <tablename> CHARACTER SET = 'binary' COLLATE = 'BINARY';
WHERE table_schema = "wiki_demo";
</source>
<references />
[[Category:MediaWiki]]
[[Category:QualityBox]]
[[Category:Wiki]]
<references />
