mysql c++ connector exceptions: Access denied for user 'root'@'localhost' (using password: NO) && MySQL server has gone away

Question

I am trying to using the MySQL c++ connector to connect MySQL database on the local machine.

I installed the MySQL c++ connector 7.1.1.9 and boost. In the c++ IDE. I included header path and the connector library file libmysqlcppconn-static.a into the project. The project compiles fine.

But when I try to run the example to just connect a local database.

try{
    sql::mysql::MySQL_Driver *driver;
    sql::Connection *con;

    driver = sql::mysql::get_mysql_driver_instance();
    // I tried the user root here, but got same exception
    //con = driver->connect("tcp://127.0.0.1:3306", "root", "password");
    con = driver->connect("tcp://127.0.0.1:3306", "user", "password");

    delete con;
}
catch(sql::SQLException &e)
{ 
    // Edit1: fix code based on Macxx's comments
    // a access denied exception is thrown from here.
    std::cerr << e.what() << '\n'; 
}

An exception
mySQL exceptions: Access denied for user 'root'@'localhost' (using password: NO)
is thrown. I checked that the MySQL is installed correctly by accessing it in the terminal:

$ mysql -u root -p
Enter password: // <---entered "password" here, so I am sure the password is correct
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

It seems the connector is ignoring the second parameter. I also tried to use the structure ConnectOptionsMap to set ['password'] to "password". Still not working properly.
Any suggestion to fix the exception?

---

A little something about after the connection is granted: I had a very difficult time using the binary libraries (libmysqlcppconn.so / libmysqlcppconn-static.a) downloaded from the Oracle MySQL official website. If I execute
con->setSchema("AN_EXISTING_DB")
The app throws an exception: MySQL server has gone away;
If I use :

sql::Statement *st = con->createStatement();
st->execute("use AN_EXISTING_DB");` 

The app crashes due to bad allocation.

Eventually, I solved this problem by using the packages from apt-get (They are early version of the sqlcppconn).
sudo apt-get install libmysqlcppconn-dev Then I changed the project file to link the libraries in /usr/lib/libmysqlcppconn.so and /usr/lib/x86_64-linux-gnu/libmysqlclient.so.

[environment]
Ubuntu 16.04.2
MySQL 5.7
MySQL connector version: 7.1.1.9
MySQL connector version: 7.1.1.7
boost 1.58.0
g++ 5.4.0

Thanks in Advance

Rong

Answer 1

I used only postgres before, but I think you have to grant the rights to access the database. Look at some of these threads, I hope they will help you:

• MYSQL Access denied for user 'root'@'localhost'
• MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
• 'Access denied for user 'root'@'localhost' (using password: NO)'

In postgres you have to change the config file (pg_hba.conf) for the database to allow remote access..

---

I think you want to print out thrown exceptions. Change the last line to

{
    std::cerr << e.what() << std::endl;
}

Answer 2

In /etc/mysql/my.cnf, there will be a bind-address line. Please set it to

bind-address = 0.0.0.0

Then restart mysql service.

Next try to execute your program.

Also while creating mysql database, user make sure permissions are proper, you can refer below instructions (valid on linux / ubuntu)

CREATE USER 'user_user'@'localhost' IDENTIFIED BY 'user_password';
CREATE USER 'user_user'@'%' IDENTIFIED BY 'user_password';
GRANT ALL ON *.* TO 'user_user'@'localhost';
GRANT ALL ON *.* TO 'user_user'@'%';

create database user_database;

grant usage on *.* to user_user@'localhost' identified by 'user_password';
grant usage on *.* to user_user@'%' identified by 'user_password';

grant all privileges on user_database.* to user_user@'localhost' ;
grant all privileges on user_database.* to user_user@'%' ;

And if you are writing code on Qt, below code works for me

    QSqlDatabase db;

    db = QSqlDatabase::addDatabase("QMYSQL");

    db.setHostName(DBHost);
    db.setDatabaseName(DBName);
    db.setUserName(DBUserName);
    db.setPassword(DBPassword);
    db.setPort(DBPort);


    if (!db.open())
    {
        QMessageBox::critical(this,"Error","Could not connect to database.");
    }