MySQL Bugs: #37671: crash on prepared statement + cursor + geometry + too many open files !

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:	Sergei Glukhov	CPU Architecture:	Any
Tags:	crash, Geometry, open_files_limit, prepared statements

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.

debug infos

Attachment: bug37671_5.0.64_full_stack_trace.txt (text/plain), 2.49 KiB.

actually, this isn't an out of memory crash. got it again with prepared statement, geometry function, and cursor, single thread and plenty of free memory:

thd->query at 000000000E2D7100=select mpolyfromtext('0x1a9d68a7e90069dd94c466c26d90b48055c8d23e5266f6a066045decbb66aedd2932c774020082f3e7a0a040fc01c89b22122438ef8ccc46cc9a928053ecc1706cbeac6048167baad0a540a620701841405814dc0290d4f425cf483c2e9e059e9950b4f0c2e42f80d45b21907030f853c27c8685')

mysqld-nt.exe!select_union::send_data
mysqld-nt.exe!JOIN::exec
mysqld-nt.exe!mysql_select
mysqld-nt.exe!handle_select
mysqld-nt.exe!mysql_execute_command
mysqld-nt.exe!mysql_open_cursor
mysqld-nt.exe!Prepared_statement::execute
mysqld-nt.exe!mysql_stmt_execute
mysqld-nt.exe!dispatch_command
mysqld-nt.exe!do_command
mysqld-nt.exe!handle_one_connection
mysqld-nt.exe!pthread_start
mysqld-nt.exe!_callthreadstart
mysqld-nt.exe!_threadstart
kernel32.dll!BaseThreadStart

got same crash on 5.0.60 this time on different geometry function:

thd->query at 000000000B388070=select startpoint('0xd94ca83dd484725cd45eb91723cc369dc080ae50927e61fae0462a08fc9e0cc74093f6773a1025e406248aabe0142cc0604a734e8d1e8042affaf70764a7e046a8')

and another:

thd->query at 000000000B3A2E10=select mpolyfromwkb('0xc8e49cb700cd07658c6f0964af9e351c2094b83d5874a01058a0de38b4344504a049e24ede80289908d4a0d05118f368cc24533447a8aa0a7838cf29f8f8ce71e4c81b341ecc38e814e8aec096afec946818e111caa777395f107f46fa50d3')

another: thd->query at 000000000B38F5E0=select linefromtext('0xb70e68d74c3030041260d1f59e1ac00cf7502c68f90378133e76904082e6d6f8f420084cc97041c239d400b492826cb46070701d72e0f85cf8fa7cde31415827177d004724ae9830bf78466d84c2a82646e828a63cbf90b278239620c85db05fee1bc03873','0x2037a3470d1092f460904ef9711a2510942c20bc7a29aea0f774f848161b4c1a5ea022c050043e7e3f982c92236e7c5924b0650018d8a034405b9acdf894001820ae741024244a7d38cc04d8e292fa689773e0')

some debug info for this serious crashing bug.

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

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.

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.

testcase

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

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.

there is a related bug #37956 in case the fixer wants to check both at the same time.

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)

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)

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)

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)

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)

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.

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)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

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)

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)

Setting status back to NDI pending merge to 6.0 tree.

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)

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)

Noted in 6.0.10 changelog.