Journey with Thinking Sphinx and Crond

This is my journey through time :)

Environment:
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

AND THERE IT WAS !!

!!! 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

PATH=/usr/local/mysql/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/sbin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
SHELL=/bin/bash
RAILS_ENV="production"
LD_LIBRARY_PATH=/usr/local/mysql/lib:${LD_LIBRARY_PATH}


# 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/mysql.sh 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
/opt/local/var/run/mysql5/mysqld.sock

• 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';
FLUSH PRIVILEGES;
quit

• 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: libmysqlclient.so.15: cannot open shared object file: No such file or directory - /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.so
from /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.so
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 mysql.sh with the correct exports and re-login...

[root@heimdull log]# cat /etc/profile.d/mysql.sh
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 'mysql.so'
=> []
>> puts Mysql::VERSION
20700
=> 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/new-host-2.home.pid

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/new-host-2.home.pid --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 http://crazytoon.com/ just thought I would put it on my post-it site to remember.

#!/bin/bash

db=$1

if [ “$db” = “” ]; then

echo “Usage: $0 db_name”

exit 1

fi

mkdir $$

cd $$

clear

for table in `mysql $db -e ’show tables’ | egrep -v ‘Tables_in_’ `; do

echo “Dumping $table”

mysqldump –opt $db $table > $table.sql

done

if [ “$table” = “” ]; then

echo “No tables found in db: $db”

fi