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
- 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.
- 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
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.
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).
- 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.
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).