this version of PCRE is compiled without UTF spt at offset 0

[somekindafreak]

I have just installed XAMPP 5.6.21 on a system running Mac OS 10.11.5. All usage of the term REGEXP within my SQL queries are returning ERROR 1139 (42000): Got error 'this version of PCRE is compiled without UTF support at offset 0' from regexp

MariaDB [NBA]> SELECT 'a' REGEXP '^[a-d]';
ERROR 1139 (42000): Got error 'this version of PCRE is compiled without UTF support at offset 0' from regexp

MariaDB [NBA]> SELECT * FROM `data_team` where `city` REGEXP 'New York';
ERROR 1139 (42000): Got error 'this version of PCRE is compiled without UTF support at offset 0' from regexp

Can anyone explain why this is happening and what I can do to solve it? Downloading and recompiling PCRE would be a painful option and I would need suggestions for doing it correctly. I don't even know what PCRE is exactly other than picking up `Perl Compatible Regular Expressions` while investigating this error

[Nobbie]

The message tells you the problem: this Database Version is compiled without support for UTF-8 in Regular Expressions. These are used in some SELECT Statements by some CMS (Wordpress, Joomla etc.) and/or some forum scripts. Or in you own SELECTS.

You have two options:

a) either do NOT choose UTF-8 for the Wordpress/Joomla/Or Whatever Installation, instead choose your local character code (i.e. ISO-8859-1 or similar). Do not use UTF-8 for databases.

b) or (if you really need UTF-8) write a mail or posting to Bitnami and ask them to build a new MariaDB release, including PCRE support for UTF.

[somekindafreak]

thanks for the reply Nobbie. the question is what to set and where in order to 'not choose UTF-8'. I don't need it and I am not using Wordpress/Joomla/or anything. I am firing my own queries from perl calls or at the mysql prompt. I thought your comment would have solved things as I changed my locale settings from en_US.UTF-8 to the following:'

bash-3.2$ locale
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL=

Even after a reboot and restart of MariaDB, I am getting the same error.

MariaDB [NFL]> SELECT 'a' REGEXP '^[a-d]';
ERROR 1139 (42000): Got error 'this version of PCRE is compiled without UTF support at offset 0' from regexp

My MariaDB variables read as:

MariaDB [NFL]> show variables like 'char%';
+--------------------------+------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Applications/XAMPP/xamppfiles/share/charsets/ |
+--------------------------+------------------------------------------------+
8 rows in set (0.00 sec)

Trying to set character_set_system responds with ERROR 1238 (HY000): Variable 'character_set_system' is a read only variable. The DEFAULT_CHARACTER_SET_NAME for all of my databases is latin1

Nothing in my.cnf seems to be locale oriented and grepping utf8 and character_set in /Applications/XAMPP/xamppfiles/etc/* doesn't give me any clues on any other setting to change to alleviate this error.

[somekindafreak]

solved it myself thanks to:

http://www.mzan.com/article/35986243-error-when-using-regexp-in-mysql.shtml

by downloading pcre-8.38 and running:

$ ./configure --prefix=/Applications/XAMPP/xamppfiles --enable-utf8 --enable-unicode-properties
$ make
$ sudo make install

I had backed up my /bin and /lib directories just in case. I now have my locale settings back to en_US.UTF-8 and it's working there too. If there's an easier way to fix this by simply switching some MariaDB or other setting, please share that for others.

The bigger question is why /Applications/XAMPP/xamppfiles/bin/pcre_config is compiled without utf-8 support in XAMPP and how this isn't a problem for everyone?

[Nobbie]

The bigger question is why /Applications/XAMPP/xamppfiles/bin/pcre_config is compiled without utf-8 support

I dont know, ask Bitnami.

and how this isn't a problem for everyone?

Because not everybody uses UTF-8 for the database collation, and/or not everybody uses regular expressions.

[xoristzatziki]

http://www.mzan.com/article/35986243-error-when-using-regexp-in-mysql.shtml
returns error. Is there any other link with instructions?
(posted in 2017, aka 21st century, using OS with UTF8 support)

[Nobbie]

Is there any other link with instructions?

Blind??

Two posts over yours....

Just in case:

by downloading pcre-8.38 and running:

$ ./configure --prefix=/Applications/XAMPP/xamppfiles --enable-utf8 --enable-unicode-properties
$ make
$ sudo make install