Welcome Guest, Not a member yet? Register   Sign In
Create a custom MySQL connection error
#1

When the MySQL server isn't started, or not reachable over the network, or if the creds are incorrect, the CI will show a PHP error saying the MySQL connection failed. I was wondering if there was a way to clean that up somehow? Can I do a custom check somewhere to manually check the MySQL connection using the settings in the config/database.php file?

Im assuming I will be throwing this in a hook, so I was wondering first, which hook? would pre_system work? (Is it ran before the DB connection is made?) And will the configs be loaded before the pre_system is ran? If not, should I just include it? (Im assuming the config library wont be loaded, so I cant just load the configs)

Thanks!
Reply
#2

I answered a lot of my questions above, just to come up with more questions :-D hah

Heres the code thus far, (obviously in dev, hence all the die() statements)

PHP Code:
<?php
defined
('BASEPATH') OR exit('No direct script access allowed');

class 
App_hooks
{
    public function 
pre_system_check_mysql()
    {
        require(
APPPATH.'config/database.php');

        
$creds $db[$active_group];

        if( ! 
$conn = @mysqli_connect($creds['hostname'], $creds['username'], $creds['password']))
        {
            die(
'Cant connect');
        }

        if (
mysqli_connect_errno())
        {
            die(
"Failed to connect to MySQL: " mysqli_connect_error());
        }

        if( ! @
mysqli_select_db($conn$creds['database']))
        {
            die(
"Cant select DB ({$active_group})");
        }

        die(
'OK');
    }


This works, but obviously it only works with the mysqli driver. This application will actually be distributed as Open Source, so its possible that whoever uses it, uses a different driver.

I know the DB_Driver library has methods to accommodate for this, but obviously it isn't loaded yet.

What would you recommend I do? Or do I really even have any other option? (Short of mimicking the db_connect and db_select methods in the DB_Driver manually)

Thanks!
Reply
#3

Bonfire's installer had to do something similar, and we ended up with a somewhat nasty set of switch() statements to do different things based on the selected driver. In your case, you would probably get the driver from $db[$active_group]['dbdriver'].

We have a db_available() method which checks whether the required driver is available in PHP:
PHP Code:
switch ($driver) {

    case 'mysql':
        return function_exists('mysql_connect');
    case 'bfmysqli':
    case 'mysqli':
        return class_exists('Mysqli');
    case 'cubrid':
        return function_exists('cubrid_connect');
    case 'mongodb'// deprecated
        return class_exists('Mongo');
    case 'mongoclient'// I don't believe we have a driver for this, yet
        return class_exists('MongoClient');
    case 'mssql':
        return function_exists('mssql_connect');
    case 'oci8':
        return function_exists('oci_connect');
    case 'odbc':
        return function_exists('odbc_connect');
    case 'pdo':
        return class_exists('PDO');
    case 'postgre':
        return function_exists('pg_connect');
    case 'sqlite':
        return function_exists('sqlite_open');
    case 'sqlsrv':
        return function_exists('sqlsrv_connect');
    default:
        return false;


Then we have another method to test the connection (which calls the previous method before continuing to make sure it's not a waste of time to try to connect):
PHP Code:
switch ($driver) {

    case 'mysql':
        return @mysql_connect("$hostname:$port"$username$password);
    case 'mysqli':

        $mysqli = new mysqli($hostname$username$password''$port);
        if (! $mysqli->connect_error) {
            return true;
        }
        return false;
    case 'cubrid':
        return @cubrid_connect($hostname$port$db_name$username$password);
    case 'mongodb'// deprecated
        $connect_string $this->getMongoConnectionString($hostname$port$username$password$db_name);
        try {
            $mongo = new Mongo($connect_string);
            return true;
        } catch (MongoConnectionException $e) {
            show_error('Unable to connect to MongoDB.'500);
        }
        return false;
        break;
    case 'mongoclient'// no driver support at this time
        $connect_string $this->getMongoConnectionString($hostname$port$username$password$db_name);
        try {
            $mongo = new MongoClient($connect_string);
            return true;
        } catch (MongoConnectionException $e) {
            show_error('Unable to connect MongoClient.'500);
        }
        return false;
        break;
    case 'mssql':
        return @mssql_connect("$hostname,$port"$username$password);
    case 'oci8':
        $connect_string $this->getOracleConnectionString($hostname$port);
        return @oci_connect($username$password$connect_string);
    case 'odbc':
        $connect_string $this->getOdbcConnectionString($hostname);
        return @odbc_connect($connect_string$username$password);
    case 'pdo':
        $connect_string $this->getPdoConnectionString($hostname$db_name);
        try {
            $pdo = new PDO($connect_string$username$password);
            return true;
        } catch (PDOException $e) {
            show_error('Unable to connect using PDO.'500);
        }
        return false;
        break;
    case 'postgre':
        $connect_string $this->getPostgreConnectionString($hostname$port$username$password$db_name);
        return @pg_connect($connect_string);
    case 'sqlite':
        if (! $sqlite = @sqlite_open($db_nameFILE_WRITE_MODE$error)) {
            show_error($error500);
            return false;
        }
        return $sqlite;
    case 'sqlsrv':
        $connection $this->getSqlsrvConnection($username$password$db_name);
        return sqlsrv_connect($hostname$connection);
    default:
        return false;


As you can see, there are a number of additional methods to generate connection strings for many of the database drivers.

Generally, if those checks passed, we were comfortable loading the database library and using table_exists() and get() to perform additional checks. Unfortunately, I haven't done much in the way of performance testing on this code (and it could probably use some updating), because it is used primarily in the installer.
Reply
#4

Awesome, thank you. Im also going to need to create an installer for this app, so this is helpful
Reply
#5

(This post was last modified: 10-19-2015, 03:57 PM by jLinux.)

@mwhitney, Im looking for a way to create a decent installer for this application. Do you have any recommendations?

I was thinking have it so when they git clone/untar/unzip/whatever the files initially, and go to the folder in the browser, it would show them a web interface that they can use to configure the app, something along the lines of...
  1. Check proper PHP version and PHP dependencies
  2. Check the proper file/folder permissions
  3. Check other system related stuff (EG: Resources)
  4. Have them specify the database creds, test them, and update the database.php file (If perms allow)
  5. Create the first administrative user
  6. etc
My question is, where should the PHP code for all this go? I was thinking maybe have the setup code in its own folder, then once the setup is 100% complete, just have the whole setup folder deleted... Ive seen that before, I actually thought it was pretty neat. lol
Reply
#6

Bonfire used to do most of that stuff, except for deleting the installer. In the last couple of years, it has been simplified quite a bit, primarily removing the database-related functionality.

I believe the main reasons for removing the database-related functionality were that it was difficult to support the flexibility of the database config file, write the file, and validate the user entry (plus it's a pretty major potential security issue to worry about if you can write to the database config file).

It's also useful to have a page in the admin area of the application to display the results of some of the checks in many cases (for example, Bonfire's sysinfo module displays the Bonfire and CodeIgniter versions, PHP version, server/local time, database info, various paths, site_url(), environment, and whether a configurable list of directories/files are writable (by default the cache, logs, and config directories and the application and database config files are in the list).

The is_really_writable() function is usually all you need to check file/directory permissions.

As for where the code should go, I would recommend a couple of things:
- Isolate the majority of the code, especially anything that writes to a file or the database (especially creating the admin user)
- If you want to share some code between the installer and another part of the application, and you don't want duplicate code, put it in a library or helper that can be loaded by the installer and anything else that is going to use it.
- Setup a method of easily enabling/disabling the installer for development
- If you're going to have the installer self-destruct, setup a method of easily disabling that feature for development
- On first login, remind (or force) the admin to change their password and/or username
- You may want to put a reminder on the default admin page to check the configuration for common security issues and ensure the installer is not placed on a live production site (or, depending on the needs of the application, check these in the code and warn the user accordingly)

Just to give you an idea of how Bonfire handles the installer, especially since it can be difficult to figure out how the installer is loaded and run in the first place:

The root index.php displays a welcome message, tells the user to create the database (they don't have to create any tables, but the database has to be created) and edit their database config file. It also displays a warning that their site is not configured correctly, with some information regarding configuring the site to point to /public/index.php.

Once they have the site configured to point to /public/index.php (or they've moved things around and updated the directories in the index.php file if they can't place directories and files outside their web root), Bonfire's default controller loads the installer_lib to check whether the site is installed, and, if not, temporarily disables hooks and redirects the user to the 'install' controller, which manages the installation.

Bonfire is configured by default with a pre_controller hook which normally saves the requested page in the session data (for use after login if the page requires authentication). The constructor of the class in which this hook (and all of Bonfire's built-in hooks) resides looks for a config item named 'bonfire.installed', which is set by the installer near the end of the installation process. If it can't find it (or the value is not truthy), the installer_lib is loaded to do a more comprehensive check to determine whether Bonfire has been installed. The only thing the hooks really do with this information is disable the use of sessions in the pre_controller hook, since that hook will fire before the default controller has a chance to determine whether to redirect to the install controller (and will fire again before the install controller is loaded).

The install controller disables hooks and disables session use in the Template library, then loads the installer_lib and some other libraries/helpers/language files. The first page of the installer checks the PHP version and whether certain files/directories are writable, instructing the user to verify the configuration and follow the link to the next page (install/do_install).

The next page checks whether the application has been installed, and exits with an error message if it has (because allowing an arbitrary site visitor to run the installer again would be bad) ('This application has already been installed. Cannot install again.'). Next, it checks whether the installer could verify the database settings, and displays an error message if it could not ('Unable to locate proper database settings. Please verify settings and reload the page.'). Finally, it runs the setup() method in the installer_lib, which loads the database, installs the Bonfire migrations, configures some site settings (these used to be passed in via a form in the installer, but now it just sets some defaults), and creates the admin user. Then it runs any application module migrations it can find and writes out a text file in the config directory and the 'bonfire.installed' config value mentioned earlier, both of which are used to tell different parts of the application that Bonfire has been installed.

Finally, it displays the username and password for the default admin account and gives the user some useful links (to the admin page, the public page, the docs, and the CodeIgniter docs).
Reply




Theme © iAndrew 2016 - Forum software by © MyBB