error 1366 Incorrect string value
SM
drop database if exists dbdisco;
create database dbdisco;
use dbdisco;
drop table if exists category;
CREATE TABLE category(
category_id tinyint unsigned not null primary key,
category char(25) not null
);
INSERT INTO category (category_id, category) VALUES (1, 'test
character á');
INSERT INTO category (category_id, category) VALUES (2, 'test
character é');
INSERT INTO category (category_id, category) VALUES (3,
'Compilation');
INSERT INTO category (category_id, category) VALUES (4, 'Tributes');
I have special characters like á, é.
In the DOS command promtp window i enter this: mysql> source test.sql;
and i keep getting the following error for the two first records:
ERROR : Incorrect string value: \XEDa for column 'category' at row 1
...
My ini file is set like this
[mysql]
default-character-set=utf8
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
default-character-set=utf8
character_set_server=utf8
so what's wrong ? How come it doesn't recognize special characters?
Help
Thanks
Marco
Captain Paralytic
Try a
SET NAMES utf8
at the beginning
SM
try it but with no success...
Chuck
> i have a simple sql file(test.sql) that looks like this:
>
> drop database if exists dbdisco;
> create database dbdisco;
> use dbdisco;
>
> drop table if exists category;
> CREATE TABLE category(
> category_id tinyint unsigned not null primary key,
> category char(25) not null
> );
> INSERT INTO category (category_id, category) VALUES (1, 'test
> INSERT INTO category (category_id, category) VALUES (2, 'test
> INSERT INTO category (category_id, category) VALUES (3,
> 'Compilation');
> INSERT INTO category (category_id, category) VALUES (4, 'Tributes');
>
>
> In the DOS command promtp window i enter this: mysql> source test.sql;
>
> and i keep getting the following error for the two first records:
> ERROR : Incorrect string value: \XEDa for column 'category' at row 1
> ...
>
>
> My ini file is set like this
>
> [mysql]
>
> default-character-set=utf8
>
> [mysqld]
>
> # The TCP/IP Port the MySQL Server will listen on
> port=3306
>
> default-character-set=utf8
> character_set_server=utf8
>
> so what's wrong ? How come it doesn't recognize special characters?
Can you verify the character set for the column is utf-8?
show create table dbdisco.category;
show create database dbdisco;
Also, why are you using CHAR and not VARCHAR? Are you planning that
every row in this table will have exactly 25 characters? If not you are
wasting space and slowing down queries against this table.
SM
> SM wrote:
> > i have a simple sql file(test.sql) that looks like this:
>
> > drop database if exists dbdisco;
> > create database dbdisco;
> > use dbdisco;
>
> > drop table if exists category;
> > CREATE TABLE category(
> > category_id tinyint unsigned not null primary key,
> > category char(25) not null
> > );
> > INSERT INTO category (category_id, category) VALUES (1, 'test
> > INSERT INTO category (category_id, category) VALUES (2, 'test
> > INSERT INTO category (category_id, category) VALUES (3,
> > 'Compilation');
> > INSERT INTO category (category_id, category) VALUES (4, 'Tributes');
>
> > In the DOS command promtp window i enter this: mysql> source test.sql;
>
> > and i keep getting the following error for the two first records:
> > ERROR : Incorrect string value: \XEDa for column 'category' at row 1
> > ...
>
> > My ini file is set like this
>
> > [mysql]
>
> > default-character-set=utf8
>
> > [mysqld]
>
> > # The TCP/IP Port the MySQL Server will listen on
> > port=3306
>
> > default-character-set=utf8
> > character_set_server=utf8
>
> > so what's wrong ? How come it doesn't recognize special characters?
>
> Can you verify the character set for the column is utf-8?
>
> show create table dbdisco.category;
> show create database dbdisco;
>
> Also, why are you using CHAR and not VARCHAR? Are you planning that
> every row in this table will have exactly 25 characters? If not you are
> wasting space and slowing down queries against this table.
it is.
for: show create table dbdisco.category the endline says something
like : ENGINE=InnoDB DEFAULT CHARSET=utf8
and
for: show create database dbdisco i get: CREATE DATABASE 'dbdisco' /*!
40100 DEFAULT CHARACTER SET utf8 */
As for CHAR vs VARCHAR i dunno. I'm new at this, i've only been using
mysql for the last 6 months. I'm going to make more research on char
VS varchar. Thanks for the suggestion.
About my problem, could this be something to do with the command
promtp window? Maybe the command prompt doesn't understand utf-8...
Jerry Stuckle
Not necessarily. A VARCHAR field also has to have 1 or 2 bytes to
contain the length (depending on the size of the VARCHAR field), and
this must be checked on every access to that field. Since CHAR is fixed
length, it only needs to be checked once. If the data is mostly the
maximum length, then a CHAR can be more efficient.
However - the performance difference is negligible, and should not be
your main concern in deciding when to use CHAR and when to use VARCHAR
(premature optimization). Rather, the data itself and how it is used
should be.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Chuck
My point is this. Unless you have fixed length data, you should *never*
use CHAR.
Chuck
> As for CHAR vs VARCHAR i dunno. I'm new at this, i've only been using
> mysql for the last 6 months. I'm going to make more research on char
> VS varchar. Thanks for the suggestion.
I've been doing MySQL for about 6 mos too, but databases for about 20
years. As a general rule of thumb, you should only use CHAR when the
data is fixed length.
>
> About my problem, could this be something to do with the command
> promtp window? Maybe the command prompt doesn't understand utf-8...
I am not sure what's causing the "incorrect string value". I created a
table with a single utf8 column and was able to insert characters with
diacritics without any error. I'm using mysqld version 5.0.45.
test.sql
=========
set names utf8;
insert into mytable values ('Jos� Pe�a');
$ mysql -Ee "show create table test.mytable"
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`n` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
$ mysql -t test < test.sql
+-------------+
| n |
+-------------+
| Jos� Pe�a |
+-------------+
Jerry Stuckle
Never is a long time.
I appreciate your experience with RDBs, but I also have over 20 years of
experience. And I've found that CHAR can sometimes be a better choice,
even if the data is variable length. This is especially true for small
fields, or fields where the data length mostly fills the field but
sometimes can be a little bit shorter.
Chuck
It's the rare case where char is a better choice than varchar. OTOH I
see databases packaged with apps that use char all the time where
varchar should have been used.
Jerry Stuckle
Yes, and occasionally I see VARCHAR where CHAR should be used.