MySQL Grants for Stored Procedures

General Discussion
1

I have been attempting to track Stored Procedure changes in a MySQL DB using our Liquibase Pro trial. Not sure what else to try at this point, any and all ideas are appreciated!

The Problem:

The command liquibase generate-changelog --changelog-file=changelog.mysql.sql is returning a changeset reading [ CANNOT READ OBJECT BODY ] for a stored procedure

Database: MySQL Server 8.0.32 running locally on Windows 10 Business

Attempted resolutions:

  1. Granted all recommended permissions to the current user per this article

  2. Created a new MySQL user to match the authors name displayed in the changeset and granted all permissions from #1

  3. Verified that the liquibase.properties file is set to the correct user

  4. Other changesets are pulling through, so I believe the user information is accurate for this file

  5. Ensured that the Stored Procedure ‘Definer’ matches the MySQL user with recommended permissions

  6. Changed the default object editor delimiter in work bench to ‘//’ from ‘$$’

liquibase.config

# Enter the path for your changelog file.
changeLogFile=changelog.mysql.sql

# Enter the Liquibase Pro trial license key
liquibase.licenseKey: REMOVED FOR POST
                                                                        

#### Enter the Target database 'url' information  ####
liquibase.command.url=jdbc:mysql://localhost:3306/liquibase_demo_dev

# Enter the username for your Target database.
liquibase.command.username: root

# Enter the password for your Target database.
liquibase.command.password: 123456

#### Enter the Source Database 'referenceUrl' information ####
## The source database is the baseline or reference against which your target database is compared for diff/diffchangelog commands.

# Enter URL for the source database
liquibase.command.referenceUrl: jdbc:mysql://localhost:3306/liquibase_demo_test

# Enter the username for your source database
liquibase.command.referenceUsername: root

# Enter the password for your source database
liquibase.command.referencePassword: 123456

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 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_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION

GRANT ALL PRIVILEGES ON `liquibase_demo`.* TO `root`@`localhost` WITH GRANT OPTION

GRANT ALL PRIVILEGES ON `liquibase_demo_dev`.* TO `root`@`localhost` WITH GRANT OPTION

liquibase_demo db schemas

image

2

Update:

I’ve attempted to use the generate-changelog command and view the objects directory. The object is appearing as null. Additionally, I’ve ensured that the generated file is not using formatted SQL as outlined in the docs.

Will continue to pursue other ideas.