jOpenSimWorld

Grid Status:	Online
Total Regions:	23
Visitors (30 Days):	8
Total Residents:	491
Online Now:	0
Hypergriders online:	0

com_opensim User Management

Kristen Mynx
Topic Author
Visitor

14 years 7 months ago #37 by Kristen Mynx

Foto50..........

The file you sent me worked well with regard to mysql on a nonstandard port.. YAY!

User Managment showed some debug info, but no user information:

Array
(
    &#91;_os_db&#93; => JDatabaseMySQL::__set_state(array(
   \'name\' => \'mysql\',
   \'_nullDate\' => \'0000-00-00 00:00:00\',
   \'_nameQuote\' => \'`\',
   \'_sql\' => \'SET sql_mode = \\\'MYSQL40\\\'\',
   \'_errorNum\' => 0,
   \'_errorMsg\' => \'\',
   \'_table_prefix\' => \'\',
   \'_resource\' => NULL,
   \'_cursor\' => true,
   \'_debug\' => 0,
   \'_limit\' => 0,
   \'_offset\' => 0,
   \'_ticker\' => 0,
   \'_log\' => 
  array (
  ),
   \'_utf\' => true,
   \'_quoted\' => 
  array (
  ),
   \'_hasQuoted\' => false,
   \'_errors\' => 
  array (
  ),
))
    &#91;userquery&#93; => SELECT
							UserAccounts.PrincipalID AS userid,
							UserAccounts.FirstName AS firstname,
							UserAccounts.LastName AS lastname,
							UserAccounts.Email AS email,
							FROM_UNIXTIME(UserAccounts.Created,\'%Y-%m-%d %H:%i:%s\') AS created,
							IF(`GridUser`.`Online` = \'True\',\'1\',\'0\') AS online,
							FROM_UNIXTIME(GridUser.Login,\'%Y-%m-%d %H:%i:%s\') AS last_login,
							FROM_UNIXTIME(GridUser.Logout,\'%Y-%m-%d %H:%i:%s\') AS last_logout
						FROM
							UserAccounts LEFT JOIN GridUser ON UserAccounts.PrincipalID COLLATE utf8_general_ci = GridUser.UserID COLLATE utf8_general_ci
						
						ORDER BY
							Created DESC
    &#91;_this_total&#93; => 
)

I then copied the query into a manual mysql session, and received this error:

ERROR 1253 (42000) at line 1: COLLATION \'utf8_general_ci\' is not valid for CHARACTER SET \'latin1\'

I removed the two COLLATION clauses from opensim.class.php in getUserQuery, and the user list worked. I was then also able to link the accounts. I then removed the other COLLATION clauses from the other places in that file, and everything worked well (friends, linking, creating new users, etc)

I\'m assuming its a server default character set issue. I\'m not sure how to correct the logic if someone is using a unicode characters set. I\'m sure that the collation is needed in some circumstances, but it clearly fails in my case. I have not read any documentation that states unicode character set defaults are required or recommended for opensim mysql support.

hope this helps

Kristen

Please Log in to join the conversation.

foto50
Offline
Administrator

14 years 7 months ago #39 by foto50

Hi Kristen and thanks very much for your helpful comments!

It looks very much like this is a default charset problem caused by different MySQL versions, or more - caused by very loose table definitions from OpenSim during installation. I created a Mantis about this issue already a while ago, but it seems not to get a very high priority.

From a certain MySQL version - I think it was 5.1 (but at least somewhere between 5.0.45 and 5.1.36) - they changed the default charset for new created tables from latin1 to utf8. Also somewhere around this version, they changed the behaviour concerning collations - it became much more restricted.

On my test platform, running currently on 5.1.36, if I remove the \"COLLATE utf8_general_ci\" from the query, it will result in an error ([Err] 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation \'=\'), since the field \"UserID\" in the table \"GridUser\" has the collation \"utf8_unicode_ci\" and the equivalent field \"PrincipalID\" in the table \"UserAccounts\" has the collation \"utf8_general_ci\"! So this is why I added this to the query. Obviously it now will create an error with earlier MySQL versions since \"utf8_general_ci\" is definately not a valid collation for the charset \"latin1\" ...

Of course, I could fix this problem by running a query, altering the mentioned tables of OpenSim. But in my opinion, a webinterface should take the table structure from opensim AS IS and not change anything in there. Adding, updating and deleting data is ok for an extension, but not changing the basic structure...

It looks like a dilemma, but thanks a lot for pointing me to this. I\'ll have to reorganize the data fetch in that part (probably split it into 2 queries) to avoid problems between older and newer MySQL versions.

Greetz
FoTo50

[edit]PS.: could you please tell me your MySQL Server version?[/edit]

Please Log in to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 7 months ago #44 by Kristen Mynx

mysql version 5.0.45, installed a in August 2009, with generic defaults. (Centos 5.2)

the database and tables were generated by opensim version 0.7.0.2, fresh install

Kristen

Please Log in to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 7 months ago #45 by Kristen Mynx

Additional info... my table setup

I used show create table:

UserAccounts | CREATE TABLE `UserAccounts` (
  `PrincipalID` char(36) NOT NULL,
  `ScopeID` char(36) NOT NULL,
  `FirstName` varchar(64) NOT NULL,
  `LastName` varchar(64) NOT NULL,
  `Email` varchar(64) default NULL,
  `ServiceURLs` text,
  `Created` int(11) default NULL,
  `UserLevel` int(11) NOT NULL default \'0\',
  `UserFlags` int(11) NOT NULL default \'0\',
  `UserTitle` varchar(64) NOT NULL default \'\',
  UNIQUE KEY `PrincipalID` (`PrincipalID`),
  KEY `Email` (`Email`),
  KEY `FirstName` (`FirstName`),
  KEY `LastName` (`LastName`),
  KEY `Name` (`FirstName`,`LastName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

| GridUser | CREATE TABLE `GridUser` (
  `UserID` varchar(255) NOT NULL,
  `HomeRegionID` char(36) NOT NULL default \'00000000-0000-0000-0000-000000000000\',
  `HomePosition` char(64) NOT NULL default \'<0,0,0>\',
  `HomeLookAt` char(64) NOT NULL default \'<0,0,0>\',
  `LastRegionID` char(36) NOT NULL default \'00000000-0000-0000-0000-000000000000\',
  `LastPosition` char(64) NOT NULL default \'<0,0,0>\',
  `LastLookAt` char(64) NOT NULL default \'<0,0,0>\',
  `Online` char(5) NOT NULL default \'false\',
  `Login` char(16) NOT NULL default \'0\',
  `Logout` char(16) NOT NULL default \'0\',
  PRIMARY KEY  (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Kristen

Please Log in to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 7 months ago #46 by Kristen Mynx

Possible fix, using CONVERT to match the datatypes. Since the data is actually a UUID there should be no issue. I hope this works with your setup. It is possible that this may slow things down (because indexes may not be utilized), however, the COLLATE clause would have the same effect.

--- /home/special/os72mygrid/www/tmp/com_opensim/site/includes/opensim.class.php        2010-10-01 14:56:38.000000000 -0500
+++ opensim.class.php   2010-10-03 22:11:03.000000000 -0500
@@ -152,7 +152,7 @@
                                                        FROM_UNIXTIME(%7\\$s.%10\\$s,\'%%Y-%%m-%%d %%H:%%i:%%s\') AS last_login,
                                                        FROM_UNIXTIME(%7\\$s.%11\\$s,\'%%Y-%%m-%%d %%H:%%i:%%s\') AS last_logout
                                                FROM
-                                                       %1\\$s LEFT JOIN %7\\$s ON %1\\$s.%2\\$s COLLATE utf8_general_ci = %7\\$s.%8\\$s COLLATE utf8_general_ci
+                                                       %1\\$s LEFT JOIN %7\\$s ON CONVERT(%1\\$s.%2\\$s USING utf8) = CONVERT(%7\\$s.%8\\$s USING utf8) 
                                                %14\\$s
                                                ORDER BY
                                                        %12\\$s %13\\$s\",
@@ -184,7 +184,7 @@
                                                                                        FROM_UNIXTIME(%7\\$s.%10\\$s,\'%%Y-%%m-%%d %%H:%%i:%%s\') AS last_logout,
                                                                                        %11\\$s.%13\\$s AS passwordSalt
                                                                                FROM
-                                                                                       %1\\$s LEFT JOIN %7\\$s ON %1\\$s.%2\\$s COLLATE utf8_general_ci = %7\\$s.%8\\$s COLLATE utf8_general_ci,
+                                                                                       %1\\$s LEFT JOIN %7\\$s ON CONVERT(%1\\$s.%2\\$s USING utf8) = CONVERT(%7\\$s.%8\\$s USING utf8),
                                                                                        %11\\$s
                                                                                WHERE
                                                                                        %1\\$s.%2\\$s = \'%14\\$s\'
@@ -213,7 +213,7 @@
                                                                                FROM
                                                                                        %5\\$s,
                                                                                        %1\\$s
-                                                                                               LEFT JOIN %9\\$s ON %1\\$s.%2\\$s COLLATE utf8_general_ci = %9\\$s.%10\\$s COLLATE utf8_general_ci
+                                                                                               LEFT JOIN %9\\$s ON CONVERT(%1\\$s.%2\\$s USING utf8) = CONVERT(%9\\$s.%10\\$s USING utf8) 
                                                                                WHERE
                                                                                        %5\\$s.%2\\$s = \'%12\\$s\'
                                                                                AND
@@ -483,4 +483,4 @@
 }

Kristen

Please Log in to join the conversation.

foto50
Offline
Administrator

14 years 7 months ago #54 by foto50

\Kristen Mynx\:juawrgu6 wrote: CONVERT(%1\\$s.%2\\$s USING utf8) = CONVERT(%9\\$s.%10\\$s USING utf8)

This is an interesting aproach, I\'ll definately have to check this out a little closer.

For now, I published a new version (0.0.1 alpha) with a different way of reading the user information, that should work with all mysql versions (2 queries in a loop).

Greetz
FoTo50

Please Log in to join the conversation.

Time to create page: 0.101 seconds