jOpenSimWorld

Grid Status: Online
Total Regions: 23
Visitors (30 Days): 13
Total Residents: 477
Online Now: 0
Hypergriders online: 0
  1. Forum
  3. jOpenSim component
  5. jOpenSim
  7. com_opensim User Management

com_opensim User Management

13 years 6 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
(
    [_os_db] => 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 (
  ),
))
    [userquery] => 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
    [_this_total] => 
)

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.

13 years 6 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\" ... :S

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.

13 years 6 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.

13 years 6 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.

13 years 6 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.

13 years 6 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.

  1. Forum
  3. jOpenSim component
  5. jOpenSim
  7. com_opensim User Management
Time to create page: 0.079 seconds

Search

Donate jOpenSim

Please consider supporting our efforts.

Amount

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): 13
Total Residents: 477
Online Now: 0
Hypergriders online: 0