Useful MySQL commands

# Set the auto_increment value (e.g. `id`) to one more the highest `id` value currently
alter table `foo` auto_increment=1

# Show how MySQL will parse this query, including key (indices) used and any additional parameters
explain select * from `foo`;

# Show indices for the given table
show keys from `foo`;
Advertisements

MySQL: Use aggregate functions in conjunction with group by

I was debugging a reporting module that I wrote. The database query looks like the following:

select units_sold, segment, substring(transacted_on, 1, 7) as transacted_on
from sales s
where foo = 'bar'
group by segment, transacted_on

The sales table contains multiple records for a given month. transacted_on is a timestamp field, so we group by month (MySQL date format) so we can have the sales figures by segment and month. Based on the above query, the results were off by significant values, it was as if the entries at the end of the table were not being analyzed.

I checked the SQL query (there were joins, and where clauses that have been removed) the whole morning. It was thoroughly unproductive, because the data was correct, indeed. I wondered if I was dead tired or hungover from Sunday. Or that somehow this bug had slipped into MySQL which produced incorrect results for me, even though the query was absolutely correct.

It turns out that the query was wrong. MySQL developers: you are forgiven, hehe. I am thoroughly embarrassed of course.

select sum(units_sold) as units_sold, segment, substring(transacted_on, 1, 7) as transacted_on
from sales s
where foo = 'bar'
group by segment, transacted_on

wrapping units_sold in the aggregate function sum() solved the problem immediately. If you’re like me, wondering why the results don’t turn out correctly when you’ve have group by clause, just look at the select clause. If sum(), count() and the like are not there, chances are the query is not correct.

Separate MySQL databases environments

Now this may be common practice for some of you all, but not all.

I’d recommend the (security) practice of having separate databases for your different environments; e.g. development, testing and production. Say for a given user ‘kzhiwei’, I should have up to three databases available for use:

kzhiwei_development
kzhiwei_production
kzhiwei_testing

PHPMyAdmin provides excellent support for this function by means of its database selector dropdown, it displays databases the logged in user has access to like:

kzhiwei
|_development (3)
|_production (3)
|_testing (3)

The following is MySQL syntax with the following points:

  1. localhost-only access is provided
  2. usage of the MySQL server, sans access to other databases
  3. wildcard access to ALL databases that are named like “kzhiwei_”
CREATE USER 'kzhiwei'@ 'localhost' IDENTIFIED BY 'password';

GRANT USAGE ON * . * TO 'kzhiwei'@ 'localhost' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `kzhiwei_%` . * TO 'kzhiwei'@ 'localhost';

Using enum where sensible

On database design here.

Having been schooled in the merits of normalization previously, I had always thought it sensible to use separate tables for dictionary fields. 2NF, 3NF, yes?

Now I have a [members] table where they have some title (Dr, Mr, Mrs, ..). I used to put those in a separate table, so when you retrieve data, you gotta do a join on the identifier… yes. Horror of horrors.

Table joins simply don’t scale as well even though you do indexing and index hinting, so use enum where sensible.

[members].[title] of type enum (‘Dr’,’Mr’,’Mrs’, ..). In my next post I’ll put out a MySQL enum helper for Code Igniter (CI). Given some table, the function returns a list of rows that are of enum data type, nicely separated out.

I’m currently using it with my CI_Smarty class. If you don’t want to learn Smarty syntax, feel free to use the default solution, it works fine as well.

PHP5 on Fedora Core 6 (64-bit)

# gunzip -dc php-5.2.1.tar.gz | tar -xof -
# cd php-5.2.1
# ./configure --with-apxs2=/usr/local/apache2/bin/apxs \
--with-mysql \
--with-libdir=lib64
.
.
.
checking for MySQL support... yes
checking for specified location of the MySQL UNIX socket... no
checking for MySQL UNIX socket location... no
configure: error: Cannot find MySQL header files under yes.
Note that the MySQL client library is not bundled anymore!
localhost# mysql -v
zsh: command not found: mysql

Great! MySQL is not installed! Gotta head over to the MySQL website for more progress. Look for Linux (non RPM downloads), for platform Linux (AMD64 / Intel EM64T).

Now… Linux is really powerful, way more than Windows ever could be IMO but to even have a decent LAMP — that’s Linux, Apache, MySQL and Perl/PHP — for development purposes only going, the learning curve is so steep.

In contrast, I could just get XAMPP and have a development server on Windows running really quickly.

* update *

Yessss… the install for the AMD64 binary went through like a dream! I didn’t even know it was a binary at first:

