long write queries hang the server

>Description:

Under a variety of circumstances, creates and inserts of a
table with many columns, 2304 to be exact, hang the server. The
symptoms are that the mysqld thread gets a signal 11, and the client
never completes. "show processlist" gives, for example

mysql> show processlist;
*************************** 1. row ***************************
Id: 4
User: marki
Host: claspc14.jlab.org
db: calib
Command: Query
Time: 63
State: update
Info: INSERT GPP_DC_WIRE_sector1_reg2 ( minRunSource, maxRunSource, author, comment,
v_0001, v_0002, v_000
*************************** 2. row ***************************
Id: 6
User: dbmanager
Host: localhost
db: calib
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)

The log file says

/usr/libexec/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
....
key_buffer_size=8388600
record_buffer=131072
sort_buffer=2097144
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

Attempting backtrace. You can use the following information to find out
....
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x83e8168 = INSERT GPP_DC_WIRE_sector1_reg2 ( minRunSource, maxRunSource,
author, comment, v_0001, v_0002, v_0003, v_0004, v_0005, v_0006, v_0007, v_0008,
v_0009, v_0010, v_0011, v_0012, v_0013, v_0014, v_0015, v_0016, v_0017, v_0018,
v_0019, v_0020, v_0021, v_0022, v_0023, v_0024, v_0025, v_0026, v_0027, v_0028,
v_0029, v_0030, v_0031, v_0032, v_0033, v_0034, v_0035, v_0036, v_0037, v_0038,
v_0039, v_0040, v_0041, v_0042, v_0043, v_0044, v_0045, v_0046, v_0047, v_0048,
v_0049, v_0050, v_0051, v_0052, v_0053, v_0054, v_0055, v_0056, v_0057, v_0058,
v_0059, v_0060, v_0061, v_0062, v_0063, v_0064, v_0065, v_0066, v_0067, v_0068,
v_0069, v_0070, v_0071, v_0072, v_0073, v_0074, v_0075, v_0076, v_0077, v_0078,
v_0079, v_0080, v_0081, v_0082, v_0083, v_0084, v_0085, v_0086, v_0087, v_0088,
v_0089, v_0090, v_0091, v_0092, v_0093, v_0094, v_0095, v_0096, v_0097, v_0098,
v_0099, v_0100, v_0101, v_0102, v_0103, v_0104, v_0105, v_0106, v_0107, v_0108,
v_0109, v_0110, v_01!
11, v_0112, v_0113, v_0114, v_0115, v_0116, v_0117, v_0118, v
thd->thread_id=6

Successfully dumped variables, if you ran with --log, take a look at the
....
Number of processes running now: 1
030306 16:10:01 mysqld restarted
Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
....
innodb_data_file_path = ibdata1:30M
....
/usr/libexec/mysqld: ready for connections

This behavior is seen on different servers, and from both the
perl and C API's. The create query is 74 kB long and the insert query
is 39 kB.
The hangs seem to be history dependent. If many inserts are done
rapidly, the server is more likely to hang.
The hangs can also be reproduced by alternating creating and
inserting to a series of independent tables, all of the same size.
The tables use an auto-incremented primary key.
The output from resolve_stack_dump looks like:

0x80d51bf handle_segfault__Fi + 431
0x4003df75 _end + 935915701
0x80d07e7 vio_is_blocking + 7
0x80cfdc9 net_write_buff__FP6st_netPCcUi + 73
0x80cfcb1 my_net_write__FP6st_netPCcUl + 81
0x80d0d78 send_ok__FP6st_netUlUxPCc + 168
0x8102bc6 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt 4List1Z4Item15enum_duplicates13thr_lock_type
+ 2182
0x80dcbea mysql_execute_command__Fv + 5466
0x80ded65 mysql_parse__FP3THDPcUi + 69
0x80dacef do_command__FP3THD + 1215
0x80da2a9 handle_one_connection__FPv + 617

>How-To-Repeat:
The following commands:
mysql test < create.sql
mysql test < insert.sql
mysql test < insert.sql
mysql test < insert.sql
..
..
..

executed rapidly from the shell seems to reliably hang the
server. Here create.sql and insert.sql can be found at

http://www.jlab.org/~marki/mysql_bug/create.sql
http://www.jlab.org/~marki/mysql_bug/insert.sql

>Fix:
no known work around

>Submitter-Id:
>Originator: Mark M. Ito
>Organization:
Thomas Jefferson National Accelerator Facility
Email: marki@jlab.org
>MySQL support: none
>Synopsis: many-byte creates and inserts crash MySQL server
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for redhat-linux-gnu on
i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 3.23.49-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 15 min 42 sec

Threads: 2 Questions: 10 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables:
5 Queries per second avg: 0.011
>Environment:

System: Linux claspc14.jlab.org 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64
-D_LARGEFILE_SOURCE' CXX='gcc' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE
-D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Nov 12 11:54 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 2 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 Nov 12 12:42 /usr/lib/libc-client.a ->
c-client.a
Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr
--bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include
--libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com
--mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline
--enable-shared --with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all
--with-innodb --with-berkeley-db --enable-large-files=yes --enable-largefile=yes
--with-thread-safe-client --enable-assembler



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13959@lists.mysql.com
To unsubscribe, e-mail

Re: long write queries hang the server

Hi,

>> Description:
>=20
> Under a variety of circumstances, creates and inserts of a
> table with many columns, 2304 to be exact, hang the server. The
> symptoms are that the mysqld thread gets a signal 11, and the client
> never completes. "show processlist" gives, for example

>> How-To-Repeat:
> The following commands:
> mysql test < create.sql
> mysql test < insert.sql
> mysql test < insert.sql
> mysql test < insert.sql
> .
> .
> .
>=20
> executed rapidly from the shell seems to reliably hang the
> server. Here create.sql and insert.sql can be found at
>=20
> http://www.jlab.org/~marki/mysql_bug/create.sql
> http://www.jlab.org/~marki/mysql_bug/insert.sql

I was unable to repeat this. Can you try upgrading to the latest version
(3.23.55 currently, but 3.23.56 with numerous bug fixes should be out today
or tomorrow, if you could wait for that) and see if the problem persists. I=
f
possible, please try our binary/rpm instead of compiling on your own.

Rgds,
Indrek

--
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13964@lists.mysql.com
To unsubscribe, e-mail

Re: long write queries hang the server

Indrek,

I upgraded to 3.23.56 using the rpm's. Now I get

> mysql -uroot test < create.sql
> mysql -uroot test < insert.sql
> mysql -uroot test < insert.sql
ERROR 2013 at line 1: Lost connection to MySQL server during query
> mysql -uroot test < insert.sql
> mysql -uroot test < insert.sql
ERROR 2013 at line 1: Lost connection to MySQL server during query
> mysql -uroot test < insert.sql
> mysql -uroot test < insert.sql
ERROR 2013 at line 1: Lost connection to MySQL server during query
> mysql -uroot test < insert.sql
> mysql -uroot test < insert.sql
ERROR 2013 at line 1: Lost connection to MySQL server during query
..
..
..

So it seems to kind of work, but I get the "Lost connection" error every
other time. I can see the rows in the database, so something is getting in
there, but the row count indicates that only the error-free inserts did
anything.

On Fri, 14 Mar 2003, Indrek Siitan wrote:

> I was unable to repeat this. Can you try upgrading to the latest version
> (3.23.55 currently, but 3.23.56 with numerous bug fixes should be out today
> or tomorrow, if you could wait for that) and see if the problem persists. If
> possible, please try our binary/rpm instead of compiling on your own.

-- Mark




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13989@lists.mysql.com
To unsubscribe, e-mail

Re: long write queries hang the server

At 14:26 18/03/2003 -0500, Mark M. Ito wrote:
Hi,
>Indrek,
>
>I upgraded to 3.23.56 using the rpm's. Now I get
>
> > mysql -uroot test < create.sql
> > mysql -uroot test < insert.sql
> > mysql -uroot test < insert.sql
>ERROR 2013 at line 1: Lost connection to MySQL server during query
> > mysql -uroot test < insert.sql
> > mysql -uroot test < insert.sql

Yes you are right I was be able to repeat it with our 3.23.56
distribution tarball, below the backtrace. Thanks you for the
debug report:

/usr/local/mysql/libexec/mysqld: ready for connections
[New Thread 2051 (LWP 5987)]
[New Thread 3076 (LWP 5989)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 3076 (LWP 5989)]
0x082d06a8 in fn_format (to=3D0xbf3f900c "",
name=3D0x8464be8=20
"/usr/local/mysql/var/test/GPP_DC_WIRE_sector1_reg2.MYI", dsk=3D0x82e6071=
"",=20
form=3D0x8306c1a ".MYD", flag=3D6) at mf_format.c:52
52 DBUG_ENTER("fn_format");
Current language: auto; currently c
(gdb) backtrace
#0 0x082d06a8 in fn_format (to=3D0xbf3f900c "",
name=3D0x8464be8=20
"/usr/local/mysql/var/test/GPP_DC_WIRE_sector1_reg2.MYI", dsk=3D0x82e6071=
"",=20
form=3D0x8306c1a ".MYD", flag=3D6) at mf_format.c:52
#1 0x082a85ec in mi_open_datafile (info=3D0x0, share=3D0x84527d0)
at mi_open.c:1012
#2 0x082a5f62 in mi_open (
name=3D0xbf3fee3c "./test/GPP_DC_WIRE_sector1_reg2", mode=3D2,
open_flags=3D2) at mi_open.c:371
#3 0x08138014 in ha_myisam::open(char const*, int, unsigned) (
this=3D0x83a9ff8, name=3D0xbf3fee3c "./test/GPP_DC_WIRE_sector1_reg2",
mode=3D2, test_if_locked=3D2) at ha_myisam.cc:211
#4 0x08136507 in handler::ha_open(char const*, int, int) (
this=3D0x83a9ff8, name=3D0xbf3fee3c "./test/GPP_DC_WIRE_sector1_reg2",
mode=3D2, test_if_locked=3D2) at handler.cc:552
#5 0x08104584 in openfrm(char const*, char const*, unsigned, unsigned,=20
unsigned, st_table*) (name=3D0xbf3ff0cc "./test/GPP_DC_WIRE_sector1_reg2",
alias=3D0x83a5f88 "GPP_DC_WIRE_sector1_reg2", db_stat=3D39, prgflag=3D4=
4,
ha_open_flags=3D0, outparam=3D0x83adfa0) at table.cc:546
#6 0x08100b38 in open_unireg_entry (thd=3D0x83ab6c8, entry=3D0x83adfa0,
db=3D0x83a7f60 "test", name=3D0x83a5f58 "GPP_DC_WIRE_sector1_reg2",
alias=3D0x83a5f88 "GPP_DC_WIRE_sector1_reg2") at sql_base.cc:1270
#7 0x080ffd74 in open_table(THD*, char const*, char const*, char const*,=20
bool*) (thd=3D0x83ab6c8, db=3D0x83a7f60 "test",
---Type to continue, or q to quit---
table_name=3D0x83a5f58 "GPP_DC_WIRE_sector1_reg2",
alias=3D0x83a5f88 "GPP_DC_WIRE_sector1_reg2", refresh=3D0xbf3ff597)
at sql_base.cc:853
#8 0x08100f77 in open_ltable(THD*, st_table_list*, thr_lock_type) (
thd=3D0x83ab6c8, table_list=3D0x83a5fa8,
lock_type=3DTL_WRITE_CONCURRENT_INSERT) at sql_base.cc:1407
#9 0x08116335 in mysql_insert(THD*, st_table_list*, List&,=20
List >&, enum_duplicates, thr_lock_type) (thd=3D0x83ab6c8,
table_list=3D0x83a5fa8, fields=3D@0x83ab8f0, values_list=3D@0x83ab854,
duplic=3DDUP_ERROR, lock_type=3DTL_WRITE_CONCURRENT_INSERT)
at sql_insert.cc:146
#10 0x080eda27 in mysql_execute_command() () at sql_parse.cc:1601
#11 0x080ef9a3 in mysql_parse(THD*, char*, unsigned) (thd=3D0x83ab6c8,
inBuf=3D0x83b89d8 "INSERT GPP_DC_WIRE_sector1_reg2 ( minRunSource,=20
maxRunSource, author, comment, v_0001, v_0002, v_0003, v_0004, v_0005,=20
v_0006, v_0007, v_0008, v_0009, v_0010, v_0011, v_0012, v_0013, v_0014,=20
v_0015, v"..., length=3D39310) at sql_parse.cc:2386
#12 0x080ebb70 in do_command(THD*) (thd=3D0x83ab6c8) at sql_parse.cc:840
#13 0x080eaedb in handle_one_connection(void*) (arg=3D0xbf3f7d68)
at sql_parse.cc:558
#14 0x4002a1b0 in pthread_start_thread () from /lib/libpthread.so.0
#15 0x4002a22f in pthread_start_thread_event () from /lib/libpthread.so.0
(gdb)


