ID: 47387
User updated by: lars dot a dot johansson at se dot atlascopco dot com
Reported By: lars dot a dot johansson at se dot atlascopco dot com
Status: Open
Bug Type: MySQLi related
Operating System: Linux mandriva 2009
PHP Version: 5.3CVS-2009-02-14 (snap)
New Comment:
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();
}
}
Previous Comments:
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
[2009-02-17 18:50:05] 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 17:42:02] 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
too...@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.
------------------------------------------------------------------------
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
