Ddebyttere at 08:22, 31 May 2012

2012-05-31T08:22:50Z

← Older revision		Revision as of 08:22, 31 May 2012
Line 42:		Line 42:
	You can fix this by issue'ing the following queries (can be done online) in this order:		You can fix this by issue'ing the following queries (can be done online) in this order:
	<pre>		<pre>
-	alter table stores modify company ~~INT~~(11) ~~UNSIGNED~~;	+	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 stores modify user_id INT(11) UNSIGNED;~~	+	alter table users modify company int(11) unsigned NOT NULL default 0;
-		+	alter table users modify id int(11) unsigned NOT NULL auto_increment;
-	~~alter table stores modify id INT(11) UNSIGNED auto_increment;~~	+
-		+
-		+
-	alter table users modify company ~~INT~~(11) ~~UNSIGNED~~;	+
-		+
-	alter table users modify id ~~INT~~(11) ~~UNSIGNED~~ auto_increment;	+
	</pre>		</pre>

Msartor: Created page with "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 canno..."

2012-02-06T11:25:04Z

Created page with "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 canno..."

New page

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:
<pre>
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)
</pre>

<pre>
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)
</pre>

This will result in errors in the server.log, like:
<pre>
0x002aaac39fb6a0: SQL Failed: Duplicate entry '32767' for key 'PRIMARY', Query: "INSERT INTO users (externid, objectclass, signature) VALUES('1248', 196610, '20120202103220Z')"
</pre>

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:
<pre>
alter table stores modify company INT(11) UNSIGNED;

alter table stores modify user_id INT(11) UNSIGNED;

alter table stores modify id INT(11) UNSIGNED auto_increment;

alter table users modify company INT(11) UNSIGNED;

alter table users modify id INT(11) UNSIGNED auto_increment;
</pre>

Enabling more than 32768 entries in users/stores table - Revision history

Ddebyttere at 08:22, 31 May 2012

Msartor: Created page with "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 canno..."