Archive for 2010

MySQL: partition watchout

When designing partitions keep in mind that it will be required to have the number of partitions and open file limit system variable in harmony.

Thus each partition creates an overhead of extra two files (#p.myd and #p.myi). I.e. partition by hash of month will produce 2 * 12 months = 24 extra files. Not too bad for the default MySQL settings. However if there is plan for much more partitions, the open file limit has to be tuned respectively. Otherwise the error “mysql out of resources when opening file errcode 24” may occur.

To change the number of file descriptors available to MySQL, you can set open-files=2048 (or to any other reasonable number) in the MySQL configuration file.

Hiding Cat

PHP: split() alternative

Unfortunately (or fortunately?) split() function became deprecated as of PHP 5.3.0.

However besides annoying warning Deprecated: Function split() is deprecated in… it also means that soon this function is going to be completely unavailable. So it’s better not to wait when the warning turns into the fatal error.

Recommended alternative preg_split() has the same parameters, i.e. the only change required is just adding prefix preg_ to each call of split().

Of course if regular expression is not required then it is better apply explode() to split a string by string.

Subversion: How to require comments

Normally developers agree and promise to write comments for each commit. Often the comments are still not written though.

Subversion pre-commit hook allows to make comments required. For instance file pre-commit.tmpl contains a sample script which will reject commits if no log message is supplied, or the message is too short. However it doesn’t report back any error message (i.e. a generic “commit failed” is returned to SVN client), and there is no provision for making sure the comment is a minimum length.

Ryan Stille came up with excellent Perl script which does the job in more transparent way compare to the original shell script. It allows setting minimum length of the comment and custom error message when the message is absent or too short.

# config section
$minchars = 4;
$svnlook = '/usr/bin/svnlook';
$repos = $ARGV[0];
$txn = $ARGV[1];
$comment = `$svnlook log -t "$txn" "$repos"`;
if ( length($comment) == 0 ) {
  print STDERR "A comment is required!";
elsif ( length($comment) < $minchars ) {
  print STDERR "Comment must be at least $minchars characters.";

Note that pre-commit.tmpl has to be renamed without the “.tmpl” on it and set as executable.

Of course there is no guarantee that all comments will be relevant. But at least it could work as reminder that the comment is needed right now for current commit.

Lady Java – JavaZone

MySQL: How to force a new unique index to drop duplicated rows

If table is not empty, there may be duplicated records. If so a regular alter query to create a new unique index will return an error like below.

ERROR 1062 (23000): Duplicate entry ‘132653-47’ for key 1

However keyword IGNORE allows to force a new unique index to drop duplicated rows.


In this case the output will be something like this:

Query OK, 507 rows affected (0.02 sec)
Records: 507 Duplicates: 0 Warnings: 0

So simply using IGNORE helps to save time on writing a custom script to clean up duplicates.

MySQL 5.0 upgrade to 5.1 watchouts

Although it might be a good time to start testing the third milestone of MySQL 5.5, I am sure many projects are still in transition between versions 5.0 and 5.1.

MySQL 5.0 to 5.1 upgrade is well documented and went pretty smoothly in our case. However our project was heavily relied on stored procedures and that gave us some troubles to deal with.

The first issue was just annoying warning “Creation context of stored routine .. is invalid” for every call of any stored routine (stored procedures and functions worked as expected).

It turned out that the upgrade to 5.1 had added four columns to the mysql.proc table:

  • character_set_client,
  • collation_connection,
  • db_collation,
  • body_utf8.

Those columns were created with a default value of NULL and that was generating the warning. But if a stored procedure/function is created in ‘native’ 5.1 environment, it has these values set to the system defaults and no warning is shown.

Manual altering the mysql.proc table and setting the value of ‘character_set_client’, ‘collation_connection’ and ‘db_collation’ to valid values prevented the error form appearing:

  character_set_client = 'utf8',
  collation_connection = 'utf8_general_ci',
  db_collation = 'latin1_swedish_ci';

This issue was reported as bug and marked as closed for version 5.1.21 in 2007. But it looked like it had made it back to version 5.1.4x somehow in 2010.

The second issue was more complicated and actually some stored procedures stopped working because of weird “Column count doesn’t match value count at row 1” error (which did not make much sense though).

Additional research showed that some insert queries did not work withing prepare\execute blocks due to incompatible changes in 5.1.25 to the way that the server handles prepared statements. The bug #21774 shed some light on this issue too.

For example the statement like below (within a stored procedure) may produce the error mentioned above:

SET vNum = 1;

Simply changing vNum type from INT to TINYINT helped (so the variable type matches the column type in the table).

I hope this will save couple hours to someone unless it has been taken care of in newer MySQL releases.

Allen Hughes – Get Back Up

A good friend of mine Allen Hughes covering Toby Mac’s “Get Back Up” for the 104.7 The Fish, Opening Act contest.

MySQL: How to stop a running query?

There is a magic MySQL statement KILL which allows to terminate either connection or query associated with provided thread ID.

SHOW PROCESSLIST statement allows to see running threads (including thread ID).

Example 1:


terminates the statement that the connection is currently executing under thread 24, but leaves the connection itself intact.

Example 2:

KILL 24;



terminates the connection associated with the thread 24 (i.e. re-connect will be required).

How to write robots.txt?

« Previous PageNext Page »