Posts Tagged ‘mysql’

Why did you choose MySQL?

I would like to know the reason that you grabbed MySQL and not PostgreSQL? Easy of install? Think that its better? what you know?

Leave a comment with your thoughts…

uninitialized constant MysqlCompat::MysqlRes error when starting Rails

After a few upgrades, re-installs and moving to new servers I have been getting this error message uninitialized constant MysqlCompat::MysqlRes when I start rails. (Passenger or Thin)

I have been using mysqlplus gem but wanted to move back to MySQL 2.8.1 gem and I think that is when this started to popup more often. (after looking closer I have servers with ruby 1.8.7 and 1.8.6 using the mysql 2.8.1 gem without issues… )

After firing up thin using strace on my CentOS server I found that for some reason the mysql gem is looking for in /lib64 /usr/lib64 … and not in /opt/mysql/lib where these libraries are located. What I did to fix this issue was to create a link in /usr/lib64/ pointing to the library and voila! the problem is fixed.

PS I think you can set the mysql-lib-dir when you install the mysql gem but I have not tested this yet. I’m thinking something like this might have fixed the issue too(not tested):

[root@server]# gem install mysql -- --with-mysql-dir=/opt/mysql \
                                    --with-mysql-lib=/opt/mysql/lib \


My Upgrade to Snow Leopard Journey

After the upgrade I had a ton of issues getting my rails environment back online which I could have avoided IF I just would have uninstalled all my ports.

The problem was that I had both Ruby 1.8.6 and 1.8.7 installed as ports and that was conflicting to the new 1.8.7 that was installed from the upgrade. I could not get passenger to boot after the upgrade…

SO what I did was upgrade xcode, upgrade port then force uninstall ALL ports and install the ports that I wanted. (Reboot 🙂 ) and then I installed the passenger gem using the native 1.8.7 Ruby install.

Graphing Mysql Performance in Groundworks

The Problem

Just installed GroundWorks Community Edition 5.3 and this is a great product but when comparing the built-in graphing capabilities to Hyperics community editon it falls short. Adding monitoring to MySQL, Apache or Tomcat does not give you graphs out-of-the-box but this CAN and will be fixed…

FYI I’m assuming that groundworks is installed and working, and also that you have the ability to monitor cpu, memory and more using ssh to your mysql server. If you don’t have this setup then some steps in this article will NOT work.

The Fix

Quick rundown of fix

  • Create a service for Nagios under the configuration screen.
  • Add the new service to your MySQL server
  • Go to performance and setup a “Graph” template for the MySQL service
  • Wait
  • Watch graphs

Ok that was the quick run-down and that was what I just could NOT get to work the first-time around. I’ll go though how you can add multiple graph points and also how to debug the issue if like happened to me NOTHING is graphed or even that the graph is not created.

Setup check_mysql

First we need a user on the MySQL server that has STATUS only access

mysql> grant usage on *.* to ‘nagios’@’groundworks-host’ identified by ‘password’;

Lets create a service and a command that uses the check_mysql script that is supplied with Groundworks.

Command definition: $USER1$/check_mysql -H $HOSTADDRESS$ -u “$ARG1$” -p “$ARG2$” -P “$ARG3$”
Usage: check_mysql_status!ARG1!ARG2!ARG3
Command line: ?

The add your mysql server in the test box and click “test”

Test: Host:
/usr/local/groundwork/nagios/libexec/check_mysql -H -u "nagios" -p "password" -P "3306"

Uptime: 1862427  Threads: 7  Questions: 195920  Slow queries: 2  Opens: 166  Flush tables: 1  Open tables: 159  Queries per second avg: 0.105

Command returned exit status 0

Nice that works! but that is the easy part. Now we need to look at the “performance” area and actually create the graph setup.

Creating the graph

Go to Performance – configure. You will see a long list of the default graphs and now we need to add our own.

