MySQL Benchmarking 3: Installing Sysbench on Solaris 10

So I have been really busy lately dealing with a bunch of stuff including having to reset replication (twice) on some of the slaves that started lagging behind seriously. While I am determined to find and fix this replication lag issue, the majority of it comes from the huge spec differences between the master and the slaves.

Anyway, yesterday I got my plane ticket to go to MySQL Camp which is really exciting. I will be arriving there on Thursday night and leaving Sunday night on an overnight flight.

Last time when I blogged I was writing about MySQL benchmarking, so I will try to pick up where I left off.

I was having some compile issues when trying to compile sysbench on Solaris. When I was running ./configure I was getting the following warnings:

###############################
./configure output
###############################
configure: WARNING: signal.h: present but cannot be compiled
configure: WARNING: signal.h: check for missing prerequisite headers?
configure: WARNING: signal.h: see the Autoconf documentation
configure: WARNING: signal.h: section "Present But Cannot Be Compiled"
configure: WARNING: signal.h: proceeding with the preprocessor's result
configure: WARNING: signal.h: in the future, the compiler will take precedence
configure: WARNING: ## -------------------------------- ##
configure: WARNING: ## Report this to alexeyk at mysql dot com ##
configure: WARNING: ## -------------------------------- ##
configure: WARNING: thread.h: present but cannot be compiled
configure: WARNING: thread.h: check for missing prerequisite headers?
configure: WARNING: thread.h: see the Autoconf documentation
configure: WARNING: thread.h: section "Present But Cannot Be Compiled"
configure: WARNING: thread.h: proceeding with the preprocessor's result
configure: WARNING: thread.h: in the future, the compiler will take precedence
configure: WARNING: ## -------------------------------- ##
configure: WARNING: ## Report this to alexeyk at mysql dot com ##
configure: WARNING: ## -------------------------------- ##

#############################

Eventually the warnings went away when I used cc instead of gcc.

Running make after configure was producing

###########################
output of make
###########################
Making all in doc
make[1]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/doc'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/fmashraqi/install/bench/sysbench-0.4.7/doc'
Making all in scripts
make[1]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/scripts'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/fmashraqi/install/bench/sysbench-0.4.7/scripts'
Making all in sysbench
make[1]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench'
Making all in drivers
make[2]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench/drivers'
Making all in mysql
make[3]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench/drivers/mysql'
if gcc -DHAVE_CONFIG_H -I. -I. -I../../../config -I/usr/local/mysql/include -xO3 -mt -D_FORTEC_ -xarch=v9 -xc99=none -I../.. -D_REENTRANT -D_XOPEN_SOURCE=500 -D_GNU_SOURCE -D_REENTRANT -g -O2 -MT libsbmysql_a-drv_mysql.o -MD -MP -MF ".deps/libsbmysql_a-drv_mysql.Tpo" -c -o libsbmysql_a-drv_mysql.o `test -f 'drv_mysql.c' || echo './'`drv_mysql.c; then mv -f ".deps/libsbmysql_a-drv_mysql.Tpo" ".deps/libsbmysql_a-drv_mysql.Po"; else rm -f ".deps/libsbmysql_a-drv_mysql.Tpo"; exit 1; fi
gcc: language c99=none not recognized
gcc: drv_mysql.c: linker input file unused because linking not done
mv: cannot access .deps/libsbmysql_a-drv_mysql.Tpo
make[3]: *** [libsbmysql_a-drv_mysql.o] Error 2
make[3]: Leaving directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench/drivers/mysql'
make[2 ]: *** [all-recursive] Error 1
make[2]: Leaving directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench/drivers'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench'
make: *** [all-recursive] Error 1


######################################

The problem was the flag

-xc99=none

which I removed using the following one liner

find . -name Makefile -print | while read i; do  sed -e "s/-xc99=none//g" $i > $i.orig; mv $i.orig $i; done

Then I started getting

