Navigation

    Kopano
    • Register
    • Login
    • Search
    • Categories
    • Get Official Kopano Support
    • Recent
    Statement regarding the closure of the Kopano community forum and the end of the community edition

    Upgraded database structure differs from new created one.

    Kopano Groupware Core
    2
    3
    652
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • mclu
      mclu last edited by

      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

      mclu 1 Reply Last reply Reply Quote 0
      • mclu
        mclu @mclu last edited by mclu

        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;"
        1 Reply Last reply Reply Quote 1
        • A Former User
          A Former User last edited by

          created as https://jira.kopano.io/browse/KC-1046

          1 Reply Last reply Reply Quote 0
          • First post
            Last post