Aug 5, 2024: iRedMail-1.7.1 has been released.

**pbf343** · 2015-07-27 18:04:17

pbf343
Member
Offline

Registered: 2013-05-30
Posts: 247

Topic: SQL variations

==== Required information ====
- iRedMail version (check /etc/iredmail-release):
- Linux/BSD distribution name and version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Web server (Apache or Nginx):
- Manage mail accounts with iRedAdmin-Pro?
- Related log if you're reporting an issue:
====

CentOS using mysql backend - variations of db post upgrades

--- sever_new/vmail.sql 2015-07-14 10:57:26.798108944 -0400
+++ server_production/vmail.sql 2015-07-14 11:16:34.000000000 -0400
@@ -26,13 +26,13 @@
`username` varchar(255) NOT NULL DEFAULT '',
`password` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
- `language` varchar(5) NOT NULL DEFAULT '',
+ `language` varchar(5) NOT NULL DEFAULT 'en_US',
`passwordlastchange` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `settings` text,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
+ `settings` text,
PRIMARY KEY (`username`),
KEY `passwordlastchange` (`passwordlastchange`),
KEY `expired` (`expired`),
@@ -49,21 +49,21 @@
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alias` (
`address` varchar(255) NOT NULL DEFAULT '',
- `goto` text,
+ `goto` text NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
- `moderators` text,
+ `moderators` text NOT NULL,
`accesspolicy` varchar(30) NOT NULL DEFAULT '',
`domain` varchar(255) NOT NULL DEFAULT '',
- `islist` tinyint(1) NOT NULL DEFAULT '0',
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
+ `islist` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`address`),
KEY `domain` (`domain`),
- KEY `islist` (`islist`),
KEY `expired` (`expired`),
- KEY `active` (`active`)
+ KEY `active` (`active`),
+ KEY `islist` (`islist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

