I’ve always used MySQL fulltext indexes with a match query for keyword searches but I’ve never been happy with the results and lack of configuration choices. The limited nature of word interpretation and the boolean searches were useless when visitors didn’t know how to use search operators. Fulltext indexes require the MyISAM table structure – yet we generally prefer InnoDB because of performance and foreign key constraints.

For a recent project I decided to finally (long overdue, I know) make the the transition to an external search and indexing application called Sphinx.

(more…)

When working with MySQL databases it’s often necessary to convert a regular field into one that refers to a second table using a foreign key. The process for this requires several steps:

1. Insert distinct column values into new secondary table.
2. Replace (update) existing table fields with proper foreign key for the related record in the secondary table.

This process can be time consuming if attempted manually. Luckily, MySQL 4.1+ allows for subqueries as well as the INSERT… SELECT… syntax.
(more…)

At Trellis Development we’re working on several applications which need to frequently convert dates between various time zones.

By default, both PHP and MySQL operate using a single timezone which is identified during installation and is quite often the same as the host server. Both tools allow you to specify a different timezone on the fly, and all following date operations work based off of that locale.

The primary goal is to preserve a base standard time so that we can easily convert between different timezones, without loosing the ability to convert again. Two choices emerged – storing a Unix Time stamp or a date string stored using GMT/UTC time.
(more…)

Update: A second solution involves changing a hostname in your PHP file from localhost to 127.0.0.1. More info.

Recently I was doing some local development on my Mac OS X Leopard machine. When running apache2 with php I was able to connect to the database just fine, and from the command line I was able to run mysql commands.

However, when running a php script from the command line I was getting an error:

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

After spending some time thinking it was the code, I discovered that the mysql.sock file was missing.

Others on the net found the file elsewhere, like /private/tmp/mysql.sock or /tmp/mysql.sock but I had absolutely no .sock file anywhere.

Turns out, it did exist, but was named differently. According to the phpinfo output, the file was located at

/usr/local/mysql/run/mysql_socket

So, the solution was to create a symlink in /var/mysql to the proper file.

sudo ln -s /usr/local/mysql/run/mysql_socket /var/mysql/mysql.sock

Could have sworn that I’ve posted about this before, but it seems to be missing. To change the minimum word length that MySQL uses for a fulltext index search, just set a configuration and restart.


$ vi /etc/my.cnf


Then add in:


ft_min_word_len=3


Or whatever minimum word length you’re looking for. Then, restart:


/etc/init.d/mysqld restart


Then rebuild your fulltext index.


ALTER TABLE `mytable` DROP INDEX `myfulltext_index`
ALTER TABLE `mytable` ADD FULLTEXT `fulltext` (
`myfieldname` ,
`anotherfieldname` ,
)


While I was at zendcon I needed to setup my macbook with a typical apache/php/mysql server, but I was unsatisfied with both the default server that apple included and with the install system provided by XAMPP. I’ve since setup a server on both my MacBook and my iMac.

In order to get support for mysql as well as tools necessary for compiling, you need to install the apple developer tools. Check your original mac disc as you may already have a copy.

Anyway, on with our install:

Apache

First, download the latest version of the apache httpd web server.

$ ./configure --prefix=/usr/local/apache --enable-module=so --enable-module=rewrite
$ make
$ sudo make install

MySQL

Then download the latest mysql source file. Make sure you download the source “Compressed GNU TAR archive (tar.gz)” as we’ll need to custom compile it.

However, PHP5 no longer includes mysql shared libraries needed during the configure process. To get these, download the source file for the mysql version you just installed. Once downloaded, decompress it and run the following:

Update: As of MySQL 5.5 they’ve modified the build process. If you’re using an older version, skip the old configuration system below.

First, ensure you have CMake installed (via standard configure/make/install). Then, we’ll use cmake to configure mysql using the same options we’ve used in the original example.

$ cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DCMAKE_BUILD_TYPE=Debug
$ make
$ sudo make install
$ sudo mkdir /usr/local/mysql/run
$ cd /usr/local/mysql
$ sudo chown -R mysql:mysql *
$ chmod -R 777 /tmp
$ sudo ./scripts/mysql_install_db
$ sudo /usr/local/mysql/bin/mysqld_safe &

Pre-5.5:

$ ./configure --prefix=/usr/local/mysql --with-unix-socket-path=/usr/local/mysql/run/mysql_socket  --with-mysqld-user=mysql --with-comment --with-debug
$ make
$ sudo make install</code>
$ sudo /usr/local/mysql/bin/mysql_install_db --force
$ sudo mkdir /usr/local/mysql/run
$ sudo mkdir /usr/local/mysql/data
$ sudo chgrp -R mysql /usr/local/mysql
$ sudo chown -R mysql /usr/local/mysql/run /usr/local/mysql/var /usr/local/mysql/data

Then start the server with:

$ sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

zlib

Then, install zlib if it’s not already installed on your system. Download zlib, and it should be a basic ./configure, make, sudo make install process.

Once zlib is installed, make sure you know the path as we’ll need it during the php configure process.

PHP

Next, download the latest php package. Decompress it, and now we’re ready to configure.

$ ./configure --prefix=/usr/local/apache --enable-cli --with-mysql=/usr/local/mysql --with-zlib-dir=/usr/local --with-apxs2=/usr/local/apache/bin/apxs
$ make
$ sudo make install

Restart apache and test everything out. If php didn’t do it automatically, add in AddType application/x-httpd-php .php to your httpd.conf file – commonly located in /usr/local/apache/conf/httpd.conf.

Then, start your webserver.

$ sudo /usr/local/apache/bin/apachectl start

And finally, once everything is done you’ll want to setup your account to properly allow access to mysql and apache commands. Just run the following to add their paths to your bash profile:

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile
echo 'export PATH=/usr/local/apache/bin:$PATH' >> ~/.bash_profile

Many of my clients need a quick backup solution before we implement anything on a larger more permanent scale. I wrote this simple script that simply make a copy of a database and then created a timestamped tarball of the file.

#!bin/sh

date=`date +%Y-%m-%d_%Hh%M`

cd /my/path/for/backups/

mysqldump -umy_user -p"mypass" my_database > $date.sql

tar -zcvf $date.tgz *.sql

# uncomment this line to import the sql into another database, for example as a mirror
#mysql -umy_user -p"mypass" mirror_database < $date.sql

rm *.sql

Then, just setup a cron job to run this at whichever interval you want this to run.

I’m still rather disappointed with my options for weighting random results in MySQL, but I’ve done the best I can do with it. It’s more about figuring out what numbers you use for the weighting rather than the calculation itself. The query itself ends with:

[mysql]
ORDER BY RAND() * weight_fieldname
[/mysql]

Now, my possible weights from weight_fieldname are 5, 20, 100. 5 is the default weight for every record – 20 increases the chances of the record a bit and 100 pretty much sets the record as an exclusive result. Now, if you only have a single weight applied then setting it to 0 will reduce the weight of an item, however this is very difficult when you start having multiple weights.

One additional problem is that if you have a serious amount of records, using ORDER BY RAND() is going to be a performance hit.