Making all in .
make[2]: Entering directory `/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench'
/bin/bash ../libtool --tag=CC --mode=link /opt/SUNWspro/bin/cc -D_REENTRANT -g -lpthread -o sysbench -static sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -xarch=v9 -L/usr/local/mysql/lib -lmysqlclient_r -lz -lposix4 -lcrypt -lgen -lsocket -lnsl -lm -lmtmalloc -lrt -lm
/opt/SUNWspro/bin/cc -D_REENTRANT -g -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o -xarch=v9 -lpthread tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/usr/local/mysql/lib -lmysqlclient_r -lz -lposix4 -lcrypt -lgen -lsocket -lnsl -lmtmalloc -lrt -lm
ld: fatal: file sysbench.o: wrong ELF class: ELFCLASS32
ld: fatal: File processing errors. No output written to sysbench

The reason for these errros was the conflict when trying to link 32-bit binaries with 64-bit binaries. To address that issue, I set the following in my environment.

CFLAGS=-xarch=v9

Then I tried to build once again. Here's the summarized process

# Removed the current sysbench source directory
rm -r -f sysbench-0.4.7
# Extracted a fresh copy from the tarball.
tar -xf sysbench-0.4.7.tar
# Changed my pwd to sysbench-0.4.7
cd sysbench-0.4.7
# Set up my environment
CC=/opt/SUNWspro/bin/cc
CFLAGS=-xarch=v9
LD_LIBRARY_PATH=/usr/local/mysql/lib:/usr/ccs/lib:/usr/lib:/usr/local/lib:/lib:/usr/ucblib
PATH=/opt/SUNWspro/bin:/opt/SUNWspro/bin:/usr/sbin:/usr/bin:/usr/local/bin:.:/usr/ccs/bin:/usr/local/mysql/bin:/usr/local/bin:.:/usr/ccs/bin:/usr/local/mysql/bin
# Cleaned out  -xc99=none which was being produced by mysql_config by using a slightly modified version of the one liner recommended in this thread
find . -name Makefile -print | while read i; do  sed -e "s/-xc99=none//g" $i > $i.orig; mv $i.orig $i; done
# run make
make
# finally install
 make install

This time it worked! Yay!

Here's how my environment was setup

TERM=xterm
SHELL=/sbin/sh
SSH_CLIENT=192.168.1.11 34203 22
OLDPWD=/home/fmashraqi/install/bench/sysbench-0.4.7/sysbench
SSH_TTY=/dev/pts/5
USER=root
LD_LIBRARY_PATH=/usr/local/mysql/lib:/usr/ccs/lib:/usr/lib:/usr/local/lib:/lib:/usr/ucblib
PATH=/opt/SUNWspro/bin:/opt/SUNWspro/bin:/usr/sbin:/usr/bin:/usr/local/bin:.:/usr/ccs/bin:/usr/local/mysql/bin:/usr/local/bin:.:/usr/ccs/bin:/usr/local/mysql/bin
MAIL=/var/mail//root
PWD=/home/fmashraqi/install/bench/sysbench-0.4.7
TZ=US/Eastern
PS1=[\u@\h:\w]
SHLVL=1
HOME=/
CFLAGS=-xarch=v9
LOGNAME=root
SSH_CONNECTION=192.168.1.11 34203 192.168.1.192 22
CC=/opt/SUNWspro/bin/cc
_=/usr/bin/env

Now let's run sysbench and do a test run

[root@db32:/home/fmashraqi/install/bench/sysbench-0.4.7] sysbench --test=cpu --cpu-max-prime=20000 run
sysbench v0.4.7:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing CPU performance benchmark

Threads started!
Done.

Maximum prime number checked in CPU test: 20000


Test execution summary:
    total time:                          281.8175s
    total number of events:              10000
    total time taken by event execution: 281.8014
    per-request statistics:
         min:                            0.0281s
         avg:                            0.0282s
         max:                            0.0369s
         approx.  95 percentile:         0.0283s

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   281.8014/0.00

Many thanks to folks at Sun and OpenSolaris.org for their assistance.

References:
Wrong ELF Class
Porting from Solaris to RedHat
Downloading cc (as part of Sun Studio 11)
Sun Studio 11
Compiling Sysbench on Solaris 10

Previously:
MySQL Benchmarking Part 1
MySQL Benchmarking Part 2

Some additional tips:
MySQL compile flags

[$] /usr/local/mysql/bin/mysql_config --cflags
-I/usr/local/mysql/include -xO3 -mt -D_FORTEC_ -xarch=v9 -xc99=none

The -xarch=v9 is a 64-bit flag
Don't confuse CC (C++) with cc.
-O2 -MT -MB are gcc options whereas -xO3 -md -xarch are cc options

MySQL Benchmarking 2

In my last post MySQL Benchmarking 1, I was installing Sysbench on a Solaris 10 machine. However I stopped at

./configure

because I was getting compiler errors due to the SUNWsprot and SUNWsprot Sun OS packages not being installed.

After getting them installed on the server I tried to run the ./configure again and this time although the compile process went a little further, it stopped after throwing the following errors.

checking how to run the C preprocessor... /lib/cpp
configure: error: C preprocessor "/lib/cpp" fails sanity check
See `config.log' for more details.

MySQL Benchmarking 1

From time to time every MySQL DBA needs to benchmark queries. Fortunately there are many benchmarking tools available. In this and future posts I will walk you through using various MySQL benchmarking tools.

• MySQL Super Smack: Sasha Pachev originally developed MySQL Super Smack. After that Jeremy Zawodny maintained it for some time. Currently it is maintained by Tony Bourke. The current downloadable version of Super Smack is 1.3.
  
  References:
  Using MySQL to benchmark operating system performance
  Part 2: Comparing MySQL Performance
  


• Sysbench: Primarily developed for MySQL, "SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load." Download current version of Sysbench (0.4.7).


• mybench: A "simple" MySQL benchmarking tool written in Perl by Jeremy Zawodny. Download version 1.0 of mybench.


• mysqlslap is a load emulation client available with MySQL 5.1.4 and later. Here's Brian "Krow" Aker's post about mysqlslap.

Sysbench
After downloading Sysbench, uncompress and extract files from the archive:

gunzip sysbench-0.4.7.tar.gz
tar -xvf sysbench-0.4.7.tar

Then navigate to the sysbench-0.4.7 directory and run

./configure

to configure the package.

