Reconnect After Connection Timeout?

[smine]

hi. we had a replication problem yesterday. our replication environment runs the same query on the slave as the master. we depend on the default database to be set (USE database_name) and adding the database name to the UPDATE command generated by SQLyog (after modifying data in the Results Grid) is not sufficient for replication to work.

yesterday replication failed. my initial connection to the server does show SQLyog issuing a USE statement, but my UPDATE statement was not replicated. we are guessing that is because my connection was idle for 4 hours and the session timed out (timeout is set to one hour), then SQLyog nicely reestablished the connection but forgot the default database.

that is just a guess. is that possible? if so, would it be possible to have SQLyog restore the session to the same state it was previously?

p.s. i am not a MySQL replication expert and and only passing along information that someone else told me. if it is misinformed, i apologize.

[peterlaursen]

“we depend on … adding the database name to the UPDATE command generated by SQLyog”. Why if a use statement was generated? Does replication fail to *use the USE statement* (on slave)?

I am sorry but I do not understand.

[peterlaursen]

Is it same problem as discussed here: http://www.webyog.com/forums/index.php?showtopic=5473

Do you use 'binlog_ignore' in the replication setup for instance?

[smine]

haha, you edited my comments too much! 🙂

'peterlaursen' wrote:

“we depend on … adding the database name to the UPDATE command generated by SQLyog”. Why if a use statement was generated? Does replication fail to *use the USE statement* (on slave)?

here is my original quote, with irrelevant text struck out

'smine' wrote:

… we depend on the default database to be set (USE database_name) and adding the database name to the UPDATE command generated by SQLyog (after modifying data in the Results Grid) is not sufficient for replication to work. …

you are correct that SQLyog issues a USE command. it also prefixes the database_name to the UPDATE comand which is why i mentioned it. whether the database name is in the UPDATE or not in the UPDATE doesn't matter, we expect the USE command to set the default database.

but that doesn't matter, because yes, i believe the issue we are seeing is the same as http://www.webyog.co…?showtopic=5473. i do not know if we have 'binlog_ignore'. we are running MySql 5.0.xx (multiple flavors of 5.0).

thanks!

[Aparna]

Hi,

Could you please check in the MySQL.ini file if the 'binlog_ignore' statement is being used and inform us about the same? Thanks!

[smine]

our system administrator is on vacation. i will ask about 'binlog_ignore' when he returns sometime next week. (if this is something that i can find on my own as a regular, non-privileged user, please let me knw and i will look.)

[smine]

'Aparna' wrote:

Could you please check in the MySQL.ini file if the 'binlog_ignore' statement is being used and inform us about the same? Thanks!

here is the response from our admin/architect:

Quote:

Yup, we do things a little funky. We don't actually use binary-ignore-db, we use replicate-wild-do-table, but the effect is the same, except it is inclusionary rather than exclusionary.

does that help?

[peterlaursen]

I really do not know if we can do anything about it. I don't promise anything. But we will have to check (nad understand) with 'replicate-wild-do-table'.

[Aparna]

Hi,

You said you have a master slave set up right? Could you please give us the exact server versions of both your slave as well as the master. We will try and set up a similar environment here. And also tell us the operating system that you have the set up on.

Thank you!

[smine]

from our admin/architect:

Quote:

MySQL 5.0.45 (Community Version) on master and minion.

RHEL (Red Hat Enterprise Linux) 5.5 on the master, RHEL 5.2 on the minion.

[Aparna]

Hi,

We tried reproducing this issue at our end but it works just fine for us. In order to investigate this further would it be possible to send the “my.cnf” files of both your master as well as the slave servers? Sensitive information such as the password and other log in details can be removed. If it is OK to send these two log files then please attach them and mail them to [email protected]. This creates a private ticket which only our team and you can view.

Thank you!

[smine]

thank you so much for trying to reproduce this. since it seems to be a very rare occurrence, and since we have a workaround, we feel it is not worth continuing to try to figure out what is happening.

thanks again!

[Author]

Posts