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> 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
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 desktop: ~$
Bye
nilnandan@
nilnandan@ desktop: ~/sandboxes/ rsandbox_ Percona- Server- 5_6_21/ master/ data/test$ ll -h desktop: ~/sandboxes/ rsandbox_ Percona- Server- 5_6_21/ master/ data/test$
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@
-- 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 desktop: /tmp$
-rw-rw-rw- 1 nilnandan nilnandan 840M Jan 12 14:36 nil_table.txt
nilnandan@
-- 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 ******* ******* ******* ****** 90cf-11e4- b877-00224db1c5 03:1-34
*******
File: mysql-bin.000004
Position: 692490956
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 2c827228-
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 www.percona. com) 5.6.21-rel70.1 started; log sequence number 2084708105 /sandboxes/ rsandbox_ Percona- Server- 5_6_21/ master/ data//private_ key.pem. Some authentication plugins will not work. /sandboxes/ rsandbox_ Percona- Server- 5_6_21/ master/ data//public_ key.pem. Some authentication plugins will not work. /Downloads/ Softwares/ 5.6.21/ bin/mysqld: ready for connections. sandbox20886. sock' port: 20886 Percona Server (GPL), Release 70.1, Revision 698 :read_log_ event() : 'Event too big', data_len: 1768818944, event_type: 127
2015-01-12 14:38:17 11087 [Note] InnoDB: Percona XtraDB (http://
2015-01-12 14:38:17 11087 [Note] RSA private key file not found: /home/nilnandan
2015-01-12 14:38:17 11087 [Note] RSA public key file not found: /home/nilnandan
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
Version: '5.6.21-70.1-log' socket: '/tmp/mysql_
2015-01-12 14:45:49 11087 [ERROR] Error in Log_event: