Error reading from database

Error reading from database

by M C -
Number of replies: 4

Hi

I'll try to keep this as straight to the point as I can, but I do ramble a bit big grin

So a bit of background information.  All of our Moodle experts have left our company and we need to move our MSSQL DB from a 2008 R2 Server to a 2016 instance on a different DB server.  My post here https://moodle.org/mod/forum/discuss.php?d=387570 was help with the migration of the DB.

Now before I do that I wanted to understand how Moodle works, so I was looking at the setup for our Moodle Test system.  Now when I load up my test URL I get the below error:

Error reading from database

My config.php file is pointing to our new Windows 2016 MSSQL Test Database Server instance (before it was on a dedicated server) - below is the DB extract from my config.php file but I have changed the private information.

//=========================================================================
// 1. DATABASE SETUP
//=========================================================================
// First, you need to configure the database where all Moodle data       //
// will be stored.  This database must already have been created         //
// and a username/password created to access it.                         //

$CFG->dbtype    = 'mssql';      // 'pgsql', 'mariadb', 'mysqli', 'mssql', 'sqlsrv' or 'oci'
$CFG->dblibrary = 'native';     // 'native' only at the moment
//$CFG->dbhost    = 'dbserver1.website.org.uk';  // eg 'localhost' or 'db.isp.com' or IP
//$CFG->dbhost    = 'dbserver2.website.org.uk';
$CFG->dbhost    = 'new2016testserver.website.org.uk';
//$CFG->dbhost    = '.website.org.uk';
$CFG->dbname    = 'MoodleTest';     // database name, eg moodle
//$CFG->dbname    = 'changed';     // database name, eg moodle
$CFG->dbuser    = 'thishasbeenchanged';   // your database username
$CFG->dbpass    = 'ihavechnagedthis';   // your database password
$CFG->prefix    = 'mdl_';       // prefix to use for all table names
$CFG->dboptions = array(
    'dbpersist' => false,       // should persistent database connections be
                                //  used? set to 'false' for the most stable
                                //  setting, 'true' can improve performance
                                //  sometimes
    'dbsocket'  => false,       // should connection via UNIX socket be used?
                                //  if you set it to 'true' or custom path
                                //  here set dbhost to 'localhost',
                                //  (please note mysql is always using socket
                                //  if dbhost is 'localhost' - if you need
                                //  local port connection use '127.0.0.1')
//    'dbport'    => 'oldportnumber',          // the TCP port number to use when connecting
    'dbport'    => '1433',          // the TCP port number to use when connecting
                                //  to the server. keep empty string for the
                                //  default port


The DB server is in one DMZ on our network and the Application/Webserver in another.  Firewall rules have been created between the app server and the SQL instance on the DB.  We can telnet from the app server to the DB Server on the port and we have even created an ODBC connection on the App Server to the DB Server using the DB username and password and it connects through without an issue.  

I read somewhere on the forums about someone using 1433 as the port in the config and spoof it to the real DB port, but my Network guys thing we shouldn't have to do that (please note: the config file has port 1433 and the open DB port set but the same error shows)

We are running Moodle version 3.1 (Build: 20160523) on Windows 2012 R2

I would like to get this up and running and familiarise myself before I move our Live DB to a new instance on a DB Server

I Hope I haven't forgotten anything.

Thank you


Average of ratings: -
In reply to M C

Re: Error reading from database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Was/is the Moodle test site able to connect to the old SQL Server database?

What version of PHP do you have on the Moodle test web server? If it's PHP 7.0 or greater then $CFG->dbtype needs to be 'sqlsrv' with the Microsoft driver installed. If it's PHP 5.x 'mssql' should work if the FreeTDS (dblib) driver is installed. Find out the PHP version by opening a command prompt window, changing to the PHP directory (if it's not already on the path) and running php --version.

Is the database on the new SQL Server not the default instance? In which case try specifying this, e.g. $CFG->dbhost = 'new2016testserver.website.org.uk\instance_name';.

Also, if you add the following lines to config.php the error message you get should include more detail:

@error_reporting(E_ALL | E_STRICT);
@ini_set('display_errors', '1');
$CFG->debug = (E_ALL | E_STRICT);
$CFG->debugdisplay = 1;
Average of ratings: -
In reply to Leon Stringer

Re: Error reading from database

by M C -
Hi Leon

Thank you for your reply. We are on PHP5.6 - I cannot honeslty say if it used to work as i have been handed the support for Moodle now the others have gone and had no involvment in the past or handover.
The database is not on a defualt instance so i have set, as per your instructions, the instance onto the end of the dbhost and now i get:
Error: Database connection failed
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php

I added in the additional code to the config.php and the result is - xxxxx = the DB port used
Debug info:
Warning: mssql_connect(): Unable to connect to server: new2016testserver.website.org.uk\instance_nameJ,xxxxx in [dirroot]\lib\dml\mssql_native_moodle_database.php on line 176

Error code: dbconnectionfailed
Stack trace:
line 183 of \lib\dml\mssql_native_moodle_database.php: dml_connection_exception thrown
line 344 of \lib\dmllib.php: call to mssql_native_moodle_database->connect()
line 612 of \lib\setup.php: call to setup_DB()
line 839 of \config.php: call to require_once()
line 30 of \index.php: call to require_once()
Average of ratings: -
In reply to M C

Re: Error reading from database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

That's a step forward. The way I'd troubleshoot this is with a short script to test different settings.

If you create a text file called test_mssql.php containing:

<?php
$link = mssql_connect('new2016testserver.website.org.uk\instance_name', 'the_username', 'the_password');

if (!$link) {
    echo "Error connecting to SQL Server." . PHP_EOL;
} else {
    echo "Connection successful." . PHP_EOL;
}

and see if you can work out the settings because if they work in the script they should work for Moodle.

The documentation suggests that you should specify either an instance name or a port but not both, so maybe try 'new2016testserver.website.org.uk\instance_name' or 'new2016testserver.website.org.uk,xxxxx' and see if that helps. If the former works, try it with 'dbport' => '1433' (if I understand correctly the server then directs the client to the correct port for the named instance).

Average of ratings: -
In reply to Leon Stringer

Re: Error reading from database

by M C -
Thanks Leon - I have fixed it, by way of using $CFG->dbhost = 'new2016testserver.website.org.uk:xxxxx'; where xxxxx is the port number belonging to the instance on the DB instance.
(note I used a : to seperate the port and DB Server name)
I also commented out //'dbport' => '1433', and that allowed my test site to load.
Now that I know what needs doing I can prepare for my Live DB Migration.

Thanks ever so much for your assistance

M C
Average of ratings: -