# gunzip -dc mysql-standard-5.0.27-linux-x86_64-glibc23.tar.gz | tar -xof -
# ./configure
NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it!
.
.
.
Starting the mysqld server.  You can test that it is up and running
with the command:
./bin/mysqladmin version
localhost# Starting mysqld daemon with databases from /root/mysql-standard-5.0.27-linux-x86_64-glibc23/data
STOPPING server from pid file /root/mysql-standard-5.0.27-linux-x86_64-glibc23/data/wayne.sg.pid
070217 16:19:26  mysqld ended

OK, some problems, it started, then stopped on its own. Fine. I looked for INSTALL-BINARY, and lo and behold:

# groupadd mysql
# useradd -g mysql mysql
# cd /usr/local
# mv /root/mysql-standard-5.0.27-linux-x86_64-glibc23/ /usr/local/
# cd /usr/local/
# ln -s mysql-standard-5.0.27-linux-x86_64-glibc23 mysql
# cd mysql
# scripts/mysql_install_db --user=mysql
# chown -R root  .
# chown -R mysql data
# chgrp -R mysql .
# bin/mysqld_safe --user=mysql &
# ps -aef | grep mysql
root     18474  7962  0 16:14 pts/1    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql    18492 18474  0 16:14 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/wayne.sg.pid --skip-external-locking
root     19031  7962  0 16:23 pts/1    00:00:00 grep mysql

Yes… it worked!

Now back to PHP:

# cd /root/php-5.2.1/
# ../configure --with-apxs2=/usr/local/apache2/bin/apxs \
--with-mysql \
--with-libdir=lib64 \
--with-mysql=/usr/local/mysql \
--with-mysql-sock=/usr/local/mysql/data/wayne-devel.pid
.
.
.
checking for MySQL support... yes
checking for specified location of the MySQL UNIX socket... /usr/local/mysql/data/wayne.sg.pid
checking for MySQL UNIX socket location... /usr/local/mysql/data/wayne.sg.pid
configure: error: Cannot find libmysqlclient under /usr/local/mysql.
Note that the MySQL client library is not bundled anymore!

Hmmm. I installed MySQL server, now I need the client.

# rpm -ivh MySQL-client-standard-5.0.27-0.rhel4.x86_64.rpm
error: Failed dependencies:
perl(DBI) is needed by MySQL-client-standard-5.0.27-0.rhel4.x86_64

Noooooooooooooooooooooooooooooooooooo. Luckily I am familiar with installing CPAN modules. But I wonder why Perl DBI is required?!? Nevermind. Will figure that out again. I guess.

# gunzip -dc DBI-1.53.tar.gz | tar -xof -
# cd DBI-1.53
# perl Makefile.PL
# make
# make test
# make install

Same goes for DBD-mysql, since I am be doing Perl development in future.

# gunzip -dc DBD-mysql-4.001.tar.gz | tar -xof -
# cd DBD-mysql-4.001
# perl Makefile.PL
# make
# make test
# make install
# rpm -ivh MySQL-client-standard-5.0.27-0.rhel4.x86_64.rpm
error: Failed dependencies:
perl(DBI) is needed by MySQL-client-standard-5.0.27-0.rhel4.x86_64

Argh. What’s wrong? Here’s what’s wrong. This is the 3rd time I have encountered issues with 64-bit. So much for bleeding edge. The advice was to skip the dependency checking:

# rpm -ivh --nodeps MySQL-client-standard-5.0.27-0.rhel4.x86_64.rpm

*update2 *

I STILL can’t get php to compile with MySQL support, the error is something like “can’t find libmysqlclient”. Shall skip the –with-mysql options until I can figure out what’s wrong. Google-ing resulted in alot of links but no help so far.

Connection refused

I was recently put in-charge of overseeing the database server migration — from Oracle to MySQL.

We had all worked really hard, making sure we stick to the schedule as prescribed. Until two days before the final migration, I realised that the web server couldn’t connect to the database server.

“Connection refused” it said.

“It must be the firewall!” I thought. Our client had co-located a web server, a backup web server and their old Oracle database, and we were not managing the firewall, so it was easy to pin responsibility on the much-maligned firewall. Then I looked at the firewall — it was allowing incoming/outgoing connections on the MySQL port, no issue. Hmmm.

So I wrote a script to test for connectivity:

error_reporting(E_ALL);
$link = mysql_connect('w.x.y.z:3306:/tmp/mysql.sock', 'wayne-devel', ...);

if (!$link) {
die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully';
mysql_close($link);

There was no return value, not even the “Could not connect” message. So it was the weirdest thing.

It turned out, in ascending order of heartache:

1. the firewall on the database server was blocking incoming mysql connections
2. the firewall on the web server was blocking outgoing mysql connections
3. PHP was not compiled with mysql support on the web server

ARGH. Well. Better now then later I guess.