Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups:

Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query.
Default database: 'foobar'. Query: 'alter table picture add index FKDD905 (pictureSource_id), 
add constraint FKDD905 foreign key (pictureSource_id) references picturesource (id)'

Got to mention, that we just "host" the MySQL servers/setups; the content (including INDEXes and such) comes from the customer. We just run it.

Okay, so here's what happened, as far as I understand it:

  1. On the master, the customer created the table "picturesource"
  2. On the master, they added the index "FKDD905" to the table "picture", referencing the field "id" from "picturesource" as a Foreign Key
  3. The slave tried to execute "alter table add index…".
  4. The slave tried to execute "add table picturesource…".

In that order.

On the slave, that failed. It failed, because the table picturesource did not exist on the slave. How can that happen? Why did the slave try to add the index BEFORE creating the table? I mean, on the master, the table MUST have existed before they added the key.

Don't know if it's important, but binlog_format = MIXED. I manually added the (empty) table picturesource and could then restart the slave process (→ START SLAVE;). In a subsequent SHOW FULL PROCESSLIST\G, I then saw, that it did a copy to tmp table for the picture table; the picture table is huge (~7 GB).

Can someone clarify?

share|improve this question

migrated from serverfault.com Sep 1 '13 at 6:59

This question came from our site for system and network administrators.

    
When you created table on the master the replica already worked without any errors? Are you using replicate-ignore-table? –  ALex_hha Aug 30 '13 at 7:39
    
I did not create the table on the master. It was there. To fix it, I created it on the slave. I am not usign replicate-ingore-table. Added steps 3 & 4 to how things happened. –  alexs77 Aug 30 '13 at 10:03
    
To clarify - on the replica, the table picture existed. Not picturesource. picturesource existed on the master. That's why the customer was able to add the index FKDD905 to the table `picture. –  alexs77 Aug 30 '13 at 10:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.