Speeding up the upgrade process from 2.2 to 3.1

Hey All,

I'm testing the upgrade path for my mailserver from 2.2.11 to the 3.1.17 
on a staging server and I'm a little confused. I run 2_2-3_0.mysql over 
my 160GB production email database. 
http://git.dbmail.eu/paul/dbmail/tree/sql/mysql/2_2-3_0.mysql

I'd like someone to confirm that 3_0_2-3_1_0.mysql at this time only 
re-creates a view that's already newly created in 2_2-3_0.mysql, and as 
such there is no need to run it.

I noticed 2_2-3_0.mysql is taking a rather long time on my 160GB 
database, and I'm looking to into it to see if there is a way to speed 
up the process.

My understanding of the script:

First the script drops, creates, calls and drops a stored procedure to 
iterate all foreign keys and drops them
then it removes all indexes on the dbmail_sievescripts table.

Then 'DELETE FROM dbmail_referencesfield' is called. (inside a 
transaction? why?) I'm assuming the author used DELETE FROM to make sure 
all cascading tables would follow, but as all foreign keys have been 
dropped by the stored procedure ran earlier, why not call 'TRUNCATE 
TABLE dbmail_referencesfield'?

Some tables are dropped, (dbmail_ccfield ... dbmail_tofield) 
dbmail_envelope is truncated. Some are dropped and (re?)created 
(dbmail_mimeparts...dbmail_header)

The rest of the file is about column changes, re-adding foreign keys and 
creating views for imap.

My comments on it:

One of the queries executed by the stored procedure is 'ALTER TABLE 
dbmail_messageblks DROP FOREIGN KEY dbmail_messageblks_ibfk_1'. This 
query copies all data in the dbmail_messageblks table, which is 140GB in 
my case, into a temporary table. This means on upgrading a dbmail 
install you should never have more than about 50% of the disk used on 
your mysql database server. I couldn't find this information anywhere. 
(I looked in wiki, mailinglist & UPGRADING text file) I think this 
should be documented! Also this takes a while. My server is 6GB in 
recreating dbmail_messageblk, which took about 2000 seconds. At this 
pace 140G will take > 12 hours.

I noticed dbmail_messageblks data is never written into the 
dbmail_mimeparts, table. My guess is that the current dbmail code can 
still handle dbmail_messageblks, and conversion is not required.

Why is this foreign key removed? My guess is that it the consequence of 
the column change for dbmail_physmessage.id, from a BIGINT(21) to 
BIGINT(20) UNSIGNED. To me this seems an arbitrary change. What happens 
if I keep dbmail_physmessage.id a BIGINT(21)? I'm sure you agree this 
change in signedness does not warrant a day of mailserver downtime if it 
can be avoided.


Greetings, Casper