Re: Database schema upgrade in Maia-dev releases

Here are scheme changes from 1.0.2c to 1.0.4.  I am sure someone could script this to update the old DB, thats past my skillset, but hey at least we know what the changes are.  I think probably the other ideas are better, dump and restore to the new scheme, but here are the actual changes.

 

+--------------------------------------------+

| Catalog Diff Report                        |

+--------------------------------------------+

Table `maia`.`awl` was modified

  columns:

  - added column signedby of type VARCHAR(255)

  - added column lastupdate of type TIMESTAMP

  - modified column email

  - modified column ip

  - modified column totscore

  __

  indices:

  - added index PRIMARY with columns: username, email, signedby, ip

  - removed index PRIMARY

  __

Table `maia`.`bayes_expire` was modified

  indices:

  - added index bayes_expire_idx1 with columns: id

  - removed index bayes_expire_idx1

  __

Table `maia`.`bayes_token` was modified

  columns:

  - modified column token

  __

Table `maia`.`maia_domains` was modified

  columns:

  - added column routing_domain of type VARCHAR(255)

  - added column transport of type VARCHAR(255)

  __

  indices:

  - added index maia_domains_idx_routing_domain with columns: routing_domain

  __

Table `maia`.`maia_mail` was modified

  columns:

  - added column autolearn_status of type VARCHAR(15)

  - modified column sender_email

  - modified column envelope_to

  - modified column subject

  - modified column contents

  __

  indices:

  - added index maia_mail_idx_sender_email with columns: sender_email

  - added index maia_mail_idx_subject with columns: subject

  - added index maia_mail_idx_score with columns: score

  __

Table `maia`.`maia_users` was modified

  columns:

  - modified column password

  __

  indices:

  - added index primary_email_idx with columns: primary_email_id

  __

Table `maia`.`mailaddr` was modified

  columns:

  - modified column email

  __

Table `maia`.`users` was modified

  columns:

  - modified column email

  __

----------------------------------------------

End of MySQL Workbench Report

 

 

 

From: Maia-users [mailto:[email protected]] On Behalf Of P.J. Tezza
Sent: Saturday, January 30, 2016 9:28 AM
To: [email protected]
Subject: Re: [Maia-users] Database schema upgrade in Maia-dev releases

 

Here is my suggestion:

1) Create an new, empty mysql schema using the script provided with maia
2) Dump the data from the tables you need
3) Restore your dumped data into your new mysql schema

You don't need to dump and restore all the tables but you'll have to look through the list of tables and figure out which ones you want to keep. Here is the list I came up with that includes all my domains, users and configurations (including user's white/black lists) but does not include ham, spam or any SpamAssassin data:                    

    maia_config maia_domain_admins maia_domains maia_users mailaddr policy users wblist

Because the maia schema includes default values for most of its columns, when you insert data into mysql, you don't have to include values for every column. This is handy for upgrades.
    
Here is the mysqldump command I used:

mysqldump -h dbserver -u dbuser -p -t -c maia maia_config maia_domain_admins maia_domains maia_users mailaddr policy users wblist > maia-data.sql

PJ 

On 1/29/16 8:17 PM, Ian R. Justman wrote:


Hi.

No worries; turns out the other database's schema was already up to date without my realizing it. Just dumped it and reloaded it and it "just worked(R)". Only my personal server needed a schema update. Hand-wrangling was easy enough there. I'm about to deploy the server tonight.

Thanks!

--Ian.

On January 29, 2016 6:28:32 PM PST, Vladimir  <mailto:[email protected]> <[email protected]> wrote: 

Ian,

Nobody answered you, but I will migrate one server from an old version and I have the same problem (I want to keep the user data I have). Did you manage to migrate the database successfully?? Any tips??

Thanks.

 

2016-01-10 5:20 GMT-02:00 Ian R. Justman <ianj-/[email protected]>:


Hi, all.

I hate Mailman's archiving function as there is no reasonably-usable search function.  However, nothing shows in my local archives, so I'll ask here.

It looks like the database schema has been evolving even in recent times. Now, the big question: How hard is it to migrate the databases from an older version of Maia-dev to a newer one? I have data on two servers I'd like to keep if possible. I'm migrating one right now, and while I'd love to get it done this weekend, it's not a priority as it only handles my mail and nobody else's. The other one, on the other hand, is a different matter entirely. I need to have that system working when I finish, preferably with the existing database data, albeit in an ALTERed (MySQL here) state.

Thanks for any help anyone can render!

--Ian.

-- 
Ian R. Justman
UNIX hacker.  Anime fan.  Any questions?
ianj (at) ian-justman.com
Please direct business correspondence to my new address, biz (at) ian-justman.com. (04/27/2015)
_______________________________________________
Maia-users mailing list
[email protected]
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users

 



  _____  

 
Maia-users mailing list
[email protected]
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users

-- Sent from my Android device with K-9 Mail. Please excuse my brevity. 

_______________________________________________
Maia-users mailing list
[email protected]
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users

_______________________________________________
Maia-users mailing list
[email protected]
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users