Rolling back call to stored procedure in MySql

Question

I have a scenario where I need to insert, call and drop a stored procedure in several databases. This is done in a powershell script. I want to be able to roll back the changes made by the stored procedure if some situations occur further down the in the script.

The problem is that I'm not able to roll back the changes made by the stored procedure. The stored procedure is almost 6 KB and does a number of updates and inserts, so I'm wondering if maybe it is too much for the transaction. When only doing the SP call in the transaction, I can roll it back.

Any help would be greatly appreciated.

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand('', $mysqlConn)
$cmd.Transaction = $mysqlConn.BeginTransaction([System.Data.IsolationLevel]'ReadCommitted')

$cmd.CommandText = [IO.File]::ReadAllText($mySqlStoredProcedurePath)
[Void]$cmd.ExecuteNonQuery()

$cmd.CommandText = "CALL storedProcedureX($startHierarchyId)"
[Void]$cmd.ExecuteNonQuery()

$cmd.CommandText = "DROP PROCEDURE IF EXISTS storedProcedureX"
[Void]$cmd.ExecuteNonQuery()

$cmd.Transaction.Rollback()

Answer 1

Thanks to MJB for providing the clue to the answer: Implicit commits

Beginning with MySQL 5.0.13, ALTER PROCEDURE, CREATE PROCEDURE, and DROP PROCEDURE cause an implicit commit.

Answer 2

Oracle used to do an implicit COMMIT every time that you issued a DDL statement, such as CREATE PROCEDURE, CREATE TABLE, or DROP TABLE. Perhaps MySQL does that as well? I would check into it, especially since we now know that to be the solution (see timestamp of comments above).