Tag Archives: mysql

Quick Ubuntu 16 Setup with java8, mysql 5.7, tomcat7

sudo apt-get update

# get latest java, which is java8 at time of writing
sudo apt-get install default-jdk

# get latest mysql, which is mysql5.7 at time of writing
sudo apt-get install mysql-server

# get tomcat7
sudo apt-get install tomcat7
Tagged , , ,

How to hash mysql varchar/string into different bins

First off, what am I talking about?
Here’s a scenario.

You have a bunch of rows in your table that are URLs. You want to select an evenly distributed random set of them every so often and check if they’re still alive. How do you do this?

You could just take a range.

Get the first 10

SELECT url FROM myTable
LIMIT 0, 10

Get the next 10

SELECT url FROM myTable
LIMIT 10, 10

And the next 10…

SELECT url FROM myTable
LIMIT 20, 10

But what if you wanted to evenly distribute your checks because the URLs from the same site are sequentially ordered.

Well, you could ensure an auto-increment ID and just get chunks of them based on a mod.

So to get every 10th one

SELECT id, url FROM myTable
WHERE id % 10 = 0 

And the next 10th sequence

SELECT id, url FROM myTable
WHERE id % 10 = 1 

And the next 10th sequence

SELECT id, url FROM myTable
WHERE id % 10 = 2 

This is still not that well distributed since you might run into large clusters of site url’s (ie more than 10).
But this also requires a nice integer column value.

Instead, you could also just use the VARCHAR value like the URL itself

So to get the first 10

SELECT id, ... FROM myTable
WHERE CAST(CONV(SUBSTRING(MD5(url), 1, 16), 16, 10) AS SIGNED INTEGER) % 10 = 0

To get the next 10

SELECT id, ... FROM myTable
WHERE CAST(CONV(SUBSTRING(MD5(url), 1, 16), 16, 10) AS SIGNED INTEGER) % 10 = 1

And so on…

Let’s go over what this does.

The MD5() is a hash function that will convert your VARCHAR instead a seemingly random sequence of numbers of letters. It’s not random though. It always converts to the same sequence, but distributes the VARCHAR sequence of characters more uniformly.

The SUBSTRING(…, 1, 16) takes the first 16 digits of the MD5 hash value. I believe this gives you the first 64 bits of it, otherwise there’s a possible overflow error.

The CONV(…, 16, 10) function converts the hash (which is a hex or base-16 value) into a base-10 value.

The CAST(… AS SIGNED INTEGER) function converts it to a signed integer. (If you’re going to read this value into java, you want a signed integer otherwise you’ll get an overflow)

Then simply mod (%) it by the number of bins you want. In my example, I modded it with 10.


Formatting DATE and DATETIME in Mysql

Did you ever inherit a table with a VARCHAR for one of the date fields?
Doesn’t seem that bad, except that gives license for people to start putting different date formats into it.

2001-May-05 11:30
11-19-2009 23:33
Nov 4, 1998 8:03
3/18/08 3:50
8-15-1999 13:00

You should put these into a DATE or DATETIME column. And here’s how you would parse them

SELECT id, strDate,
  CASE WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S")
       WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S")
       WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S")
  END AS newDate
FROM date_table

Add as many formats as you like and make sure you test!

Also, if you wanted to update the date_table with this new DATETIME value, you can do this

UPDATE date_table
SET newDate = CASE
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S")
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S")
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S")  

One thing to note. If you’re going to CREATE, UPDATE or INSERT into a table with these values, there’s a chance you may run into the following error

 “Incorrect datetime value: ‘XXXX’ for function str_to_date”

It may be that your MySql server is running in strict mode.

To check, run

select @@session.sql_mode


To set it to a less strict mode, run

set session sql_mode =''

Now your UPDATE, INSERT or CREATE should work.

Once it completes, you may want to set the sql_mode back to the previous value.


Streaming/Scrollable ResultSet: for when you want results quickly and without taking up too much memory

I’m sure you’re already familiar with how to write a SELECT statement and execute it in java.

Connection conn = ...
Statement stmt = conn.createStatement();
Resultset rs = stmt.executeQuery("...");

But once in awhile, you might run into a very, very, VERY large table. By default, your mysql driver will read and buffer all the rows and then return it to you.

There are two problems you will encounter

  1. The reading takes a long time, and your stmt.executeQuery() method will hang for awhile before getting results. Other things could time-out during that time.
  2. The buffering of all the rows in-memory will consume a lot of heap and you may not have enough

