Mysql Replication Problem

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
follows:

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
my.cnf:

[Slave my.cnf]

replicate-do-db = info2

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

Thanks!!

Re: Mysql Replication Problem

>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
>follows:
>
>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'

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

>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'
^^^^^^^^^

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?

>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
>my.cnf:
>
>[Slave my.cnf]
>
>replicate-do-db = info2

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.

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

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 */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;



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

mysql> SHOW SLAVE STATUS;

--+---------------+--------------------+-------------------- +---------------
-----+-----------------+-------------------+---------------- +---------------
--------+
| Waiting for master to send event | 64.290.27.139 | 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@64.290.27.139:3306', 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
main2.doctorInfo...blablabla)

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.


------------------------------------------------------------ ----------------
--------------------------------------------
"Gordon Burditt" wrote in message
news:124idnghgq5k162@corp.supernews.com...
> >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
> >follows:
> >
> >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'
>
> What did the query look like when it was sent to the master?
>
> >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'
> ^^^^^^^^^
>
> 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?
>
> >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
> >my.cnf:
> >
> >[Slave my.cnf]
> >
> >replicate-do-db = info2
>
> 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.
>
> >Any suggestions to have the slave NOT use the database name?
>
> 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:

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.

DOES THE TABLE EXIST ON THE SLAVE? WITH THE CORRECT CAPITALIZATION?

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


>********** 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 */;
>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
>
>
>
>********** ON MY SLAVE SERVER ****************
>
>mysql> SHOW SLAVE STATUS;
>
>--+---------------+--------------------+------------------- -+---------------
>-----+-----------------+-------------------+--------------- -+---------------
>--------+
>| Waiting for master to send event | 64.290.27.139 | 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'

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

>| 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@64.290.27.139:3306', 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
>main2.doctorInfo...blablabla)
>
>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.
>
>
>----------------------------------------------------------- -----------------
>--------------------------------------------
>"Gordon Burditt" wrote in message
>news:124idnghgq5k162@corp.supernews.com...
>> >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
>> >follows:
>> >
>> >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'
>>
>> What did the query look like when it was sent to the master?
>>
>> >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'
>> ^^^^^^^^^
>>
>> 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?
>>
>> >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
>> >my.cnf:
>> >
>> >[Slave my.cnf]
>> >
>> >replicate-do-db = info2
>>
>> 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.
>>
>> >Any suggestions to have the slave NOT use the database name?
>>
>> Capitalize consistently, and I don't think the database name was ever
>> an issue.
>>
>> Gordon L. Burditt
>
>