CODEWITHSUNDEEP
mysqldatabaseaws-dms
Girish
Girish

Reputation: 1717

Error While Replicating Database using DMS

I am doing the DMS replication in prod and dev environment both, and my DMS of dev is running fine but getting below error in DMS prod replication.

[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1461 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-log]Can't create more than max_prepared_stmt_count statements (current value: 16382) [1022502] (ar_odbc_stmt.c:3644)

I can increase max_prepared_stmt_count at the source side, but unable to understand why it is running in DEV environment

Upvotes: 0

Views: 681

Answers (1)

Manoj Arya
Manoj Arya

Reputation: 133

There are multiple aspects needs to be looked for sure.

Who all are accessing your database? Is it only DMS connecting to you database or there are other services connect to it as well? What is nature of connections? Is it transient or are you using thread-pool?

Conceptually, when you connect to Mysql there are two types of prepared statements come into play. Server side and client side. Read about it more here on stack overflow. Prepared statements created are per connection and will live as long as connection is alive. The max_prepared_stmt_count system level property for server and is summation of across connections' statement count.

In case there are services connecting to the database in question, and if you are using JDBC MySQL driver the maximum cached prepared statements are 50 per connection (it varies as per driver, my mariaDB it's 250). Which means if you are using fixed size pool of 20, then you can have 20 * 50 = 1000 prepared statements. Please note, prepared statement uniqueness will only be per connection. Same query can be present across multiple connections. If you are not using pool, as soon as any connection is closed, it will flush out the associated prepared statements.

You should see JDBC url something like this for server side prepared statements to go up jdbc:mysql://host:port/db?useServerPrepStmts=true. One of the interesting thing is once prepared statements are maxed out, if the prepare fails, it falls back to a client prepared statement. So usually the error you listed will not be seen in case of such JDBC access code. It's just going to hamper the performance instead of benefiting as there is round trip involved now.

In case of DMS, I think its ODBC driver which is being used underneath where it uses primarily MySQL binary protocol, i.e. prepared statements.

So to conclude, one of these things might be happening. In dev you have lesser number of other service connection touching your mysql server causing that count lesser than max. Or if there are no services in play, the amount of data comes into picture for production case might be high. DMS tasks will try to load table in batches and will require the prepared statements to be created, if not found will fail.

Upvotes: 1

Related Questions