[root@db32:/home/fmashraqi/install/bench/sysbench-0.4.7] ./configure
checking build system type... sparc-sun-solaris2.10
checking host system type... sparc-sun-solaris2.10
checking target system type... sparc-sun-solaris2.10
checking for a BSD-compatible install... config/install-sh -c
checking whether build environment is sane... yes
checking for gawk... no
checking for mawk... no
checking for nawk... nawk
checking whether make sets $(MAKE)... no
checking whether to compile with MySQL support... (cached) yes
checking whether to compile with Oracle support... (cached) no
checking whether to compile with PostgreSQL support... (cached) no
checking for style of include used by make... none
checking for gcc... no
checking for cc... no
checking for cc... no
checking for cl... no
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details.

Notice we got an error about no acceptable C compiler being found. To resolve this situation we need to add the path to gcc (or cc) to $PATH. So we open .bashrc file and export new value of $PATH

PATH=$PATH:/usr/local/bin:.
export PATH

Now to apply changes to our current shell, we "dot" the bashrc file.

 . /.bashrc

Running "gcc -v" now produces

 Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.10/3.3.2/specs
Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls
Thread model: posix
gcc version 3.3.2

Yay, we can now proceed with running ./configure.

To be continued...

Storing Passwords in MySQL

Securing plain text passwords in MySQL is NEVER a good idea. As a DBA you should take great care in protecting the users' information. Fortunately MySQL provides you with several options to protect passwords.

After a quick scan of the manual, you may be tempted to store the password by applying the password function to it which is NOT a good idea. MySQL itself advises against using PASSWORD to manage application passwords.

Instead of using PASSWORD(), we can use SHA1 or MD5. Unfortunately exploits for both of these encryption functions have been quite common these days. Still, SHA1 or MD5 keep your password more protected than storing them as plain text.

You can apply SHA1 algorithm to a password string:

mysql>  SELECT SHA1('mysecretpassword');
+------------------------------------------+
| SHA1('mysecretpassword')                 |
+------------------------------------------+
| 08cd923367890009657eab812753379bdb321eeb |
+------------------------------------------+
1 row in set (0.00 sec)

Since SHA is an alias for SHA1, it produces the same result

mysql>  SELECT SHA('mysecretpassword');
+------------------------------------------+
| SHA('mysecretpassword')                  |
+------------------------------------------+
| 08cd923367890009657eab812753379bdb321eeb |
+------------------------------------------+
1 row in set (0.00 sec)

To store passwords encrypted with SHA1, we need to be able to store 40 characters.

mysql> SELECT CHARACTER_LENGTH(SHA1('mysecretpasswordsssssss'));
+---------------------------------------------------+
| CHARACTER_LENGTH(SHA1('mysecretpassword')) |
+---------------------------------------------------+
|                                                40 |
+---------------------------------------------------+
1 row in set (0.00 sec)

On the other hand, to store passwords encrypted with MD5, we need the column to be able to hold 32 characters.

mysql>  SELECT MD5('secretpassword');
+----------------------------------+
| MD5('secretpassword')            |
+----------------------------------+
| 2034f6e32958647fdff75d265b455ebf |
+----------------------------------+
1 row in set (0.00 sec)

mysql>  SELECT CHARACTER_LENGTH(MD5('secretpassword'));
+-----------------------------------------+
| CHARACTER_LENGTH(MD5('secretpassword')) |
+-----------------------------------------+
|                                      32 |
+-----------------------------------------+
1 row in set (0.00 sec)

Using MD5 in your application is easy. Here's how your queries will need to be to take advantage of encryption offered by MD5.


First, let's create a table:

mysql> create table user_md5 (user_name VARCHAR(16), password VARCHAR(32));
Query OK, 0 rows affected (0.00 sec)

Now let's insert a record with MD5 applied to the password field.

mysql> INSERT INTO user_md5 VALUES ('member1',MD5('secretpassword') );
Query OK, 1 row affected (0.00 sec)

Finally, let's see if it all works when we try to authenticate a user.

mysql> SELECT * FROM user_md5 WHERE user_name='member1' AND password=MD5('secretpassword');
+-----------+----------------------------------+
| user_name | password                         |
+-----------+----------------------------------+
| member1   | 2034f6e32958647fdff75d265b455ebf |
+-----------+----------------------------------+
1 row in set (0.00 sec)

SHA1
Here's how we can store passwords encrypted with SHA1 algorithm, which is "cryptographically more secure" than MD5. Note that we are changing the definition of password field so it can store 40 characters.

mysql> create table user_sha1 (user_name VARCHAR(16), password VARCHAR(40));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO user_sha1 VALUES ('member1',SHA1('secretpassword') );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user_sha1 WHERE user_name='member1' AND password=SHA1('secretpassword');
+-----------+------------------------------------------+
| user_name | password                                 |
+-----------+------------------------------------------+
| member1   | edbd1887e772e13c251f688a5f10c1ffbb67960d |
+-----------+------------------------------------------+
1 row in set (0.00 sec)

Since the exploits for MD5 and SHA1, especially in the form of dictionary attacks, are out there, using them may not provide us the level of encryption and protection we desire. Fortunately, MySQL offers


AES
If you would like to implement AES (Advanced Encryption Standard) encryption, you will need to specify the password field to be of type BLOB. AES functions encode with a 128-bit key length which can be changed by modifying source and then recompiling MySQL.

mysql> create table user_aes (user_name VARCHAR(16), password BLOB);
Query OK, 0 rows affected (0.00 sec)

Now to insert a record, we will call AES function and in addition to providing the secret password to be protected, we will also provide the key_str.

