Connecting to external mysql database with codeigniter running on a local nginx server

Question

I have codeigniter (php framework) running on an nginx webserver on my local machine (Mac OSX 10.8).

I am trying to connect to an external mySQL database hosted with mediaTemple.

When loading in the database helper in codeigniter, with all the correct details, l keep getting:

  A Database Error Occurred
  Unable to connect to your database server using the provided settings.

The contents of my database.php file is:

  $db['default']['hostname'] = 'external-db.#######.gridserver.com';
  $db['default']['username'] = '#######';
  $db['default']['password'] = '#######';
  $db['default']['database'] = '#######_olliejennings';
  $db['default']['dbdriver'] = 'mysql';
  $db['default']['dbprefix'] = '';
  $db['default']['pconnect'] = TRUE;
  $db['default']['db_debug'] = TRUE;
  $db['default']['cache_on'] = FALSE;
  $db['default']['cachedir'] = '';
  $db['default']['char_set'] = 'utf8';
  $db['default']['dbcollat'] = 'utf8_general_ci';
  $db['default']['swap_pre'] = '';
  $db['default']['autoinit'] = TRUE;
  $db['default']['stricton'] = FALSE;

The contents of my nginx.conf file is:

  daemon off;

  user olliejennings staff;

  worker_processes 2;
  pid /opt/boxen/data/nginx/nginx.pid;

  events {
     worker_connections 1024;
  }

  http {
     include mime.types;
     default_type application/octet-stream;

     log_format main '$remote_addr - $remote_user [$time_local] $status '
                     '"$request" $body_bytes_sent "$http_referer" '
                     '"$http_user_agent" "http_x_forwarded_for"';

     access_log /opt/boxen/log/nginx/access.log main;
     error_log  /opt/boxen/log/nginx/error.log debug;

     sendfile on;

     tcp_nopush  on;
     tcp_nodelay off;

     gzip              on;
     gzip_http_version 1.0;
     gzip_comp_level   2;
     gzip_proxied      any;

     upstream www-upstream-pool {
          server unix:/tmp/php-fpm.sock;
     }

     server {
          listen      80;
          server_name localhost;

          location / {
               root   /opt/boxen/config/nginx/public;
          }
     }

     include /opt/boxen/config/nginx/sites/*;
  }

And the contents of my sites/olliejennings.conf file is:

  server {
       server_name      olliejennings.dev;
       root         /Users/olliejennings/Code/olliejennings;
       index        index.php index.html index.htm;

       location ~* \.(ico|css|js|gif|jpe?g|png)(\?[0-9]+)?$ {
         expires max;
         log_not_found off;
         add_header Pragma public;
         add_header Cache-Control "public, must-revalidate, proxy-revalidate";
       }

       location / {
         # Check if a file exists, or route it to index.php
         try_files $uri $uri/ /index.php;
       }

       location ~ \.php$ {
         include fastcgi_php_default.conf;
         fastcgi_pass 127.0.0.1:9000;
         fastcgi_index index.php;
         fastcgi_param SCRIPT_FILENAME $document_root/index.php;
       }
   }

NB: the php website works fine if i don't try and connect to the external database.

EDIT - Found differences is the mySQL settings between nginx and apache

This is what phpinfo() throws out when l connect to the database on apache:

mySQL config when using apache

This is what the phpinfo() throws out when using nginx as l can't connect to the database:

mySQL config when using nginx

Also the socket for mySQL when using nginx is wrong as it is not located at

/var/mysql/mysql.sock

But instead located at:

/temp/mysql.sock

I have chnaged these settings in the php.ini file, yet they don't appear to be read by php when using nginx, it appears to ignore anything l change within that file.

Is your remote database allowed to accept remote connections? — John Corry
– John Corry, Commented Apr 11, 2013 at 13:45
Yes the database is allowed to accept remote connections, l can connect to this database via phpmyadmin or through sequel Pro — Ollie Jennings
– Ollie Jennings, Commented Apr 11, 2013 at 13:52
Did you triple check the username and pass? There shouldn't be anything explicitly denying your request... — stormdrain
– stormdrain, Commented Apr 11, 2013 at 14:54
Try adding $db['default']['port'] = 3306; to the db conf file. Maybe it needs to be explicitly set for some reason. Also, maybe try writing a little script with native php mysqli and running it on nginx to make certain it's an issue with nginx. — stormdrain
– stormdrain, Commented Apr 11, 2013 at 17:13

Community · Accepted Answer · 2017-05-23 12:12:35Z

1

Okay so l have finally managed to fix this issue.

When l tried using native php mysqli that stormdrain suggested l was getting the message:

Connect failed: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication.

So to fix this issue l did the following:

Connect to mysql database using phpmyadmin
Ran the following query SHOW VARIABLES LIKE 'old_passwords'
If this returns ON then l knew that the problem lied with the database.
Run this query to turn this variable off - SET SESSION old_passwords=FALSE;

Then l was able to connect to my mySQL database with out any problems or code change.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Apr 12, 2013 at 9:03

Ollie Jennings

921 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connecting to external mysql database with codeigniter running on a local nginx server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related