For using UTF-8, you MUST use MySQL 4.1

# =============
# General
# =============
You can specify a character set for the server,
database, table, column, or client connection.

This makes the setting up the database a bit complicated.

@see http://dev.mysql.com/doc/mysql/en/charset-defaults.html

# =============
# Windows version
# =============
* Windows version of MySQL 4.1 now comes with 3 installation packages.

* If you used Windows-installer versions, they ask about the language setting.
  Choose the "most internatioanl" option, which uses UTF-8.
  
* In addition you MUST create your database with

CREATE DATABASE lams DEFAULT CHARACTER SET utf8
  
* You should use the standard installation version
  (not the "essentail" version) for development, since
  it installs the bebug codes in case you need it.

* my.ini or my.conf used to be located in C:\
  but now it is located where your copy of MySQL is installed
  (typically C:\Program Files\MySQL\MySQL Server 4.1).
  This allows different instances of MySQL to run with
  config files of their own, if you have not installed it
  as a Service (which you should not for a development environment).
  Add the line in the my.ini as Chris' instruction to 
  change the default transaction isolation level.
  
* You must also connect with a correct character set.

Currently we are using:
jdbc:mysql://localhost/lams?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&autoReconnect=true

# =============
# Linux/Solaris version
# =============
* my.conf file is located at /etc/my.conf
* set the database language to use UTF-8 in my.conf

[mysqld]
default-character-set=UTF-8
[mysqldump]
default-character-set=UTF-8
[mysql]
default-character-set=UTF-8

* If it does not exist, you have to create one by
  copying a sample config file included in the MySQL
  home directory

* Create lams database with utf8 as default

* Send cleint connection request with specifying utf8

# =============
# Securing the initial database
# =============
* Set the root password as:
  mysqladmin -u root password "yoursecret"

* As usual, delete the "test" database which has the anonymous user
  drop database test;
  
* Also, delete the anonymous user (user=' ' and password is ' ');
  delete from user where user=' ';

# =============
# creating LAMS database and setting up the lams user with password
# =============

#-----------------------------------------------------------------------
# Example MySQL script
# Replace 'melcoe006' and '192.168.111.106' with appropriate addresses
# Replace 'lamsdemo' with appropriate password
#-----------------------------------------------------------------------
 
CREATE DATABASE IF NOT EXISTS lams DEFAULT CHARACTER SET utf8;

USE mysql;

DELETE FROM user WHERE user='';
DELETE FROM db WHERE user='';

GRANT ALL PRIVILEGES ON *.* TO lams@localhost IDENTIFIED BY 'lamsdemo';
REVOKE PROCESS,SUPER ON *.* FROM lams@localhost;

# The host name of the machine on which MYSQL resides
GRANT ALL PRIVILEGES ON *.* TO lams@'melcoe006' IDENTIFIED BY 'lamsdemo';
REVOKE PROCESS,SUPER ON *.* FROM lams@'melcoe006';

# This is for two server configuration (JBoss and MySQL on different servers)
# The internal IP address of the JBoss machine that connects to the MySql database
#GRANT ALL PRIVILEGES ON *.* TO lams@'192.168.111.106' IDENTIFIED BY 'lamsdemo';
#REVOKE PROCESS,SUPER ON *.* FROM lams@'192.168.111.106';

# This may be a security risk (do it only if you needed this to make MySQL work)
GRANT ALL PRIVILEGES ON *.* TO lams@'%' IDENTIFIED BY 'lamsdemo';
REVOKE PROCESS,SUPER ON *.* FROM lams@'%';

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON lams.* TO lams@localhost IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON lams.* TO lams@'melcoe006' IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON lams.* TO lams@'192.168.111.106' IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON lams.* TO lams@'%' IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION;

FLUSH PRIVILEGES;
#------- end -----------------------------------------------------------



From: Chris Perfect [chris@e-plus.com.au]
Sent: Tuesday, 8 February 2005 4:04 PM
To: LAMS Dev
Subject: LAMS 1.1 Database Configuration

Firstly in order to cope with UTF-8 character sets we must use MySQL 4.1.
To achieve this:

    1) lams db must be created to use UTF-8

    CREATE DATABASE lams DEFAULT CHARACTER SET utf8;

    2) JDBC connection must be told to use UTF-8

    jdbc:mysql://137.111.229.13:3306/lams?characterEncoding=utf8


Secondly the transaction isolation level for InnoDB should be set to
READ-COMMITTED: this seems to occur out of the box for Win32 
(which is good because there doesn't seem to be a way of altering it
that works on 4.1 in Windows) but the linux version 
(and I am assumeing other UNIX-like OSs) defaults to REPEATABLE-READ.
In order to change this for linux you need to create or edit the 
config file /etc/my.cnf like the following:

[mysqld]
transaction-isolation=READ-COMMITTED


Note that according to the documentation you should be able to change 
the system variables to achieve these changes (or at least make changes 
that produce the same results) using the SET GLOBAL <variable>=<value> 
commands like we do in MySQL 4.0. Unfortunately the SET GLOBAL does not 
seem to work for many of the system variables 4.1 (though it doesn't 
produce an error).

Chris