Guys, I changed DB to Mariadb and uploaded backed up data bases, and now I’m trying to update users and I get this error:
SQL select * from user where user = '' failed : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Does anyone know how to fix this issue, please?. Thanks
It won’t even let me create new databases…
Failed to create database : SQL select distinct host from user where user = ? failed : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Which version was your MySQL and what version of MariaDB were you trying to migrate to? Also, how did you migrate them?
I don’t remember the version. It was installed default with VM, and I removed it and installed latest Maria…
Based on your OS version, I am assuming you were using MySQL v8.0.xx
Unfortunately, the migration from MySQL v8.0+ to MariaDB isn’t as straightforward anymore and you have to use the mysqldump utility to move the database over.
This is clearly stated in the official MariaDB Docs which also has some pointers on how to migrate from MySQL v8.0+ to MariaDB.
That’s good to know. I actually didn’t do this myself. I’ll look into it. TY
I would recommend moving your backup to a test server or spare server that uses MySQL, make sure the backup works fine and then go ahead with migrating the database, but this time, using mysqldump as shown in the MariaDB Docs.
Great. Thank you for you help
Is there a way to do this in VM?.. I was always told to these things using VM… TY
No, there aren’t any built-in Virtualmin feature for migrating MySQL to MariaDB (AFAIK).
I do understand that there is a slight increase in performance with MariaDB but I would personally recommend sticking with the database shipped with your OS though (here, MySQL v8.0.xx) unless you are good at and willing to debug weird compatibility issues later down the road.
Too many issues with applications… Mariadb seems to be smooth without hassles…
I am trying to back db in VM and I keep getting this error: Backup failed : Backup failed - try again to a file on the server to see the full error message…
Does that mean VM doesn’t do backups either?.. TY
Never mind. I was able to make it go, however, I’m faced with the same exact issue.
backup of mysql failed : mysql::execute_sql failed : SQL select distinct host from user where user = ? failed : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
What are some of these issues that you see in MySQL and not in MariaDB?
Other than the slight performance difference, the latest MySQL v8.0+ is quite efficient as well if tweaked correctly (just as you have to tweak MariaDB to make it efficient).
If you are concerned with the slight performance difference, pair your MySQL database with a memory cache system like MemCached or Redis and you should see positive results with regards to database performance.
P.S. You can DM if you would like me to configure this on your server(s) for you.
My issue was creating this new server using Ubuntu and coming off Centos 7. We uploaded the back up database onto 8 and nothing worked correctly. We replaced it with Mariadb and it started working correctly until I tried creating a new user, and this is where we’re stuck now. I have a backed up copy of the complete dump. Can I delete all DB from Phpmyadmin and re-upload using mysqldump? Db’s are not my thing…
That seems extremely unlikely. I think you’re just making your life harder here, and I don’t recommend doing it (it seems you’re already committed to the project, but hopefully others reading this thread can be dissuaded from doing drastic things for no good reason).
It seems the only way to make all this happen is by creating all websites new from scratch without moving any databases… This project has been a total nightmare. How would you go about this?..
I fixed the entire thing by filtering and uploading one site at a time…
This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.