chinese character become "?" (question mark)

lawrence

hi i don't know why the chinese character in those reply & topic become "?" I installed the chinese pack, even the chinese interface works perfect just those typed in chinese failed what is my problem ????????? the encoding mode of browser is uft-8 thanks

Minisweeper

http://lussumo.com/docs/doku.php?id=vanilla:administrators:encodings

sujang

Minisweeper's direction is very important and, i suggest you especially that be sure to save your conf/settings.php file in utf-8(no BOM)incoding, from my experience.

root

So let me confirm, if I have

CREATE TABLE `LUM_Category` ( `CategoryID` int(2) NOT NULL auto_increment, `Name` varchar(100) collate latin1_general_ci NOT NULL default '', `Description` text collate latin1_general_ci, `Priority` int(11) NOT NULL default '0', `Cat_filter` text collate latin1_general_ci, `Subscribeable` int(1) NOT NULL default '0', PRIMARY KEY (`CategoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;

I should edit it to be

CREATE TABLE `LUM_Category` ( `CategoryID` int(2) NOT NULL auto_increment, `Name` varchar(100) NOT NULL default '', `Description` text, `Priority` int(11) NOT NULL default '0', `Cat_filter` text, `Subscribeable` int(1) NOT NULL default '0', PRIMARY KEY (`CategoryID`) ) ENGINE=MyISAM AUTO_INCREMENT=8 ;

Is this correct?

lech

Not sure how correct this is, but if you already have a database you could simply run a query for the DB itself and each table in it with something that looks like the following. (someone who knows better may want to double check this) Perform at your own risk.

ALTER DATABASE `yourDataBasesName` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Category` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_...

For each table in the database and so on.

root

lech: Applied this to a local copy, and (without seeing Vanilla running with this database) this is a sample table:

CREATE TABLE `LUM_Category` ( `CategoryID` int(2) NOT NULL auto_increment, `Name` varchar(100) character set latin1 collate latin1_general_ci NOT NULL default '', `Description` text character set latin1 collate latin1_general_ci, `Priority` int(11) NOT NULL default '0', `Cat_filter` text character set latin1 collate latin1_general_ci, `Subscribeable` int(1) NOT NULL default '0', PRIMARY KEY (`CategoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

The character set and collate of some fields remains as latin1. This, I suppose, must be manually removed?

lech

root, while that syntax is correct it only works if you're recreating that table and want to start your category index at 8 instead of 1. Otherwise it's not ideal if you plan to apply it to a database table already full of data or you would need to drop and recreate that table. Simply meaning: you'd lose everything that row once held. Instead you want to simply alter the existing database it's tables and rows for future use. I did some quick poking around and came up with the following query:

note: Before attempting this, back up your database! This is not 100% proven and I don't know what kind of errors will arise from it so I encourage someone who's more versed in this to double check my work. And again, apply at your own risk!

-- -- Alter the database to accept utf-8 -- Once applied, this SHOULD automatically default new tables and rows -- created to utf-8 unless the creator of those tables specifies otherwise. -- make sure to replace `TheDatabasesName` with the one you're targeting... ALTER DATABASE `TheDatabasesName` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; -- -- Begin alterting tables and specific roles for utf-8 input if not already attributed -- The following tables do not need modifications as they're all numerical -- LUM_UserDiscussionWatch, LUM_CategoryBlock, LUM_CategoryRoleBlock -- LUM_DiscussionUserWhisperFrom, LUM_DiscussionUserWhisperTo, LUM_IpHistory -- -- Categories ALTER TABLE `LUM_Category` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Category` CHANGE `Name` `Name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Description` `Description` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL; -- Comments ALTER TABLE `LUM_Comment` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Comment` CHANGE `Body` `Body` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `RemoteIp` `RemoteIp` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL; -- Discussion Threads ALTER TABLE `LUM_Discussion` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Discussion` CHANGE `Name` `Name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; -- Roles ALTER TABLE `LUM_Role` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Role` CHANGE `Name` `Name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Icon` `Icon` VARCHAR( 155 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Description` `Description` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; -- Styles ALTER TABLE `LUM_Style` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_Style` CHANGE `Name` `Name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Url` `Url` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `PreviewImage` `PreviewImage` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; -- Users ALTER TABLE `LUM_User` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_User` CHANGE `CustomStyle` `CustomStyle` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `FirstName` `FirstName` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `LastName` `LastName` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Name` `Name` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Email` `Email` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `Icon` `Icon` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `Picture` `Picture` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `Discovery` `Discovery` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL; -- Role History ALTER TABLE `LUM_UserRoleHistory` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `LUM_UserRoleHistory` CHANGE `Notes` `Notes` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

That should account for most of the user attributed rows within vanilla, obviously this is only the core so you're on your own if extensions are using the DB. Depending on your table creation, the only thing that really needs to be changed are the "DEFAULT CHARSET" and "COLLATE" flags after all the rows, not necessarily each individual row. But again that may depend on the SQL version in question from a creation standpoint, I did this all on a mySQL 5 server and so far notice nothing huge affecting it.

Your mileage may vary.

root

lech: I tried to dump the sql data and import it into my local database, but that caused no end of errors... I finally went with the manual way, which was to use phpMyAdmin to change each table one at a time =P Now when I export the tables they look decent enough =P

Thanks for your patience.

lech

Yeah, it's not always likely that this will repair entries already present in the DB but it should accommodate any new entries created. I need to get someone to double check whether or not my above alterations impact the DB in a good or bad way. If good then we should track the changes to reflect the wiki and extend these settings to individual tables within the DB.

I think the reason why this occurs now is that by default, mySQL isn't configured (ie: no root password etc...) and I suspect many admins just leave things "as-is". So out of the box you get a fairly loose setup with not-so-native settings for new databases created. Usually selecting the first approximate Collation option and leaving it as such. In the case of Vanilla, it's simply specifying the default charset as Latin1 upon install (which while friendly) has some room for improvement. I think we need to create some kind of environment testing during the install process in addition to the manual setup.