1

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.

2

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.

3

Re: SQL variations

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

4

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.

5

Re: SQL variations

Thank you.