Enabling more than 32768 entries in users/stores table

From Zarafa wiki

Latest revision as of 08:22, 31 May 2012

When you have ever ran an older 6.40 version of ZCP, it could be that some of the database field types, in the users and stores tables, are smallints, which means that they cannot hold more than 32768 entries:

mysql> describe users;
+-------------+----------------+------+-----+---------+----------------+
| Field       | Type           | Null | Key | Default | Extra          |
+-------------+----------------+------+-----+---------+----------------+
| id          | smallint(11)   | NO   | PRI | NULL    | auto_increment |
| externid    | blob           | YES  | MUL | NULL    |                |
| objectclass | int(11)        | NO   |     | 0       |                |
| signature   | varbinary(255) | NO   |     | 0       |                |
| company     | smallint(11)   | NO   |     | 0       |                |
+-------------+----------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe stores;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | smallint(6) unsigned  | NO   | UNI | NULL    | auto_increment |
| hierarchy_id | int(11) unsigned      | NO   | PRI | 0       |                |
| user_id      | smallint(6) unsigned  | NO   | PRI | 0       |                |
| user_name    | varchar(255)          | NO   |     |         |                |
| company      | smallint(11) unsigned | NO   |     | 0       |                |
| guid         | blob                  | NO   |     | NULL    |                |
| type         | smallint(6) unsigned  | NO   | PRI | 0       |                |
+--------------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

This will result in errors in the server.log, like:

0x002aaac39fb6a0: SQL Failed: Duplicate entry '32767' for key 'PRIMARY', Query: "INSERT INTO users (externid, objectclass, signature) VALUES('1248', 196610, '20120202103220Z')"

In NEW installations this is fixed. But if you have ever used an older 6.40 version it could be that these fields need to be modified. First check with the above queries if it you really need to modify your tables.

You can fix this by issue'ing the following queries (can be done online) in this order:

alter table stores modify company int(11) unsigned NOT NULL default 0;
alter table stores modify user_id int(11) unsigned NOT NULL default 0;
alter table stores modify id int(11) unsigned NOT NULL auto_increment;

alter table users modify company int(11) unsigned NOT NULL default 0;
alter table users modify id int(11) unsigned NOT NULL auto_increment;