The top one should be Current Load click on the copy button and create a new performance graph that will look like this:

Graph Label: MySQL Stats
Service: mysql_status-port-330[1-9]
Use Service as a Regular Expression ON
Host: *
Status Text Parsing Regular Expression: [\w\d\s:]+Threads: (\d+)[\w\d\s:]+avg: ([\d\.]+)
Use Status Text Parsing instead of Performance Data ON
RRD Name /usr/local/groundwork/rrd/$HOST$_$SERVICE$.rrd
RRD Create Command $RRDTOOL$ create $RRDNAME$ –step 60 –start n-1yr DS:Threads:GAUGE:1800:U:U DS:QPS:GAUGE:1800:U:U RRA:AVERAGE:0.5:1:8640 RRA:AVERAGE:0.5:12:9480 2>&1
RRD Update Command $RRDTOOL$ update $RRDNAME$ $LASTCHECK$:$VALUE1$:$VALUE2$ 2>&1
Custom RRDtool Graph Command
Enable ON

I have MySQL servers listening to multiple ports so when I created the graph I made the service name a regular expression.


This matches the three service names that I have created


The name of the service IS VERY IMPORTANT when you create the graph. When the performance data is imported in the performance graph Groundworks does a search through the database to find the service data using the service name. Since I setup the service with multiple ports for the same mysql_status service it adds the port-3306… to each of the services and that is the name stored in the database. Here is how I setup the multiple ports

In the service check of the hosts mysql_status I added three instances at the bottom of that screen.

Instance Name Suffix Status Arguments

Ok back to the graph setup…

After you have saved the new performance graph there are two directories that you need to worry about.



In the rrd directory you will get a file that is named something like mysqlservername.cadechristian.com_mysql_status-port-3306.rrd, this file is created using the RRD Create Command from the performance template. That will will also be updated using the RRD Update Command.

Lets talk about the RRD Create command

$RRDTOOL$ create $RRDNAME$ –step 60 –start n-1yr DS:Threads:GAUGE:1800:U:U DS:QPS:GAUGE:1800:U:U RRA:AVERAGE:0.5:1:8640 RRA:AVERAGE:0.5:12:9480 2>&1

The important part is

–step (how often to plot on the graph) 300 is a good production number but for testing I like 60

DS:Threads:GAUGE:1800:U:U – First datasource
DS:QPS:GAUGE:1800:U:U – Second Datasource

2>&1 put errors to standard out log.

The datasources are found through the regex that I setup that parses the output from the mysql command ( Each datasource is one regex group ):

[\w\d\s:]+Threads: (\d+)[\w\d\s:]+avg: ([\d\.]+)

The (\d+) is how you create a group with the () around what you are matching. So in this regex we get the first group with the number of threads and the second group with the Queries per second.

Now it is important that we also have 2 values in the RRD update command.


If you need to change the RRD create command you need to MANUALLY DELETE the RRD file in the /usr/local/groundworks/rrd directory!!!

The rrd file is created in a default setup after 1-2 min and the performance data will be parsed and visible in the graph maybe after 5min.


The “event” scripts are located in /usr/local/groundworks/nagios/eventhandlers and inside of the scripts there is a variable called $debug that you should set to 3 ( default = 0 ) if you want to get a more descriptive output in the logs.

The  logfile /usr/local/groundworks/nagios/eventhandlers/service_perfdata.log has very light debug information but it shows when the service is pulled for a name host

1244226293 mysql_status-port-3306 Uptime: 1864121  Threads: 7  Questions: 196033  Slow queries: 2  Opens: 166  Flush tables: 1  Open tables: 159  Queries per second avg: 0.105
1244226296 tcp_ssh SSH OK – OpenSSH_4.3 (protocol 2.0)
1244226297 olex-perfdb icmp_ping PING OK – Packet loss = 0%, RTA = 0.13 ms
1244226293 mysqlserver mysql_status-port-3306 Uptime: 1864121  Threads: 7  Questions: 196033  Slow queries: 2  Opens: 166  Flush tables: 1  Open tables: 159  Queries per second avg: 0.105 1244226296 mysqlserver tcp_ssh SSH OK - OpenSSH_4.3 (protocol 2.0) 1244226297 mysqlserver icmp_ping PING OK - Packet loss = 0%, RTA = 0.13 ms

