Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again.

111128 8:42:24 [Note] Slave I/O thread: connected to master 'repl@masterIP:3306', replication started in log 'mysql-bin.000006' at position 169

111128 15:46:50 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: 'Dbase'. Query: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id', Error_code: 0 111128 15:46:50 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000006' position 1277768

I'm not sure how to do the following:

try restarting transaction' (1213)

Thanks.

share|improve this question
    
I've had this problem before, and running start slave; on the slave server allows the server to sync up again, but I'd be interested to see the root cause and fix it properly. –  matthewh Nov 29 '11 at 10:49
add comment

1 Answer

up vote 1 down vote accepted

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;
share|improve this answer
    
This answer is exactly what is recommended in the error message. (+1) I did this as well for my clients as long as my clients are aware of it. I sometimes get this condition when the master's execution of the query is interrupted but still gets recorded in its binary logs, thus letting it get replicated to the slave. The slave caught that condition and nicely gave the option of having the SQL re-executed. –  RolandoMySQLDBA Nov 29 '11 at 16:47
    
One way to avoid this from happening is to make sure innodb_flush_log_at_trx_commit is NOT 0. 0 means it writes transactions to the log every second regardless of whether the transaction finished which could cause this state. –  TechieGurl Nov 30 '11 at 16:24
    
@TechieGurl I've set this already, but still it occurs. –  itsraja Dec 3 '11 at 5:21
    
@RolandoMySQLDBA any tips to avoid this, since it is mission critical. The query is run everyday and this happens everyday(only date changes). Also any general guideline regarding the query itself, as it seems to lock the entire table even though it is InnoDB –  itsraja Dec 3 '11 at 5:25
    
Note: when this query is run insertions to the same table may also take place. –  itsraja Dec 3 '11 at 5:27
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.