Search code examples
mysqlinnodbmyisam

can mysql.user table's engine be converted from MyISAM to InnoDb?

I ran the following query on my production MySQL database:

SELECT  CONCAT('`', table_schema,'`.`', table_name, '`') AS schema_tables
FROM    information_schema.tables AS tb
WHERE   `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

It returned all the tables from mysql schema, some of them are:

`mysql`.`user`
`mysql`.`time_zone_transition_type`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_name`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone`
`mysql`.`tables_priv`
`mysql`.`servers`
`mysql`.`proxies_priv`
...

My queries are:
1. Can these be converted to innodb safely?
2. Why were they in MyISAM engine in the first place? Did I change it by mistake or were they default?

Server version: 5.6.22-log MySQL Community Server (GPL)
PS: I know InnoDb is default engine from 5.6 and I never ran any query to convert to MyISAM.

Solution

  • No, you can't!

    As the docs say:

    Important

    Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.