我有一个项目使用 Wt::Dbo 作为 MySQL 的对象关系数据库管理。大约一周以来，我已经评论了数据库准备语句中的泄漏。以前这个项目使用的是SQLite。
我尝试了不同的flush()但没有成功，并且无法弄清楚导致此泄漏的确切原因，但是可以肯定的是，当准备好的语句在某个点增长时，MySQL会停止回答。
以下是我监视泄露的准备语句的方法：
$ mysql -uroot -p -e "SHOW SESSION STATUS LIKE '%prepare%';" | grep stmt_count
Enter password:
Prepared_stmt_count 260
重新启动程序时，将清除泄漏的语句。
所有的数据库操作都集中在一个名为DataBase的类中，以下是一些已知会泄漏的函数：
数据库：：初始化((
void DataBase::initialize(void)
{
m_oMutex.lock();
if(!m_bInitialized)
{
m_bInitialized = true;
try
{
m_oSessionConfigurations.setConnection(*new Wt::Dbo::backend::Sqlite3("databases/configuration.db"));
m_oSessionConfigurations.mapClass<InformationSite>("informationsite");
m_oSessionConfigurations.createTables();
}
catch(std::exception &e)
{
ajouterLog("error",std::string("DataBase::initialize() : ") + e.what());
}
try
{
#if defined(DATABASE_TYPE_SQLITE)
Wt::Dbo::backend::Sqlite3 *pBackend = new Wt::Dbo::backend::Sqlite3("databases/dataBase.db");
#elif defined(DATABASE_TYPE_MYSQL)
Wt::Dbo::backend::MySQL *pBackend;
try
{
pBackend = new Wt::Dbo::backend::MySQL(DATABASE_NAME,DATABASE_USERNAME,DATABASE_PASSWORD,"localhost");
}
catch(Wt::Dbo::Exception &e)
{
ajouterLog("error",std::string("DataBase::initialize() : ") + e.what());
// If MySQL is not available, this cause issue to program until restart.
exit(1);
}
#endif
pBackend->setProperty("show-queries","true");
m_oSession.setConnection(*pBackend);
m_oSession.setFlushMode(Wt::Dbo::FlushMode::Auto);
m_oSession.mapClass<RFNode>("rfnode");
m_oSession.mapClass<NodeMeasure>("nodemeasure");
// Override the default InnoDB from Wt, MyISAM is easier to repair in case of hardware failure with database corruption
#if defined(DATABASE_TYPE_MYSQL)
try
{
Wt::Dbo::Transaction oTransaction(m_oSession);
m_oSession.execute("SET default_storage_engine=MYISAM;");
oTransaction.commit();
}
catch(Wt::Dbo::Exception &e)
{
ajouterLog("error",std::string("DataBase::initialize() : ") + e.what());
}
#endif
m_oSession.createTables();
}
catch(Wt::Dbo::Exception &e)
{
ajouterLog("error",std::string("DataBase::initialize() : ") + e.what());
}
}
m_oMutex.unlock();
}
数据库：：addNodeMeasure((
void DataBase::addNodeMeasure(NodeMeasure *p_pItem)
{
m_oMutex.lock();
try
{
Wt::Dbo::Transaction oTransaction(m_oSession);
Wt::Dbo::ptr<NodeMeasure> oItem = m_oSession.add(p_pItem);
oItem.flush();
oTransaction.commit();
}
catch(std::exception &e)
{
ajouterLog("error",std::string("Exception DataBase::addNodeMeasure() : ") + e.what());
}
m_oMutex.unlock();
printPreparedStatementCount("DataBase::addNodeMeasure()");
}
数据库：：updateNode((
void DataBase::updateNode(RFNode *p_pItem)
{
printPreparedStatementCount("DataBase::updateNode() Before");
m_oMutex.lock();
try
{
Wt::Dbo::Transaction oTransaction(m_oSession);
Wt::Dbo::ptr<RFNode> oItem = m_oSession.find<RFNode>().where("mac = ?").bind(p_pItem->mac);
oItem.modify()->zone = p_pItem->zone;
oItem.modify()->subZone = p_pItem->subZone;
oItem.modify()->unit = p_pItem->unit;
oItem.modify()->pwm = p_pItem->pwm;
oItem.modify()->led = p_pItem->led;
oItem.modify()->network = p_pItem->network;
oItem.modify()->lastContact = p_pItem->lastContact;
oItem.modify()->ioConfiguration = p_pItem->ioConfiguration;
oItem.modify()->networkAddress = p_pItem->networkAddress;
oItem.modify()->type = p_pItem->type;
oItem.modify()->functionality = p_pItem->functionality;
oItem.modify()->transmitPowerLevel = p_pItem->transmitPowerLevel;
oItem.modify()->lastNetworkRoute = p_pItem->lastNetworkRoute;
oItem.modify()->lastNetworkJumpsCount = p_pItem->lastNetworkJumpsCount;
oItem.modify()->lastRequestDuration = p_pItem->lastRequestDuration;
oItem.modify()->hardwareVersion = p_pItem->hardwareVersion;
oItem.modify()->softwareVersion = p_pItem->softwareVersion;
oItem.flush();
oTransaction.commit();
}
catch(std::exception &e)
{
ajouterLog("error",std::string("Exception DataBase::updateNode() : ") + e.what());
}
m_oMutex.unlock();
printPreparedStatementCount("DataBase::updateNode() After");
}
数据库：：getNodeMeasures((
std::vector<NodeMeasure> DataBase::getNodeMeasures(std::string p_sMAC, int p_nType, Wt::WDateTime p_oStartDate, Wt::WDateTime p_oEndDate, std::string p_sOrder, int p_nLimit)
{
std::vector<NodeMeasure> lNodeMeasures;
m_oMutex.lock();
try
{
Wt::Dbo::Transaction oTransaction(m_oSession);
std::string sWhereClause = "", sOrderClause = "";
if(!p_sMAC.empty())
{
if(!sWhereClause.empty())
{
sWhereClause += " AND ";
}
sWhereClause += "mac = '" + p_sMAC + "'";
}
if(p_nType != -1)
{
if(!sWhereClause.empty())
{
sWhereClause += " AND ";
}
sWhereClause += "type = " + std::to_string(p_nType);
}
if(p_oStartDate.isValid())
{
if(!sWhereClause.empty())
{
sWhereClause += " AND ";
}
// When not using type, we usually want nodes measures (not external temperature), so we want to find them using batchDate instead of date
sWhereClause += (p_nType != -1 ? "date" : "batchDate");
sWhereClause += " >= '";
sWhereClause += p_oStartDate.toString("yyyy-MM-ddTHH:mm:ss").toUTF8();
sWhereClause += "'";
}
if(p_oEndDate.isValid())
{
if(!sWhereClause.empty())
{
sWhereClause += " AND ";
}
// When not using type, we usually want nodes measures (not external temperature), so we want to find them using batchDate instead of date
sWhereClause += (p_nType != -1 ? "date" : "batchDate");
sWhereClause += " <= '";
// Add one second because SQLite have microseconds, and we must include results no matter microseconds field
sWhereClause += p_oEndDate.addSecs(1).toString("yyyy-MM-ddTHH:mm:ss").toUTF8();
sWhereClause += "'";
}
if(!p_sOrder.empty())
{
sOrderClause = " ORDER BY " + p_sOrder;
}
std::string sQuery = "";
if(!sWhereClause.empty())
{
sQuery += " WHERE ";
sQuery += sWhereClause;
}
if(!sOrderClause.empty())
{
sQuery += sOrderClause;
}
//std::cout << "**************************************************************************" << std::endl;
//std::cout << sQuery << std::endl;
//Wt::WDateTime oStart = Wt::WDateTime::currentDateTime();
if(Configuration::getParameter(Configuration::PARAMETER_DEBUG).getBooleanValue())
{
ajouterLog("debug","DataBase::getNodeMeasures() " + sQuery);
}
// TEST : find vs query
Wt::Dbo::collection<Wt::Dbo::ptr<NodeMeasure>> lMeasures = m_oSession.find<NodeMeasure>(sQuery).limit(p_nLimit).resultList();
// TODO : Get it cleaner... can't use Wt::Dbo::ptr outside transaction.
for(Wt::Dbo::collection<Wt::Dbo::ptr<NodeMeasure>>::const_iterator pMeasure = lMeasures.begin();pMeasure != lMeasures.end();pMeasure++)
{
lNodeMeasures.push_back(
NodeMeasure(
(*pMeasure)->mac,
(*pMeasure)->type,
(*pMeasure)->date,
(*pMeasure)->batchDate,
(*pMeasure)->value
)
);
(*pMeasure).flush();
}
//lNodeMeasures = m_oSession.find<NodeMeasure>(sQuery).limit(p_nLimit).resultList();
//std::cout << "Result : " << lNodeMeasures.size() << " in " << oStart.secsTo(Wt::WDateTime::currentDateTime()) << "s" << std::endl;
//std::cout << "**************************************************************************" << std::endl;
oTransaction.commit();
}
catch(std::exception &e)
{
ajouterLog("error",std::string("Exception DataBase::getNodeMeasures() : ") + e.what());
}
m_oMutex.unlock();
printPreparedStatementCount("DataBase::getNodeMeasures()");
return lNodeMeasures;
}
语句的大小是否失控？Wt：:D bo 缓存 MySQL SqlConnection 对象中的所有语句，并在销毁时清除它们。由于应用程序通常使用大小为 10 的连接池，因此预准备语句的数量通常是所用语句数量的倍数。
由于您似乎将语句组合为 ascii 文本而不是使用参数绑定，因此您最终可能会在缓存中得到很多语句。
Dbo 支持这种类型的代码样式：
auto measuresQuery = m_oSession.find<NodeMeasure>(sQuery).limit(p_nLimit);
if(!p_sMAC.empty())
{
measureQuery.where("mac = ?").bind(p_sMAC);
}
if(p_nType != -1)
{
measureQuery.where("type = ?").bind(std::to_string(p_nType));
}
if(p_oStartDate.isValid())
{
// When not using type, we usually want nodes measures (not external temperature), so we want to find them using batchDate instead of date
measureQuery.where(p_nType != -1 ? "date >= ?" : "batchDate >= ?").bind(p_oStartData);
}
if(!p_sOrder.empty())
{
measureQuery.orderBy(p_sOrder);
}
Wt::Dbo::collection<Wt::Dbo::ptr<NodeMeasure>> lMeasures = measureQuery.resultList();
这将产生更少数量的预准备语句，在我看来，代码更短、更清晰、更安全。