- Posts: 20
- Thank you received: 0
jOpenSimWorld
| Grid Status: | Online |
| Total Regions: | 23 |
| Visitors (30 Days): | 10 |
| Total Residents: | 491 |
| Online Now: | 0 |
| Hypergriders online: | 0 |
error in user
12 years 1 month ago #1717
by foto50
Hi luigi
Ok, here the queries are coming
But before you change anything, I can not express enough, how important it is to make backups before you change anything in the database 
To explain: with "old database" I did not mean the "old OpenSim version" but rather the "old MySQL version" when first installing OpenSim. In older versions of MySQL the default character set was set to "latin1" (with collation "latin1_swedish_ci" even). But since a while - dont remember exactly what version it was - by default is used "utf8" (with collation "utf8_general_ci").
You can easy check this out on your server with executing following queries:
As of MySQL 5.something, most lines here should show "utf8" and "utf8_general_ci".
Now to change the mentioned tables of OpenSim so the queries are working correct again are the following (did I already mention to make backup before?):
There are other tables as well (assets, auth, Avatars, Friends, inventoryfolders, inventoryitems, regions, tokens) with char-fields and it would be basically a good idea to keep them all with the same character set, but for this special problem here, mostly these 3 tables need to be on utf8.
I hope this helped
Greetz
FoTo50
Ok, here the queries are coming
But before you change anything, I can not express enough, how important it is to make backups before you change anything in the database To explain: with "old database" I did not mean the "old OpenSim version" but rather the "old MySQL version" when first installing OpenSim. In older versions of MySQL the default character set was set to "latin1" (with collation "latin1_swedish_ci" even). But since a while - dont remember exactly what version it was - by default is used "utf8" (with collation "utf8_general_ci").
You can easy check this out on your server with executing following queries:
SHOW VARIABLES LIKE "%character%";
SHOW VARIABLES LIKE "%collation%";As of MySQL 5.something, most lines here should show "utf8" and "utf8_general_ci".
Now to change the mentioned tables of OpenSim so the queries are working correct again are the following (did I already mention to make backup before?):
ALTER TABLE `GridUser`
MODIFY COLUMN `UserID` varchar(255) CHARACTER SET utf8 NOT NULL FIRST ,
MODIFY COLUMN `HomeRegionID` char(36) CHARACTER SET utf8 NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' AFTER `UserID`,
MODIFY COLUMN `HomePosition` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '<0,0,0>' AFTER `HomeRegionID`,
MODIFY COLUMN `HomeLookAt` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '<0,0,0>' AFTER `HomePosition`,
MODIFY COLUMN `LastRegionID` char(36) CHARACTER SET utf8 NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' AFTER `HomeLookAt`,
MODIFY COLUMN `LastPosition` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '<0,0,0>' AFTER `LastRegionID`,
MODIFY COLUMN `LastLookAt` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '<0,0,0>' AFTER `LastPosition`,
MODIFY COLUMN `Online` char(5) CHARACTER SET utf8 NOT NULL DEFAULT 'false' AFTER `LastLookAt`,
MODIFY COLUMN `Login` char(16) CHARACTER SET utf8 NOT NULL DEFAULT '0' AFTER `Online`,
MODIFY COLUMN `Logout` char(16) CHARACTER SET utf8 NOT NULL DEFAULT '0' AFTER `Login`,
DEFAULT CHARACTER SET=utf8;
ALTER TABLE `Presence`
MODIFY COLUMN `UserID` varchar(255) CHARACTER SET utf8 NOT NULL FIRST ,
MODIFY COLUMN `RegionID` char(36) CHARACTER SET utf8 NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' AFTER `UserID`,
MODIFY COLUMN `SessionID` char(36) CHARACTER SET utf8 NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' AFTER `RegionID`,
MODIFY COLUMN `SecureSessionID` char(36) CHARACTER SET utf8 NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' AFTER `SessionID`,
DEFAULT CHARACTER SET=utf8;
ALTER TABLE `UserAccounts`
MODIFY COLUMN `PrincipalID` char(36) CHARACTER SET utf8 NOT NULL FIRST ,
MODIFY COLUMN `ScopeID` char(36) CHARACTER SET utf8 NOT NULL AFTER `PrincipalID`,
MODIFY COLUMN `FirstName` varchar(64) CHARACTER SET utf8 NOT NULL AFTER `ScopeID`,
MODIFY COLUMN `LastName` varchar(64) CHARACTER SET utf8 NOT NULL AFTER `FirstName`,
MODIFY COLUMN `Email` varchar(64) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `LastName`,
MODIFY COLUMN `ServiceURLs` text CHARACTER SET utf8 NULL AFTER `Email`,
MODIFY COLUMN `UserTitle` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '' AFTER `UserFlags`,
DEFAULT CHARACTER SET=utf8;There are other tables as well (assets, auth, Avatars, Friends, inventoryfolders, inventoryitems, regions, tokens) with char-fields and it would be basically a good idea to keep them all with the same character set, but for this special problem here, mostly these 3 tables need to be on utf8.
I hope this helped
Greetz
FoTo50
Please Log in to join the conversation.
Time to create page: 0.096 seconds