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;"