Tag Archives: MySQL

Reading MariaDB/MySQL binary log files in Go

While looking into a replication-related issue, I came across an interesting problem where I wanted to get the name of the largest event in a very large binary log file. So, I thought of putting Vaquita’s binary log API to a test. Here’s a simple program to read a binary log file and print the largest event and its size.

In this case, having RawEvents is sufficient as we are not interested in the actual content of the events.
Wiki : https://github.com/vaquita/mysql/wiki/Replication-Binary-Logs

Edit: Updated the above program to account for a recent change done to RawEvent(), which now returns error.

Go projects in MySQL/MariaDB ecosystem

Please let me know if you come across a MySQL/MariaDB Go project not listed here.

A quick take on ClassNotFoundException: com.mysql.jdbc.Driver

Its a common problem that programmers often face and sometimes look for a solution. The problem is clear – JVM is not able to locate the MySQL JDBC driver. We will try to solve this step-by-step using a simple java program (below) to print server’s version information. Also, before we proceed please note that I will be trying it on my Ubuntu laptop where I already have got java (openjdk) and MySQL Connector/J (libmysql-java) installed.

Now lets try to compile & run it.

$ javac Version.java 
$ java Version 
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

As expected, jvm is not able to locate the driver jar. We can easily fix this by setting the CLASSPATH environment variable to point to the MySQL Connector/J jar file (mysql-connector-java-5.1.10.jar) on the system.

$ export CLASSPATH=/usr/share/java/mysql-connector-java-5.1.10.jar:.
$ java Version 
5.7.2-m12-debug-log

One last thing to note here is that we have also appended ‘.‘ (i.e. the current directory) to the CLASSPATH in order to be sure that our main ‘Version’ class can also be found.

Building a MySQL client program on Linux

Building a C program (client) to connect to MySQL server is simple, but at times it can become a little problematic for a newbie to find all the necessary dependencies (includes/libs). The best option that I would recommend is to use the mysql_config script that comes bundled with the MySQL distribution. This shell script when executed with some options, would print all necessary compiler options/flags/switches required to build a MySQL client program. Lets see it in action!

/**
  @file client_version.c
  Display the client version.
*/

#include 
#include                               /* All public MySQL APIs */

int main()
{
    printf("MySQL client version: %s\n", mysql_get_client_info());
    return 0;
}

# mysql_config's output
> .../bin/mysql_config --cflags --libs
-I/path..to../include  -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing
-L/path..to../lib -lmysqlclient -lpthread -lm -lrt -ldl

# and now pass it down to the compiler..
> gcc client_version.c `/home/nirbhay/project/repo/bld/trunk/bld/bin/mysql_config --cflags --libs`
.. done

> ./a.out 
MySQL client version: 5.7.2-m12

# whoa.. that worked!

Sometimes, the generated binary would complain of missing shared object file. It can be solved by setting up LD_LIBRARY_PATH to the lib directory containing libmysqlclient.so.NN file.

> ./a.out 
./a.out: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
nirbhay@Nirbhay-lenovo:~/project/sandbox/mysql$ ldd a.out 
	linux-vdso.so.1 =>  (0x00007fffce1ee000)
	libmysqlclient.so.18 => not found
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fca652e6000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fca656a3000)

LD_LIBRARY_PATH=/path..to../lib ./a.out
MySQL client version: 5.7.2-m12

Building a spell-checker using soundex

One of the little know facts about MySQL is that it also provides SOUNDEX() function. Soundex is a well known phonetic algorithm that indexes different words such that ‘homophones’ get the same encoding. So, lets try to see it in action using a MySQL server.

mysql> SELECT SOUNDEX('hello');
+------------------+
| SOUNDEX('hello') |
+------------------+
| H400             |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT SOUNDEX('hellow');
+-------------------+
| SOUNDEX('hellow') |
+-------------------+
| H400              |
+-------------------+
1 row in set (0.00 sec)

Great! As we can see soundex’s encoding has a proper format : Lnnn, i.e the first letter of the supplied word followed by a 3-digit number. This output should mostly be same for homophones (words with similar pronunciation). Lets us now harness this fact to build a ‘not-so-perfect spell checker’.

First, create a table that stores all possible valid words and their soundex code, for brevity I will just use ‘hello’.

mysql> CREATE TABLE `spell_checker` (`word` VARCHAR(50), `code` VARCHAR(4));
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO `spell_checker` VALUES ('hello', SOUNDEX('hello'));
Query OK, 1 row affected (0.00 sec)

Now, once we have the soundex store in place, we can use it as the spell-checker backend to provide the correct spelling for an invalid word.

# hello
mysql> SELECT `word` FROM `spell_checker` WHERE SOUNDEX('hello') = `code`;
+-------+
| word  |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

# hellow
mysql> SELECT `word` FROM `spell_checker` WHERE SOUNDEX('hellow') = `code`;
+-------+
| word  |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

# hallow
mysql> SELECT `word` FROM `spell_checker` WHERE SOUNDEX('hallow') = `code`;
+-------+
| word  |
+-------+
| hello |
+-------+
1 row in set (0.01 sec)

# hallo
mysql> SELECT `word` FROM `spell_checker` WHERE SOUNDEX('hallo') = `code`;
+-------+
| word  |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

As we can see the output is the correct word even it we feed an invalid one.
Sounds interesting.. Isn’t it!!