Information Schema showing two character encodings for column

Question

I'm in the process of migrating a MySQL database from the utf8 character set to uft8mb4, following this guide (https://mathiasbynens.be/notes/mysql-utf8mb4). For one of the tables I updated (table1) I get weird output from the information_schema. table1 has four columns, each listed below:

data_store VARCHAR(24)
data_group VARCHAR(24)
source_count INT
load_count INT

I have validated that only 4 columns appear through SELECT * on the table. However, running the following query on information_schema produces odd output.

SELECT column_name, character_set_name FROM information_schema.COLUMNS 
WHERE table_name = "table1";

COLUMN_NAME                                         CHARACTER_SET_NAME                
--------------------------------------------------------------------------------------
data_store                                          utf8                              
data_group                                          utf8                              
source_count                                        <null>                            
load_count                                          <null>                            
data_store                                          utf8mb4                           
data_group                                          utf8mb4                           
source_count                                        <null>                            
load_count                                          <null> 

I don't see duplicate rows (with differing character sets) for any other table that I have updated and am at a loss in regards to what is wrong and/or how to fix it. Any help would be much appreciated!

Notes: I believe I could just remove the unwanted columns from information_schema, but I'm not sure if this would break anything.

Answer 1

I think you're seeing the columns from different table1 tables in several different database schemas.

Try this to verify that claim.

 SELECT table_schema, column_name, character_set_name
   FROM information_schema.COLUMNS 
  WHERE table_name = 'table1'

Try this query to filter by the current database.

 SELECT column_name, character_set_name
   FROM information_schema.COLUMNS 
  WHERE table_name = 'table1'
    AND table_schema = DATABASE()

Do not try to alter the INFORMATION_SCHEMA database in any way. Don't delete rows, don't add columns, or anything else. It's supposed to be readonly. But sometimes it isn't, and altering it can trash your MySQL instance. Don't ask me how I know that. :-)