--=20
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Miguel Angel Sol=F3rzano
/ /|_/ / // /\ \/ /_/ / /__ S=E3o Paulo - Brazil
/_/ /_/\_, /___/\___\_\___/
<___/ www.mysql.com=20


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13990@lists.mysql.com
To unsubscribe, e-mail

Re: long write queries hang the server

Indrek and Miguel,

Any ideas for a work-around?

On Tue, 18 Mar 2003, miguel solórzano wrote:

> At 14:26 18/03/2003 -0500, Mark M. Ito wrote:
> Hi,
> >Indrek,
> >
> >I upgraded to 3.23.56 using the rpm's. Now I get
> >
> > > mysql -uroot test < create.sql
> > > mysql -uroot test < insert.sql
> > > mysql -uroot test < insert.sql
> >ERROR 2013 at line 1: Lost connection to MySQL server during query
> > > mysql -uroot test < insert.sql
> > > mysql -uroot test < insert.sql
>
> Yes you are right I was be able to repeat it with our 3.23.56
> distribution tarball, below the backtrace. Thanks you for the
> debug report:


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14002@lists.mysql.com
To unsubscribe, e-mail

Re: long write queries hang the server

Mark M. Ito writes:
> Indrek and Miguel,
>
> Any ideas for a work-around?
>

