MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Danny Haworth Date:September 18 2002 10:08am
Subject:My Replication Story
View as plain text  
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello All,

	Hope somebody can shed some light on this as i think my torch is running
out of batteries ;-)

I have two mysql 4.0.1 servers on two 1Ghz athlon boxes (which are both
running on Mandrake linux 8.2). Our main server has been bulked up with
1Gb of ram and various other goodies whilst the second server is a more
modest replication slave with 128mb and a single ide hdd and a dds4 drive.

We use the replication slave to take snapshot backups of the database
during the working day (hence the dds drive ;-) but recently have had
problems...

We make regular use of temporary tables in our application which seems
to cause problems for the replication slave. Doing a 'SHOW SLAVE STATUS'
gives us the following on one particular table.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Slave: query 'drop table gessica.tmpcredit_card' partially completed on
the master and was aborted. There is a chance that your master is
inconsistent at this point. If you are sure that your master is ok, run
this query manually on the slave and then restart the slave with SET
SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

We get messages like this for every temporary table we use. Normally i'd
be happy to tell mysql to ignore this, as this particular temporary
table is not used in any subsequent updates or inserts, but we need to
use some temp tables to update data.

In an effort to resolve this we upgraded to mysql-4.0.3 which seemed to
go fine. Replication worked a treat and everything seemed ok until next
morning. Once the server was under load (about 25 users), mysql
processes started falling over on the server reporting "mysqld got
signal 11;". After trying everything we could think of, and in a last
ditch attempt to make the system "usable" again we downgraded back to
4.0.1 and everything worked.... with exception to the replication.

I had taken a backup of the logfile before downgrading to 4.0.1 which
showed the following (many times obviously)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=402649088
read_buffer_size=2093056
sort_buffer_size=2097144
max_used_connections=0
max_connections=500
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 2439208 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8721418
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe7ea78, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8072b1a
0x827b7b8
0x80c817d
0x80c7010
0x80a4025
0x809d8b4
0x8096913
0x80a3a86
0x807ceda
0x8080af5
0x807bfe3
0x8081eae
0x807b1be
0x8278e9c
0x82ae29a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve
the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8728558 = SELECT DISTINCT job_headers.*,
job_threads.access_timeslot, job_threads.access_c
omments, job_threads.access_timeslot_details,
job_threads.date_of_inspection, job_threads.access_type, job
_threads.engineer_comments FROM job_headers, job_threads WHERE
job_threads.jid_ptr=job_headers.jid AND cmp
id=18874
thd->thread_id=1

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Running the backtrace through resolve_stack_dump with the sym file
revealed the following...

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

thd=0x8721418
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe7ea78, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8072b1a handle_segfault__Fi + 450
0x827b7b8 pthread_sighandler + 184
0x80c817d open__7ha_heapPCciUi + 317
0x80c7010 ha_open__7handlerPCcii + 36
0x80a4025 open_tmp_table__FP8st_table + 29
0x809d8b4
create_tmp_table__FP3THDP15TMP_TABLE_PARAMRt4List1Z4ItemP8st_orderbN24Ul
+ 4560
0x8096913
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 4035
0x80a3a86 handle_select__FP3THDP6st_lexP13select_result + 102
0x807ceda mysql_execute_command__Fv + 946
0x8080af5 mysql_parse__FP3THDPcUi + 557
0x807bfe3 dispatch_command__F19enum_server_commandP3THDPcUi + 1495
0x8081eae do_command__FP3THD + 94
0x807b1be handle_one_connection__FPv + 682
0x8278e9c pthread_start_thread + 204
0x82ae29a thread_start + 4
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8728558 = SELECT DISTINCT job_headers.*,
job_threads.access_timeslot, job_threads.access_comments,
job_threads.access_timeslot_details, job_threads.date_of_inspection,
job_threads.access_type, job_threads.engineer_comments FROM job_headers,
job_threads WHERE job_threads.jid_ptr=job_headers.jid AND cmpid=18874
thd->thread_id=1

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Im not a database expert so the above really doesn't mean much to be
honest. But all other errors in the file are virtually identical.

If anyone needs any more information i'd be quite happy to post the full
log files on the net (am reluctant to fill up everyones mailbox!) or by
email.

something i forgot to mention is the above logfiles came from the master.

any pointers gladly taken, however small..

best regards to you all,

danny

- --

Danny Haworth
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Ge2 System Administrator
Gas~Elec Safety Systems
Tel : 01895 422 997
Email : dan@stripped (mailto:dan@stripped)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE9iFCXUKf+497XC3QRAo4bAJ9R90nfFCn60zDDIvVQ3b/fiZLPhwCfU3q+
y26N/gXC2TFbe9atAz2tyNY=
=Cdcz
-----END PGP SIGNATURE-----

Thread
My Replication StoryDanny Haworth18 Sep
  • re: My Replication StoryVictoria Reznichenko18 Sep
    • Re: My Replication StoryDanny Haworth18 Sep
      • re: Re: My Replication StoryVictoria Reznichenko21 Sep