Skip navigation links

Additional languages
5.2.4.4 Logging Format for Changes to mysql Database Tables »
Section Navigation      [Toggle]

5.2.4.3. Mixed Binary Logging Format

When running in MIXED mode, automatic switching from statement-based to row-based replication takes place under the following conditions:

  • When a DML statement updates an NDB table

  • When a function contains UUID()

  • When 2 or more tables with AUTO_INCREMENT columns are updated

  • When any INSERT DELAYED is executed

  • When the body of a view requires row-based replication, the statement creating the view also uses it — for example, this occurs when the statement creating a view uses the UUID() function

  • When a call to a UDF is involved

  • If a statement is logged by row and the client that executed the statement has any temporary tables, then logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that client are dropped

    This is true whether or not any temporary tables are actually logged

    Temporary tables cannot be logged using the row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe, and we approximate this condition by treating all statements made by that client as unsafe until the client no longer holds any temporary tables

  • Beginning with MySQL 6.0.4:

Note

A warning is generated if you try to log execute a statement in statement-logging mode that should be logged in row-logging mode. The warning is shown both in the client (in the output of SHOW WARNINGS) and through the mysqld error log. A warning is added to the SHOW WARNINGS table each time a statement is executed. However, only the first statement that generated the warning for each client session is logged to the mysqld error log to prevent flooding the error log.

In addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The following table lists the logging formats supported by each storage engine:

Storage Engine Row Logging Supported Statement Logging Supported
ARCHIVE Yes Yes
BLACKHOLE No Yes
CSV Yes Yes
EXAMPLE Yes No
Falcon Yes No
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes Yes
MyISAM Yes Yes
MERGE Yes Yes
NDB Yes No

A given storage engine can support either or both logging formats; the logging capabilities of an individual engine can be further defined as follows:

  • If an engine supports row-based logging, then the engine is said to be row-logging capable.

  • If an engine supports statement-based logging, then the engine is said to be statement-logging capable.

When determining the logging mode to be used, the capabilities of all the tables affected by the event are combined. The set of affected tables is then marked according to these rules:

  • A set of tables is defined as row logging restricted if the tables are row logging capable but not statement logging capable.

  • A set of tables is defined as statement logging restricted if the tables are statement logging capable but not row logging capable.

Once the determination of the possible logging formats required by the statement is complete it is compared to the current BINLOG_FORMAT setting. The following table is used to decide how the information is recorded in the binary log or, if appropriate, whether an error is raised. In the table, a safe operation is defined as one that is deterministic. A number of rules decide whether the statement is deterministic or not, as shown in the following table (where RLC stands for “row-logging capable” and SLC stands for “statement-logging capable”):

Condition Action
Safe/unsafe BINLOG_FORMAT RLC SLC Error/Warning Logged as
Safe STATEMENT N N Error: not loggable  
Safe STATEMENT N Y   STATEMENT
Safe STATEMENT Y N Error: not loggable  
Safe STATEMENT Y Y   STATEMENT
Safe MIXED N N Error: not loggable  
Safe MIXED N Y   STATEMENT
Safe MIXED Y N   ROW
Safe MIXED Y Y   STATEMENT
Safe ROW N N Error: not loggable  
Safe ROW N Y Error: not loggable  
Safe ROW Y N   ROW
Safe ROW Y Y   ROW
Unsafe STATEMENT N N Error: not loggable  
Unsafe STATEMENT N Y Warning: unsafe STATEMENT
Unsafe STATEMENT Y N Error: not loggable  
Unsafe STATEMENT Y Y Warning: unsafe STATEMENT
Unsafe MIXED N N Error: not loggable  
Unsafe MIXED N Y Error: not loggable  
Unsafe MIXED Y N   ROW
Unsafe MIXED Y Y   ROW
Unsafe ROW N N Error: not loggable  
Unsafe ROW N Y Error: not loggable  
Unsafe ROW Y N   ROW
Unsafe ROW Y Y   ROW

When a warning is produced by the determination, a standard MySQL warning is produced (and is available using SHOW WARNINGS). The information is also written to the mysqld error log. Only one error for each error instance per client connection is logged. The log message will include the SQL statement that was attempted.

If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.