Hi!
Your email address gives the error:
"
DNS for host dev.noris.de is mis-configured
The following recipients did not receive this message:
<[EMAIL PROTECTED]>
"
Still one question: is this a deadlock of threads at all? Maybe the sorting
which mysqld does, or the fetches which:
mysql -q ... | program
executes take so long that InnoDB thinks the server has hung? How big is the
result set of your ORDER BY query in terms of rows and megabytes?
Regards,
Heikki
----- Original Message -----
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, March 03, 2003 10:05 AM
Subject: Re: innodb deadlock leads to server crash
> Hi!
>
> A deadlock of threads is a bug. It is is not connected to transactions or
> multiversioning.
>
> Is the problem repeatable in your computer?
>
> Can you compile a debug version of mysqld? Go to the source tree root
> directory /mysql/ and do:
>
> ./BUILD/compile-pentium-debug-max
>
> Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do:
>
> (gdb) info threads
>
> (gdb) thread 1
>
> (gdb) bt full
>
> ... and so on for all threads. Send the output to me.
>
> You could also try an official MySQL binary you can download from
> www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat
> suspicious and might produce broken binaries.
>
> I tried to repeat the hang in my computer, but did not succeed. Can you
send
> me what
>
> SHOW CREATE TABLE ticketlast;
>
> prints?
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> ---
> Order technical MySQL/InnoDB support at https://order.mysql.com/
> See http://www.innodb.com for the online manual and latest news on InnoDB
>
> sql query
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Monday, March 03, 2003 3:58 AM
> Subject: innodb deadlock leads to server crash
>
>
> > >Description:
> > A deadlock within innodb leads to a server crash.
> >
> > I have a large table which I need to update in-place. So one mysql
> > connection does a SELECT, and another updates the data. I thought
> > that, since innodb supports transactions and multiversioning, the
> > two should not block each other.
> >
> > Apparently, this is wrong and leads to a server crash.
> >
> > The relevant output from mysqld's server log is this:
> > ----------
> > SEMAPHORES
> > ----------
> > OS WAIT ARRAY INFO: reservation count 13947, signal count 13945
> > --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the
> semaphore:
> > X-lock on RW-latch at 40172668 created in file btr0sea.c line 128
> > a writer (thread id 13326) has reserved it in mode wait exclusive
> > number of readers 1, waiters flag 1
> > Last time read locked in file btr0sea.c line 683
> > Last time write locked in file btr0sea.c line 1117
> > --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the
> semaphore:
> > S-lock on RW-latch at 40172668 created in file btr0sea.c line 128
> > a writer (thread id 13326) has reserved it in mode wait exclusive
> > number of readers 1, waiters flag 1
> > Last time read locked in file btr0sea.c line 683
> > Last time write locked in file btr0sea.c line 1117
> > Mutex spin waits 662, rounds 8840, OS waits 94
> > RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31
> > ------------
> > TRANSACTIONS
> > ------------
> > Trx id counter 0 2108142
> > Purge done for trx's n:o < 0 2108136 undo n:o < 0 0
> > Total number of lock structs in row lock hash table 0
> > LIST OF TRANSACTIONS FOR EACH SESSION:
> > ---TRANSACTION 0 2107229, not started, OS thread id 10251
> > MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132
updater
> > ---TRANSACTION 0 2107136, not started, OS thread id 9226
> > MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132
updater
> > ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting
> index read
> > MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132
> updater preparing
> > select timestamp,seq,wann from ticketlast where ticket = '1823' and
> person = '3
> > 406'
> > ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276
> > , holds adaptive hash latch
> > MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132
> updater Sending data
> > select timestamp,ticket,person from ticketlast where timestamp >=
> FROM_UNIXTIME (916760612) order by ticket,person
> > Trx read view will not see trx with id >= 0 2108138, sees < 0 2108138
> > --------
> >
> > >How-To-Repeat:
> > Create a table with suitably many records.
> > mysql -q -e "select id from FOO order by id" | program
> >
> > The program would do a loop with "select * from FOO where id=$ID",
> > and do an occasional UPDATE.
> >
> > >Fix:
> > the two processes should not block each other.
> > Dropping the "-q" is not a solution; the table is too large.
> >
> >
> > >Submitter-Id: <submitter ID>
> > >Originator:
> > >Organization:
> > noris network AG, Nuernberg, Germany
> > >MySQL support: license
> > >Synopsis: innodb deadlock
> > >Severity: serious
> > >Priority: high
> > >Category: mysql
> > >Class: sw-bug
> > >Release: mysql-3.23.56
> >
> > >Environment:
> > System: Linux dev1.dev.noris.de 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.0)
> > Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++'
>
CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt
> i' LDFLAGS=''
> > LIBC:
> > lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 ->
> libc-2.2.5.so
> > -rwxr-xr-x 1 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
> > -rwxr-xr-x 1 root root 2194520 Feb 6 12:32
> /usr/lib/libc-client.a
> > Configure command: ./configure '--prefix=/usr' '--without-debug'
> '--enable-shared' '--without-mit-threads' '--libexecdir=/usr/sbin'
> '--localstatedir=/var/mysql' '--enable-thread-safe-client'
> '--sysconfdir=/etc' '--datadir=/usr/share' '--enable-large-files'
> '--without-readline' '--with-mysqld-user=mysql'
> '--with-unix-socket-path=/var/run/mysql.socket' '--enable-strcoll'
> '--with-comment=noris network MySQL' '--with-docs' '--with-bench'
> '--without-berkeley-db' '--without-bench' '--with-innodb'
> 'CPPFLAGS=-DTHREAD_SAFE_CLIENT'
>
'CXXFLAGS=-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt
> i'
> >
> >
> > ---------------------------------------------------------------------
> > 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 <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
