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:maia-users-***@renaissoft.com] On Behalf Of P.J. Tezza
Sent: Saturday, January 30, 2016 9:28 AM
To: maia-***@renaissoft.com
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:***@gmail.com> <***@gmail.com> 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 <***@ian-justman.com>:
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
Maia-***@renaissoft.com
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users
_____
Maia-users mailing list
Maia-***@renaissoft.com
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users
-- Sent from my Android device with K-9 Mail. Please excuse my brevity.