@@ -112,14 +112,14 @@
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`username` varchar(255) NOT NULL DEFAULT '',
`domain` varchar(255) NOT NULL DEFAULT '',
- `maildir` varchar(255) NOT NULL DEFAULT '',
+ `maildir` text NOT NULL,
`admin` varchar(255) NOT NULL DEFAULT '',
KEY `id` (`id`),
KEY `timestamp` (`timestamp`),
KEY `username` (`username`),
KEY `domain` (`domain`),
KEY `admin` (`admin`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+) ENGINE=MyISAM AUTO_INCREMENT=499 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
@@ -131,19 +131,20 @@
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain` (
`domain` varchar(255) NOT NULL DEFAULT '',
- `description` text,
- `disclaimer` text,
+ `description` text NOT NULL,
+ `disclaimer` text NOT NULL,
`aliases` int(10) NOT NULL DEFAULT '0',
`mailboxes` int(10) NOT NULL DEFAULT '0',
`maxquota` bigint(20) NOT NULL DEFAULT '0',
`quota` bigint(20) NOT NULL DEFAULT '0',
`transport` varchar(255) NOT NULL DEFAULT 'dovecot',
`backupmx` tinyint(1) NOT NULL DEFAULT '0',
- `settings` text,
+ `defaultpasswordscheme` varchar(10) NOT NULL DEFAULT '',
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
+ `settings` text,
PRIMARY KEY (`domain`),
KEY `backupmx` (`backupmx`),
KEY `expired` (`expired`),
@@ -180,10 +181,10 @@
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mailbox` (
- `username` varchar(255) NOT NULL DEFAULT '',
+ `username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
- `language` varchar(5) NOT NULL DEFAULT '',
+ `language` varchar(5) NOT NULL DEFAULT 'en_US',
`storagebasedirectory` varchar(255) NOT NULL DEFAULT '/var/vmail',
`storagenode` varchar(255) NOT NULL DEFAULT 'vmail1',
`maildir` varchar(255) NOT NULL DEFAULT '',
@@ -210,25 +211,26 @@
`enableinternal` tinyint(1) NOT NULL DEFAULT '1',
`enabledoveadm` tinyint(1) NOT NULL DEFAULT '1',
`enablelib-storage` tinyint(1) NOT NULL DEFAULT '1',
- `enableindexer-worker` tinyint(1) NOT NULL DEFAULT '1',
- `enablelmtp` tinyint(1) NOT NULL DEFAULT '1',
- `enabledsync` tinyint(1) NOT NULL DEFAULT '1',
- `allow_nets` text,
`lastlogindate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastloginipv4` int(4) unsigned NOT NULL DEFAULT '0',
`lastloginprotocol` char(255) NOT NULL DEFAULT '',
- `disclaimer` text,
- `allowedsenders` text,
- `rejectedsenders` text,
- `allowedrecipients` text,
- `rejectedrecipients` text,
- `settings` text,
+ `disclaimer` text NOT NULL,
`passwordlastchange` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `allowedsenders` text NOT NULL,
+ `rejectedsenders` text NOT NULL,
+ `allowedrecipients` text NOT NULL,
+ `rejectedrecipients` text NOT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`expired` datetime NOT NULL DEFAULT '9999-12-31 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
`local_part` varchar(255) NOT NULL DEFAULT '',
+ `bytes` bigint(20) NOT NULL DEFAULT '0',
+ `messages` bigint(20) NOT NULL DEFAULT '0',
+ `enablelmtp` tinyint(1) NOT NULL DEFAULT '1',
+ `settings` text,
+ `enableindexer-worker` tinyint(1) NOT NULL DEFAULT '1',
+ `allow_nets` text,
PRIMARY KEY (`username`),
KEY `domain` (`domain`),
KEY `department` (`department`),
@@ -247,15 +249,14 @@
KEY `enablemanagesievesecured` (`enablemanagesievesecured`),
KEY `enablesieve` (`enablesieve`),
KEY `enablesievesecured` (`enablesievesecured`),
- KEY `enablelmtp` (`enablelmtp`),
KEY `enableinternal` (`enableinternal`),
KEY `enabledoveadm` (`enabledoveadm`),
KEY `enablelib-storage` (`enablelib-storage`),
- KEY `enableindexer-worker` (`enableindexer-worker`),
- KEY `enabledsync` (`enabledsync`),
KEY `passwordlastchange` (`passwordlastchange`),
KEY `expired` (`expired`),
- KEY `active` (`active`)
+ KEY `active` (`active`),
+ KEY `enablelmtp` (`enablelmtp`),
+ KEY `enableindexer-worker` (`enableindexer-worker`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

@@ -382,9 +383,9 @@
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
-/*!50003 SET character_set_client = latin1 */ ;
-/*!50003 SET character_set_results = latin1 */ ;
-/*!50003 SET collation_connection = latin1_swedish_ci */ ;
+/*!50003 SET character_set_client = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
@@ -407,4 +408,4 @@
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

--- Dump completed on 2015-07-14 10:57:26
+-- Dump completed on 2015-07-14 11:16:34

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

**ZhangHuangbin** · 2015-07-27 18:24:46

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,088

Re: SQL variations

There's no big differences. Please allow me to explain:

1) No difference, or let's say, it doesn't matter.

-  `language` varchar(5) NOT NULL DEFAULT '',
+  `language` varchar(5) NOT NULL DEFAULT 'en_US',

-  `goto` text,
+  `goto` text NOT NULL,

-  `moderators` text,
+  `moderators` text NOT NULL,

-  `maildir` varchar(255) NOT NULL DEFAULT '',
+  `maildir` text NOT NULL,

2) These 2 columns are deprecated, but we didn't mention to drop them in iRedMail upgrade tutorial for iRedMail-0.7.4 (which was released on Jan 9, 2012). My mistake. I updated tutorial moment ago: http://www.iredmail.org/docs/upgrade.ir … -sql-table

pbf343 wrote:

+ `bytes` bigint(20) NOT NULL DEFAULT '0',
+ `messages` bigint(20) NOT NULL DEFAULT '0',

You can fix it with below SQL commands:

sql> USE vmail;
sql> ALTER TABLE mailbox DROP COLUMN bytes;
sql> ALTER TABLE mailbox DROP COLUMN messages;

No other differences in your diff.

**pbf343** · 2015-07-27 18:40:04

pbf343
Member
Offline

Registered: 2013-05-30
Posts: 247

Re: SQL variations

Why is it not better to keep the databases matching new installs vs upgrades?

**ZhangHuangbin** · 2015-07-27 18:43:15

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,088

Re: SQL variations

pbf343 wrote:

Are you saying that there is nothing to worry about in doing a mysqldump of production databases and restore to newly installed machine, then point the MX records to the newly installed machine?

Yes.

pbf343 wrote:

In the upgrade steps here: http://www.iredmail.org/docs/iredmail.releases.html
Did you perform all the steps on the production system?

Yes.

pbf343 wrote:

Why is it not better to keep the databases matching new installs vs upgrades?

I apologize for not making iRedMail upgrade tutorials cover all these details, i will improve it in the future.

**pbf343** · 2015-07-27 18:46:07

pbf343
Member
Offline

Registered: 2013-05-30
Posts: 247

Re: SQL variations

Thank you.

Aug 5, 2024: iRedMail-1.7.1 has been released.

SQL variations

Posts: 5

1 Topic by pbf343 2015-07-27 18:04:17

Topic: SQL variations

2 Reply by ZhangHuangbin 2015-07-27 18:24:46

Re: SQL variations

3 Reply by pbf343 2015-07-27 18:40:04

Re: SQL variations

4 Reply by ZhangHuangbin 2015-07-27 18:43:15

Re: SQL variations

5 Reply by pbf343 2015-07-27 18:46:07

Re: SQL variations

Posts: 5