Installation instructions for lib_mysqludf_preg (by Rich Waters) for Windows 7... from scratch with mingw & msys for xampp (but tailorable to most windows mysql needs).

INSTALL LIB_MYSQLUDF_PREG.md

Installing lib_mysqludf_preg from source on Windows

Tested on: Win 7 Pro SP1 - 64 bit, Intel CPU with 8gb ram.

lib_mysqludf_preg is a perl regex library for mysql, by Rich Waters and jasny (Arnold Daniels).

Step 0 - Prerequisites

1. Knowledge

• Level of difficulty? Medium. You don't need serious gcc/msys/mingw experience to execute this, but you do need to know how to use some linux command line commands effectively & how to make files from source from instructions.
• Basic vim knowledge. How to create files, how to insert, how to save, edit. Nano works as well, if that's your cup of tea.
• Knowlege that PATH SPACES ARE VERY BAD. VERY BAD. VERY VERY VERY BAD. Really, they're bad. Don't use them. Not for installing mysql. Not for installing mingw. Not for anything revolving around this.

2. Mysql

• I used xampp for installing and managing mysql (and apache, and phpmyadmin and tomcat and perl).
• This has thus been only tested with using xampp, but installing with the .msi installer from mysql.com causes issues (because of spaces in the name & mingw's compiler environment hating spaces in file names).
• Make sure it's running. Doesn't have to be fully set up, just running.

3. MinGW (or) Cygwin

• MinGW use this particular installer
• Cygwin requires a boatload of dependencies which will be covered later in the document (but, essentially a GCC environment & C, C++, mingw, msys, perl). This is reason why you can't just use cygwin.

Step 1- Setting up Environment

1. Run the installer

• Set install path C:\mingw\ (AGAIN NO SPACES), and make sure it's on the same drive that mysql is on as well.
• Make sure to use C++ & MSYS, and MinGW Developer Tools (and maybe fortran if you're feeling fun and want to work on some of New York State's legacy apps)
• Takes about 20 minutes to install all the dependancies

2. In Windows: go to -> mingw\msys\1.0

• Make Shortcut of msys.bat (right click, create shortcut)
  • target: C:\mingw\msys\1.0\msys.bat --rxvt
  • start in in: C:\mingw\msys\1.0\bin
  • advanced properties -> run as admin
• pin the shortcut to your start menu, in case. It doesn't particularly like taskbars, if you're thinking that. It just will open up a default .bat command line, which isn't what you want at all.

3. Open MSYS.bat.shortcut (not MSYS.bat) which brings you to a command line prompt. This prompt is with posix paths (/usr/bin/ not C:\windows\program files)

• run make -y

• should see: it doesn't error

• run gcc -v

• should see: it doesn't error

• mingw-get install msys-wget msys-unzip

• and then create makeMounts.sh (in this gist as well) by making a file in your ~ directory (home)

• and then run sh makeMounts.sh

• Optional: source /local64/etc/profile.local has to be run every login to build 64 bit dll's... setting your local profile to default to 64 is your choice.

Step 2 - Compiling PCRE

1. Compile stuff

• grab PCRE-8.x and download that, put it in the root local64 directory via

• cd /local64/lib

• wget http://ftp.cs.stanford.edu/pub/exim/pcre/pcre-8.32.tar.bz2

• tar jxvf pcre-8.32.tar.bz2

• cd pcre-8.32.tar.bz2

• ./configure

• make "LDFLAGS=-avoid-version"

• make install

• and then boom.

• it's created in /local/bin/ (in terminal) or C:\mingw\msys\1.0\local\bin\

• You now have a compiled pcre library called libpcre.dll (which is now included)

Step 3 - Compiling lib_mysqludf_preg

1. Prepping the sauce:

• IF YOUR MYSQL FOLDER NAME HAS A SPACE IN IT, I FEEL BAD FOR YOU SON BECAUSE YOU GOT 99 PROBLEMS and awkward spacing is one (see addendums)

• run mount in your msys terminal

• you should see your mysql path as "your\mysql\path" on /mysqls

• restart it if it's not there, if not, check & rerun line 21 in makeMounts.sh

• source /local64/etc/profile.local

• check your environment path (via echo $PATH). if you don't see mysqls/bin check line 63 in makeMounts.sh, and rerun it

• come back, cheer. drop the libpcre.dll into you mysql/bin folder (MAKE SURE TO DO THIS, either via terminal or winders)

2. Get the mats:

• Time to get from git lib_mysqludf_preg

• wget --no-check-certificat https://github.com/mysqludf/lib_mysqludf_preg/archvie/master.zip

• mv master master.zip

• unzip -a master

• cd lib_mysqludf_preg-master/

3. The next re-sets-up your mingw toolchain to properly work with the directory in question. there should be some warnings at the end about non-POSIX variable names... but they're no big deal

• autoreconf -ivf

4. It's a piece of cake to bake a pretty cake:

• time to: config && make && make install

• NOTE: if configure opens up a copy of mysql (which it might) type 'exit' from mysql command line (NOT CTRL-C, because that kills the compiling)

• ./configure --with-mysql=no --prefix='/mysqls/' --with-mysqlinclude='/mysqls/include/' --with-pcre='/usr/local'

• make

• make install

5. TRICKY PART

• check in /mysqls/lib (or C:\xampp\mysql\lib), if you see lib_mysqludf_preg.dll & a host of other similar files... you have to move them to the plugin location which is noted under my.ini file (on xampp, it's xampp\mysql\bin)
• so. if there's not a folder where the plugins are, make one... for xampp, you need mysql\data\lib\
• in either winders or your terminal session
• copy lib_mysqludf_preg.dll in mysql\lib into mysql\lib\plugin
• copy mysql\lib\plugin into mysql\data\lib\plugin
• make sure libpcre.dll is in your mysql/bin folder (again)

6. Setup Validation

• you have to toy around with the makefile a little bit because make doesn't know what your default mysql credentials are

• vim /local64/lib/lib_mysquludf_preg-master/makefile

• search for function: installdb and uninstalldb (@ around line 960) and put your -u -p there after $(MYSQL)...

• so it looks like (sub root for whatever admin root-able username you have)

• $(MYSQL) -u root -p <./installdb_win.sql

• $(MYSQL) -u root -p <./uninstalldb.sql

• :wq that, and then go make installdb

• make installdb

• it should ask you to put in your password twice (do it)

• clean up the makefile of your mess (be kind, please rewind) by removing your -u root

7. Actually Validate

• open up mysql -u root (or again, anyone with access to the mysql table)

• use mysql

• select * from func where dl = "lib_mysqludf_preg.dll";

• you should see SIX rows.

• select LIB_MYSQLUDF_PREG_INFO();

• should return lib_mysqludf_preg version number (tested with 1.1)

• SELECT PREG_REPLACE('/fox/i', 'dog', 'The brow fox' );

• should return "The brown dog."

FIN

And you've won. Congrats, pat yourself on the back. This was not easy! Make yourself a cup of coffee.

Any issues, please submit comments send me an email at pozzie@nysenate.gov or find me on freenode irc as lacesoutdan in #nysstech.

Addendum

• There might be a way to cleverly mount and fake symlinks on windows in order to get by the issues involving path spacing, but it might require a little something called Junction Magic.

makeMounts.sh

mount 'C:\mingw\mingw32' /mingw32
mount 'C:\mingw\mingw64' /mingw64

mkdir /c/mingw/opt
mkdir /c/mingw/build32 /c/mingw/local32
mkdir /c/mingw/build64 /c/mingw/local64

mount 'C:\mingw\opt\'     /opt
mount 'C:\mingw\local32\' /local32
mount 'C:\mingw\local64\' /local64
mount 'C:\mingw\build32\' /build32
mount 'C:\mingw\build64\' /build64

###
# WARNING
# WARNING
# HERES THE BIG KEY HERE.
# your mysql directory HAS HAS HAS HAS HAS HAS HAS to be without spaces
# so that you can A) mount it, and B) set the path

mount 'C:\xampp\mysql\' /mysqls


mkdir /opt/bin /local{32,64}/{bin,etc,include,lib,share}
mkdir /local{32,64}/lib/pkgconfig

cat > /local32/etc/profile.local << "EOF"
#
# /local32/etc/profile.local
#
PKG_CONFIG_PATH="/local32/lib/pkgconfig"
CPPFLAGS="-I/local32/include"
CFLAGS="-I/local32/include -mms-bitfields -mthreads -mtune=pentium3"
CXXFLAGS="-I/local32/include -mms-bitfields -mthreads -mtune=pentium3"
LDFLAGS="-L/local32/lib -mthreads"
export PKG_CONFIG_PATH CPPFLAGS CFLAGS CXXFLAGS LDFLAGS

PATH=".:/local32/bin:/mingw32/bin:/mingw/bin:/bin:/opt/bin:/mysqls/bin"
export PATH
# package build directory
LOCALBUILDDIR=/build32
# package installation prefix
LOCALDESTDIR=/local32
export LOCALBUILDDIR LOCALDESTDIR

EOF

cat > /local64/etc/profile.local << "EOF"
#
# /local64/etc/profile.local
#

alias dir='ls -la --color=auto'
alias ls='ls --color=auto'

PKG_CONFIG_PATH="/local64/lib/pkgconfig"
CPPFLAGS="-I/local64/include"
CFLAGS="-I/local64/include -mms-bitfields -mthreads"
CXXFLAGS="-I/local64/include -mms-bitfields -mthreads"
LDFLAGS="-L/local64/lib"
export PKG_CONFIG_PATH CPPFLAGS CFLAGS CXXFLAGS LDFLAGS

PATH=".:/local64/bin:/mingw64/bin:/mingw/bin:/bin:/opt/bin:/mysqls/bin"
export PATH

# package build directory
LOCALBUILDDIR=/build64
# package installation prefix
LOCALDESTDIR=/local64
export LOCALBUILDDIR LOCALDESTDIR

EOF

cat >> /etc/profile << "EOF"
if [ -f /local32/etc/profile.local ]; then
        source /local32/etc/profile.local
fi

EOF

source /local64/etc/profile.local

cat > ~/.vimrc << "EOF"
" Configuration file for VIM
set nocompatible
set bs=2                " allow backspacing over everything in insert mode
set ai                  " set autoindenting on
" set backup            " keep a backup file
set nobackup            " do not keep a backup file
set history=256          " keep 256 lines of command line history
set ruler               " show the cursor position all the time
set tabstop=8           " tab at 4 characters
set shiftwidth=8        " 4 characters indentation
set nowrap              " do not wrap long lines
set visualbell          " no bell
set background=light    " msys rxvt has a light background
"set background=dark    " mingw shell uses a black background
syntax on               " syntax highlighting on

EOF

cat >> /local32/etc/profile.local << "EOF"
EDITOR=vim
export EDITOR
EOF

cat >> /local64/etc/profile.local << "EOF"
EDITOR=vim
export EDITOR
EOF

# use source /localxy/etc/profile.local
# to swap between 32 & 64 bit environs

I have a very strong feeling that you would save hundreds of hours of many DBAs just by compiling these libraries for Windows platform by yourself and uploading them here.

I've been using your library on Linux environments for the last one year, I think it's absolutely awesome but I've never been able to compile it on Windows - not 1 year ago, not in March 2014 and not now. I've spent many hours with this, but your tutorial is unfortunately still out of date.

Could you please also upload dll files?

@DusanBrejka
I think the tutorial is wonderful and with its help I finally got it set up in Win XP.
Though I must admit it took me one day and effort to fully understand what I needed to do..since I never used MAKE command before.
So what's your problem? Maybe I can be of a little help here.

And big thanks to tadiou!