MySQL batch insert fails, no error

Question

I'm trying to create a php function to split up the data into batches as it fails when I try to insert them fairly quickly.

I'm trying to insert thousands of records of user-data into a different format in the same database, later to be exported to a seperate database. However the query fails.

Based on comments and answers below I've updated the code to the following. Still fails, though.

The code inserting values:

        function insertUsers( $users ){
            error_reporting(E_ALL);
            ini_set('display_errors',1);

            global $pdo;
            //insert into database
            $i = 0;
            $base = 'INSERT INTO tth_user_accounts (user_login, user_pass, user_email, user_registered,
                      user_firstname, user_lastname ) VALUES ';
            $sql = '';

            var_dump($users);

            while( $i < count( $users ) ){
                $sql = $sql . ' ("' .
                    $users[$i]['user_login'] . '", "' .
                    $users[$i]['user_pass'] . '", "' .
                    $users[$i]['user_email'] . '", "' .
                    $users[$i]['user_registered'] . '", "' .
                    $users[$i]['meta_value']['first_name'] . '", "' .
                    $users[$i]['meta_value']['last_name'] . '")';

                if (!( $i % 25 === 0 )){
                    $sql = $sql . ', ';
                }

                if ($i % 25 === 0) {
                    //execute $base + $query here
                    $sql = $base . $sql;

                    $query = $pdo->prepare( $sql );
                    echo 'check query: <br />';
                    print_r( $query );

                    if( $query->execute() ){
                        echo '50 users succesfully added to the database';
                    } else {
                        echo 'Query failed: ';
                        print_r( $pdo->errorInfo() );
                        echo '<br />';
                    }
                    $sql = ''; //Re-init query string
                }
                $i++;
            }

            if ( strlen( $sql ) > 0 ) {  // Execute remainder, if any
                //execute $base + $query here
                $sql = $base . $sql;

                $query = $pdo->prepare( $sql );
                echo 'check query: <br />';
                print_r($query);

                if( $query->execute() ){
                    echo 'User succesfully added to the database';
                } else {
                    echo 'Query failed: ';
                    print_r( $pdo->errorInfo() );
                    echo '<br />';
                }
            }
        }

check query: PDOStatement Object ( [queryString] => INSERT INTO tth_user_accounts (user_login, user_pass, user_email, user_registered, user_firstname, user_lastname ) VALUES ("John Smith", "4\/\/350M3 P4sS\/\/0r|)", "john.smith@greatmail.com", "2013-04-11 11:18:58", "John", "Smith") )

Query failed: Array ( [0] => 00000 [1] => [2] => )

Tried it with a %25 and %50, both don't work. Keep getting the 00000 error which is supposed to lead to victory (success, though for me it still fails, nothing in the DB)

I'd do it manually if I had the time but this won't be a one-time event so I need a solution to this issue. Is there a good way to split up the query into batches (and how?) that would allow this to be repeated and queries to be executed one after the other? I've been looking at a whole bunch of questions on SO (and elsewhere) already and can't find one that suits my needs.

UPDATE - has been answered, need a small modification as shown below:

if (!( $i % 25 === 0 )){
    if(!( $i == ( count( $users ) - 1 ))){
        $sql = $sql . ', ';
    }
}

Answer 1

You're mixing PDO with mysql_ functions. Pick one and follow the respective library's error handling.

It would also be beneficial print out your $sql to yourself to see if it's formatted correctly. Additionally, if you're handling POSTed data, you will want to use prepared statements,

Answer 2

You might need to increase the max_allowed_packet value which defaults to 1Mb. If you want to split up the query in batches you can do so using the modulus operator.

$base = 'INSERT INTO ...';
$sql = '';
while( $i < count( $users ) ){
    $sql = $sql . ' ("' ... //etc.
    if ($i % 50 === 0) {
        //execute $base + $qry here
        $sql = ''; //Re-init query string
    }
}
if (strlen($qry)>0) {  // Execute remainder, if any
    //execute $base + $query here
}

