Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MASSIVE mysql import ! problems

Options
  • 03-07-2007 9:32am
    #1
    ChicoMendez
    Registered Users Posts: 648 ✭✭✭


    hi
    i have a table i need to import into my server mysql db- the table has close to 3 million entries.

    currently i am using :
    http://www.ozerov.de/bigdump.php
    but the problem here is after a few hundred thousand entries it falls over with an error like this


    =======================
    Error at the line 80001: INSERT INTO `jos_places` VALUES (79965, 'ao', 'quissanga', 'Quissanga', '05', '-7.9833333', '15.2833333\n', 0, '');

    Query: INSERT INTO `jos_places` VALUES (79965, 'ao', 'quissanga', 'Quissanga', '05', '-7.9833333', '15.2833333\n', 0, '');

    MySQL: Duplicate entry '79965' for key 1
    =======================

    does anyone have a good solution for importing a db table of that size ?


    tnx


Comments

  • Options
    #2
    masterclass
    Closed Accounts Posts: 49 ✭masterclass


    sent you a pm


  • Options
    #3
    mneylon
    Registered Users Posts: 7,739 ✭✭✭mneylon


    Do it from the command line ...

    Blog | Tweets



  • Options
    #4
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    Are you recreating the table yourself from scratch?

    The error you post suggests that the value 79965 already exists in the database - you're attempting to insert it into the column that's the primary key, but that value has already been inserted.

    This would suggest to me that you've made a column in the new table a primary key, where it wasn't one in the old table. That or you're not doing a
    "TRUNCATE `jos_places`" before the import (this empties the table of all values).


  • Options
    #5
    jmcc
    Registered Users Posts: 7,412 ✭✭✭jmcc


    ChicoMendez wrote:
    =======================
    Error at the line 80001: INSERT INTO `jos_places` VALUES (79965, 'ao', 'quissanga', 'Quissanga', '05', '-7.9833333', '15.2833333\n', 0, '');

    Query: INSERT INTO `jos_places` VALUES (79965, 'ao', 'quissanga', 'Quissanga', '05', '-7.9833333', '15.2833333\n', 0, '');

    MySQL: Duplicate entry '79965' for key 1
    =======================

    does anyone have a good solution for importing a db table of that size ?
    It looks like you are importing data into a table schema that differs from the original. Something that should be unique is not. Dump the schema from both servers and check the difference first. Did you generate these sql statements or are they dumps from existing tables?

    The duplicate entry could be for a field that is set to auto_increment. If you are not cleaning the data from the table on the new server, and data exists with that id element, the load will fail.

    Also for fast dumps, use the -e flag on mysqldum. If you are happy enough with the original schema and it won't break anything on the other server, you can use --add-drop-table as flag to mysqldump. This will have the effect of creating a new table schema on the new server. Without knowing whether this is a partial update or a new set of data, it is difficult to recommend anything else.

    Regards...jmcc


Advertisement