uint32_t get_open_act_reward(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
if(check_val_len(msg_type, request_len, sizeof(db_msg_get_open_reward_t)))
{
return ERR_MSG_LEN;
}
db_msg_get_open_reward_t *p_body = (db_msg_get_open_reward_t*)p_request_body;
sprintf(g_sql_str, "update db_monster_%d.t_activity_%d set flag = 1 where user_id = %u and activity_id = %u and reward_id = %u;", DB_ID(user_id), TABLE_ID(user_id), user_id, p_body->act_id, p_body->reward_id);
KINFO_LOG(user_id, "get open reward:%s", g_sql_str);
MYSQL *p_conn = p_mysql_conn->get_conn();
if (NULL == p_conn)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if (mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "[msg:%u]sql exec failed(%s).", msg_type, p_mysql_conn->get_last_errstr());
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
sprintf(g_sql_str, "INSERT INTO db_monster_%d.t_stuff_%d(user_id, stuff_id, stuff_num) values(%u, %u, %u) ON DUPLICATE KEY UPDATE stuff_num = stuff_num + %u;", DB_ID(user_id), TABLE_ID(user_id), user_id, p_body->reward_id, p_body->reward_num, p_body->reward_num);
KINFO_LOG(user_id, "open act_reward:%s", g_sql_str);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "[msg:%u]sql exec failed(%s).", msg_type, p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
// 结束事务
if (mysql_commit(p_conn) != 0)
{
KCRIT_LOG(user_id, "mysql_commit() failed.");
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
g_pack.pack(p_request_body, request_len);
return 0;
}
uint32_t maintain_plant(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
if (check_val_len(msg_type, request_len, sizeof(db_msg_maintain_req_t)) != 0)
{
return ERR_MSG_LEN;
}
db_msg_maintain_req_t *p_req = (db_msg_maintain_req_t*)p_request_body;
MYSQL *p_conn = p_mysql_conn->get_conn();
if (NULL == p_conn)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if (mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
//sprintf(g_sql_str,"UPDATE db_monster_%d.t_plant_%d SET growth = growth + %u, last_growth_value = growth + %u, last_growth_time = %u, maintain = %u, maintain_count = %u, last_reward_id = %u where user_id = %u and hole_id = %u;", DB_ID(user_id), TABLE_ID(user_id), p_req->add_growth, p_req->add_growth, p_req->add_growth_time, p_req->new_maintain_type, p_req->maintain_count, p_req->last_reward_id, user_id, p_req->hole_id);
sprintf(g_sql_str,"UPDATE db_monster_%d.t_plant_%d SET growth = growth + %u, last_growth_value = growth, last_growth_time = %u, maintain = %u, maintain_count = %u, last_reward_id = %u where user_id = %u and hole_id = %u;", DB_ID(user_id), TABLE_ID(user_id), p_req->add_growth, p_req->add_growth_time, p_req->new_maintain_type, p_req->maintain_count, p_req->last_reward_id, user_id, p_req->hole_id);
KINFO_LOG(user_id, "%s", g_sql_str);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
sprintf(g_sql_str, "INSERT INTO db_monster_%d.t_plant_reward_%d(user_id, hole_id, reward_id, reward_happy, reward_exp, reward_coins) VALUES(%u, %u, %u, %u, %u, %u) ON DUPLICATE KEY UPDATE reward_happy = %u, reward_exp = %u, reward_coins = %u;", DB_ID(user_id), TABLE_ID(user_id), user_id, p_req->hole_id, p_req->last_reward_id, p_req->reward_happy, p_req->reward_exp, p_req->reward_coins, p_req->reward_happy, p_req->reward_exp, p_req->reward_coins);
KINFO_LOG(user_id, "%s", g_sql_str);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
// 结束事务
if (mysql_commit(p_conn) != 0)
{
KCRIT_LOG(user_id, "mysql_commit() failed.");
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
return 0;
}
uint32_t add_plant(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
if (check_val_len(msg_type, request_len, sizeof(db_msg_add_plant_req_t)) != 0)
{
return ERR_MSG_LEN;
}
db_msg_add_plant_req_t *p_req = (db_msg_add_plant_req_t *)p_request_body;
KINFO_LOG(user_id, "[add plant]plant id:%u, hole id:%u, color:%u", p_req->plant_id, p_req->hole_id, p_req->color);
snprintf(g_sql_str, sizeof(g_sql_str), "UPDATE db_monster_%d.t_plant_%d set plant_id = %u, color = %u, last_extra_growth_time = %u, last_growth_time= %u, maintain = %u, next_maintain_time = %u where user_id = %u and hole_id = %u;", DB_ID(user_id), TABLE_ID(user_id), p_req->plant_id, p_req->color, p_req->last_extra_growth_time, p_req->last_grown_time, p_req->maintain, p_req->maintain_time, user_id, p_req->hole_id);
MYSQL *p_conn = p_mysql_conn->get_conn();
if (NULL == p_conn)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if (mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
//将种子数量减1
snprintf(g_sql_str, sizeof(g_sql_str), "UPDATE db_monster_%d.t_stuff_%d SET stuff_num = stuff_num - 1 WHERE user_id = %u AND stuff_id = %u and stuff_num > 0", DB_ID(user_id), TABLE_ID(user_id), user_id, p_req->plant_id);
KINFO_LOG(user_id, "desc seed:%s", g_sql_str);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
// 结束事务
if (mysql_commit(p_conn) != 0)
{
KCRIT_LOG(user_id, "mysql_commit() failed.");
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
return 0;
}
/*
** Set "auto commit" property of the connection. Modes ON/OFF
*/
static int conn_setautocommit (lua_State *L) {
conn_data *conn = getconnection (L);
if (lua_toboolean (L, 2)) {
mysql_autocommit(conn->my_conn, 1); /* Set it ON */
}
else {
mysql_autocommit(conn->my_conn, 0);
}
lua_pushboolean(L, 1);
return 1;
}
uint32_t activity_update_dragon_boat_info(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
if(check_val_len(msg_type, request_len, sizeof(uint8_t)) != 0)
{
return ERR_MSG_LEN;
}
uint8_t level_id = *(uint8_t *)p_request_body;
uint8_t status = 0;
MYSQL_ROW row = NULL;
MYSQL *p_conn = NULL;
sprintf(g_sql_str, "SELECT flag FROM db_monster_%d.t_activity_%d WHERE user_id = %u AND activity_id = %u AND reward_id = %u limit 1;", DB_ID(user_id), TABLE_ID(user_id), user_id, DRAGON_BOAT_ACTIVITY_ID, level_id);
int result_count = p_mysql_conn->select_first_row(&row, g_sql_str);
if(result_count < 0)
{
KCRIT_LOG(user_id, "sql exev failed(%s)", g_sql_str);
return ERR_SQL_ERR;
}
if(row)
{
status = 1;
}
else
{
p_conn = p_mysql_conn->get_conn();
if (NULL == p_conn)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if (mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
sprintf(g_sql_str, "INSERT INTO db_monster_%d.t_activity_%d(user_id, activity_id, reward_id, flag) VALUES(%u, %u, %u, 2);", DB_ID(user_id), TABLE_ID(user_id), user_id, DRAGON_BOAT_ACTIVITY_ID, level_id);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "[msg:%u]sql exec failed(%s).", msg_type, p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
}
db_return_update_dragon_boat_t res = {level_id, status};
g_pack.pack((char *)&res, sizeof(res));
return 0;
}
uint32_t prize_lottery(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
if (check_val_len(msg_type, request_len, sizeof(db_request_prize_lottery_t)) != 0)
{
return ERR_MSG_LEN;
}
db_request_prize_lottery_t *p_req = (db_request_prize_lottery_t *)p_request_body;
MYSQL_ROW row = NULL;
MYSQL *p_conn = p_mysql_conn->get_conn();
if(p_conn == NULL)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if(mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
sprintf(g_sql_str, "UPDATE db_monster_%d.t_stuff_%d SET stuff_num = stuff_num-%u WHERE user_id= %u AND stuff_id=%u;", DB_ID(user_id), TABLE_ID(user_id), p_req->cost, user_id, p_req->stuff_a_id);
if(p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn,true);
return ERR_SQL_ERR;
}
sprintf(g_sql_str, "INSERT INTO db_monster_%d.t_stuff_%d(user_id, stuff_id, stuff_num) VALUES(%u, %u, 1) ON DUPLICATE KEY UPDATE stuff_num = stuff_num+1;", DB_ID(user_id), TABLE_ID(user_id), user_id, p_req->stuff_b_id);
if(p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "sql exec failed(%s).", p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn,true);
return ERR_SQL_ERR;
}
if (mysql_commit(p_conn) != 0)
{
KCRIT_LOG(user_id, "mysql_commit() failed.");
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
g_pack.pack(p_request_body, request_len);
return 0;
}
static int test_wl4284_1(MYSQL *mysql)
{
int rc;
MYSQL_ROW row;
MYSQL_RES *result;
if (mysql_get_server_version(mysql) < 60000) {
diag("Test requires MySQL Server version 6.0 or above");
return SKIP;
}
/* set AUTOCOMMIT to OFF */
rc= mysql_autocommit(mysql, FALSE);
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "DROP TABLE IF EXISTS trans");
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "CREATE TABLE trans (a INT) ENGINE= InnoDB");
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "INSERT INTO trans VALUES(1)");
check_mysql_rc(rc, mysql);
rc= mysql_refresh(mysql, REFRESH_GRANT | REFRESH_TABLES);
check_mysql_rc(rc, mysql);
rc= mysql_rollback(mysql);
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "SELECT * FROM trans");
check_mysql_rc(rc, mysql);
result= mysql_use_result(mysql);
FAIL_IF(!result, "Invalid result set");
row= mysql_fetch_row(result);
FAIL_IF(!row, "Can't fetch row");
mysql_free_result(result);
/* set AUTOCOMMIT to OFF */
rc= mysql_autocommit(mysql, FALSE);
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "DROP TABLE trans");
check_mysql_rc(rc, mysql);
return OK;
}
/* {{{ mysql_handle_autocommit */
static inline int mysql_handle_autocommit(pdo_dbh_t *dbh)
{
PDO_DBG_ENTER("mysql_handle_autocommit");
PDO_DBG_INF_FMT("dbh=%p", dbh);
PDO_DBG_INF_FMT("dbh->autocommit=%d", dbh->auto_commit);
PDO_DBG_RETURN(0 <= mysql_autocommit(((pdo_mysql_db_handle *)dbh->driver_data)->server, dbh->auto_commit));
}
void DBConnection::RollBackTransaction() {
if (!IsConnected()) {
return;
}
mysql_rollback(m_pMysqlConn);
mysql_autocommit(m_pMysqlConn, 1);
}
void DBConnection::CommitTransaction() {
if (!IsConnected()) {
return;
}
mysql_commit(m_pMysqlConn);
mysql_autocommit(m_pMysqlConn, 1);
}
static int wsql_connection_set_autocommit(wsql_connection *self, PyObject *value, void *closure)
{
int error = 0;
int flag = PyLong_AsLong(value);
if (PyErr_Occurred())
return -1;
CHECK_CONNECTION(self, -1);
Py_BEGIN_ALLOW_THREADS
#if MYSQL_VERSION_ID >= 40100
error = mysql_autocommit(&(self->connection), flag);
#else
{
char query[256];
snprintf(query, 256, "SET AUTOCOMMIT=%d", flag);
error = mysql_query(&(self->connection), query);
}
#endif
Py_END_ALLOW_THREADS
if (error)
{
wsql_raise_error(self);
return -1;
}
self->autocommit = flag ? 1 : 0;
return 0;
}
// helper
int make_mysql_connection(const simple_list_node *node, void *data)
{
sql_connections *connections = (sql_connections *)data;
const char *mysql_server = (const char *)node->data;
MYSQL *con = mysql_init(NULL);
if (con == NULL)
{
write_log(6, mysql_server);
return 1;
}
if (mysql_real_connect(con, mysql_server, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT, NULL, 0) == NULL)
{
write_log(6, mysql_server);
mysql_close(con);
return 2;
}
if (mysql_autocommit(con, 0) != 0)
{
write_log(6, mysql_server);
mysql_close(con);
return 3;
}
simple_list_push(connections->connection_list, con);
return 0;
}
MYSQL *OpenDB(char *pHost, char *pUser, char *pPasswd, char *pDB, char *pUnixSocket, unsigned long lClientFlag, unsigned int nPort)
{
MYSQL *pMySql = mysql_init(NULL);
if (!pMySql)
{
SystemErrorInfor("OpenDB", errno);
return NULL;
}
if (!mysql_real_connect(pMySql, pHost, pUser, pPasswd, pDB, nPort, pUnixSocket, lClientFlag))
{
ErrorInfor("OpenDB-1", (char *)mysql_error(pMySql));
CloseDB(pMySql);
return NULL;
}
if (mysql_autocommit(pMySql, 0) != 0)
{
ErrorInfor("OpenDB-2", (char *)mysql_error(pMySql));
CloseDB(pMySql);
return NULL;
}
return pMySql;
}
bool MySQLConnection::Connect(void)
{
MYSQL *mysql_con;
mysql_con = mysql_init(NULL);
if (!mysql_con)
{
std::cout << "Could not initialize Mysql connection to database " << this->connection_info_.database << std::endl;
return false;
}
this->mysql_ = mysql_real_connect(mysql_con, this->connection_info_.host.c_str(),
this->connection_info_.user.c_str(), this->connection_info_.password.c_str(),
this->connection_info_.database.c_str(), this->connection_info_.port, NULL, CLIENT_MULTI_RESULTS);
if (this->mysql_)
{
my_bool my_true = true;
mysql_autocommit(this->mysql_, my_true);
return true;
}
else
{
std::cout << "sql: Connection failed. Reason was " << mysql_error(this->mysql_) << std::endl;
mysql_close(mysql_con);
return false;
}
}
void MySqlDataProvider::beginTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to begin a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (inTransaction())
{
const std::string error = "Trying to begin a transaction while another "
"one is still open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (mysql_autocommit(mDb, AUTOCOMMIT_OFF))
{
const std::string error = "Error while trying to disable autocommit";
LOG_ERROR(error);
throw std::runtime_error(error);
}
mInTransaction = true;
execSql("BEGIN");
LOG_DEBUG("SQL: started transaction");
}
void MySqlDataProvider::rollbackTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to rollback a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (!inTransaction())
{
const std::string error = "Trying to rollback a transaction while no "
"one is open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (mysql_rollback(mDb) != 0)
{
LOG_ERROR("MySqlDataProvider::rollbackTransaction: "
<< mysql_error(mDb));
throw DbSqlQueryExecFailure(mysql_error(mDb));
}
mysql_autocommit(mDb, AUTOCOMMIT_ON);
mInTransaction = false;
LOG_DEBUG("SQL: transaction rolled back");
}
void MySQLConnection::connect()
{
bool reconnecting = false;
if (_myConn != nullptr) //reconnection attempt
{
if (!mysql_ping(_myConn)) //ping ok
return;
else
reconnecting = true;
}
//remove any state from previous session
this->clear();
Poco::Logger& logger = _dbEngine->getLogger();
for(;;)
{
const char* unix_socket = nullptr;
if (_unix_socket.length() > 0)
unix_socket = _unix_socket.c_str();
_myConn = mysql_real_connect(_myHandle, _host.c_str(), _user.c_str(), _password.c_str(), _database.c_str(), _port, unix_socket, CLIENT_REMEMBER_OPTIONS);
if (!_myConn)
{
const char* actionToDo = "connect";
if (reconnecting)
actionToDo = "reconnect";
unsigned int errNo = mysql_errno(_myHandle);
if (IsConnectionErrFatal(errNo))
throw SqlException(errNo,mysql_error(_myHandle),actionToDo);
static const long sleepTime = 1000;
logger.warning(Poco::format("Could not %s to MySQL database at %s: %s, retrying in %d seconds",
string(actionToDo),_host,string(mysql_error(_myHandle)),static_cast<int>(sleepTime/1000)));
Poco::Thread::sleep(sleepTime);
continue;
}
break;
}
string actionDone = (reconnecting)?string("Reconnected"):string("Connected");
poco_information(logger,Poco::format( actionDone + " to MySQL database %s:%d/%s client ver: %s server ver: %s",
_host, _port,_database,string(mysql_get_client_info()),string(mysql_get_server_info(_myConn)) ));
//Autocommit should be ON because without it, MySQL would require everything to be wrapped into a transaction
if (!mysql_autocommit(_myConn, 1))
poco_trace(logger,"Set autocommit to true");
else
poco_error(logger,"Failed to set autocommit to true");
//set connection properties to UTF8 to properly handle locales for different server configs
//core sends data in UTF8, so MySQL must expect UTF8 too
if (!mysql_set_character_set(_myConn,"utf8"))
poco_trace(logger,Poco::format("Character set changed to %s",string(mysql_character_set_name(_myConn))));
else
poco_error(logger,Poco::format("Failed to change charset, remains at %s",string(mysql_character_set_name(_myConn))));
}
static int test_select_direct(MYSQL *mysql)
{
int rc;
MYSQL_RES *result;
rc= mysql_autocommit(mysql, TRUE);
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_select");
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "CREATE TABLE test_select(id int, id1 tinyint, "
" id2 float, "
" id3 double, "
" name varchar(50))");
check_mysql_rc(rc, mysql);
/* insert a row and commit the transaction */
rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 5, 2.3, 4.5, 'venu')");
check_mysql_rc(rc, mysql);
rc= mysql_commit(mysql);
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "SELECT * FROM test_select");
check_mysql_rc(rc, mysql);
/* get the result */
result= mysql_store_result(mysql);
FAIL_IF(!result, "Invalid result set");
mysql_free_result(result);
return OK;
}
uint32_t update_activity_status(i_mysql_iface *p_mysql_conn, c_memcached *p_memcached, uint32_t user_id, uint32_t msg_type, char *p_request_body, uint32_t request_len)
{
single_activity_t *p_sa = (single_activity_t*)p_request_body;
if(check_val_len(msg_type, request_len, sizeof(single_activity_t) + p_sa->kind_num * sizeof(single_reward_t)) != 0)
{
return ERR_MSG_LEN;
}
MYSQL *p_conn = p_mysql_conn->get_conn();
if (NULL == p_conn)
{
KCRIT_LOG(user_id, "get mysql conn failed.");
return ERR_SQL_ERR;
}
if (mysql_autocommit(p_conn, false) != 0)
{
KCRIT_LOG(user_id, "close mysql_autocommit failed.");
return ERR_SQL_ERR;
}
for(uint16_t idx = 0; idx < p_sa->kind_num; idx++)
{
sprintf(g_sql_str, "INSERT INTO db_monster_%d.t_activity_%d(user_id, activity_id, reward_id, reward_num, flag) values(%u, %u, %u, %u, %u) ON DUPLICATE KEY UPDATE flag = %u;", DB_ID(user_id), TABLE_ID(user_id), user_id, p_sa->activity_id, p_sa->reward[idx].reward_id, p_sa->reward[idx].reward_num, p_sa->reward[idx].reward_status, p_sa->reward[idx].reward_status);
KINFO_LOG(user_id, "update open act_reward:%s", g_sql_str);
if (p_mysql_conn->execsql(g_sql_str) < 0)
{
KCRIT_LOG(user_id, "[msg:%u]sql exec failed(%s).", msg_type, p_mysql_conn->get_last_errstr());
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
}
// 结束事务
if (mysql_commit(p_conn) != 0)
{
KCRIT_LOG(user_id, "mysql_commit() failed.");
mysql_rollback(p_conn);
mysql_autocommit(p_conn, true);
return ERR_SQL_ERR;
}
mysql_autocommit(p_conn, true);
g_pack.pack(p_request_body, request_len);
return 0;
}
bool
DbUtil::databaseLogin(const char* system, const char* usr,
const char* password, unsigned int portIn,
const char* sockIn, bool transactional)
{
if (!(m_mysql = mysql_init(NULL)))
{
myerror("DB Login-> mysql_init() failed");
return false;
}
setUser(usr);
setHost(system);
setPassword(password);
setPort(portIn);
setSocket(sockIn);
m_dbname.assign("test");
if (!(mysql_real_connect(m_mysql,
m_host.c_str(),
m_user.c_str(),
m_pass.c_str(),
m_dbname.c_str(),
m_port,
m_socket.c_str(), 0)))
{
myerror("connection failed");
disconnect();
return false;
}
m_mysql->reconnect = TRUE;
/* set AUTOCOMMIT */
if(!transactional)
mysql_autocommit(m_mysql, TRUE);
else
mysql_autocommit(m_mysql, FALSE);
#ifdef DEBUG
printf("\n\tConnected to MySQL server version: %s (%lu)\n\n",
mysql_get_server_info(m_mysql),
(unsigned long) mysql_get_server_version(m_mysql));
#endif
selectDb();
m_connected= true;
return true;
}
bool MySQLConnection::OpenConnection(bool reconnect)
{
MYSQL * mysqlInit = mysql_init(NULL);
if (!mysqlInit)
{
sLog.outError( "Could not initialize Mysql connection" );
return false;
}
mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8");
if (m_use_socket) // socket use option (Unix/Linux)
{
unsigned int opt = MYSQL_PROTOCOL_SOCKET;
mysql_options(mysqlInit, MYSQL_OPT_PROTOCOL, (char const*)&opt);
}
mMysql = mysql_real_connect(mysqlInit, m_host.c_str(), m_user.c_str(),
m_password.c_str(), m_database.c_str(), m_port, NULL, 0);
if (mMysql)
{
DETAIL_LOG( "Connected to MySQL database at %s",
m_host.c_str());
DETAIL_LOG( "MySQL client library: %s", mysql_get_client_info());
DETAIL_LOG( "MySQL server ver: %s ", mysql_get_server_info(mMysql));
/*----------SET AUTOCOMMIT ON---------*/
// It seems mysql 5.0.x have enabled this feature
// by default. In crash case you can lose data!!!
// So better to turn this off
// ---
// This is wrong since mangos use transactions,
// autocommit is turned of during it.
// Setting it to on makes atomic updates work
// ---
// LEAVE 'AUTOCOMMIT' MODE ALWAYS ENABLED!!!
// W/O IT EVEN 'SELECT' QUERIES WOULD REQUIRE TO BE WRAPPED INTO 'START TRANSACTION'<>'COMMIT' CLAUSES!!!
if (!mysql_autocommit(mMysql, 1))
DETAIL_LOG("AUTOCOMMIT SUCCESSFULLY SET TO 1");
else
DETAIL_LOG("AUTOCOMMIT NOT SET TO 1");
/*-------------------------------------*/
// set connection properties to UTF8 to properly handle locales for different
// server configs - core sends data in UTF8, so MySQL must expect UTF8 too
Execute("SET NAMES `utf8`");
Execute("SET CHARACTER SET `utf8`");
return true;
}
else
{
sLog.outError( "Could not connect to MySQL database at %s: %s\n",
m_host.c_str(), mysql_error(mysqlInit));
mysql_close(mysqlInit);
return false;
}
}
void CMySQLConnection::enable_autocommit(bool enabled) throw (CDBException)
{
MYSQL* mysql_handler = static_cast<MYSQL*>(_mysql_handler);
my_bool auto_mode = enabled? 1: 0;
if (mysql_autocommit(mysql_handler, auto_mode) != 0)
THROW_DB_EXCEPTION(NULL, mysql_error(mysql_handler), mysql_errno(mysql_handler));
}
// methods for transactions
// ---------------------------------------------
void MysqlDatabase::start_transaction() {
if (active)
{
mysql_autocommit(conn, false);
CLog::Log(LOGDEBUG,"Mysql Start transaction");
_in_transaction = true;
}
}
void mydb::set_autocommit(bool autocommit)
{
tu_autolock locker(s_mysql_plugin_mutex);
if (m_trace) log_msg("set autocommit=%s\n", autocommit ? "true" : "false");
mysql_autocommit(m_db, autocommit ? 1 : 0);
}
void MysqlDatabase::rollback_transaction() {
if (active)
{
mysql_rollback(conn);
mysql_autocommit(conn, true);
CLog::Log(LOGDEBUG,"Mysql rollback transaction");
_in_transaction = false;
}
}
void MysqlDatabase::commit_transaction() {
if (active)
{
mysql_commit(conn);
mysql_autocommit(conn, true);
CLog::Log(LOGDEBUG,"Mysql commit transaction");
_in_transaction = false;
}
}
/* autocommit() */
static VALUE autocommit(VALUE obj, VALUE mode)
{
MYSQL* m = GetHandler(obj);
int f;
f = (mode == Qnil || mode == Qfalse || NUM2INT(mode) == 0) ? 0 : 1;
if (mysql_autocommit(m, f) != 0)
mysql_raise(m);
return obj;
}
bool CDatabaseConnectionMySql::DoRollBack( const String & name )
{
bool result = mysql_rollback( _connection ) == 0;
if ( result )
{
result = mysql_autocommit( _connection, true ) == 0;
}
return result;
}
bool Mysql::endTransaction()
{
// mysql_autocommit第二个参数，为0表示不自动提交，为1表示自动提交
if ( mysql_autocommit(this->db, 1) != 0 )
{
KY_LOG_ERROR("endTransaction set mysql autocommit=1 fail");
return false;
}
return true;
}
bool CDBSrcMySql::Rollback(void)
{
if (m_pDB == NULL) return false;
if(mysql_rollback(m_pDB) == 0)
{
mysql_autocommit(m_pDB, 1);
return true;
}
else
return false;
}