This is ok information BUT it will not tell us if something did NOT work. So lets add $debug = 3 to the process_service_perf(_db AND _db_file).pl file and wait for the process_service_perf.log file to be created in the eventhandlers directory

And here is a good entry from the debug log:

Fri Jun  5 14:43:14 MDT 2009
Host: mysqlserver
Svcdesc: mysql_status-port-3306
Lastcheck: 1244234573
Statustext: Uptime: 1872401  Threads: 7  Questions: 196614  Slow queries: 2  Opens: 166  Flush tables: 1  Open tables: 159  Queries per second avg: 0.105

No exact service name mysql_status-port-3306. Query database for service pattern matches.mysql_status-port-3306 matches database service pattern mysql_status-port-330[1-9]. Using this entry.

Match in status text for regular expression (?-xism:[\w\d\s:]+Threads: (\d+)[\w\d\s:]+avg: ([\d\.]+))

SQL = SELECT hs.host_service_id FROM host_service as hs, datatype as dt WHERE (’mysqlserver’ AND hs.service=’mysql_status-port-3306′ AND dt.type=’RRD’ AND dt.location=’/usr/local/groundwork/rrd/mysqlserver_mysql_status-port-3306.rrd’ AND hs.datatype_id=dt.datatype_id)

Table host_service, host=mysqlserver, service=mysql_status-port-3306 already has an exisitng entry for location /usr/local/groundwork/rrd/mysqlserver_mysql_status-port-3306.rrd. New entry not added.
/usr/local/groundwork/common/bin/rrdtool update /usr/local/groundwork/rrd/mysqlserver_mysql_status-port-3306.rrd 1244234573:7:0.105 2>&1

Label Threads found in rrd create string: $RRDTOOL$ create $RRDNAME$ –step 60 –start n-1yr:GAUGE:1800:U:U DS:QPS:GAUGE:1800:U:U RRA:AVERAGE:0.5:1:8640 RRA:AVERAGE:0.5:12:9480 2>&1
Label QPS found in rrd create string: $RRDTOOL$ create $RRDNAME$ –step 60 –start n-1yr:GAUGE:1800:U:U:GAUGE:1800:U:U RRA:AVERAGE:0.5:1:8640 RRA:AVERAGE:0.5:12:9480 2>&1

Posting data to Foundation
Elapsed Execution time = 4.50323104858398 seconds

You see that it finds the service name using the regex for the service, then we find two values that are stuffed into the two labels that we have (Threads, QPS)

One of the biggest issues I had was with the collected performance data. After I found that I could use regex everything started working much better.


Using Groundworks to monitor and get graphs is NOW easy but it was a pain to get to this point. I turning point was finding the eventhandler debug log. I hope this will be helpful to someone out there…

Journey with Thinking Sphinx and Crond

This is my journey through time 🙂

Ruby => 1.8.7-72
Rails => 2.3.2
Thinking-sphinx => 1.1.3

I had a server running thinking-sphinx through crontab but noticed that this was not working so I started doing some debugging to see what was causing the non running thinking-sphinx:index task…

First I Changed crontab to have full path like so: (crontab -e)

*/10 * * * * cd /rails/current && RAILS_ENV=production /usr/local/bin/rake thinking_sphinx:index >> /dev/null 2>&1

This did nothing… Then I changed the output to go to a file to see if this would give me something

*/10 * * * * cd /rails/current && RAILS_ENV=production /usr/local/bin/rake thinking_sphinx:index >> /tmp/sphinx.output 2>&1