Or as an array (described here):

$base = 'INSERT INTO ...';
$sql = array();
while( $i < count( $users ) ){
    $sql[] = ' ("' ... //etc.
    if ($i % 50 === 0) {
        //execute $base + implode(',', $sql) here
       $sql = array(); //Re-init query string
    }
}
if (sizeof($qry)>0) {  // Execute remainder, if any
    //execute $base + implode(',', $sql) here
}

Also please make sure you're using prepared statements correctly so you're not vulnerable to SQL injections.

Finally: you might need to enable error reporting so failures won't be silent; if they're still silent after enabling error reporting (e.g. error_reporting(-1);) you might need to set MySQL to strict mode (not sure if that will help). If they still fail silently file a bugreport.

Edit Oh, I missed the fact that you're mixing mysql_ and PDO; that will probably be the reason why you're not seeing any errors... D'uh. Go read the manual on PDO error handling.

@Downvoters: If you're downvoting at least have the decency to leave a comment on why.

Answer 3

Is there a reason why you want to do it as one single statement? Why not just iterate through the users and insert each one with something like:

while($i < count( $users )) {

    $sql = 'INSERT INTO tth_user_accounts (user_login, user_pass, user_email, user_registered, user_firstname, user_lastname ) VALUES ';

    $sql = $sql + "(";
    $sql = $sql + "'" . $users[$i]['user_login'] . '",';
    $sql = $sql + "'" . $users[$i]['user_pass'] . '",';
    $sql = $sql + "'" . $users[$i]['user_email'] . '",';
    $sql = $sql + "'" . $users[$i]['user_registered'] . '",';
    $sql = $sql + "'" . $users[$i]['meta_value']['first_name'] . '",';
    $sql = $sql + "'" . $users[$i]['meta_value']['last_name'] . '"';
    $sql = $sql + ")";


    $query = $pdo->prepare( $sql );

    if( $query->execute() ){
        echo 'User succesfully added to the database';
    } else {
        die ("Query failed: " . $pdo->errorInfo());
    }
}

Even better is to prepare the statement once and then bind the parameters. As pointed out by RobIII, you can introduce SQL injection vulnerabilities by building up your SQL statements as strings so instead, you could do something like:

$sql = 'INSERT INTO tth_user_accounts (user_login, user_pass, user_email, user_registered, user_firstname, user_lastname) ';
$sql += ' VALUES (:user_login, :user_pass, :user_email, :user_registered, :user_firstname, :user_lastname)';

$query = $pdo->prepare( $sql );

while ($i < count($users)) {

    $query->bindParam(":user_login", $users[$i]['user_login']);
    $query->bindParam(":user_pass", $users[$i]['user_pass']);
    $query->bindParam(":user_email", $users[$i]['user_email']);
    $query->bindParam(":user_registered", $users[$i]['user_registered']);
    $query->bindParam(":user_firstname", $users[$i]['user_firstname']);
    $query->bindParam(":user_lastname", $users[$i]['user_lastname']);

    if( $query->execute() ){
        echo 'User succesfully added to the database';
    } else {
        die ("Query failed: " . $pdo->errorInfo());
    }
}

Answer 4

Another issue you may be encountering is a simple fact that the user's name / information may contain invalid characters and killing your command. Take for instance a person's last name is "O'Maley". When building your string of values and wrapping the name with hard quotes, you would get

'O'Maley'

I ran into something similar to this about 8 years ago and had to validate certain values within the data. Once you find it, you'll know how to correct each respective value, then go back to batch mode.

Also, consider someone supplying a bogus value of a bogus / bad value of "--" which indicates rest of line is a comment... or even a ";" to indicate end of statement. That is most likely what you are running into.

As RobIII responded, and I was too quick in my response, don't test one at a time, but query the data an look at it for such anomolies / bad escape posts in the data. Fix what you need to BEFORE trying the insert.