Bug #14496 Crash or strange results with prepared statement, MATCH and FULLTEXT

Submitted: 30 Oct 2005 22:45 Modified: 14 Feb 2006 2:50
Reporter: Michael Wallner (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.0.15-nt, 5.0.16-BK, 4.1.16-BK, 4.1.14 OS:Windows (Windows, Linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[30 Oct 2005 22:45] Michael Wallner
Description:
Some MySQL people on IRC necessitated me to try some code.

Obviously the didn't tell me that it may cause my mysqld to restart, because I wouldn't have to make this report otherwise ;)

0x815460b handle_segfault + 523
0x4005ffe1 _end + 933956433
0x4030b409 _end + 936755065
0x80f3f81 _ZN4Item4sendEP8ProtocolP6String + 433
0x81492d7 _ZN11select_send9send_dataER4ListI4ItemE + 167
0x8187976 _Z8end_sendP4JOINP13st_join_tableb + 182
0x818dca7 _Z10sub_selectP4JOINP13st_join_tableb + 263
0x81913ed _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 781
0x819b52b _ZN4JOIN4execEv + 3931
0x819c597 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 375
0x819ccc4 _Z13handle_selectP3THDP6st_lexP13select_result + 196
0x8168354 _Z21mysql_execute_commandP3THD + 9620
0x81a2856 _Z12execute_stmtP3THDP18Prepared_statementP6Stringb + 406
0x81a2d65 _Z18mysql_stmt_executeP3THDPcj + 485
0x816b14e _Z16dispatch_command19enum_server_commandP3THDPcj + 3166
0x816b606 _Z10do_commandP3THD + 118
0x816be6b handle_one_connection + 1899
0x40058f3c _end + 933927596
0x402ad8ba _end + 936371242

How to repeat:
<?php
$dbh = new PDO('mysql:host=localhost;dbname=', '', '');
$dbh->exec('create table foo ( bar text NULL, FULLTEXT KEY bar (bar) )');
/* init some data */
$dbh->exec('insert into foo (bar) values ( "This is row sample")');
$dbh->exec('insert into foo (bar) values ( "This is row foobar" )');
$dbh->exec('insert into foo (bar) values ( "This is row sample")');
$dbh->exec('insert into foo (bar) values ( "This is row another" )');
$dbh->exec('insert into foo (bar) values ( "This is row wordlist" )');
$dbh->exec('insert into foo (bar) values ( "This is row random" )');

$match =  "MATCH(bar) AGAINST ('foobar sample')";
$sql = "select bar, $match as afoo from foo where $match";
$stmt = $dbh->prepare($sql)
  or print_r($dbh->errorInfo());

print "First Exec\n";
$stmt->execute() or print_r($stmt->errorInfo());

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  print "{$row['afoo']}: {$row['bar']}\n";
}

print "\nSecond Exec\n";
$stmt->execute() or print_r($stmt->errorInfo());
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  print "{$row['afoo']}: {$row['bar']}\n";
}

$dbh->exec('drop table foo');
?>
[31 Oct 2005 9:38] Valeriy Kravchuk
Thank you for a bug report. I was able to repeat this crash om 5.0.15-nt using mysql client:

mysql> create table foo ( bar text NULL, FULLTEXT KEY bar (bar) ) engine=MyISAM;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into foo (bar) values ( "This is row sample");
Query OK, 1 row affected (0.02 sec)

mysql> insert into foo (bar) values ( "This is row foobar");
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row sample");
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row another" );
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row wordlist" );
Query OK, 1 row affected (0.04 sec)

mysql> insert into foo (bar) values ( "This is row random" );
Query OK, 1 row affected (0.01 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    -> from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.03 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    -> from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)

So, it works OK two times whne PS is not used. Let's try to prepare it:

mysql> prepare stmt2 from "select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    "> from foo where MATCH(bar) AGAINST ('foobar sample')";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.00 sec)

mysql> execute stmt2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

This is a crash on Windows. 

Now let's come to Linux. Both 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com) and 4.1.16-BK-debug (ChangeSet@1.2450, 2005-10-27 10:20:38-07:00, jimw@mysql.com) gave me the following results (with the same table and data) on Fedora Core 1:

mysql> prepare stmt2 from "select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
from foo where MATCH(bar) AGAINST ('foobar sample')";
Query OK, 0 rows affected (0,00 sec)
Statement prepared

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |                0 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.16-debug |
+--------------+
1 row in set (0,00 sec)

This different results is a bug also. Without PS we get the consistent results:

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo  from foo where
MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

Your PHP script also works and give some strange results for me on 4.1.16 and 5.0.16, but no crash. But I think that simple mysql is enough to demonstrate the erronious behaviour and crash.
[1 Feb 2006 16:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2015
[7 Feb 2006 19:42] Sergey Vojtovich
Fixed that fulltext query using PS results in unexpected behaviour
when executed 2 or more times.

Fixed in 4.1.19, 5.0.19. Wasn't able to push into 5.1 yet.
[14 Feb 2006 2:50] Paul DuBois
Noted in 4.1.19, 5.0.19, 5.1.6 changelogs.

        <para>
          A <literal>FULLTEXT</literal> query in a prepared statement
          could result in unexpected behavior. (Bug #14496)
        </para>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.