Alex Stetsenko

PROCEDURE ANALYSE examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes. Simply append PROCEDURE ANALYSE to the end of a select statement:

SELECT f1, f2 FROM t PROCEDURE ANALYSE(20, 2000);

where

• 10 is is the maximum number of distinct values which is checked for columns;
• 2000 is the maximum amount of memory which can be allocated per column while trying to find all distinct values.

This MySQL feature can be helpful after importing new data or for checking your existing tables to verify whether columns datatype was designed providently.

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.

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.

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.

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

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.

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.

ALTER IGNORE TABLE `t` ADD UNIQUE INDEX `i` (`f1`, `f2`);

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.

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:

UPDATE proc SET
  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:

DECLARE vNum INT;
 
CREATE TABLE t (f TINYINT);
 
SET vNum = 1;
SET @SQL = CONCAT('INSERT INTO t SELECT ', vNum);
 
PREPARE QUERY FROM @SQL;
EXECUTE QUERY;
DEALLOCATE PREPARE QUERY;

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.

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

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:

KILL QUERY 24;

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

Example 2:

KILL 24;

or

KILL CONNECTION 24;

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