[Opensim-users] Problems creating MySQL tables for XmlRpcGroupsServer

[Kurt Reisler]

Unfortunately, that does not solve the problem.

I created the database using the latin1 character set:

    mysql> create database `mosgroups` character set latin1;
    Query OK, 1 row affected (0.01 sec)

    mysql> use mosgroups;
    Database changed

When I then attempted to create the table using the original definition, 
I got the same error:

    mysql> CREATE TABLE `osgroupinvite` (
         ->   `InviteID` varchar(128) NOT NULL default '',
         ->   `GroupID` varchar(128) NOT NULL default '',
         ->   `RoleID` varchar(128) NOT NULL default '',
         ->   `AgentID` varchar(128) NOT NULL default '',
         ->   `TMStamp` timestamp(14) NOT NULL,
         ->   PRIMARY KEY  (`InviteID`),
         ->   UNIQUE KEY `GroupID` (`GroupID`,`RoleID`,`AgentID`)
         -> ) ENGINE=MyISAM;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near '(14) NOT NULL,
       PRIMARY KEY  (`InviteID`),
       UNIQUE KEY `GroupID` (`GroupID`,`R' at line 6

If I tried specifying the latin1 character set in the first set of 
directives, I get the same error, but pointing to a different line:

    mysql> CREATE TABLE `osgroupinvite` CHARACTER SET latin1 (
         ->   `InviteID` varchar(128) NOT NULL default '',
         ->   `GroupID` varchar(128) NOT NULL default '',
         ->   `RoleID` varchar(128) NOT NULL default '',
         ->   `AgentID` varchar(128) NOT NULL default '',
         ->   `TMStamp` timestamp(14) NOT NULL,
         ->   PRIMARY KEY  (`InviteID`),
         ->   UNIQUE KEY `GroupID` (`GroupID`,`RoleID`,`AgentID`)
         -> ) ENGINE=MyISAM CHARACTER SET latin1;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near '`InviteID` varchar(128) NOT NULL default '',
       `GroupID` varchar(128) NOT NULL d' at line 2

These errors occur even if I do not specify the character set when 
creating the database.

Any other ideas or suggestions?

Thanks
Kurt

On 10/21/2011 1:34 AM, Volker Gaessler wrote:
> Hello Kurt, I had this same problem this week.
>
> It is a character set problem. Your database has been created with utf8
> character set. This charset uses 3 bytes per character. With 128 character
> keys you get a problem in the combined key consisting in the three IDs
> because MySQL only supports 1000 bytes max for keys.
>
> The solution is to create the database with character set latin1 or each of
> the groups tables. latin1 uses only 1 byte per character:
>
> CREATE DATABASE `opensim` CHARACTER SET latin1;
>
> or
>
> CREATE TABLE `osgroupinvite` CHARACTER SET latin1 (
>     :
> ) ENGINE=MyISAM CHARACTER SET latin1;
>
> and so on for each table.
>
> Cheers
>
> Volker
>
> -----Original Message-----
> From: opensim-users-bounces at lists.berlios.de
> [mailto:opensim-users-bounces at lists.berlios.de] On Behalf Of Kurt Reisler
> Sent: Donnerstag, 20. Oktober 2011 17:43
> To: opensim-users at lists.berlios.de
> Subject: [Opensim-users] Problems creating MySQL tables for
> XmlRpcGroupsServer
>
> Using Server version: 5.5.16 MySQL Community Server (GPL) under Windows
> 2008 server
>
> Attempting to build XmlRpcGroupsServer from mcortez-flotsam-963d99e.zip
> dated 10/18/2010 on my local server.
>
> Seems that there have been changes made to MySQL.  Had to change "TYPE"
> to "ENGINE" in all the other table build command strings.  All of them
> worked except when trying to create the osgroupinvite table:
>
> mysql>  CREATE TABLE `osgroupinvite` (
>       ->    `InviteID` varchar(128) NOT NULL default '',
>       ->    `GroupID` varchar(128) NOT NULL default '',
>       ->    `RoleID` varchar(128) NOT NULL default '',
>       ->    `AgentID` varchar(128) NOT NULL default '',
>       ->    `TMStamp` timestamp(14) NOT NULL,
>       ->    PRIMARY KEY  (`InviteID`),
>       ->    UNIQUE KEY `GroupID` (`GroupID`,`RoleID`,`AgentID`)
>       ->  ) ENGINE=MyISAM;
>
> Which throws the following error:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '(14) NOT NULL,
>     PRIMARY KEY  (`InviteID`),
>     UNIQUE KEY `GroupID` (`GroupID`,`R' at line 6
>
> I am at a loss here.  Has anyone run into this before and are there any
> suggestions as to how to get around it? I can not make use of the public
> server at osflotsam.org for a variety of reasons.
>
> Thanks in advance
> Kurt
>
>
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20111021/35cd8ad6/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: kreisler.vcf
Type: text/x-vcard
Size: 324 bytes
Desc: not available
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20111021/35cd8ad6/attachment.vcf>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2630 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20111021/35cd8ad6/attachment.bin>