4.10.5 Replication Features and Known Problems

Here is an explanation of what is supported and what is not:

• Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values.
• USER() and LOAD_FILE() functions are replicated without changes and will thus not work reliably on the slave. This is also true for CONNECTION_ID() in slave versions strictly older than 4.1.1. The new PASSWORD() function in MySQL 4.1, is well replicated since 4.1.1 masters ; your slaves must be 4.1.0 or above to replicate it. If you have older slaves and need to replicate PASSWORD() from your 4.1.x master, you should start your master with option --old-password.
• sql_mode, FOREIGN_KEY_CHECKS and table_type variables are not replicated.
• You have to use the same character set (--default-character-set) on the master and the slave. If not, you may get duplicate key errors on the slave, because a key that is regarded as unique in the master character set may not be unique in the slave character set.
• If you are using transactional tables on the master and non-transactional tables (for the same tables) on the slave, you will get problems if the slave is stopped in the middle of a BEGIN/COMMIT block, as the slave will later start at the beginning of the BEGIN block. This issue is on our TODO and will be fixed in the near future.
• Update queries that use user variables are badly replicated in 3.23 and 4.0. This is fixed in 4.1. Note that user variables' names are case insensitive starting from version 5.0, so you should take this into account when setting up replication between 5.0 and a previous version.
• The slave cannot connect to the master using SSL yet.
• There is a theoretical chance (though we have never heard of it actually occurring) that the data on the master and slave become different if a query is designed in such a way that the data modification is non-deterministic, that is, left to the will of the query optimiser (which generally is not good practice!). For a detailed explanation, see section 1.8.6.2 Open Bugs / Design Deficiencies in MySQL.
• Strictly before MySQL 4.1.1, FLUSH, ANALYZE, OPTIMIZE, REPAIR commands are not stored in the binary log and are because of this not replicated to the slaves. This is not normally a problem as these commands don't change anything. This does however mean that if you update the MySQL privilege tables directly without using the GRANT statement and you replicate the mysql privilege database, you must do a FLUSH PRIVILEGES on your slaves to put the new privileges into effect. Also if you use FLUSH TABLES when renaming a MyISAM table involved in a MERGE table, you will have to issue FLUSH TABLES manually on the slave. Since MySQL 4.1.1, these commands are written to the binary log (except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES WITH READ LOCK) unless you specify NO_WRITE_TO_BINLOG (or its alias LOCAL) (for a syntax example, see section 4.5.3 FLUSH Syntax).
• MySQL only supports one master and many slaves. Later we will add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help do load balancing by sending select queries to different slaves.
• Temporary tables are replicated with the exception of the case when you shut down slave server (not just slave thread) when you have some replicated temporary tables and they are used in subsequent updates by the master. To deal with this problem shutting down the slave, do STOP SLAVE, check Slave_open_temp_tables variable to see if it is 0, if so issue mysqladmin shutdown. If the number is not 0, restart the slave threads with START SLAVE and see if you have better luck next time. We have plans to fix this in the near future.
• It is safe to connect servers in a circular master-slave relationship with log-slave-updates enabled. Note, however, that many queries will not work right in this kind of setup unless your client code is written to take care of the potential problems that can happen from updates that occur in different sequence on different servers. This means that you can do a setup like the following:

  A -> B -> C -> A
  

  Thanks to server ids, which are encoded in the binary log events, A will know when the event it reads had originally been created by A, so A will not execute it and there will be no infinite loop. But this circular setup will only work if you only do non conflicting updates between the tables. In other words, if you insert data in A and C, you should never insert a row in A that may have a conflicting key with a row insert in C. You should also not update the same rows on two servers if the order in which the updates are applied matters.
