Edit report at http://bugs.php.net/bug.php?id=47387&edit=1
ID: 47387
Comment by: clewis at myfonts dot com
Reported by: lars dot a dot johansson at se dot atlascopco dot
com
Summary: Load stmt result bool false
Status: No Feedback
Type: Bug
Package: MySQLi related
Operating System: Linux mandriva 2009
PHP Version: 5.3CVS-2009-02-14 (snap)
Assigned To: mysql
Block user comment: N
Private report: N
New Comment:
The bug happens if you do a SELECT before the LOAD DATA. In the script
above
(comment [2009-08-25 13:24 UTC] u...@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
Previous Comments:
------------------------------------------------------------------------
[2009-09-02 01:00:00] 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".
------------------------------------------------------------------------
[2009-08-25 15:24:10] u...@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-02-25 17:13:34] johan...@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-02-25 15:23:28] 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-24 08:35:59] 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.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/bug.php?id=47387
--
Edit this bug report at http://bugs.php.net/bug.php?id=47387&edit=1
