RoundCube: update the datatbase, doing each query line by line


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » Email » Webmail Clients » RoundCube

RoundCube: update the datatbase, doing each query line by lineLast Modified: Jul 10, 2017, 3:25 pm
Usually, you can update your RoundCube database by running:

cd /usr/local/directadmin/custombuild
./build update
./build roundcube

which lets RoundCube do it's own thing, deciding what update queries to run.

It's possible that things get out sync, and the update order is changed (possibly going backwards, or manually restoring an older DB, etc).
RoundCube stores it's current version in value:

da_roundcube.system.roundcube-version = XXXXX

I believe this is what it uses to determine which SQL update files to run from:

/var/www/html/roundcube/SQL/mysql/*.sql



If the update process fails, you might see something similar to:

Executing database schema update.
Updating database schema (2013011000)... [FAILED]

There could be a few possibilities, with a different possible solutions.

You're typically going to only want to do these as a last resort.
If you have backups, be sure they're safe and restore for a restore, as it's possible you might not be able to save the current data, if the tables have been corrupted (#1), but if it's juts a sync issues, then #2, #3 have a chance of saving the current data.

This is not an official guide from RoundCube, but has worked for us in some cases in the past.  #3 is the most thorough, often required, but very time consuming.

  1. Empty Database

    This scenario is simplest.  Grab the da_admin user/pass from

    /usr/local/directadmin/conf/mysql.conf

    and login to /phpMyAdmin.
    Check for the da_roundcube database.   If it has 0 tables, then it's empty, then the solution is to drop the whole database.
    If there are tables there, DO NOT DROP THE DATABASE, but instead click the "users" and "contacts" tables to confirm you can see those tables, and they have data in them.  If they do, skip to #2.  If you get table read errors, then you could attempt to repair them, but that's often unsuccessful.  If that's the case, then continue to DROP and re-install roundcube.
    To do that, go to your SQL tab, and run:

    DROP DATABASE da_roundcube

    If you have backups, then you might be able to restore it after you re-run the "./build roundcube" command to re-install RC.
  2. Version out of Sync

    This possibility means the version in the roundcube-version row (above) was wrong, so the update was done for the wrong version.
    For this case, you'd need to go backwards to the correct version file (if unsure, go back a bit further), and for each version .sql file, you'd run

    cd /var/www/html/roundcube/SQL/mysql
    mysql -uda_admin -pdaadminpass da_roundcube < 201XXXXXXX.sql

    going from the oldest to newest. Follow it up with a ./build roundcube.
  3. Version out of Sync with manual line-by-line queries required

    If the above failed, which is probable, you might need to run each *query* from those .sql files, one at a time.  This is very tedious, but is the last resort.   The reason is that if any query in an .sql file fails, future queries are not run.
      If the DB is out of sync enough, it's possible queries further in a given file are needed, even if earlier ones have already been run.

    To do this, similar to before, you'd figure out which .sql file to start with (I believe they're be date) and start oldest to newest, but instead of running the mysql command, in /phpMyAdmin as da_admin (or da_roundcube if you want to grab that from the roundcube/config/config.inc.php file), you'd go to the SQL tab in phpMyAdmin, and for each file, run each query from the start of the file to the end of the file, one at a time.   A query will be a line that start with ALTER,
    DROP, UPDATE, INSERT, CREATE, which might span multiple lines, but each query will end with a colon ; character.  Be sure to copy/paste the entire query, from the start of the line to the end of the query, including the colon.

    When running each query, you may hit errors if that task has already been run.  Don't worry, just keep running them, each query, each file (from where you decided to start from), until you're all the way to the end of the last file.

    After all queries are fully done, then be sure to finish up with a normal RC update

    cd /usr/local/directadmin/custombuild
    ./build update
    ./build roundcube

    to ensure the roaundcube-version gets correctly updated to the current script version.

    © 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST