Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SQL Error (1356): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

nat's profile image nat posted 1 week ago in General Permalink

I am in the process of upgrading my MariaDB from 10.1 to 10.6 on a Windows Pro 10 server.

Because it is such a big version jump I though I would be best to install a new instance -- export all data -- import all data -- and finally add all users and their privileges by hand. These are my steps:

To export I used:

mysqldump --port=3306 -u root -p --all-databases --databases > "D:\backup.sql"

I imported I used:

mysql --port=3307 -u root -p < "D:\backup.sql"

I got some errors, so did:

mariadb-upgrade-service.exe --service=MariaDB

The log file states:

mysql.user                                         OK
sys.host_summary
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
error    : Corrupt
sys.host_summary_by_file_io
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
error    : Corrupt
sys.host_summary_by_file_io_type
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
error    : Corrupt
sys.host_summary_by_stages
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
error    : Corrupt

Checking the new database using HeidiSQL 11.3.0.6369 the imported data looks OK, but going to use User Management -- I get this error:

SQL Error (1356): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Is HeidiSQL User Management no longer supported in this MariaDB version, or did I somehow corrupt the User Management mechanism in MariaDB 10.6?

ansgar's profile image ansgar posted 1 week ago Permalink

You were using "--all-databases" with no exception, that broke some tables and views in the "mysql" system database. Newer versions differ in the structure of some tables, mysql.proc for example, as well as mysql.user.

I would recommend to redo the export, this time ignore system databases:

mysqldump ... --all-databases --ignore-database=information_schema --ignore-database=mysql > yourfile.sql
nat's profile image nat posted 1 week ago Permalink

Thank you so very much for that helpful info.

Is the new "mysql" system database structure supported by the HeidiSQL user management tool? I really like that UI.

ansgar's profile image ansgar posted 1 week ago Permalink

HeidiSQL's user manager does not direct queries on the mysql database. Instead it works with SHOW GRANTS FOR... and GRANT xy TO yz commands. So the structure of that database should not matter here.

nat's profile image nat posted 1 week ago Permalink

Am I understanding right? In other words, I can still use the UI that controls the user settings in HeidiSQL -- even on this new structure. Right?

nat's profile image nat posted 1 week ago Permalink

I see it is working. Nice.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.