Mariadb – The target table user of the INSERT is not insertable-into MariaDB 10.4.11

mariadb

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

Best Answer

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

Related Solutions

Mysql – Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

I experimented with this and found something very interesting.

Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino.

If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put what I put in my my.cnf at the end.

Could you test this for your 3 scenarios (CREATE, TRUNCATE & DELETE) running your server using the new my.cnf. If you obtain similar results to mine, then we can investigate further.

Strangely, as far as I can see, the client reports a time of approx 0.3 seconds for me for all of my experiments - but the results from the profiling seem very different. Still puzzling through this one.

Below are my results just for the CREATE table statement before and after enabling the performance schema.

Before enabling P_S.

mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000290 |
| checking permissions | 0.000021 |
| Opening tables       | 0.000141 |
| creating table       | 0.365769 |  <<====
| After create         | 0.000037 |
| query end            | 0.000023 |
| closing tables       | 0.000018 |
| freeing items        | 0.000114 |
| cleaning up          | 0.000030 |
+----------------------+----------+
9 rows in set, 1 warning (0.00 sec)

After enabling P_S

mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000188 |
| checking permissions | 0.000131 |
| query end            | 0.000015 |
| closing tables       | 0.000020 |
| freeing items        | 0.000068 |
| cleaning up          | 0.000052 |
+----------------------+----------+
6 rows in set, 1 warning (0.00 sec)

MariaDB

Before enabling P_S

Query OK, 0 rows affected (0.33 sec)

MariaDB [test]> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000321 |
| checking permissions | 0.000024 |
| Opening tables       | 0.000073 |
| After opening tables | 0.000017 |
| System lock          | 0.000011 |
| Table lock           | 0.000019 |
| After opening tables | 0.000056 |
| creating table       | 0.328769 |  <<======
| After create         | 0.000043 |
| query end            | 0.000026 |
| closing tables       | 0.000022 |
| freeing items        | 0.000043 |
| updating status      | 0.000050 |
| cleaning up          | 0.000039 |
+----------------------+----------+
14 rows in set (0.00 sec)

After enabling P_S on MariaDB

MariaDB [test]> show profile for CREATE TABLE cache_bootstrap
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000311 |
| checking permissions | 0.000024 |
| Opening tables       | 0.000044 |
| After opening tables | 0.000038 |
| System lock          | 0.000014 |
| Table lock           | 0.000012 |
| After opening tables | 0.000347 |
| After create         | 0.000015 |
| query end            | 0.000014 |
| closing tables       | 0.000021 |
| freeing items        | 0.000014 |
| updating status      | 0.000026 |
| cleaning up          | 0.000034 |
+----------------------+----------+
13 rows in set (0.00 sec)

To be added in the [mysqld] section of my.cnf

[mysqld]

performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON

MySQL – GREATEST(timestamp, NOW()) Padding Results with Zeros

The MariaDB doku claims for NOW():

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar.

Try using timestamp literals:

SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() )

Or functional conversion:

SELECT GREATEST( STR_TO_DATE('2016-04-14 15:06:31','%Y-%m-%d %H:%i:%s'), NOW() )

Best Answer

Related Solutions

Mysql – Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

MySQL – GREATEST(timestamp, NOW()) Padding Results with Zeros

Related Question