mysql> INSERT INTO user_aes VALUES ('member1',AES_ENCRYPT('secretpassword','my_secret_key_to_encrypt') );
Query OK, 1 row affected (0.02 sec)

Now to authenticate a user, we encrypt the password which the user is using to login with the same key_str as we used before. Then we can match it against the records in the table.

mysql> SELECT * FROM user_aes WHERE user_name='member1' AND password=AES_ENCRYPT('secretpassword','my_secret_key_to_encrypt');
+-----------+------------------+
| user_name | password         |
+-----------+------------------+
| member1   | ñGa·`·
                    +'U |
+-----------+------------------+
1 row in set (0.00 sec)

Since AES provides reversible encryption (provided you have the key_str), we can obtain the password in plain text format.

mysql> SELECT AES_DECRYPT(password, 'my_secret_key_to_encrypt') AS unencrypted FROM user_aes ;
+----------------+
| unencrypted    |
+----------------+
| secretpassword |
+----------------+
1 row in set (0.00 sec)

According to MySQL AES functions (AES_ENCRYPT() and AES_DECRYPT()) were added in MySQL 4.0.2 and are currently the most cryptographically secure encryption functions in MySQL.

Note: A common error is to use AES function to encrypt instead of AES_ENCRYPT. If you do so you'll get the following error:

ERROR 1305 (42000): FUNCTION db_name.AES does not exist

Now we can see the table status for each of the table to see the differences in Avg_row_length etc.

Using SHA1

mysql> show table status like 'user_sha1' \G
*************************** 1. row ***************************
           Name: user_sha1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 56
    Data_length: 104
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 48
 Auto_increment: NULL
    Create_time: 2006-08-21 13:38:10
    Update_time: 2006-08-21 14:06:43
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Using MD5

mysql> show table status like 'user_md5' \G
*************************** 1. row ***************************
           Name: user_md5
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 48
    Data_length: 48
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-08-21 13:58:59
    Update_time: 2006-08-21 14:01:55
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

For table with AES encryption

mysql> show table status like 'user_aes' \G
*************************** 1. row ***************************
           Name: user_aes
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 32
    Data_length: 32
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-08-21 14:08:22
    Update_time: 2006-08-21 14:17:25
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

If MySQL has been configured with SSL support, you can use the DES functions which use the Triple-DES algorithm to encrypt a string with the supplied key.

The syntax for these functions is

DES_DECRYPT(crypt_str[,key_str])
DES_ENCRYPT(str[,{key_num|key_str}])

I will discuss them in more detail in the near future.

So now that you have read this post do you mind sharing what algorithm do you use to store passwords in your application?

--Frank

References:
SHA1: Secure Hash Algorithm version 1.0
SHA functions
MD5: Message Digest Algorithm 5
RFC 1321: The Message Digest Algorithm
AES: Advaned Encryption Standard
Storing Passwords in MySQL
Storing passwords safely in mysql
Keeping passwords secure with MySQL

mysqld restarted: MyISAM tables crashing frequently

When MyISAM tables start crashing somewhat frequently, you can be sure that's not a good sign.

The first step in investigating the issue is to analyze the MySQL error log file (usually host-name.err). There you may find messages indicating that either the slave thread was killed, if the replication was enabled, or that mysqld was restarted just before the corruption happened.

If the reason for corruption was that the slave thread was killed, you can avoid it by ensuring that you issue "STOP SLAVE" first before shutting down MySQL.

On the other hand, if the reaason for MyISAM corruption was the "mysqld restarted" messages that you are seeing in the error log files, then you need to find out whether the current parent process of mysqld is the same as when MySQL was last restarted manually/properly.


If the parent process is new then you may need to investigate whether there is a cron job or a monitoring script that is automatically restarting MySQL leading to corruption. In the event that there is such a script, you should modify it so it stops the slave threads explicitly by issuing "STOP SLAVE".

If the parent process is the same as when MySQL was restarted manually then another explanation is that some query is causing mysqld to fail which is in turn causing "mysqld restarted" messages to appear in the log file.

Here's what you can do to troubleshoot and investigate instances like this:

1. Check all MyISAM tables not just the ones that have been crashing on db9. It may be the case that there is some table that is corrupted but not being reported by MySQL and queries on that table are triggering a crash.

2. Enable full query logging to investigate and find out which exact query is causing MySQL to fail and producing the "mysqld restarted" messages. The drawback is that you will need ample disk space for each and every query executed on the server to be logged.

3. Enable automatic recovery by using "myisam-recover" server startup option (this will not help us troubleshoot the crashes, however it will repair the tables automatically if they were marked as 'not closed properly' or 'crashed'). If we enable automatic recovery, we need to decide whether MySQL should keep a backup of the original corrupted data file (can use a lot of disk space).

The ideal configuration for automatic recovery is to use BACKUP with FORCE options which will cause MySQL to forcefully repair the corrupted table (even if the repair would cause some rows to be lost). MySQL will however keep a backup of the corrupted table if this option is used.

Anything else that I missed that can be done to investigate and troubleshoot frequent MyISAM corruptions?

MySQL Error 1032: A somewhat ambiguous error?

Today I experienced error 1032 on one of my slave servers which has MyISAM tables. The full error message was:

060810 14:46:45 [ERROR] Slave: Error 'Can't find record in 'group_upload_counter'' on query. Default database: 'db1'. Query: 'insert into group_counter (...) values('username', now(), 1, now())', Error_code: 1032

I invoked "perror 1032" to see what the error was all about but got the following message:

-bash-3.00$ perror 1032
Illegal error code: 1032

I asked on the #mysql and Jay and Roland were quick to respond. We all researched for some time but the only thing we were leading to was something about running out of disk space which wasn't the issue in this case. So I decided to check the table for corruption and guess what, the table was indeed corrupt and was fixed with a simple REPAIR TABLE invocation. After that I started the slave and it caught up fully.

Also see: error codes

Renaming a stored procedure

Yesterday night I was working to create stored procedures to simplify my work to manage hierarchical data in MySQL. I had seen Mike Hillyer's excellent presentation at MySQL UC and truly enjoyed it. BTW, he has put up a nice guide on dev.mysql.com in case you are interested (links below).

While working with the stored procedures (will post them soon) I wanted to rename one of the stored procedure I created. So I searched for it and found that we should be able to rename it using the ALTER PROCEDURE command however I couldn't figure out the exact syntax to rename it. I did drop the SP and recreated it but wanted to find out how can I rename an existing SP. Any ideas, tips, hints are appreciated.

• Managing Hierarchical Data presentation (Flash)
  
• Managing Hierarchical Data (Dev.MySQL.com)

Selecting NULL

So I got a question from a developer today who was trying to SELECT NULL values from a table. As I have been asked about this many times in the past, I decided to write a little post about it.

mysql> SHOW CREATE TABLE testing_null \G
*************************** 1. row ***************************
Table: testing_null
Create Table: CREATE TABLE `testing_null` (
`id` int(11) unsigned NOT NULL auto_increment,
`mycolumn` varchar(12) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO testing_null (mycolumn) VALUES('ho'), (NULL), (NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testing_null;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
| 2 | NULL |
| 3 | NULL |
+----+----------+
3 rows in set (0.00 sec)

Now the wrong way to query the table to find the NULL values (Some examples are really ridiculous to test for NULL but presented here any way):

mysql> SELECT * FROM testing_null WHERE mycolumn='NULL';
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn=NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn=\N;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE !mycolumn;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE !mycolumn != NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn != NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn != '' ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testing_null WHERE mycolumn =0 ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

The problem is that while NULL represents a value that is unknown or missing, we cannot use the comparison operators to determine whether a value is NULL. The correct way to test for NULL or to query based on NULL is to use the "IS NULL" and "IS NOT NULL" operators:

mysql> SELECT * FROM testing_null WHERE mycolumn IS NULL ;
+----+----------+
| id | mycolumn |
+----+----------+
| 2 | NULL |
| 3 | NULL |
+----+----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM testing_null WHERE mycolumn IS NOT NULL ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

Also see:

• Working with NULL in MySQL
  

Take care of disk paritions filling up

One of our MySQL db servers was filling up the disk space pretty quickly due to normal reasons. Since we use 3Par for storage purposes, we created a larger partition and then linked the db directory to point to the new partition.

ln -s /path/to/new/mysql/data/partition/db_1 /path/to/mysql/data/db_1

The sequence of steps needed were:

• create a new parition
• initialize and clear filesystem with newfs before using for the first time.
• Create appropriate entries in /etc/vfstab
• Create the directory
• mount (mount -a)
• Copy data [tar cf - db_1 | ( cd /path/to/new/mysql/data/guestbook; tar xvf - )]
• invoke "fuser -k /path/to/old/mysql/data" to view the processes using the filesystem you just unmounted
• unmount the old partition
• backup the data that was on old file system.
• create a soft link that points to the new partition.
• confirm

Some references

• luxadm: Sun Enterprise Network Array (SENA) administration program
• use it with the "-e" option for expert mode
  
• get the "using luxadm" guide
  
• example:

• luxadm -e dump_map /devices/pci@1d,700000/SUNW,qlc@1/fp@0,0:fc

  
  
• Adding filesystem under Solaris
  

Relay Log File Size and Limit

For any DBA, broken replication represents a dreaded moment. Replication can break for many factors including when the master server issues a 'Duplicate entry' error. At that point you can either skip the slave counter (recommended) or ignore/skip the errors (slave-skip-errors=1062) if you are sure that ignoring such errors won't cause a nasty effect somewhere.

Depending on when you troubleshoot the situation, when replication resumes, slaves may fall significantly behind and the relay log files may start consuming huge amount of disk space. If you have ample free disk space then that is not an issue however, if you are tight on disk space then the relay-log-space-limit option can really come handy.

The relay-log-space-limit option limits the disk space used by the relay log files. You can specify the value in bytes, megabytes (10000M) or gigabytes (10G) in either my.cnf file or as a start up option.

To specify it in the my.cnf file, backup your current my.cnf file (always recommended), stop slave, stop the MySQL server and place the following option:

# relay log restrictions
relay-log-space-limit=15G

Then save and quit the file and start MySQL. Unless you configured differently, MySQL will automatically start the slave thread.

Now assuming the relay log files were using more than 15G space, issuing a

SHOW SLAVE STATUS \G

would show the following message:

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space

What that message indicates is that MySQL is waiting for the existing relay logs to be read so some space can be freed before new logs will be downloaded to the slave.

If you do not want to restrict the disk space for relay log files then specify 0 as the value for relay-log-space-limit.

For more information on the states of slave I/O threads, see the MySQL manual.

Compiling Super Smack on Solaris 10

I have been trying to compile super-smack, a benchmarking tool for MySQL, on a Solaris 10 box but compiling super-smack fails with the following message(s):

###################
[root@db:/home/me/benchmarking/super-smack-1.3] ./configure --with-mysql=/usr/local/mysql/bin/mysql
checking for a BSD-compatible install... ./install-sh -c
checking whether build environment is sane... yes
checking whether make sets $(MAKE)... no
checking for working aclocal... missing
checking for working autoconf... missing
checking for working automake... missing
checking for working autoheader... missing
checking for working makeinfo... missing
checking for sh... /bin/bash
checking for gcc... no
checking for cc... cc
checking for C compiler default output file name... configure: error: C compiler cannot create executables
See `config.log' for more details.
################

