Mysql Replication Problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hello, I'm having a problem replicating a simple database using the binary
log replication, here is the problem:

When the master sends an update to the slave, an example update reads as

UPDATE  MainInfo SET  dAddress='38 Holland Blvd',  dCity='miami',
dState='FL',  dZip='33000',  dCountry='USA',  dPhone='999987565',
dNum='AC15857',  dName='Michael A Scott'  WHERE did=22'

and I get an error (I'm logging the replication errors) that says:

060420 17:44:01 [ERROR] Slave: Error 'Table 'info2.mainInfo' doesn't exist'
on query. Default database: 'info2'. Query: 'UPDATE  MainInfo SET
dAddress='38 Holland Blvd',  dCity='miami',  dState='FL',  dZip='33000',
dCountry='USA',  dPhone='999987565',  dNum='AC15857',  dName='Michael A
Scott'  WHERE did=22', Error_code: 1146

Now, i think it's because the query is including the Database Name and it
shouldn't because I'm already specifying which database to replicate on

[Slave my.cnf]

replicate-do-db = info2

Any suggestions to have the slave NOT use the database name?


Re: Mysql Replication Problem

Quoted text here. Click to load it

What did the query look like when it was sent to the master?

Quoted text here. Click to load it

cApiTaLIzAtIOn cOUnTs on many platforms.  Which table exists,
MainInfo or mainInfo?

What was the default database when the query was sent to the master?
What database was explicitly specified with a table name in the
query when it was sent to the master?  Is either of these a database
that is NOT info2?

Quoted text here. Click to load it

If you are not altering the database name, e.g. replicating info2
on the master into info37 on the slave using replicate-rewrite-db,
this shouldn't matter.  If you are using replicate-rewrite-db,
cross-database queries are likely to not work.

Quoted text here. Click to load it

Capitalize consistently, and I don't think the database name was ever
an issue.

                    Gordon L. Burditt

Re: Mysql Replication Problem

Hi Gordon, first of all, thank you very much for your reply.

I replicated the error and I'm pasting here exactly what I did:

********** ON MY MASTER SERVER *************

bash# mysqlbinlog mysql-bin.000001

... after a lot of queries, the last one reads as follows:

#060421 16:07:19 server id 1  end_log_pos 568668        Query
thread_id=4741  exec_time=0     error_code=0
SET TIMESTAMP=1145650039;
UPDATE  main2Info SET  dAddress='1850 Hollywood rd ',  dCity='doral',
dState='FL',  dZip='33155',  dCountry='USA',  dPhone='4449873000',
dRNum='AC15857',  dName='Michael Scott'  WHERE did=22;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

********** ON MY SLAVE SERVER ****************


| Waiting for master to send event | | repl        | 3306
| 60            | mysql-bin.000001 | 568668              | relay.000002   |
445           | mysql-bin.000001      | Yes              | No
| main2        |                     |                    |
|                         |                             | 1146       | Error
'Table 'main2.doctorInfo' doesn't exist' on query. Default database:
'main2'. Query: 'UPDATE  mainInfo SET  dAddress='1850 Hollywood rd ',
dCity='doral',  dState='FL',  dZip='33155',  dCountry='USA',
dPhone='4449873000',  dRNum='AC15857',  dName='Michael Scott'  WHERE did=22'
| 0            | 568386              | 727             | None            |
| 0             | No                 |                    |
|                 |                   |                | NULL

********** THIS IS THE ERROR.LOG ON THE SLAVE **********

060421 16:06:55 [Note] Slave SQL thread initialized, starting replication in
log 'mysql-bin.000001' at position 568176, relay log
'/usr/log/mysql/relay.000001' position: 4
060421 16:06:55 [Note] Slave I/O thread: connected to master
'repl@',  replication started in log 'mysql-bin.000001' at
position 568176
060421 16:07:21 [ERROR] Slave: Error 'Table 'main2.doctorInfo' doesn't
exist' on query. Default database: 'main22'. Query: 'UPDATE  doctorInfo SET
dAddress='1850 Hollywood rd ',  dCity='doral',  dState='FL',  dZip='33155',
dCountry='USA',  dPhone='4449873000',  dRNum='AC15857',  dName='Michael
Scott'  WHERE did=22', Error_code: 1146
060421 16:07:21 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'mysql-bin.000001' position 568386

If I run this query on mysql> it works perfectly IF i remove the database
name from it (ex: UPDATE doctorInfo... blablabla instead of UPDATE

In regards to your questions:

* The CapItAlizaTion error was my mistake since I sligthly changed the
Database name for security purposes (since im posting in a newsgroup)

*The query is being sent to the master via PHP, it's a simple php query and
the default database is main2

*The only option that I'm using on my slave's my.cnf is replicate-do-db =
info2  so it doesn't replicate the other databases from the master.

This should be a very simple procedure and all I'm trying to do is replicate
from a main database server to another one, I just can't seem to figure out
what's wrong..

THANK YOU very much for your help.

Quoted text here. Click to load it

Re: Mysql Replication Problem

Quoted text here. Click to load it

How can you possibly have bad RAM that only fails when it contains
a table name?  Once again, the table name (not database name) in
the query differs in the query to the master and the error message
from the slave.  Actually, you've got THREE different names, the
one from the master, the error message from the slave, and the query
from the slave.  And the error you're getting looks for all the
world like a spelling problem in the table name.

If you want help, DON'T EDIT FOR SECURITY PURPOSES (except passwords,
which shouldn't be in your examples anyway, and weren't) (because
you make too many mistakes at such editing) and HIRE SOMEONE YOU
TRUST rather than asking newsgroups.


A couple of other things to check:  what version of MySQL are you
running on the master?  On the slave?  Are they the same version?

                    Gordon L. Burditt

Quoted text here. Click to load it
Quoted text here. Click to load it
Quoted text here. Click to load it

How can the error message refer to a table not mentioned in the query?

Quoted text here. Click to load it

Site Timeline