crossplane-contrib / crossplane-contrib/provider-sql
MySQL: Databases not synced because of max_prepared_stmt_count
- Dominant language
- Go
- Stars
- 154
- Forks
- 119
- Avg merge
- 6d 17h
- Merged PRs (30d)
- 8
Description
### 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: https://github.com/go-sql-driver/mysql/issues/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
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in pkg/clients/mysql/mysql.go at the sql.Open() calls and compare the MySQL driver's important settings with the reported max_prepared_stmt_count error. Investigate how the provider opens connections for Databases, Users, and Grants, then verify that production-like workloads no longer produce the error and those resources become SYNCED=True.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 28/100