Choosing a binary log format is very important. The replication format can greatly be affected by this choice. Also, if the STATEMENT format is used, the developers should be aware of its important limitations.
To choose the binary log format, we can set the binlog_format variable in the configuration file or the --binlog-format startup option. If not specified, the format defaults to STATEMENT. We can also obtain the currently used format by querying the @@binlog_format server variable.
Note
The @@binlog_format variable is a dynamic variable that exists at both the global and session level. This means that it is possible to change the binary log format on the master while it is running, for all connections or only for the current connection. However, this should never be done. Changing the binary logging format on the master might cause the replication to fail, or it may cause an unexpected behavior. For this reason, changing the value of @@binlog_format, even at session level, requires the SUPER privilege. However, changing this variable on a slave is always safe.
Note that it is not possible to change the value of @@binlog_format within a stored program.
The master and the slaves do not need to use the same binary log format.
The
STATEMENT format is the most ancient binary log format. When this format is used, the log contains the queries that could modify data. Statements such as UPDATE, DELETE, or REPLACE with a WHERE clause sometimes do not modify any row. However, these statements will still be written to the log. Statements such as SELECT are never written to the log, because they cannot modify any data.
The STATEMENT format has an important limitation: only deterministic statements should be sent to the server. Deterministic means that, if they are executed twice on identical databases, they must necessarily have the same effects. Of course this limitation does not apply to the statements that do not modify data, such as SELECT queries, because they are not logged and do not affect the replicated data. If a statement uses the current timestamp, the current user, or random data, they will produce different results on the slaves.
If the STATEMENT format is in use, MariaDB tries to detect the unsafe statements and produces the following warnings:
Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
However, MariaDB does not check if the values assigned to user variables are deterministic or not. Thus, when using variables, it is easy to insert the non-deterministic data without getting any warning:
MariaDB [test]> SET @a = RAND(); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> CREATE TABLE example ENGINE = InnoDB SELECT @a; Query OK, 1 row affected (0.50 sec) Records: 1 Duplicates: 0 Warnings: 0
If stored programs are used, special attention should be paid to these kinds of mistakes.
On the other hand, the heuristics used by MariaDB to detect non-deterministic statements are a bit too pessimistic. In other words, since the algorithms used here do not properly analyze some statements, the 1592 warning is produced for some deterministic statements. This is a well-known problem that can cause the error log to grow too quickly. In extreme cases, this could be a good reason to switch to the MIXED format.
Here is a list of the possible causes why a statement is not deterministic:
- Non-deterministic functions, such as
RAND()orUSER(). Note thatFOUND_ROWS(),ROW_COUNT(), andLOAD_FILE()are considered non-deterministic. - User-defined functions (functions written in the C language and installed in the server).
- References to server variables. Their value can differ on slaves. There are some exceptions but, since they are currently not documented, we must rely on our logic to find them, or simply avoid referencing server variables at all.
- An
UPDATEstatement with aLIMITclause (even if a deterministicORDER BYis specified; this is a known bug). - An
AUTO_INCREMENTcolumn is modified, and the statement causes a trigger to be executed or calls a stored function. - An
AUTO_INCREMENTvalue is automatically generated, and it is not the first column in the primary key (this is rare and probably not efficient). - The
LOAD DATA INFILEstatement was not considered safe before Version 10.0. - The
INSERT DELAYEDcommand against a MyISAM table (other storage engines ignore theDELAYEDclause). - A system table is involved in a statement. Tables in any system database (
mysql,information_schema, orperformance_schema) can store different data on different servers, even if the servers received the same statements.
Only READ COMMITTED and READ UNCOMMITTED can be used with the STATEMENT format. The reason is that, with REPEATABLE READ and SERIALIZABLE, the order of statement execution can sometimes depend on the statement's execution time. This happens because a statement that uses locks can delay the execution of other statements.
Note that some non-deterministic functions are safe. The reason is that the binary log contains the information necessary to replicate them exactly. For example, date or time functions are safe because the binary log stores a timestamp for each statement. The safe non-deterministic functions are:
DATABASE(),SCHEMA()CONNECTION_ID()LAST_INSERT_ID()CURDATE(),CURRENT_DATE()CURTIME(),CURRENT_TIME()CURRENT_TIMESTAMP(),UNIX_TIMESTAMP(),NOW()UTC_DATE(),UTC_TIME(),UTC_TIMESTAMP()LOCALTIME(),LOCALTIMESTAMP()
Note
The SYSDATE() function is the only unsafe time function. The reason is that it returns the exact time of the function call, not the time of the statement execution. Multiple SYSDATE() calls in the same query can return multiple different values. It is thus impossible to replicate it obtaining the same return value.
Note that these limitations apply to the statement-based binary logging itself. Even if replication is not used, the binary log should never contain non-deterministic statements using this format. This would make backups unreliable.
As mentioned previously, if the ROW format is used, the binary log contains the changes to the data. There are, however, some exceptions: some operations are written to the log in a different way. These exceptions are the following:
- DDL statements, such as
CREATE TABLEandDROP TABLE. - Statements that implicitly modify tables in the
mysqldatabase. This includes statements such asCREATE USERorGRANT, but not statements that explicitly modify system tables, such asINSERTorUPDATE. - Statements that involve temporary tables.
These exceptions are logged as statements, as if the STATEMENT format was in use. The CREATE … SELECT clause is logged in a different way: the CREATE TABLE part of the command is written as a statement and is logged as a statement, but the selected data is logged as rows.
The main advantage of this format is that it does not have the limitations of the STATEMENT format: all statements are safe with the ROW format. Since data modifications are logged, it is irrelevant if the statements that produced the changes were deterministic or not. Any isolation level can be used with the ROW format, including READ COMMITTED and READ UNCOMMITTED.
Also, the slaves do not need to execute the SQL statements. Executing statements can include extra work such as the grouping or ordering of the rows, the execution of functions, and so on. Applying changes to the rows is a simple operation for the slaves. If complex statements are executed on the master, this is an important advantage.
However, there is also an important disadvantage: if a statement modifies many rows, the ROW format requires much more data to be written into the log. This can make the binary log too large. If replication is used, the master will need to send much more data to the clients.
However, the ROW format is more compact for data itself. Usually, the data that need to be written into a database are written in an INSERT or UPDATE statement. If many data are inserted this way, the STATEMENT format could result in a bigger binary log. Also, the STATEMENT format is not efficient for workloads consisting of many statements that only modify a single row.
While the STATEMENT format has many limitations, the ROW format is less efficient for the majority of workloads. For this reason we may want to use the MIXED format, in most cases. When it is used, most statements are logged with the STATEMENT format. When a non-deterministic statement is detected, it is logged using the ROW format. The logging of binary data performed by the MIXED format is called
binary injection. Note that only the READ COMMITTED and READ UNCOMMITTED isolation levels can be used with the STATEMENT or MIXED format. This is because, with the REPEATABLE READ format, the duration of locking transactions can affect the order of execution of other statements. This can cause discrepancies between the slaves and the master.
Stored routines (procedures and functions) are only safe if they are deterministic and do not modify any data. Thus, when the binary log is enabled, MariaDB prevents the creation of stored functions if they are not declared with the NOT DETERMINISTIC clause, or one of the READS SQL DATA, CONTAINS SQL, or NO SQL clauses. To force their creation, it is possible to set the @@log_bin_trust_function_creators variable. However, such statements will not be safe with the STATEMENT format.
Triggers cannot be declared as DETERMINISTIC or NOT DETERMINISTIC. However, if they contain non-deterministic statements, they are not safe.
Also, we must remember that SQL statements can behave differently if permissions are different. The slave executes all the statements with a user who has all the privileges. If we are not definitely sure that the stored programs always have the rights to perform all the operations they attempt on the master, we should not log them with the STATEMENT format.