 | | | Replication error with stored proc and triggers | Replication error with stored proc and triggers 2007-12-03 - By Richard@(protected)
Back
>Description:
I am using MySQL server as backend database supporting a telecomm platform
providing voice over ip services.
tables involved: acc, cdr, account, location2
Partial processing flow:
1. When a phone call is started. A record will be inserted into table acc
(method=INVITE). This will trigger an insert of a record into table cdr.
2. When the phone call is ended. Another record will be inserted into table
acc (method=BYE). This will trigger an update of the cdr record (endtime and
duration).
3. When table cdr is updated, a trigger will start. If cdr_duration is not
null, the account table will be updated with adjusted balance.
The process flow has been working fine. The database is replicated to
another MySQL server instance running on another machine.
Recently, I am encountering an error that caused the replication to stop.
IN show slave status, I am getting an error message:
Last_Error: Query caused different errors on master and
slave. Error on master: 'Can't update table '%-.64s' in stored
function/trigger because it is already used by statement which invoked this
stored function/trigger.' (1442), Error on slave: 'no error' (0). Default
database: 'opnser'. Query: 'insert into acc
(method,from_tag,to_tag,callid,sip_code,sip_reason,time,ani,did,dnis )
values
('BYE','3405618788-778336','516B0D10-1448','59591-3405618788-778299@(protected)
hnl.aloha.net','200','OK','2007-12-02
21:14:23','8088646143','818084473408','14378288809164968574')'
Skip counter: 0
Seconds_Behind_Master: NULL
The transaction causing error was completed: cdr record updated, account
balance adjusted.
The only problem is that replication process is frozen.
I have checked the application and it does not look like there is any
deadlock situation.
>How-To-Repeat:
It would be difficult to reproduce the error. The creation of acc record is
the result of a voip software 'openser'. The error condition is random and
unpredictable.
>Fix:
When this happen I need to re-establish the replication by making a copy
from master and do change master on the slave machine.
>Submitter-Id: <submitter ID>
>Originator: Richard Siy
>Organization:
<organization of PR author (multiple lines)> Quattro Broadband, Inc.
>MySQL support: none
>Synopsis: <synopsis of the problem (one line)> Error causing
replication to stop.
>Severity: <[ non-critical | serious | critical ] (one line)> serious
>Priority: <[ low | medium | high ] (one line)> high
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
sw-bug
>Release: mysql-5 (See http://sql-5.ora-code.com).0.27-standard (MySQL Community Edition - Standard
(GPL))
>C compiler: gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>C++ compiler: gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux qbdbm1.qbnet 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005
i686 athlon i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake
Compilation info: CC='gcc'
CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' CXX='gcc'
CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' LDFLAGS=''
ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Aug 17 2006 /lib/libc.so.6 -> libc-2 (See http://ibc-2.ora-code.com).3.4.so
-rwxr-xr-x 1 root root 1439335 Dec 20 2004 /lib/libc-2 (See http://ibc-2.ora-code.com).3.4.so
Configure command: ./configure '--disable-shared'
'--with-server-suffix=-standard' '--without- embedded-server'
'--with-innodb' '--with-archive-storage-engine' '--without-bench'
'--with-zlib- dir=bundled' '--with-big-tables' '--enable-assembler'
'--enable-local-infile' '--with-mysqld- user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic'
'--prefix=/' '--with- extra-charsets=complex' '--with-yassl'
'--exec-prefix=/usr' '--libexecdir=/usr/sbin' '-- libdir=/usr/lib'
'--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql'
'-- infodir=/usr/share/info' '--includedir=/usr/include'
'--mandir=/usr/share/man' '--enable-thread-safe- client'
'--with-comment=MySQL Community Edition - Standard (GPL)' '--with-readline'
'CC=gcc' 'CFLAGS= -O2 -g -pipe -m32 -march=i386 -mtune=pentium4'
'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 - mtune=pentium4' 'CXX=gcc'
'LDFLAGS='
Looking forward to hear from you soon.
Thanks,
Richard Siy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|