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

[jamisonbryant]

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 with the Fixture Factories plugin. 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?
• If an error occurs when createTruncateDirtyTablesProcedure() is called, is that logged anywhere that's visible? If not, can we log the result of that query somewhere for debugging purposes?

[jamisonbryant]

I created a new pipeline job whose only job was to connect to the MySQL server and create the sproc:

apt-get update
apt-get install -y -q mariadb-client libmariadb-dev
mysql -h $DB_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD < ./bin/scripts/pipeline/truncate_dirty_tables_sproc.sql

And this is the error I get from that:

ERROR 2061 (HY000): RSA Encryption not supported - caching_sha2_password plugin was built with GnuTLS support

This appears to be a MySQL 8-specific problem, see this StackOverflow question. Still not sure what the underlying issue is, but this might be a hint.

Update: Disregard, this was due to a mismatch in the MySQL/MariaDB versions between the Docker image and the packages I was trying to install.

[jamisonbryant]

I was able to get my query test job to run successfully by adding a DELIMITER // and END // to the SQL file containing the sproc. I know that's not how it's done in the code, but I was just trying to find out if there was a syntax difference between MySQL 5.7 and 8.0 causing my issue. I'm uploading the new sproc SQL to this comment.

truncate_dirty_tables_sproc.sql.txt

Update: Unfortunately this did not resolve the issue in my PHPUnit test suite jobs, although they are still using the same hardcoded SQL as before.

[pabloelcolombiano]

Hi,

in the Passbolt API, we also run the test suiteon Gitlab CI, with the following cnfiguration.

I never had a MySQL 8 specific issue, so could this be due to your pipeline configuration?

[jamisonbryant]

log_bin_trust_function_creators = 1

I believe this was the key. I noticed this line was in my mysql.cnf as well as your GitLab pipeline. Once I put it in our GitLab pipeline, we no longer get the SQL errors. So thank you for sharing that :)

I checked the test-suite-light documentation and I don't see any mention of this configuration setting. I will submit a PR to update the README if that's all right.