• If the query on the slave gets an error, the slave SQL thread will terminate, and a message will appear in the `.err' file. You should then connect to the slave manually, fix the cause of the error (for example, non-existent table), and then run START SLAVE.
• If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages. However, the slave will notice the network outage only after receiving no data from the master for slave_net_timeout seconds. So if your outages are short, you may want to decrease slave_net_timeout ; see section 4.5.7.4 SHOW VARIABLES.
• Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not synced before the system died. Your system fault tolerance will be greatly increased if you have a good UPS.
• Due to the non-transactional nature of MyISAM tables, it is possible to have a query that will only partially update a table and return an error code. This can happen, for example, on a multi-row insert that has one row violating a key constraint, or if a long update query is killed after updating some of the rows. If that happens on the master, the slave thread will exit and wait for the DBA to decide what to do about it unless the error code is legitimate and the query execution results in the same error code. If this error code validation behaviour is not desirable, some ( or all) errors could be masked out with slave-skip-errors option starting in Version 3.23.47.
• If you update transactional tables from non-transactional tables inside a BEGIN/COMMIT segment updates to the binary log may be out of sync if some thread changes the non-transactional table before the transaction commits. This is because the transaction is written to the binary log only when it's commited.
• Any updates to a non-transactional table is written to the binary log at once when the update is made while transactional updates are written on COMMIT or not written at all if you use ROLLBACK. You have to take this into account when updating both transactional tables and non-transactional tables in the same transaction and you are using binary logging for backups or replication.

The following table is about problems in 3.23 that are fixed in 4.0:

• LOAD DATA INFILE will be handled properly as long as the file still resides on the master server at the time of update propagation.
• LOAD LOCAL DATA INFILE will be skipped.
• In 3.23 RAND() in updates does not replicate properly. Use RAND(some_non_rand_expr) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() for the argument to RAND(). This is fixed in 4.0.

User Comments

FYI

I could not get replication to work between
3.23.53 (master) and 3.23.41 (slave)

The symptons where:
Very very slow replication. Like 30 seconds
before a simple update got replicated.

About every 30 seconds, I got these entries in the
replication slave's log file:
020820 13:46:51 Error reading packet from server:
(server_errno=1159)
020820 13:47:51 Slave: Failed reading log event,
reconnecting to retry, log 'dragon-mount-bin.004'
position 137
020820 13:47:51 Slave: reconnected to master
'repl@192.168.1.11:3306',replication resumed in
log 'dragon-mount-bin.004' at position 137

By simply upgrading to 3.23.53(latest) for the
slave it worked just fine

Got exactly the same error as above (error reading
packet every 30 sec), using v3.23.51 for both
master and slave.

Switching to v3.23.52 (latest for Solaris) solved
the problem.

I created a table, loaded part of it from local
files, and part of it from update statements.
When I looked at what was replicated, of course,
only the lines that were manually inserted
propigated. There were about 130 rows from
load, and 4 from insert. When I read the
limitations, I realized what was going on - I had
used local files and not copied the files into the
mysql directory. So I created a second table
manually, and I populated it with an insert into
xxx (f1, f2) select * from oldtable;

And the same lines were replicated. The 4 rows
that were propagated were the same ones. I
can only assume that the update / select was
propagated as a command and not the data.

I tried altering the data (integer+0, string concat
(field,'')) - again, the propagation was via
command, and not via value, so the same 4 lines
ended up in the target table.

I ended up having to propagate the data via a
file dump from source/ftp(scp)/restore via piping
the dump into mysql.

I did not actually try it, but it is possible that I
could have fixed the issue simply by dumping
and restoring the tables (and recreating the
data) on the source machine. Recreating the
data with update statements should have
worked.

With replication, LOAD DATA INFILE does not replicate using strictly the filename, e.g.
mysql> load data infile 'a_table.txt' ...

(a row of default values usually appears on the slaves)

Use of the full path, however, does replicate, e.g.
mysql> load data infile /var/lib/mysql/a_db/a_table.txt' ...

Either way, data is loaded on master properly. No cross database access is involved.

Master is 3.23.49. Some slaves are 3.23.49, some are 3.23.54.

I AM GETTING THE FOOLOWIN GERROR MESSAGE ON THE SLAVE .ERR FILE I HAVE GIVEN REPLICATION SLAVE PRIVELEGE ON MASTER CAN ANY BODY GIVE ME THE DETAIL STEPS OF REPLICATION AND SOLUTION FOR THIS ERROR.


030519 0:13:43 Slave I/O thread: connected to master 'SHASHI@203.196.160.20:3
306', replication started in log '1.001' at position 179
030519 0:13:43 While trying to obtain the list of slaves from the master '203.
196.160.20:3306', user 'SHASHI' got the following error: 'Access denied. You ne
ed the REPLICATION SLAVE privilege for this operation'
030519 0:13:43 Slave I/O thread exiting, read up to log '1.001', position 179

THANKS

I had the same error. my replication user had all rights (at least for testing) but complains about insufficient priviliges

Note that the GRANT command always replicates, regardless of whether you're replicating the mysql privileges tables.

I initially got the same "Access denied" error with replication, but running the mysql_fix_privilege_tables script fixed it. (Please note that in my case I had upgraded the master from 3.23 to 4.0.13)

Add your own comment.