MySQL: Databases not synced because of max_prepared_stmt_count

[jscheytt]

What happened?

We are using MySQL Databases, Users, and Grants resources to manage them in a Percona XtraDB operated MySQL server. (Nothing fancy, around 60 resources for each of the 3 types.)

All non-prod environments work fine, but in our production environment, the all Databases, Users, and Grants become ready (as indicated by the READY=True column) but Databases and Users do not become synced (as indicated by the SYNCED=False column). The events of the resources show e.g. this error:

cannot select database: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)

We have not tried increasing this value yet because we found out the following:

1. This issue in go-sql-driver/mysql indicates that others have run into this problem before as well: Resource leakage: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382) go-sql-driver/mysql#701
2. Their maintainers' recommendation (I simplify to what I have grasped) is that you should definitely set these "Important settings": https://github.com/go-sql-driver/mysql?tab=readme-ov-file#important-settings
3. I don't see these anywhere in the code. I suppose they should probably happen here (in all calls of sql.Open()? https://github.com/crossplane-contrib/provider-sql/blob/33fb91cad4fe5172224bd38e7ace3c5e30a12e2c/pkg/clients/mysql/mysql.go#L78C12-L78C20

Can we work towards implementing these "important settings"? I am happy to contribute if possible.

How can we reproduce it?

Sorry, I don't have very concrete steps for reproduction.
Probably try a stressed MySQL server with lots of data and connections?

What environment did it happen in?

• Crossplane version: v1.19.2
• Crossplane SQL Provider version: v0.11.0
• Cloud provider: Hetzner
• Kubernetes version: v1.30.6+k3s1

[chlunde]

Based on the threads, it seems connections are reused and only when fully closed they free prepared statements, is that right? So to ensure connections are not reused forever we must use SetConnMaxLifetime. Could you try that in https://github.com/crossplane-contrib/provider-sql/blob/master/pkg/clients/mysql/mysql.go

Maybe you could verify it with a command like this:

mysql -uroot -e 'SHOW PROCESSLIST' 

https://pkg.go.dev/database/sql#DB.SetConnMaxLifetime