Bug #37671 crash on prepared statement + cursor + geometry + too many open files !
Submitted: 26 Jun 2008 15:59 Modified: 28 Jan 2009 22:00
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:5.0.66, 5.0.64, 5.0.60, 5.1.28, 6.0.6 OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: crash, Geometry, open_files_limit, prepared statements
Triage: Triaged: D1 (Critical) / R2 (Low) / E3 (Medium)

[26 Jun 2008 15:59] Shane Bester
Description:
the prepared statement that caused this crash was: "select polygon(?,?,?)" with some garbage parameters, however, could probably have been any other query.

server was complaining about out of memory errors for thousands of previous queries.  shortened stack trace:

mysqld.exe!select_union::send_data
mysqld.exe!JOIN::exec
mysqld.exe!mysql_select
mysqld.exe!handle_select
mysqld.exe!mysql_execute_command
mysqld.exe!mysql_open_cursor
mysqld.exe!Prepared_statement::execute
mysqld.exe!mysql_stmt_execute
mysqld.exe!dispatch_command
mysqld.exe!do_command
mysqld.exe!handle_one_connection
mysqld.exe!pthread_start

How to repeat:
do what you want to fill up then release memory, execute prepared statement like above, with read only cursor and prefetch (set to 5000) a few million times.  no easy way to repeat.
[3 Jul 2008 20:55] Shane Bester
some debug info for this serious crashing bug.

Attachment: bug37671_5.0.66_debug_info.txt (text/plain), 4.96 KiB.

[3 Jul 2008 21:08] Shane Bester
I found the cause of a crash.  when the materialized table cannot be created due to number of open_files being too close to open_files_limit, this crash happens.

How to repeat: start mysqld like this:

--open-files-limit=10 --table_open_cache=2 --log-warnings=2 --max_connections=3

Then run some prepared statements like I described earlier in the bug report that require materialized tables.  Will upload a .c testcase soon.
[3 Jul 2008 21:38] Shane Bester
That was hard to debug!

Run the attached testcase to crash server.  make sure that open_files_limit is quite low (at least lower than max_prepared_statements).

workaround: disable materialized cursors, set low max_prepared_statements, set higher open_files_limit, fix code to call mysql_stmt_close asap, and to not use geometry (or other weird?) types.
[3 Jul 2008 21:39] Shane Bester
testcase

Attachment: bug37671.c (text/plain), 3.42 KiB.

[8 Jul 2008 6:04] Shane Bester
Just wanted to clarify something.  I am purposely not calling mysql_stmt_close().  The reason is because I want to provoke the crash quicker (tmp table count will quickly use up all available file descriptors).  However, on normal servers this error can still be seen when the file limits are hit under legitimate load :)

This bug is of course much more likely on Windows (2048 posix fd limitation)..
I don't know if this crash is limited to only geometry types either. The scope may
well be higher, but some developer should tell us.
[8 Aug 2008 8:07] Shane Bester
there is a related bug #37956 in case the fixer wants to check both at the same time.
[25 Nov 2008 15:15] 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/59808

2722 Sergey Glukhov	2008-11-25
      Bug#37956 memory leak and / or crash with geometry and prepared statements!
      Bug#37671 crash on prepared statement + cursor + geometry + too many open files!
      if mysql_execute_command() returns error then free materialized_cursor object.
      is_rnd_inited is added to satisfy rnd_end() assertion
      (handler may be uninitialized in some cases)
[25 Nov 2008 17:13] 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/59828

2722 Sergey Glukhov	2008-11-25
      Bug#37956 memory leak and / or crash with geometry and prepared statements!
      Bug#37671 crash on prepared statement + cursor + geometry + too many open files!
      if mysql_execute_command() returns error then free materialized_cursor object.
      is_rnd_inited is added to satisfy rnd_end() assertion
      (handler may be uninitialized in some cases)
[8 Dec 2008 12:46] 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/60901

2722 Sergey Glukhov	2008-12-08
      Bug#37956 memory leak and / or crash with geometry and prepared statements!
      Bug#37671 crash on prepared statement + cursor + geometry + too many open files!
      if mysql_execute_command() returns error then free materialized_cursor object.
      is_rnd_inited is added to satisfy rnd_end() assertion
      (handler may be uninitialized in some cases)
[10 Dec 2008 14:14] 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/61216

2722 Sergey Glukhov	2008-12-10
      Bug#37956 memory leak and / or crash with geometry and prepared statements!
      Bug#37671 crash on prepared statement + cursor + geometry + too many open files!
      if mysql_execute_command() returns error then free materialized_cursor object.
      is_rnd_inited is added to satisfy rnd_end() assertion
      (handler may be uninitialized in some cases)
[6 Jan 2009 13:57] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[8 Jan 2009 21:32] Paul Dubois
Noted in 5.0.76 changelog.

Use of spatial data types in prepared statements could cause memory
leaks or server crashes.

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:40] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:28] Paul Dubois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:31] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:09] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 15:14] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 19:00] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[28 Jan 2009 22:00] Paul Dubois
Noted in 6.0.10 changelog.