Best practice: Import mySQL file in PHP; split queries

Question

I have a situation where I have to update a web site on a shared hosting provider. The site has a CMS. Uploading the CMS's files is pretty straightforward using FTP.

I also have to import a big database file (Around 2-3 MB uncompressed). Mysql is closed for access from the outside, so I have to upload a file using FTP, and start a PHP script to import it. Sadly, I do not have access to the mysql command line function so I have to parse and query it using native PHP. I also can't use LOAD DATA INFILE. I also can't use any kind of interactive front-end like phpMyAdmin, it needs to run in an automated fashion. I also can't use mysqli_multi_query().

Does anybody know or have a already coded, simple solution that reliably splits such a file into single queries (there could be multi-line statements) and runs the query. I would like to avoid to start fiddling with it myself due to the many gotchas that I'm likely to come across (How to detect whether a field delimiter is part of the data; how to deal with line breaks in memo fields; and so on). There must be a ready made solution for this.

EDIT: Thanks for your input. I left a comment to most answers explaining which answer I accepted and why.

Answer 1

Can't you install phpMyAdmin, gzip the file (which should make it much smaller) and import it using phpMyAdmin?

EDIT: Well, if you can't use phpMyAdmin, you can use the code from phpMyAdmin. I'm not sure about this particular part, but it's generaly nicely structured.

Answer 2

Can you use LOAD DATA INFILE?

If you format your db dump file using SELECT INTO OUTFILE, this should be exactly what you need. No reason to have PHP parse anything.

Answer 3

Already answered: http://stackoverflow.com/questions/147821/loading-sql-files-from-within-php Also:

• http://tinyurl.com/y8ddqr2
• http://webxadmin.free.fr/article/import-huge-mysql-dumps-using-php-only-342.php
• http://www.phpbuilder.com/board/showthread.php?t=10323180
• http://forums.tizag.com/archive/index.php?t-3581.html

Answer 4

Do these links help :
http://www.ozerov.de/bigdump.php
http://www.wanderings.net/notebook/Main/HowToImportLargeMySQLDataFiles

Answer 5

Hi.. what do you think about:

system("cat xxx.sql | mysql -l username database"); 

Answer 6

I suppose that your data file always the same structure...

If so, you can use a regex to transform your data : create a "update_data.php" which contains all your commands.

When you try to execute it, it does the following :

1. Look for your uploaded data file
2. Transform your data into a PHP file with regex

Those previous operation are done once per input file.

1. Connect to the DB
2. Include the PHP file you've just created
3. Disconnect from DB

*Eventually, you can check store the last executed query, so you can be able to split the execution of your load (depending on the max_execution_time value and the time needed to load the whole data).*

Here's a sample data file :

1;admin;1234;[email protected]
2;test;1244;[email protected]
3;user;10;[email protected]

and you create an array from this data :

// First part (points 1 & 2)
// Check that your data file have already been transformed
$data = file_get_contents('datafile.txt');
preg_match_all('YOUR_REGEX_HERE', $data, $matches );
$export_data = var_export( $matches, true);
file_put_contents('php_file.php', $export_data);
// ... initialize last included line in DB (write in a file ?)

// Second part (points 3, 4 & 5)
include 'php_file.php';
// Check last inserted line into DB (read from a file ?)
// Connect to DB
// Loops through data
//   get back data from the "$matches" variable
//   insert data
//   update last included line

Answer 7

Here is a memory-friendly function that should be able to split a big file in individual queries without needing to open the whole file at once:

function SplitSQL($file, $delimiter = ';')
{
    set_time_limit(0);

    if (is_file($file) === true)
    {
        $file = fopen($file, 'r');

        if (is_resource($file) === true)
        {
            $query = array();

            while (feof($file) === false)
            {
                $query[] = fgets($file);

                if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1)
                {
                    $query = trim(implode('', $query));

                    if (mysql_query($query) === false)
                    {
                        echo '<h3>ERROR: ' . $query . '</h3>' . "\n";
                    }

                    else
                    {
                        echo '<h3>SUCCESS: ' . $query . '</h3>' . "\n";
                    }

                    while (ob_get_level() > 0)
                    {
                        ob_end_flush();
                    }

                    flush();
                }

                if (is_string($query) === true)
                {
                    $query = array();
                }
            }

            return fclose($file);
        }
    }

    return false;
}

I tested it on a big phpMyAdmin SQL dump and it worked just fine.

---

Some test data:

CREATE TABLE IF NOT EXISTS "test" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT,
    "description" TEXT
);

BEGIN;
    INSERT INTO "test" ("name", "description")
    VALUES (";;;", "something for you mind; body; soul");
COMMIT;

UPDATE "test"
    SET "name" = "; "
    WHERE "id" = 1;

And the respective output:

SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT );
SUCCESS: BEGIN;
SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul");
SUCCESS: COMMIT;
SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1;

Answer 8

When StackOverflow released their monthly data dump in XML format, I wrote PHP scripts to load it into a MySQL database. I imported about 2.2 gigabytes of XML in a few minutes.