So what if you want to get results quickly. ie. as soon as the first row is fetched, return it so you can process it.
Also, suppose you don’t want to hold all billion rows in memory. You just want to read one row, process it, and trash it.

Then you should create what’s called a streaming or scrollable result set like so

    String selectSQL =
        "SELECT * FROM verylargetable";
    ResultSet selectRs = null;
    try (
        Connection conn = BoneCPSQLUtil.getNewConnection();
        Statement  stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

       ) {
      selectRs = stmt.executeQuery(selectSQL);

      while(selectRs.next()) {
    } catch(Exception e) {
    } finally {
      try {
      } catch(Exception e2) { }


You’ll find that results return right away. (Note: the overall throughput is lower because it’s faster to read everything first and give it to you, but the response time is faster here because you get each row right away).

You’ll also find that you don’t need much memory because as soon as you’re done with the row, it throws it away, and then you can fetch the next row.

Tagged ,

Writing weird unicode characters into mysql

Recently, I had to import a database of foreign names into MySQL. The names were written in farsi, like this شرکت ملی صنایع پتروشیمی (don’t ask me what that means, I have no idea)

Once imported into Mysql, it looked something like this “???? ?????????? ?? ???”. That’s bad. That’s MySQL saying it can’t recognize the characters.

So first, I made sure my table column could handle this character set. I changed the table column’s encoding to “UTF-8”. But this wasn’t enough.

Next I had to change the jdbc connection string from




And that should do it

Tagged , ,

Your Mysql on AWS may be running 20x slower!


No, this is not a Spam Ad trying to get you to click on something to speed up your machine. You should take this seriously and look into it if you have a mysql instance on AWS.

Recently, I created a regular ubuntu12 EC2 m1.large instance on amazon. Then I used the standard apt-get tool to install mysql. This installed mysql 5.5 with all the defaults.

I ran a small import of about 5k records into an innodb table. This took about 10s on my macbook pro (with a 7200rpm HD, no solid state HD). I ran the same test on my EC2 instance and noticed it took 100s!!!

I tried adding and modifying a bunch of parameters in the my.cnf (mysql’s config file, probably located in /etc/mysql). Nothing really made much of a difference except this:


The default value is 1, which guarantees ACID compliance. But if you can live with a possible 1 second lost of data in case of a catastrophic event, then setting it to 0 or 2 will speed it up quite a bit. I set mine to 2.
You can read more about it here

Now, when I import the 5k records, it takes about 5s. That’s a 20X increase in speed.

So to review, this will affect you if the following apply:

  1. You launched an Ubuntu EC2 instance from AWS
  2. You installed mysql using apt-get
  3. You are using InnoDB tables
  4. You want greater WRITE performance (the change above does not affect READs)


Tagged ,

Mysql Replication

If you’re worried about your database server going down unexpectedly, (and who isn’t?,) then you should read this.

Mysql is built with support for high-availability and redundancy. With mysql, you can replicate your servers. In the simplest form, it means you have one (master) mysql server and one (slave) mysql server. Any updates to the master server also happens on the slaves. In the event that one server goes down, you still have the second with the most up-to-date state.

In this post, we go through the following:
– Installing MySQL 5.6 (for Ubuntu)
– Setting up replication with Mysql Utilities
– Running through a typical replication scenario.

First a disclaimer. Mysql provides asynchronous replication, which means the slave servers poll the master for updates. This does not guarantee absolute reliability. The master can fail before all updates get to the slaves and you can be out of sync. There’s a small sync lag, but it just depends. MySQL Cluster is supposed to provide synchronous replication, so try that if you need 100% uptime and reliability.

Also, why Mysql 5.6? Previous versions of Mysql provides replication, but that’s all. You still need to handle failover, which means you need to know if the master has failed, and if it has, then to switch over the master’s duty to a slave. Without the new utilities in 5.6, you’d have to write your own monitoring of the master(s), and a set of scripts to change slave to master, and other slaves to sync to the new master.

The following instructions were adapted from a set of instructions here and here.

Installing Mysql 5.6
This assumes that you have removed previous versions of Mysql and that you are starting from a clean slate.

You’ll need to perform many of the steps below as root so just go ahead and make yourself root

sudo -i

Install dependency

apt-get install libaio1

Download the package

wget -O mysql-5.6.deb http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14-debian6.0-x86_64.deb

Install package

dpkg -i  mysql-5.6.deb

Setup Startup Script

cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server
update-rc.d mysql.server defaults

Update Path

vim /etc/environment
source /etc/environment

Add mysql User

adduser mysql

The password doesn’t matter. Just enter the defaults for the other info.

Add Data Folder and Change Ownership to mysql User

mkdir /opt/mysql/server-5.6/data
chown mysql /opt/mysql/server-5.6/data
chgrp mysql /opt/mysql/server-5.6/data

Add Log Folder

mkdir /opt/mysql/server-5.6/log

Setting Up Replication

Update Mysql Config File for Replication
The file should be located at /etc/mysql/my.cnf. (don’t ask me why).
This should be the file contents:

basedir = /opt/mysql/server-5.6
lc-messages-dir = /opt/mysql/server-5.6/share
log_error = /opt/mysql/server-5.6/log/error.log


Note that you should create a domain name or static IP for this server and insert it into the config file. If your server domain or IP changes, then the setup will break.

Start Mysql

service mysql.server start

Note, your server will automatically start up when you reboot the machine. We’re starting it manually because we just installed it.
Similarly, you can manually stop the server by typing

service mysql.server stop

Create remote mysql users that other mysql instances can log into for replication. In my case, I just added a password to root and allowed remote access.

mysqladmin -u root password NEWPASSWORD
mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'NEWPASSWORD' with grant option; flush privileges;"

Ensure that your firewalls are set up so that every mysql machine can access every other mysql machine. From each machine, you can test access to the other machine with a command such as

mysql -h OTHER_MYSQL_SERVER -u root -p

Download the Mysql Utilities here on one machine. I downloaded this onto my laptop.

Start the replication
From the terminal where Mysql Utilities is installed, issue the following command

mysqlreplicate --master=root:NEWPASSWORD@MASTER_MYSQL --slave=root:NEWPASSWORD@SLAVE_MYSQL_1

Do this for each mysql slave you want to replicate against the master mysql server with.

Set up Automatic Failover
From the terminal where Mysql Utilities is installed, run

mysqlfailover --master=root:NEWPASSWORD@MASTER_MYSQL --discover-slaves-login=root:NEWPASSWORD --rediscover --force

A console will appear and run continuously to monitor the health of the master and slave servers. In the event the master server fails, it will prepare one of the slaves to become the new master, and sync the remaining slaves to the new master.
*Note: I believe there is a bug in the utils. When this is run, an entry is added to the master server’s mysql.failover_console table. I believe it’s supposed to record the IP of the machine where it’s being monitored. But it seems to record its own IP. Furthermore, when the console stops, the entry is never removed. This puts the machine in a weird state and future attempts to connect to it with a monitoring console causes an error. So the –force flag causes the utility to skip the check of the failover_console table and allow it to monitor it for failure.

If you want to run this monitoring process forever (as you probably do), then I found that the best (and only) way to do that is using the screen command

screen -A -m -d -S RUN_AS_USER mysqlfailover --master=root:NEWPASSWORD@MASTER_MYSQL --discover-slaves-login=root:NEWPASSWORD --rediscover --force &

This way, even when you exit the terminal and go to sleep at night, your mysql servers are still being monitored for failure and failover will take place.

You can reconnect to that screen to check the monitor’s status by using the screen commands.
Use this to list the screens you have open

screen -ls

once you have the screen id, use this to re-attach the screen

screen -d [SCREEN_ID]

Running through a typical Replication Scenario
Now that you have replication working, let’s test it out along with fail-over.

Here’s the test script:

  1. Create a table and insert a column on the master mysql server
  2. Ensure that the slaves get the update (by checking if the table exists and has the inserted row)
  3. Simulate a master mysql server failure by shutting it down
  4. Ensure that automatic failover kicks in and that one of the other slaves becomes the new master.
  5. Make sql updates to the new master and check that other slaves are getting its updates
  6. Bring the failed server back online as a slave.
    Use the same command we used before to set up slave.

    mysqlreplicate --master=root:NEWPASSWORD@SLAVE_MYSQL--slave=root:NEWPASSWORD@MASTER_MYSQL

    Notice that this time, we’re setting the MASTER_SQL machine to be the slave of SLAVE_MYQL machine. This is so that the machine catches up to any writes missed when it was down. And it’s temporary until we switch it back to master.

  7. Ensure that the returning server gets all the updates it missed from step 5
  8. Switch the returning server over as the master again.
    mysqlrpladmin --master=root:NEWPASSWORD@SLAVE_MYSQL--new-master=root:NEWPASSWORD@MASTER_MYSQL--demote-master --discover-slaves-login=root --force switchover

    *I found that the –force flag was necessary to overcome a login credentials issue. The article above didn’t use it and perhaps you won’t need to either, but if you run into any login errors, use it.

    At this point, your mysqlfailover monitoring tool will be in an invalid state. You can quit it and start a new one.

  9. Ensure that the switchover happened correctly. To do this just rerun mysqlfailover after closing the last instance.
    Here’s the command again

    mysqlfailover --master=root:NEWPASSWORD@MASTER_MYSQL --discover-slaves-login=root:NEWPASSWORD --rediscover --force
Tagged , , , ,

Authentication in Apache2.4 using mod_auth_form and mod_dbd (part 1: installation)

Update: There’s a much easier way to install and setup Apache 2.4 with authentication using Vagrant and Puppet. Instructions and working example here.

In this lesson, we’re going to set up authentication for all the resources served by Apache2, including php pages, images, and even REST calls that pass through the apache proxy.

We’re going to use a module called mod_auth_form, which requires Apache2.4.

First things first. Let’s install Apache2.4.
You’ll need 2 pre-requisites though. Download these:
1) apr/apr-util
2) pcre
And of course, download Apache2.4 as well.

