2

I did a wonderful thing of deleting all root users from mysql by accident. That kind of a day.

However before I panic I figured I would use this method here from searching online that didn't seem so bad.

"Add 'skip-grant-tables' to my.cnf under the [mysqld] section restart mysql"

then insert a new root user using "INSERT INTO mysql.user..."

When I do this I get an error I cannot find much information about

The target table user of the INSERT is not insertable-into

Does anyone have any ideas about this and how can I re-create a root user?

at first I thought this might have something to do with virtualmin because they talk about it here but I did this from CLI into mysql and it still says the same error

my environment Centos7

Webmin version 1.940
Usermin version|1.780
Virtualmin version 6.08
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1
gstlouis
  • 119
  • 3
  • 12
  • On related user authorization changes in MariaDB 10.4, see my answer here: https://dba.stackexchange.com/a/276298/216424 – chang zhao Sep 30 '20 at 01:29

1 Answers1

2

I finally found my solution (big thanks to @RolandoMySQLDBA on this thread here).

The only problem I had with his answer was that WITH GRANT OPTION; was missing from /var/lib/mysql/InitFile.sql:

GRANT ALL PRIVILEGES ON *.* TO root@localhost
IDENTIFIED BY 'password' WITH GRANT OPTION;

Do not forget to remove the file when you are done and update to remove the init-file string you added to /etc/my.cnf.

I am still unsure why MariaDB was outputting error INSERT is not insertable-into and could not find any documentation explaining this was a MariaDB problem.

gstlouis
  • 119
  • 3
  • 12