mysqli bind_param giving error: (1210) Incorrect a

问题:

I have this exact same code working great on another server:

$mysqli_Cxn = new mysqli($SQL_HOST,$SQL_USER,$SQL_PASS,$SQL_DB);
if($mysqli_Cxn->connect_errno){
echo 'Unable to connect!!';
exit();
}

$userID=12345;
$userFirstName = 'Charley';
$userLocale = 'en_US';

$sql = "UPDATE userProfile SET userFirstName=?, userLocale=? WHERE id=?";

if($stmt = $mysqli_Cxn->prepare($sql)){
 if(!$stmt->bind_param('ssi',$userFirstName,$userLocale,$userID)){
  echo "<br/><br/>Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
 }
 if($stmt->execute()){
  totalAffected=$stmt->affected_rows;
  if($totalAffected>=1){
   echo '<br/><br/>UPDATE OK: Affected rows = '. $totalAffected;
  }
 }else{
  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
 }
}
$stmt->close();

That code gives me the following output: Execute failed: (1210) Incorrect arguments to mysql_stmt_execute

If I change these two lines:

$sql = "UPDATE userProfile SET userFirstName=?, userLocale=? WHERE id=?";
$stmt->bind_param('ssi',$userFirstName,$userLocale,$userID);

to this:

$sql = "UPDATE userProfile SET userFirstName=?, userLocale='en_US' WHERE id=12345";
$stmt->bind_param('s',$userFirstName);

...then the Update is successful and I don't get any error.

Does anyone know why I can't bind more than one param in this code?

I had this code running perfectly on a Centos 4.9, PHP 5.3.3, MySQL 5.0.91/5.0.91-community-log

I need to run it on my current server which is Centos 6.2, PHP 5.3.10, MySQL 5.0.95-community-log

回答1:

I did a little research, and it seems like a reported error in the MySQL source in combination with your version of GCC and the optimization flags you use. If you can't change the MySQL version, try recompile MySQL with added -fno-strict-aliasing to your CFLAGS.

See http://bugs.mysql.com/bug.php?id=48284 for some more details

回答2:

Final notes on this issue.

I got a new server up and running with the following config:

PHP 5.3.10
MySQL 5.1.61-cll

I enable the new server to accept external connections and tested the same code on the fist server (this time replacing "localhost" for my new server's IP).

It gave me the exact same error when I tried to use more than one param in the query.

Finally I tested the code (with the multiple paramater query) on the new server and it worked without any issue.

My logic might be way off, but I guess the problem is with mysqli's client version:

first server:

printf("Client library version: %d\n", mysqli_get_client_version());
//Output:
//Client library version: 50095 

new server:

printf("Client library version: %d\n", mysqli_get_client_version());
//Output:
//Client library version: 50161 

So I can make two recommendations:

1. Do yourself a favor and go over to PDO
2. Want/Have to stick with mysqli only? Make sure your server's mysqli client version is not 50095

Thanks to everyone's comments/help!

PS: I'm using VPS servers with CPanel. I tried to recompile the mysqli client using EasyApache in WHM on the first server - and being a total noob at all of this - I guess I'm stuck with the version provided via Cpanel (until my next upgrade).

回答3:

I would suggest you that explicitelly cast $userID to integer and retest if it still fails.

Since PHP variables are variant as default, maybe you are running into problems when binding a non string (integer in this case) to the query.

Can you try:

$sql = "UPDATE userProfile SET userFirstName=?, userLocale=? WHERE id=?";
$stmt->bind_param('ssi',$userFirstName,$userLocale,(int)$userID);

回答4:

I encountered exactly the same problem.

(Bind parameters did not accept more than one parameter)

My version of mysql using this command:

printf ("Client library Version:% d \ n", mysqli_get_client_version ());

was: 50094

The 50095 version is at issue here to this problem:

https://www.liketly.com/forum/thread/27005/mysqli-bind_param-giving-error-1210-incorrect-arguments-to-mysql_stmt_execute/

After an update to PHP 5.4 to 5.6 (on a shared server) mysql version was also updated and the problem disappeared.

My new mysql version is 50011