Upgraded database structure differs from new created one.

I am using zarafa from nearly day one (somewhere around 2006 i think) :-)
I upgrade the database according to the documentations. Also the utf8 switch worked long time.
Now I want to switch from zarafa 7.1 to kopano. I use a 8.4.4 testinstallation I created some time ago.

I restored my old database which was upgraded to 8.4.4 and compared the DB schema of this with a clean kopano created schema.

I see some strange differences. Can someone explain this and advice what to do? In general the testinstallation works.
I have some charset issues in the webapp with german “umlaute” so I am searching for the reason :-) Still I dont think the db
is the reason.

Issue 1:
Upgrade:

CREATE TABLE `indexedproperties` (
  `hierarchyid` int(11) unsigned NOT NULL DEFAULT '0',
  `tag` int(11) NOT NULL DEFAULT '0',
  `val_binary` varbinary(255) DEFAULT NULL,
  PRIMARY KEY (`hierarchyid`,`tag`),
  UNIQUE KEY `bin` (`tag`,`val_binary`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

NEW:

CREATE TABLE `indexedproperties` (
  `hierarchyid` int(11) unsigned NOT NULL DEFAULT '0',
  `tag` smallint(6) unsigned NOT NULL DEFAULT '0',
  `val_binary` varbinary(255) DEFAULT NULL,
  PRIMARY KEY (`hierarchyid`,`tag`),
  UNIQUE KEY `bin` (`tag`,`val_binary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Issue 1.1 :
In almost all old tables I have “DEFAULT CHARSET=latin1” but this should not be an issue because related string columns like “mvproperties.val_string” are defined as:

updated:  `val_string` longtext CHARACTER SET utf8,
new:      `val_string` longtext,

Issue 1.2 :
In the table indexedproperties you see :

updated: `tag` int(11) NOT NULL DEFAULT '0',
new:     `tag` smallint(6) unsigned NOT NULL DEFAULT '0',

Is this an issue or can this and similar items be ignored

Issue 2:
my updated table “abchanges” has a column

`moved_from` varbinary(255) DEFAULT NULL,
with a key:
KEY `moved` (`moved_from`)

strange. locking at zarafa 7.2.6.10 source code I see that this was added only to the changes table at:
// 23
ECRESULT UpdateDatabaseKeysChanges …
and somehow remove here:
// 57
ECRESULT UpdateDatabaseConvertChanges
–> can I simply drop the column and the key?

Issue 3:
my updated lob table has:

updated:  `id` int(11) NOT NULL AUTO_INCREMENT,   
         PRIMARY KEY (`id`),
         UNIQUE KEY `hct` (`instanceid`,`chunkid`,`tag`)
new:  The new one just have a  PRIMARY KEY (`instanceid`,`tag`,`chunkid`)

Issue 4:
Table “object”

updated: `objectclass` int(11) unsigned NOT NULL,
new:     `objectclass` int(11) unsigned NOT NULL DEFAULT '0',

–> extra default?

Issue 5:
Table “objectmvproperty” and “objectproperty”

updated: `propname` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
new:     `propname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

–> extra COLLATE setting?

Issue 6:
several tables like: receivefolder or outgoingqueue
several table columns are unsigned like

updated: `store_id` int(11) NOT NULL DEFAULT '0',
new:     `store_id` int(11) unsigned NOT NULL DEFAULT '0',

Issue 7:
the “settings” table seams not updated during uft8 conversion ( // 46 ECRESULT UpdateDatabaseConvertToUnicode)

updated: `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
new:     `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

As a software developer I can try to write migration scripts myself, if I know what I need to change. :-)
Maybe someone can give some advice. Should not be an isolated issue because other client maybe also have followed the same upgrade path…

Thx!!
Markus

Seams to be to special :-). Anyway…

I checked each table and wrote a script to change my structure so it equals almost a new created kopano db ( except 2 table columns )
Just to complete this post here is the script.
The db seams to be intact. Everything is still working as expected:


mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE abchanges DROP COLUMN moved_from, DROP key moved;"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE abchanges CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE acl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table acl modify hierarchy_id int(11) unsigned NOT NULL DEFAULT '0';" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table acl modify type tinyint(4) unsigned NOT NULL DEFAULT '0';" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table acl modify rights int(11) unsigned NOT NULL DEFAULT '0';" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE changes CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE deferredupdate CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE hierarchy CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE indexedproperties CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table indexedproperties modify tag smallint(6) unsigned NOT NULL DEFAULT '0';"

# lob table not used. so drop recreate
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; select count(*) from lob;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; DROP TABLE IF EXISTS lob; CREATE TABLE lob ( instanceid int(11) unsigned NOT NULL, chunkid smallint(6) unsigned NOT NULL, tag smallint(6) unsigned NOT NULL, val_binary longblob,  PRIMARY KEY (instanceid,tag,chunkid)) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=1750;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE mvproperties CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table mvproperties modify orderid smallint(6) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table mvproperties modify tag smallint(6) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table mvproperties modify type smallint(6) unsigned NOT NULL DEFAULT '0';"

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; SHOW CREATE TABLE names;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE names CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table names modify namestring varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;"

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table object modify objectclass int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE object CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE objectmvproperty CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table objectmvproperty modify propname varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;"

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE objectproperty CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table objectproperty modify propname varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;"

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE objectrelation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; select store_id,hierarchy_id,flags from outgoingqueue;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table outgoingqueue modify store_id int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table outgoingqueue modify hierarchy_id int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table outgoingqueue modify flags tinyint(4) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE outgoingqueue CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 
  
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table receivefolder modify id int(11) unsigned NOT NULL AUTO_INCREMENT;"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table receivefolder modify storeid int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table receivefolder modify objid int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE receivefolder CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE searchresults CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 


mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE singleinstances CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

# stores
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; SHOW CREATE TABLE stores;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; select id,hierarchy_id,user_id,user_name,company,type from stores;" 
# update system user name . Was "2" within my old one
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; update stores set user_name='SYSTEM' where id = 1;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table stores modify id int(11) unsigned NOT NULL AUTO_INCREMENT;"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table stores modify hierarchy_id int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table stores modify user_id int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table stores modify company int(11) unsigned NOT NULL DEFAULT '0';"
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE stores CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE syncedmessages CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE syncs CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; DROP TABLE IF EXISTS usergroup_acl;" 
#change users
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table users modify id int(11) unsigned NOT NULL AUTO_INCREMENT;" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table users modify objectclass int(11) NOT NULL DEFAULT '0';" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; alter table users modify company int(11) unsigned NOT NULL DEFAULT '0';" 
mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" 

mysql -u root --password=${ML_MYSQL_PASSWORD} -e "use kopano; ALTER TABLE versions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"