Archive

Posts Tagged ‘MariaDB’

MariaDB MySQL Percona list all indexes without using INFORMATION_SCHEMA.STATISTICS

July 8, 2015 1 comment

There is nothing more to be said:

SELECT
gen.TABLE_SCHEMA
, gen.TABLE_NAME
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
) as COLUMN_NUM
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY != ""
) as INDEX_NUM_ALL
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "PRI"
) as INDEX_NUM_PRI
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "UNI"
) as INDEX_NUM_UNI
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "MUL"
) as INDEX_NUM_MUL

from information_schema.tables gen
where true
and gen.TABLE_SCHEMA !='mysql'
and gen.TABLE_SCHEMA!='performance_schema'
and gen.TABLE_SCHEMA!='information_schema'
;
+-----------------+-----------------------+------------+---------------+---------------+---------------+---------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NUM | INDEX_NUM_ALL | INDEX_NUM_PRI | INDEX_NUM_UNI | INDEX_NUM_MUL |
+-----------------+-----------------------+------------+---------------+---------------+---------------+---------------+
Categories: MariaDB, MySQL Tags: , , ,

MySQL, Percona, MariaDB long running processes clean up one liner

April 30, 2015 Leave a comment

There are tools like pt-kill from the percona tool kit that may print/kill the long running transactions at MariaDB, MySQL or at Percona data instances, but a lot of backup scripts are just some simple bash lines.
So checking for long running transactions before the backup to be executed seems to be a step that is missed a lot.

Here is one line that might be just added in every bash script before the backup to be executed
Variant 1. Just log all the processlist entries and calculate which ones were running longer than TIMELIMIT:

$ export TIMELIMIT=70 && echo "$(date) : check for long runnig queries start:" >> /tmp/processlist.list.to.kill && mysql -BN -e 'show processlist;' | tee -a /tmp/processlist.list.to.kill | awk -vlongtime=${TIMELIMIT} '($6>longtime){print "kill "$1";"}' | tee -a /tmp/processlist.list.to.kill

Variant 2: Log all the processlist, calculate the calculate which processes are running longer than TIMELIMIT, and kill them before to execute the backup:

$ export TIMELIMIT=70 && echo "$(date) : check for long runnig queries start:" >> /tmp/processlist.list.to.kill && mysql -BN -e 'show processlist;' | tee -a /tmp/processlist.list.to.kill | awk -vlongtime=${TIMELIMIT} '($6>longtime){print "kill "$1";"}' | tee -a /tmp/processlist.list.to.kill | mysql >> /tmp/processlist.list.to.kill 2>&1

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

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: , , , , ,

Compile MariaDB 5.5.28 MySQL on Slackware i486-slackware-linux-gcc-3.3.4 with Cmake 2.8.10.1

November 27, 2012 Leave a comment

1. install Cmake

wget  http://www.cmake.org/files/v2.8/cmake-2.8.10.1.tar.gz
tar xvfz cmake-2.8.10.1.tar.gz
cd cmake-2.8.10.1/
./configure
gmake -j3


2. install libaio

gmake -wget  http://www.kernel.org/pub/linux/kernel/people/bcrl/aio/libaio-0.3.92.tar.gz
tar xvfz libaio-0.3.92.tar.gz
cd libaio-0.3.92/
make prefix=/usr/
make prefix=/usr/ installj3 install

3. install MariaDB 5.5.28
go to https://downloads.mariadb.org/mariadb/5.5.28/ OR

wget https://downloads.mariadb.org/f/mariadb-5.5.28/kvm-tarbake-jaunty-x86/mariadb-5.5.28.tar.gz/from/http:/mariadb.ulak.net.tr/
tar xvf mariadb-5.5.28.tar.gz
cd mariadb-5.5.28/

the build will fail :

