#47387 [Asn->Fbk]: Load stmt result bool false

ID: 47387 Updated by: uw@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] 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-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] johannes@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