enterprise-support team mailing list archive

[Nilnandan Joshi <nilnandan.joshi@xxxxxxxxxxx>]

Public bug reported:

SHOW BINLOG EVENTS fails and gives error if binlog has event size of
more than max_allowed_packet.

mysql> show global variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)

mysql> create table nil (id int, name varchar(10), city varchar(10), phone int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into nil values (1, 'nilnandan','ahmedabad',2348762347);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from nil;
+------+-----------+-----------+------------+
| id   | name      | city      | phone      |
+------+-----------+-----------+------------+
|    1 | nilnandan | ahmedabad | 2147483647 |
+------+-----------+-----------+------------+
1 row in set (0.00 sec)

mysql> insert into nil select * from nil;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into nil select * from nil;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
..
.

mysql> insert into nil select * from nil;
Query OK, 8388608 rows affected (37.83 sec)
Records: 8388608  Duplicates: 0  Warnings: 0

mysql> insert into nil select * from nil limit 2000000;
Query OK, 2000000 rows affected, 1 warning (8.36 sec)
Records: 2000000  Duplicates: 0  Warnings: 1

mysql> insert into nil select * from nil limit 2000000;
Query OK, 2000000 rows affected, 1 warning (7.66 sec)
Records: 2000000  Duplicates: 0  Warnings: 1

mysql> insert into nil select * from nil limit 3000000;
Query OK, 3000000 rows affected, 1 warning (11.86 sec)
Records: 3000000  Duplicates: 0  Warnings: 1

mysql> quit
Bye
nilnandan@desktop:~$ 

nilnandan@desktop:~/sandboxes/rsandbox_Percona-Server-5_6_21/master/data/test$ ll -h
total 1.4G
drwx------ 2 nilnandan nilnandan 4.0K Jan 12 13:58 ./
drwx------ 5 nilnandan nilnandan 4.0K Jan 12 13:54 ../
-rw-rw---- 1 nilnandan nilnandan 8.5K Jan 12 13:58 nil.frm
-rw-rw---- 1 nilnandan nilnandan 1.4G Jan 12 14:28 nil.ibd
nilnandan@desktop:~/sandboxes/rsandbox_Percona-Server-5_6_21/master/data/test$ 

-- Create outfile to load the data


mysql> SELECT * INTO OUTFILE '/tmp/nil_table.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM nil;
Query OK, 23777216 rows affected (20.00 sec)

nilnandan@desktop:/tmp$ ll -h nil_table.txt 
-rw-rw-rw- 1 nilnandan nilnandan 840M Jan 12 14:36 nil_table.txt
nilnandan@desktop:/tmp$ 

-- Create new table and load the data

mysql> create table nil_new (id int, name varchar(10), city varchar(10), phone int);
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA INFILE '/tmp/nil_table.txt' 
    -> INTO TABLE nil_new
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 23777216 rows affected (1 min 34.75 sec)
Records: 23777216  Deleted: 0  Skipped: 0  Warnings: 0


mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 692490956
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2c827228-90cf-11e4-b877-00224db1c503:1-34
1 row in set (0.00 sec)

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000004' FROM 692490800;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
mysql> 


nilnandan@desktop:~/sandboxes/rsandbox_Percona-Server-5_6_21/master/data$ tail -f msandbox.err 
2015-01-12 14:38:17 11087 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.21-rel70.1 started; log sequence number 2084708105
2015-01-12 14:38:17 11087 [Note] RSA private key file not found: /home/nilnandan/sandboxes/rsandbox_Percona-Server-5_6_21/master/data//private_key.pem. Some authentication plugins will not work.
2015-01-12 14:38:17 11087 [Note] RSA public key file not found: /home/nilnandan/sandboxes/rsandbox_Percona-Server-5_6_21/master/data//public_key.pem. Some authentication plugins will not work.
2015-01-12 14:38:17 11087 [Note] Server hostname (bind-address): '127.0.0.1'; port: 20886
2015-01-12 14:38:17 11087 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-01-12 14:38:17 11087 [Note] Server socket created on IP: '127.0.0.1'.
2015-01-12 14:38:17 11087 [Note] Event Scheduler: Loaded 0 events
2015-01-12 14:38:17 11087 [Note] /home/nilnandan/Downloads/Softwares/5.6.21/bin/mysqld: ready for connections.
Version: '5.6.21-70.1-log'  socket: '/tmp/mysql_sandbox20886.sock'  port: 20886  Percona Server (GPL), Release 70.1, Revision 698
2015-01-12 14:45:49 11087 [ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 1768818944, event_type: 127

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: New

** Summary changed:

- SHOW BINLOG EVENTS fails if binlog has event size of close or more than max_allowed_packet
+ SHOW BINLOG EVENTS fails if binlog has event size of more than max_allowed_packet

** Bug watch added: MySQL Bug System #75480
   http://bugs.mysql.com/bug.php?id=75480

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=75480
   Importance: Unknown
       Status: Unknown

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1409652

Title:
  SHOW BINLOG EVENTS fails if binlog has event size of more than
  max_allowed_packet

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1409652/+subscriptions