MariaDB connection issue

[ihmc3jn09hk]

Describe the bug
Fail on the connection to MariaDB when testing the model create ctl.
Copied the default model.json into a folder named /models/testModel and run the following command.

drogon_ctl create model testModel

Create model
mysql
Connect to server...
Source files in the eshop folder will be overwritten, continue(y/n)?
20200202 17:04:55.725077 UTC 3864 ERROR Failed to mysql_real_connect() - MysqlConnection.cc:229
20200202 17:04:56.725637 UTC 3864 ERROR Failed to mysql_real_connect() - MysqlConnection.cc:229
20200202 17:04:57.725777 UTC 3864 ERROR Failed to mysql_real_connect() - MysqlConnection.cc:229
...

The database is running which I can use PhpMyAdmin to access it. Created a database name "cppwebserver" and some other credentials for testing. I tried different ports (failed) and thought there was a bug in the model.json ( "passwd" to "password" ) still failed.

BTW, THANKS for the good works to the C++ community. I would like to take deeper understanding on this web-framework and compare with the Cutelyst and Wt. Are there some examples for the ORM part? Say, using the default model in the model.json.

To Reproduce
Steps to reproduce the behavior:

1. Go to '/models' directory
2. mkdir testModel && cp model.json testModel/
3. edit the parameters "rdbms": mysql, "port":3306, "host":"127.0.0.1", "dbname":"cppwebserver".......
4. drogon_ctl create model testModel and see error

Expected behavior
ERROR Failed to mysql_real_connect() - MysqlConnection.cc:229
Desktop (please complete the following information):

• OS: Linux

[an-tao]

@ihmc3jn09hk Try to use mysql -h127.0.0.1 -uuser_name -p cppwebserver to confirm the right privileges.
https://github.com/an-tao/drogon/blob/ce675c1b871c9961a5b47d2e4c6cc1064a9296e8/orm_lib/tests/db_test.cc#L512-L533

There are some examples of ORM in the test project.

[ihmc3jn09hk]

@an-tao With mysql -hlocalhost -umariadb_user -p cppwebserver, I can login (Yup, changed to localhost). Modified the corresponding parameters "127.0.0.1" -> "localhost" in model.json failed with the following information:

Create model
mysql
Connect to server...
Source files in the eshop folder will be overwritten, continue(y/n)?
y
No connection to database server

[ihmc3jn09hk]

@an-tao Further tested with this library(see /test/connect.cpp) which results in a successful connection.

$ test/connect
DB uri: tcp://localhost:3317
DB user: mariadb_user
DB passwd: mariadb_user_password
Connection status: SUCESS
MySQL Stat: Uptime: 402  Threads: 7  Questions: 16  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 10  Queries per second avg: 0.039

[ihmc3jn09hk]

@an-tao Update: The error "ERROR Failed to mysql_real_connect() - MysqlConnection.cc:229" doesnt show up if direct key in the parameters in MysqlConnection.cc (e.g. Host, username, ...).
However, there is nothing generated for the model.

[an-tao]

@ihmc3jn09hk , After some inverstigations of this. I think this may be a issue of the asynchronous API of mariadb earlier version. This issue ocurrs when calling the mysql_real_connect_start() function with a hostname(or domain name). there are two solutions.

1. use IP address(127.0.0.1) instead of hostname('localhost')

You shoud grant right privileges to the user as follows:

MariaDB [(none)]> create user 'admin'@'%' identified by '123';
MariaDB [(none)]> grant all privileges on *.* to 'admin'@'%' with grant option;

2. Or update the libmariadb to 10.4 version or newer.

For example, install mariadb10.4 on ubuntu1804

sudo apt remove ....
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository "deb [arch=amd64,arm64,ppc64el] http://mariadb.mirror.liquidtelecom.com/repo/10.4/ubuntu $(lsb_release -cs) main"
sudo apt update
sudo apt -y install mariadb-server mariadb-client
apt install libmariadb-dev

For more OSs, see here

[an-tao]

Both solutions above have been tested and work properly.

[an-tao]

@an-tao Further tested with this library(see /test/connect.cpp) which results in a successful connection.

