Archive

Posts Tagged ‘CentOS’

Connect MySQL to sqlite3 database or to flat txt file via CONNECT engine of MariaDB 10.0.7 on Fedora/CentOS

January 31, 2014 Leave a comment

You might don’t know that, but the MariaDB engines are amazing ..
More info you can find here:
https://mariadb.com/kb/en/mariadb-storage-engines/

I was teased by CONNECT engine : https://mariadb.com/kb/en/connect/

What is good for ? Well, I leave that to your imagination, as you could have MariaDB as a spider connected and managing InnoDB tables, flat files, MS ACCESS databases, MS Excel files … all that in same time.
So how to use it?
By the documentation :
1. https://mariadb.com/kb/en/loading-the-connect-handler/
check if there is CONNECT already installed, if not, install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:14 2014][(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                            | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                         | NO           | NO   | NO         |
| CASSANDRA          | YES     | Cassandra storage engine                                       | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Its not installed by default for MariaDB 10.0.7
So lets install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:19 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/ha_connect.so' (errno: 2, cannot open shared object file: No such file or directory)

My first install failed and I had to install MariaDB-connect-engine package from MariaDB

$ yum info MariaDB-connect-engine
Name        : MariaDB-connect-engine
Arch        : x86_64
Version     : 10.0.7
Release     : 1
Size        : 4.3 M
Repo        : installed
From repo   : mariadb
Summary     : MariaDB: a very fast and robust SQL database server
URL         : http://mariadb.org
License     : GPL
Description : MariaDB: a very fast and robust SQL database server
            : 
            : It is GPL v2 licensed, which means you can use the it free of charge under the
            : conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).
            : 
            : MariaDB documentation can be found at http://kb.askmonty.org/
            : MariaDB bug reports should be submitted through https://mariadb.atlassian.net/


$ cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/yum/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


then again

mysql root@mariadb-10.0.7:[Wed Jan 29 09:40:45 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
Query OK, 0 rows affected (0.00 sec)
mysql root@mariadb-10.0.7:[Wed Jan 29 09:44:56 2014][(none)]> pager grep CONNECT
PAGER set to 'grep CONNECT'
mysql root@mariadb-10.0.7:[Wed Jan 29 09:45:24 2014][(none)]> show engines;
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                            | Transactions | XA   | Savepoints |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                 | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                              | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                              | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables          | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys         | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                 | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                             | NO           | NO   | NO         |
| CASSANDRA          | YES     | Cassandra storage engine                                           | NO           | NO   | NO         |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
12 rows in set (0.00 sec)

Now, sqlite3
UnixODBC was installed along with the MariaDB-connect-engine package, but I was not a able to find SQLite UnixODBC driver installed. Neither I was able to get if from the Fedora repos.
Checking for the http://www.unixodbc.org/drivers.html I ended up with compiling the SQLite ODBC Driver from http://www.ch-werner.de/sqliteodbc/

The instructions are good enough for me:
1. get the source from

$ wget http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.996.tar.gz

2. Untar it :

$ tar xvf sqliteodbc-0.996.tar.gz

3. go to the source directory:

$ cd sqliteodbc-0.996

4. compile it

$ ./configure
..
...
configure: WARNING: SQLite4 header file and source not found
configure: error: No usable SQLite header/library on this system

4.1 In case you get the No usable SQLite header/library error, installs the sqlite-devel package

$ yum install sqlite-devel
sqlite-devel.x86_64 0:3.6.20-1.el6 will be installed

4.2 run configure again

$ ./configure
..
...
checking for ODBC headers and libraries... no
configure: error: ODBC header files and/or libraries not found

4.3 In case you get error: ODBC header files and/or libraries not found, install the unixODBC-devel package:

$ yum install unixODBC-devel
Package unixODBC-devel.x86_64 0:2.2.14-12.el6_3 will be installed

4.4 run configure again:

$ ./configure
make 
make install

5. Check the installed ODBC drivers:

$ odbcinst -q -d
[PostgreSQL]
[MySQL]
$ cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

6. Install the sqlite3 odbc driver:

$ nano /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/local/lib/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so
Threading=2
 
$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[SQLite]

So the SQLite ODBC driver was installed.

In a view to trace the CONNECT engine , its good to create connect.ini file in the mysql directory

$ cat /var/lib/mysql/connect.ini 
[CONNECT]
Trace=2

The CONNECT engine errors will be in the mysql error.log, in my case mariadb-10.0.7.err

How to create a MySQL table using sqlite3 database?
create the sqlite3 table :

root@mariadb-10.0.7:[Wed Jan 29 10:57:39][/usr/lib64/mysql]$ sqlite3 maria-sqlite3.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE DEPARTMENT(
   ...>     ID INT PRIMARY KEY      NOT NULL,
   ...>     DEPT           CHAR(50) NOT NULL,
   ...>     EMP_ID         INT      NOT NULL
   ...>  );
sqlite> .tables
DEPARTMENT
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(1,'sales',1);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(2,'sales',2);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(3,'sales',3);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(4,'marketing',4);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(5,'marketing',5);

note,  its in /usr/lib64/mysql
its good to place the database in a place where the mysql user can read/write it.
root@mariadb-10.0.7:[Wed Jan 29 11:04:14][/usr/lib64/mysql]$ mv maria-sqlite3.db /tmp/
root@mariadb-10.0.7:[Wed Jan 29 11:04:20][/usr/lib64/mysql]$ chmod 0777 /tmp/maria-sqlite3.db
root@mariadb-10.0.7:[Wed Jan 29 11:07:26][/usr/lib64/mysql]$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.7-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:27 2014][(none)]> create database sqlite3;
Query OK, 1 row affected (0.00 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:39 2014][(none)]> use sqlite3                                                                                                                            Database changed
mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:57 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.13 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:03 2014][sqlite3]> select * from my_dept;
+----+-----------+--------+
| ID | DEPT      | EMP_ID |
+----+-----------+--------+
|  1 | sales     |      1 |
|  2 | sales     |      2 |
|  3 | sales     |      3 |
|  4 | marketing |      4 |
|  5 | marketing |      5 |
+----+-----------+--------+
5 rows in set (0.00 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:41 2014][sqlite3]> show create table my_dept \G
*************************** 1. row ***************************
       Table: my_dept
