problem in table creation and population

[davidklonski]

Hello

I have the following table:

CREATE TABLE language_settings (
  Language_ID INT UNSIGNED NOT NULL,
  Lang_Code CHAR(2) NOT NULL,
  ISO_Code CHAR(5) NOT NULL,
  English_Name VARCHAR(255) NOT NULL,
  Locale_Name VARCHAR(255) NOT NULL,
  DIR CHAR(3) NOT NULL,
  PRIMARY KEY (Language_ID)
) ENGINE=MYISAM;

when I create it and execute: describe language_settings I get:

+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| Language_ID  | int(10) unsigned |      | PRI | 0       |       |
| Lang_Code    | char(2)          |      |     |         |       |
| ISO_Code     | varchar(5)       |      |     |         |       |
| English_Name | varchar(255)     |      |     |         |       |
| Locale_Name  | varchar(255)     |      |     |         |       |
| DIR          | char(3)          |      |     |         |       |
+--------------+------------------+------+-----+---------+-------+

Notice how the type of the ISO_Code changed from CHAR(5) to varchar(5).
It there a reason why such conversion will take place?

Also, I populate this table using:
LOAD DATA INFILE 'languageSettings.sql' INTO TABLE language_settings;

Here is the content of languageSettings.sql:

1	EN	en_US	English	English	ltr
2	HE	he_IW	Hebrew	עברית	rtl
3	FR	fr_FR	French	Français	ltr

For some reason, after the table is populated I get an incorrect langauge_id for the English language:

select * from language_settings;

+-------------+-----------+----------+--------------+-------------+-----+
| Language_ID | Lang_Code | ISO_Code | English_Name | Locale_Name | DIR |
+-------------+-----------+----------+--------------+-------------+-----+
|           0 | EN        | en_US    | English      | English     | ltr |
|           2 | HE        | he_IW    | Hebrew       | ╫ó╫ס╫¿╫ש╫¬  | rtl |
|           3 | FR        | fr_FR    | French       | Français   | ltr |
+-------------+-----------+----------+--------------+-------------+-----+

Somehow the language_id for the first row became 0 instead of 1.
Does anyone know what I am doing wrong?
BTW, I am using MySQL 5.0

[kleineme]

Hi,

for the first part the explanation is here.

For the second part I don't know.

[davidklonski]

Thanks for the help

I have found the bug.
File 'languageSettings.sql' was saved in UTF8 format which added a BOM (Byte Order Mark) at the beginning of the file.
When MySQL encountered the BOM, it couldn't understand it and things got ugly from there.

The solution was to open the file in an editor and manually remove the BOM.