We help IT Professionals succeed at work.

Installing MySQLi for Windows 2003 Server

Chris Kenward
on
622 Views
Last Modified: 2013-12-13
Hi Folks

Hope someone can help with this. I have MySQL 5.x running on a Windows 2003 server with PHP 5.1.1 but one of my clients gets;

"Client does not support authentication protocol requested by server; consider upgrading MySQL client"

After doing some research it appears I need to make sure that the MySQLi module is installed on the server, but I've no idea how to do this on a Windows server. Would sincerely appreciate some help with this one or some suggestions how to get around it.

Regards
Chris
Comment
Watch Question

Top Expert 2006

Commented:
The error message that you are receiving is documented here:

http://dev.mysql.com/doc/refman/5.0/en/old-client.html
Chris KenwardIT Tech Support

Author

Commented:
Thanx - been there, looked at that. Still hasn't helped me figure out how to upadate the MySQLi module on the Windows Server though and this is, I guess, what this is all about.

Any ideas?
Top Expert 2006

Commented:
Have you determined that mysqli is NOT installed on your machine?  To determine if it is or not, use phpinfo() to determine what is installed:

http://us3.php.net/phpinfo
Chris KenwardIT Tech Support

Author

Commented:
Hmmm - good point. Did the phpinfo thing and returned:

mysqli
MysqlI Support enabled
Client API version  4.1.7  
MYSQLI_SOCKET  /tmp/mysql.sock  

Directive Local Value Master Value
mysqli.default_host no value no value
mysqli.default_port 3306 3306
mysqli.default_pw no value no value
mysqli.default_socket no value no value
mysqli.default_user no value no value
mysqli.max_links Unlimited Unlimited
mysqli.reconnect Off Off

So does throw a whole new light on the topic? If it's enabled doesn't that mean that the "old" client auth should work fine? I'm stumped.
Top Expert 2006

Commented:
Looks like you have mysqli installed.

Are you using the mysqli methods (mysqli_ instead of mysql_)?  Let's see the code where you get the connection.  It sounds like you may be using the old mysql_ library instead.
Chris KenwardIT Tech Support

Author

Commented:
Hi Todd

OK - you're talking to someone that knows very little about all this. I host the server for a web designer who's having trouble when he tried to access his database on the server. Is it HIS code you're wanting to see? If so I'll send him a note asking for it - no probs at all. If not, then could you be a little specific about where you want me to look and I'll go do it straight away?

All the best and thanks so much for the prompt responses. Much appreciated.

Chris
Top Expert 2006

Commented:
Yeah - his code.  Chances are, he's using the old library.
Chris KenwardIT Tech Support

Author

Commented:
Gotcha!

I've sent him an Email - will forward to you just as soon as I have it.

Regards
Chris
Chris KenwardIT Tech Support

Author

Commented:
Hi Todd

Hope it's OK to post something of this length. Here is the code being used to query the database.

Cheers!
Chris

<?PHP

