#1 ✓resolved

Regular expression /(\s)+/ causes MySQL server to crash

Reported by rthurul | March 25th, 2011 @ 08:16 AM

I have noticed a possible bug into the preg_replace function. This query or other similar will always crash the mysql (tested on 5.0.77 on multiple servers)

SELECT PREG_REPLACE("/(\\s)+/", " ", "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t")

Did I missed anything with regards to this functionality?

Comments and changes to this ticket

  • Arnold

    Arnold March 25th, 2011 @ 03:38 PM

    • Tag set to crash
    • Title changed from “Possible bug” to “Regular expression /(\s)+/ causes MySQL server to crash”

    Needs to be tested and confirmed. If this is a bug, it most likely a problem of pcre and not lof this UDF.

    The captured group is overwritten a lot. This is not a case of catastrophic back-referencing though.

  • rthurul

    rthurul March 25th, 2011 @ 03:44 PM

    I am sure I have used double escaped \ \ s - probably there is a system display bug
    The problem usually appears where there are lot of occurences to be replaced


  • Rich Waters

    Rich Waters October 7th, 2011 @ 07:40 PM

    • State changed from “new” to “open”
    • Assigned user set to “Rich Waters”

    The bug here is invoked by the parenthesis matching every character in the subject combined with a large subject string. The \t and \s aren't really important. The same result occurs for:

    SELECT PREG_REPLACE("/(a)+/", "d", "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",-1);

    Remove the parenthesis and the problem goes away. So that's the first (and lamest :>) work around. If the above query is an exact representation of what you're trying to do, removing the parens should have no effect other than to stop the crashing.


    The second work around is to upgrade to mysql 5.1.x. I am not 100% positive that this will fix the problem, but I was unable to reproduce using 5.1.47 on OSX. I was able to reproduce using: 5.0.51a-24+lenny4 (Debian)

    I upgraded the libpcre on the problematic system to 8.13, but that, in itself did not fix the problem.

    Have there been any occurrences of this issue on a 5.1 Mysql Server?


    Finally, the bug here seems to be in the pcre_exec code in the libpcre library. It is not so much a bug as a known issue whereby the stack can get corrupted. Here's what their comments have to say about it:

    It turns out that on some non-Unix-like systems there are problems with
    programs that use a lot of stack. (This despite the fact that every last chip
    has oodles of memory these days, and techniques for extending the stack have
    been known for decades.) So....

    There is a fudge, triggered by defining NO_RECURSE, which avoids recursive
    calls by keeping local variables that need to be preserved in blocks of memory
    obtained from malloc() instead instead of on the stack. Macros are used to
    achieve this so that the actual code doesn't look very different to what it
    always used to.

    While we're experiencing the problem on a Unix system, I think that there is some interaction with the 5.0 UDF environment that is causing this problem. As such. the work around is to compile pcre and use the --disable-stack-for-recursion flag.

    ... Here's what I recommend:

    • download latest pcre
    • build it statically
    • export CFLAGS="-fPIC"
    • ./configure --disable-cpp --disable-shared --disable-stack-for-recursion --prefix=/opt/pcre

    Configure lib_mysqludf_preg:
    ./configure --with-pcre=/opt/pcre/

    And proceed accordingly.


    If this problem does actually exist on 5.1, I will investigate the issue further to see if there is a cleaner solution.

  • Travers Carter

    Travers Carter June 7th, 2012 @ 07:20 AM

    The attached patch prevent a pcre stack overflow from crashing mysqld by checking the available stack space and setting the pcre recursion limits appropriatly such that the stack shouldn't overflow. It doesn't actually fix the underlying problem, but it helps mitigate the damage significantly and tries to dump a helpful error message into the log.

    As an aside one possible workaround for the underlying problem is to raise the stack size in MySQL by setting the "thread_stack" variable to a higher value (it defaults to 256K).

  • Travers Carter

    Travers Carter June 8th, 2012 @ 07:05 AM

    Uploaded the wrong version of the patch, here it is again without the compile errors

  • Rich Waters

    Rich Waters March 25th, 2013 @ 10:54 PM

    • State changed from “open” to “resolved”

    Thanks Travers for this awesome patch.

    I've applied it (and added some autoconf to deal with missing pthread_getattr_np on OSX) to the dev-1.2 branch at https://github.com/mysqludf/lib_mysqludf_preg which is where the project is hosted now.

    Unfortunately, there were some merge conflicts due to the timing of how I've updated the project.

    Anyway, please post subsequent new issues on github.


Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

A library to access the functionality in the PCRE library, offering Perl 5 compatible regular expressions. This can be as an enhancement to (or replacement of) the RLIKE or REGEXP syntax native to MySQL, or to replace regular expressions in the client or application layer (such as the PHP preg_ functions).

Shared Ticket Bins

People watching this ticket


Referenced by