Now that main question is why is it complaining about C compiler not being able to create executables and what can be done about it?

Following are excerpts from the config.log


# #########################
This file contains any messages produced by compilers while
running configure, to aid debugging if configure makes a mistake.

It was created by configure, which was
generated by GNU Autoconf 2.59. Invocation command line was

$ ./configure --with-mysql=/usr/local/mysql/bin/mysql

## --------- ##
## Platform. ##
## --------- ##

hostname = db.host
uname -m = sun4u
uname -r = 5.10
uname -s = SunOS
uname -v = Generic_118833-03

/usr/bin/uname -p = sparc
/bin/uname -X = System = SunOS
Node = db.host
Release = 5.10
KernelID = Generic_118833-03
Machine = sun4u
BusType =
Serial =
Users =
OEM# = 0
Origin# = 1
NumCPU = 2

/bin/arch = sun4
/usr/bin/arch -k = sun4u
/usr/convex/getsysinfo = unknown
hostinfo = unknown
/bin/machine = unknown
/usr/bin/oslevel = unknown
/bin/universe = unknown

PATH: /usr/sbin
PATH: /usr/bin
PATH: /usr/local/bin
PATH: /opt/csw/bin
PATH: /sbin
PATH: /bin
PATH: /usr/sbin
PATH: /usr/bin
PATH: /usr/local/sbin
PATH: /usr/bin
PATH: /usr/local/mysql/bin
PATH: /usr/ucb
PATH: /etc
PATH: .
PATH: /usr/local/bin
PATH: /opt/csw/bin
PATH: /sbin
PATH: /bin
PATH: /usr/sbin
PATH: /usr/bin
PATH: /usr/local/sbin
PATH: /usr/bin
PATH: /usr/local/mysql/bin
PATH: /usr/ucb
PATH: /etc
PATH: .


