SMF 1.1rc1 install changes database character set (solved)

[kawika]

Apparently "CREATE DATABASE IF NOT EXISTS" will reset the database character set to the server default.

From MySql (hxxp:dev.mysql.com/doc/refman/4.1/en/charset-database.html [nonactive])
"The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose. "

[ez2ask]

Apparently "CREATE DATABASE IF NOT EXISTS" will reset the database character set to the server default.

From MySql (hxxp:dev.mysql.com/doc/refman/4.1/en/charset-database.html [nonactive])
"The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose. "

I have questions about the database character set.

I just installed 1.1rc1 to my local machine and it's another language of utf8 version rather than just English, when I created the database before installed, I set the character set to utf8, after installed all the table and fields became latin, just wondering how to set it back to utf8, otherwise it will show junk code to the message, title, subject....... I also tried to change the tables and fields to utf8 one by one, but after that I found the smf won't work correctly, so just wondering if some of the field needs to be set to latin..........

Could you please help me to figure out what should I do to make it work correctly and have no junk code by using utf8? Thank you very much!!

[kawika]

That's exactly what happened to me. The solution for me was to remove SMF, edit my.cnf on my database server and add "default-character-set = utf8" to the [mysqld] section, then restarting mysql. This way all new databases and tables that do not specify a character set will be created with UTF8.

The other option is to modify the SQL create statements in the SMF install and add "DEFAULT CHARSET=utf8" at the end and outside the parentheses in the create table statements. Here's an example:

Koodi [Valitse] Laajenna

CREATE TABLE `smf_attachments` (
  `ID_ATTACH` int(10) unsigned NOT NULL auto_increment,
  `ID_THUMB` int(10) unsigned NOT NULL default '0',
  `ID_MSG` int(10) unsigned NOT NULL default '0',
  `ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
  `attachmentType` tinyint(3) unsigned NOT NULL default '0',
  `filename` tinytext NOT NULL,
  `size` int(10) unsigned NOT NULL default '0',
  `downloads` mediumint(8) unsigned NOT NULL default '0',
  `width` mediumint(8) unsigned NOT NULL default '0',
  `height` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID_ATTACH`),
  UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),
  KEY `ID_MSG` (`ID_MSG`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[ez2ask]

That's exactly what happened to me. The solution for me was to remove SMF, edit my.cnf on my database server and add "default-character-set = utf8" to the [mysqld] section, then restarting mysql. This way all new databases and tables that do not specify a character set will be created with UTF8.

The other option is to modify the SQL create statements in the SMF install and add "DEFAULT CHARSET=utf8" at the end and outside the parentheses in the create table statements. Here's an example:

Koodi [Valitse] Laajenna

CREATE TABLE `smf_attachments` (
  `ID_ATTACH` int(10) unsigned NOT NULL auto_increment,
  `ID_THUMB` int(10) unsigned NOT NULL default '0',
  `ID_MSG` int(10) unsigned NOT NULL default '0',
  `ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
  `attachmentType` tinyint(3) unsigned NOT NULL default '0',
  `filename` tinytext NOT NULL,
  `size` int(10) unsigned NOT NULL default '0',
  `downloads` mediumint(8) unsigned NOT NULL default '0',
  `width` mediumint(8) unsigned NOT NULL default '0',
  `height` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID_ATTACH`),
  UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),
  KEY `ID_MSG` (`ID_MSG`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Thank you for your reply!
What if I can't change the my.cnf file, because the file is on the web hosting company..............then I have to do the second option, right? Do you mean I have to put "DEFAULT CHARSET=utf8" on every tables in the XXX.sql file before install? Am I correct?

[kawika]

That's correct but you should test it out with one table and look at the collation for the table and fields.

[Imago]

Hello,

I need only SET NAMES for mysql_query to be UTF8

In Joomla, this is defined in database.php

Koodi [Valitse] Laajenna

        @mysql_query("SET NAMES 'utf8'", $this->_resource);

Any hints as how to do the same with SMF both for installation and functioning?

Now my component SMF uses utf8 for queries, my standallone SMF sets the default names which in my case are cp1251 (as defined in my.cnf - which must remain the same for many other reasons).