Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replication acting bizarre 1

Status
Not open for further replies.
instar4per

instar4per

Programmer
Jan 21, 2002
48
US
Is this the right area for this? Regardless,

At work, we have multiple DB servers. A single master, and a single (soon to add more) slaves. Web front-ends load-balance between the slave servers.

As our users post forms, the post data is added to the master and allowed to propegate to the slaves. This works on every application except for one. This one is the source of this post:

In order to remove any possible problems with permissions, we've reverted our code to post as `root`. We are aware of the security implications of this move, and will change the user, as soon as the replication problem is fixed.

The code being executed is a simple INSERT statement.
* When run on the master from the mysql binary, it replicates.
* When run from a desktop posting to the master via MySQL Query Browser, it replicates.
* When run from a web-frontend's mysql binary, it replicates.
* When run from the PHP script, it doesn't replicate.

This would lead me, initially, to believe it was a PHP malfunction. However, if it was, then why would it post to the master and not replicate across? Using the same username, password, and query from the mysql binary replicates without flaw.

The error logs on both the master and the slave are blank, and the SHOW SLAVE STATUS command shows nothing out of the ordinary. It says the thread is running, with no errors reported.

Any suggestions or things to look at? The tables aren't locked, the file permissions are good, they've even been recreated under a different name and the same problem happens. Other tables in the same database replicate just fine, it's this one application.

-iNSTA
aim: instar4per
email: instar4per @ hotmail.com
 
Sort by date Sort by votes
Can you see the updated record on the master? Does the update appear in the master's replication log?
 
Upvote 0 Downvote
The record does post to the master, from any application (command-line binaries included). Only from the web application does it not replicate.

How would I check the replication logs as you're asking?

-iNSTA
aim: instar4per
email: instar4per @ hotmail.com
 
Upvote 0 Downvote
on unix you can run something like:
Code: 
strings /var/lib/mysql/data/s1-bin.000012 | tail
 
Upvote 0 Downvote
The master server is Windows.

The slaves are Unix.

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
perhaps this is a silly thing to suggest, but I cannot think of anything better...

run ethereal on the master server, and watch for what comes across when you use that PHP script to update the database. See if it sends the data to the slave. Perhaps somehow it gets jumbled up? Any way, it would be good to see if the replication packets are sent across.

On the heals of this idea comes another one...are your master MySQL server and slaves the same version? Is it possible that the PHP script is doing something which can be done on the master server but not on the slaves? (say, master is 4.1, and the slaves are 4.0 and you are doing replication)

Just shooting in the dark here
 
Upvote 0 Downvote
The master is 5.0.0-nt-log.

The slaves are all 4.1.10-Debian_4.

Ideally, we'd like to avoid a restart, if at all possible ... not sure how Ethereal will behave on the master. If it helps, here is the query being run:

INSERT INTO dbservices.tblevents_listings
(id, ipaddress, name, email, phone, startdate, enddate, title, description, url, status)
VALUES
('{$this->_id}',
inet_aton('{$this->_ipaddy}'),
'{$this->_name}',
'{$this->_email}',
'{$this->_phone}',
'{$this->_dateStart->getMySQLDate()}',
'{$this->_dateEnd->getMySQLDate()}',
'{$this->_title}',
'{$this->_description}',
'{$this->_url}',
'{$this->_status}');

I'm not sure how familiar you are with PHP, but it should be fairly easy to figure out. It's being called from within a class object.

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
Is this an intermittant problem or all the time?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...
 
Upvote 0 Downvote
If it were me, I wouldn't be running a beta version of the server in production. Can you at least upgrade to the latest 5.0.x release?
 
Upvote 0 Downvote
I agree with ericbrunson - running V5 in production is asking for trouble. It would have been better if they were all the same version.

Anyway, as for running Ethereal on a production box - DON'T!!! It installs waaayyyy too much that you don't need. You should get a copy of the command line utility TCPDUMP instead. Then, when you have run the TCPDUMP to capture the traffic you can then take the data back to you personal machine and analyze it with Ethereal.

THis thread contains a quick intro into using TCPDUMP -
 
Upvote 0 Downvote
I'll go give that a shot. Is this to determine whether or not the master is sending "slave update" packets?

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
yes - and I think you should be able to see the payload too - the actual query.
 
Upvote 0 Downvote
One thing I just thought of ...

Other queries are replicating just fine. They insert simple data. This one won't replicate, but it's using "inet_aton". Think that will have anything to do with it? Remember, 4.1 on the slaves.

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
Code: 
Your MySQL connection id is 104729 to server version: 4.1.10a-standard-log

mysql> select inet_aton( "192.168.1.1" );
+----------------------------+
| inet_aton( "192.168.1.1" ) |
+----------------------------+
|                 3232235777 |
+----------------------------+
1 row in set (0.00 sec)
I doesn't look like 4.1.10 should have any problems with inet_aton.

Have your php print the actual SQL being run. Don't infer it or translate it or type what you *know* is, make PHP print it, then paste (not retype, cut and paste) that into a mysql shell on the master.

 
Upvote 0 Downvote
We found the problem, right before trying the packet-capture.

It had something to do with not using a "mysql_select_db()" command. Included this extra bit in the PHP, and lo and behold, it replicates. More info:
Notice, however, that I do explicitly name the DB in the query ... that's not good enough for MySQL.

[sad]

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
I am confuseled and discombobulated...what does a mysql_select_db() have to do with it not replicating?! I mean, ok, the query failing I can understand...but replication failing? The query is successful from what you said...This means that the record is inserted. I would imagine at that point the PHP script has NOTHING to do with further happenings in regard to replication, so this is very strange.
 
Upvote 0 Downvote
Read the link I provided, it describes it. It's a "feature".

-iNSTA
aim: instar4per
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

soushi01
  • Locked
  • Question
MySQL replication slave server re-sync issue
Replies
0
Views
235
soushi01
soushi01
medeek
  • Locked
  • Question
Errors with Replication
Replies
1
Views
78
jpadie
jpadie
csgonan
  • Locked
  • Question
problem starting mysql replication with database with stored procedure
Replies
1
Views
95
csgonan
csgonan
countdrak
  • Locked
  • Question
MySQL Replication Pros/Cons
Replies
1
Views
77
lgarner
lgarner
NewbieScoobie
  • Locked
  • Question
Mysql Replication
Replies
1
Views
64
Kozusnik
Kozusnik

Part and Inventory Search

Sponsor

Back
Top