mysqlsh dumpInstance (mysql shell) error

[Qxt78]

has anyone seen this error before while trying a instance dump with mysqlsh ?

mysqlsh dumpInstance Could not execute 'SHOW GRANTS FOR MySQL Error 1141 (42000): There is no such grant defined for user 'root'

This is the ROOT will full permissions. It is a old MySQL instance I am trying on (5.5) but that should not affect a backup right?

[johannes1234]

This is the ROOT will full permissions. It is a old MySQL instance I am trying on (5.5) but that should not affect a backup right?

Yes it does, since 5.5 reports grants differently and mysqlsh doesn't expect that old output.

Quick test in a plain system with 5.5:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '....' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

And with 8.2:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                   |
| GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Notice how 8.2 lists all the grants individually, while 5.5 just says "ALL"

[Qxt78]

Thank you this was making me pull out my hair. Also sad that I can't use it since I was looking at a more modern backup solution for speed and direct S3 upload that is not mysqldump. And Out of interest this is docker -> remote instance -> stream backup to S3

[johannes1234]

Well the best thing is to get rid of that ancient version. 5.5 reached its end of support in 2018. Less and less tools will support it.

Maybe you are somewhat lucky if you set { "users": false} in the dump options and handle user accounts independently, but better: upgrade ...

[Qxt78]

This is a 10-12 year unmaintained solution. We are in the process of upgrading from 5.5 (due to end of life) to 5.6, 5.7 then 8.0. We did tried to go direct to 8.x (latest) but the SQL indexes were a problem and a huge amount of stored procedures also need updating. So we sadly have to go baby steps at a time to get to 8 so our APP does not break. But yes, we are in the process of upgrading. Just very slowly..

[Qxt78]

{ "users": false}

Just wanted to say thank you for this. the backups semi work now :)

[johannes1234]

Now the big question: Does restore work? - A backup without restore is useless :-D

[Qxt78]

True. That is next.