Create Table: CREATE TABLE `my_dept` (
  `ID` int(9) NOT NULL,
  `DEPT` varchar(50) NOT NULL,
  `EMP_ID` int(9) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENT'
1 row in set (0.00 sec)

In case you have the CONNECT engine trace set to 2, you will see in the mysql error log something like:

 DBMS: SQLite, Version: 3.6.20, rc=0
ODBCColumns: max=4096 len=0,0,0
Getting col results ncol=12
Columns: NBCOL=12 NBLIN=3
s_init: CREATE TABLE whatever (`ID` INT(9) NOT NULL,`DEPT` VARCHAR(50) NOT NULL,`EMP_ID` INT(9) NOT NULL) TABLE_TYPE='ODBC' TABNAME='DEPARTMENT' CONNECTION='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;'
ColDB: am=100 colname=ID tabname=my_dept num=0
cdp(1).Name=ID cp=(nil)
 making new ODBCCOL C1 ID at 0x7f1259400420
colp=0x7f1259400420
ColDB: am=100 colname=DEPT tabname=my_dept num=0
cdp(2).Name=DEPT cp=(nil)
 making new ODBCCOL C2 DEPT at 0x7f12594004b8
colp=0x7f12594004b8
ColDB: am=100 colname=EMP_ID tabname=my_dept num=0
cdp(3).Name=EMP_ID cp=(nil)
 making new ODBCCOL C3 EMP_ID at 0x7f1259400550
colp=0x7f1259400550
....
DBMS: SQLite, Version: 3.6.20, rc=0
Prepare hstmt=0x7f12ebc59f00 INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (?,?,?)
 setting INTEGER to: 1
 setting INTEGER to: 1
S1000: [unixODBC][SQLite]attempt to write a readonly database (8), Native=8
ODBC CloseDB: closing my_dept

In case you get read only table :

mysql root@mariadb-10.0.7:[Wed Jan 29 11:09:51 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values(6,'IT-DBA',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT

It seems you have your SQLite db in a folder where your database user /mysql/ must have write permissions,but does not.
Most probably you have selinux activated.
How to fix that without disabling the selinux? The best way is move the SQLite db file to the mysql data directory : /var/lib/mysql/, or to create a separated directory where mysql shell user has write permissions.

mysql root@mariadb-10.0.6:[Fri Jan 31 12:06:54 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values (6,'mysql',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT
mysql root@mariadb-10.0.6:[Fri Jan 31 12:07:27 2014][sqlite3]> drop table my_dept;
Query OK, 0 rows affected (0.00 sec)

Move the db file to MySQL data directory :

root@mariadb-10.0.6:[Fri Jan 31 12:09:15][/var/lib/mysql]$ cp -rp /tmp/maria-sqlite3.db  ./
mysql root@mariadb-10.0.6:[Fri Jan 31 12:08:17 2014][sqlite3]> create table my_dept_new  engine=CONNECT table_type=ODBC tabname='DEPARTMENT'  Connection='Driver=SQLite;Database=/var/lib/mysql/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.11 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:09:57 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
+----+------------+--------+
6 rows in set (0.01 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:02 2014][sqlite3]> insert into my_dept_new (ID,DEPT,EMP_ID) values (6,'mysql',6);                                                                        Query OK, 1 row affected (0.08 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:07 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
|  6 | mysql      |      6 |
+----+------------+--------+
7 rows in set (0.01 sec)

So now we have R/W sqlite3 table managed by MariaDB 10.0.7

Advertisements

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 ftp.ulak.net.tr


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
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
#baseurl = http://yum.mariadb.org/10.0/centos6-amd64
baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
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 https://github.com/mysqludf/lib_mysqludf_preg.git
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
         'configure.ac' or m4 files included by 'configure.ac'.
         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/ltmain.sh'
libtoolize: Consider adding `AC_CONFIG_MACRO_DIR([m4])' to configure.ac and
libtoolize: rerunning libtoolize, to keep the correct libtool macros in-tree.
libtoolize: Consider adding `-I m4' to ACLOCAL_AMFLAGS in Makefile.am.
test/Makefile.am:13: wildcard *.test: non-POSIX variable name
test/Makefile.am:13: (probably a GNU make extension)
test/Makefile.am:22: `%'-style pattern rules are a GNU make extension
test/Makefile.am:25: `%'-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;\
	fi
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: , , , , ,

Yum clean up / rollback RHEL 6 / CentOS packages to the initial status.

July 20, 2013 Leave a comment

In short, playing with RDO – the RedHat OpenStack install script /puppet based .. yummm / I needed several times to rollback the RHEL installed machines to their initial state.

So, in short, in a view to clean everything to the “after install” status :

# if you are brave heart, execute it right away 🙂 
yum history list all | sed  '/D, E/d;/Erase/d' | awk '/^ *[[:digit:]]/ {print $1}' | sed  '/^1$/d;' | while read crap; do echo yum history undo $crap -y;done
# check what has been done : 
yum history list all

Migrating MySQL 5.5.25a jiradb ERROR 2013 (HY000) on huge single db import

January 18, 2013 Leave a comment

well, I incremented max_allowed_packet from 16M to 512M
anyway, I got the same error on the next clean import.
so decided to find a workaround.
so, how to get the data separated from the ddl statements:

# get the tables names into the insert statement, its better to have that in file for future usage
sed '/^INSERT INTO/!d;s/ VALUES.*$//' jiradb.20130118.sql | sort | uniq > tablas.como.nombres.txt

then how to get the data separated:

root@jiragg:[Fri Jan 18 15:26:33]:[/usr/local/BACKUP]$ cat make.inserts.sh
#!/bin/sh
# trim function thank to http://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-bash-variable
# and http://codesnippets.joyent.com/posts/show/1816
trim() {
    local var=$1
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo -n "$var"
}

while read tabname
do
        tablename=$(trim $(echo $tabname | sed 's/INSERT INTO//;s/[[:punct:]]*//g'))
        echo "${tabname}:=>${tablename}"
        sed "/^INSERT INTO \`${tablename}\` VALUES/!d" /usr/local/BACKUP/jiradb.20130118.sql | gzip > "/usr/local/BACKUP/${tablename}.jiradb.20130118.sql.gz"
done < tablas.como.nombres.txt

how to get the DDLs:

sed '/^INSERT INTO/d' jiradb.20130118.sql > non.insert.jiradb.20130118.sql

how to import the whole jiradb:

# create the empty database
mysql --defaults-file=~/..credentials.jira -e 'create database jiradb;'
#import the DDLs:
mysql --defaults-file=~/..credentials.jira jiradb < /usr/local/BACKUP/non.insert.jiradb.20130118.sql
# make a list of the complressed datafiles per table:
find /usr/local/BACKUP/ -type f -name "*sql.gz"  > list.import.tables.txt
# execute simple export script 
root@jiragg:[Fri Jan 18 15:35:05]:[/usr/local/BACKUP]$ cat import.tables.sh
#!/bin/sh
while read tablefile
do
	echo -n "importing ${tablefile}: "
	zcat "${tablefile}" |  mysql --defaults-file=~/..credentials.jira jiradb
	echo ""
done < list.import.tables.txt

and that is it
anyway do not forget to increment the max_allowed_packet

Slackware4Life !

Categories: bash, CentOS, Linux, MySQL, RHEL Tags: , , ,

Add a new GlusterFS volume to be used by puppet master on CentOS based HA cluster

December 21, 2012 2 comments

I have puppet master running at CentOS based HA cluster of two nodes.
The install is done on two real machines connected by local direct link (crossover).
Its done using Corosync, Pacemaker, GlusterFS and MySQL

I could write the full install procedure later, now I will just add the after install creation of an additional GluasterFS volume.

the internal IPs of the two nodes are 10.0.0.4 (currently active) and 10.0.0.5m and we work at the active one :

Create the the new volume named puppe-core data directory at the bigger partition /home on both the nodes 10.0.0.{4,5}

root@puppetmaster4:[Fri Dec 21 09:32:08]:[~]$ mkdir /home/gluster-puppet-core
root@puppetmaster4:[Fri Dec 21 09:33:21]:[~]$ ssh 10.0.0.5 mkdir /home/gluster-puppet-core

Create the puppet-core glusterfs volume ONLY at ONE of the nodes, like at 10.0.0.4 :

root@puppetmaster4:[Fri Dec 21 09:33:35]:[~]$ gluster volume create puppet-core replica 2 transport tcp 10.0.0.4:/home/gluster-puppet-core 10.0.0.5:/home/gluster-puppet-core
Creation of volume puppet-core has been successful. Please start the volume to access data.

Set the allowed hosts list for that volume

root@puppetmaster4:[Fri Dec 21 09:34:16]:[~]$ gluster volume set puppet-core auth.allow  10.0.0.4,10.0.0.5
Set volume successful

Start the GlusterFS volume

root@puppetmaster4:[Fri Dec 21 09:35:03]:[~]$ gluster volume start puppet-core
Starting volume puppet-core has been successful

Check the status:

root@puppetmaster4:[Fri Dec 21 09:35:25]:[~]$ gluster volume info
 
Volume Name: puppet-mysql
Type: Replicate
Volume ID: af4b5a20-22ac-4b7d-a2b3-bda47d90ba92
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 10.0.0.4:/home/puppet-mysql
Brick2: 10.0.0.5:/home/puppet-mysql
Options Reconfigured:
auth.allow: 10.0.0.5,10.0.0.4
 
Volume Name: puppet
Type: Replicate
Volume ID: 41136ba2-9063-4b6e-bd86-5f6a01ae12b4
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 10.0.0.4:/home/gluster-puppet
Brick2: 10.0.0.5:/home/gluster-puppet
Options Reconfigured:
auth.allow: 10.0.0.4,10.0.0.5
 
Volume Name: puppet-files
Type: Replicate
Volume ID: 59b4c6c7-aa7e-440f-8372-2b4e141da703
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 10.0.0.4:/home/puppet-files
Brick2: 10.0.0.5:/home/puppet-files
Options Reconfigured:
auth.allow: 10.0.0.5,10.0.0.4
 
Volume Name: puppet-config
Type: Replicate
Volume ID: 9200a32a-d4ef-46ce-a64e-b58c2ccbe49d
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 10.0.0.4:/home/puppet-config
Brick2: 10.0.0.5:/home/puppet-config
Options Reconfigured:
auth.allow: 10.0.0.5,10.0.0.4
 
Volume Name: puppet-core
Type: Replicate
Volume ID: fc2cdbd6-3b94-4adc-b499-f3fdb452976b
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 10.0.0.4:/home/gluster-puppet-core
Brick2: 10.0.0.5:/home/gluster-puppet-core
Options Reconfigured:
auth.allow: 10.0.0.4,10.0.0.5

Make the mount point

root@puppetmaster4:[Fri Dec 21 09:44:16]:[/]$ mkdir /puppet-core
root@puppetmaster4:[Fri Dec 21 09:44:37]:[/]$ ssh 10.0.0.5 mkdir /puppet-core

Create the fstab records on both the nodes as local mounts:

root@puppetmaster4:[Fri Dec 21 09:44:42]:[/]$ echo "10.0.0.4:/puppet-core  /puppet-core   glusterfs  defaults  0  0" >> /etc/fstab
root@puppetmaster4:[Fri Dec 21 09:44:45]:[/]$ ssh 10.0.0.5 echo "10.0.0.5:/puppet-core  /puppet-core   glusterfs  defaults  0  0" >> /etc/fstab

Mount the volumes:

root@puppetmaster4:[Fri Dec 21 09:44:58]:[/]$  mount /puppet-core
root@puppetmaster4:[Fri Dec 21 09:44:58]:[/]$  ssh 10.0.0.5 mount /puppet-core

set the proper ownership

root@puppetmaster4:[Fri Dec 21 09:48:53]:[/]$ ssh 10.0.0.5 chown -R puppet:puppet /puppet-core/
root@puppetmaster4:[Fri Dec 21 09:49:04]:[/]$ chown -R puppet:puppet /puppet-core

Now you may start using it, its replicated between the two nodes.

Compile svn kannel sqlbox and opensmmp with PostgreSQL and gsoap support at CentOS 6.2 x86_64 GNU/Linux

November 29, 2012 2 comments

1, install PostgreSQL 9.2 from the official postgresql.org repository

yum -y install wget bash-completion
wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm
yum localinstall --nogpgcheck pgdg-centos92-9.2-6.noarch.rpm
yum install postgresql92-server postgresql92-plperl postgresql92-pltcl postgresql92-devel postgresql92-docs postgresql92-libs postgresql92-plpython postgresql92-contrib -y 
yum install skytools-92 skytools-92-modules pgtune pgbouncer pgagent_92 pg_top92 bucardo

2. install kannel with PostgreSQL and soap interface

yum install automake  pcre* mlocate -y
yum groupinstall 'Development Tools'
yum install libxml2-devel -y
yum install openssl-devel -y 
mkdir /opt/installs/kannel.svn &amp;&amp; cd /opt/installs/kannel.svn
svn co https://svn.kannel.org/gateway/trunk
cd /opt/installs/
svn co https://gsoap2.svn.sourceforge.net/svnroot/gsoap2 gsoap2
cd gsoap2
./configure

The build will fail with the following error :

make -j3
cd . &amp;&amp; /bin/sh /opt/installs/gsoap2/missing --run autoconf
 cd . &amp;&amp; /bin/sh /opt/installs/gsoap2/missing --run automake-1.10 --foreign 
/opt/installs/gsoap2/missing: line 46: automake-1.10: command not found
WARNING: `automake-1.10' is needed, and you do not seem to have it handy on your
         system.  You might have modified some files without having the
         proper tools for further handling them.  Check the `README' file,
         it often tells you about the needed prerequirements for installing
         this package.  You may also peek at any GNU archive site, in case
         some other package would contain this missing `automake-1.10' program.
make: *** [Makefile.in] Error 1
make: *** Waiting for unfinished jobs....
aclocal.m4:14: error: this file was generated for autoconf 2.61.
You have another version of autoconf.  If you want to use that,
you should regenerate the build system entirely.
aclocal.m4:14: the top level
autom4te: /usr/bin/m4 failed with exit status: 63
WARNING: `autoconf' is missing on your system.  You should only need it if
         you modified `configure.in'.  You might want to install the
         `Autoconf' and `GNU m4' packages.  Grab them from any GNU
         archive site.

After some digging into the source and googling in deep, the fix is simple, but very efficient …:

cd /opt/installs/gsoap2/
touch config.h.in
touch Makefile.in
touch configure
# NOW you can build it 🙂 
make -j3
.....
**  The gSOAP code generator for C and C++, soapcpp2 release 2.8.11
**  Copyright (C) 2000-2012, Robert van Engelen, Genivia Inc.
**  All Rights Reserved. This product is provided "as is", without any warranty.
**  The soapcpp2 tool is released under one of the following two licenses:
**  GPL or the commercial license by Genivia Inc.

Saving wsdlStub.h annotated copy of the input declarations
Saving xmime.nsmap namespace mapping table
Saving wsdlH.h interface declarations
Saving wsdlC.cpp XML serializers

Compilation successful
....
# install it : 
make -j3  install 
....
+--------------------------------------------------------+
| You now have successfully built and installed gsoap.   |
|                                                        |
| You can link your programs with -lgsoap++ for          |
| C++ projects created with soapcpp2 and you can link    |
| with -lgsoap for C projects generated with soapcpp2 -c |
|                                                        |
| There are also corresponding libraries for SSL and     |
| zlib compression support (-lgsoapssl and lgsoapssl++)  |
| which require linking -lssl -lcrypto -lz               |
|                                                        |
| Thanks for using gsoap.                                |
|                                                        |
|               http://sourceforge.net/projects/gsoap2   |
+--------------------------------------------------------+

Now go for the kannel install

cd /opt/installs/kannel.svn/trunk/
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
# it will fail due to missing libpq-fe.h
# fix it by creating the proper link 
ln -s /usr/pgsql-9.2/ /usr/pgsql
# now try again
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
# it will fails AGAIN 🙂 with the following error : 
Configuring for gSOAP support ...
checking whether to compile with SOAP support... not found
configure: error: Unable to find gSOAP import at /usr/share/gsoap/import or plugin at /usr/share/gsoap/plugin
root@aegir.voicecom.bg:[Thu Nov 29 23:11:29]:[/opt/installs/kannel.svn/trunk]$ /usr/local/bin/soapcpp2 -d `dirname soap/service/parlayx/soapH.h` -I /usr/share/gsoap/import -I soap/service/parlayx -cLxw soap/service/parlayx/parlayx_sms_send_service_2_1_wrapper.h

**  The gSOAP code generator for C and C++, soapcpp2 release 2.8.11
**  Copyright (C) 2000-2012, Robert van Engelen, Genivia Inc.
**  All Rights Reserved. This product is provided "as is", without any warranty.
**  The soapcpp2 tool is released under one of the following two licenses:
**  GPL or the commercial license by Genivia Inc.

Critical error: #import: Cannot open file "wsse.h" for reading.
Hint: use option -I<path> (for example -Igsoap/import:gsoap/custom:.)

# Fix that with creating the proper link :
ln -s /usr/local/share/gsoap /usr/share/

# now try again
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
......
License information ...
+--------------------------------------------------------------------+
| License:                                                           |
| This software is subject to the Kannel Software License, available |
| in this distribution in the file LICENSE. By continuing this       |
| installation process, you are bound by the terms of this license   |
| agreement. If you do not agree with the terms of this license, you |
| must abort the installation process at this point.                 |
|                                                                    |
|                      The Kannel Group <http://www.kannel.org/>     |
+--------------------------------------------------------------------+

Thank you for using Kannel.

# now compile and install 
make -j3 install 
.....
Using project directory path: soap/service/parlayx/
Saving soap/service/parlayx/soapStub.h annotated copy of the input declarations
Using px1 service name: SendSmsBinding
Using px1 service style: document
Using px1 service encoding: literal
Using px1 service location: http://localhost:9080/ParlayXSms/services/SendSms
Using px1 schema namespace: http://www.csapi.org/wsdl/parlayx/sms/send/v2_1/service
Saving soap/service/parlayx/SendSmsBinding.nsmap namespace mapping table
Using px2 service name: SmsNotificationBinding
Using px2 service style: document
Using px2 service encoding: literal
Using px2 service location: http://localhost:9080/SmsNotificationService/services/SmsNotification
Using px2 schema namespace: http://www.csapi.org/wsdl/parlayx/sms/notification/v2_1/service
Saving soap/service/parlayx/SmsNotificationBinding.nsmap namespace mapping table
Using px3 service name: ReceiveSmsBinding
Using px3 service style: document
Using px3 service encoding: literal
Using px3 service location: http://localhost:9080/ReceiveSmsService/services/ReceiveSms
Using px3 schema namespace: 
Saving soap/service/parlayx/ReceiveSmsBinding.nsmap namespace mapping table
Saving soap/service/parlayx/soapClient.c client calling stubs
Saving soap/service/parlayx/soapServer.c server request dispatcher
Saving soap/service/parlayx/soapH.h interface declarations
Saving soap/service/parlayx/soapC.c XML serializers

Compilation successful
.....

Complile and install opensmppbox :

cd /opt/installs/kannel.svn/trunk/addons/opensmppbox
./bootstrap
./configure
make -j3 install 

Complile and install sqlbox :

cd /opt/installs/kannel.svn/trunk/addons/sqlbox
./bootstrap
./configure
make -j3 install 

Check the install :

cd /usr/local/sbin
root@darkstart:[Thu Nov 29 23:24:31]:[/usr/local/sbin]$ ls
bearerbox  opensmppbox  run_kannel_box  smsbox  sqlbox  start-stop-daemon  wapbox

aaand that is it ,,, Slackware4Life .. 😀

Install Redis stable (2.4.13) on CentOS release 6.2 x86_64 and Slackware 12.0.0 i686

May 15, 2012 Leave a comment

just follow the instructions here : http://redis.io/topics/quickstart

there is small issue with the Slackware 12.0.0 install – the default tclsh version installed is tclsh8.4
so after the make is done, on make test you will see this error at Slackware shell:

Hint: To run 'make test' is a good idea 😉

make[1]: Leaving directory `/opt/installs/redis-2.4.13/src'
root@darkwater:[Tue May 15 18:01:17]:[/opt/installs/redis-2.4.13]$ make test
cd src && make test
make[1]: Entering directory `/opt/installs/redis-2.4.13/src'
which: no tclsh8.5 in (/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/usr/games:/usr/lib/java/bin:/usr/lib/java/jre/bin:/usr/lib/java/bin:/usr/lib/java/jre/bin:/usr/lib/qt/bin:/usr/share/texmf/bin:/opt/pgsql/bin:/opt/mysql/bin:/usr/local/ssl/bin:/opt/kannel/sbin:/opt/apache2/bin)
You need 'tclsh8.5' in order to run the Redis test
make[1]: *** [test] Error 1
make[1]: Leaving directory `/opt/installs/redis-2.4.13/src'
make: *** [test] Error 2
root@darkwater:[Tue May 15 18:01:21]:[/opt/installs/redis-2.4.13]$ locate tclsh
locate: warning: database /var/lib/slocate/slocate.db' is more than 8 days old
/usr/bin/tclsh
/usr/bin/tclsh8.4

how to fix that – install the latest tcl and deinstall the old one :


wget http://prdownloads.sourceforge.net/tcl/tcl8.5.11-src.tar.gz
tar xvfz tcl8.5.11-src.tar.gz
cd tcl8.5.11/
cd unix/
./configure
make -j3
removepkg tcl-8.4.15-i486-1
make install
ldconfig

then proceed with the usual Redis install
the debian init script works for Slackware, just configure it

cp redis_init_script /etc/rc.d/rc.redis
chmod +x /etc/rc.d/rc.redis

and add the stop / start execution of the script at the rc.0 and rc.3 script in a view to stop properly and to start on boot

Regarding the CentOS install ,
there is epel package ready for install version 2.4.10:


root@aegir.ee:[Tue May 15 21:48:38]:[~]$ yum info redis
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.skknet.net
 * epel: mirror.telepoint.bg
 * extras: centos.skknet.net
 * updates: centos.skknet.net
Available Packages
Name        : redis
Arch        : x86_64
Version     : 2.4.10
Release     : 1.el6
Size        : 213 k
Repo        : epel
Summary     : A persistent key-value database
URL         : http://redis.io
License     : BSD
Description : Redis is an advanced key-value store. It is similar to memcached but the data
            : set is not volatile, and values can be strings, exactly like in memcached, but
            : also lists, sets, and ordered sets. All this data types can be manipulated with
            : atomic operations to push/pop elements, add/remove elements, perform server side
            : union, intersection, difference between sets, and so forth. Redis supports
            : different kind of sorting abilities.

however I do prefer the latest stable version compiled from source, so I installed Redis by http://redis.io/topics/quickstart

the provided init script will start the redis server but will not be recognized by chkconfig,
so the fast way to set the start stop init script for Redis is to follow the instructions here:

http://www.saltwebsites.com/2012/install-redis-245-service-centos-6

and that is all, you may start playing with this amazing Redis