## ----------- ##
## Core tests. ##
## ----------- ##

configure:1359: checking for a BSD-compatible install
configure:1414: result: ./install-sh -c
configure:1425: checking whether build environment is sane
configure:1468: result: yes
configure:1483: checking whether make sets $(MAKE)
configure:1507: result: no
configure:1535: checking for working aclocal
configure:1546: result: missing
configure:1550: checking for working autoconf
configure:1561: result: missing
configure:1565: checking for working automake
configure:1576: result: missing
configure:1580: checking for working autoheader
configure:1591: result: missing
configure:1595: checking for working makeinfo
configure:1606: result: missing
configure:1619: checking for sh
configure:1645: result: /bin/bash
configure:1698: checking for gcc
configure:1727: result: no
configure:1778: checking for cc
configure:1794: found /usr/ucb/cc
configure:1804: result: cc
configure:1968: checking for C compiler version
configure:1971: cc --version &5
/usr/ucb/cc: language optional software package not installed
configure:1974: $? = 1
configure:1976: cc -v &5
/usr/ucb/cc: language optional software package not installed
configure:1979: $? = 1
configure:1981: cc -V &5
/usr/ucb/cc: language optional software package not installed
configure:1984: $? = 1
configure:2007: checking for C compiler default output file name
configure:2010: cc conftest.c >&5
/usr/ucb/cc: language optional software package not installed
configure:2013: $? = 1
configure: failed program was:
| /* confdefs.h. */
|
| #define PACKAGE_NAME ""
| #define PACKAGE_TARNAME ""
| #define PACKAGE_VERSION ""
| #define PACKAGE_STRING ""
| #define PACKAGE_BUGREPORT ""
| #define PACKAGE "super-smack"
| #define VERSION "1.1"
| #define VERSION "1.1"
| /* end confdefs.h. */
|
| int
| main ()
| {
|
| ;
| return 0;
| }
configure:2052: error: C compiler cannot create executables
See `config.log' for more details.

## ---------------- ##
## Cache variables. ##
## ---------------- ##

ac_cv_env_CC_set=
ac_cv_env_CC_value=
ac_cv_env_CFLAGS_set=
ac_cv_env_CFLAGS_value=
ac_cv_env_CPPFLAGS_set=
ac_cv_env_CPPFLAGS_value=
ac_cv_env_CPP_set=
ac_cv_env_CPP_value=
ac_cv_env_CXXFLAGS_set=
ac_cv_env_CXXFLAGS_value=
ac_cv_env_CXX_set=
ac_cv_env_CXX_value=
ac_cv_env_LDFLAGS_set=
ac_cv_env_LDFLAGS_value=
ac_cv_env_build_alias_set=
ac_cv_env_build_alias_value=
ac_cv_env_host_alias_set=
ac_cv_env_host_alias_value=
ac_cv_env_target_alias_set=
ac_cv_env_target_alias_value=
ac_cv_prog_SHELL=/bin/bash
ac_cv_prog_ac_ct_CC=cc
ac_cv_prog_make_make_set=no

## ----------------- ##
## Output variables. ##
## ----------------- ##

ACLOCAL='/home/me/benchmarking/super-smack-1.3/missing aclocal'
AUTOCONF='/home/me/benchmarking/super-smack-1.3/missing autoconf'
AUTOHEADER='/home/me/benchmarking/super-smack-1.3/missing autoheader'
AUTOMAKE='/home/me/benchmarking/super-smack-1.3/missing automake'
CC='cc'
CFLAGS=''
CPP=''
CPPFLAGS=''
CXX=''
CXXFLAGS=''
DATADIR=''
DEFS=''
ECHO_C=''
ECHO_N='-n'
ECHO_T=''
EGREP=''
EXEEXT=''
INSTALL_DATA='${INSTALL} -m 644'
INSTALL_PROGRAM='${INSTALL}'
INSTALL_SCRIPT='${INSTALL}'
LDFLAGS=''
LEX=''
LEXLIB=''
LEX_OUTPUT_ROOT=''
LIBOBJS=''
LIBS=''
LN_S=''
LTLIBOBJS=''
MAKEINFO='/home/me/benchmarking/super-smack-1.3/missing makeinfo'
MYSQL_INCLUDE=''
MYSQL_LIB=''
OBJEXT=''
ORACLE_INCLUDE=''
ORACLE_LIB=''
PACKAGE='super-smack'
PACKAGE_BUGREPORT=''
PACKAGE_NAME=''
PACKAGE_STRING=''
PACKAGE_TARNAME=''
PACKAGE_VERSION=''
PATH_SEPARATOR=':'
PGSQL_INCLUDE=''
PGSQL_LIB=''
SET_MAKE='MAKE=make'
SHELL='/bin/bash'
SMACKS_DIR=''
VERSION='1.1'
YACC=''
ac_ct_CC='cc'
ac_ct_CXX=''
bindir='${exec_prefix}/bin'
build_alias=''
datadir='${prefix}/share'
exec_prefix='NONE'
host_alias=''
includedir='${prefix}/include'
infodir='${prefix}/info'
libdir='${exec_prefix}/lib'
libexecdir='${exec_prefix}/libexec'
localstatedir='${prefix}/var'
mandir='${prefix}/man'
oldincludedir='/usr/include'

prefix='NONE'
program_transform_name='s,x,x,'
sbindir='${exec_prefix}/sbin'
sharedstatedir='${prefix}/com'
sysconfdir='${prefix}/etc'
target_alias=''

## ----------- ##
## confdefs.h. ##
## ----------- ##

#define PACKAGE "super-smack"
#define PACKAGE_BUGREPORT ""
#define PACKAGE_NAME ""
#define PACKAGE_STRING ""
#define PACKAGE_TARNAME ""
#define PACKAGE_VERSION ""
#define VERSION "1.1"
#define VERSION "1.1"

configure: exit 77


#################################
Now I need to investigate as to why this is happening.
Frank

MyISAM to InnoDB: Lost Rows 2

Thank you for the replies to my previous post.

However, let me rephrase the question:

I have a table of type MyISAM that is reporting 47 million rows when I do a SELECT COUNT(*). When I convert this table to InnoDB, running a SELECT COUNT(*) returns only 19 million rows. The conversion confirms 19 million rows were inserted and reports no warnings or duplicates.

I have done the conversion to InnoDB using the following ways
1. by dumping all the data in a text file and loading it.
2. by using ALTER TABLE

Why is the record count so low after conversion to InnoDB?
Who should I believe: InnoDB or MyISAM?
Any ideas as to what can be done to avoid loss of this many rows?

I will be posting output from my latest conversion attempt in some time.

----------------------------
PS: Thank you for all the replies. It turned out that the index was corrupt and after running OPTIMIZE table and then converting to InnoDB everything went fine, i.e. all the records were converted to InnoDB.

Where did the records go?

I experienced a strange thing happening on one of the tables I converted recently to InnoDB from MyISAM. When the table was using MyISAM, MySQL was reporting 47219404 rows in the output of

SHOW TABLE STATUS LIKE 'book_v2' \G

After converting the table to InnoDB and loading data using

INSERT INTO ... SELECT * FROM ...

I noticed that

SHOW TABLE STATUS LIKE 'book_v2_innodb' \G

reports 19265701 records and for the same table running

SELECT count(identifier) FROM db.book_v2_innodb;

returns

+-------------------+
| count(identifier) |
+-------------------+
| 19996851 |
+-------------------+
1 row in set (6 min 31.14 sec)

Now where did the remaining records go and why is MySQL reporting different value for count(identifier) than the number of rows in "SHOW TABLE STATUS" output?

One thing I would like to note is that the records were inserted after setting UNIQUE_CHECK and AUTOCOMMIT to 0.

<update</>
Another weird thing is (notice the number of rows):

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 19265701
Avg_row_length: 305
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (2.60 sec)


mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 19445335
Avg_row_length: 303
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (1.02 sec)


mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 18951343
Avg_row_length: 311
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (0.79 sec)

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 18996251
Avg_row_length: 310
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (1.65 sec)

Weird, huh?

Fluctuating number of rows during data insertion

Today I noticed something interesting. As I was migrating a table from MyISAM to InnoDB, I saw the number of rows fluctuate up and down as time passed suggesting that SHOW PROCESSLIST wasn't actually reporting the exact number of rows but rather an estimate.

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7505110
Avg_row_length: 272
Data_length: 2043674624
Max_data_length: NULL
Index_length: 391118848
Data_free: 0
Auto_increment: 7980185
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 6144 kB
1 row in set (0.01 sec)

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7138176
Avg_row_length: 287
Data_length: 2055208960
Max_data_length: NULL
Index_length: 392167424
Data_free: 0
Auto_increment: 8018719
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 9216 kB
1 row in set (0.00 sec)

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7434343
Avg_row_length: 277
Data_length: 2059403264
Max_data_length: NULL
Index_length: 393216000
Data_free: 0
Auto_increment: 8035298
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 11264 kB
1 row in set (0.05 sec)

Copying MySQL Tables

I really find this one liner very handy when copying MySQL tables between different hosts.

MySQL Failed to open log file (InnoDB)

One needs to be very careful before deleting files as once deleted the files are gone for good. A friend of mine today called as MySQL was failing to start on his server. Earlier when "cleaning" his server, he had deleted binary log files without fully understanding their role. When I logged in to his server, I noticed the following errors:

060716 17:21:53  mysqld started
060716 17:21:53  InnoDB: Started; log sequence number 0 1736000119
/usr/sbin/mysqld: File './mysql-bin.000002' not found (Errcode: 2)
060716 17:21:53 [ERROR] Failed to open log (file './mysql-bin.000002', errno 2)
060716 17:21:53 [ERROR] Could not open log file
060716 17:21:53 [ERROR] Can't init tc log
060716 17:21:53 [ERROR] Aborting

060716 17:21:53  InnoDB: Starting shutdown...
060716 17:21:55  InnoDB: Shutdown completed; log sequence number 0 1736000119
060716 17:21:55 [Note] /usr/sbin/mysqld: Shutdown complete

060716 17:21:55  mysqld ended

I deleted the entries from mysql-bin.index and MySQL started without a problem.

MyISAM to InnoDB

One thing that sucks really bad with MyISAM is table level locking which can cause some serious issues at times when INSERT/UPDATE/DELETE load is very high. If you are experiencing problems in your application associated with locking, try converting your tables to InnoDB.

I was asked today a question as to how one can convert a MyISAM table to InnoDB. I will blog about it in detail later but for right now here's one way:

-ALTER TABLE table_name ENGINE = InnoDB;

and here's another way:

- dump the table to a SQL file
- change table type from MyISAM to InnoDB in the dumped file
- disable keys (to speed up the process)
- SET AUTOCOMMIT to 0 (for speeding up the process)
- load the table
- SET AUTOCOMMIT to 1
- enable keys (the index is rebuilt at this point)

One is preferred over the other based on various factors such as time limit in which the conversion must be done.


Also see:

• MyISAM to InnoDB conversion article
• Join Performance of MyISAM and InnoDB
  

Indexes, Low Index Selectivity and Index Performance

The other day I was talking to Jay about low index selectivity. One of the column in my users table had extremely low index selectivity (less than 0.0000005). However when running certain queries involving COUNT and utilizing that column, it seemed the index was being used as when I ran the same queries with IGNORE INDEX, the query execution time was significantly higher.

There have been posts earlier on PlanetMySQL that noted that in cases of extremely low index selectivity, MySQL's cost based optimizer will never use it.

Jay pointed out that using COUNT(column_name) in a query is a special case where even in case of extremely low selectivity, having an index speeds up the query. For all other queries not involving COUNT, the index will not be used if the selectivity was very low.

I will be posting the queries and further analysis in a later post.

You may want to see:

• Sheeri has a great post on Selectivity and Index Performance
• Measuring Index Selectivity


• Jay Pipes writes about calculating index selectivity using Information Schema
• Measuring index selectivity on Oracle
  
• Vadim writes about Indexes in MySQL
• Why does using an index takes my query longer

err-log or log-error

Today I was investigating why one of the MySQL server wasn't logging anything in the error log. By default the error logs are written in the data directory with a .err extension.

The "err-log" configuration option only works with safe_mysqld. If you try to specify this option in the configuration file under [mysqld] then the following error is generated.

[ERROR] /path/to/mysqld: unknown variable 'err-log=/path/to/error_log'

When this option is used, MySQL server doesn't report the error log file path for the "log_error" variable in the output of "SHOW VARIABLES"

To specify the error log file so that it will show up in SHOW VARIABLES, we need to use the "log-error" option.

Compiling MySQL Administrator on Solaris 10

I have been trying to compile MySQL Administrator on a Solaris 10 box with no success. I followed Ronald's notes (link later) on the compile issues related to mysql-gui-common but continued having problems.

So eventually I filed a bug report and yesterday it was verified. So hopefully it can be resolved soon.

Frank