Azure MySQL Flexible version 8

Question

I am investigating Azure DB MySQL Migration from on-prem to Azure and I am currently preparing some test scripts that can be run before and after.

I have been following the Migration documentation,

https://learn.microsoft.com/en-us/azure/mysql/migrate/mysql-on-premises-azure-db/08-data-migration#database-objects

and have had success running the above stored procedure OK against on-prem MySQL 5.6.28. However, when I run the stored procedure against the Azure MySQL Flexible version 8, I have issues with the below PREPARE statement.

I get an the following error,

Error Code: 1045. Access denied for user 'dbadmin'@'%' (using password: YES)

Does anyone know, if I can run PREPARE statement(s), or is this a super-privilege constraint, of which I will have to create a workaround.

Any guidance would be grateful.

OPEN curTableNames;
                
                getTableName: LOOP
                        FETCH curTableNames INTO tableName;
                        IF finished = 1 THEN
                              LEAVE getTableName;
                        END IF;

				SET @s = CONCAT('SELECT COUNT(*) into @TableCount FROM ', schemaName,'.', tableName);
				
					#SELECT @s;
				 	PREPARE stmt FROM @s;
					EXECUTE stmt;
					INSERT INTO MIG_INVENTORY (REPORT_TYPE,OBJECT_NAME, OBJECT_TYPE, COUNT)

					SELECT
						'TABLECOUNT', tableName, 'TABLECOUNT', @TableCount;
        
					DEALLOCATE PREPARE stmt;

				END LOOP getTableName;
				CLOSE curTableNames;

Answer 1

Hi @Duncan House,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

Why do I get access denied when I run a stored procedure with an SQL command of "PREPARE stmt FROM @s;

If you remove this command from the stored procedure it runs fine with no access issues.

Solution:

I agree with your response and understand the permission constraints of Azure MySQL Flexible for super privilege SQL commands etc.

Therefore, it is a recommended, to consider a different approach to retrieve the same information, which I have done with success.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.