Alex Stetsenko

Figuring out how to apply unique index on a case sensitive varchar column turned out to be only a part of the solution.

Another thing to take into account is sorting. Once a column is case sensitive, ORDER BY starts working in a bit different way:

Abc.txt
Bcd.txt
Cde.txt
abc.txt
bcd.txt
cde.txt

To address this ORDER BY should look as follows:

SELECT name FROM file
ORDER BY CAST(name AS CHAR) ASC, BINARY name DESC;

In this case the result set is sorted “as usual”:

Abc.txt
abc.txt
Bcd.txt
bcd.txt
Cde.txt
cde.txt

Very detailed list of MySQL 5.6 improvements from Peter Zaitsev, CEO of Percona.

A few to name:

• Optimized row based replication
• Replication utilities for failover and admin
• Improved EXPLAIN (e.g. explain for UPDATE/DELETE queries)
• Explicit partition selection in queries (e.g. SELECT * FROM employees PARTITION (p0, p2);)
• Full text search index for Innodb
• Microsecond TIME precision

Although MySQL 5.6 is still development release, I think it is not too long before it becomes GA.

MySQL is case insensitive by default and normally it is more than enough. However one of my recent projects required a case sensitive varchar column with unique index. Latter would immediately trigger ‘Duplicate entry … for key …’ error for “the same” strings.

CREATE TABLE file (
  id INT AUTO_INCREMENT, 
  name VARCHAR(100) NOT NULL, 
  PRIMARY KEY(id),
  UNIQUE(name)
);
 
INSERT INTO file (name) VALUES ('test.txt'), ('test.TXT');

For example in the query above the second insert fails returning that error.

A way to address this is just to use a case sensitive collation (e.g. utf8_bin).

CREATE TABLE file (
  id INT AUTO_INCREMENT, 
  name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  PRIMARY KEY(id),
  UNIQUE(name)
);

MySQL’s show full processlist is a great way to monitor and diagnose what is going on with the database server at certain moment.

However often it is needed to have an aggregated view or evaluate only specific type of users and\or queries.

The solution turned out to be pretty simple since show full processlist is just an alias for a regular query to processlist table within information_schema database.

So the following queries are equivalent:

SHOW FULL PROCESSLIST;
 
SELECT * FROM information_schema.processlist;

As result it is possible to present the data from process list any convenient way. There are some basic examples below.

-- display number of connections for each user
SELECT `USER`, COUNT(*) FROM information_schema.processlist
GROUP BY `USER`;
 
-- display number of connections for each host
SELECT `HOST`, COUNT(*) FROM information_schema.processlist
GROUP BY `HOST`;
 
-- display root user activity
SELECT * FROM information_schema.processlist
WHERE `USER` = 'root';
 
-- display processes associated with SELECT queries
SELECT * FROM information_schema.processlist
WHERE `INFO` LIKE 'SELECT %';
 
-- display average query time for each database
SELECT `DB`, AVG(`TIME`) FROM information_schema.processlist
GROUP BY `DB`;

Often it is quite reasonable to have the database to take care of some audit related columns like date added or updated.
Unfortunately MySQL does NOT allow functions within table definitions like let’s say MS SQL does, e.g.:

CREATE TABLE t (f datetime NOT NULL DEFAULT now());

The only work around is to use timestamp column instead of date or datetime ones. There can be only one auto set timestamp column though.
So I normally use it to store date updated and save the date created manually.

CREATE TABLE t
(
  name VARCHAR(20),
  date_updated TIMESTAMP NOT NULL
               DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Also there are two things to keep in mind:

• date_updated will be set based on MySQL server time. This is especially important if other data is stored using GMT or non-server time zone.
• date_updated must NOT be a part of update or insert query.

-- set date_updated to current time
INSERT INTO t (name) VALUES ('test');
UPDATE t SET name = 'test';
 
-- override date_updated to certain value
INSERT INTO t (name, date_updated)
       VALUES ('test', '2011-08-02 20:00:00');
UPDATE t SET name = 'test',
             date_updated = '2011-08-02 20:00:00';

Alternatively it is possible to use triggers to have as many auto updated datetime columns as needed. However personally I prefer to avoid triggers as much as possible. Mostly because often triggers are misused and as result application complexity is increased for no reason.

Apparently there is a pretty straight forward way to recover MySQL password. Although server root access is mandatory it is great solution in certain cases like recovering password of abandoned test server or when server was configured by someone else etc.

The key for success is to be able to (re)start MySQL using -skip-grant-tables option. In that case MySQL won’t ask for password.
Then simply reset MySQL password and restart MySQL ordinarily.

Details for each step are described by Vivek Gite in Recover MySQL root Password.

There could be some cases when the general query log has to be kept running. This includes development stage, different flavors of debugging and query analysis and so on. However certain queries do not need to be stored there even though the logging is on. One of the examples is setting user password – it will be logged in clear text.

A possible workaround is to turn off the general logging temporarily. MySQL 5.1 and higher allows to do it without the service restart.

CREATE USER john;
SET SESSION sql_log_off = 1;
SET PASSWORD FOR john = PASSWORD('smith');
SET SESSION sql_log_off = 0;

However SUPER privilege is required to set the session variable sql_log_off.

Apparently some web applications do store uploaded files in the database as blob field by default (for instance it is true for Mantis bug tracker). While it is acceptable (but not too common?) to store files in the database, it may cause certain problems like uploading large files (particularly 1Mb+).

Normally the first thing to look at would be the actual application configuration. OK, Mantis is configured to accept files up to 5Mb by default. I.e. no problems here. Wait… but PHP is configured to accept files up to 2Mb by default… So where does 1Mb restriction come from?

The answer is that it comes from the database. MySQL has limits on packet size. The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1Gb. However by default it is 1Mb! As result any packets larger than 1Mb will cause packet too large error.

There is max_allowed_packet option in the configuration which allows to set the packet size. For example:

[mysqld]
max_allowed_packet=16M

Recently I’ve run into another MySQL bug. This time it is related to temporary tables. The bug was reported as #12257.

Basically select * from tmpTable does not work within stored procedure if any column is altered since the table has been originally created. So MySQL keeps converting * to a old column list while some columns may not exist anymore.

A simple workaround allows to eliminate impact of this bug as well as using select * is not recommended anyway. However the most surprising part of this story that this bug cannot be resolved for more than 5 years: it was reported in 2005.

It is still common to store IP addresses as a varchar(15) field though it is possible to use integer type instead. Unlike the varchar type, integer has fixed size and uses only 4 bytes.

INET_ATON() is used to convert an IP address to a number and INET_NTOA() – for the reverse operation.

SELECT INET_ATON('127.0.0.1');
SELECT INET_NTOA(2130706433);

It is important to use INT UNSIGNED with INET_ATON() so that IP addresses for which the first octet is greater than 127 is stored correctly.

Also PHP has similar functions – ip2long() and long2ip(). However ip2long() function may return negative results in certain cases. To make it always positive unsigned intereger ip2long() call has to be paired with printf() or sprintf() function:

printf('%u', ip2long('128.0.0.1');

Thus plan the IP address column datatype accordingly.