It is just now fixed.

Thank you so much for your test case.

Fix will come with 4.0.13, but this is a patch:


===== myisam/mi_dynrec.c 1.23 vs edited =====
*** /tmp/mi_dynrec.c-1.23-14575 Thu Nov 7 03:53:06 2002
--- edited/myisam/mi_dynrec.c Thu Mar 20 18:38:45 2003
***************
*** 62,68 ****

extra= (ALIGN_SIZE(MI_MAX_DYN_BLOCK_HEADER)+MI_SPLIT_LENGTH+
MI_DYN_DELETE_BLOCK_HEADER+1);
! reclength= (info->s->base.pack_reclength+
_my_calc_total_blob_length(info,record)+ extra);
#ifdef NOT_USED /* We now support big rows */
if (reclength > MI_DYN_MAX_ROW_LENGTH)
--- 62,68 ----

extra= (ALIGN_SIZE(MI_MAX_DYN_BLOCK_HEADER)+MI_SPLIT_LENGTH+
MI_DYN_DELETE_BLOCK_HEADER+1);
! reclength= (info->s->base.pack_reclength+ info->s->base.pack_bits+
_my_calc_total_blob_length(info,record)+ extra);
#ifdef NOT_USED /* We now support big rows */
if (reclength > MI_DYN_MAX_ROW_LENGTH)

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14003@lists.mysql.com
To unsubscribe, e-mail