jOpenSimWorld

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

com_opensim User Management

Kristen Mynx
Topic Author
Visitor

14 years 2 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 or Create an account to join the conversation.

foto50
Offline
Administrator

14 years 2 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 or Create an account to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 2 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 or Create an account to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 2 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 or Create an account to join the conversation.

Kristen Mynx
Topic Author
Visitor

14 years 2 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 or Create an account to join the conversation.

foto50
Offline
Administrator

14 years 2 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 or Create an account to join the conversation.

Time to create page: 0.104 seconds

Search

Donate jOpenSim

Please consider supporting our efforts.

Our Regions

Region:	Loc X:	Loc Y:
Agora	1000	998
BareBad…	997	997
Bohemas	996	998
BoraBor…	1002	999
Crystal…	1001	1000
DeepTho…	1005	998
FoToSan…	999	995
jCity	1001	997
jOpenWe…	1000	999
JuniorT…	1002	1001
Kanadah…	1002	1000
LindaKe…	1000	997
LittleC…	996	997
MonteSc…	1001	1001
Naos	999	999
Pangaea	1005	1003
RiverSi…	999	1000
Sakani	999	998
Snambin	1001	998
Tartola	997	998
ViewerH…	1002	998
WaterWo…	1001	999
WilderK…	1000	1000

jOpenSimWorld

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