0

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
Improve this question

2 Answers 2

Reset to default
1

I finally found my solution.

This is a big thanks to @RolandoMySQLDBA on this thread here

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

example:

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

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

I am still not sure why mariadb was giving me the error "INSERT is not insertable-into" and I cold not find any documentation that explains this was a mariaDB problem. If anyone has more information about this please share

Improve this answer
3
  • If it's something obscure and MariaDB specific, maybe the MariaDB lists? I've used them in the past and found them to be good! You might even like to report back here if you find the answer to this issue? In any case, please mark your own answer as correct (no points though! :-) ) as it may help others in the same situation!
    – Vérace
    Commented Jan 5, 2020 at 21:45
  • How do I mark my answer as correct? It says I cant accept my own answer in 2 days..
    – gstlouis
    Commented Jan 5, 2020 at 23:38
  • Yes - you'll have to wait! It's a mechanism that StackExchange has to try and "enforce" good quality answers so that the OP (Original Poster - i.e. the person who asked the question - in this case, you) doesn't accept the first answer given and then have better ones come along later unacknowledged. At least, that's my understanding.
    – Vérace
    Commented Jan 6, 2020 at 6:16
0

I am still not sure why mariadb was giving me the error "INSERT is not insertable-into" and I cold not find any documentation that explains this was a mariaDB problem. If anyone has more information about this please share

In MariaDB 10.4, they have introduced some security-related changes. In particular, Authentication in MariaDB 10.4 — Understanding the Changes:

All user accounts, passwords, and global privileges are now stored in a mysql.global_priv table. What happened to the mysql.user table? It still exists and has exactly the same set of columns as before, but it’s now a view over mysql.global_priv.

In other words, you can select from mysql.user as before, but you cannot update it anymore.

As far as I can tell,

(1) Virtualmin still have problems with upgrading to work with latest MariaDB versions.

(2) MariaDB's documentation should be updated here:

https://mariadb.com/kb/en/create-user/

as it still speaks of mysql.user table.

However, the new way is described in another place:

https://mariadb.com/kb/en/authentication-from-mariadb-104/

For details see also:

MariaDB Authorization and Permissions for SQL Server Users.

I wouldn't post here particular "solutions", because the topic is about working with users table in new ways, which may mean CREATE user, ALTER, GRANT privileges, etc.

According to various needs, when your old ways don't work, look for the new ways in those docs.

Improve this answer
4
  • This answer would be much better if you included the gist of the solution into the answer itself, not just a link to it, which is bound to go stale one day.
    – mustaccio
    Commented Sep 30, 2020 at 12:29
  • @mustaccio, the question is "Why the target table user of the INSERT is not insertable-into". I answered that question in detail, so please upvote it. As for solutions, they could be different, such as using another table or (preferably) CREATE_USER. Hence my advice is read the docs (as I pointed out) and do what you need. As for "stale links", rather answers here would become stale than developers' docs.
    – chang zhao
    Commented Sep 30, 2020 at 19:42
  • Not sure where you see this "why" -- the question I see is "how can I re-create a root user?". And the stale link means that the URL points to a wrong or non-existent page (documentation pages move around more often than you think), and not that the information on a page is incorrect. You are, of course, welcome to not take any of this into account and keep your answer as is. I'm not going to upvote it either way.
    – mustaccio
    Commented Sep 30, 2020 at 20:22
  • @mustaccio, there is an answer from the OP where he "found a solution" to your question. So the question he still had was: «I am still not sure why mariadb was giving me the error "INSERT is not insertable-into" and I cold not find any documentation that explains this was a mariaDB problem. If anyone has more information about this please share». That's what the title of the topic says, too. So I answered exactly that question, hence my answer should be upvoted. You are free to write your own answer to whatever problem seems to you to be there.
    – chang zhao
    Commented Oct 1, 2020 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.