Re: [Sqlrelay-discussion] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)
|
From: Chris C. <cc...@gm...> - 2008-02-19 23:05:07
|
Now that you mention the loop.. Something akin to that appears to be happening without OPT_RECONNECT according to tcpdump and query logs. Seems to open a new connection, and immediately quit.. Added some debug output to mysqlconnection.C to make sure it was catching mysql_error() properly, and it is. Getting "Lost connection to MySQL server during query" on or after the re-login. What's interesting, is the reconnect does work when when I let the connection timeout and use ping().. However the next query attempted will fail. -- my.cnf -- (nothing special) [mysqld] user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking old_passwords = 1 bind-address = 0.0.0.0 key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K max_connections=200 thread_cache_size=10 table_cache=512 query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1 log-bin = /var/log/mysql/mysql-bin.log max_binlog_size = 104857600 wait_timeout = 10 server-id = 1 On Feb 19, 2008 1:54 PM, David Muse <dav...@fi...> wrote: > When I disable the MYSQL_OPT_RECONNECT line and test it, I get an error > message: "mysql server has gone away". SQL Relay detects that string, > re-logs in and everything works. > > I did discover a bug though, because it should have caught the > CR_SERVER_GONE_ERROR error instead of having to resort to testing the > error string, but I was checking the result of > mysql_stmt_execute/mysql_real_query rather than the result of > mysql_stmt_errno/mysql_errno. I have a fuzzy memory though of some > version around 4.0 returning CR_SERVER_GONE_ERROR as the result of the > query rather than in errno, so maybe that wasn't a bug. > > At any rate, the mysql connection daemon code gets the errno now and > detects CR_SERVER_GONE_ERROR instead of having to test the actual error > string. But that still doesn't explain why it works for me but not for > anyone else. > > I do remember an issue way back where running create,drop,start,begin > and a few other queries through the mysql stmt api caused a > CR_SREVER_GONE_ERROR and somehow triggered a loop-up when sqlrelay tried > to re-log in. I don't remember the exact details. The fix was to run > those queries using the older mysql api. I'm pretty sure you guys are > seeing something similar but I'm at a loss for how to reproduce it. > > Granted I'm not running on Centos, just Fedora, maybe that's the key. > > Could you post your /etc/my.cnf file? Maybe there's some other odd > mysql parameter that's triggering the issue. > > Thanks, > > Dave > dav...@fi... > > On Tue, 2008-02-19 at 11:46 -0500, Chris Coyle wrote: > > Centos 4.4 (Generally) > > Mysql 5.0.22 or 5.0.41 > > > > > > Test Case > > - Set the wait_timeout on the mysql server to 10 seconds > > - open local sqlrsh instance to mysql server > > - run a few queries > > - let it sit for > 10s > > - run another query > > > > > > Without auto_reconnect enabled, sqlrelay will hang indefinitely (or > > not, I've never been that patient). With reconnect enabled you can > > sit there and every 15 seconds select session_id() and watch it > > increment. > > > > > > It may be that the API treats a timeout or other non-server failure > > based disconnect differently from the server bouncing (? vaguely > > recall something like this from when I was digging through docs, but > > not sure). > > > > > > Hope this helps, > > - Chris > > > > > > On Feb 19, 2008 10:16 AM, David Muse <dav...@fi...> > > wrote: > > This patch appears to be more correct that the code I had > > before, so > > I'll apply it. Though even without this patch, I can't > > replicate the > > problem that several folks have reported. > > > > If I run an instance of mysql, run sqlrelay against it, run > > some queries > > through sqlrsh, stop the mysql server, run a query through > > sqlrsh, then > > restart the mysql server, sqlrelay successfully reconnects and > > the query > > goes through. I've tried several different versions of mysql > > and tried > > restart vs. stop/start, and other things, but I can't seem to > > reproduce > > the error. > > > > Could someone who's had trouble with this send me some more > > info. What > > version of mysql are you using, what platform, and exactly > > what steps > > are you using to reproduce the error? > > > > Thanks, > > > > David Muse > > dav...@fi... > > > > > > On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote: > > > Cool. I'll give it a try and should be able to include it > > in the next > > > release. > > > > > > Thanks! > > > > > > Dave Muse > > > dav...@fi... > > > > > > > > > On Mon, 2008-02-11 at 18:05 -0800, Renaud Amar wrote: > > > > I wrote a small patch which fixes an issue I've noticed > > with the SQL > > > > Relay connection daemons for MySQL: > > > > > > > > the connection daemon doesn't automatically reconnect to > > the MySQL > > > > database once it's been disconnected (either when > > restarting mysqld or > > > > when the connection times out). I've also noticed that > > after being > > > > disconnected, the connection daemon usually gets stuck in > > a weird loop > > > > where it keeps trying to unsuccessfully send a query over > > and over. > > > > > > > > > > > > > > > > After looking through the mysql connection daemon code, I > > noticed that > > > > the code that is supposed to enable the > > MYSQL_OPT_RECONNECT option > > > > (in mysqlconnection.h and mysqlconnection.C) was ifdef'ed > > using a > > > > different #define symbol as the one used in the config.h > > file. > > > > > > > > Namely, it's using "#ifdef MYSQL_OPT_RECONNECT" while > > config.h defines > > > > "HAVE_MYSQL_OPT_RECONNECT". > > > > > > > > This patch simply modifies mysqlconnection.h and > > mysqlconnection.C to > > > > use HAVE_MYSQL_OPT_RECONNECT. > > > > > > > > > > > > > > > > This is probably the same issue Abraham Müller mentioned > > in a previous > > > > post (here: > > > > > > > http://sourceforge.net/mailarchive/forum.php?thread_name=47739600.8090800%40gmx.de&forum_name=sqlrelay-discussion > ). > > > > > > > > Hopefully, this patch will fix this issue as well. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Renaud. > > > > > > > > > > > > > > > > > > > > > > > > PS: when applying the patch in your spec file, in the % > > prep section, > > > > make sure you call it with the –p1 option, in order to > > strip one level > > > > of directories from the paths in the patch file. > > > > > > > > > > > > > > > > > > > > __________________________________________________ > > > > D O T E A S Y - "Join the web hosting revolution!" > > > > http://www.doteasy.com > > > > > > > ------------------------------------------------------------------------- > > > > This SF.net email is sponsored by: Microsoft > > > > Defy all challenges. Microsoft(R) Visual Studio 2008. > > > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > > > > _______________________________________________ > > Sqlrelay-discussion mailing list > > Sql...@li... > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > > > > > > > > __________________________________________________ > > D O T E A S Y - "Join the web hosting revolution!" > > http://www.doteasy.com > > > > > ------------------------------------------------------------------------- > > This SF.net email is sponsored by: Microsoft > > Defy all challenges. Microsoft(R) Visual Studio 2008. > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > > _______________________________________________ > > Sqlrelay-discussion mailing list > > Sql...@li... > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > > > > > > > > __________________________________________________ > > D O T E A S Y - "Join the web hosting revolution!" > > http://www.doteasy.com > > > > __________________________________________________ > D O T E A S Y - "Join the web hosting revolution!" > http://www.doteasy.com > |
