MySQL and Unicode

• pixnet blog: Mysql調整成全UTF-8語系 (some values are old names for MySQL 4.x only)
• Article: Unicode development with DbForms, MySQL 4.1, and friends
  • Unicode support is available since MySQL 4.1
  • At the end of the table definition add: ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • Must use –default-character-set=utf8 option with mysqldump for backup to retain Unicode data
• MySQL 5.0 Manual – Unicode
  • To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR
  • Run: [set names “utf8″] before do anything
  • Use this to connect to server: mysql –default-character-set=utf8 -uyour_username -p -h your_databasehost.your_domain.com your_database
• Convert database to Unicode
  • How to convert a complete Database to Unicode (UTF-8), using Windows.
    1. Create a dump file (use Mysqldump or phpMyAdmin)
    2. Open in Notepad
    3. Save in Unicode format
    4. Load to MySQL with: mysql –user=root –password databasename file.txt
  • *Maybe* another way
  • MySQL ODBC 3.x may not be Unicode friendly and still display the wrong charset
• Tested Result:
  • Webpage charset = utf8, table charset=utf8, via php, will store in latin1? When mysql client connect as latin1, can get back the utf8 bytecode data. Saving the value charset is utf8
  • Access update db, table charset=utf8, access and mysql cli can see back the value. Saving the value charset is big5.
  • Access update db, table charset=utf8, ODBC connect set utf8, access cannot read value!?
  • change the charset variables at server runtime does not change the value return to mysql cli, seems settings are per connection

    set character_set_client=utf8;
    set character_set_connection=utf8;
    set character_set_database=utf8;
    set character_set_results=utf8;
    set character_set_server=utf8;
    

    But even set these values, in mysql win32 CLI, update value with 2 Chinese characters still show 3 x ? values (24 bit, 1st char ASCII63,

  • Bug: MyODBC 3.51 is not yet support Unicode, wait MyODBC 3.53
    More

  • function to check database values: SELECT charset(value)
  • Create Database syntax (for create database with unicode support)
    create database dbname default character set utf8;

  • To complete convert a db from latin1 to utf8, a complete dump / restore maybe needed
  • Solution to DB_DataObject
    use a constructor to execute connection setting, mysql library seems don’t have related settings

  • Unicode solution:
    • Make sure create the database with default charset utf8, or set the server variable ‘default-character-set=utf8’ under [mysqld]
    • Set init_connect for non-root users at /etc/my.cnf, so any non-root users will set the connection to utf8:

      			[mysqld]
      			init_connect='SET NAMES utf8'
      			

    • when connect with mysql client with a non-root user, \s should see:

      		Server characterset:    utf8
      		Db     characterset:    utf8
      		Client characterset:    utf8
      		Conn.  characterset:    utf8
      		

    • For php, make sure the website default charset is set to: utf-8
    • For Microsoft Windows ODBC, need to set “init statement" of the ODBC driver to: big5 (or anything match the system locale),
      due to the MyODBC 3.51 is not yet support utf8

    • Problem fixed if use MyODBC 5.1 driver, and set connection parameter to “SET NAMES utf8″. Microsoft Office ODBC data source can get data in Unicode format properly.
  • mysql.exe in win32 does NOT use unicode, so it follow the system locale

• MySQL official website
• MySQL 5.0 Changes
• MySQL local full backup using mysqlhotcopy
• Log file maintenance
• InnoDB: Forcing recovery
  Use force recovery upto mode 4 if encounter similar problem:
  InnoDB: Fatal error: cannot read from file. OS error number 17

• Enable InnoDB support in MySQL 3.23
  For minimal, add to the [mysqld] section the line: innodb_data_file_path=ibdata:10M:autoextend

• MySQL InnoDB foreign key must have an associate index else it won’t work (the table won’t create)
• Change the auto increment value of an existing table
  ALTER TABLE YourTable AUTO_INCREMENT=10000;

• MySQL 4 and Unicode problem
  Maybe the default binary does not has Unicode support compiled

• Compile MySQL 4 in RedHat 8
  need these packages or more: gcc-c++, libstdc++-devel, ncurses-devel

• Create a case sensitive query
  1. if you defined the column binary
  2. you cast the field to type binary (SELECT * FROM members where userID = binary “username")

• FogBugz – MySQL Setting up Full-Text Search Index

• Login error after used MySQL 4.1
  Password format changed in MySQL 4.1. Add this into /etc/my.cnf, reset the password again.

  [mysqld]
  old-passwords
  

  Reference: MySql 4.1 A Few Important Features To Consider

• Client does not support authentication protocol
  Reset the password with: SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘newpwd’);
  A.2.3 Client does not support authentication protocol

• 050725 15:43:56 Warning: Asked for 196608 thread stack, but got 126976. InnoDB: Error: pthread_create returned 11
  You may have SELinux turned on. Check /var/log/messages, try to reconfig your policy. Or simply disable SELinux with: /usr/sbin/setenforce 0 and edit the /etc/sysconfig/selinux

• Restore the database without foreign key constraint:

  SET FOREIGN_KEY_CHECKS = 0;
  SOURCE /path/backupfile.sql;
  SET FOREIGN_KEY_CHECKS = 1;
  

• DB Designer 4
  Free CASE tool for MySQL

• MySql-Spy
  Free Win32 Admin tool for MySQL

• MySQL 5.0 Manual – Functions and Operators
• Searchable MySQL Manual with user comments
• MySQL HTML Manual
  • MySQL Column / Data Types
  • Foreign Keys in MySQL
  • FOREIGN KEY Constraints
  • 6.3 Functions for Use in SELECT and WHERE Clauses
• MySQL CREATE INDEX syntax
  Fulltext index only available in MyISAM tables, and does not work well with multi-byte characters, can work with utf-8

• MySQL InnoDB startup options
• InnoDB benchmark results
• Add to [mysqld] section of the config file: set-variable=lower_case_table_names=1, to enforce lower case table name, for compatibility between Windows & UNIX version
• MySQL User Account Management
  • SQL to set the initial password for root@localhost:
    SET PASSWORD FOR ‘root’@’localhost’ = OLD_PASSWORD(‘password’);

  • SQL to set the initial password for root@%:
    GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

  • Create a Unicode UTF-8 charset database in MySQL 4.1.10:
    create database ez1 default character set utf8;