class MyDatabase
{
// The var that stores the last
// used SQL statement
var $SQLStatement = "";

// The var that stores the error
// (if any)
var $Error = "";

function MyDatabase()
{
  // Config for the database
  // connection
  $this->DBUser = "user";
  $this->DBPass = "password";
  $this->DBName = "dbname";
  $this->DBHost = "server.domain.com";
}

function Connect()
{
  //Connect to a mysql database
  $this->db = mysql_connect($this->DBHost,
  $this->DBUser, $this->DBPass) or
  die("MYSQL ERROR: ".mysql_error());
  // Select the database
  mysql_select_db($this->DBName,
  $this->db) or die("MYSQL ERROR:
  ".mysql_error());
}

// Disconnect from the MYSQL database
function Disconnect()
{
  mysql_close($this->db) or die("MYSQL
  ERROR: ".mysql_error());
}

// Method that dynamically adds
// values to a MYSQL database
// table using the $_POST vars
function AddToDB($tbl)
{
// Set the arrays we'll need
$sql_columns = array();
$sql_columns_use = array();
$sql_value_use = array();

// Pull the column names from the
// table $tbl
$pull_cols = mysql_query("SHOW COLUMNS
FROM ".$tbl) or die("MYSQL ERROR:
".mysql_error());

// Pull an associative array of the
// column names and put them into a
// non-associative array
while ($columns =
mysql_fetch_assoc($pull_cols))
  $sql_columns[] = $columns["Field"];

foreach( $_POST as $key => $value )
{
      if (!get_magic_quotes_gpc()) {
            $value = mysql_real_escape_string($value);
      } else {
               stripslashes($value);
               $value = mysql_real_escape_string($value);
      }
            
  // Check to see if the variables
  // match up with the column names
  if ( in_array($key, $sql_columns)
  && trim($value) )
  {
   // If this variable contains the
   // string "DATESTAMP" then use MYSQL
   // function NOW()
   if ($value == "DATESTAMP")
   $sql_value_use[] = "NOW()";
   else
   {
    // If this variable contains a
    // number, then don't add single
    // quotes, otherwise check to see
    // if magic quotes are on and use
    // addslashes if they aren't
    if ( is_numeric($value) )
    $sql_value_use[] = $value;
    else
     $sql_value_use[] =
     "'"
     .nl2br($value)."'";
   }
   // Put the column name into the array
   $sql_columns_use[] = $key;
  }
}

// If $sql_columns_use or $sql_value_use
// are empty then that means no values
// matched
if ( (sizeof($sql_columns_use) == 0) ||
(sizeof($sql_value_use) == 0) )
{
// Set $Error if no values matched
$this->Error = "Error: No values were
passed that matched any columns.";
return false;
}
else
{
// Implode $sql_columns_use and
// $sql_value_use into an SQL insert
// sqlstatement
$this->SQLStatement = "INSERT INTO
".$tbl." (".implode(",",$sql_columns_use).
") VALUES (".implode(",",$sql_value_use).
")";

// Execute the newly created statement
if ( @mysql_query($this->SQLStatement) )
   return true;
else
{
// Set $Error if the execution of the
// statement fails
$this->Error = "Error: ".mysql_error();
   return false;
  }
}
}

// Method that dynamically updates
// values in a MYSQL database table
// using the $_POST vars
function UpdateDB($tbl, $id, $id_name)
{
// Set the arrays we'll need
$sql_columns = array();
$sql_value_use = array();

// Pull the column names from the
// table $tbl
$pull_cols = mysql_query("SHOW COLUMNS FROM ".$tbl) or die(  "MYSQL ERROR: ".mysql_error() );

// Pull an associative array of
// the column names and put them
// into a non-associative array
while ($columns = mysql_fetch_assoc($pull_cols))
  $sql_columns[] = $columns["Field"];

foreach($_POST as $key => $value)
{
  // Check to see if the variables
  // match up with the column names
  if ( in_array($key, $sql_columns)
  && isset($value) )
  {
   // If this variable contains the
   // string "DATESTAMP" then use
   // MYSQL function NOW()
   if ($value == "DATESTAMP")
    $sql_value_use[] = $key."=NOW()";
   else
   {
    // If this variable contains a
    // number, then don't add single
    // quotes, otherwise check to see
    // if magic quotes are on and use
    // addslashes if they aren't
    if ( is_numeric($value) )
     $sql_value_use[] = $key."=".$value;
    else
     $sql_value_use[] = (
     get_magic_quotes_gpc() ) ?
     $key."='".$value."'" : $key."=
     '".mysql_escape_string($value)."'";
   }
  }
}

// If $sql_value_use is empty then
// that means no values matched
if ( sizeof($sql_value_use) == 0 )
{
  // Set $Error if no values matched
  $this->Error = "Error: No values
  were passed that matched any columns.";
  return false;
}
else
{
  // Implode $sql_value_use into an
  // SQL insert sqlstatement
  $this->SQLStatement = "UPDATE ".$tbl."
  SET ".implode(",",$sql_value_use)."
  WHERE ".$id_name."=".$id;

  // Execute the newly created
  // statement
  if ( @mysql_query($this->SQLStatement) )
   return true;
  else
  {
   // Set $Error if the execution of the
   // statement fails
   $this->Error = "Error: ".mysql_error();
   return false;
  }
}
}

// A simple query here
// Example Useage - $query = $db->query("");
function query($query) {
    $query = mysql_query($query) or die(mysql_error());
    $num=mysql_numrows($query);
    return $query;
    return $num;
}

// Fetch 1 Row
// Example Useage - $query = $db->query("");
//                              $res = $db->fetch_array($query);
//                              $var = $res[column];
function fetch_array($query) {
    $query = mysql_fetch_array($query);
    return $query;
}

// More then 1 row (loops)
// Example Useage - $query = $db->query("");
//                              while($res = $db->fetch_row($query)) {
//                              $stuff = $res[result];
//                              }
function fetch_row($query) {
    $query = mysql_fetch_row($query);
    return $query;
}

// More then 1 row (loops)
// Example Useage - $query = $db->query("");
//                              while($res = $db->mysql_fetch_assoc($query)) {
//                              $stuff = $res[result];
//                              }
function fetch_assoc($query) {
    $query = mysql_fetch_assoc($query);
    return $query;
}

}
Top Expert 2006

Commented:
Yes, he's using the old library.  You can tell because of lines like:

  $this->db = mysql_connect($this->DBHost,
  $this->DBUser, $this->DBPass) or
  die("MYSQL ERROR: ".mysql_error());


If he were using the newer libraries (that are compatible with MySQL 4.1+), you would see mysqli_connect(...) instead of mysql_connect(...).

Chris KenwardIT Tech Support

Author

Commented:
Hi Todd

Cool - so how does he go about upgrading? Is it just a matter of putting in the "mysqli" entries in the code or does he physically have to upgrade?

The other question I have is... well.. the code is surely going to run on the local MySQL server that is popping up the error, because the website and the PHP and MySQL are all running on the same server. Does this mean there could be a client problem on the server or is this simply stuff in his code?

Gosh - I feel dumb! ;)
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Chris KenwardIT Tech Support

Author

Commented:
Thanks, Todd

Will get back to him and then we'll do some testing. Cheers for the help to date - really brilliant.
Chris KenwardIT Tech Support

Author

Commented:
Todd

Just wanted to say a large THANKS for the help. Really appreciated. Everything now working just fine - all he did was change the code as you suggested he might be able to.

Happy Days!
Chris
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.