Connecting to a remote database through CodeIgniter

Question

Unable to connect to your database server using the provided settings.

I've been having difficulty connecting my application to my remote database. I'm receiving the error shown above when I run my application. I've looked at previous answers to this question on this site, but I've gotten no luck at solving it. From what I learnt, I need to replace the hostname and make my mysql database accept external connections, which I have both done. I tried the same values in my database.php through the mysql command-line and it works.

So what else could I possibly be missing?

Here are my settings in database.php:

$active_group = 'staging';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'myDatabase';
$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;

$db['staging']['hostname'] = 'XX.XX.XXX.XXX';
$db['staging']['username'] = 'user';
$db['staging']['password'] = 'pwd';
$db['staging']['database'] = 'myDatabase';
$db['staging']['dbdriver'] = 'mysql';
$db['staging']['dbprefix'] = '';
$db['staging']['pconnect'] = TRUE;
$db['staging']['db_debug'] = TRUE;
$db['staging']['cache_on'] = FALSE;
$db['staging']['cachedir'] = '';
$db['staging']['char_set'] = 'utf8';
$db['staging']['dbcollat'] = 'utf8_general_ci';
$db['staging']['swap_pre'] = '';
$db['staging']['autoinit'] = TRUE;
$db['staging']['stricton'] = FALSE;

how did you try so far? give the code of database config. – Ariful Islam Nov 12 '11 at 20:51 — Ariful Islam, Nov 12 '11 at 20:51
I've edited my question with my database config. – Mikey Nov 12 '11 at 21:18 — Mikey, Nov 12 '11 at 21:18

score 4 · Accepted Answer · answered Nov 12 '11 at 20:51

4

try this command from your shell to see if you can connect:

mysql -h HOSTNAME -uUSERNAME -p DATABASE

also check to see if you don't have a different port to connect to mysql set, by default codeigniter will connect to 3306, unless you define the actual port set. this might be the issue.

good luck

answered Nov 12 '11 at 20:51

Robert Van Sant

1,497
10
12

yes you should see the mysql prompt, but it looks like you're on windows since you're using an exe, that command is meant for unix based machines. are you using the staging config to connect like so: `$this->db_staging= $this->CI->load->database('staging', TRUE);`? – Robert Van Sant Nov 12 '11 at 21:47
Sorry about removing previous comments, I realized that I didn't write your command correctly. No, I am not using the staging config like that. No, I only load my database in the constructor as such: $this->CI->load->database(). – Mikey Nov 12 '11 at 22:04
ok, but you have 2 definitions defined, and since it's an array, it will return the default definition, so you should specify your connection definition. i would suggest define which connection you want to use, for if you don't have a localhost db running on your staging server, but a remote connection then it will fail and prompt that error – Robert Van Sant Nov 12 '11 at 22:10
From what I found out, it loads up the correct group e.g. staging because the array values are used when it tries to connect: return @mysql_connect($this->hostname, $this->username, $this->password, TRUE) on line 70 of system/database/drivers/mysql/mysql_driver. However, it's that line that is failing which led me to this error as shown in this [question](http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication). Anyways, thanks a lot for your help! – Mikey Nov 12 '11 at 23:25
which driver is your remote database using, check your mysql config (mysql.cnf, i believe) to make sure it's not using mysqli vs mysql – Robert Van Sant Nov 12 '11 at 23:33
The cause of my error was exactly because of the [old password scheme used before MySQL 4.1](http://stackoverflow.com/questions/1575807/cannot-connect-to-mysql-4-1-using-old-authentication). Unfortunately I've no control over of the MySQL server, so I had to downgrade my PHP version :( – Mikey Nov 14 '11 at 06:37

score 3 · Answer 2 · answered Nov 12 '11 at 20:51

mysql by default accepts only localhost connections, make sure your server is configured properly locally

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

make sure that your server isn't behind a router that's in a private subnet, you might neet to configure static NAT for portforwarding your server into the web

score 1 · Answer 3 · edited Feb 08 '17 at 07:45

1

$active_group = 'staging';
$active_record = TRUE;

try to change above code to

$active_group = 'default';
$active_record = TRUE;

edited Feb 08 '17 at 07:45

Tom Brunberg

20,312
8
37
54

answered Feb 07 '17 at 03:12

Mia Torres

21
8

score 0 · Answer 4 · answered Aug 02 '17 at 09:03

0

If you have enforced SELinux Policy in your server make sure you enable httpd_can_network_connect boolean flag

setsebool -P httpd_can_network_connect=1

answered Aug 02 '17 at 09:03

Gopal Gautam

369
1
3
9

Connecting to a remote database through CodeIgniter

4 Answers4

Linked