$ test/connect
DB uri: tcp://localhost:3317
DB user: mariadb_user
DB passwd: mariadb_user_password
Connection status: SUCESS
MySQL Stat: Uptime: 402  Threads: 7  Questions: 16  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 10  Queries per second avg: 0.039

The synchronous API used by this library is different from the asynchronous API used by drogon.

[an-tao]

There are some database examples in the TFB benchmark suits

[ihmc3jn09hk]

@an-tao May I know the command 'create model' creates table(s) automatically ? Or user have to create the tables on prior???

[an-tao]

@an-tao May I know the command 'create model' creates table(s) automatically ? Or user have to create the tables on prior???

This command reads the tables in the database and creates a model source file for each table. So, yes, users have to create the tables on prior.

[ihmc3jn09hk]

Problem resolved. I misunderstood the functionality of "create model". I thought it would create tables from C++ classes and maintain the OO/member variables of other classes/references relationship into DB tables. But now "create model" works the other way round. So the connection issue actually is because of the empty database.

@an-tao BTW, would there be any support for NoSQL type DB? I think it is much better suit for Drogon.

[an-tao]

Problem resolved. I misunderstood the functionality of "create model". I thought it would create tables from C++ classes and maintain the OO/member variables of other classes/references relationship into DB tables. But now "create model" works the other way round. So the connection issue actually is because of the empty database.

Yes, this is a no migrations approach, as it means you can deal with your databases in their own language and do the migration workflow separate from the rest of the project, being able to take full advantage of the existing workflows and tools for creating databases.

@an-tao BTW, would there be any support for NoSQL type DB? I think it is much better suit for Drogon.

I will add support for Redis, MongoDB, but this will take time. Thank you for your suggestion.

[ihmc3jn09hk]

@an-tao Ok, the previous problem is kind of resolved. But found an interesting issue for many-to-many
A [ id, name, age, weight, height ] /Personal Info/
B [ id, from_id, to_id ] /Candies exchanges/

             {
                "type": "many to many",
                "original_table_name": "A",
                "original_table_alias": "from",
                "original_key": "id",
                "pivot_table": {
                    "table_name": "B",
                    "original_key": "from_id",
                    "target_key": "to_id"
                },
                "target_table_name": "A",
                "target_table_alias": "to",
                "target_key": "id",
                "enable_reverse": true
            }

The drogon_ctl produces getTo() relationship interface only.

[an-tao]

@ihmc3jn09hk Sorry for late reply, I'll do some testing for this and then give you an answer.

[an-tao]

About the relationship idea, please refer to #241

[ihmc3jn09hk]

@an-tao Take your time. Thanks for the reference

[an-tao]

@ihmc3jn09hk I fixed the 'many to many' issue in PR #369 , please check.

[ihmc3jn09hk]

@an-tao Will check it out when merged

[ihmc3jn09hk]

Another quick question, on the Db connection. Everytime drogon connects to the DB when startup, the connection can hardly be established for certain duration (see below). This happens to
drogon_ctl create model as well.
Any idea? Using phpMyAdmin does not have such issue.

[an-tao]

The connection interface of Db in drogon is in non-blocking mode, this means that the connections are not established when the method returns. Maybe I should add some synchronization mechanism to this interface.
For model creation, when drogon_ctl pauses at Source files in the models folder will be overwritten, continue(y/n)?, it is connecting to Database server in the background, so you could wait for some seconds more to ensure the establishing of connections.

[ihmc3jn09hk]

For model creation, when drogon_ctl pauses at Source files in the models folder will be overwritten, continue(y/n)?, it is connecting to Database server in the background, so you could wait for some seconds more to ensure the establishing of connections.

This is what I am doing for kind of hack. Since if using the flag -f will break the logic immediately.
Btw, I am having compilation error with the #369 . For the generated "many-to-many" interface functions, I am checking.

error: template argument 1 is invalid | ... const ExceptionCallback &ecb) const

[ihmc3jn09hk]

@an-tao Found the problem, missing closing ">" for each generated function in the .cc files for "many-to-many" interfaces. Refer to #370

[an-tao]

@ihmc3jn09hk Yes, I'll fix this, sorry!

[ihmc3jn09hk]

@an-tao No worries. Just raised a pull request #370

[ihmc3jn09hk]

