Bug #4139 Too long multiple insert - makes slave down!
Submitted: 15 Jun 2004 10:16 Modified: 21 Jul 2004 9:01
Reporter: Sergey Frolovichev Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18 OS:Linux (SuSe 9.2 (2.6 kernel))
Assigned to: CPU Architecture:Any

[15 Jun 2004 10:16] Sergey Frolovichev
Description:
Slave MySQL Server stops slave thread on error in SQL after too long multiple insert (more 1Mb in default).

How to repeat:
Make 2 tables on master and make it sync to slave after that make huge multiple insert (more than max_allowed_packed) - you will receive error in client on master and after this slave will stop :-(.

Also try please a situation than multiple insert takes on end of binary log and it must be devided between new one and old.
[15 Jun 2004 10:42] Guilhem Bichot
Hi,
Indeed, max_allowed_packet on master and slave must be bigger than the size of any query which needs to be sent from master to slave. You should increase it using
max_allowed_packet=xxxx
in your master's and slave's my.cnf.
[15 Jun 2004 11:38] Sergey Frolovichev
max_allowed_packet on master and slave are similar - 1Mb
On master I receive error on huge packet but slave receives this packed and stops.
[15 Jun 2004 11:53] Guilhem Bichot
> max_allowed_packet on master and slave are similar - 1Mb

Yes, that's why I suggested you increase it on both machines.

> On master I receive error on huge packet but slave receives this packed and
> stops.

If master gives error, slave cannot have received the packet. What slave probably says is that "got error from master: etc".

Please test the suggestions I made. What you have is absolutely normal, and it won't go away until you change max_allowed_packet. Thank you.
[18 Jun 2004 17:49] Sergey Frolovichev
Let's try again, because I still receive an error!

I have a master and a slave.
Both master and slave have max_allowed_packet=1Mb
Until all of my queries wehe less 1Mb all was ok, but some days ago I have a new feature (multiple insert) which sometimes make a query more 1Mb and my slave sometimes stops on error:
-----------------
Error 'Column count doesn't match value count at row 1' on query 'insert into Search2.extendedSearch_2_F_N (oid,user_id,language_id,photo_oid,photo_extension,photo_adult,iam,lookfor,type,name,birthdate,gender,country_id,country_name,region_id,region_name,city_id,city_name,metro_id,metro_name,aboutme,photos,banned,changed,created,sign,target,constitution,hairshead,marital,children,circumstance,sponsor,music,sport,smoke,drink,drug,aids,sexexcitation,sexpenis,sexbreast,sexpriority,orientation,age,height,weight) values ('750856','750855','2','','','','F','M','Free','???'1984-10-17','F'. Default database: ''

-----------------
[18 Jun 2004 22:54] Guilhem Bichot
Again:
please test if the problem goes away when increasing the max_allowed_packet on master and slave to more than the maximum length of your queries.
Note that it's the 3rd time I write this suggestion to you.
[19 Jun 2004 11:54] Sergey Frolovichev
I m going to be very angry. THE SLAVE MUST NOT STOP AFTER ERROR ON MASTER! THIS IS A BUG! Of course I will increase and everithing  will be okey. BUT THIS IS A BUG! Error query must not stop slave!!!! How many times I have to repeat a REAL BUG?
[19 Jun 2004 15:20] Guilhem Bichot
Hello,

I am sorry if I made you angry, it was not my goal.
If you think there is a bug in MySQL, we would need a complete testcase. In your case, that would just be the entire master query which caused the error, so that I can try to reproduce the error myself.
I need the exact query which was submitted on master (not only the beginning).
Thanks in advance. And I hope we can keep courtesy in this discussion.

Guilhem
[19 Jun 2004 20:38] Sergey Frolovichev
I'm sorry for my behavio. It's only in "text messages" it looks so, in real life this "angry" will look as a huge smile. Sorry I didn't set any of smiles :-)
I've set a log to catch a bug again. I'll send you a query as sonn as it will happen again.
But it will be huge (>1Mb) - how can I report it?
[19 Jun 2004 22:20] Guilhem Bichot
I'm very happy that I misunderstood your mood. Text messages lack the tone of voice, it's always a trap for me.
The query you pasted looked strange:
values
('750856','750855','2','','','','F','M','Free','???'1984-10-17','F'
There are too many ' in '???'1984-10-17'. Some Russian colleague suggested to me that maybe it's because it was of a non-ASCII encoding, which copy-paste to the bug form made look strange ?
The error the slave gets:
Column count doesn't match value count at row 1
is surprising. I wonder if the slave is not mis-interpreting the query. That could happen if there was a character set discrepancy between master and slave (you know, in 4.0, master and slave must have the same character set, and user must not change the character set of his session; in the coming 4.1.3 this is fixed).
Apart from that, I don't know yet. We'll see when the total query is available. Could you please compress and upload it there:
ftp://support.mysql.com/pub/mysql/secret/
Thank you!
[20 Jun 2004 12:27] Sergey Frolovichev
As soon as I get it from my log - I'll send it as you wish ;-)
This must be happen tomorrow (where will be a work day and where are will be more load on mysql.
[14 Feb 2005 22:54] Bugs System
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".