MySQL replication & autoincrements issues: solved the (dead) simple way

First three links on the topic I just found googlin' around:
http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html (this one really impressive)
http://www.neocodesoftware.com/replication/
http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

It really seems that with MySQL 5.1+ it turned out to be really simple replicating things around.

Enjoy

MySQL foreign keys (MySQL Error Nr. 1005)

12-09-2009 - 12:00
UPDATE:
Well, it seems to me that this topic deserves some more attention, so I'm going to investigate it thoroughly.
From this page I found that:

a table [...] must have indexes on the referenced keys. If these are not satisfied [read: "if they are not present for those keys"], MySQL returns error number 1005 and refers to error 150 in the error message.

More in detail, in the rest of the update I'm assuming the following command (as it is reported on the same page I linked above) as reference:

[CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (index_col_name, ...)
 REFERENCES tbl_name (index_col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]

@Dhruva Sagar: I think you were talking about this one:

If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

@payal: Check this (same page as above...):

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

Furthermore I suggest using the following command to check when errors occur: SHOW ENGINE INNODB STATUS, which displays a detailed explanation of the most recent foreign key error in the server.
I'm trying to replicate this error today; I'll drop you the results as soon as I can.


----------------- old updates -----------------

27-08-2007 - 10:08 (OLD UPDATE!!!)

UPDATE:
It seems the problem is caused by the "ON UPDATE" and "ON DELETE" clauses: they have to be set to the same policy, i.e. you have to write "ADD CONSTRAINT 'new_fk_constraint' FOREIGN KEY 'new_fk_constraint' ('fieldA1') REFERENCES 'tableB' ('fieldB2') ON DELETE RESTRICT ON UPDATE RESTRICT" or "ON DELETE UPDATE ON UPDATE UPDATE", while "ON DELETE SET NULL ON UPDATE RESTRICT" won't work.
Indexes are not the cause of the problem: when you create a foreign key an index for the field involved on the foreign key is automatically created by the MySQL environment, so you don't need to manually create it before adding the foreign key (read: "Bull-Shit at 09:41, Good-Shit at 10:08").

27-08-2007 - 09:41 (OLD UPDATE!!!)
If you want to place a foreign key from field 1 on table A to field 2 on table B, first of all you need to create an index for the field 1 on table A.
If you don't create the index before attempting to create the foreign key you are likely to be stopped by the error "MySQL Error Nr. 1005- Can't create table...".

I experienced this situation while using InnoDB.

One more reason

I'm still asking myself why people insist on using MySQL.

Today another near-buggy behavior for this DBMS.

Just try to call a MySQL function name and then leave a space between it and the open parenthesis:

"Function FUNCTION_NAME doesn't exist on DB_NAME"

...

Seriously man... migrate to PostgreSQL and leave the old and stupid things behind.

MySQL vs Ubuntu 8.04: Default settings really annoy me!

By default Ubuntu 8.04 sets MySQL to listen to local connections only.
This really is an annoying and stupid thing I found in the server (!) version of Ubuntu Hardy Heron.

Here the solution.

Here some thoughts of mine:

• Does a server mantainer need Ubuntu to prevent him from using MySQL for the purpose it has been meant?
• Does someone still use all-in-one machines, equipped with DBMS, appserver, webserver and whatever else (maybe someone like this still exists...)?

I have to admit it: the only OS I saw doing stupid things like this one is... Windows.