My technique is to prepare() an INSERT statement with parameter placeholders for the column values. Then use XMLReader to loop over the XML elements and execute() my prepared query, plugging in values for the parameters. I chose XMLReader because it's a streaming XML reader; it reads the XML input incrementally instead of requiring to load the whole file into memory.

You could also read a CSV file one line at a time with fgetcsv().

If you're inporting into InnoDB tables, I recommend starting and committing transactions explicitly, to reduce the overhead of autocommit. I commit every 1000 rows, but this is arbitrary.

I'm not going to post the code here (because of StackOverflow's licensing policy), but in pseudocode:

connect to database
open data file
PREPARE parameterizes INSERT statement
begin first transaction
loop, reading lines from data file: {
    parse line into individual fields
    EXECUTE prepared query, passing data fields as parameters
    if ++counter % 1000 == 0,
        commit transaction and begin new transaction
}
commit final transaction

Writing this code in PHP is not rocket science, and it runs pretty quickly when one uses prepared statements and explicit transactions. Those features are not available in the outdated mysql PHP extension, but you can use them if you use mysqli or PDO_MySQL.

I also added convenient stuff like error checking, progress reporting, and support for default values when the data file doesn't include one of the fields.

I wrote my code in an abstract PHP class that I subclass for each table I need to load. Each subclass declares the columns it wants to load, and maps them to fields in the XML data file by name (or by position if the data file is CSV).

Answer 9

If its only 2-3 Mb you can just run through them and do a mysql_query on each one, it should not take to long. If they are separated by a comma here is the code:

$file = file_get_contents('file.txt');
$queries = explode(",", $file);
foreach($queries as $query){
    mysql_query($query);
}

Answer 10

Single page PHPMyAdmin - Adminer - Just one PHP script file. check : http://www.adminer.org/en/

Answer 11

I ran into the same problem. I solved it using a regular expression:

function splitQueryText($query) {
    // the regex needs a trailing semicolon
    $query = trim($query);

    if (substr($query, -1) != ";")
        $query .= ";";

    // i spent 3 days figuring out this line
    preg_match_all("/(?>[^;']|(''|(?>'([^']|\\')*[^\\\]')))+;/ixU", $query, $matches, PREG_SET_ORDER);

    $querySplit = "";

    foreach ($matches as $match) {
        // get rid of the trailing semicolon
        $querySplit[] = substr($match[0], 0, -1);
    }

    return $querySplit;
}

$queryList = splitQueryText($inputText);

foreach ($queryList as $query) {
    $result = mysql_query($query);
}

Answer 12

You can use phpMyAdmin for importing the file. Even if it is huge, just use UploadDir configuration directory, upload it there and choose it from phpMyAdmin import page. Once file processing will be close to the PHP limits, phpMyAdmin interrupts importing, shows you again import page with predefined values indicating where to continue in the import.

Answer 13

Export

The first step is getting the input in a sane format for parsing when you export it. From your question it appears that you have control over the exporting of this data, but not the importing.

~: mysqldump test --opt --skip-extended-insert | grep -v '^--' | grep . > test.sql

This dumps the test database excluding all comment lines and blank lines into test.sql. It also disables extended inserts, meaning there is one INSERT statement per line. This will help limit the memory usage during the import, but at a cost of import speed.

Import

The import script is as simple as this:

<?php

$mysqli = new mysqli('localhost', 'hobodave', 'p4ssw3rd', 'test');
$handle = fopen('test.sql', 'rb');
if ($handle) {
    while (!feof($handle)) {
        // This assumes you don't have a row that is > 1MB (1000000)
        // which is unlikely given the size of your DB
        // Note that it has a DIRECT effect on your scripts memory
        // usage.
        $buffer = stream_get_line($handle, 1000000, ";\n");
        $mysqli->query($buffer);
    }
}
echo "Peak MB: ",memory_get_peak_usage(true)/1024/1024;

This will utilize an absurdly low amount of memory as shown below:

daves-macbookpro:~ hobodave$ du -hs test.sql 
 15M    test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 1.75
real    2m55.619s
user    0m4.998s
sys 0m4.588s

What that says is you processed a 15MB mysqldump with a peak RAM usage of 1.75 MB in just under 3 minutes.

Alternate Export

If you have a high enough memory_limit and this is too slow, you can try this using the following export:

~: mysqldump test --opt | grep -v '^--' | grep . > test.sql

This will allow extended inserts, which insert multiple rows in a single query. Here are the statistics for the same datbase:

daves-macbookpro:~ hobodave$ du -hs test.sql 
 11M    test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 3.75
real    0m23.878s
user    0m0.110s
sys 0m0.101s

Notice that it uses over 2x the RAM at 3.75 MB, but takes about 1/6th as long. I suggest trying both methods and seeing which suits your needs.

Edit:

I was unable to get a newline to appear literally in any mysqldump output using any of CHAR, VARCHAR, BINARY, VARBINARY, and BLOB field types. If you do have BLOB/BINARY fields though then please use the following just in case:

~: mysqldump5 test --hex-blob --opt | grep -v '^--' | grep . > test.sql