Roundcube dbconfig error: system SET value='2018021600' WHERE name='roundcube-version'' at line 1

[halfgaar]

Upgrading Ubuntu 18.04 to 20.04 gives the following roundcube error:

mysql said: ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system SET value='2018021600' WHERE name='roundcube-version'' at line 1

This seems to come from 'usr/share/dbconfig-common/data/roundcube/upgrade/mysql/1.4.1-1' from 'roundcube-core_1.4.3+dfsg.1-1_all.deb'

System is a reserved keyword and needs to be surrounded with backticks. This applies to many of the migrations.

[Launchpad Janitor (janitor)]

This bug was fixed in the package roundcube - 1.6~rc+dfsg-2

---------------
roundcube (1.6~rc+dfsg-2) experimental; urgency=medium

  * Adjust d/origtargz-diff.sh for 1.6~rc+dfsg.
  * Refresh lintian overrides to accommodate lintian v2.115.
  * Bump Standards-Version to 4.6.1 (no changes needed).
  * Promote GuzzleHttp\Client back to "require" from "suggest".
  * Revert "Don't install the installer into /usr/share/roundcube."
  * Run upstream installer checks for apache2 and lighttpd DEP-8 tests.
  * Add roundcube-cleandb.{service,timer} which replaces the cronjob on
    systems where PID1 is systemd.
  * Add roundcube-gc.{service,timer} to purge expired sessions, caches and
    tempfiles in the background in a scheduled fashion.
  * Don't force set session.gc_probability=1 since we don't have to rely on
    probabilistic synchronous garbage collection anymore.
  * Remove obsolete /etc/default/roundcube and /etc/cron.daily/roundcube-core
    files since removing temporary files is part of the normal garbage
    collection routine.
  * DEP-8: Create tempfiles in $AUTOPKGTEST_TMP not /tmp.
  * DEP-8: Test roundcube-{cleandb,gc}.service (cleanup and garbage collection
    routines).

 -- Guilhem Moulin <email address hidden> Wed, 29 Jun 2022 20:23:02 +0200

[halfgaar (wiebe-halfgaar)]

Version 1.6~rc+dfsg-2 is not in Ubuntu 20.04 or 22.04. Will the upgrade paths remain broken?

[halfgaar (wiebe-halfgaar)]

The status says 'fix released', but how is that a fix?

The upgrade from Ubuntu 18.04 (Roundcube 1.3.6+dfsg.1-1) to Ubuntu 20.04 (Roundcube 1.4.3+dfsg.1-1) is broken because of invalid SQL.

[halfgaar (wiebe-halfgaar)]

Upstream having released a fix doesn't actually help until the migration scripts in 'usr/share/dbconfig-common/data/roundcube/upgrade/mysql' inside the deb are fixed. Here's a workaround.

I performed an upgrade to Ubuntu 22.04, for which there are two migration scripts. They will fail half way, at which point you need to say 'ignore' and run them by hand:

* upgrade to Ubuntu 20.04
* say 'ignore' on the dbconfig error
* Finish the upgrade
* run 'mysql roundcube < 1.4.1-1__starting_from_first_failed_statement.sql'
* Upgrade to Ubuntu 22.04
* say 'ignore' on the dbconfig error
* Finish the upgrade
* run 'mysql roundcube < 1.5~beta-1__starting_from_first_failed_statement.sql'

On both my test virtual machines and real server the upgrade from MySQL 5.7 to 8.0.33 actually failed for various reason (invalid config file and redo log corruption), but that's outside the scope of this bug report.

The question then is for the next upgrade, whether the .deb will contain migration scripts that properly escape the 'system' keyword.