InnoDB crash

Hi,

During the execution

INSERT INTO tab3 SELECT tab1 LEFT JOIN tab2 USING (varchfield1, varchfield2)
WHERE tab2.varchfield1 IS NOT NULL

query on two 1-2 million record tables, MySQL 4.0.5a crashes. I have found
several dumps of InnoDB monitor and the below info in the error log.

InnoDB: Warning: a long semaphore wait:
--Thread 45068 has waited at btr0sea.c line 103 for 625.00 seconds the
semaphore:
X-lock on RW-latch at 4506a668 created in file btr0sea.c line 128
a writer (thread id 45068) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0cur.c line 511
Last time write locked in file btr0sea.c line 1106
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
021203 13:46:30 InnoDB: Assertion failure in thread 24583 in file
sync0arr.c line 934
InnoDB: We intentionally generate a memory trap.

OS is Linux 2.4.18 w/glibc 2.2.5 on i686.

--
MySQL, database, sql, query, how-to-repeat



------------------------------------------------------------ ---------
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-thread13198@lists.mysql.com
To unsubscribe, e-mail

Re: InnoDB crash

Maciek,

can you send me the entire .err log?

Was the database under a high load at the time? Or did it appear genuinely
hung?

Do you have MySQL binlogging on?

This might be mutex starvation during the big join.

Regards,

Heikki
Innobase Oy


......................

Subject: InnoDB crash
From: Maciek Dobrzanski
Date: Tue, 3 Dec 2002 14:02:14 +0100


------------------------------------------------------------ ----------------
----

Hi,

During the execution

INSERT INTO tab3 SELECT tab1 LEFT JOIN tab2 USING (varchfield1, varchfield2)
WHERE tab2.varchfield1 IS NOT NULL

query on two 1-2 million record tables, MySQL 4.0.5a crashes. I have found
several dumps of InnoDB monitor and the below info in the error log.

InnoDB: Warning: a long semaphore wait:
--Thread 45068 has waited at btr0sea.c line 103 for 625.00 seconds the
semaphore:
X-lock on RW-latch at 4506a668 created in file btr0sea.c line 128
a writer (thread id 45068) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0cur.c line 511
Last time write locked in file btr0sea.c line 1106
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
021203 13:46:30 InnoDB: Assertion failure in thread 24583 in file
sync0arr.c line 934
InnoDB: We intentionally generate a memory trap.

OS is Linux 2.4.18 w/glibc 2.2.5 on i686.

--
MySQL, database, sql, query, how-to-repeat




------------------------------------------------------------ ---------
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-thread13199@lists.mysql.com
To unsubscribe, e-mail

Re: InnoDB crash

Maciek,

I may have found the bug, which was introduced in 4.0.5. If binlogging is
not switched on, queries of type

INSERT INTO ... SELECT ...

CREATE TABLE .... SELECT ...

may hang.

Workaround: switch binlogging on. The fix will be in 4.0.6. The bug does not
exist in the 3.23 branch.

Regards,

Heikki
Innobase Oy


ChangeSet
1.1448 02/12/03 18:16:48 heikki@hundin.mysql.fi +1 -0
row0sel.c:
Fix a hang introduced in 4.0.5 in INSERT INTO ... SELECT ... when
binlogging is not on

innobase/row/row0sel.c
1.27 02/12/03 18:16:39 heikki@hundin.mysql.fi +10 -2
Fix a hang introduced in 4.0.5 in INSERT INTO ... SELECT ... when
binlogging is not on

# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql-4.0

--- 1.26/innobase/row/row0sel.c Thu Nov 14 00:38:17 2002
+++ 1.27/innobase/row/row0sel.c Tue Dec 3 18:16:39 2002
@@ -2702,14 +2702,22 @@

unique_search_from_clust_index = TRUE;

- if (prebuilt->select_lock_type == LOCK_NONE
+ if (trx->mysql_n_tables_locked == 0
+ && prebuilt->select_lock_type == LOCK_NONE
&& trx->isolation_level > TRX_ISO_READ_UNCOMMITTED
&& trx->read_view) {

/* This is a SELECT query done as a consistent read,
and the read view has already been allocated:
let us try a search shortcut through the hash
- index */
+ index.
+ NOTE that we must also test that
+ mysql_n_tables_locked == 0, because this might
+ also be INSERT INTO ... SELECT ... or
+ CREATE TABLE ... SELECT ... . Our algorithm is
+ NOT prepared to inserts interleaved with the SELECT,
+ and if we try that, we can deadlock on the adaptive
+ hash index semaphore! */

if (btr_search_latch.writer != RW_LOCK_NOT_LOCKED) {
/* There is an x-latch request: release

----- Original Message -----
From: "Heikki Tuuri"
To:
Sent: Tuesday, December 03, 2002 5:12 PM
Subject: Re: InnoDB crash


> Maciek,
>
> can you send me the entire .err log?
>
> Was the database under a high load at the time? Or did it appear genuinely
> hung?
>
> Do you have MySQL binlogging on?
>
> This might be mutex starvation during the big join.
>
> Regards,
>
> Heikki
> Innobase Oy
>
>
> .....................
>
> Subject: InnoDB crash
> From: Maciek Dobrzanski
> Date: Tue, 3 Dec 2002 14:02:14 +0100
>
>
> ------------------------------------------------------------ --------------
--
> ----
>
> Hi,
>
> During the execution
>
> INSERT INTO tab3 SELECT tab1 LEFT JOIN tab2 USING (varchfield1,
varchfield2)
> WHERE tab2.varchfield1 IS NOT NULL
>
> query on two 1-2 million record tables, MySQL 4.0.5a crashes. I have found
> several dumps of InnoDB monitor and the below info in the error log.
>
> InnoDB: Warning: a long semaphore wait:
> --Thread 45068 has waited at btr0sea.c line 103 for 625.00 seconds the
> semaphore:
> X-lock on RW-latch at 4506a668 created in file btr0sea.c line 128
> a writer (thread id 45068) has reserved it in mode wait exclusive
> number of readers 1, waiters flag 1
> Last time read locked in file btr0cur.c line 511
> Last time write locked in file btr0sea.c line 1106
> InnoDB: Error: semaphore wait has lasted > 600 seconds
> InnoDB: We intentionally crash the server, because it appears to be hung.
> 021203 13:46:30 InnoDB: Assertion failure in thread 24583 in file
> sync0arr.c line 934
> InnoDB: We intentionally generate a memory trap.
>
> OS is Linux 2.4.18 w/glibc 2.2.5 on i686.
>
> --
> MySQL, database, sql, query, how-to-repeat
>
>
>


------------------------------------------------------------ ---------
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-thread13200@lists.mysql.com
To unsubscribe, e-mail