tomcat/jdbc/mysql: can insert ÿ(U+00FF) but not Ā (U+0100)

Question

hi,

my setup:

mysql 5.1

show variables:
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
| character_sets_dir | D:\Programme\MySQL\MySQL Server 5.1\share charsets\
| collation_connection | utf8_general_ci
| collation_database | utf8_unicode_ci
| collation_server | utf8_general_ci
and even
| init_connect | SET collation_connection = utf8_general_ci; SET NAMES utf8;

the table table has character set utf8

tomcat 6.0

the jdbc connector uses characterEncoding="utf8" useUnicode="true"

now when i try

stmt.execute("UPDATE table SET value=\"ÿ\" WHERE ...)

it works but for

stmt.execute("UPDATE table SET value=\"Ā\" WHERE ...)

i get an java.sql.SQLException: Incorrect string value: '\xC4\x80' for column 'value' at row 1

furthermore it works for all characters below ÿ, which can be encoded with 1 byte but as soon as 2 bytes are needed: bang!

why is that so? and how can i get it to work?

Answer 1

after i added another two tables to check if it's an MyISAM vs. InnoDB problem it just worked on the new tables and why?

in the new tables each column used the default charset while in my existing tables the charsets of each column were set to latin1. this was because i copied the db from a non-utf8 mysql instance and manually changed the table charset to utf-8. BUT while copying, HeidiSQL added a "CHARACTER SET latin1" to each column which wasn't changed when changing the charset AND it is not very easily visible in HeidiSQL that a column has an individual charset ...