0

Oracle's documentation says:

String columns in INFORMATION_SCHEMA tables have a collation of utf8_general_ci

I need to change it to utf8_unicode_ci to avoid "Illegal mix of collations" error when running some query.

I tried to change it:

  • on the table level using this command to generate a query:

select concat("ALTER TABLE information_schema.", table_name, " COLLATE utf8_unicode_ci;") as ExecuteTheString from information_schema.tables where table_schema="information_schema";

  • on the database level using this command:

ALTER DATABASE information_schema CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Both commands give the following result:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

Is it even possible to change the collation of INFORMATION_SCHEMA?

EDIT:

I'm not asking how to troubleshoot the "Illegal mix of collations" error, I mentioned it just to introduce the context of my problem.

I've been reading about information_schema all the day yesterday, and every article states that it is special, that it doesn't have its own files, etc.

And when I google about changing the collation it only finds articles about regular schemas, nothing about special ones like information_schema.

What I really need is the clear answer like "yes, it's possible, here is how to do that" or "no, it's not possible, here is the link to read about it".

shiraeeshi
  • 13
  • 4
  • Never ever mess around with the information_schema. It's of no use actually anyway. This schema holds meta information about the database that is populated by MySQL itself. You won't find data about it anywhere on the hard drive. – fancyPants Sep 06 '17 at 13:56
  • @fancypants Thanks for the answer. But unfortunately it doesn't answer the question asked. – shiraeeshi Sep 07 '17 at 04:04
  • Like I said, you don't change anything in the information_schema. Never. When you get this error in a query, you change the part of the query that does not affect the information_schema with `COLLATION whatever` like you can see in the duplicate question. – fancyPants Sep 07 '17 at 06:40

0 Answers0