1

We have a MySQL installation that has mysql.user table a VIEW. So, MySQL is not starting properly.

We are now running it with skip grant tables. Is there any option to convert the view to BASE TABLE or create user table with the data?

All other tables are showing type BASE TABLE

SHOW FULL TABLES IN mysql WHERE TABLE_TYPE LIKE 'VIEW';

| Tables_in_mysql | Table_type |
+-----------------+------------+

| user            | VIEW       |
+-----------------+------------+
1 row in set (0.001 sec)

Not sure how this happened. Any help would be appreciated.

Thanks

2
  • A an experiment - SHOW CREATE VIEW mysql.user;?
    – Akina
    Jun 2, 2021 at 11:13
  • The mysql.user was already view. I sorted it by installing same version of MySQL in local machine and exported the mysql table. Then dropped the view on the live server and imported the new mysql database. THIS WAS POSSIBLE ONLY BECAUSE THERE WAS ONLY ONE EXTERNAL USER. STILL NOT SURE WHAT TO DO IF THERE WERE MANY OTHER USERS
    – prime999
    Jun 3, 2021 at 11:25

1 Answer 1

1

I believe that the root cause of this is having dumped data from MariaDB 10.4 (or later) and then imported it into MariaDB 10.3.

In MariaDB 10.3, mysql.user is a table. In MariaDB 10.4, mysql.user is a view.

That probably doesn't solve your problem but at least it explains the root cause. Hope it helps.

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.