MySQL Bugs: #69654: Federated: MySQL Database Error: Got an error writing communication packets

Bug #69654	Federated: MySQL Database Error: Got an error writing communication packets
Submitted:	3 Jul 2013 7:16	Modified:	21 Aug 2014 18:13
Reporter:	nelson mendaros	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Linux (RHEL 6)
Assigned to:		CPU Architecture:	Any
Tags:	Insert statement on Federated tables

Description:
MySQL Database Error: Got an error writing communication packets occurs when inserting to a federated table.

Target database and engine: MySQL version 5.5.27 on RHEL 5.3, INNODB
Source database and engine: MySQL version 5.6.11 on RHEL 6.3, INNODB

Federated table on on source database

Insert statement:

INSERT INTO sf_guard_profile
(sf_guard_user_id, ref_office_id, school_id, school_employee_id, last_name, first_name, middle_name, gender, position_id) 
SELECT sf_guard_user_id, ref_office_id, school_id, school_employee_id, last_name, first_name, middle_name, gender, position_id 
FROM sf_profile_tmp;

How to repeat:
1. create one table on the target on MySQL version 5.5.27
2. create a federated table on the source for the target table on MySQL version 5.6.11
3. create another table with the same data structure with more than 1000 rows (our data is around 65K) on MySQL version 5.6.11.

Note: This also occurs on other tables:

Here's the structure:

CREATE TABLE `sf_guard_profile` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `sf_guard_user_id` int(11) DEFAULT NULL,
  `ref_office_id` int(10) unsigned DEFAULT NULL,
  `school_id` int(9) unsigned DEFAULT NULL,
  `school_employee_id` varchar(10) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `first_name` varchar(30) NOT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `gender` smallint(5) unsigned DEFAULT NULL,
  `position_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sf_guard_user_id` (`sf_guard_user_id`),
  KEY `gender` (`gender`),
  KEY `position_id` (`position_id`),
  KEY `school_id` (`school_id`),
  KEY `ref_office_id` (`ref_office_id`),
  KEY `user_index_01` (`first_name`)
) ENGINE=federated AUTO_INCREMENT=1 connection='mysql://root:passwd@10.0.0.9:/newDB/sf_guard_profile';

Hello Nelson,

May I ask you to try in the latest release 5.6.13 ?

Thanks
Anand

Hello Nelson,

It happens to me also, or at least I think it could be the same problem; my scenario is as follows:

Original tables in a MySQL 5.5.32 server (it is an slave from another server).
Federated tables in a MySQL 5.6.12 server.

I'm not sure it is the same case since I get the error randomly, almost everytime I get it and I try again and it works, and not only in inserts, sometimes connecting to the database from Toad or even with the web application that works over the database.

Is there any known workaround?

Regards,

Ángel

I forgot to comment that I have the same environment as the 5.6.12 one that connects to the same tables in the slave using federated tables but it version is 5.5.16 and it works perfectly.

Regards,

Ángel

We'll try 5.6.13

@@Ángel OR, yeah it's the same thing with us. TOAD and other applications.

Hello everyone,

It seems there is a workaround and, as far as we checked, it worked.

As the documentation says, the Federated engine creates a connection with the remote database using the MySQL C Client API, it is posible that the errors come because this connection expires and closes, so maybe increasing the timeout should fix this.

In our case, the wait_timeout parameter was increased from 3600 (1 hour) to 28800 (8 hours) both in the origin and in the slave databases.

BR,

Ángel

Hi, I've the same problem. I created 2 federated tables. Both servers have the same mysql version: 5.6.10-enterprise-commercial-advanced. After a few hours the connections of the fedetered tables to the real tables are lost. I have to drop and re-create the federated tables to solve the problem. Sometimes, this solution won't work and the problem returns. I've tried to solve the problem increasing the following global system variables "net_write_timeout" and "wait_timeout" on both servers but the bug persists.

Thanks in advance
Enrico Messina

Sorry, I forgotten to indicate that the servers are both RedHat Enterprise 6.

That's right, our solution increasing the wait parameter didn't work either.

Anybody found the solution?
I'm stuck with this issue.
Anybody tried with a newer version? 
Best regards,

Thank you for the report.

Strictly saying this is not good practice to mix major versions of MySQL server for source and target tables of federated storage engine.

But anyway please turn option --log-warnings=2 on the source server and after you see same error again send us full error log files from both source and target servers.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".