1 Topic by michael934

  • michael934
  • Member
  • Offline
  • Registered: 2018-09-11
  • Posts: 11

Topic: Unknown column 'expires'

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.9
- Deployed with iRedMail Easy or the downloadable installer? downloadable installer
- Linux/BSD distribution name and version: CentOS 7.6.1810
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): nginx
- Manage mail accounts with iRedAdmin-Pro? No
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====
I manually upgraded the server from 0.8.3 to 0.9.9. After executing 

/usr/bin/php /opt/www/roundcubemail/bin/gc.sh >/dev/null

root receives mail:

ERROR: [1054] Unknown column 'expires' in 'where clause' (SQL Query: DELETE FROM `cache` WHERE `expires` < now())
ERROR: [1054] Unknown column 'expires' in 'where clause' (SQL Query: DELETE FROM `cache_messages` WHERE `expires` < now())
ERROR: [1054] Unknown column 'expires' in 'where clause' (SQL Query: DELETE FROM `cache_index` WHERE `expires` < now())
ERROR: [1054] Unknown column 'expires' in 'where clause' (SQL Query: DELETE FROM `cache_thread` WHERE `expires` < now())

I have the following in the roundcubemail database:

MariaDB [roundcubemail]> SHOW TABLES;
+-------------------------+
| Tables_in_roundcubemail |
+-------------------------+
| cache                   |
| cache_index             |
| cache_messages          |
| cache_shared            |
| cache_thread            |
| contactgroupmembers     |
| contactgroups           |
| contacts                |
| dictionary              |
| forgot_password         |
| identities              |
| notes                   |
| searches                |
| session                 |
| system                  |
| users                   |
+-------------------------+

MariaDB [roundcubemail]> SHOW COLUMNS FROM cache;
+-----------+------------------+------+-----+---------------------+----------------+
| Field     | Type             | Null | Key | Default             | Extra          |
+-----------+------------------+------+-----+---------------------+----------------+
| cache_id  | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| cache_key | varchar(128)     | NO   |     | NULL                |                |
| created   | datetime         | NO   | MUL | 1000-01-01 00:00:00 |                |
| data      | longtext         | NO   |     | NULL                |                |
| user_id   | int(10) unsigned | NO   | MUL | NULL                |                |
+-----------+------------------+------+-----+---------------------+----------------+

MariaDB [roundcubemail]> SHOW COLUMNS FROM cache_messages;
+---------+------------------+------+-----+---------------------+-------+
| Field   | Type             | Null | Key | Default             | Extra |
+---------+------------------+------+-----+---------------------+-------+
| user_id | int(10) unsigned | NO   | PRI | NULL                |       |
| mailbox | varchar(255)     | NO   | PRI | NULL                |       |
| uid     | int(11) unsigned | NO   | PRI | 0                   |       |
| changed | datetime         | NO   | MUL | 1000-01-01 00:00:00 |       |
| data    | longtext         | NO   |     | NULL                |       |
| flags   | int(11)          | NO   |     | 0                   |       |
+---------+------------------+------+-----+---------------------+-------+

MariaDB [roundcubemail]> SHOW COLUMNS FROM cache_index;
+---------+------------------+------+-----+---------------------+-------+
| Field   | Type             | Null | Key | Default             | Extra |
+---------+------------------+------+-----+---------------------+-------+
| user_id | int(10) unsigned | NO   | PRI | NULL                |       |
| mailbox | varchar(255)     | NO   | PRI | NULL                |       |
| changed | datetime         | NO   | MUL | 1000-01-01 00:00:00 |       |
| valid   | tinyint(1)       | NO   |     | 0                   |       |
| data    | longtext         | NO   |     | NULL                |       |
+---------+------------------+------+-----+---------------------+-------+

MariaDB [roundcubemail]> SHOW COLUMNS FROM cache_thread;
+---------+------------------+------+-----+---------------------+-------+
| Field   | Type             | Null | Key | Default             | Extra |
+---------+------------------+------+-----+---------------------+-------+
| user_id | int(10) unsigned | NO   | PRI | NULL                |       |
| mailbox | varchar(255)     | NO   | PRI | NULL                |       |
| changed | datetime         | NO   | MUL | 1000-01-01 00:00:00 |       |
| data    | longtext         | NO   |     | NULL                |       |
+---------+------------------+------+-----+---------------------+-------+

I did not find 'expires' in the upgrade tutorials. How do I fix the 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,081

Re: Unknown column 'expires'

How did you upgrade Roundcube?
Please follow this tutorial, it will update SQL db automatically:
https://github.com/roundcube/roundcubem … stallation

3 Reply by elfueda (edited by elfueda 2022-02-17 08:44:22)

  • elfueda
  • Member
  • Offline
  • Registered: 2013-08-22
  • Posts: 10

Re: Unknown column 'expires'

use roundcubemail;

ALTER TABLE `cache` ADD `expires` datetime DEFAULT NULL;
ALTER TABLE `cache_shared` ADD `expires` datetime DEFAULT NULL;
ALTER TABLE `cache_index` ADD `expires` datetime DEFAULT NULL;
ALTER TABLE `cache_thread` ADD `expires` datetime DEFAULT NULL;
ALTER TABLE `cache_messages` ADD `expires` datetime DEFAULT NULL;

-- initialize expires column with created/changed date + 7days
UPDATE `cache` SET `expires` = `created` + interval 604800 second;
UPDATE `cache_shared` SET `expires` = `created` + interval 604800 second;
UPDATE `cache_index` SET `expires` = `changed` + interval 604800 second;
UPDATE `cache_thread` SET `expires` = `changed` + interval 604800 second;
UPDATE `cache_messages` SET `expires` = `changed` + interval 604800 second;

ALTER TABLE `cache` DROP INDEX `created_index`;
ALTER TABLE `cache_shared` DROP INDEX `created_index`;
ALTER TABLE `cache_index` DROP `changed`;
ALTER TABLE `cache_thread` DROP `changed`;
ALTER TABLE `cache_messages` DROP `changed`;

ALTER TABLE `cache` ADD INDEX `expires_index` (`expires`);
ALTER TABLE `cache_shared` ADD INDEX `expires_index` (`expires`);
ALTER TABLE `cache_index` ADD INDEX `expires_index` (`expires`);
ALTER TABLE `cache_thread` ADD INDEX `expires_index` (`expires`);
ALTER TABLE `cache_messages` ADD INDEX `expires_index` (`expires`);

4 Reply by elfueda (edited by elfueda 2024-02-17 03:03:14)

  • elfueda
  • Member
  • Offline
  • Registered: 2013-08-22
  • Posts: 10

Re: Unknown column 'expires'

And 2 years later, after many upgrades, same problem. And I get to use my own solution to fix the error! Brilliant!

5 Reply by ZhangHuangbin

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

Re: Unknown column 'expires'

Please follow this official tutorial to upgrade Roundcube, it will update SQL db automatically:
https://github.com/roundcube/roundcubem … stallation