Rechercher dans le manuel MySQL
17.4.1.14 Replication and System Functions
Certain functions do not replicate well under some conditions:
The
USER()
,CURRENT_USER()
(orCURRENT_USER
),UUID()
,VERSION()
, andLOAD_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()
andCURRENT_USER()
are automatically replicated using row-based replication when usingMIXED
mode, and generate a warning inSTATEMENT
mode. (See also Section 17.4.1.8, “Replication of CURRENT_USER()”.) This is also true forVERSION()
andRAND()
.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 anINSERT
statement on this table, and then select from the table, as shown here:- Query OK, 0 rows affected (0.06 sec)
- Query OK, 1 row affected (0.00 sec)
- +---------------------+
- | mycol |
- +---------------------+
- | 2009-09-01 12:00:00 |
- +---------------------+
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 value2009-09-01 18:00:00
is returned. For this reason, if you select from the slave's copy ofmytable
after theCREATE TABLE
andINSERT
statements just shown have been replicated, you might expectmycol
to contain the value2009-09-01 18:00:00
. However, this is not the case; when you select from the slave's copy ofmytable
, you obtain exactly the same result as on the master:- +---------------------+
- | mycol |
- +---------------------+
- | 2009-09-01 12:00:00 |
- +---------------------+
Unlike
NOW()
, theSYSDATE()
function is not replication-safe because it is not affected bySET 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 causeSYSDATE()
to be an alias forNOW()
. 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 usingMIXED
mode, and generates a warning inSTATEMENT
mode.The following restriction applies to statement-based replication only, not to row-based replication. The
GET_LOCK()
,RELEASE_LOCK()
,IS_FREE_LOCK()
, andIS_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 asINSERT INTO mytable VALUES(GET_LOCK(...))
.These functions are automatically replicated using row-based replication when using
MIXED
mode, and generate a warning inSTATEMENT
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:
To work around the problem, do this instead:
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:
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:
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:
However, if you are replicating mytable
, you
should use SELECT
... INTO
, and then store the variable in the table,
like this:
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)
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-features-functions.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.