Posts Tagged ‘PCRE’

How to install MySQL 10.0.6-MariaDB and to compile lib_mysqludf_preg on CentOS 6.4

December 18, 2013 Leave a comment

In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.

Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.

So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at

root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
name = MariaDB
#baseurl =
baseurl =
root@seik-centos-01:[Wed Dec 18 23:56:39][/tmp]$ yum repolist | grep -i maria
mariadb          MariaDB                                                      10
root@seik-centos-01:[Wed Dec 18 19:25:49[/tmp]$ yum install MariaDB-server MariaDB-client MariaDB-cassandra-engine  MariaDB-devel MariaDB-shared -y
root@seik-centos-01:[Wed Dec 18 19:31:26][/etc/yum.repos.d]$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")
root@seik-centos-01:[Wed Dec 18 19:32:13][/etc/yum.repos.d]$ service mysql 
Usage: mysql  {start|stop|restart|reload|force-reload|status|configtest}  [ MySQL server options ]
root@seik-centos-01:[Wed Dec 18 19:32:18][/etc/yum.repos.d]$ service mysql  configtest
Testing MySQL configuration syntax SUCCESS! Syntax OK
root@seik-centos-01:[Wed Dec 18 19:32:26][/etc/yum.repos.d]$ service mysql start 
Starting MySQL. SUCCESS! 
root@seik-centos-01:[Wed Dec 18 19:32:32][/etc/yum.repos.d]$ service mysql status
 SUCCESS! MySQL running (1636)
root@seik-centos-01:[Wed Dec 18 19:32:37][/etc/yum.repos.d]$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.6-MariaDB MariaDB Server

How to compile lib_mysqludf_preg:

root@seik-centos-01:[Wed Dec 18 21:08:50]$ yum install git -y
root@seik-centos-01:[Wed Dec 18 21:09:12]$ mkdir -p /opt/installs
root@seik-centos-01:[Wed Dec 18 21:09:14]$ cd /opt/installs
root@seik-centos-01:[Wed Dec 18 21:10:50][/opt/installs]$ git clone
Initialized empty Git repository in /opt/installs/lib_mysqludf_preg/.git/
remote: Counting objects: 296, done.
remote: Compressing objects: 100% (139/139), done.
remote: Total 296 (delta 192), reused 256 (delta 155)
Receiving objects: 100% (296/296), 504.10 KiB | 311 KiB/s, done.
Resolving deltas: 100% (192/192), done.
root@seik-centos-01:[Wed Dec 18 21:11:20][/opt/installs]$ cd lib_mysqludf_preg/

In case on configure you get error: no acceptable C compiler found, you have to set up the development environment

root@seik-centos-01:[Wed Dec 18 21:11:53][/opt/installs/lib_mysqludf_preg]$ ./configure 
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking how to print strings... printf
checking for style of include used by make... GNU
checking for gcc... no
checking for cc... no
checking for cl.exe... no
configure: error: in `/opt/installs/lib_mysqludf_preg':
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details
root@seik-centos-01:[Wed Dec 18 21:12:12][/opt/installs/lib_mysqludf_preg]$ yum groupinstall Development tools -y

If you get an error: “Can’t find libpcre” , you need to install the pcre dev libs

root@seik-centos-01:[Wed Dec 18 21:23:15][/opt/installs/lib_mysqludf_preg]$ ./configure 
configure: setting libdir to mysql plugin dir /usr/lib64/mysql/plugin
checking for pcre-config... no
checking for PCRE - version >= 1... no
configure: error: "Can't find libpcre"
root@seik-centos-01:[Wed Dec 18 21:28:04][/opt/installs/lib_mysqludf_preg]$ yum install pcre-devel

If after the configure on make you get this WARNING: ‘aclocal-1.13’ is missing on your system

root@seik-centos-01:[Wed Dec 18 21:28:57][/opt/installs/lib_mysqludf_preg]$ make 
CDPATH="${ZSH_VERSION+.}:" && cd . && /bin/sh /opt/installs/lib_mysqludf_preg/config/missing aclocal-1.13 
/opt/installs/lib_mysqludf_preg/config/missing: line 81: aclocal-1.13: command not found
WARNING: 'aclocal-1.13' is missing on your system.
         You should only need it if you modified 'acinclude.m4' or
         '' or m4 files included by ''.
         The 'aclocal' program is part of the GNU Automake package:

         It also requires GNU Autoconf, GNU m4 and Perl in order to run:

make: *** [aclocal.m4] Error 127

The most easy way to fix this is to execute this:
aclocal && libtoolize –force && autoreconf

root@seik-centos-01:[Wed Dec 18 21:52:42][/opt/installs/lib_mysqludf_preg]$  aclocal && libtoolize --force && autoreconf
libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
libtoolize: linking file `config/'
libtoolize: Consider adding `AC_CONFIG_MACRO_DIR([m4])' to and
libtoolize: rerunning libtoolize, to keep the correct libtool macros in-tree.
libtoolize: Consider adding `-I m4' to ACLOCAL_AMFLAGS in
test/ wildcard *.test: non-POSIX variable name
test/ (probably a GNU make extension)
test/ `%'-style pattern rules are a GNU make extension
test/ `%'-style pattern rules are a GNU make extension

then again execute ./configure && make

root@seik-centos-01:[Wed Dec 18 21:52:55][/opt/installs/lib_mysqludf_preg]$ ./configure  
checking for pthread_getattr_np declaration... missing
configure: creating ./config.status
config.status: creating Makefile
config.status: creating test/Makefile
config.status: creating doc/Makefile
config.status: creating config.h
config.status: executing depfiles commands
config.status: executing libtool commands
root@seik-centos-01:[Wed Dec 18 21:53:05][/opt/installs/lib_mysqludf_preg]$ make -d
Must remake target `all-am'.
Successfully remade target file `all-am'.
make[2]: Leaving directory `/opt/installs/lib_mysqludf_preg'
Reaping winning child 0x0183f9f0 PID 3130 
Removing child 0x0183f9f0 PID 3130 from chain.
Successfully remade target file `all-recursive'.
make[1]: Leaving directory `/opt/installs/lib_mysqludf_preg'
Reaping winning child 0x01955110 PID 3129 
Removing child 0x01955110 PID 3129 from chain.
Successfully remade target file `all'.
root@seik-centos-01:[Wed Dec 18 21:53:13][/opt/installs/lib_mysqludf_preg]$ make install
root@seik-centos-01:[Wed Dec 18 22:03:05][/opt/installs/lib_mysqludf_preg]$ make installdb
/usr/bin/mysql  <./uninstalldb.sql
cat installdb.sql | sed 's/\.so/.dll/g' >installdb_win.sql
if test -f .libs/lib_mysqludf_preg.dll; then  \
		/usr/bin/mysql  <./installdb_win.sql; \
	else \
		/usr/bin/mysql  <./installdb.sql;\
root@seik-centos-01:[Wed Dec 18 22:03:19][/opt/installs/lib_mysqludf_preg]$ 

How to test the PCRE with MySQL MariaDB 10.0.6:

mysql root@seik-centos-01:[Thu Dec 19 00:09:27 2013][(none)]> SELECT CONVERT( PREG_REPLACE( '/fox/i' , 'dog' , 'The brown fox' ) USING UTF8) as replaced;
| replaced      |
| The brown dog |
1 row in set (0.00 sec)

and you are done

Slackware4Life 🙂

Categories: CentOS, MariaDB, MySQL Tags: , , , , ,

Drupal install of commerce_kickstart-7.x-1.8-core at AWS on two PostgreSQL 9.1.4 replicated nodes with Nginx and php-fpm

July 22, 2012 Leave a comment

I decided to try Drupal on two PostgreSQL nodes replicated as master and readonly slave.
The usual choice for webserver is nginx

Follow the and

# get the source and install it at the proper location like /usr/share/nginx/drupal

#set the proper nginx config 
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:36:27]:[/var/lib/pgsql]$ cat /etc/nginx/conf.d/drupal.conf
server {
#        root /var/lib/pgsql9/; 
        root /usr/share/nginx/drupal; ## <-- Your only path reference.
        location = /favicon.ico {
                log_not_found off;
                access_log off;
        location = /robots.txt {
                allow all;
                log_not_found off;
                access_log off;
        # This matters if you use drush
        location = /backup {
                deny all;
        # Very rarely should these ever be accessed outside of your lan
        location ~* \.(txt|log)$ {
                deny all;
        location ~ \..*/.*\.php$ {
                return 403;
        location / {
                # This is cool because no php is touched for static content
                try_files $uri @rewrite;
        location @rewrite {
                # Some modules enforce no slash (/) at the end of the URL
                # Else this rewrite block wouldn't be needed (GlobalRedirect)
                rewrite ^/(.*)$ /index.php?q=$1;
        location ~ \.php$ {
                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                #NOTE: You should have "cgi.fix_pathinfo = 0;" in php.ini
                include fastcgi_params;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
		#fastcgi_pass   unix:/var/run/php-fpm.sock;
        	fastcgi_index  index.php;
		fastcgi_param  SCRIPT_NAME        $fastcgi_script_name;

        # Fighting with ImageCache? This little gem is amazing.
        location ~ ^/sites/.*/files/imagecache/ {
                try_files $uri @rewrite;
        # Catch image styles for D7 too.
        location ~ ^/sites/.*/files/styles/ {
                try_files $uri @rewrite;
        location ~* \.(js|css|png|jpg|jpeg|gif|ico)$ {
                expires max;
                log_not_found off;

#check the nginx config :
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:42:02]:[/var/lib/pgsql]$ nginx -t -c /etc/nginx/nginx.conf
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

# and reload it
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:44:05]:[/var/lib/pgsql]$ service  nginx reload
Reloading nginx:  

# create pgsql user and db and set the pemirssions
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 11:55:33]:[~]$ createuser -U postgres -h --pwprompt --encrypted --no-createrole --no-createdb drupal 
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 12:06:25]:[~]$ createdb -U postgres -h --encoding=UTF8 --owner=drupal drupal

# set the psql user permissions at the slave with same password you set to the drupal user at the master:
root@ip-10-28-82-97-node-2:[Sun Jul 22 16:01:59]:[/var/lib/pgsql/data]$ cat  /var/lib/pgsql/data/pg_hba.conf | grep -i drupal
hostnossl       drupal          drupal             masterIP/32	 password

# ativate the user permissions 
root@ip-10-28-82-97-node-2:[Sun Jul 22 14:23:31]:[/var/lib/pgsql/data]$ service postgresql reload

#check the connection to slave:
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 16:03:33]:[/usr/share/nginx]$ echo "select version();" | psql -h slaveIP -U drupal drupal
Password for user drupal: 
 PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
(1 row)

#start the install process hitting the index.php
#in case you get <strong>Error not found</strong>  change the permissions of the drupal root directory to 0755:   
chmod -R 0755  /usr/share/nginx/drupal

# install some of the php modules requested by the install as gd, dom and mbstring:
yum install php-soap php-mbstring php-xml  -y
#then restart the php-fpm 
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:09:19]:[/var/lib/pgsql]$ service  php-fpm restart
Stopping php-fpm:                                          [  OK  ]
Starting php-fpm:                                          [  OK  ]

if you get an Warning message like this one:
Compilation failed: unknown option bit(s) set at offset 0 in user_validate_name() (line 645 of /usr/share/nginx/drupal/modules/user/user.module).

this is due to php PECL pcre extension update
there will be some more similar errors indicated at
+1606 /usr/share/nginx/drupal/includes/
+3726 /usr/share/nginx/drupal/includes/
+645 /usr/share/nginx/drupal/modules/user/user.module

the fastest way to fix it at AWS is to do yum update of pcre.x86_64

in general with AWS is better to do just yum update

then, everything works as expected,

do not forget to set the permissions of ./sites/default/settings.php to 0755 after the install

how to set the drupal to use the slave :
thank to padraig o’sullivan who posted
The settings.php file for the Drupal site needs to be updated to know about this slave database.

end of the story, now we have possible web cluster as the second node could be used for second web server, just some db connection settings have to be adjusted. and NFS with CacheFS for the web writable part of the drupal.

The drupal admin is owned by my daughter now … she is about to create the online store.