SEO

Hi,

I'm encountering the Error #2006 when inserting data into a table (ERROR
2006 (HY000): MySQL server has gone away). Each time after the same amount
of rows have been successfully added the mysql client window shows me this
error.

The mysql client then tries to reconnect but with the same error message.

I already set:
max_allowed_packet=16M;
delayed_insert_timeout=10000;
connect_timeout=100000;

as it was suggested in some newsgroups. But with the same result.

I'm using 5.0.37 on a XP-box.

Any ideas what might go wrong?
--
Matthias Wirtz - Karlsruhe, DE

Matthias Wirtz wrote:
> Hi,
>
> I'm encountering the Error #2006 when inserting data into a table (ERROR
> 2006 (HY000): MySQL server has gone away). Each time after the same amount
> of rows have been successfully added the mysql client window shows me this
> error.
>
> The mysql client then tries to reconnect but with the same error message.
>
> I already set:
> max_allowed_packet=16M;
> delayed_insert_timeout=10000;
> connect_timeout=100000;
>
> as it was suggested in some newsgroups. But with the same result.
>
> I'm using 5.0.37 on a XP-box.
>
> Any ideas what might go wrong?

a couple of things:
1-are you sure you don't have any corruption in any of your data files (
in the data dir)
2-can you post the 50 last lines of your error file (generated by mysql)

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

"lark" <hamzee@sbcdeglobalspam.net> schrieb im Newsbeitrag
news:eVn_h.187$UU.70@newssvr19.news.prodigy.net...
> Matthias Wirtz wrote:

>> I'm encountering the Error #2006 when inserting data into a table (ERROR
>> 2006 (HY000): MySQL server has gone away). Each time after the same
>> amount of rows have been successfully added the mysql client window shows
>> me this error.

> a couple of things:
> 1-are you sure you don't have any corruption in any of your data files (
> in the data dir)

I had the table files checked with myisamchk and everything is ok. Or did
you mean to use a windows tool on file level to find any corruption?

> 2-can you post the 50 last lines of your error file (generated by mysql)

I'm not sure which log file you are referencing to. There is a
[computername].err file generated in the data folder of my MySQL
installation. The last couple of lines look like this:

----------
070427 16:24:51 InnoDB: Started; log sequence number 0 46291
070427 16:24:51 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
ready for connections.
Version: '5.0.37-community-nt' socket: '' port: 3306 MySQL Community
Edition (GPL)
070427 17:09:18 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
Normal shutdown

070427 17:09:21 InnoDB: Starting shutdown...
070427 17:09:24 InnoDB: Shutdown completed; log sequence number 0 46291
070427 17:09:24 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
Shutdown complete

070503 11:50:52 InnoDB: Started; log sequence number 0 46291
070503 11:50:53 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
ready for connections.
Version: '5.0.37-community-nt' socket: '' port: 3306 MySQL Community
Edition (GPL)
----------

Looks ok to me. If there is another log file I should have a look at let me
know.
--
Matthias Wirtz - Karlsruhe, DE

Matthias Wirtz wrote:
> "lark" <hamzee@sbcdeglobalspam.net> schrieb im Newsbeitrag
> news:eVn_h.187$UU.70@newssvr19.news.prodigy.net...
>> Matthias Wirtz wrote:
>
>>> I'm encountering the Error #2006 when inserting data into a table (ERROR
>>> 2006 (HY000): MySQL server has gone away). Each time after the same
>>> amount of rows have been successfully added the mysql client window shows
>>> me this error.
>
>> a couple of things:
>> 1-are you sure you don't have any corruption in any of your data files (
>> in the data dir)
>
> I had the table files checked with myisamchk and everything is ok. Or did
> you mean to use a windows tool on file level to find any corruption?
>
>> 2-can you post the 50 last lines of your error file (generated by mysql)
>
> I'm not sure which log file you are referencing to. There is a
> [computername].err file generated in the data folder of my MySQL
> installation. The last couple of lines look like this:
>
> ----------
> 070427 16:24:51 InnoDB: Started; log sequence number 0 46291
> 070427 16:24:51 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
> ready for connections.
> Version: '5.0.37-community-nt' socket: '' port: 3306 MySQL Community
> Edition (GPL)
> 070427 17:09:18 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
> Normal shutdown
>
> 070427 17:09:21 InnoDB: Starting shutdown...
> 070427 17:09:24 InnoDB: Shutdown completed; log sequence number 0 46291
> 070427 17:09:24 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
> Shutdown complete
>
> 070503 11:50:52 InnoDB: Started; log sequence number 0 46291
> 070503 11:50:53 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-nt:
> ready for connections.
> Version: '5.0.37-community-nt' socket: '' port: 3306 MySQL Community
> Edition (GPL)
> ----------
>
> Looks ok to me. If there is another log file I should have a look at let me
> know.

you're right! there's nothing wrong at the server level and it appears
your data files are not corrupted either. so i dug in bugzilla a little
bit and found this from the logs:

if you send a query (specially via compressed packets) to the server
that is larger than the max_allowed_packet, it drops the connection
without providing a good error message. A packet too large is handled
the same way as a bogus packet - there's no knowing if the connection is
still good or what is going on, so the easiest way for the server is
just to drop the connection and move along.

from Monty himself:
The easy way to avoid this problem is to ensure that max_allowed_packet
is set bigger in the mysqld server than in the client and that all
clients uses the same value for max_allowed_packet.


here's what i recommend, increase the value of max_allowed_packet at the
server level to 128M but leave the other two what they are. then
increase the value of the same variable in all of your clients to 32M or
even 64M and see what happens.

let us know of the results.


--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

"lark" <hamzee@sbcdeglobalspam.net> wrote in
news:%HG_h.18$y_7.1@newssvr27.news.prodigy.net...

>>>> I'm encountering the Error #2006 when inserting data into a table
>>>> (ERROR 2006 (HY000): MySQL server has gone away). Each time after the
>>>> same amount of rows have been successfully added the mysql client
>>>> window shows me this error.

> here's what i recommend, increase the value of max_allowed_packet at the
> server level to 128M but leave the other two what they are. then increase
> the value of the same variable in all of your clients to 32M or even 64M
> and see what happens.

Yes, now it's working.

I set the max_allowed_packet parameter not only in the mysql-client window
by specifying:

set session max_allowed_packet=16000000;

but also in the my.ini config file.

Looks very much like this was the problem. Thanks for your help.
--
Matthias Wirtz - Karlsruhe, DE