Problem with upgrade to latest version (database issues -- solved)

RSMemphis

Hello everyone,

This is the first time I signed up to this forum, I have always been able to solve my problems with Zarafa and Kopano in the past myself. But this time, I am really stuck.

I was previously on version 8.4.90 and wanted to upgrade to 8.7.80…
I did make a database dump (which may help still).

Core Server did not start at first:

Sun Nov 25 08:19:32 2018: [crit   ] Unable to open pidfile '/var/run/kopano/server.pid'
Sun Nov 25 08:19:33 2018: [error  ] SQL [00000034] Failed: Specified key was too long; max key length is 767 bytes, Query Size: 170, Query: "ALTER TABLE `names` ADD UN$
Sun Nov 25 08:19:33 2018: [error  ] KDatabase::I_Update() query failed: "Specified key was too long; max key length is 767 bytes", query: ALTER TABLE `names` ADD UNIQU$
Sun Nov 25 08:19:33 2018: [error  ] K-1216: Cannot update to schema v69, because the "names" table contains unexpected rows. Certain prior versions of the server erron$
Sun Nov 25 08:19:33 2018: [error  ] K-1220: To fix the excess rows, use `kopano-dbadm k-1216`. Consult the manpage and preferably make a backup first.
Sun Nov 25 08:19:33 2018: [error  ] K-1221: Alternatively, the server may be started with --ignore-da to forego the schema update.
Sun Nov 25 08:19:33 2018: [warning] WARNING: Unable to upgrade database from version 8.4.90.2153381888.66 to 8.7.80.0.115
Sun Nov 25 08:19:33 2018: [warning]    You can force the server to start with --ignore-database-version-conflict
Sun Nov 25 08:19:33 2018: [warning]    Warning, you can lose data! If you don't know what you're doing, you shouldn't be using this option!
Sun Nov 25 08:19:33 2018: [=======] Server shutdown complete.

running “kopano-dbadm k-1216” actually did not resolve anything, I figured the problem was with the queries.

So, I run:

set global innodb_large_prefix = `ON`;
set global innodb_file_format = `BARRACUDA`;

ALTER TABLE abchanges ROW_FORMAT=DYNAMIC;

The last command was repeated for all rows.
Now, kopano server started mostly without errors, but now the queries are failing, because of union making in terms of size. I assume this is because somehow the dynamic row format did not change all of the index rows the same ways, but I am totally lost now.
Here are the new error messages where I am stuck:

Sun Nov 25 09:39:33 2018: [crit   ] Unable to open pidfile '/var/run/kopano/server.pid'
Sun Nov 25 09:39:59 2018: [error  ] SQL [00000058] Failed: Illegal mix of collations for operation 'UNION', Query Size: 1527, Query: "SELECT 0,properties.tag,properties.type,properties.val_ulong, LEFT(properties.val_string,255),LEFT(properties.val_binary,511),properties.val_double,properties.val_longint,properties.val_hi,properties.val_lo FROM properties WHERE hierarchyid=112032 AND properties.tag IN (23,26,41,48,54,55,55,57,59,63,64,65,66,67,68,81,82,99,100,101,117,118,119,120,3095,3097,3098,3101,3102,3103,3586,3587,3588,3591,3592,3611,4224,4225,4226,4240,4241,4245,4246,12295,12296,16355,16367,26255,32769,32803,32855,32884,32885,33186,33187,33204,33209,33232,33280,33390,34050) UNION SELECT count(*),mvproperties.tag,mvproperties.type,group_concat(length(mvproperties.val_ulong),':', mvproperties.val_ulong ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_string),':', mvproperties.val_string ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_binary),':', mvproperties.val_binary ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_double),':', mvproperties.val_double ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_longint),':', mvproperties.val_longint ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_hi),':', mvproperties.val_hi ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_lo),':', mvproperties.val_lo ORDER BY mvproperties.orderid SEPARATOR '') FROM mvproperties WHERE hierarchyid=112032 AND mvproperties.tag IN (34051)  GROUP BY hierarchyid, tag"
Sun Nov 25 09:39:59 2018: [error  ] KDatabase::DoSelect(): query failed: "Illegal mix of collations for operation 'UNION'", query: SELECT 0,properties.tag,properties.type,properties.val_ulong, LEFT(properties.val_string,255),LEFT(properties.val_binary,511),properties.val_double,properties.val_longint,properties.val_hi,properties.val_lo FROM properties WHERE hierarchyid=112032 AND properties.tag IN (23,26,41,48,54,55,55,57,59,63,64,65,66,67,68,81,82,99,100,101,117,118,119,120,3095,3097,3098,3101,3102,3103,3586,3587,3588,3591,3592,3611,4224,4225,4226,4240,4241,4245,4246,12295,12296,16355,16367,26255,32769,32803,32855,32884,32885,33186,33187,33204,33209,33232,33280,33390,34050) UNION SELECT count(*),mvproperties.tag,mvproperties.type,group_concat(length(mvproperties.val_ulong),':', mvproperties.val_ulong ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_string),':', mvproperties.val_string ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_binary),':', mvproperties.val_binary ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_double),':', mvproperties.val_double ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_longint),':', mvproperties.val_longint ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_hi),':', mvproperties.val_hi ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_lo),':', mvproperties.val_lo ORDER BY mvproperties.orderid SEPARATOR '') FROM mvproperties WHERE hierarchyid=112032 AND mvproperties.tag IN (34051)  GROUP BY hierarchyid, tag
Sun Nov 25 09:48:39 2018: [error  ]

