crossplane-contrib / crossplane-contrib/provider-sql

MySQL: Databases not synced because of max_prepared_stmt_count

Open
#220 1 comment 1 reaction 0 assignees View on GitHub
bug
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.