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
