1 Topic by jeremyjvogel (edited by jeremyjvogel 2017-11-16 01:24:14)

  • jeremyjvogel
  • Member
  • Offline
  • Registered: 2016-10-08
  • Posts: 18

Topic: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.7
- Linux/BSD distribution name and version:  centOS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?:  yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====
After running the roundcube upgrade script per the instructions here https://github.com/roundcube/roundcubem … i/Upgrade, I got this error:

done.
Installing JavaScript dependencies...PHP exec() function is required. Check disable_functions in php.ini
done.
Running update script at target...
Executing database schema update.
Error 500: Error in DDL upgrade 2016112200: [1060] Duplicate column name 'id'
Updating database schema (2016112200)... [FAILED]
All done.

So I removed exec from the php.ini file, restarted httpd and reran the upgrade script upgrade.sh.  Same error.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,163

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

Run MySQL command line directly, then source this SQL file (SQL/mysql/2016112200.sql under Roundcube directory) directly. e.g.

$ cd /path/to/roundcubemail-1.3.3/SQL/mysql/
$ mysql -uroot -p roundcubemail
mysql> SOURCE 2016112200.sql;

It's a bug of Roundcube sql file, PLEASE do report this issue to Roundcube team:
https://github.com/roundcube/roundcubemail/issues

3 Reply by jeremyjvogel

  • jeremyjvogel
  • Member
  • Offline
  • Registered: 2016-10-08
  • Posts: 18

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

From path /var/www/roundcubemail-1.3.3/SQL/mysql, got the same error.  

MariaDB [roundcubemail]> SOURCE 2016112200.sql;
ERROR 1060 (42S21) at line 1 in file: '2016112200.sql': Duplicate column name 'id'
Query OK, 0 rows affected (0.20 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.05 sec)

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,163

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

Safe to ignore.

5 Reply by jeremyjvogel (edited by jeremyjvogel 2017-11-14 22:54:06)

  • jeremyjvogel
  • Member
  • Offline
  • Registered: 2016-10-08
  • Posts: 18

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

Roundcube doesn't work.  I can login, but I can't see any email.  The folders are there, but no emails.  Clicking on the "about" link doesn't do anything either.  I can compose an email, but can't send it, but iRedMail is working fine for us with our email clients.

6 Reply by ThASattler

  • ThASattler
  • Member
  • Offline
  • Registered: 2016-10-15
  • Posts: 44

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

Our system was updated from 1.2.5 to 1.3.0 (and afterwards to 1.3.1 and 1.3.3). In version 1.3.1 there are two SQL-Scripts which are to be executed:
2016100900.sql
2016112200.sql

During update to 1.3.0 script 2016100900.sql was executed and the value of 'roundcube-version' in system TABLE was updated to '2016100900'.
Script 2016112200.sql failed, only the first line "ALTER TABLE dictionary ADD COLUMN id ..." was executed, the lines "DROP TABLE cache;" and "DROP TABLE cache_shared;" both failed.
Therefore the value of 'roundcube-version' in system TABLE was not updated, but remained '2016100900'.

When executing the next update from 1.3.0 to 1.3.1 the update process tried to execute 2016112200.sql because value of 'roundcube-version' was less than '2016112200'. But now the first line command to ADD COLUMN 'id' in 'dictionary' TABLE failed too.

So table 'dictionary' was altered during last update but the tables 'cache' and 'cache_shared' were not. Both tables contained still the COLUMN created (datetime NOT NULL DEFAULT '1000-01-01 00:00:00').

You have to apply 2016112200.sql to your database and to update the value of 'roundcube-version' in 'system' TABLE to '2016112200'.

7 Reply by jeremyjvogel (edited by jeremyjvogel 2017-11-16 00:57:02)

  • jeremyjvogel
  • Member
  • Offline
  • Registered: 2016-10-08
  • Posts: 18

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

So should I remove 

ADD COLUMN 'id'

from 2016112200.sql and run 

$ cd /path/to/roundcubemail-1.3.3/SQL/mysql/
$ mysql -uroot -p roundcubemail
mysql> SOURCE 2016112200.sql;

again?

I can't seem to find 'roundcube-version'.

8 Reply by jeremyjvogel (edited by jeremyjvogel 2017-11-16 01:19:26)

  • jeremyjvogel
  • Member
  • Offline
  • Registered: 2016-10-08
  • Posts: 18

Re: [SOLVED]Upgrading Roundcube From 1.3.0 to 1.3.3,Database Upgrade Fails

I posted the same issue in the Roundcube support forum and they suggested rerunning the install-jsdeps.sh script.  I had to comment out the entire disable_functions of the php.ini file, then my javascript dependencies were updated and everything seems to work (email is showing again).  I assume I am safe to ignore the error in database schema per ZhangHuangbin's suggestion. 

Roundcube forum found this bug:  https://github.com/roundcube/roundcubemail/issues/6034, which fixed the database schema issue.  You were right, the roundcube-version was 2016100900, after changing to 2016112200, and re-running update.sh, it completed without error. 

Thank you very much for all of your help ZhangHuangbin and ThASattler.