Igor Simic
5 years ago

PCRE error when using regxp in mysql XAMPP


What is PCRE?

The Perl Compatible Regular Expressions (PCRE) library is a set of functions that implement regular expression pattern matching using the same syntax and semantics as Perl 5.This library allow using regular expression in mysql queries.


If you are receiving error saying that 
this version of PCRE is compiled without UTF support at offset 0

and in Laravel it could look like this
QueryException in Connection.php line 729:
SQLSTATE[42000]: Syntax error or access violation: 1139 Got error 'this version of PCRE is compiled without UTF support at offset 0' from regexp (SQL: select count(*) as aggregate from `users` where slug RLIKE '^user-name(-[0-9]+)?$')

this means that your pcre library used in XAMPP version does not support UTF 8. This can be easily checked by executing this in your terminal:

/Applications/XAMPP/xamppfiles/bin/pcretest -C

 output  should look like this:
PCRE version 8.37 2015-04-28
Compiled with
8-bit support
No UTF-8 support
No Unicode properties support
No just-in-time compiler support
Newline sequence is LF
\R matches all Unicode newlines
Internal link size = 2
POSIX malloc threshold = 10
Parentheses nest limit = 250
Default match limit = 10000000
Default recursion depth limit = 10000000
Match recursion uses stack



HOW TO FIX IT?

In order to fix this issue we need to recompile PCRE library and here i show to do it:

  1. Download PCRE from this site: 
    https://sourceforge.net/projects/pcre/files/pcre/8.38/
  2. in the extracted folder execute these commands:
  3. ./configure --prefix=/Applications/XAMPP/xamppfiles --enable-utf8 --enable-unicode-properties
    make
    sudo make install
  4. wait till compiling is over and then copy newly created files to our library folder
  5. sudo cp .libs/* /Applications/XAMPP/xamppfiles/lib
    
    
    // if needed execute this line as well
    sudo cp .libs/* /Applications/XAMPP/xamppfiles/bin
    
  6. restart XAMPP


Now, if we check 
/Applications/XAMPP/xamppfiles/bin/pcretest -C

we should see that UTF 8 is supported:
PCRE version 8.38 2015-11-23
Compiled with
  8-bit support
  
UTF-8 support
  Unicode properties support
  No just-in-time compiler support
  Newline sequence is LF
  \R matches all Unicode newlines
  Internal link size = 2
  POSIX malloc threshold = 10
  Parentheses nest limit = 250
  Default match limit = 10000000
  Default recursion depth limit = 10000000
  Match recursion uses stack

That is all folks!


links:
http://www.mzan.com/article/35986243-error-when-using-regexp-in-mysql.shtml
https://community.apachefriends.org/f/viewtopic.php?f=29&t=74101