public void MaxPreparedStmtCount()
{
InitCollection();
Collection coll = GetCollection();
try
{
((Session)coll.Session).SQL("SET GLOBAL max_prepared_stmt_count=0").Execute();
var findStmt = coll.Find("_id = :id and pages = :pages").Bind("id", 1).Bind("pages", 20);
DocResult doc = ExecuteFindStatement(findStmt);
Assert.AreEqual("Book 1", doc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
Assert.True(findStmt.Session.SupportsPreparedStatements);
ValidatePreparedStatements(0, 0, null);
doc = ExecuteFindStatement(findStmt.Bind("id", 2).Bind("pages", 30).Limit(1));
Assert.AreEqual($"Book 2", doc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
Assert.False(findStmt.Session.SupportsPreparedStatements);
doc = ExecuteFindStatement(findStmt.Bind("id", 3).Bind("pages", 40).Limit(1));
Assert.AreEqual($"Book 3", doc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
Assert.False(findStmt.Session.SupportsPreparedStatements);
}
finally
{
((Session)coll.Session).SQL("SET GLOBAL max_prepared_stmt_count=16382").Execute();
}
}
public void FindWithChanges()
{
InitCollection();
Collection coll = GetCollection();
var findStmt = coll.Find().Where("_id = 1");
DocResult foundDoc = ExecuteFindStatement(findStmt);
Assert.AreEqual("Book 1", foundDoc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
ValidatePreparedStatements(0, 0, null);
foundDoc = ExecuteFindStatement(findStmt.Limit(1));
Assert.AreEqual("Book 1", foundDoc.FetchAll()[0]["title"].ToString());
Assert.True(findStmt._isPrepared || !findStmt.Session.SupportsPreparedStatements);
ValidatePreparedStatements(1, 1,
$"SELECT doc FROM `{schemaName}`.`{_collectionName}` WHERE (JSON_EXTRACT(doc,'$._id') = 1) LIMIT ?, ?");
for (int i = 1; i <= _docs.Length; i++)
{
DocResult foundDoc2 = ExecuteFindStatement(findStmt.Where($"_id = {i}"));
Assert.AreEqual($"Book {i}", foundDoc2.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
}
ValidatePreparedStatements(0, 0, null);
}
public void DeallocatePreparedStatmentsWhenClosingSession()
{
InitCollection();
string threadId;
using (Session mySession = MySQLX.GetSession(ConnectionString))
{
mySession.SetCurrentSchema(schemaName);
threadId = mySession.SQL("SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=CONNECTION_ID()").Execute().FetchOne()[0].ToString();
Collection coll = mySession.GetSchema(schemaName).GetCollection(_collectionName);
var findStmt = coll.Find().Where($"_id = 1");
DocResult foundDoc = ExecuteFindStatement(findStmt);
Assert.AreEqual("Book 1", foundDoc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
ValidatePreparedStatements(0, 0, null, threadId);
foundDoc = ExecuteFindStatement(findStmt.Limit(1));
Assert.AreEqual("Book 1", foundDoc.FetchAll()[0]["title"].ToString());
Assert.True(findStmt._isPrepared || !findStmt.Session.SupportsPreparedStatements);
if (findStmt.Session.SupportsPreparedStatements)
{
ValidatePreparedStatements(1, 1,
$"SELECT doc FROM `{schemaName}`.`{_collectionName}` WHERE (JSON_EXTRACT(doc,'$._id') = 1) LIMIT ?, ?",
threadId);
}
mySession.Close();
ValidatePreparedStatements(0, 0, null, threadId);
}
}
public void ExclusiveLockAfterSharedLock()
{
if (!session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
Collection coll = CreateCollection("test");
coll.CreateIndex("myIndex", true).Field("$._id", "INT", true).Execute();
var docs = new[]
{
new { _id = 1, a = 1 },
new { _id = 2, a = 1 },
new { _id = 3, a = 1 },
};
coll.Add(docs).Execute();
Collection coll2 = session2.GetSchema("test").GetCollection("test");
session.SQL("START TRANSACTION").Execute();
DocResult docResult = coll.Find("_id in (1, 3)").LockShared().Execute();
Assert.Equal(2, docResult.FetchAll().Count);
session2.SQL("START TRANSACTION").Execute();
// Should return immediately since document isn't locked.
docResult = coll2.Find("_id = 2").LockExclusive().Execute();
// Should return immediately due to LockShared() allows reading by other sessions.
docResult = coll2.Find("_id = 2").LockShared().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
// Session2 blocks due to to LockExclusive() not allowing to read locked documents.
session2.SQL("SET SESSION innodb_lock_wait_timeout=1").Execute();
Exception ex = Assert.Throws <MySqlException>(() => coll2.Find("_id = 1").LockExclusive().Execute());
Assert.Equal("Lock wait timeout exceeded; try restarting transaction", ex.Message);
// Session unlocks documents.
session.SQL("ROLLBACK").Execute();
docResult = coll2.Find("_id = 1").LockExclusive().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
session2.SQL("ROLLBACK").Execute();
}
}
public void SharedLockForbidsToModifyDocuments()
{
if (!session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
Collection coll = CreateCollection("test");
var docs = new[]
{
new { _id = 1, a = 1 },
new { _id = 2, a = 1 },
new { _id = 3, a = 1 },
};
coll.Add(docs).Execute();
Collection coll2 = session2.GetSchema("test").GetCollection("test");
session.SQL("START TRANSACTION").Execute();
DocResult docResult = coll.Find("_id = 1").LockShared().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
session2.SQL("START TRANSACTION").Execute();
// Reading the same document is allowed with LockShared().
docResult = coll2.Find("_id = 1").Execute();
Assert.Equal(1, docResult.FetchAll().Count);
// Modify() is allowed for non-locked documents.
Result result = coll2.Modify("_id = 2").Set("a", 2).Execute();
Assert.Equal <ulong>(1, result.RecordsAffected);
// Session1 blocks, Modify() is not allowed for locked documents.
session2.SQL("SET SESSION innodb_lock_wait_timeout=1").Execute();
Exception ex = Assert.Throws <MySqlException>(() => coll2.Modify("_id = 1").Set("a", 2).Execute());
Assert.Equal("Lock wait timeout exceeded; try restarting transaction", ex.Message);
session.SQL("ROLLBACK").Execute();
// Modify() is allowed since document isn't locked anymore.
coll2.Modify("_id = 1").Set("a", 2).Execute();
session2.SQL("COMMIT").Execute();
}
}
public void Find()
{
InitCollection();
Collection coll = GetCollection();
var findStmt = coll.Find("_id = :id and pages = :pages").Bind("id", 1).Bind("pages", 20);
DocResult doc = ExecuteFindStatement(findStmt);
Assert.AreEqual("Book 1", doc.FetchAll()[0]["title"].ToString());
Assert.False(findStmt._isPrepared);
ValidatePreparedStatements(0, 0, null);
for (int i = 1; i <= _docs.Length; i++)
{
doc = ExecuteFindStatement(findStmt.Bind("id", i).Bind("pages", i * 10 + 10).Limit(1));
Assert.AreEqual($"Book {i}", doc.FetchAll()[0]["title"].ToString());
Assert.True(findStmt._isPrepared || !findStmt.Session.SupportsPreparedStatements);
}
ValidatePreparedStatements(1, 4,
$"SELECT doc FROM `{schemaName}`.`{_collectionName}` WHERE ((JSON_EXTRACT(doc,'$._id') = ?) AND (JSON_EXTRACT(doc,'$.pages') = ?)) LIMIT ?, ?");
}
public void SimpleSharedLock()
{
if (!session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
Collection coll = CreateCollection("test");
var docs = new[]
{
new { _id = 1, a = 1 },
new { _id = 2, a = 1 },
new { _id = 3, a = 1 },
};
coll.Add(docs).Execute();
Collection coll2 = session2.GetSchema("test").GetCollection("test");
session.SQL("START TRANSACTION").Execute();
DocResult docResult = coll.Find("_id = 1").LockShared().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
session2.SQL("START TRANSACTION").Execute();
// Should return immediately since document isn't locked.
docResult = coll2.Find("_id = 2").LockShared().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
// Should return immediately due to LockShared() allows reading by other sessions.
docResult = coll2.Find("_id = 1").LockShared().Execute();
Assert.Equal(1, docResult.FetchAll().Count);
session.SQL("ROLLBACK").Execute();
session2.SQL("ROLLBACK").Execute();
}
}
private static void PrintDocResult(DocResult doc)
{
List <Dictionary <string, object> > data = doc.FetchAll();
foreach (Dictionary <string, object> row in data)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("{");
int i = 0;
foreach (KeyValuePair <string, object> kvp in row)
{
sb.AppendFormat("\t\"{0}\" : \"{1}\"{2}\n", kvp.Key, kvp.Value, (i == row.Count - 1) ? "" : ",");
i++;
}
sb.AppendLine("},");
Console.Write(sb);
}
}