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

mysql-8.0 createuser
user3717337 picture user3717337 · Jan 21, 2019 · Viewed 15.1k times · Source

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

jbrahy picture jbrahy · Apr 24, 2019

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.

Related questions

Authentication plugin 'caching_sha2_password' cannot be loaded

I am connecting MySQL - 8.0 with MySQL Workbench and getting the below error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found I have tried with other client …

Read More
How to grant all privileges to root user in MySQL 8.0

Tried mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; Getting ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right …

Read More
How do you use the "WITH" clause in MySQL?

I am converting all my SQL Server queries to MySQL and my queries that have WITH in them are all failing. Here's an example: WITH t1 AS ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid = …

Read More