Upgrade from Zarafa 7.1 and Ubuntu 10.04

Pax

Hey people,
my first post in the “new” Kopano world.
As you can see from the topic, i’m doing a big upgrade. Small step for mankind but a big step for me.

My situation:
I have an old Ubuntu 10.04 running Zarafa 7.1 and Mysql 5.1 (shame on me)
Now I have installed a fresh 16.04 with Mysql 5.7 and Kopano.
I managed to import the old database via sql dump. (yeah 5.1 -> 5.7 not nice shame on me again). But first try and so far so good. Database made an upgrade after I started the kopano-server and Im able to login into the Webapp. Im happy that I got this far. And the road to go is still long.
Until this point there is one thing which bothers me. The kopano-server log is telling me things like:

Wed Sep  6 19:03:36 2017: [error  ] SQL [00000083] Failed: Field 'id' doesn't have a default value, Query Size: 171, Query: "REPLACE INTO changes(change_type, sourcekey, parentsourcekey, sourcesync, flags) VALUES (4100, '\▒9\▒▒L▒U\▒xf▒\n\▒\▒      \0\0\0\0', '▒▒▒&▒;Jp▒^j▒p▒F\r\0\0\0\0\0\0', 0, 0)"
Wed Sep  6 19:03:36 2017: [error  ] KDatabase::_Update() query failed: REPLACE INTO changes(change_type, sourcekey, parentsourcekey, sourcesync, flags) VALUES (4100, '\▒9\▒▒L▒U\▒xf▒\n\▒\▒    \0\0\0\0', '▒▒▒&▒;Jp▒^j▒p▒F\r\0\0\0\0\0\0', 0, 0): Field 'id' doesn't have a default value

Kopano is missing some default values for the id field in different mysql tables. Did I miss something with the mysql_dump?

mysqldump zarafa -u *** --password=*** --events --skip-opt --quick --single-transaction --verbose

Can I fix this by hand? Do I even need to?

AnotherAndy

HI Pax,
welcome here.
Have you done manually a mysql_Upgrade?
https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html
According to
https://documentation.kopano.io/kopanocore_administrator_manual/installing.html
only mysql 5.6 is tested.
I use Mariadb 10 and everything works fine :-)
Good Luck

fbartels

@merlin2104 said in Upgrade from Zarafa 7.1 and Ubuntu 10.04:

only mysql 5.6 is tested.

Actually 5.7 is the default with Ubuntu 16.04 and supported since Kopano Core 8.1. Only the manual did not reflect this change. I’ve update it now.

Pax

Hi Merlin,
yepp I’ve done a mysql_upgrade after the import finished. The mysql_upgrade finished within one minute and said everything was ok. After this I started kopano-server and kopano did some changes to the database too. And told me this interesting thing like ‘missing standard values’. I cant even guess what a standard value is supposed for an ID field. Besides standard-next-value.

Didnt had any time for testing today. Maybe I will give the import another try. And I will also try to copy the ibd files directly.

---------edit---------

Its exactly that. The the dump/ import is missing the extra values auto_increment:

original 5.1

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | smallint(11)     | NO   | PRI | NULL    | auto_increment |
| externid    | blob             | YES  | MUL | NULL    |                |
| objectclass | int(11) unsigned | NO   |     | NULL    |                |
| signature   | varbinary(255)   | NO   |     | 0       |                |
| company     | smallint(11)     | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+

import 5.7


+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| id          | smallint(11)     | NO   | PRI | NULL    |       |
| externid    | blob             | YES  | MUL | NULL    |       |
| objectclass | int(11) unsigned | NO   |     | NULL    |       |
| signature   | varbinary(255)   | NO   |     | 0       |       |
| company     | smallint(11)     | NO   |     | 0       |       |
+-------------+------------------+------+-----+---------+-------+

AnotherAndy

Hi Pax,
any news here? I just made my successful migration on my production server.
If you stil have this error I could try to downgrade my debian server from mariadb 10 to mysql and try the import.
best regards

dw2412

Regarding this dumping and importing stuff - did you check that the auto_increment columns being updated in the new database so they’re at least as high as the once in the original database and that they keep being auto_increment?

Actually regarding my upgrade from Zarafa 7.2 -> Kopano Core 8.3 - I did it the do-release-upgrade way. Works nice…

Andreas

Pax

It was related to the --skip-opt switch. It also skipps the --create-options which is responsible for the auto-increment things.
With:

mysqldump zarafa -u user --password=pass --events --skip-opt --create-options --disable-keys --set-charset --quick --single-transaction >

its working ass expected.
@dw2412 Jepp I checked the columns as you mentioned. They are looking good. Thanks for the hint. By the way I tried the do-release-upgrade several years ago and it broke so many things. So it wasnt/isnt an option. And Id also like to have a fresh new installation.

However the export of a ~50GB big database takes aprox 2 hours and the import 25 hours. So I’m planning with a downtime of 36 hours. I rather would do it the sfae way wit a dump, than copying the ibd files from an 5.1 Mysql to a 5.7.

Pax

Here we go.
Kopano 8.3.4 on a new fresh Ubuntu 16.04 is up and running. Got some problems upgrading the old ZarafaClient 7.1.13 up to 7.2.6 as 7.1.13 is not able to connect to Kopano-Server.
The new server got some faster harddrives and more RAM for the import of the Database. And with some tuning of MySQL i could reduce the time for the import of the 50g database from 25 hours down to 2 hours.

Another question which I would like to ask relating Microsofts Active Directory.
There is a new ADS Extension Tool. Now I have Zarafa settings and Kopano settings in my AD. How do I migrate the schema from Zarafa to Kopano? Some time ago I found a Whitepaper relating this, but I cant find it anymore.