Version sans cache


Mise en cache désactivé. Réglage défaut pour cette page : actif (code DEF204)
Si l'affichage est trop lent, vous pouvez désactiver le mode utilisateur pour visualiser la version en cache.

Rechercher dans le manuel MySQL

17.4.1.14 Replication and System Functions

Table des matières

Certain functions do not replicate well under some conditions:

  • The USER(), CURRENT_USER() (or CURRENT_USER), UUID(), VERSION(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave unless row-based replication is enabled. (See Section 17.2.1, “Replication Formats”.)

    USER() and CURRENT_USER() are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. (See also Section 17.4.1.8, “Replication of CURRENT_USER()”.) This is also true for VERSION() and RAND().

  • For NOW(), the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave. To avoid unexpected results when replicating between MySQL servers in different time zones, set the time zone on both master and slave. See also Section 17.4.1.32, “Replication and Time Zones”

    To explain the potential problems when replicating between servers which are in different time zones, suppose that the master is located in New York, the slave is located in Stockholm, and both servers are using local time. Suppose further that, on the master, you create a table mytable, perform an INSERT statement on this table, and then select from the table, as shown here:

    1. mysql> CREATE TABLE mytable (mycol TEXT);
    2. Query OK, 0 rows affected (0.06 sec)
    3.  
    4. mysql> INSERT INTO mytable VALUES ( NOW() );
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> SELECT * FROM mytable;
    8. +---------------------+
    9. | mycol               |
    10. +---------------------+
    11. | 2009-09-01 12:00:00 |
    12. +---------------------+
    13. 1 row in set (0.00 sec)

    Local time in Stockholm is 6 hours later than in New York; so, if you issue SELECT NOW() on the slave at that exact same instant, the value 2009-09-01 18:00:00 is returned. For this reason, if you select from the slave's copy of mytable after the CREATE TABLE and INSERT statements just shown have been replicated, you might expect mycol to contain the value 2009-09-01 18:00:00. However, this is not the case; when you select from the slave's copy of mytable, you obtain exactly the same result as on the master:

    1. mysql> SELECT * FROM mytable;
    2. +---------------------+
    3. | mycol               |
    4. +---------------------+
    5. | 2009-09-01 12:00:00 |
    6. +---------------------+
    7. 1 row in set (0.00 sec)

    Unlike NOW(), the SYSDATE() function is not replication-safe because it is not affected by SET TIMESTAMP statements in the binary log and is nondeterministic if statement-based logging is used. This is not a problem if row-based logging is used.

    An alternative is to use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). This must be done on the master and the slave to work correctly. In such cases, a warning is still issued by this function, but can safely be ignored as long as --sysdate-is-now is used on both the master and the slave.

    SYSDATE() is automatically replicated using row-based replication when using MIXED mode, and generates a warning in STATEMENT mode.

    See also Section 17.4.1.32, “Replication and Time Zones”.

  • The following restriction applies to statement-based replication only, not to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the slave knowing the concurrency context on the master. Therefore, these functions should not be used to insert into a master table because the content on the slave would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).

    These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode.

As a workaround for the preceding limitations when statement-based replication is in effect, you can use the strategy of saving the problematic function result in a user variable and referring to the variable in a later statement. For example, the following single-row INSERT is problematic due to the reference to the UUID() function:

  1. INSERT INTO t VALUES(UUID());

To work around the problem, do this instead:

  1. SET @my_uuid = UUID();
  2. INSERT INTO t VALUES(@my_uuid);

That sequence of statements replicates because the value of @my_uuid is stored in the binary log as a user-variable event prior to the INSERT statement and is available for use in the INSERT.

The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:

  1. SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
  2. INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:

  1. INSERT INTO t2 SELECT UUID(), * FROM t1;

Within a stored function, RAND() replicates correctly as long as it is invoked only once during the execution of the function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

The FOUND_ROWS() and ROW_COUNT() functions are not replicated reliably using statement-based replication. A workaround is to store the result of the function call in a user variable, and then use that in the INSERT statement. For example, if you wish to store the result in a table named mytable, you might normally do so like this:

  1. SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
  2. INSERT INTO mytable VALUES( FOUND_ROWS() );

However, if you are replicating mytable, you should use SELECT ... INTO, and then store the variable in the table, like this:

  1. SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
  2. INSERT INTO mytable VALUES(@found_rows);

In this way, the user variable is replicated as part of the context, and applied on the slave correctly.

These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. (Bug #12092, Bug #30244)


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-features-functions.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut