Solved

Import very large text file into mysql using php

Posted on 2011-03-13
9
656 Views
Last Modified: 2012-05-11
Hello experts,

Background:
Everynight an automated process runs on the internal server and creates a zip file uploaded to the web server. I have written a script which unzips the file, and imports each txt file contained within to a mysql database. There are 4 files, 3 small(ish) files of upto 10MB and containing 50,000 rows. These import fine.

Problem:
1 file is 1.5GB and contains 27,000,000 rows. I am using the code below to import the file, but it errors out at around 5 minutes with a server 500 error which means nothing to me.
mysql_query("TRUNCATE TABLE `accountproducts_schema`;") or die(mysql_error());
mysql_query("LOAD DATA LOCAL INFILE '/home/wgoffice/public_html/FilesToImport/wgo_accountproducts_replace".$fn.".txt' INTO TABLE `accountproducts_schema` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';") or die(mysql_error());

Open in new window


If I open the file and pull off 1 milltion rows and write them to a seperate file, it imports fine. So obviously it is a size issue. Any ideas how I can solve it? I know I could sit here at 3am every morning and split the file, but thats not the ideal solution! :)

Many thanks
Jim
0
Comment
Question by:dovercomputers
  • 5
  • 3
9 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35121660
I would not do it from PHP. I would do it from a command line like so

mysql -u username -p -D database_name  < text-file.sql

It will prompt you for the password for whatever username you are using. You may also need to incread MySQL buffer sizes and such, but I'll need to dig that one out

Using the command line like this is far, far, far faster than any other method.
0
 

Author Comment

by:dovercomputers
ID: 35121716
But I need this to be an automated process. The file gets sent up at 2am every morning so I need some kind of automation to import this file. Speed isn't so much an issue at 3am, when I split the file I calculated that it would take around 20 minutes to import the whole lot.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35121743
If you are on Linux / Mac then use a CRON job. It is specifically designed to do this kind of job. On a command line enter

crontab -e

and for 2am of every day of every week add this line

0 2 * * * mysql -u username --password=password -D databasename < mysql_text.sql

and any commands in the file mysql_text.sql will be executed. By using the --password= option you can provide the password and the system will not prompt you for it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35121750
An additional thought, on mant systems the cron line will echo into the log thus showing the password. One way to prevent this is to put the mysql command in a text file and make the file executeable with chmod +x filename and you can then run the text file from the cronjob

0 2 * * * /path/to/file/textfile.sh
0
 

Author Comment

by:dovercomputers
ID: 35121803
OK, I understand what you have written, but the text file isn't an sql formatted file. Its csv file (using  |  as a seperator) so how would I import it? I think using your method it would need to be an proper sql file, no?
0
 
LVL 5

Expert Comment

by:RizyDeWino
ID: 35121875
No , it do not need to be proper sql file/dump , you can simple add you LOAD DATA command or any other command in the text file that will be executed in the cron.

the command can take the form like :

mysql -e LOAD DATA LOCAL INFILE 'thefile' FIELDS TERMINATED BY '|' LINES .......

Similarly other commands can be added to the file in sequence as per reqirement and that file can be executed through cron as explained by others in last few replies.
0
 

Author Comment

by:dovercomputers
ID: 35121912
Sorry, having a really bad day here.

So i create a text file on the server (dataimport.txt) with my command in

mysql -e LOAD DATA LOCAL INFILE '/home/wgoffice/public_html/FilesToImport/wgo_accountproducts_replace".$fn.".txt' INTO TABLE `accountproducts_schema` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

then create a cron job 0 2 * * * /path/to/file/dataimport.txt

Is this correct?
0
 

Accepted Solution

by:
dovercomputers earned 0 total points
ID: 35122818
Couldn't really get my head around it so used the script below to break the file into smaller files then LOAD DATA LOCAL INFILE each individual file. Gives me the added advantage that any errors I can narrow down to an individual file at least. Only takes a couple of minutes to split the file up into 28 pieces so not too slow.

Thanks for trying anyway.
Jim

<?php
    // Store the line no:
    $i = 0;
    // Store the output file no:
    $file_count = 1;
    // Create a handle for the input file:
    $input_handle = fopen('../FilesToImport/wgo_accountproducts_replace.txt', "r") or die("Can't open output file.");
    // Create an output file:
    $output_handle = fopen('wgo_accountproducts_replace'.$file_count.'.txt', "w") or die("Can't open output file.");

    // Loop through the file until you get to the end:
    while (!feof($input_handle)) 
    {
        // Read from the file:
        $buffer = fgets($input_handle);
        // Write the read data from the input file to the output file:
        fwrite($output_handle, $buffer);
        // Increment the line no:
        $i++;
        // If on the 999999th line:
        if ($i==999999)
        {
            // Reset the line no:
            $i=0;
            // Close the output file:
            fclose($output_handle);
            // Increment the output file count:
            $file_count++;
            // Create the next output file:
            $output_handle = fopen('wgo_accountproducts_replace'.$file_count.'.txt', "w") or die("Can't open output file.");
        }
    }
    // Close the input file:
    fclose($input_handle);
    // Close the output file:
    fclose($output_handle);
?>

Open in new window

0
 

Author Closing Comment

by:dovercomputers
ID: 35163477
Worked out a solution myself taking an alternative approach.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question