php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #47387 Load stmt result bool false
Submitted: 2009-02-14 16:06 UTC Modified: 2009-09-02 01:00 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: lars dot a dot johansson at se dot atlascopco dot com Assigned: mysql (profile)
Status: No Feedback Package: MySQLi related
PHP Version: 5.3CVS-2009-02-14 (snap) OS: Linux mandriva 2009
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
14 - 12 = ?
Subscribe to this entry?

 
 [2009-02-14 16:06 UTC] lars dot a dot johansson at se dot atlascopco dot com
Description:
------------
Hi,
I run three simple queries i mysqli->query
1 Exec use test;
2 CREATE TABLE...;
3 LOAD DATA LOCAL INFILE...;
when I run this in 5.2.2 the result is true for all 3 queries.
when I run this in 5.3 the result for the LOAD statement is false,
but I can not find any problem with the LOAD, it loads as good as in 5.2.2.



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-02-15 08:25 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi again,
Im running mysql 5.1.30 CE.
I run my queries one by one in mysqli->query(xxx).
Running the script via mysql source stmt also works ok.
I also run other load scripts under 5.3 that runs ok.
Note using PHP 5.2.2 works as expected and differs from 5.3.
It is not easy to ship this code, its in the middle of a rather complex multitasking job control system, so if further debugging is needed it is probably easier if someone give me instructions how to debug and what to look for.
 [2009-02-17 16:21 UTC] johannes@php.net
Can you please give the configure line and check whether the file you're trying to read is properly formatted. How big is the file?

For getting more debug data you could try to compile using --with-mysqli=mysqlnd and --enable-debug and then set 
    mysqlnd.debug=t:o,/tmp/mysqlnd.trace
