Why is mysqli working but mysql is not? WordPress
This is a PHP question, not a WordPress question, but I could use an answer quickly.
I took over an old code base and have tried to fix it. This code base is very large. The site is written in PHP and uses Mysql as the database. Now the code had to move and be mixed in with another website, also running PHP. However, the new site sits on a new server, where things are differently configured. As near as I can see, the old MySql module for PHP is not working. But mysqli does seem to work. The other code on the site, which was written for the new server, uses mysqli and seems to working. But there is a vast amount of code in the old format, using these functions:
http://us1.php.net/manual/en/book.mysql.php
but this does seem to work:
http://us1.php.net/manual/en/book.mysqli.php
Rather than re-write all of the code to use mysqli, I am wondering what the quickest way would be to get the old mysql module working?
When I run this PHP code:
$dbh = mysql_connect($hostname, $username, $password);
echo mysql_error();
the mysql_error() gives me:
Access denied for user 'heartcandy_produser'@'localhost' (using password: YES)
which makes me think the old myql module is installed but for some reason not working.
The exact same host, database, user and password is working for the code that uses mysqli, but not mysql.
php -v
gives me:
PHP 5.2.17 (cli) (built: Aug 30 2012 13:11:35)
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2010 Zend Technologies
with the ionCube PHP Loader v4.2.2, Copyright (c) 2002-2012, by ionCube Ltd., and
with Zend Optimizer v3.3.9, Copyright (c) 1998-2009, by Zend Technologies
Checking via phpinfo() via browser confirms browser also sees PHP PHP Version 5.2.17.
And if I run "php -i" and search through for it any mention of mysql, I see:
Configure Command => './configure' '--disable-cgi' '--enable-bcmath' '--enable-calendar' '--enable-dbase' '--enable-exif' '--enable-fastcgi' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-magic-quotes' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-sqlite-utf8' '--enable-wddx' '--enable-zip' '--prefix=/usr/local' '--with-bz2' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/opt/openssl' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mhash=/opt/mhash/' '--with-mime-magic' '--with-mssql=/usr' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/opt/openssl' '--with-openssl-dir=/opt/openssl' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-pspell' '--with-sqlite=shared' '--with-tidy=/opt/tidy/' '--with-ttf' '--with-unixODBC=/usr' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr'
Server API => Command Line Interface
Virtual Directory Support => disabled
Configuration File (php.ini) Path => /usr/local/lib
Loaded Configuration File => /usr/local/lib/php.ini
Scan this dir for additional .ini files => (none)
additional .ini files parsed => (none)
PHP API => 20041225
PHP Extension => 20060613
Zend Extension => 220060519
Debug Build => no
Thread Safety => disabled
Zend Memory Manager => enabled
IPv6 Support => enabled
Registered PHP Streams => https, ftps, compress.zlib, compress.bzip2, php, file, data, http, ftp, zip
Registered Stream Socket Transports => tcp, udp, unix, udg, ssl, sslv3, sslv2, tls
Registered Stream Filters => zlib.*, bzip2.*, convert.iconv.*, string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed
mysql
MySQL Support => enabled
Active Persistent Links => 0
Active Links => 0
Client API version => 5.5.33
MYSQL_MODULE_TYPE => external
MYSQL_SOCKET => /var/lib/mysql/mysql.sock
MYSQL_INCLUDE => -I/usr/include/mysql
MYSQL_LIBS => -L/usr/lib64 -lmysqlclient
Directive => Local Value => Master Value
mysql.allow_persistent => Off => Off
mysql.connect_timeout => 60 => 60
mysql.default_host => no value => no value
mysql.default_password => no value => no value
mysql.default_port => no value => no value
mysql.default_socket => no value => no value
mysql.default_user => no value => no value
mysql.max_links => Unlimited => Unlimited
mysql.max_persistent => Unlimited => Unlimited
mysql.trace_mode => Off => Off
mysqli
MysqlI Support => enabled
Client API library version => 5.5.33
Client API header version => 5.5.21
MYSQLI_SOCKET => /var/lib/mysql/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
PDO
PDO support => enabled
PDO drivers => sqlite, sqlite2, mysql
pdo_mysql
PDO Driver for MySQL, client library version => 5.5.33
To be clear, this code no longer works (no longer connects to the database):
$dbh = mysql_connect($hostname, $username, $password);
$databaseChoosen = mysql_select_db($database);
but this code does work:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die("Error " . mysqli_error($link));
I don't have time to rewrite all the code, so it would be best to enable the old module. Sadly, code does not live on my server. It is on a cheap shared hosting account on Hostgator.com. So I am unsure about how much I can do.
Just to be super clear, when I run this code:
$dbh = mysql_connect($hostname, $username, $password);
$databaseChoosen = mysql_select_db($database);
and then run a query using mysql_query(), I get an error message like:
Access denied for user 'heartcandy_produser'@'localhost' (using password: YES)
Please don't tell me to recheck the username and password, as I have checked them a dozen times.
If I ssh to the server I can connect to mysql on the command line, using the username and password that I have. But using the same username and password via PHP mysql_connect(), I get the above error.
The ext/mysql PHP extension is officially deprecated as of PHP v5.5.0, but this server is using PHP Version 5.2.17.
Any suggestions?
Answers (5)
Hariprasad Vijayan answers:
Hello Lawerence,
I think it might be because of version issue. Hope it will work if you change connecting method like this
$dbh = mysql_connect($hostname, $username, $password);
if (!$dbh ) {
die('Could not connect: ' . mysql_error());
}
$databaseChoosen = mysql_select_db($database,$dbh );
http://in1.php.net/mysql_select_db
Lawrence Krubner comments:
What do you mean version issue?
The ext/mysql PHP extension is officially deprecated as of PHP v5.5.0, but this server is using PHP Version 5.2.17.
Lawrence Krubner comments:
The code I am using:
$dbh = mysql_connect($hostname, $username, $password);
if ($dbh) {
$databaseChoosen = mysql_select_db($database);
if (!$databaseChoosen) {
$controller->error("In the constructor of the Database class, we tried to connect to the database called '$database', but we could not.");
}
} else {
$controller->error("In the constructor of the Database class, we tried but failed to connect to the database. For the host, user, and password, we were using these: '$hostname', '$username', '$password'. The function mysql_error() returned this message: " . mysql_error());
}
The else() branch writes to the log and gives the error I posted above.
Lawrence Krubner comments:
I am sorry to say I have run out of time. I will have to re-write the code to use the mysqli module. I am going to allow this $50 to go the Community Pot.
zebra webdesigns answers:
Hello Lawrence
Just a wild card test.
Can you create any other database with some other user name and try to connect it and check whether it works.
If you can configure the database to be used externally then you can access the database in your local machine and check whether it connects. Since your local will run different php version.
I cant say this help much. but its just a basic check.
I will send further suggestions
zebra webdesigns comments:
Seems already some one got in to this problem
http://stackoverflow.com/questions/13802446/strange-host-issue
Lawrence Krubner comments:
Zebra, thanks for that. There is also an issue that seems to be specific to HostGator:
http://support.hostgator.com/articles/articles/cpanel/troubleshooting-mysql-database-connection-issues
However, I do not think that issue applies in my case since the database-user "heartcandy349" can connect to the database using mysqli functions. Just not the old mysql_connect().
Lawrence Krubner comments:
I am sorry to say I have run out of time. I will have to re-write the code to use the mysqli module. I am going to allow this $50 to go the Community Pot.
Deepak answers:
change the username and password provided by your hosting company...try it
OR
To provide a specific user with a permission, you can use this framework:
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.
Each time you update or change a permission be sure to use the Flush Privileges command.
Deepak comments:
Lawrence Krubner! Try granting the privileges hope help you...
mysql> GRANT ALL ON your_database.* TO your_user@'localhost' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
Deepak comments:
granting the privileges
Deepak comments:
Client does not support authentication protocol
The current implementation of the authentication protocol uses a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. Attempts to connect to a 4.1 or newer server with an older client may fail with the following message:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
To deal with this problem, the preferred solution is to upgrade all client programs to use a 4.1.1 or newer client library. If that is not possible, use one of the following approaches:
To connect to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.
Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:
mysql> SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Substitute the password you want to use for “newpwd” in the preceding example. MySQL cannot tell you what the original password was, so you'll need to pick a new one.
Tell the server to use the older password hashing algorithm by default:
https://dev.mysql.com/doc/refman/5.5/en/old-client.html
Lawrence Krubner comments:
But this username/password works if I use mysqli function calls.
Lawrence Krubner comments:
About this:
<em>Attempts to connect to a 4.1 or newer server with an older client may fail with the following message:</em>
Does the old mysql module use a different client than the newer mysqli module?
Lawrence Krubner comments:
I am sorry to say I have run out of time. I will have to re-write the code to use the mysqli module. I am going to allow this $50 to go the Community Pot.
Bob answers:
The database is on same server or you are tying to another?
are you able to connect using phpMyAdmin(from hostgator cpanel)?
Bob comments:
Please try creating new user on that database with full access and try with those credential?
Lawrence Krubner comments:
This code works:
DEFINE ('DB_USER', 'xxx');
DEFINE ('DB_PASSWORD', 'xxx);
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'xxx');
// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (!$dbc) {
trigger_error ('Could not connect to MySQL: ' . mysqli_connect_error() );
echo "not connected";
}else{
//echo "connected";
}
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
If I use exactly the same DB_HOST, DB_USER, DB_PASSWORD and DB_NAME, this does not work:
$dbh = mysql_connect($hostname, $username, $password);
if ($this->database_link) {
$databaseChoosen = mysql_select_db($database);
if (!$databaseChoosen) {
$controller->error("In the constructor of the Database class, we tried to connect to the database called '$database', but we could not.");
}
}
I have trouble seeing how the database username/password could be the issue.
Lawrence Krubner comments:
I am sorry to say I have run out of time. I will have to re-write the code to use the mysqli module. I am going to allow this $50 to go the Community Pot.
Just Me answers:
Maybe you can try the rename_function() function to change mysql_connect commands to (userdefined) mysqli_connect() commands.
Lawrence Krubner comments:
Interesting. But I think under the covers it is the old mysql_connect() and mysql_query() that would still be in use, and that seems to be where the trouble is.
I am curious how Hostgator and/or the site owner might have disabled the old mysql module, especially since "php -i" gives me results that seem to suggest that it is enabled. (The site owner is non-technical so I can not ask them. They previously hired another programmer to work on this site, but they are no longer in contact with that programmer.)
Lawrence Krubner comments:
I am sorry to say I have run out of time. I will have to re-write the code to use the mysqli module. I am going to allow this $50 to go the Community Pot.