Codeigniter error when trying to connect to database using mysqli

Question

I am getting Following error in my CodeIgniter application which is live on server.

Here is the output of the error:

A PHP Error was encountered

Severity: Warning

Message: mysqli::real_connect(): (HY000/1044): Access denied for user 'xxx'@'localhost' to database 'xxx'

Filename: mysqli/mysqli_driver.php

Line Number: 161

Backtrace:

File: /home/arya123/public_html/application/controllers/Home.php Line: 7 Function: __construct

File: /home/arya123/public_html/index.php Line: 292 Function: require_once

In database file which extension r u using? `$config['dbdriver'] = "mysqli";` — devpro, Mar 07 '16 at 20:19
Seems like you don't have persmission to approach to that DB. Is it external DB or `localhost` (server's) one? — Tpojka, Mar 07 '16 at 21:42
Have you checked that the application/config/database.php settings are correct? — jtheman, Mar 07 '16 at 22:51
if you are using shared hosting su as godaddy, I suggest you check the user which you created, because I had same problem, but It was the problem of user's permission in mysql — Marmik Bhatt, Mar 08 '16 at 02:30
if you using OSX, this is the correct answer for you [CodeIgniter: Unable to connect to your database server using the provided settings Error Message](http://stackoverflow.com/a/11043416/5531595) — Rohman HM, Nov 26 '16 at 17:05
Make sure user `xxx` actually has the privileges to connect to database `xxx` — Nick, Jul 19 '17 at 05:03
try localhost:3306.. i have mysqlworkbench(localhost:3306) and phpmyadmin (localhost) — marlonpya, Jul 23 '17 at 21:08

score 6 · Answer 1 · answered Jun 18 '17 at 16:55

6

you have to set mysql port number which is by default 3306 check this in database.php

use either

'dbport' => '3306',

or

 'hostname' => 'mysql.hostingprovider.com:3306',

answered Jun 18 '17 at 16:55

Anurag Biradar

94
1
7

score 4 · Answer 2 · answered Oct 25 '19 at 06:25

Connect localhost with port solves the problem for me.

$db['default'] = array(
    'dsn' => '',
    'hostname' => 'localhost:3308', //Added port here
    'username' => 'root',
    'password' => '1234',
    'database' => 'my_database',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

score 1 · Answer 3 · answered Mar 08 '16 at 05:23

The same error I was getting when I upload my codeigniter project from localhost to Live server.

What solution I find is to make some changes into the application => config => database.php

Following is the database setting for the

localhost

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'creator',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Following database setting for

live server (Just demo, setting differ as per hosting provider)

1) First you have to create database.

2) find MySql Databases(or anything related database) on dashboard,

3) create new database and put database name, username, password.

4) export database from localhost

5) open phpmyadmin on live server and import it.

6) change the setting of application=>config=>database.php by using FTP client or dashboard.

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'mysql.hostingprovider.com',    
    'username' => 'abc.username',
    'password' => 'abc.password',
    'database' => 'abc.databasename',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Have you find any difficulty to following this steps? or you miss something? have you change hostname, username, password, dbname? please recheck it! — pra5hant, Mar 08 '16 at 06:35

score 1 · Answer 4 · answered Sep 05 '19 at 10:25

u can add new user and pass and give it all privileges, like below:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

then, u should update your config.php file, with new user and pass.

score 0 · Answer 5 · edited Sep 14 '18 at 13:49

0

Do the following:

In applications/config/database.php, add dbport => '3306', to the default array $db['default'] = array();
In system/database/DB_driver.php, change public $port = ''; to public $port = '3306';

edited Sep 14 '18 at 13:49

Wouter Vanherck

2,070
3
27
41

answered Jul 14 '18 at 04:58

พูลทรัพย์ บุญวัง

17
2

score 0 · Answer 6 · answered Nov 14 '18 at 08:34

Try to use the plain password with no special characters, below is the example:

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'password',
    'password' => 'Mynewpass@756',
    'database' => 'database_name',
);

score 0 · Answer 7 · edited Feb 27 '20 at 01:31

0

You can edit your server's php.ini file to define the port that CodeIgniter will use by default.

C:\wamp64\bin\apache\apache2.4.41\bin\php.ini

Default port number for mysqli_connect().

mysqli.default_port = 3308

edited Feb 27 '20 at 01:31

ButchMonkey

1,873
18
30

answered Feb 26 '20 at 20:18

jinx

1
2

score 0 · Answer 8 · answered Sep 26 '20 at 08:21

0

Try to set config/database.php 'username' => 'xxx' to 'username' => 'root' without any password.

$db['default'] = array( 
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',

answered Sep 26 '20 at 08:21

0Zef0

77
2
11

score 0 · Answer 9 · answered Jan 28 '22 at 17:30

0

verify is user has the rights privilege declare on database

answered Jan 28 '22 at 17:30

jeyglo

137
1
5

score 0 · Answer 10 · answered Dec 04 '22 at 05:46

i was having the same issue in CodeIgniter code. database and everything was correct but i fixed it by changing these lines

Before


    'dsn' => 'mysql:host=localhost;dbname=kiuurduapp',
    'hostname' => 'localhost',
    'username' => 'kiuurdu',

After


    'dsn' => 'mysql:host=127.0.0.1;dbname=kiuurduapp',
    'hostname' => '',
    'username' => 'kiuurdu',

and it works for me

score -1 · Answer 11 · edited Jul 13 '18 at 13:37

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'xxx',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

score -1 · Answer 12 · answered Aug 12 '18 at 20:48

I had the same trouble. Since I use xampp and I was running my codeigniter app locally, the problem at the end I solved it by remplacing: 'hostname' => 'localhost ' by 'hostname' => '127.0.0.1:33065' the :33065 is the port assigned by default by xampp for phpmyadmin. If you are running your proyect in the xampp default options, changing this, should be enough (hopefully). But if you don´t, I guess, you can search which port you are using for the database conection, and add it to your hostname with ":" (if you are not sure which port you are using, you always can see in your D.B. administration tool -phpmyadmin or the one you are using- on the options, which port is taking).

Codeigniter error when trying to connect to database using mysqli

12 Answers12

Linked