in your php.ini. This generates a file /tmp/mysqlnd.trace please provide that for further investigation.
 [2009-02-17 17:01 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi
I cut down the infile to one row:
F-1490;Honeywell - Crowfoot C340 et roue de sor;;31150004;0;GREA;;3000;0033012001;EUR;EUR;2;20060214;20060315;3115;ZN;4;1;TAG;DAY;ZFRN002;ZFRN001;ZFRN01;X;PR;;;;ZFRN00000001;000001;ZFN002;;;;;;;;;2061;2061

php 5.3 also gives this err msg:
PHP Warning:  mysqli::query(): (00000/0):  in /home/tooljn/dw/pgm/include/scriptEx.php on line 262

my 3 queries are generated by my PHP code and look like this:

use test;

CREATE TABLE IF NOT EXISTS PROJECT_DETAIL (`PROJECT_DEFINITION` CHAR(24),`DESCRIPTION` CHAR(40),`MASK_ID` CHAR(24),`RESPONSIBLE_NO` DECIMAL(8,0),`APPLICANT_NO` DECIMAL(8,0),`COMP_CODE` CHAR(4),`BUS_AREA` CHAR(4),`CONTROLLING_AREA` CHAR(4),`PROFIT_CTR` CHAR(10),`PROJECT_CURRENCY` CHAR(5),`PROJECT_CURRENCY_ISO` CHAR(3),`NETWORK_ASSIGNMENT` DECIMAL(1,0),`START` DATE,`FINISH` DATE,`PLANT` CHAR(4),`CALENDAR` CHAR(2),`PLAN_BASIC` DECIMAL(1,0),`PLAN_FCST` DECIMAL(1,0),`TIME_UNIT` CHAR(3),`TIME_UNIT_ISO` CHAR(3),`NETWORK_PROFILE` CHAR(7),`PROJECT_PROFILE` CHAR(7),`BUDGET_PROFILE` CHAR(6),`PROJECT_STOCK` CHAR(1),`OBJECTCLASS` CHAR(2),`STATISTICAL` CHAR(1),`TAXJURCODE` CHAR(15),`INT_PROFILE` CHAR(7),`WBS_SCHED_PROFILE` CHAR(12),`CSH_BDGT_PROFILE` CHAR(6),`PLAN_PROFILE` CHAR(6),`JOINT_VENTURE` CHAR(6),`RECOVERY_IND` CHAR(2),`EQUITY_TYPE` CHAR(3),`JV_OBJECT_TYPE` CHAR(4),`JV_JIB_CLASS` CHAR(3),`JV_JIB_SUB_CLASS_A` CHAR(5),`DELETION_FLAG` CHAR(1),`OBJECTCLASS_EXT` CHAR(5),`FUNC_AREA` CHAR(4),`FUNC_AREA_LONG` CHAR(16)

 ,PRIMARY KEY (`PROJECT_DEFINITION`));
 LOAD DATA LOCAL INFILE 'PROJECT_DEFINITION_STRU.CSV' replace INTO TABLE PROJECT_DETAIL FIELDS TERMINATED BY ';' IGNORE 0 LINES(`PROJECT_DEFINITION`,`DESCRIPTION`,`MASK_ID`,`RESPONSIBLE_NO`,`APPLICANT_NO`,`COMP_CODE`,`BUS_AREA`,`CONTROLLING_AREA`,`PROFIT_CTR`,`PROJECT_CURRENCY`,`PROJECT_CURRENCY_ISO`,`NETWORK_ASSIGNMENT`,`START`,`FINISH`,`PLANT`,`CALENDAR`,`PLAN_BASIC`,`PLAN_FCST`,`TIME_UNIT`,`TIME_UNIT_ISO`,`NETWORK_PROFILE`,`PROJECT_PROFILE`,`BUDGET_PROFILE`,`PROJECT_STOCK`,`OBJECTCLASS`,`STATISTICAL`,`TAXJURCODE`,`INT_PROFILE`,`WBS_SCHED_PROFILE`,`CSH_BDGT_PROFILE`,`PLAN_PROFILE`,`JOINT_VENTURE`,`RECOVERY_IND`,`EQUITY_TYPE`,`JV_OBJECT_TYPE`,`JV_JIB_CLASS`,`JV_JIB_SUB_CLASS_A`,`DELETION_FLAG`,`OBJECTCLASS_EXT`,`FUNC_AREA`,`FUNC_AREA_LONG`);
 [2009-02-17 17:42 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi again,
I created a smaller program 
#!/home/tooljn/PHP5.3/local/bin/php
<?php
// #!/home/tooljn/PHP/php-5.2.6/sapi/cli/php
// #!/usr/local/bin/php
// #!/home/tooljn/PHP5.3/local/bin/php
// #!/home/tooljn/PHP/php5.3-200802231330/sapi/cli/php
mysqli_report(MYSQLI_REPORT_ERROR);
$sqlhost='192.168.1.9';
$sqluser='uuuuu';
$sqlpw='pppppp';
$sqldb='test';
$port='3306';
$mysqli = new mysqli("$sqlhost", "$sqluser", "$sqlpw", "$sqldb",$port);
$result = $mysqli->query('use test;');
var_dump($result);
$result = $mysqli->query('CREATE TABLE IF NOT EXISTS PROJECT_DETAIL (`PROJECT_DEFINITION` CHAR(24),`DESCRIPTION` CHAR(40),`MASK_ID` CHAR(24),`RESPONSIBLE_NO` DECIMAL(8,0),`APPLICANT_NO` DECIMAL(8,0),`COMP_CODE` CHAR(4),`BUS_AREA` CHAR(4),`CONTROLLING_AREA` CHAR(4),`PROFIT_CTR` CHAR(10),`PROJECT_CURRENCY` CHAR(5),`PROJECT_CURRENCY_ISO` CHAR(3),`NETWORK_ASSIGNMENT` DECIMAL(1,0),`START` DATE,`FINISH` DATE,`PLANT` CHAR(4),`CALENDAR` CHAR(2),`PLAN_BASIC` DECIMAL(1,0),`PLAN_FCST` DECIMAL(1,0),`TIME_UNIT` CHAR(3),`TIME_UNIT_ISO` CHAR(3),`NETWORK_PROFILE` CHAR(7),`PROJECT_PROFILE` CHAR(7),`BUDGET_PROFILE` CHAR(6),`PROJECT_STOCK` CHAR(1),`OBJECTCLASS` CHAR(2),`STATISTICAL` CHAR(1),`TAXJURCODE` CHAR(15),`INT_PROFILE` CHAR(7),`WBS_SCHED_PROFILE` CHAR(12),`CSH_BDGT_PROFILE` CHAR(6),`PLAN_PROFILE` CHAR(6),`JOINT_VENTURE` CHAR(6),`RECOVERY_IND` CHAR(2),`EQUITY_TYPE` CHAR(3),`JV_OBJECT_TYPE` CHAR(4),`JV_JIB_CLASS` CHAR(3),`JV_JIB_SUB_CLASS_A` CHAR(5),`DELETION_FLAG` CHAR(1),`OBJECTCLASS_EXT` CHAR(5),`FUNC_AREA` CHAR(4),`FUNC_AREA_LONG` CHAR(16)

 ,PRIMARY KEY (`PROJECT_DEFINITION`));');
var_dump($result);
$result = $mysqli->query("LOAD DATA LOCAL INFILE 'PROJECT_DEFINITION_STRU.CSV' replace INTO TABLE PROJECT_DETAIL FIELDS TERMINATED BY ';' IGNORE 0 LINES(`PROJECT_DEFINITION`,`DESCRIPTION`,`MASK_ID`,`RESPONSIBLE_NO`,`APPLICANT_NO`,`COMP_CODE`,`BUS_AREA`,`CONTROLLING_AREA`,`PROFIT_CTR`,`PROJECT_CURRENCY`,`PROJECT_CURRENCY_ISO`,`NETWORK_ASSIGNMENT`,`START`,`FINISH`,`PLANT`,`CALENDAR`,`PLAN_BASIC`,`PLAN_FCST`,`TIME_UNIT`,`TIME_UNIT_ISO`,`NETWORK_PROFILE`,`PROJECT_PROFILE`,`BUDGET_PROFILE`,`PROJECT_STOCK`,`OBJECTCLASS`,`STATISTICAL`,`TAXJURCODE`,`INT_PROFILE`,`WBS_SCHED_PROFILE`,`CSH_BDGT_PROFILE`,`PLAN_PROFILE`,`JOINT_VENTURE`,`RECOVERY_IND`,`EQUITY_TYPE`,`JV_OBJECT_TYPE`,`JV_JIB_CLASS`,`JV_JIB_SUB_CLASS_A`,`DELETION_FLAG`,`OBJECTCLASS_EXT`,`FUNC_AREA`,`FUNC_AREA_LONG`);");
var_dump($result);

that gives me this output
tooljn@TOOSEWW14 projectDetails0]$ ./err53.php
PHP Warning:  mysqli::mysqli(): OK packet 6 bytes shorter than expected in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 13
PHP Warning:  mysqli::mysqli(): (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using old authentication in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 13
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 14
NULL
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 18
NULL
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 20
NULL

I have to find out why I can logon in my original program and not here. I hope I can fix this in the next comming hour.
 [2009-02-17 18:50 UTC] lars dot a dot johansson at se dot atlascopco dot com
hi again,
the logon was a red herring. I used the wrong userid.
I cannot reproduce the error with my abrevaited test program.
It works as it should.
Still my original program do not work the same in 5.3 as it did in 5.2. I do not think I have time work on this util the weekend.
 [2009-02-17 19:06 UTC] johannes@php.net
Would be great if you can narrow it down a bit more, Setting to "Feedback" for tracking this better. Thank you.
 [2009-02-18 17:28 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi,
I compiled with --enable-debug, but, I use SAPRFC in this scenario,  it does not compile with debug. And it seems PHP debug does not play with dynamic modules compiled without debug. I see if I can force this bug? some other way.
 [2009-02-24 08:35 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi, how do I use/set 'feedback'?
I have made my script a bit simpler. I can reproduce this without forking. What seems to trigger this odd behavior is the first CREATE TABLE. If I run a TRUNCATE table PROJECT_DETAIL, or a DELETE from PROJECT_DETAIL where 1=2 in between the subsequent LOAD results as it should. So I can circumvent the problem by inserting a dummy delete.
(I tried to insert a SELECT * from PROJECT_DETAIL where 1=2 in between, but that didn't work)

I do one more attempt this weekend to expose the problem.
 [2009-02-25 15:23 UTC] lars dot a dot johansson at se dot atlascopco dot com
Hi,
I can reproduce 'the bug' with attached ugly code. you can use the infile I supplied earlier. the problem seems a bit tricky.
1 $result = $mysqli->query('CREATE TABLE IF NOT EXISTS ...'
2 $result = $mysqli->query("SHOW WARNINGS", MYSQLI_USE_RESULT...
     this shows one warning:
     Note (1050): Table 'PROJECT_DETAIL' already exists
3 $result->close();
4 Now when I run $result = $mysqli->query(LOAD DATA LOCAL INFILE ...
     then $result is false and I get the warning: 
     //PHP Warning:  mysqli::query(): (00000/0): ...
     Note! this only happen in 5.3, in 5.2 $result is true.

Look in the code for the snippet that prevent the 'bug'.

Please let me know if there anything more I can do.

 
  



#!/home/tooljn/PHP5.3/local/bin/php
<?php
// #!/usr/local/bin/php
// #!/home/tooljn/PHP5.3/local/bin/php

$ok = sql2();

function sql2 () {
mysqli_report(MYSQLI_REPORT_ERROR);
$sqlhost='192.168.1.9';
$sqluser='test1tt';
$sqlpw='xxx';
$sqldb='test';
$port='3306';
$mysqli = new mysqli("$sqlhost", "$sqluser", "$sqlpw", "$sqldb",$port);
$sqlstmt = 'use test;';
$result = $mysqli->query($sqlstmt);
var_dump($result);
//if($result) $result->close();
if ($result = $mysqli->query("SELECT DATABASE()")) {
	$row = $result->fetch_row();
	$result->close();
}
var_dump($row);
$sqlstmt = 'CREATE TABLE IF NOT EXISTS PROJECT_DETAIL (`PROJECT_DEFINITION` CHAR(24),`DESCRIPTION` CHAR(40),`MASK_ID` CHAR(24),`RESPONSIBLE_NO` DECIMAL(8,0),`APPLICANT_NO` DECIMAL(8,0),`COMP_CODE` CHAR(4),`BUS_AREA` CHAR(4),`CONTROLLING_AREA` CHAR(4),`PROFIT_CTR` CHAR(10),`PROJECT_CURRENCY` CHAR(5),`PROJECT_CURRENCY_ISO` CHAR(3),`NETWORK_ASSIGNMENT` DECIMAL(1,0),`START` DATE,`FINISH` DATE,`PLANT` CHAR(4),`CALENDAR` CHAR(2),`PLAN_BASIC` DECIMAL(1,0),`PLAN_FCST` DECIMAL(1,0),`TIME_UNIT` CHAR(3),`TIME_UNIT_ISO` CHAR(3),`NETWORK_PROFILE` CHAR(7),`PROJECT_PROFILE` CHAR(7),`BUDGET_PROFILE` CHAR(6),`PROJECT_STOCK` CHAR(1),`OBJECTCLASS` CHAR(2),`STATISTICAL` CHAR(1),`TAXJURCODE` CHAR(15),`INT_PROFILE` CHAR(7),`WBS_SCHED_PROFILE` CHAR(12),`CSH_BDGT_PROFILE` CHAR(6),`PLAN_PROFILE` CHAR(6),`JOINT_VENTURE` CHAR(6),`RECOVERY_IND` CHAR(2),`EQUITY_TYPE` CHAR(3),`JV_OBJECT_TYPE` CHAR(4),`JV_JIB_CLASS` CHAR(3),`JV_JIB_SUB_CLASS_A` CHAR(5),`DELETION_FLAG` CHAR(1),`OBJECTCLASS_EXT` CHAR(5),`FUNC_AREA` CHAR(4),`FUNC_AREA_LONG` CHAR(16)

 ,PRIMARY KEY (`PROJECT_DEFINITION`));';

$result = $mysqli->query($sqlstmt);
var_dump($result);
if($result) {
	if ($mysqli->field_count) $result->close();
}
if ($mysqli->warning_count) {
	if ($result = $mysqli->query("SHOW WARNINGS", MYSQLI_USE_RESULT)) {
		while ($row = $result->fetch_row()) {
			printf("%s (%d): %s\n", $row[0], $row[1], $row[2]);
		}
	}
	$result->close();
}

/*
// prevent bug from happen
$result = $mysqli->query('delete from PROJECT_DETAIL where 1=2');
if($result) {
	if ($mysqli->field_count) $result->close();
}
if ($mysqli->warning_count) {
	if ($result = $mysqli->query("SHOW WARNINGS", MYSQLI_USE_RESULT)) {
		while ($row = $result->fetch_row()) {
			printf("%s (%d): %s\n", $row[0], $row[1], $row[2]);
		}
	}
	$result->close();
}
// end of bug prevention
*/

$sqlstmt = "LOAD DATA LOCAL INFILE 'PROJECT_DEFINITION_STRU.CSV' replace INTO TABLE PROJECT_DETAIL FIELDS TERMINATED BY ';' IGNORE 0 LINES(`PROJECT_DEFINITION`,`DESCRIPTION`,`MASK_ID`,`RESPONSIBLE_NO`,`APPLICANT_NO`,`COMP_CODE`,`BUS_AREA`,`CONTROLLING_AREA`,`PROFIT_CTR`,`PROJECT_CURRENCY`,`PROJECT_CURRENCY_ISO`,`NETWORK_ASSIGNMENT`,`START`,`FINISH`,`PLANT`,`CALENDAR`,`PLAN_BASIC`,`PLAN_FCST`,`TIME_UNIT`,`TIME_UNIT_ISO`,`NETWORK_PROFILE`,`PROJECT_PROFILE`,`BUDGET_PROFILE`,`PROJECT_STOCK`,`OBJECTCLASS`,`STATISTICAL`,`TAXJURCODE`,`INT_PROFILE`,`WBS_SCHED_PROFILE`,`CSH_BDGT_PROFILE`,`PLAN_PROFILE`,`JOINT_VENTURE`,`RECOVERY_IND`,`EQUITY_TYPE`,`JV_OBJECT_TYPE`,`JV_JIB_CLASS`,`JV_JIB_SUB_CLASS_A`,`DELETION_FLAG`,`OBJECTCLASS_EXT`,`FUNC_AREA`,`FUNC_AREA_LONG`);";
$result = $mysqli->query($sqlstmt);
// When running 5.3 $result is false and I got the following err msg if the table PROJECT_DETAIL already exists in create above
//PHP Warning:  mysqli::query(): (00000/0):  in /home/tooljn/dw/data/090217173424_chopsap01/projectDetails0/err53.php on line 61
//
var_dump($result);
if($result) {
	if ($mysqli->field_count) $result->close();
}
if ($mysqli->warning_count) {
	if ($result = $mysqli->query("SHOW WARNINGS", MYSQLI_USE_RESULT)) {
		while ($row = $result->fetch_row()) {
			printf("%s (%d): %s\n", $row[0], $row[1], $row[2]);
		}
	}
	$result->close();
}
}
 [2009-02-25 17:13 UTC] johannes@php.net
I couldn't reproduce using myslqnd and libmysql currently creates other trouble for me. but I'll investigate. Thanks for the short script so far :-)
 [2009-08-25 15:24 UTC] uw@php.net
Running the example gives:

bool(true)
array(1) {
  [0]=>
  string(4) "test"
}
bool(true)

Warning: mysqli::query(): (42S22/1054): Unknown column 'APP
LICANT_NO' in 'field list' in /home/nixnutz/src/login/PHP_5_3/bug47387.php on line 85
bool(false)


Please verify if that is the cause of the bool(false).

A short LOAD DATA test such as the one below does not show any differences between PHP 5.2 and PHP 5.3 on my box. Would be great, if you could try that one as well.

$mysqli = new mysqli("127.0.0.1", "root", "root");

$res = $mysqli->query("USE phptest");
printf("USE phptest -> %s\n", var_export($res, true));

$mysqli->query("DROP TABLE IF EXISTS foo");
$res = $mysqli->query("CREATE TABLE IF NOT EXISTS foo (id INT, bar CHAR(2))");
printf("CREATE TABLE -> %s\n", var_export($res, true));

$file = tempnam(sys_get_temp_dir(), "bug47387");
var_dump($file);
$fp = fopen($file, "w");
fwrite($fp, "1;a\n");
fclose($fp);

$res = $mysqli->query(sprintf("LOAD DATA LOCAL INFILE '%s' INTO TABLE foo FIELDS TERMINATED BY ';'", $file));
printf("LOAD DATA -> %s\n", var_export($res, true));

$res = $mysqli->query("SELECT * FROM foo");
if ($res)
	while ($row = $res->fetch_assoc())
		var_dump($row);



 [2009-09-02 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2010-12-10 23:29 UTC] clewis at myfonts dot com
The bug happens if you do a SELECT before the LOAD DATA. In the script above 
(comment [2009-08-25 13:24 UTC] uw@php.net), if you insert these lines before 
the LOAD DATA query:

$res = $mysqli->query("SELECT * FROM foo limit 1");
printf("SELECT -> %s\n", var_export($res, true));

... then the LOAD DATA query will return false. Note that even though the LOAD 
DATA returns false, the data is inserted successfully.

I created a new bug for this the other day: http://bugs.php.net/bug.php?id=53503

Sorry for the duplicate
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 08:01:27 2024 UTC