"PROCEDURE my_user.TruncateDirtyTables does not exist" on MySQL 8.0+ #249

Closed jamisonbryant closed 2 months ago

Description

Hi, I am running my test suite in a GitLab CI pipeline using an attached MySQL service container. The service container gets its image directly from Docker Hub. My whole entire test suite passes when using the mysql:5.7 docker image:

OK, but some tests were skipped! Tests: 3816, Assertions: 8341, Skipped: 108.

But when switching to the mysql:8 image, many tests fail with this error:

RuntimeException: SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE my_user.TruncateDirtyTables does not exist

I also tried using mysql:8.0 and mysql:8.4 and got the same result.

Expected outcome

The TruncateDirtyTables sproc gets created in the background before/as the test suite runs, just like it did in MySQL 5.7.

Things I tried

Investigated MysqlTriggerBasedTableSniffer::createTruncateDirtyTablesProcedure() and nothing looks amiss there
Using the root user in my database connection configuration, as opposed to the non-root user
Reproducing the problem locally: cannot reproduce :(

I have my local Docker MySQL container configured with these engine settings, but I have no idea if they affect sproc creation, as they're mostly for legacy support of some of our other apps:

[mysqld]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
default_authentication_plugin = mysql_native_password
log_bin_trust_function_creators = 1
bind_address = 0.0.0.0

Questions

Did anything change between MySQL 5.7 and 8.0 with regard to creating sprocs? I have already looked into this a little bit and nothing stood out to me, but I'm not very proficient in MySQL internals.
I'm using Cake 5 and PHP8. Do I still have to use test-suite-light and if so, why? Can I use one of the other built-in fixture manager strategies?

Moved to https://github.com/vierge-noire/cakephp-test-suite-light/issues/68

vierge-noire / cakephp-fixture-factories