Can't connect to MySQL server on external DB.... utf-8?

Can't connect to MySQL server on external DB.... utf-8?

by Ray Schumacher -
Number of replies: 8
I'm trying to set up the External database authentication.
Both servers are with the same host, Stormerhosting, and use Moodle 1.9.2
database mysql docs.gifversion 4.1.16 is required and you are running 5.0.51 OK
php
docs.gifversion 4.3.0 is required and you are running 5.2.
MySQL is NOT using utf-8, and I don't have access to the .conf/setup.
What to do?
Does the remote server also need to be utf-8?
I hope not...

ErrorAttempting login returns (eventually):
Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'xxxxx.com' (4) in /home/cmecool/public_html/lib/adodb/drivers/adodb-mysql.inc.php on line 365
cooltoes.com: Connection error to server 'xxxxx.com' with user 'xxxxxx'
(data xxx'd out)

Best,
Ray
Average of ratings: -
In reply to Ray Schumacher

Re: Can't connect to MySQL server on external DB.... utf-8?

by Mathieu Petit-Clair -
Picture of Core developers Picture of Moodle HQ Picture of MoodleCloud team Picture of Plugin developers Picture of Testers
I think your setup is good (php and mysql versions are more recent than required). The mysql_connect() error makes me think that the login/password supplied for the remote database is not the right one.

A connection error doesn't have much to do with Moodle itself - it's at PHP level.

Mat

Average of ratings: -
In reply to Mathieu Petit-Clair

Re: Can't connect to MySQL server on external DB.... utf-8?

by Peter Bloomfield -
If the username/password fails, doesn't MySQL usually report something like "USING PASSWORD yes/no"? It therefore looks like it simply is unable to establish a connection to the database server (like when there's a firewall or similar blocking the request).

utf-8 could indeed be the culprit, although I am definitely no expert on it! When you say it's not using utf-8, what do you mean exactly? The connection collation, or the database itself?

Average of ratings: -
In reply to Peter Bloomfield

Re: Can't connect to MySQL server on external DB.... utf-8?

by Ray Schumacher -
I'm not sure; are they reported/set separately? I agree that it seems to be the basic connect() failing.

moodle/admin/environment.php?version=2.0
(on Moodle's server) reports these checks; unicode a "must"
unicode
docs.gifmust be installed and enabled

It is required that you store all your data in Unicode format (UTF-8). New installations must be performed into databases that have their default character set as Unicode. If you are upgrading, you should perform the UTF-8 migration process (see the Admin page).

 Check
php_extension mbstring docs.gifshould be installed and enabled for best results

Installing the optional MBSTRING library is highly recommended in order to improve site performance, particularly if your site is supporting non-Latin languages.

 Check
php_extension openssl docs.gifshould be installed and enabled for best results

Installing the optional OpenSSL library is highly recommended -- it enables Moodle Networking functionality.

 Check
php_extension xmlrpc docs.gifshould be installed and enabled for best results

Installing the optional xmlrpc extension is useful for Moodle Networking functionality.


the mySQLServer variables and settings for the Moodle machine
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci


the phpInfo for the Moodle machine
Active Persistent Links 0
Active Links 1
Client API version 5.0.51a
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib -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



phpMyadmin Server variables and settings for the server I'm connecting to shows:
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish
socket /var/lib/mysql/mysql.sock
so, nothing about tcp/ip,

and, the cpanel's Remote Database Access Hosts page does have each server enabled for each other - IP and names.
I just tried via command line from another Unix server and couldn't ...

If I can't get this to work, is there a handy script to synch tables via cron and cURL or such, since that's all Moodle is trying to do on first login. I can do selects etc on each fine. I could write one if I knew how to force it to be called on user login.

Thanks,
Ray
Average of ratings: -
In reply to Ray Schumacher

Re: Can't connect to MySQL server on external DB.... utf-8?

by Peter Bloomfield -
Each database (and in fact each table and even each column) can have its own collation for storing data, but (as far as I understand) the actual transfer of data to/from the database takes place using a specific collation. I think this is determined by the incoming client connecting to MySQL, so long as the MySQL server supports what it's trying to use. (As you can imagine, a restrictive connection collation can result in data problems if it's stored differently).

It certainly looks as though you would be totally fine if your database was on your Moodle server, but I can't really tell about the remote server.

I assume you've already created your Moodle database on your remote server? Access that database through phpMyAdmin, click the "Operations" tab at the top right, and somewhere on there (location depending on which version you are running) should be a "collation" section, with a drop-down box. The current collation of your database should be listed by default, but you can change it (click the "Go" button to do the change).

