Hi Marius,

Apologies for the delayed response.  I appreciate the need for confidentiality; can you post your a ‘describe table’ for the table which is having issues?  If you’d like, you can change the name of the table/columns to better mask everything.

Also, what charset is used on the new table?  Perform a ‘show table status’ and send the output (removing anything that you’d like to keep hidden).

Cheers,

Jeff

Hi Jeff,

Luckily, I have figured out the problem in another thread:
http://www.infobright.org/Forums/viewthread/1797/

It is a bit weird to me why the first LOAD DATA INFILE worked and any of the subsequent ones didn’t (even if I used smaller files as described at the beginning of this thread). I simply DROP DATABASE, then CREATE DATABASE… using a DEFAULT CHARSET identical to the one used in CREATE TABLE, and it worked.

Cheers,
marius

Hi Marius,

Thanks for responding so quickly.  Can you tell me what the different character sets were?  From what I understand, the issue shouldn’t exist, so I’m going to take a deeper look.

Cheers,

Jeff

Hi Jeff,

Back in March we begun investigating InfoBright as DB engine, so we used ICE3.3.1 Later we tried the same with production grade servers, and we installed ICE2.4.2. The problems started with the migration. Three weeks ago we’ve purchased IEE and installed IEE3.4.2 hoping the problems were solved. Unfortunately this was not the case. Currently we’re staging our DB on IEE on the first (desktop grade) machine we used initially, so we had to uninstall ICE3.3.1. This is why I cannot tell the original charset/collation used.

On the currently installed ICE we have in my-ib.cnf:
default-storage-engine=brighthouse
collation_server=latin1_bin
character_set_server=latin1
On the currently installed IEE we have the same.

The tables have
ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8

Our tests show the following (with the above setup / ini file):
a) if you create a database w/o specifying the charset used, and then create a table with a charset like above
The first (no matter the size or number of rows) LOAD DATA INFILE works, while the subsequent fail
b) if you dcreate a database with DEFAULT CHARSET=utf8, and then create a table with the same charset
All LOAD DATA INFILE work

To my knowledge, charset specification at table creation is used to specify a different charset from the default DB one.
Also, while creating a DB, the charset used by default is the one in INI file.
It seems to me that there’s a bug in ICE/IEE where charset specification at table level is not observed at some point (first data loading is fine while the subsequent fail). Unfortunately, the error returned by the loader is not descriptive enough, so I cannot tell at which line (or field) the loader gae up with an error.

If you really need a set of commands and data that shows this, we can try to provide some (a minimal table definition, and a minimal set of CSV files). Just let me know, and we’ll try to provide them in due time.

Much obliged for trying to help us,
marius

Hi Marius,

Excellent description—I really appreciate the verbose and descriptive response.  If you can provide us your list (to ensure we can duplicate), I’d really appreciate it.  If you can’t, I can try to spend some time duplicating, but it’ll be faster if you have something you can chunk over the wall.  If it’s too much work, let me know.

Also, please feel free to reach out to me on skype if you have anything else you notice—my skype name is jeff.at.infobright.  You can also e-mail me at (JavaScript must be enabled to view this email address).

Cheers (and happy new years!)

Jeff

Hi Jeff,

Any news regarding this from your side?
Please notice that we’re struggling to find workarounds for this with both ICE and IEE.
Right now, with IEE v3.4.2 it’s a nightmare: the CHARACTER SET at CREATE DATABASE does not help (although it works for ICE same version). The first LOAD DATA INFILE after DB creation works like a charm. However, any subsequent loads fail with “Unknown error”. The workaround we’ve tried is to add “CHARACTER SET utf8” before “FIELDS…” in the load statement. However this is not documented anywhere and I fear this may not work in future versions.
The current workaround with IEE is to change in my-ib.cnf:
collation_server=latin1_bin
character_set_server=latin1
to
collation_server=utf8_general_ci
character_set_server=utf8

best,
marius

Hi Marius,

Take a look at the just released 3.5.2.  It has addressed a lot of bug fixes, and I believe some focused on charsets.  If 3.5.2 doesn’t take care of the issue, then let’s file a bug.  Feel free to skype me or e-mail if you’re running into any issues.

Cheers,

Jeff