[prosody-dev] Important: MySQL changes in Prosody trunk

[Matthew Wild]

Hi folks,

If you do not use MySQL with Prosody trunk, you may stop reading now!

## The important part

If you upgrade to trunk build 993 or later, and have configured
Prosody to store its data in MySQL, Prosody will refuse to connect to
the database until you upgrade the schema.

This can be done simply by running:

prosodyctl mod_storage_sql upgrade

After running this command, restart Prosody and it should connect successfully.

## The details

Our recent work with persistence of pubsub and PEP data brought to
light a couple of bugs in our MySQL storage driver.

The first issue (#1073) was caused by Prosody setting an index on the
archive store (which is used for MAM and pubsub/PEP) to be 'unique'.
However MySQL by default only looks at the first 20 characters of the
data, and this caused issues for storage of e.g. OMEMO keys and
avatars, as they both store multiple items with the same prefix.

MySQL supports increasing the 20 bytes to a larger value, but not to
the full size of the column. Therefore we have removed the constraint
to avoid placing unnecessary restrictions on what data can be stored
by XMPP clients. Prosody already maintains uniqueness itself, and the
constraint was just an additional check.

The second issue (#1200) was simply our code emitting SQL that MySQL
and MariaDB do not support. This SQL was used to enforce a maximum
number of items in a store (e.g. in a pubsub/PEP node). We've replaced
it with an equivalent query that is supported by MySQL as well as the
other databases we support.

As part of fixing these issues we also added new tests that perform
and verify the same set of operations across all of our supported
storage drivers. This should help us catch and fix these kinds of
issues sooner and more easily.

That's all for now - are you really still reading? :)

Regards,
Matthew