Rechercher dans le manuel MySQL

5.4.4.3 Mixed Binary Logging Format

Inhaltsverzeichnis

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:

In earlier releases, when mixed binary logging format was in use, if a statement was logged by row and the session that executed the statement had any temporary tables, all subsequent statements were treated as unsafe and logged in row-based format until all temporary tables in use by that session were dropped. As of MySQL 8.0, operations on temporary tables are not logged in mixed binary logging format, and the presence of temporary tables in the session has no impact on the logging mode used for each statement.

Note

A warning is generated if you try to execute a statement using statement-based logging that should be written using row-based logging. 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 such a statement is executed. However, only the first statement that generated the warning for each client session is written to the error log to prevent flooding the log.

In addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The logging capabilities of an individual engine can be defined as follows:

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

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

A given storage engine can support either or both logging formats. The following table lists the formats supported by each engine.

Storage Engine Row Logging Supported Statement Logging Supported
ARCHIVE Yes Yes
BLACKHOLE Yes Yes
CSV Yes Yes
EXAMPLE Yes No
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes Yes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.
MyISAM Yes Yes
MERGE Yes Yes
NDB Yes No

Whether a statement is to be logged and the logging mode to be used is determined according to the type of statement (safe, unsafe, or binary injected), the binary logging format (STATEMENT, ROW, or MIXED), and the logging capabilities of the storage engine (statement capable, row capable, both, or neither). (Binary injection refers to logging a change that must be logged using ROW format.)

Statements may be logged with or without a warning; failed statements are not logged, but generate errors in the log. This is shown in the following decision table. Type, binlog_format, SLC, and RLC columns outline the conditions, and Error / Warning and Logged as columns represent the corresponding actions. SLC stands for “statement-logging capable”, and RLC stands for “row-logging capable”.

Type binlog_format SLC RLC Error / Warning Logged as
* * No No Error: Cannot execute statement: Binary logging is impossible since at least one engine is involved that is both row-incapable and statement-incapable. -
Safe STATEMENT Yes No - STATEMENT
Safe MIXED Yes No - STATEMENT
Safe ROW Yes No Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging. -
Unsafe STATEMENT Yes No Warning: Unsafe statement binlogged in statement format, since BINLOG_FORMAT = STATEMENT STATEMENT
Unsafe MIXED Yes No Error: Cannot execute statement: Binary logging of an unsafe statement is impossible when the storage engine is limited to statement-based logging, even if BINLOG_FORMAT = MIXED. -
Unsafe ROW Yes No Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection STATEMENT Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection MIXED Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection ROW Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Safe STATEMENT No Yes Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging. -
Safe MIXED No Yes - ROW
Safe ROW No Yes - ROW
Unsafe STATEMENT No Yes Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging. -
Unsafe MIXED No Yes - ROW
Unsafe ROW No Yes - ROW
Row Injection STATEMENT No Yes Error: Cannot execute row injection: Binary logging is not possible since BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED No Yes - ROW
Row Injection ROW No Yes - ROW
Safe STATEMENT Yes Yes - STATEMENT
Safe MIXED Yes Yes - STATEMENT
Safe ROW Yes Yes - ROW
Unsafe STATEMENT Yes Yes Warning: Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. STATEMENT
Unsafe MIXED Yes Yes - ROW
Unsafe ROW Yes Yes - ROW
Row Injection STATEMENT Yes Yes Error: Cannot execute row injection: Binary logging is not possible because BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED Yes Yes - ROW
Row Injection ROW Yes Yes - 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 to prevent flooding the log. The log message includes the SQL statement that was attempted.

If a slave server has log_error_verbosity set to display warnings, 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, statements that are unsafe for statement-based logging, and so forth.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-binary-log-mixed.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut