mysqli_query returns false with error code 0, but query succeeds

Question

I have a huge amount of data that is generated from a PHP script and needs to be inserted into a database. I've tried various solutions with different results, but the current solution (and the one I think should be the best) is that i generate the data into a CSV file and then inserts it into the database with the following query:

LOAD DATA LOCAL INFILE 'myfile.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"

I'm using CodeIgniter as the PHP framework, and after the query has executed im redirected to an error page which only says

A Database Error Occurred

Error Number: 0

There is no error message or anything.

I've stepped through the code but all I can find is that mysqli_query() returns false and later on mysqli_errno() returns 0 and mysqli_error() returns an empty string.

However, the query has actually succeeded and when I look in the database i can see that all the data from the CSV file have successfully been inserted. Is this behaviour to be expected? If so, I guess I have to hack the CodeIgniter code a little, or call mysqli_query() directly instead of going through the framework.

I've also run the exact same query in MySQL Workbench, and I do not get an error message there.

no, I mean the full code block including the part where you output the error message. You can edit it into the question *Edit*: Ah, I see you are using CodeIgniter which probably makes things a bit more difficult. Still, try posting what you can — Pekka, Jan 13 '11 at 15:02
The code is not important, I've stepped through the code with XDebug and can see that the return values from the mysql functions in question return the above values. CodeIgniter itself shows an error page that just says Error Code: 0 Im just wondering why mysqli_query() returns false with an error code of 0, when the actual query has succeeded — Johan, Jan 13 '11 at 15:07
As the redirecting is not the default behaviour of codeigniter, the code may still be interesting — Dr.Molle, Jan 13 '11 at 20:53

score 2 · Answer 1 · edited Nov 21 '12 at 14:08

2

I had the exact same issue, when I realized I was not connected to the database.

I was calling require_once('connect.php'), but this was the second time in the code that I used the require_once, so PHP did not pull in the connection.

Changing the require_once to require fixed this issue for me.

edited Nov 21 '12 at 14:08

Stephan

41,764
65
238
329

answered May 22 '11 at 23:04

Bruce

21
2

score 0 · Answer 2 · answered Jan 13 '11 at 15:11

I have never tried a LOAD INFILE from php before, but a quick google came up with the function mysqli_set_local_infile_handler() . Perhaps the examples/comments might help?

Edit: My theory is the query expects a number of rows return value, and the connection isn't returning any numbers. So while the query is successful on MySQL's end, PHP expects a number > 0 but doesn't get it. So it reports a failure, with no error message.

The infile_handler would return the number of rows inserted.

I did a quick test but after calling mysqli_set_local_infile_handler() the script just dies. I might have missed something — Johan, Jan 13 '11 at 15:31

score 0 · Answer 3 · answered Dec 22 '22 at 11:28

Error (0) at some instances may just be misleading. For example, when returned during a mysqli update, it might simply mean the changes were done but compared to the last records, nothing actually changed. This could be the issue you are facing. An example with mysqli update is shown below:

$xCount = 0;        //will monitor if changes were done
$updateFlag = false;
$xMsg = '';
$dbx = $this->dbx;
$dbx->select_db("dbname");
$query = "UPDATE xxx_table SET NAME = ?, GENDER = ? WHERE ORG_ID = ? AND EMPID = ?";
$statement = $dbx->prepare($query); 
$statement->bind_param('ssss', $flname, $gender, $orgId, $empId);
if($statement->execute()){
    $xCount = $statement -> affected_rows;  //real-check if changes occured
    if($xCount > 0){
        $updateFlag = true; 
        $xMsg = 'Employee Data Successfully Updated';
    }else {
        //if no changes occur, like same values, this will run
        //in this particular case, your may treat as fine 
        $updateFlag = true; 
        $xMsg = "Successful With No Major Changes";
    }       
} else {
    //this is simply your coding error;
    //die('Error : ('. $dbx->errno .') '. $dbx->error); 
    $updateFlag = false;
    $xMsg = 'Internal Error On Employee Logs';
}
$statement->close();

I hope this helps on a related problem, may be not this particular one.

mysqli_query returns false with error code 0, but query succeeds

3 Answers3