This gave me a timestamp ??? Running that exact command from the shell gives me a full reindex without any issues

Then I figured maybe I needed a trace so I moved the cron task to /etc/cron.d/thinking-sphinx.ct and put this in the file

*/10 * * * * cd /rails/current && RAILS_ENV=production /usr/local/bin/rake thinking_sphinx:index –trace >> /tmp/sphinx.output 2>&1


!!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.

Damn I should have know. So its a PATH issue when running rake under cron… Here is the latest /etc/cron.d/thinking-sphinx.ct I have that is now WORKING!!

# ————- minute (0 – 59)
# | ———– hour (0 – 23)
# | | ——— day of month (1 – 31)
# | | | ——- month (1 – 12)
# | | | | —– day of week (0 – 6) (Sunday=0)
# | | | | |
# * * * * * command to be executed


# re-index production sphinx every 15 minutes
*/10 * * * * root cd /rails/current && /usr/local/bin/rake thinking_sphinx:index >> /dev/null 2>&1

There are two important settings there setting the PATH with ruby/rakes install path and MySQLs install path. But also the LD_LIBRARY_PATH so that rails can find the MySQL library. I have that set in /etc/profile.d/ but cron does not load the profile.

Mysql5 in OSX

Install mac ports

Install Mysql:
freddy@new-host-2:~$ port list | grep mysql5
mysql5 @5.0.67 databases/mysql5
mysql5-devel @5.1.28-rc databases/mysql5-devel
freddy@new-host-2:~$ port install mysql5

  • First run this command in your terminal to install the MySQL 5 package:

    sudo port install mysql5 +server
  • Second load the MySQL server by running the following command: (Note: Mac OS X Tiger and above use launchd for starting programs, and this command is worth getting familiar with. In this case the startup configuration file was created for us when we installed MySQL 5.)

    sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist
  • Third figure out the path for your server socket. It should be /opt/local/var/run/mysql5/mysqld.sock but verify this by running this command:

    mysql_config5 --socket
  • Fourth you want to create a shortcut to the MySQL socket so that PHP, Ruby on Rails, Python, and your other languages can access it. These commands should make MySQL work for PHP and Ruby on Rails:

    sudo ln -s /opt/local/var/run/mysql5/mysqld.sock /tmp/mysql.sock
    sudo mkdir /var/mysql
    sudo ln -s /opt/local/var/run/mysql5/mysqld.sock /var/mysql/mysql.sock
  • Fifth you will need to shut down the MySQL server for now. Run this command:

    sudo launchctl unload -w /Library/LaunchDaemons/org.macports.mysql5.plist
  • Sixth you need to setup the root user and the default MySQL database. Sometimes called the grant tables. To do this run this command:

    sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql
  • And last you’ll need to start up the MySQL server and then login and change the root password.

    sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist
    mysql5 -u root
    UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
  • Now you should be able to login using your new password.

    mysql5 -u root -p

That’s it you’re all setup with MySQL on Mac OS X.

mysql gem and mysql NOT from RPM

Have you ever seen this:

