0

Some relevant my.cnf settings:

binlog-format=ROW
init_connect='SET autocommit=1'
autocommit=1
innodb_flush_log_at_trx_commit=1

I also have a replication running... Now, most of the time things runs rather well.

But sometimes I do get this:

Could not execute Delete_rows/Update_rows event on table auto.parcels_to_cache; Can't find record in 'parcels_to_cache'.

This is because of this:

mysql-bin.000021.decoded-26373095-### DELETE FROM auto.parcels_to_cache
mysql-bin.000021.decoded-26373096-### WHERE
mysql-bin.000021.decoded-26373097-###   @1='0101'
mysql-bin.000021.decoded-26373098-###   @2='2013:01:05'
mysql-bin.000021.decoded:26373099:###   @3='01014700669249'
--
mysql-bin.000022.decoded-4143326-### INSERT INTO auto.parcels_to_cache
mysql-bin.000022.decoded-4143327-### SET
mysql-bin.000022.decoded-4143328-###   @1='0101'
mysql-bin.000022.decoded-4143329-###   @2='2013:01:05'
mysql-bin.000022.decoded:4143330:###   @3='01014700669249'

This is a decoded binary log from the master server. The replication server reflects this.

Also this seems only to happen on InnoDB tables. But not always. Although I think the MyISAM problems with replication I had were related to another problem.

I recently recoded all the sources to remove the few transactions I had in there to remove all of them. So no begins, no commits, no rollbacks anymore... Then I added into the mysql database class to always turn off commits as well.

This because I read on the MySQL website there were issues with transactions & transactionable and non-trans tables.

For example this auto.parcels_to_lifecycle table is heavily used, sometimes it is possibly accessed by 20 threads at once. Hence the InnoDB. Otherwise each thread will wait for when only 1 thread is updating...

Anyone knows how to fix this DELETE before INSERT problem? Or maybe some way to approach the problem and fix it?

Thanks!

Steven Van Ingelgem
  • 872
  • 2
  • 9
  • 25
  • Could you clarify what exact SQL statements you are issuing, and in what order, which is generating the above logs? – jeremycole Jan 08 '13 at 23:17
  • @jeremycole Not really. I know which ones are being issued. Just not what order. As I said, it's like 20 threads at once working on 1 innodb... So I've no idea what order. But speaking in a highlevel view: this table is a caching table. So data is queued via the table. It cannot be processed if it's not in the table. At the end of the processing script the data is removed. The inserts happen from all over the place. The delete from only 1 script (which is thread-safe, so only 1 script can run at the same time)... I hope this helps you a little to assess the situation? – Steven Van Ingelgem Jan 09 '13 at 02:24
  • One point, in your error message you have table `parcels_to_lifecycle `, but in your log you have `parcels_to_cache`. Are you absolutely sure that all tables are now InnoDB? -- In general this situation should be impossible if everything is properly configured. – jeremycole Jan 09 '13 at 16:20
  • @jeremycole: Sorry, I copied the wrong entries. That should be the same table of course (but I have it for both). Last step I did now was removing "delayed" from all insertion queries. Let's hope it works. And I do agree with you, it should be impossible. But contrary to that, I saw the evidence that it is quite possible :-). – Steven Van Ingelgem Jan 09 '13 at 19:23
  • Ohh, you were using `INSERT DELAYED`? That code has been extremely buggy in the past. That was a critical piece of information missing from the question. :) In theory that shouldn't change anything, but in practice, it's probably the cause. I'll do some testing on that. – jeremycole Jan 09 '13 at 20:30
  • @jeremycole: What I noticed is that under really heavy load it happens much more often. And yes, DELAYED is a very beautifull principle as it queues everything inside the MySQL server, but I am working ROW based, so I expect it to just work (TM)... Not :( – Steven Van Ingelgem Jan 10 '13 at 04:19

0 Answers0