[SOLVED] problem in table population

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

problem in table population

Post by davidklonski »

Hello

I have the following table:

Code: Select all

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;
I populate this table using:
LOAD DATA INFILE 'languageSettings.sql' INTO TABLE language_settings;

Here is the content of languageSettings.sql:

Code: Select all

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;

Code: Select all

+-------------+-----------+----------+--------------+-------------+-----+
| 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Try :
LOAD DATA INFILE 'languageSettings.sql' INTO TABLE language_settings FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

I think MySQL 5 is still in development version so try in 4 and see if it works
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

The problem was that the file was saved in UTF8 format and it contained a BOM (Byte Order Mark) at the beginning.
MySQL wasn't able to understand the BOM, and so it disregarded the first row.

The solution? Just delete the BOM in your favorite editor
Post Reply