mariusg - 19 November 2010 06:29 AM
1. we have ICE v.3.3.1 running on a server
2. we have ICE v 3.4.2 installed on a CEntOS 5 - x86_64
3. we need to migrate a DB from the first server to the second one
We’ve started by SELECT INTO OUTFILE with a limit of 10M (10,000,000) rows.
Then tried to LOAD DATA INFILE on the second server
First chunk loaded fine. The second returns the error mentioned in this thread. We’re stuck.
mariusg,
Can I assume that the DDL for the tables on the different servers is the same, meaning that lookups are defined on the same columns in both databases? If so, it’s possible that the table in 3.3.1 was loaded in multiple increments instead of a single large load (or loads) as you are attempting in 3.4.2, and not experienced the condition I describe below.
The lookup issue could appear here if the cardinality (uniqueness) of any of the columns defined with lookups exceeds 10,000. If cardinality exceeds 10,000, you will experience slow loads and unpredictable load behavior. If cardinality exceeds 50,000, the loader has been known to crash during load execution.
To determine cardinality for a given column, on 3.3.1 run
SELECT COUNT(DISTINCT colname)
FROM table;
If it’s greater than 10,000, remove the lookup from that column on the table in 3.4.2. (You cannot later alter the column to add the lookup.)
Also, the uniqueness of the column’s values should be 10% or less of the total number of values in the column (= records in the table). You can determine this by comparing the cardinality from above against the total number of values returned from:
SELECT COUNT(colname)
FROM table;
Bear in mind, that this measurement and decision is made at a point in time. Lookups are best used when there is a finite range of possible values that meets the cardinality recommendation - states or provinces in a country, gender, product codes, status codes, etc. They are not appropriate for columns where the cardinality is initially low but grows over time - names, cities, free-form text fields, etc. - if the total table size will exceed 10,000 records.
And another thing, in general, the lower the cardinality the better.