Hello list! I would like to propose a feature for MySQL that would solve the multiple master replication problem.
Unless I miss some important information, there is currently a limitation to the use of master-master replication which concerns AUTO_INCREMENT columns (which are usually used as PRIMARY KEYs): both masters generate numbers from the same set, which guarantees there will be problems with replication when such a column is used as a UNIQUE INDEX. The current solution would require either that one generates the primary keys in some other way, without the use of AUTO_INCREMENT, or that one use an additional column in the primary key. Both of these solutions would require significant changes of much code that is already written, and would not be nearly as easy as AUTO_INCREMENT columns are. I find this a big problem because I think that master-master replication scenarios are very useful and would be used much more if this problem wasn't present. In the real world, there are many slow, unreliable, high-latency, and even intermittent links over which MySQL connections are made, and in many of these cases the solution would be to put the server on both sides of the link, let clients use their "local" server, and let the servers (mutually) replicate transparently in the background. As I am exactly in this situation, I propose some solutions to consider: 1) Make the AUTO_INCREMENT clause accept a RANGE parameter, and then assign numbers only from the specified range. Then, the user would assign a different range to each replication master. Example of proposed syntax: CREATE TABLE example (id INT AUTO_INCREMENT RANGE 1-9999999 PRIMARY KEY, other_column VARCHAR(100)); 2) Make a CHAR (or VARCHAR) AUTO_INCREMENT column with mandatory PREFIX. MySQL would then assign values that start with the prefix, followed by a sequential number. The numbers could be chosen either from the whole INT set sequentially for all prefixes, or individually for each prefix, effectively multiplying the number of possible values - I don't really care about that. Example of proposed syntax: CREATE TABLE example2 (id VARCHAR(14) AUTO_INCREMENT PREFIX 'ISP', other_column VARCHAR(100)); This would create id values of: 'ISP1', 'ISP2' etc. A related question: how does MySQL replication handle intermittent connections? What if a connection goes down unexpectedly - is there any danger of data loss or corruption? And when the connection comes up again, is any action required to recover from the problem and/or resume replication? Thanks for your time! Vaclav Dvorak [EMAIL PROTECTED] IDAS, s.r.o. http://www.idas.cz --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
