I cannot import into mySQL correctly-help please

[jeffmic]

I spent the entire day Saturday trying to get some data imported into mySQL under the <products-categories> table. My data is origination from Filemaker, so my first attempt was to use "fmSQL synch". As I await the registration keys, I couldn't get things to work right.

Now moving on, I then took the 2 fields that are needed for <products-categories> from Filemaker and exported them as a CSV file and imported it with Navicat (on OSX server).
This is where things go real strange for me. I received tons of errors on Key 1.

Here's how it went:

The first part of the csv file looks like this (with over 20,000 total records)
"10000010107","96"
"20000010107","113"
"10000010117","96"
"20000010117","113"
"10000020104","96"
"20000020104","113"
"10000020110","96"
"20000020110","113"
"10000030101","96"
"20000030101","113"
"10000030102","96"
"20000030102","113"
"10000030103","96"
"20000030103","113"
"10000030104","96"
"20000030104","113"
"10000030105","96"
"20000030105","113"
"10000030106","96"
"20000030106","113"
etc.


Each time this was imported, I would get the first record, and then I would get this error in the log in Navicat (and other mySQL editors also)

Import log (2006-09-17 08:44:36)
Row2 - Duplicate entry '2147483647' for key 1
Row3 - Duplicate entry '2147483647' for key 1
Row4 - Duplicate entry '2147483647' for key 1
Row5 - Duplicate entry '2147483647' for key 1
Row6 - Duplicate entry '2147483647' for key 1
Row7 - Duplicate entry '2147483647' for key 1
Row8 - Duplicate entry '2147483647' for key 1
Row9 - Duplicate entry '2147483647' for key 1
Row10 - Duplicate entry '2147483647' for key 1
Row11 - Duplicate entry '2147483647' for key 1
Row12 - Duplicate entry '2147483647' for key 1
Row13 - Duplicate entry '2147483647' for key 1
Row14 - Duplicate entry '2147483647' for key 1
Row15 - Duplicate entry '2147483647' for key 1
etc.

Where is this 2147483647 number coming from?????
Does this have to do with 11 digit numbers? I changed the field to now have 20 digits, but it still won't work.
I'm sure this has to do with the Primary key. Both fields in this table have a primary key. I deleted the key on the second field and now I cannot put it back (oops) I have a section that is only 8 digits from 16,000- to the end and it worked in the first tests, but now that I screwed up by removing the second primary key item in Navicat and that I can't put it back, it now errors like the 11 digit numbers.

I'm so confused (and tired) and I appreciate any help (or further questions) on this and how to properly import my data. I started with a small table with only 2 fields to test the importing, but I have to import into 10 different tables and the attributes table will have 286,000 records to import!!

Thanks so much from the 'team' and I look forward to a helpful solution to my problem and my 'wasted' Saturday trying to do this.

Jeff Michaels
Musical Creations Ltd.

[kuroi]

This looks like a type mismatch to me. You're product and category IDs are being exported to the CSV file with quotes around them, making them strings. However the products_to_categories table needs them to be integers ......

[jeffmic]

I discovered one fix.

If I change the product type to bigint, it imports an 11 digit number perfectly.
If I leave it at int, it imports digits of 10 and under correctly.

By changing all the 11 digit types to bigint, will it screw something else up?

thanks,
Jeff Michaels

[kuroi]

Sounds like it's trying to make something of the leading quotes that shouldn't be there. Theoretically I'm not aware of any reason why changing these to big ints wouldn't work, but it's untested, may lead to problems upgrading and would swell what's already going to be a big database. Why not try getting rid of the offending quotes first?