createuser - How to fix ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. in Mysql 8.0 after CREATE USER

Question

I have an installation of Debian Stretch and a new installation of Mysql 8.0 (no changes in configuration yet). When I try to create a new user with:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xyz';

I got the following:

ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]

Any suggestion about what the problem could be?

Thank you

Answer 1

Sarel suggested this solution which performs all the steps I did below but does it the MySQL way which is probably safest.

mysqld --upgrade=FORCE

https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_upgrade

The server upgrades the data dictionary, the Performance Schema, and the INFORMATION_SCHEMA

Thanks, @Sarel!

My previous post for historical use:

I had restored a DB backup into my new dev MySQL 8 system without thinking and overwrote the MySQL database tables. It wasn't that hard to fix but just took a bit of hacking at it for a while and this is what fixed it.

alter table mysql.db ENGINE=InnoDB;
alter table mysql.columns_priv ENGINE=InnoDB;

after that, I was able to create a user with no problems.

The key was in the error message.

ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]

So I knew it was mysql.db that was MyISAM and needed to be something else so I just changed it to InnoDB.

Hope that helps someone!

If your MySQL database is the wrong type that will work or the other alternative would be to initialize your db

mysqld --initialize

That'll recreate it all. If you can dump the SQL before you do that it's always best.