Now, I can downgrade and reinstall my old database, but I would rather fix the issue instead. I hope someone can help me!

Thanks,

Raoul

jengelh

Looks like the name column in the “names” table has some rather long keys. Didn’t see that coming. That of course would require some fixing before going to schema 69 (or one moves to Barracuda as you did).
The second part seems to be about mismatching collations between “properties” and “mvproperties”, which… I find there’s a missing ALTER statement in the update procedure. So much for now.

jengelh

The namestring column in utf8mb3 is 255 chars, or 765 bytes. Plus the 16 bytes from guid makes 781. In MariaDB 10.2 and the Antelope+compact format, it is possible to have an index key with size 781 so long as the column is <=767. It might be such that older versions had more limits, which one are you using?

RSMemphis

@jengelh said in Problem with upgrade to latest version:

Looks like the name column in the “names” table has some rather long keys. Didn’t see that coming. That of course would require some fixing before going to schema 69 (or one moves to Barracuda as you did).
The second part seems to be about mismatching collations between “properties” and “mvproperties”, which… I find there’s a missing ALTER statement in the update procedure. So much for now.

Yes, you were absolutely right. I had finally figured it out myself. One had the general, one had the unicode setting. It is important to set it to the same (I did it inverse at first, and it did not work).

The correct command is:

alter table mvproperties modify val_string longtext collate utf8mb4_general_ci;

EDIT: The final question would be why one of the properties had utf8mb4_unicode_ci and the other one general_ci, and why it has to be general and not unicode. But I am already happy it is working again. Everything up to that mismatch was easy to find with online.

RSMemphis

@jengelh said in Problem with upgrade to latest version:

The namestring column in utf8mb3 is 255 chars, or 765 bytes. Plus the 16 bytes from guid makes 781. In MariaDB 10.2 and the Antelope+compact format, it is possible to have an index key with size 781 so long as the column is <=767. It might be such that older versions had more limits, which one are you using?

Yes, an upgrade to 10.2 would have likely helped, but Ubuntu 16.04 is really stubborn, and it did not like me adding the MariaDB server directly. I ended up just migrating everything manually, it oddly ended up being less painless than to convince Ubuntu to allow MariaDB 10.2…

Problems I never had with gentoo.

jengelh

Modifications to kopano-dbadm are underway to automate this.

RSMemphis

@jengelh Cool, thanks.

jengelh

@RSMemphis said in Problem with upgrade to latest version (database issues -- solved):

@jengelh said in Problem with upgrade to latest version:

The namestring column in utf8mb3 is 255 chars, or 765 bytes. Plus the 16 bytes from guid makes 781. In MariaDB 10.2 and the Antelope+compact format, it is possible to have an index key with size 781 so long as the column is <=767. It might be such that older versions had more limits, which one are you using?

Yes, an upgrade to 10.2 would have likely helped, but Ubuntu 16.04 is really stubborn

Still, the question was “what version did you have”, not “how do you feel about an upgrade” ;-)