ID: 47387
Updated by: u...@php.net
Reported By: lars dot a dot johansson at se dot atlascopco dot com
-Status: Assigned
+Status: Feedback
Bug Type: MySQLi related
Operating System: Linux mandriva 2009
PHP Version: 5.3CVS-2009-02-14 (snap)
Assigned To: mysql
New Comment:
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);
Previous Comments:
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
[2009-02-18 17:28:35] 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-17 19:06:36] johan...@php.net
Would be great if you can narrow it down a bit more, Setting to
"Feedback" for tracking this better. Thank you.
------------------------------------------------------------------------
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/47387
--
Edit this bug report at http://bugs.php.net/?id=47387&edit=1
