starred/ViMbAdmin-opensolutions
1
0
Fork 0
mirror of https://github.com/opensolutions/ViMbAdmin.git synced 2026-04-26 08:45:54 +03:00
Code Issues 16 Projects Releases 10 Packages Wiki Activity

[GH-ISSUE #91] 2.x to 3.x SQL migration #69

New issue
Closed
opened 2026-02-26 09:35:36 +03:00 by kerem · 3 comments
kerem commented 2026-02-26 09:35:36 +03:00
Owner
Copy link

Originally created by @dustinfarris on GitHub (Jun 10, 2014).
Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/91

I'd like to add this to the wiki, but I think someone else should look at it first. This worked for me.

  • Move old database:
mysqldump vimbadmin > vimbadmin.sql
mysqladmin create vimbold
mysql vimbold < vimbadmin.sql

DROP DATABASE vimbadmin;

INSERT INTO vimbadmin.domain (domain, description, max_aliases, alias_count, max_mailboxes, mailbox_count, max_quota, quota, transport, backupmx, active, created) SELECT domain, description, aliases, 0, mailboxes, 0, maxquota, 0, transport, backupmx, active, created FROM vimbold.domain;
  • Copy old mailboxes
INSERT INTO vimbadmin.mailbox (username, password, name, quota, local_part, active, access_restriction, homedir, maildir, uid, gid, created, Domain_id, delete_pending, alt_email) SELECT old.username, old.password, old.name, old.quota, old.local_part, old.active, old.access_restriction, old.homedir, old.maildir, old.uid, old.gid, old.created, vimbadmin.domain.id, 0, "" FROM vimbold.mailbox old JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;
  • Copy old aliases
INSERT INTO vimbadmin.alias (address, goto, active, created, Domain_id) SELECT old.address, old.goto, old.active, old.created, vimbadmin.domain.id FROM vimbold.alias old JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;
  • Copy admins
INSERT INTO vimbadmin.admin (username, password, super, active, created) SELECT username, password, super, active, created FROM vimbold.admin;
INSERT INTO vimbadmin.domain_admins (Admin_id, Domain_id) SELECT vimbadmin.admin.id, vimbadmin.domain.id FROM vimbold.domain_admin JOIN vimbadmin.admin ON vimbadmin.admin.username = vimbold.domain_admin.username JOIN vimbadmin.domain ON vimbadmin.domain.domain = vimbold.domain_admin.domain;
  • Update counts
USE vimbadmin;
UPDATE domain SET mailbox_count = (SELECT COUNT(*) FROM mailbox WHERE mailbox.Domain_Id=domain.id);
UPDATE domain SET alias_count = (SELECT COUNT(*) FROM alias WHERE alias.Domain_Id=domain.id);
  • Drop old database
DROP DATABASE vimbold;

I'm still kicking the tires and may have missed something critical; but this is working so far. Also, I had to reset the passwords for my admins. For whatever reason the copied passwords didn't seem to work even with the matching salts. Is a different encryption method being used now?

Note: if the mailbox copy complains about character encoding (as mine did) you can do this to align the collations (foreign keys have to be dropped first to do this):

ALTER TABLE vimbold.mailbox DROP FOREIGN KEY mailbox_domain_domain_domain;
ALTER TABLE vimbold.alias DROP FOREIGN KEY alias_domain_domain_domain;
ALTER TABLE vimbold.domain_admin DROP FOREIGN KEY domain_admin_domain_domain_domain;
ALTER TABLE vimbold.domain_admin DROP FOREIGN KEY domain_admin_username_admin_username;
ALTER TABLE vimbold.mailbox MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci;
ALTER TABLE vimbold.alias MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci;
ALTER TABLE vimbold.domain_admin MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci;
ALTER TABLE vimbold.domain_admin MODIFY COLUMN username varchar(255) COLLATE utf8_unicode_ci;
Originally created by @dustinfarris on GitHub (Jun 10, 2014). Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/91 I'd like to add this to the wiki, but I think someone else should look at it first. This worked for me. - Move old database: ``` console mysqldump vimbadmin > vimbadmin.sql mysqladmin create vimbold mysql vimbold < vimbadmin.sql ``` ``` sql DROP DATABASE vimbadmin; ``` - Create new database as [per instructions](https://github.com/opensolutions/ViMbAdmin/wiki/Installation#database-setup).. (including schema [via doctrine2-cli](https://github.com/opensolutions/ViMbAdmin/wiki/Installation#database-creation)) - Make sure application.ini salts match from v2.x - Copy old domains ``` sql INSERT INTO vimbadmin.domain (domain, description, max_aliases, alias_count, max_mailboxes, mailbox_count, max_quota, quota, transport, backupmx, active, created) SELECT domain, description, aliases, 0, mailboxes, 0, maxquota, 0, transport, backupmx, active, created FROM vimbold.domain; ``` - Copy old mailboxes ``` sql INSERT INTO vimbadmin.mailbox (username, password, name, quota, local_part, active, access_restriction, homedir, maildir, uid, gid, created, Domain_id, delete_pending, alt_email) SELECT old.username, old.password, old.name, old.quota, old.local_part, old.active, old.access_restriction, old.homedir, old.maildir, old.uid, old.gid, old.created, vimbadmin.domain.id, 0, "" FROM vimbold.mailbox old JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain; ``` - Copy old aliases ``` sql INSERT INTO vimbadmin.alias (address, goto, active, created, Domain_id) SELECT old.address, old.goto, old.active, old.created, vimbadmin.domain.id FROM vimbold.alias old JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain; ``` - Copy admins ``` sql INSERT INTO vimbadmin.admin (username, password, super, active, created) SELECT username, password, super, active, created FROM vimbold.admin; INSERT INTO vimbadmin.domain_admins (Admin_id, Domain_id) SELECT vimbadmin.admin.id, vimbadmin.domain.id FROM vimbold.domain_admin JOIN vimbadmin.admin ON vimbadmin.admin.username = vimbold.domain_admin.username JOIN vimbadmin.domain ON vimbadmin.domain.domain = vimbold.domain_admin.domain; ``` - Update counts ``` sql USE vimbadmin; UPDATE domain SET mailbox_count = (SELECT COUNT(*) FROM mailbox WHERE mailbox.Domain_Id=domain.id); UPDATE domain SET alias_count = (SELECT COUNT(*) FROM alias WHERE alias.Domain_Id=domain.id); ``` - Drop old database ``` sql DROP DATABASE vimbold; ``` I'm still kicking the tires and may have missed something critical; but this is working so far. Also, I had to reset the passwords for my admins. For whatever reason the copied passwords didn't seem to work even with the matching salts. Is a different encryption method being used now? --- Note: if the mailbox copy complains about character encoding (as mine did) you can do this to align the collations (foreign keys have to be dropped first to do this): ``` sql ALTER TABLE vimbold.mailbox DROP FOREIGN KEY mailbox_domain_domain_domain; ALTER TABLE vimbold.alias DROP FOREIGN KEY alias_domain_domain_domain; ALTER TABLE vimbold.domain_admin DROP FOREIGN KEY domain_admin_domain_domain_domain; ALTER TABLE vimbold.domain_admin DROP FOREIGN KEY domain_admin_username_admin_username; ALTER TABLE vimbold.mailbox MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci; ALTER TABLE vimbold.alias MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci; ALTER TABLE vimbold.domain_admin MODIFY COLUMN domain varchar(255) COLLATE utf8_unicode_ci; ALTER TABLE vimbold.domain_admin MODIFY COLUMN username varchar(255) COLLATE utf8_unicode_ci; ```
kerem closed this issue 2026-02-26 09:35:36 +03:00
kerem commented 2026-02-26 09:35:37 +03:00
Author
Owner
Copy link

@barryo commented on GitHub (Jun 10, 2014):

Thanks for this!

Put live at: https://github.com/opensolutions/ViMbAdmin/wiki/Migrate-from-ViMbAdmin2

<!-- gh-comment-id:45593620 --> @barryo commented on GitHub (Jun 10, 2014): Thanks for this! Put live at: https://github.com/opensolutions/ViMbAdmin/wiki/Migrate-from-ViMbAdmin2
kerem commented 2026-02-26 09:35:37 +03:00
Author
Owner
Copy link

@soalhn commented on GitHub (Sep 4, 2014):

There are 2 issues with this solution:

  • aliases will not be counted properly - because in case address == goto it should be not counted as alias - it's just an e-mail account; so the proper solution is (works for me perfectly):
    UPDATE domain SET alias_count = (SELECT COUNT(*) FROM alias WHERE alias.Domain_Id=domain.id AND address != goto);
  • missing solution for migration of data from table "log" (but it will not work as expected when there are usernames with IP address [old version of ViMbAdmin], e.g. "john.doe@example.com (8.8.8.8)"):
    INSERT INTO vimbadmin.log (action, data, timestamp, Admin_id, Domain_id) SELECT old.action, old.data, old.timestamp, vimbadmin.admin.id, vimbadmin.domain.id FROM vimbold.log old JOIN vimbadmin.admin ON vimbadmin.admin.username = old.username JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;
<!-- gh-comment-id:54438818 --> @soalhn commented on GitHub (Sep 4, 2014): There are 2 issues with this solution: - aliases will not be counted properly - because in case address == goto it should be not counted as alias - it's just an e-mail account; so the proper solution is (works for me perfectly): `UPDATE domain SET alias_count = (SELECT COUNT(*) FROM alias WHERE alias.Domain_Id=domain.id AND address != goto);` - missing solution for migration of data from table "log" (but it will not work as expected when there are usernames with IP address [old version of ViMbAdmin], e.g. "john.doe@example.com (8.8.8.8)"): `INSERT INTO vimbadmin.log (action, data, timestamp, Admin_id, Domain_id) SELECT old.action, old.data, old.timestamp, vimbadmin.admin.id, vimbadmin.domain.id FROM vimbold.log old JOIN vimbadmin.admin ON vimbadmin.admin.username = old.username JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;`
kerem commented 2026-02-26 09:35:37 +03:00
Author
Owner
Copy link

@barryo commented on GitHub (Sep 9, 2014):

Thanks @soalhn - wiki updated to reflect your comments.

<!-- gh-comment-id:54954782 --> @barryo commented on GitHub (Sep 9, 2014): Thanks @soalhn - wiki updated to reflect your comments.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
master
dependabot/composer/smarty/smarty-4.3.1
v2
3.4.1
3.4.0
3.3.0
3.2.1
3.2.0
3.1.1
3.1.0
3.0.15
3.0.14
3.0.13
3.0.12
3.0.11
2.2.4
3.0.10
3.0.9
3.0.8
3.0.7
3.0.6
3.0.5
3.0.4
3.0.3
3.0.2
3.0.1
3.0.0
2.2.3
2.2.2
2.2.1
2.2.0
2.1.0
2.0.8
2.0.7
2.0.6
2.0.5
2.0.4
2.0.3
2.0.2
2.0.1
2.0.0
0.3.4
0.3.3
0.3.2
0.3.1
0.3
Labels
Clear labels   
bug

   
feature

   
feature

   
improvement

   
improvement

   
pull-request

Mirrored from GitHub Pull Request

No labels
bug
feature
feature
improvement
improvement
pull-request
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/ViMbAdmin-opensolutions#69
No description provided.