MySQL Bugs: #41878: Deleting a row, trigger (before delete) executed, read

Bug #41878	Deleting a row, trigger (before delete) executed, read_error in binlog
Submitted:	5 Jan 2009 18:03	Modified:	10 Jan 2009 22:18
Reporter:	Olaf Stein	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.24	OS:	Linux (RHEL)
Assigned to:		CPU Architecture:	Any
Tags:	binlog, event_type: 73, read_error, trigger

Description:
When deleting a row from my table (adi_95_long, 209 columns), a before delete trigger is executed that writes the row to a table (D_adi_95_long, 211 columns)

The reason I mention the amount of columns is because I believe it has something to do with this bug/problem, i have the exact setup on tables with less columns without any problems.

So when i delete a row, the trigger is actually executed, the row is written into D_adi_95_long and removed from adi_95_long. The problem is that the following statement is written into my binlog:

ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 172800, event_type: 73

with event type always being 73. This breaks my replication.

How to repeat:
1)
mysql> create database test;

2)
I am attaching a dump with the two tables adi_95_long and D_adi_95_long

3)
DROP TRIGGER d_adi_95_long;

DELIMITER //
CREATE TRIGGER d_adi_95_long
BEFORE DELETE ON adi_95_long FOR EACH ROW
BEGIN
INSERT INTO D_adi_95_long select *,NULL,NOW() from adi_95_long WHERE adi_id=OLD.adi_id;
END;
//
DELIMITER ;

4)
mysql> insert into adi_95_long (ident) values (1);
mysql> insert into adi_95_long (ident) values (2);

5)
delete from adi_95_long where ident=1;

6) check last entry in binlog

dump file for the two tables need to replicate this bug

Attachment: test.sql (application/octet-stream, text), 47.62 KiB.

Thank you for the problem report. Please, send your my.cnf file content.

Had you tried to repeat this with a newer version, 5.1.30, on both master and slave?

here is my config file

[mysqld]

#replication
server-id=1
sync_binlog=1
binlog-ignore-db=mysql

# DIRECTORIES
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# LOG OPTIONS
log=/data/mysqllog/mcclintock.log
log-bin=/data/mysqllog/mcclintock
log-error=/data/mysqllog/mcclintock.err
#syslog=mcclintock.err
log-slow-queries=/data/mysqllog/mcclintock-slow.log
#expire_logs_days=7

# GENERAL
user=mysql

# MYISAM
key_buffer_size=5G
myisam_sort_buffer_size=2G

# INNODB
innodb_buffer_pool_size=6G
innodb_additional_mem_pool_size=16M
innodb_log_buffer_size=4M # maybe even less
innodb_log_file_size=256M # high values increases perfromance, but also recovery time
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

# CACHES
query_cache_size=1G
table_open_cache=1024
#thread_cache=16M

# BUFFERS
sort_buffer_size=256M
read_buffer_size=256M
join_buffer_size=256M
read_rnd_buffer_size=256M
tmp_table_size=256M
max_heap_table_size=256M

# MISC
#skip-bdb
#lower_case_table_names=1
#sql-mode=STRICT_TRANS_TABLES

max_allowed_packet=256M

[mysqldump]
quick
max_allowed_packet=128M #16M

[mysql]
max_allowed_packet=128M
port=3306

And i have not tried a newer version.
If you think that will help I can try, I am always hesitant to update versions of mysql...

Thank you for the bug report. I couldn't repeat with latest source, however you could try the latest release on test setup. To run your test case I added/modified the below steps:

set foreign_key_checks = 0;
insert into adi_95_long (ident,date_assessment) values (1,'2009-01-01');
insert into adi_95_long (ident,date_assessment) values (2,'2009-01-01');

Thanks in advance.

But you were able to reproduce on 5.1.24, correct?

I will update as soon as i can and check if that fixes the issue

Olaf

I only tested the latest source and not on older versions.

Hi,
I had a chance to install version 5.1.24 today and test this.
As I wrote you on the mailing list, I checked this with the latest version 5.1.30 and was not able to repeat the bug.

With version 5.1.24, I noticed a break in the replication.

Notes:
1. I used standard my-small.ini files included in the build
2. I changed your script:

-
/*!50003 CREATE*/ /*!50017 DEFINER=`adminAgpv2`@`%`*/ /*!50003 TRIGGER `d_adi_95_long` BEFORE DELETE ON `adi_95_long` FOR EACH ROW BEGIN
+
/*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER */ /*!50003 TRIGGER `d_adi_95_long` BEFORE DELETE ON `adi_95_long` FOR EACH ROW BEGIN

(the definer needs to be CURRENT_USER in order to have a ready to use SQL script otherwise the trigger creation fails).

Also, I disabled foreign key checks as Miguel did (table 'individual' is not included in the file you uploaded):

set foreign_key_checks = 0;
insert into adi_95_long (ident,date_assessment) values (1,'2009-01-01');
insert into adi_95_long (ident,date_assessment) values (2,'2009-01-01');

What I did:

1. Reset the master -> a new binlog in position 106 is created
2. Check replication is running -> it is
3. Delete the record -> record deleted from adi_95_long, trigger executed, row inserted in D_adi_95_long
4. Check replication -> replication is break

When checking the log file of the slave, I found this:

090107 23:52:52 [Note] Slave I/O thread: connected to master 'root@localhost:51241',replication started in log 'mysql-bin.000001' at position 106
090107 23:53:59 [ERROR] Error reading packet from server: error reading log entry ( server_errno=1236)
090107 23:53:59 [ERROR] Got fatal error 1236: 'error reading log entry' from master when reading data from binary log
090107 23:53:59 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 1255

I can attach the content of the binlog, if you need, but please note that I can't see there the error:

ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 172800, event_type: 73

BTW the replication is down.

PS:
[..] I am always hesitant to update versions of mysql... [..]

When you need to test something on a new version of MySQL without affecting your production installation:

- on windows, consider the "Unzip in C" package: just download it and start the server from a temporary directory on a different port
- on linux, consider using MySQL Sandbox, a tool that helps you installing a single MySQL installation or a replication in seconds, in isolation on your home using some perl scripts (https://launchpad.net/mysql-sandbox).

As per your MySQL version (5.1.24), that's a very old version (April 2008). A GA version (5.1.30, stable) has been released in November 2008 and includes many bugs fix. 
You can consider test a new replication with MySQL 5.1.30 using MySQL Sandbox and then evaluate if you get advantages by upgrading to the new version.

Hope this helps.

Regards,
Santo Leto.

Santo,

thanks for your post, you basically get the same errors that I get.

I will update mysql tomorrow to 5.1.30 and let you guys know what happens.

Olaf

Thank you guys. I am changing the status to need feedback so.

The problem is gone in 5.1.30...

Thanks guys

Olaf

Thank you for the feedback. Closing according last comment.