search

hashicorp / terraform-provider-mysql

Terraform MySQL provider – This Terraform provider is archived per our provider archiving process: https://terraform.io/docs/internals/archiving.html
https://www.terraform.io/docs/providers/mysql/
Mozilla Public License 2.0
61 stars 189 forks source link

Can't create more than max_prepared_stmt_count statements #118

Closed mohsen0 closed 4 years ago

mohsen0 commented 4 years ago

On big production servers that are heavily in use, the default maximum number of prepared statements is 16382. it seems this provider does not close its connection frequently enough and prepare too many queries that hit that limit randomly and very frequently on our servers.

Terraform Version

Terraform v0.12.20

Affected Resource(s)

mysql_database, maybe all other resources too since it is related to the database connection.

Debug Output

# terraform import module.db_service_users.mysql_database.prod_database prod_database
.
.
.
module.db_service_users.mysql_database.prod_database: Importing from ID "prod_database"...
module.db_service_users.mysql_database.prod_database: Import prepared!
  Prepared mysql_database for import
module.db_service_users.mysql_database.prod_database: Refreshing state... [id=prod_database]
2020-03-05T10:35:31.667Z [DEBUG] plugin.terraform-provider-mysql_v1.8.0_x4: 2020/03/05 10:35:31 [DEBUG] Waiting for state to become: [success]
2020-03-05T10:35:31.739Z [DEBUG] plugin: plugin process exited: path=/tmp/build/f8d447e2/git_resource/providers/prod_database-prod/.terraform/plugins/linux_amd64/terraform-provider-random_v2.2.1_x4 pid=787
2020-03-05T10:35:31.739Z [DEBUG] plugin: plugin exited
2020-03-05T10:35:31.742Z [DEBUG] plugin: plugin process exited: path=/tmp/build/f8d447e2/git_resource/providers/prod_database-prod/.terraform/plugins/linux_amd64/terraform-provider-kubernetes_v1.11.0_x4 pid=769
2020-03-05T10:35:31.742Z [DEBUG] plugin: plugin exited
2020-03-05T10:35:31.779Z [DEBUG] plugin.terraform-provider-mysql_v1.8.0_x4: 2020/03/05 10:35:31 Executing query: SHOW CREATE DATABASE `prod_database`
2020/03/05 10:35:31 [ERROR] module.db_service_users: eval: *terraform.EvalRefresh, err: Error getting default charset: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382), latin1
2020/03/05 10:35:31 [ERROR] module.db_service_users: eval: *terraform.EvalSequence, err: Error getting default charset: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382), latin1
2020/03/05 10:35:31 [DEBUG] [aws-sdk-go] DEBUG: Request dynamodb/GetItem Details:
---[ REQUEST POST-SIGN ]-----------------------------
POST / HTTP/1.1
Host: dynamodb.eu-west-1.amazonaws.com
User-Agent: aws-sdk-go/1.25.3 (go1.12.13; linux; amd64) APN/1.0 HashiCorp/1.0 Terraform/0.12.20
Content-Length: 209
Accept-Encoding: identity
Authorization: AWS4-HMAC-SHA256 Credential=ASIAVD5SDU77O2TW7OWC/20200305/eu-west-1/dynamodb/aws4_request, SignedHeaders=accept-encoding;content-length;content-type;host;x-amz-date;x-amz-security-token;x-amz-target, Signature=f4c25fb5a9ef997ee76acbe3c52c4d3ef0828ad1f6e775e179fcb6d517723aa5
Content-Type: application/x-amz-json-1.0
X-Amz-Date: 20200305T103531Z
X-Amz-Security-Token: FwoGZXIvYXdzEKz//////////wEaDOnkstYCCwHpi+N8GCKuAdRPYjvnDsamk7zC4TgmO5HHrjHU89NFnJkO5FnpElVLFB75WJ+w85UF/yTgrP/ZBhRV9OZW3vC/mZD7KFaU/TSGK5xScHQz11b4Byx4C6SOlZTFQNmqiktuLtJogOkj9HRDHFgv3rY6fdVwGv7Pdgz5ZZXqenTJfY77ax/emwVdQdofjs+GOZ4QEjxVbJIqve9mewGGMjNMFb6UB5dv7Or1CABN9GswXucg6PVJ8CiJoIPzBTItfwJkre6ym3i/fV309UEF/tvzNQU1I9WPA5PdHHnsSpi6KxjmhcKoy/tiPRfd
X-Amz-Target: DynamoDB_20120810.GetItem

{"ConsistentRead":true,"Key":{"LockID":{"S":"prod_database-management-terraform-state/mysql/prod_database-prod/terraform.tfstate"}},"ProjectionExpression":"LockID, Info","TableName":"prod_database-management-terraform-state-lock"}
-----------------------------------------------------

Error: Error getting default charset: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382), latin1

This error happens randomly on running apply. since the max_prepared_stmt_count is a server-wide limit that potentially could cause problems for other applications who want to prepare statements.

show global status like 'com_stmt%';

'Com_stmt_execute', '6506784240'
'Com_stmt_close', '185741724'
'Com_stmt_fetch', '0'
'Com_stmt_prepare', '199476683'
'Com_stmt_reset', '85496'
'Com_stmt_send_long_data', '85846'
'Com_stmt_reprepare', '126'

show variables like 'max_prepared_stmt_count';

'max_prepared_stmt_count', '16382'

Expected Behavior

Import happens it does happen with the same error on apply.

Steps to Reproduce

Run the terraform apply or terraform import a database resource in a busy server.

mohsen0 commented 4 years ago

I think this is a problem with our side. so closing this.

SHOW GLOBAL STATUS like 'Prepared_stmt_count';
'Prepared_stmt_count', '16382'