14

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

Alicia
  • 1,152
  • 1
  • 23
  • 41
user3717337
  • 205
  • 1
  • 3
  • 9

5 Answers5

14

as @Mae suggested below, make sure you stop the server before you do any of these steps.

as @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.

jbrahy
  • 4,228
  • 1
  • 42
  • 54
6

Do not run alter table to force it to change mysql.db to innodb. You will end up with a different error like 'Cannot load from mysql.db. The table is probably corrupted'. If you did do it the following will fix that too.

I restored a dump from 5.7 into a new 8.0 server and then had this problem.

To fix it stop the mysql service and then run this command to make mysql 8 upgrade the internal schemas:

mysqld --upgrade=FORCE

You can watch the logs to see its progress and if it has any errors.

Sarel Botha
  • 12,419
  • 7
  • 54
  • 59
  • I like this answer, I've never seen that option. Which version of MySQL released it? – jbrahy Mar 03 '21 at 15:37
  • 1
    No clue. I only saw one mention of it on some other site but no reports of whether it works or not. In my case it did work. – Sarel Botha Mar 04 '21 at 16:03
4

I ran into the same problem after a fresh installation of MySQl 8.0 followed by reloading an old (v 5.7) dump file. Solution was to delete and reinstall MySQL and this time created a new dump file from the 5.7 MySQL containing only my own tables excluding system tables and imported them into the v 8.0 MySQL. Everything works flawlessly.

Ali Bakhshandeh
  • 433
  • 6
  • 10
2

You should maybe consider moving away from MyISAM. InnoDB is the default engine in MySQL since 5.6, MySQL 8.0 will be the last version that has limited support for it.

You can read up on the details in this Percona blog post

Erik
  • 74
  • 6
  • Did you run the upgrade script after the upgrade? It should have changed the systems tables to InnoDB. – Dave Stokes Jan 21 '19 at 15:30
  • It was clean install without data migration. I just loaded one database (from dump from 5.6) where are all tables in InnoDB and tried create new user. – user3717337 Jan 21 '19 at 15:40
  • Was it a dump of the whole database or just your own schemas? If it was the whole database the system tables form the dump will have overwritten the ones from the installation maybe. – Erik Jan 21 '19 at 16:22
  • It was own schema. I noted that system tables were really in MyISAM. I sorted problem by reinstallation of Mysql. Tables were InnoDB after reinstallation, I successfully created the user and load same database dump. I can't say what exactly changed system table into MyISAM. The only change in first try was that loading failed because of ROW_FORMAT=FIXED in the dump by few own tables (sorted with sed -ie 's/ROW_FORMAT=FIXED//g' ). – user3717337 Jan 22 '19 at 07:17
2

Different solution: I had a MySQL dump of all my databases from MySQL 5.7, including the mysql table. After importing the whole dump folder (including this mysql table) I got this error. After a complete reinstall of MySQL 8.0.21 and removing the mysql table from the dump folder everything worked as expected.

Cerveser
  • 752
  • 8
  • 23