#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    198
    Rep Power
    3

    Is there an alternative


    Hi, i have a script that runs once a month on my site which updates our database with GEO co-ordinates. This dump contains 100K rows of data.

    This all worked fine on my development server, however i have just found out that my hosting company not support LOAD DATA INFILE on their cloud databases for security reasons.

    Does anyone know if there is an alternative way to do this update as inserting the data normally, without LOAD DATA INFILE, takes about 5 minutes.

    Thanks in advance for your help...
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Do they allow phpmyadmin or anything? That product has a functionality similar to load data infile, though it's slower, as you've found.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    198
    Rep Power
    3
    Hi, thanks for your reply... i wanted to keep it automatic and not use a manual approach
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    How were you doing it automatically with load data infile? You had a cron?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    198
    Rep Power
    3
    Originally Posted by ManiacDan
    How were you doing it automatically with load data infile? You had a cron?
    Yes i was using a cron.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    You can front-load the data using your development machine, combining LOAD DATA INFILE with mysqldump to load the data and then dump it to a remote host.

    But that's kind of dumb.

    Ask your host if they have a solution, since they disabled the real one.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    You could write a script to convert the dump into SQL statements and then execute those against the database using the mysql command line client. That is probably the second fastest way of loading the data.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    198
    Rep Power
    3
    Originally Posted by E-Oreo
    You could write a script to convert the dump into SQL statements and then execute those against the database using the mysql command line client. That is probably the second fastest way of loading the data.
    Thanks E-Oreo, can you elaborate on your method... no need for code, but just describe what you mean in simpler terms
  16. #9
  17. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I'm assuming your dump file is something like CSV. You could write a PHP script to convert that each line of the CSV file into a SQL INSERT statement, and you can put all of the INSERT statements into a file. Then you can upload the file to the server, and using the mysql command line client you can run the file against the database using a command like:
    Code:
    mysql -u user --password="password" database < filename.sql
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    198
    Rep Power
    3
    Thanks E-Oreo,

    I have decided to use the following approach:

    1. Go to MaxMind and get the data
    2. UnZip the data into a local directory
    3. Create a new table on the data base
    4. Load the new table by reading from the unzipped file
    5. Lock the tables
    6. Drop (or rename) the old table
    7. Rename the newly loaded table
    8. Unlock the tables
    9. Lather, rinse, repeat
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0
    This may be over-simplifying things, but have you thought of running your script in shorter increments? Instead of 100K rows every month, maybe 25k a week?

IMN logo majestic logo threadwatch logo seochat tools logo