Step 1)
Copy the apr and apr-util source downloads into the apache’s srclib folder.
Make sure to remove the version numbers, so apr-1.4.6 becomes apr and apr-util-1.5.1 becomes apr-util

Step 2)
Install pcre. I placed the installation at /usr/local/pcre

./configure --prefix=/usr/local/pcre
make install

Step 3)
Install Apache

./configure --prefix=/usr/share/apache2.4 --with-included-apr --with-pcre=/usr/local/pcre --enable-so
make install

The “–with-included-apr” directive tells it to look for the apr libraries in the srclib folder (in step 1).
The “–with-pcre” directive tells it where to find the pcre library you just installed (in step 2).
The “–enable-so” directive enables shared modules which will make it easier to set up php later. (You won’t find this suggestion until you try to install php so you’re learning from my mistake.)
The “–enable-session-crypto” directive will allow the username and password to be encrypted when they’re being transferred back and forth between the browser and server.

Step 4)
Configure Apache
a) Add or uncomment the following lines in httpd.conf

LoadModule auth_form_module modules/mod_authn_core.so
LoadModule auth_form_module modules/mod_auth_form.so
LoadModule session_module modules/mod_session.so
LoadModule request_module modules/mod_request.so
LoadModule session_cookie_module modules/mod_session_cookie.so
LoadModule authn_dbd_module modules/mod_authn_dbd.so
LoadModule dbd_module modules/mod_dbd.so

