:A max_binlog_cache_size-related error occurs when a SQL statement for a large transaction is executed

Issue

The following error occurred while executing the SQL statement for large transactions.

Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

Cause

The max_binlog_cache_size parameter specifies the maximum number of binlogs that can be used by a single transaction. If the value is exceeded, the current error occurs.

Solutions

You can choose the following methods to make changes based on the actual situation:

• If this issue occurs when you previously used an ordinary data change ticket, we recommend that you try to use the lock-free data change feature to make the changes. For more information about this feature, see change lock-free data.
• Split SQL statements. You can split complex SQL statements, reduce the number of affected rows in SQL statements, and then commit them in batches to control the size of Binlog logs generated by SQL statements:
  • You can add a limit parameter after the SQL statement. For example, you can add a limit 1000 parameter after the SQL statement to limit the number of rows that are affected by the SQL statement.
  • You can submit multiple SQL statements for a data change request. That is, you can approve a ticket only once, but can split SQL statements into multiple SQL statements.
• If you want to clear data of an entire table, you can use the truncate table [$Table_Name]; statement.

  note :[$Table_Name] is the name of the table to be cleared.

• You can modify the values of the max_binlog_cache_size parameters based on the actual situation:
  • RDS instance:
    If you want to modify this parameter, see set instance parametersset max_binlog_cache_size parameters.
    
  • Self-managed databases:
    Log on to the database and refer to the following SQL statement to modify the value of this parameter.

    SET GLOBAL max_binlog_cache_size=[$Size];

    note: evaluate the Size of the [$Size] value based on your actual conditions.

    
    

Scope

• Data Management (DMS)
• ApsaraDB RDS