root@bubu:[Tue Nov 27 10:15:45]:[/opt/installs/mariadb-5.5.28]$ ./BUILD/compile-pentium-max
testing pentium3 ... ok
+++ /bin/rm -rf configure
+++ /bin/rm -rf CMakeCache.txt CMakeFiles/
+++ path=./BUILD
+++ . ./BUILD/autorun.sh
+++++ dirname ./BUILD/compile-pentium-max
++++ path=./BUILD
++++ cp ./BUILD/cmake_configure.sh ./BUILD/../configure
++++ chmod +x ./BUILD/../configure
+++ CC='ccache gcc'
+++ CFLAGS='-Wall -Wextra -Wunused -Wwrite-strings -Wno-uninitialized -mcpu=pentium3 -O3 -fno-omit-frame-pointer -g  '
+++ CXX='ccache g++'
+++ CXXFLAGS='-Wall -Wextra -Wunused -Wwrite-strings -Wno-uninitialized -Wno-unused-parameter -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentium3 -O3 -fno-omit-frame-pointer -g  '
+++ CXXLDFLAGS=
+++ ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-plugin-aria --with-aria-tmp-tables --with-readline --with-ssl --with-plugins=max --with-embedded-server --with-libevent --enable-local-infile
configure.pl : calling cmake /opt/installs/mariadb-5.5.28  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DENABLE_ASSEMBLER=1 -DWITH_EXTRA_CHARSETS=complex -DENABLE_THREAD_SAFE_CLIENT=1 -DWITH_BIG_TABLES=1 -DWITH_PLUGIN_ARIA=1 -DWITH_ARIA_TMP_TABLES=1 -DWITH_READLINE=1 -DWITH_SSL=bundled -DWITH_MAX=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_LIBEVENT=1 -DENABLE_LOCAL_INFILE=1
-- The C compiler identification is unknown
-- The CXX compiler identification is unknown
-- Check for working C compiler: /usr/bin/ccache
-- Check for working C compiler: /usr/bin/ccache -- broken
CMake Error at /usr/local/share/cmake-2.8/Modules/CMakeTestCCompiler.cmake:61 (message):
  The C compiler "/usr/bin/ccache" is not able to compile a simple test
  program.

  It fails with the following output:

   Change Dir: /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp

  

  Run Build Command:/usr/bin/gmake "cmTryCompileExec591660954/fast"

  /usr/bin/gmake -f CMakeFiles/cmTryCompileExec591660954.dir/build.make
  CMakeFiles/cmTryCompileExec591660954.dir/build

  gmake[1]: Entering directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  /usr/local/bin/cmake -E cmake_progress_report
  /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp/CMakeFiles 1

  Building C object
  CMakeFiles/cmTryCompileExec591660954.dir/testCCompiler.c.o

  /usr/bin/ccache gcc -Wall -Wextra -Wunused -Wwrite-strings
  -Wno-uninitialized -mcpu=pentium3 -O3 -fno-omit-frame-pointer -g -o
  CMakeFiles/cmTryCompileExec591660954.dir/testCCompiler.c.o -c
  /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp/testCCompiler.c

  cc1: error: unrecognized option `-Wextra'

  gmake[1]: Leaving directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  gmake[1]: *** [CMakeFiles/cmTryCompileExec591660954.dir/testCCompiler.c.o]
  Error 1

  gmake: *** [cmTryCompileExec591660954/fast] Error 2

  

  

  CMake will not be able to correctly generate this project.
Call Stack (most recent call first):
  CMakeLists.txt:70 (PROJECT)


-- Configuring incomplete, errors occurred!
+++ gmake -j 6
-- The C compiler identification is unknown
-- The CXX compiler identification is GNU 3.3.4
-- Check for working C compiler: /usr/bin/ccache
-- Check for working C compiler: /usr/bin/ccache -- broken
CMake Error at /usr/local/share/cmake-2.8/Modules/CMakeTestCCompiler.cmake:61 (message):
  The C compiler "/usr/bin/ccache" is not able to compile a simple test
  program.

  It fails with the following output:

   Change Dir: /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp

  

  Run Build Command:/usr/bin/gmake "cmTryCompileExec3386681460/fast"

  gmake[1]: Entering directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  /usr/bin/gmake -f CMakeFiles/cmTryCompileExec3386681460.dir/build.make
  CMakeFiles/cmTryCompileExec3386681460.dir/build

  gmake[2]: Entering directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  /usr/local/bin/cmake -E cmake_progress_report
  /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp/CMakeFiles 1

  Building C object
  CMakeFiles/cmTryCompileExec3386681460.dir/testCCompiler.c.o

  /usr/bin/ccache gcc -Wall -Wextra -Wunused -Wwrite-strings
  -Wno-uninitialized -mcpu=pentium3 -O3 -fno-omit-frame-pointer -g -o
  CMakeFiles/cmTryCompileExec3386681460.dir/testCCompiler.c.o -c
  /opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp/testCCompiler.c

  cc1: error: unrecognized option `-Wextra'

  gmake[2]: Leaving directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  gmake[2]: *** [CMakeFiles/cmTryCompileExec3386681460.dir/testCCompiler.c.o]
  Error 1

  gmake[1]: Leaving directory
  `/opt/installs/mariadb-5.5.28/CMakeFiles/CMakeTmp'

  gmake[1]: *** [cmTryCompileExec3386681460/fast] Error 2

  

  

  CMake will not be able to correctly generate this project.
Call Stack (most recent call first):
  CMakeLists.txt:70 (PROJECT)


-- Configuring incomplete, errors occurred!
gmake: *** [cmake_check_build_system] Error 1

REASON for the fail :
Since you use -Wall to report all warning, the two flags -Wall -Wextra are redundant.
However gcc treat them as errors and stop compiling.

FIX :

## make backup of of the Build script
cp -p    BUILD/SETUP.sh BUILD/SETUP.sh.ORIGINAL

# fix the Wextra issues 
root@bubu:[Tue Nov 27 10:19:41]:[/opt/installs/mariadb-5.5.28]$ sed -i 's/-Wextra//' BUILD/SETUP.sh

# Now run the build again, it will not fail, make sure your home directory has enough space for the ~/.ccache 
root@bubu:[Tue Nov 27 10:21:11]:[/opt/installs/mariadb-5.5.28]$ ./BUILD/compile-pentium-max 

Enjoy Slackware 🙂

Categories: bash, Linux, MySQL, Slackware Tags: , , ,