The first 4 lines are to enable mod_auth_form.
The 5th and 6th lines are to use a database to store the user credentials.

b) Add the following lines in httpd.conf

DBDriver mysql
DBDParams "dbname=myDB,user=root,pass=XXX"
DBDMin  4
DBDKeep 8
DBDMax  20
DBDExptime 300
<Location /private>
  AuthFormProvider dbd
  AuthType form
  AuthName private

  Session On
  SessionCookieName session path=/
  SessionCryptoPassphrase secret
  ErrorDocument 401 /login.html

  # mod_authz_core configuration
  Require valid-user

  # mod_authn_dbd SQL query to authenticate a user
  AuthDBDUserPWQuery "SELECT password FROM apache_user WHERE username = %s"

What you need to know (and change):
– DBDParams: define your mysql credentials here
– <Location PATH>: The PATH is the url that you want to secure
– ErrorDocument 401: This is the location of the login page where users will be redirected if they’re not logged in yet
– AuthDBDUserPWQuery: This is the mysql query to access the table where the credentials are stored

Step 4x)
I ran into an error when I tried to start up apache. You may not get this as I don’t think this is really what should happen.
Skip this unless you see the following error:

AH00526: Syntax error on line 260 of /usr/share/apache2.4/conf/httpd.conf:
Can't load driver file apr_dbd_mysql.so

When you compiled apr and apr-util, it should have included the needed apr_dbd_mysql.so libraries. This dynamically links to libmysqlclient_r.so libraries. (You can ‘ldd’ the apr_dbd_mysql.so library to see this dynamic link.) However, for whatever reason, my libmysqlclient_r.so could not be located by apache so I had to include its containing folder in LD_LIBRARY_PATH environment variable.
So first, locate your libmysqlclient_r.so file (use unix ‘locate’ command). Mine was in /usr/lib.
Then edit /usr/share/apache2.4/bin/envvars and find where LD_LIBRARY_PATH is set.

if test "x$LD_LIBRARY_PATH" != "x" ; then

Below where LD_LIBRARY_PATH is set, I added the folder of my libmysqlclient_r.so file

Tagged , , , , ,