Opened 16 years ago

Closed 16 years ago

#101 closed defect (fixed)

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

Reported by: aarons at tdtech dot net Owned by: rjl
Priority: normal Milestone:
Component: SQL scripts Version: 1.0.0 RC5
Severity: normal Keywords: sql script
Cc:

Description

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.

Change History (4)

comment:1 Changed 16 years ago by 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 https://secure.renaissoft.com/cgi-bin/trac.cgi/ticket/32 32 much easier by getting around MySQL's auto_increment weakness in that regard.

comment:2 Changed 16 years ago by dmorton

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

comment:3 Changed 16 years ago by 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.

comment:4 Changed 16 years ago by anonymous

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.