>> require ‘mysql’
LoadError: cannot open shared object file: No such file or directory – /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/
from /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/
from /usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:32:in `require’
from /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
from /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
from /usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
from (irb):2

I found this happens when your environment for mysql is not setup correctly. The fix is easy. All you need is to add a file in the /etc/profile.d directory called with the correct exports and re-login…

[root@heimdull log]# cat /etc/profile.d/
export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH
export PATH=/usr/local/mysql/bin:${PATH}

That fixes the binary and library paths… MAKE SURE THERE ARE NO MYSQL RPMS INSTALLED…

rpm -qa | grep mysql should give no mysql-[version] or mysql-server-[version] or mysql-devel-[version]

log-in and out…

install the gem

[root@heimdull ~]# gem install mysql — –with-mysql-config=`which mysql_config`
Building native extensions. This could take a while…
Successfully installed mysql-2.7
1 gem installed

test the new gem:

[root@heimdull current]# RAILS_ENV=production ruby script/console
Loading production environment (Rails 2.1.0)
>> require ‘’
=> []
>> puts Mysql::VERSION
=> nil
>> exit

MySQL on Leopard for rails

SQLite is a great little tool and very easy to use and setup… gem install sqlite3-ruby and you are done!! But now I’m creating a rails application that needs foreign keys and that’s something that SQLite does not do. So MySQL it is…(I’m a MySQL guy if you haven’t figured)
My choice to install MySQL on the macbook was using macports… I’ll assume that macports is installed correctly for the next exercises…  (port version should give you the version number)
Installing MySQL 5.0 with port:
sudo port install mysql5 +server
When the installation is done you have to create the run directory
sudo mkdir /opt/local/var/run
sudo mkdir /opt/local/var/run/mysql5
cd /opt/local/var/run
sudo chown _mysql mysql5
ls -al
total 0
drwxr-xr-x  4 _mysql  admin   136B Mar 12 10:37 mysql5/
setup the config file for MySql. (use the template so you can see where Mysql puts stuff)
sudo /opt/local/share/mysql5/mysql/my-small.cnf /opt/local/etc/mysql5/my.cnf
Testing if MySQL Starts…
sudo /opt/local/lib/mysql5/bin/mysqld_safe5
(you will need crtl-Z and then type bg. This will put the MySQL process in the background. type jobs to see the process)
now connect to mysql with the client
mysql5 -uroot
If you want to stop MySQL you have to kill the process. ps -ax | grep mysqld… there should be one mysqld and one mysqld_sfae5 process get BOTH pids and run kill pid pid.
ps -ax | grep mysqld 70170   ??  S      0:00.01 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe –datadir=/opt/local/var/db/mysql5 –pid-file=/opt/local/var/db/mysql5/
70195   ??  S      0:01.22 /opt/local/libexec/mysqld –basedir=/opt/local –datadir=/opt/local/var/db/mysql5 –user=mysql –pid-file=/opt/local/var/db/mysql5/ –port=3306 –socket=/opt/local/var/run/mysql5/mysqld.sock kill  70170 70195
If you only kill one the server will auto restart!!
Running MySQL using MySQL launchctl
This is where I need more help BUT this is what I did and it works:
sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist  
PS(make sure you kill mysqld_safe5 before doing this…)
Now the part I don’t udnerstand… the server just started but I don’t know how to CONTROL the server like stop, start and restart???
sudo launchctl stop org.macports.mysql5

This did “restart” MySQL but using start/restart did nothing. Looks like when you stop MySQL it aut restart from lunched. 

Password for MySQL root user?
mysqladmin5 -u password ‘secret-password’
connecting with password
mysql5 -uroot -p

Mysql driver for rails

When installing the mysql gem you might get this:
Building native extensions.  This could take a while… ERROR:  Error installing mysql: ERROR: Failed to build gem native extension.
the fix is easy but you are going to need the mysql-devel package if you are on redhat then do this:
yum install mysql-devel
Now you can install the mysql driver like this:
gem install mysql — –with-mysql-config=`which mysql_config`
To test the driver us the rails console:
# ruby script/console
>> require ‘mysql’
>> puts Mysql::VERSION
This should give you the 20700 version. (As of Jan 2007 thats the latest version.)Now enjoy the speed.

MySQL Dump shell script

I found this script over at just thought I would put it on my post-it site to remember.
if [ “$db” = “” ]; then
echo “Usage: $0 db_name”
exit 1
mkdir $$
cd $$
for table in `mysql $db -e ’show tables’ | egrep -v ‘Tables_in_’ `; do
echo “Dumping $table”
mysqldump –opt $db $table > $table.sql
if [ “$table” = “” ]; then
echo “No tables found in db: $db”