maia-mysql.sql does not work with MySql 4.1.xx

[aarons at tdtech dot net]

The table definitions in maia-mysql.sql contain default values in columns that use auto_increment. As of MySQL 4.1, this is no longer allowed. The default values must be removed from these columns to use the maia-mysql.sql file.

See http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html in the notes about auto_increment for more information.

[rjl]

I've been toying with the idea of removing all of the auto_increment columns altogether, and replacing them with some other uniquely-indexable key. This would make failover and replication scenarios [32] much easier by getting around MySQL's auto_increment weakness in that regard.

[dmorton]

Something like combining a timestamp, server id, and process id into one?

[rjl]

It doesn't need to be that complicated in most cases. In some tables we already have a suitable unique key in another column--e.g. the users table needs the email column to be unique anyway. Likewise the policy table just needs a unique handle that can be referenced by the users table, so the policy_name column could simply store the e-mail address (i.e. users.email). References to users.policy_id would become users.policy_name, etc.

For reference tables, there usually aren't auto_increment keys anyway, but where there are they can be replaced with a multi-column primary key definition. In any case, we should probably be defining proper FOREIGN KEY relationships to improve referential integrity.

For maia_mail, we may be able to use the Message-ID to guarantee uniqueness. I'm not sure I'd trust this, though, given the potential for forgery (e.g. a broken spamware tool might use the same bogus Message-ID for some or all of the crap it sends out).

As a last resort we can, as you say, use a hash of the timestamp, server ID, and process ID. Store this as a char(32) MD5 hash, for instance.

[anonymous]

This issue was fixed in Changeset [488].