473,569 Members | 2,629 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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='9999875 65',
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='9999875 65', 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
3 6080
>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='9999875 65',
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='9999875 65', 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
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=11456 50039;
UPDATE main2Info SET dAddress='1850 Hollywood rd ', dCity='doral',
dState='FL', dZip='33155', dCountry='USA', dPhone='4449873 000',
dRNum='AC15857' , dName='Michael Scott' WHERE did=22;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE =@OLD_COMPLETIO N_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.doctorIn fo' 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='4449873 000', 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.doctorIn fo' 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='4449873 000', 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.doctorInf o...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.supe rnews.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='9999875 65',
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='9999875 65', 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
>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=47 41 exec_time=0 error_code=0
SET TIMESTAMP=11456 50039;
UPDATE main2Info SET dAddress='1850 Hollywood rd ', dCity='doral',
dState='FL', dZip='33155', dCountry='USA', dPhone='4449873 000',
dRNum='AC15857 ', dName='Michael Scott' WHERE did=22;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE =@OLD_COMPLETIO N_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.doctorIn fo' 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='444987 3000', 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.2 7.139:3306', replication started in log 'mysql-bin.000001' at
position 568176
060421 16:07:21 [ERROR] Slave: Error 'Table 'main2.doctorIn fo' doesn't
exist' on query. Default database: 'main22'. Query: 'UPDATE doctorInfo SET
dAddress='18 50 Hollywood rd ', dCity='doral', dState='FL', dZip='33155',
dCountry='USA' , dPhone='4449873 000', 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.doctorIn fo...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.sup ernews.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='9999875 65',
>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='9999875 65', 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1455
by: unplug | last post by:
HI all, I want to ask whether I can do replication in the following case. innodb (master) --replication --> myisam (slave) Rgds, Ringo --
0
1844
by: System | last post by:
Hello All, How will i setup Mysql Replication btween two redhat 7.3/9.0 boxes. I want the replication to happen through a Secure tunnel between these two. I am following the steps that is given on the offcial Mysql website. Just wants to know if there is any easy howto to set this up using a Tunnel. Any comments will be appreciated. ...
0
456
by: System | last post by:
Hello All, I want to setup mysql replication between two hosts Redhat 7.3 and 9.0. I have MySQL 4.0.13 on both the systems running.I am following a HOWTO from http://docsrv.caldera.com:8457/cgi-bin/info2html?(mysql)Replication%2520HOWT O My Question is It says to edit my.cnf on Master and slave but i dont have my.cnf in the current...
0
1280
by: ed | last post by:
I should appologize for my earlier post "Another Replication Problem". I had not properly analyzed the problem before posting. Although I have been administering a MySQL database for about a year now, replication is completely new to me. Sorry if I wasted anyone's time. Thanks to Jeremy Zawodny for leading me in the right direction. I...
0
583
by: I.P. | last post by:
------=_NextPart_000_03FF_01C368A4.75720DC0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version.
3
2009
by: Chad J. Lemmer | last post by:
We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a replication environment. Server 1 (Master) is connected gigabit to our Cisco 3550 core switch The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548 switch in another building. Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched...
5
3915
by: barrathi | last post by:
hai i have problem with mysql replication. master -slave replication and two way replication please can any one tell me step by step solution
0
1090
by: jamesc | last post by:
I am replicating information from one server (master) to another (slave) using MYSQL replication. I have it set up, and works fine other than the fact that I have to do a "USE dbname" as opposed to "update in dbname.foobar set foo=1" for example. Is there any way around this? thanks, james
1
1219
by: Terri | last post by:
I have a client with several different types of users. Most use desktops and work off of the standard tables in a common backend from specific frontends. The sales manager uses a laptop and needs a bunch of tables only he and his assistant use. Most of the data on the network he only needs to read, not change. He uses the laptop both in the...
0
7938
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6317
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5519
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5245
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.