MySQL AUTO_INCREMENT updates

I just found out the painful way that if you manually update a column which is normally calculated automatically as an auto_increment it seems to work at first ..

but MySQL doesn't update the auto_increment value for the table - so subsequent inserts can fail.

The solution seems to be to delete and re-insert the row.

http://bugs.mysql.com/bug.php?id=12434

Test code:

mysql> create table test (id int(10) unsigned NOT NULL, col1 varchar(255), PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (col1) VALUES ('test1');

Read more

SQL Greatest and Least

Most of the time I can get what I want out of SQL using the standard aggregate functions, but today I needed to find the latest timestamp from any one of three columns.

I achieved this using the GREATEST() function, it's not party of the SQL standard but is commonly available.

GREATEST(value [, ...])

Read more

Adding more data types to schema API for Drupal 7

I've written a patch for Drupal 7 to enable date and time data types to be defined for the schema API.

http://drupal.org/node/200953

There seems to be some resistance to this idea due to concerns about compatibility between database versions (Drupal now supports MySQL, PostgreSQL and sqlite).

Personally I don't think Drupal can afford to work at lowest common denominator level if the aim is to be a serious option for the enterprise

Read more

Updating Drupal

For most of 2008 I've been working on a large Drupal installation (around 10 developers,  something like 100 custom modules, 17 languages, split over 2 sites).

This all adds up to a lot of updates, and schema changes.

Drupal's hook_install, and hook_update_N functions are pretty good, but not perfect at bringing order to this chaos.

Read more

MySQL restore is very slow

It seems there is a bug in MySQL which is causing restores to run very slowly (hours instead of minutes)

http://bugs.mysql.com/bug.php?id=33057

The bug is fixed in release 5.0.67

But Ubuntu hasn't updated yet....

Fortunately this bug is specific to the command line client. 

Workaround: use the MySQL Administrator GUI instead.

Read more

Adding comments to the database

Comments in code are widely used and generally (but not universally) accepted as a good thing.

Databases can also accommodate comments - but these seem to be very rarely used.

I'm of the opinion that database comments are very useful.

It's often hard to determine the exact meaning of a column - maybe it would be ideal to rename the column but you don't want to risk breaking code.

Read more

Project: Ramblers' Walks Finder

This project lists walks organised by Ramblers' groups nationwide and is kept updated by volunteers who plan, check and lead the walks

walks finder screenshot

The project aim was to make the website open to participation by hundreds of volunteers (without technical expertise) and to publicise activities to potential new members.

Read more