@an-tao Trying to perform a load test to drogon::app().getFastDbClient() with Mapper to do a lookup and update a record with MariaDB which easily crash the program. And I just got an interesting stdout message posted below. My case is similar to the
one given. Instead of a for loop, I have 1000 concurrent api-requests to a controller in a local network. Shockingly the console shows something broken in the program as following. The ▒{▒▒U0▒x▒▒ is legit copied from the stdout.
Any suggestion to solve the problem? All of the crashes is Assertion failed: rcb.

20200308 09:25:58.117065 UTC 20215 TRACE [execSqlInLoop] ▒{▒▒U0▒x▒▒U name = ?,fruit = ?  where id = ? - MysqlConnection.cc:322
Assertion failed: rcb (/home/drogon/orm_lib/src/mysql_impl/MysqlConnection.cc: execSqlInLoop: 326)
Aborted

One important difference, the concurrent requests are on a single record.

[ihmc3jn09hk]

Just tried some other thing with the for loop approach which tries to increment a valid in a record. Assuming the record to be updated is initialized as {name="foo", amount=0}. Sure the program will crash at the point "Assert rcb" as above comment. Luckily, there is still successful run but I check the record, the field is amount=1 which I thought would be 1000 as the number of loops. So the ORM callback is copying the record in DB? Concurrent/Loop calls to a repeating record will fail?

...
std::string id("foo");
Mapper<Foo> mapper(dbClientPtr);
for (auto i=0; i<1000; ++i ){
mapper.findOne(
    Criteria(Foo::Cols::_name,CompareOperator::EQ,id)),
    [req, callbackPtr, this, &client = *m_dbClient](Foo f){
        f.setAmount( r.getValueOfAmount() + 1 );   //Add 1 to the amount
        Mapper<Foo> mapper(client);
        mapper.update(f,
             [req, callbackPtr](const size_t count){
                  if ( 1 == count ){
                        LOG_INFO << "Updated";
                  }
             },
             [req, callbackPtr](const DrogonDbException &e){
                  LOG_ERROR << e.base().what();
             }
        )
    },
    [req, callbackPtr](const DrogonDbException &e){
        LOG_ERROR << e.base().what();
    }
);
}
...

[an-tao]

@ihmc3jn09hk
I can't reproduce the crashing on my MacOS, I'll test this on linux tomorrow. Below is my test code：

FooCtrl.h

#pragma once
#include <drogon/HttpSimpleController.h>
#include <drogon/IOThreadStorage.h>
using namespace drogon;
class FooCtrl:public drogon::HttpSimpleController<FooCtrl>
{
  public:
    virtual void asyncHandleHttpRequest(const HttpRequestPtr& req, std::function<void (const HttpResponsePtr &)> &&callback) override;
    PATH_LIST_BEGIN
    //list path definitions here;
    PATH_ADD("/foo",Get);
    PATH_LIST_END
  private:
    IOThreadStorage<orm::DbClientPtr> _dbClient;
};

and FooCtrl.cc

#include "FooCtrl.h"
#include "Foo.h"
#include <drogon/drogon.h>
using namespace drogon;
using namespace drogon::orm;
using namespace drogon_model::test;
void FooCtrl::asyncHandleHttpRequest(
    const HttpRequestPtr &req,
    std::function<void(const HttpResponsePtr &)> &&callback)
{
    // write your application logic here
    std::string id("foo");
    auto callbackPtr =
        std::make_shared<std::function<void(const HttpResponsePtr &)>>(
            std::move(callback));
    if (!*_dbClient)
    {
        *_dbClient = drogon::app().getFastDbClient();
    }
    Mapper<Foo> mapper(*_dbClient);
    for (auto i = 0; i < 1000; ++i)
    {
        mapper.findOne(
            Criteria(Foo::Cols::_name, CompareOperator::EQ, id),
            [req, callbackPtr, this, &client = *_dbClient](Foo f) {
                LOG_INFO << "Find one";
                f.setAmount(f.getValueOfAmount() + 1);  // Add 1 to the amount
                Mapper<Foo> mapper(client);
                mapper.update(
                    f,
                    [req, callbackPtr](const size_t count) {
                        LOG_INFO << "update " << count << " line";
                        if (1 == count)
                        {
                            LOG_INFO << "Updated";
                        }
                    },
                    [req, callbackPtr](const DrogonDbException &e) {
                        LOG_ERROR << e.base().what();
                    });
            },
            [req, callbackPtr](const DrogonDbException &e) {
                LOG_ERROR << e.base().what();
            });
    }
}

And about the value of the amount field, I think it's right. consider that the finding requests are send first asynchrounously, they will be executed before any updating statement, so this means all updating statements are the same.

[ihmc3jn09hk]

I still don't know why the rcb became null and the statement corrupted during the query.

Agree, if from my original concurrent requests case, the amount should probably be something between 1 and 1000.

[an-tao]

@ihmc3jn09hk I've reproduce this crashing on linux, I'll fix it. Sorry for confusing you.

[an-tao]

@ihmc3jn09hk I think this issue was fixed by the PR #379 , please check it out. thanks.

[ihmc3jn09hk]

@an-tao Have to say thank you first. May I have some information on what causes the crash? Would like to learn something XD. From the update in #379 , you fixed the SQL DB connection with making the connection parameters into member variables and capture the connection in this rather than copying?

All these causing the query crash? Or it is fixing the original "connection problem"?

[an-tao]

@ihmc3jn09hk actrually I fixed two problems In #379:

1. I found that mysql_real_connect_start holds raw pointers, and the data they point to is used by successive calls to mysql_real_connect_cont, so these strings must be persistent.
2. the following modification fixed the crashing issue:
   https://github.com/an-tao/drogon/blob/eda7de050e9e14d77631556a071b9a2d86c4a0f6/orm_lib/src/mysql_impl/MysqlConnection.cc#L431-L432
   We can see in this case, in the old code the result is fetched immedietely and the idleCb_ is called before the execSql returns, consider the following code:
   https://github.com/an-tao/drogon/blob/eda7de050e9e14d77631556a071b9a2d86c4a0f6/orm_lib/src/DbClientLockFree.cc#L341-L349
   in that case, the first cmd in the queue is reused when the idleCb_ is called, but its data has been moved, so the assert(rcb) failure occurs.
   I will modify this as follows to further increase security:

        std::shared_ptr<SqlCmd> cmd = std::move(sqlCmdBuffer_.front());
        sqlCmdBuffer_.pop_front();
        conn->execSql(std::move(cmd->sql_),
                      cmd->parametersNumber_,
                      std::move(cmd->parameters_),
                      std::move(cmd->lengths_),
                      std::move(cmd->formats_),
                      std::move(cmd->callback_),
                      std::move(cmd->exceptionCallback_));

[ihmc3jn09hk]

@an-tao Great thx for clarifications. I was trying to fix the bug as well and recognized there were 2 threads in 2 different classes performing the queries namely: Mysqlconnection and Sqlbinder. The Mysqlconnection one worked in the "idle event" while the Sqlbinder did the jobs stored in the "cmd vector". I was stucked there.
Much thx. I think this issue shall be closed completely.

[an-tao]

@an-tao Great thx for clarifications. I was trying to fix the bug as well and recognized there were 2 threads in 2 different classes performing the queries namely: Mysqlconnection and Sqlbinder. The Mysqlconnection one worked in the "idle event" while the Sqlbinder did the jobs stored in the "cmd vector". I was stucked there.
Much thx. I think this issue shall be closed completely.

Thank you for helping me find such a serious bug ^_^

[ihmc3jn09hk]

Have to thank you for the work too.
Just run the test again. It worked stably. Some figures for reference
1000 concurrent HTTP requests that find and update a single record in the MariaDB table.
Loop the process 10 times. Compiled as debug mode with TRACE on.
4 cores 3.5Ghz, 4GB DDR4.

Ohh, dont left out the value of amount is 426, lolz.

[an-tao]

Have to thank you for the work too.
Just run the test again. It worked stably. Some figures for reference
1000 concurrent HTTP requests that find and update a single record in the MariaDB table.
Loop the process 10 times. Compiled as debug mode with TRACE on.

It is best to output the log to a file. Printing the log on the standard output will seriously reduce the performance.

4 cores 3.5Ghz, 4GB DDR4.

Ohh, dont left out the value of amount is 426, lolz.