You basically want "utf8_general_ci" or "utf8_unicode_ci". If you can't select either of those, try anything starting with "utf8"... and if that fails, then I'm afraid your database won't support Moodle properly.


Hope it works, 'cos I'm all out of ideas after that! sad
Average of ratings: -
In reply to Peter Bloomfield

Re: Can't connect to MySQL server on external DB.... utf-8?

by Ray Schumacher -
Thanks Peter,
phpMyAdmin 2.11.6 on those servers does not have such a collation drop-down, I think because the information_schema table is not available to clients on that hosting. (It is on some other hosts I work with).

In addition, I've found (using the test script below) that I can't remote log into any of the hosted mySQL servers that I have access to.
Some require an IP# (or localhost) rather than host name, even locally; the mySQL server is not on the same IP# as the domain!
I've run it on 4 different hosting companies trying to log into each other's servers (it always works locally). Some are even fully utf-8.

Is it possible to run Moodle without utf8 locally? Can we get around moodle/install.php and it's checks? If Icould, I could install it on the local server and not worry about remote connection, but the installer balks:
unicode
docs.gifmust be installed and enabled

It is required that you store all your data in Unicode format (UTF-8). New installations must be performed into databases that have their default character set as Unicode.

 Check
database mysql docs.gifversion 4.1.16 is required and you are running 4.0.27



<?PHP

$DB_SERVER = "x.x.x.x";
$DB_LOGIN = "xxxuser"; // mysql user
$DB_PASSWORD = "xxxpass"; // mysql password
$DB = "xxname"; // mysql database
$tableName = 'some_table';

if (!$link){ //loop twice around until we pconnect
for ($i=0; !$link && $i < 2; $i++) {
print "try # $i: $DB_SERVER, $DB_LOGIN\n";
flush();
$link = mysql_connect ($DB_SERVER,$DB_LOGIN, $DB_PASSWORD);
}
}
if (!$link){ //display error if pconnect fails
print sprintf("<p>error %s %s %s %d:%s\n",$DB_SERVER, $DB_LOGIN, '****', mysql_errno(), mysql_error());
return(0);
} elseif (!(mysql_select_db($DB))) {
print sprintf("<p>error %d:%s\n", mysql_errno(), mysql_error());
return(0);
}
$sql = "SELECT * FROM $tableName";
if (!($result = mysql_db_query($DB, $sql))) {
print sprintf("<p>error %d:%s\n", mysql_errno(), mysql_error());
return(0);
}

if (mysql_num_rows($result)){
$row = mysql_fetch_array ($result);
print "<p>success! " . $row[0];
}
 
?>
Average of ratings: -
In reply to Peter Bloomfield

Re: Can't connect to MySQL server on external DB.... utf-8?

by Ray Schumacher -
One other thing Peter,
Can we get Moodle to call a custom script instead of the usual mySQL connect action when a user is logging in?
If so, then I can write scripts for each end that to local SELECTs and INSERTs and pass the data via cURL.

Thanks,
Ray
Average of ratings: -
In reply to Peter Bloomfield

Re: Can't connect to MySQL server on external DB.... utf-8?

by Ray Schumacher -
I was just able to remote log into one of them using my simple php script.
From
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html
  • Check whether the server is running on that host by executing telnet some_host 3306 and pressing the Enter key a couple of times. (3306 is the default MySQL port number. Change the value if your server is listening to a different port.) If there is a MySQL server running and listening to the port, you should get a response that includes the server's version number. If you get an error such as telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port.
>telnet x.x.x.x 3306
works as well for THAT server. So it looks like a port/firewall issue for the hosting.

Thanks,
Ray

Average of ratings: -
In reply to Ray Schumacher

Re: Can't connect to MySQL server on external DB.... utf-8?

by Peter Bloomfield -
Hmmm... some of what you're saying does sound like there's some security restrictions in place. In think that's quite common on hosts, which can be a pain, but it does make everything (a bit) more secure.

I guess it's theoretically possible to make the authentication db check work via cURL, although I wouldn't really know where to start on that I'm afraid. It's pretty easy to bypass the initial installation checks for utf-8 etc. though... just create your config.php script manually (copy the sample one that's there). When you access the site in your browser after that, it will take you straight to database table setup step. Not quite sure what will happen if you do that... hopefully it shouldn't be a problem if you don't use any language but English.
Average of ratings: -