By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Mysql Replication Problem

P: n/a
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!!
Apr 21 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
>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
Apr 21 '06 #2

P: n/a
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
'r***@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" <go***********@burditt.org> wrote in message
news:12*************@corp.supernews.com...
Hello, I'm having a problem replicating a simple database using the binarylog 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

Apr 21 '06 #3

P: n/a
>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
'r***@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" <go***********@burditt.org> wrote in message
news:12*************@corp.supernews.com...
>Hello, I'm having a problem replicating a simple database using thebinary >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


Apr 21 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.