public void ConnectionStringExpiry()
{
executeSQL("CREATE TABLE test3 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
executeSQL("INSERT INTO test3 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
#if !NETCOREAPP1_1
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = Connection.ConnectionString + ";logging=true;table cache=true;default table cache age=1";
#else
string connStr = Connection.ConnectionString + ";table cache=true;default table cache age=1";
#endif
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test3", c);
cmd.CommandType = CommandType.TableDirect;
ConsumeReader(cmd);
Thread.Sleep(1500);
// now run the query again but this time it should generate a call to the database
// since our next query is past the cache age of 1 second
ConsumeReader(cmd);
}
#if !NETCOREAPP1_1
Assert.Equal(2, listener.Find("Resultset Opened: field(s) = 3"));
#endif
}
public void BatchUpdatesAndDeletes()
{
st.execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
st.execSQL("INSERT INTO test VALUES (1, 'boo'), (2, 'boo'), (3, 'boo')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = st.GetConnectionString(true) + ";logging=true;allow batch=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateBatchSize = 100;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["name"] = "boo2";
dt.Rows[1]["name"] = "boo2";
dt.Rows[2]["name"] = "boo2";
da.Update(dt);
}
Assert.Equal(1, listener.Find("Query Opened: UPDATE"));
}
public void MultipleThreads()
{
GenericListener myListener = new GenericListener();
ManualResetEvent ev = new ManualResetEvent(false);
ArrayList threads = new ArrayList();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int i = 0; i < 20; i++)
{
ParameterizedThreadStart ts = new ParameterizedThreadStart(MultipleThreadsWorker);
Thread t = new Thread(ts);
threads.Add(t);
t.Start(ev);
}
// now let the threads go
ev.Set();
// wait for the threads to end
int x = 0;
while (x < threads.Count)
{
while ((threads[x] as Thread).IsAlive)
{
Thread.Sleep(50);
}
x++;
}
}
public void SettingAgeOnCommand()
{
execSQL("CREATE TABLE test2 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
execSQL("INSERT INTO test2 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test2", c);
cmd.CommandType = CommandType.TableDirect;
cmd.CacheAge = 20;
ConsumeReader(cmd);
Thread.Sleep(1000);
// now run the query again but this time it shouldn't generate a call to the database
// since we have overriden the connection string cache age of 1 second and set it
// to 20 seconds on our command
ConsumeReader(cmd);
}
Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void ProviderNormalizingQuery()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
StringBuilder sql = new StringBuilder("SELECT '");
for (int i = 0; i < 400; i++)
{
sql.Append("a");
}
sql.Append("'");
using (var logConn = new MySqlConnection(Connection.ConnectionString + ";logging=true"))
{
logConn.Open();
MySqlCommand cmd = new MySqlCommand(sql.ToString(), logConn);
cmd.ExecuteNonQuery();
}
Assert.AreEqual(28, listener.Strings.Count);
StringAssert.EndsWith("SELECT ?", listener.Strings[listener.Strings.Count - 5]);
}
public void FieldConversion()
{
_fixture.execSQL("INSERT INTO Test VALUES (1, 'Test1')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", _fixture.conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
short s = reader.GetInt16(0);
long l = reader.GetInt64(0);
string str = reader.GetString(1);
}
Assert.Equal(6, listener.Strings.Count);
Assert.True(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test"));
Assert.True(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.True(listener.Strings[3].Contains("Usage Advisor Warning: The field 'id' was converted to the following types: Int16,Int64"));
Assert.True(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=8"));
Assert.True(listener.Strings[5].Contains("Query Closed"));
}
public void SimpleLogging()
{
ExecuteSQL("CREATE TABLE Test(id INT, name VARCHAR(200))");
ExecuteSQL("INSERT INTO Test VALUES (1, 'Test1')");
ExecuteSQL("INSERT INTO Test VALUES (2, 'Test2')");
ExecuteSQL("INSERT INTO Test VALUES (3, 'Test3')");
ExecuteSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
using (var logConn = new MySqlConnection(Connection.ConnectionString + ";logging=true"))
{
logConn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", logConn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
//Assert.AreEqual(4, listener.Strings.Count);
Assert.AreEqual(27, listener.Strings.Count);
StringAssert.Contains("Query Opened: SELECT * FROM Test", listener.Strings[listener.Strings.Count - 5]);
StringAssert.Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1", listener.Strings[listener.Strings.Count - 4]);
StringAssert.Contains("Resultset Closed. Total rows=4, skipped rows=4, size (bytes)=32", listener.Strings[listener.Strings.Count - 3]);
StringAssert.Contains("Query Closed", listener.Strings[listener.Strings.Count - 2]);
}
public void Warnings()
{
_fixture.execSQL("DROP TABLE IF EXISTS Test");
_fixture.execSQL("CREATE TABLE Test(id INT, name VARCHAR(5))");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
using (MySqlConnection logConnection = new MySqlConnection(this.conn))
{
logConnection.Open();
MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO Test VALUES (1, 'abcdef')", logConnection);
cmd.ExecuteNonQuery();
}
Assert.Equal(32, listener.Strings.Count);
Assert.True(listener.Strings[listener.Strings.Count - 10].Contains("Query Opened: INSERT IGNORE INTO Test VALUES (1, 'abcdef')"));
Assert.True(listener.Strings[listener.Strings.Count - 9].Contains("Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0"));
Assert.True(listener.Strings[listener.Strings.Count - 8].Contains("Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0"));
Assert.True(listener.Strings[listener.Strings.Count - 7].Contains("Query Opened: SHOW WARNINGS"));
Assert.True(listener.Strings[listener.Strings.Count - 6].Contains("Resultset Opened: field(s) = 3, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[listener.Strings.Count - 5].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=55"));
Assert.True(listener.Strings[listener.Strings.Count - 4].Contains("Query Closed"));
Assert.True(listener.Strings[listener.Strings.Count - 3].Contains("MySql Warning: Level=Warning, Code=1265, Message=Data truncated for column 'name' at row 1"));
Assert.True(listener.Strings[listener.Strings.Count - 2].Contains("Query Closed"));
}
public void SimpleTableCaching()
{
_fixture.execSQL("CREATE TABLE test (id INT, name VARCHAR(20), name2 VARCHAR(20))");
_fixture.execSQL("INSERT INTO test VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = _fixture.GetConnectionString(true) + ";logging=true;table cache=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test", c);
cmd.CommandType = CommandType.TableDirect;
ConsumeReader(cmd);
// now run the query again but this time it shouldn't generate a call to the database
ConsumeReader(cmd);
}
Assert.Equal(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void Warnings()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(5))");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO Test VALUES (1, 'abcdef')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(9, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: INSERT IGNORE INTO Test VALUES (1, 'abcdef')"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0"));
Assert.IsTrue(listener.Strings[2].Contains("Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0"));
Assert.IsTrue(listener.Strings[3].Contains("Query Opened: SHOW WARNINGS"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Opened: field(s) = 3, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[5].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=55"));
Assert.IsTrue(listener.Strings[6].Contains("Query Closed"));
Assert.IsTrue(listener.Strings[7].Contains("MySql Warning: Level=Warning, Code=1265, Message=Data truncated for column 'name' at row 1"));
Assert.IsTrue(listener.Strings[8].Contains("Query Closed"));
}
public void NoIndexUsed()
{
ExecuteSQL("CREATE TABLE Test (id int, name VARCHAR(200))");
ExecuteSQL("INSERT INTO Test VALUES (1, 'Test1')");
ExecuteSQL("INSERT INTO Test VALUES (2, 'Test1')");
ExecuteSQL("INSERT INTO Test VALUES (3, 'Test1')");
ExecuteSQL("INSERT INTO Test VALUES (4, 'Test1')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT name FROM Test WHERE id=3", Connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.AreEqual(6, listener.Strings.Count);
StringAssert.Contains("Query Opened: SELECT name FROM Test WHERE id=3", listener.Strings[0]);
StringAssert.Contains("Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1", listener.Strings[1]);
StringAssert.Contains("Usage Advisor Warning: Query does not use an index", listener.Strings[2]);
StringAssert.Contains("Usage Advisor Warning: The following columns were not accessed: name", listener.Strings[3]);
StringAssert.Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=6", listener.Strings[4]);
StringAssert.Contains("Query Closed", listener.Strings[5]);
}
public void BadIndexUsed()
{
_fixture.execSQL("DROP TABLE IF EXISTS Test");
_fixture.execSQL("CREATE TABLE Test(id INT, name VARCHAR(20) PRIMARY KEY)");
_fixture.execSQL("INSERT INTO Test VALUES (1, 'Test1')");
_fixture.execSQL("INSERT INTO Test VALUES (2, 'Test2')");
_fixture.execSQL("INSERT INTO Test VALUES (3, 'Test3')");
_fixture.execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT name FROM Test WHERE id=3", _fixture.conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.Equal(6, listener.Strings.Count);
Assert.True(listener.Strings[0].Contains("Query Opened: SELECT name FROM Test WHERE id=3"));
Assert.True(listener.Strings[1].Contains("Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.True(listener.Strings[3].Contains("Usage Advisor Warning: The following columns were not accessed: name"));
Assert.True(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=6"));
Assert.True(listener.Strings[5].Contains("Query Closed"));
}
public void NotReadingEveryRow()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
reader.Read();
Assert.IsTrue(reader.NextResult());
reader.Read();
reader.Read();
Assert.IsFalse(reader.NextResult());
}
Assert.AreEqual(11, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test; SELECT * FROM Test WHERE id > 2"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: Skipped 2 rows. Consider a more focused query."));
Assert.IsTrue(listener.Strings[4].Contains("Usage Advisor Warning: The following columns were not accessed: id,name"));
Assert.IsTrue(listener.Strings[5].Contains("Resultset Closed. Total rows=4, skipped rows=2, size (bytes)=32"));
Assert.IsTrue(listener.Strings[6].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[7].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[8].Contains("Usage Advisor Warning: The following columns were not accessed: id,name"));
Assert.IsTrue(listener.Strings[9].Contains("Resultset Closed. Total rows=2, skipped rows=0, size (bytes)=16"));
Assert.IsTrue(listener.Strings[10].Contains("Query Closed"));
}
public void SimpleLogging()
{
st.execSQL("INSERT INTO Test VALUES (1, 'Test1')");
st.execSQL("INSERT INTO Test VALUES (2, 'Test2')");
st.execSQL("INSERT INTO Test VALUES (3, 'Test3')");
st.execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
using (MySqlConnection logConn = new MySqlConnection(this.conn))
{
logConn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", logConn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
//Assert.Equal(4, listener.Strings.Count);
Assert.Equal(23, listener.Strings.Count);
Assert.True(listener.Strings[listener.Strings.Count - 5].Contains("Query Opened: SELECT * FROM Test"));
Assert.True(listener.Strings[listener.Strings.Count - 4].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[listener.Strings.Count - 3].Contains("Resultset Closed. Total rows=4, skipped rows=4, size (bytes)=32"));
Assert.True(listener.Strings[listener.Strings.Count - 2].Contains("Query Closed"));
}
public void ProcedureCache()
{
// open a new connection using a procedure cache
string connStr = Connection.ConnectionString;
connStr += ";procedure cache size=25;logging=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
// install our custom trace listener
GenericListener myListener = new GenericListener();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int x = 0; x < 10; x++)
{
ExecuteSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
cmd.CommandType = CommandType.StoredProcedure;
for (int y = 0; y < 20; y++)
{
cmd.ExecuteNonQuery();
}
}
// remove our custom trace listener
System.Diagnostics.Trace.Listeners.Remove(myListener);
// now see how many times our listener recorded a cache hit
Assert.AreEqual(190, myListener.Find("from procedure cache"));
Assert.AreEqual(10, myListener.Find("from server"));
}
}
public void ProviderNormalizingQuery()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
StringBuilder sql = new StringBuilder("SELECT '");
for (int i = 0; i < 400; i++)
{
sql.Append("a");
}
sql.Append("'");
using (MySqlConnection logConnection = new MySqlConnection(this.conn))
{
logConnection.Open();
MySqlCommand cmd = new MySqlCommand(sql.ToString(), logConnection);
cmd.ExecuteNonQuery();
}
Assert.Equal(24, listener.Strings.Count);
Assert.True(listener.Strings[listener.Strings.Count - 5].EndsWith("SELECT ?", StringComparison.OrdinalIgnoreCase));
}
public void FieldConversion()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
short s = reader.GetInt16(0);
long l = reader.GetInt64(0);
string str = reader.GetString(1);
}
Assert.AreEqual(6, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: The field 'id' was converted to the following types: Int16,Int64"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=8"));
Assert.IsTrue(listener.Strings[5].Contains("Query Closed"));
}
public void BadIndexUsed()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20) PRIMARY KEY)");
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT name FROM Test WHERE id=3", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.AreEqual(6, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT name FROM Test WHERE id=3"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: The following columns were not accessed: name"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=6"));
Assert.IsTrue(listener.Strings[5].Contains("Query Closed"));
}
public void SettingAgeOnCommand()
{
_fixture.execSQL("CREATE TABLE test2 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
_fixture.execSQL("INSERT INTO test2 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = _fixture.GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test2", c);
cmd.CommandType = CommandType.TableDirect;
cmd.CacheAge = 20;
ConsumeReader(cmd);
Thread.Sleep(1000);
// now run the query again but this time it shouldn't generate a call to the database
// since we have overriden the connection string cache age of 1 second and set it
// to 20 seconds on our command
ConsumeReader(cmd);
}
Assert.Equal(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void Warnings()
{
ExecuteSQL("CREATE TABLE Test(id INT, name VARCHAR(5))");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
using (var logConn = new MySqlConnection(Connection.ConnectionString + ";logging=true"))
{
logConn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO Test VALUES (1, 'abcdef')", logConn);
cmd.ExecuteNonQuery();
}
Assert.AreEqual(32, listener.Strings.Count);
StringAssert.Contains("Query Opened: INSERT IGNORE INTO Test VALUES (1, 'abcdef')", listener.Strings[listener.Strings.Count - 10]);
StringAssert.Contains("Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0", listener.Strings[listener.Strings.Count - 9]);
StringAssert.Contains("Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0", listener.Strings[listener.Strings.Count - 8]);
StringAssert.Contains("Query Opened: SHOW WARNINGS", listener.Strings[listener.Strings.Count - 7]);
StringAssert.Contains("Resultset Opened: field(s) = 3, affected rows = -1, inserted id = -1", listener.Strings[listener.Strings.Count - 6]);
StringAssert.Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=55", listener.Strings[listener.Strings.Count - 5]);
StringAssert.Contains("Query Closed", listener.Strings[listener.Strings.Count - 4]);
StringAssert.Contains("MySql Warning: Level=Warning, Code=1265, Message=Data truncated for column 'name' at row 1", listener.Strings[listener.Strings.Count - 3]);
StringAssert.Contains("Query Closed", listener.Strings[listener.Strings.Count - 2]);
}
public void QuotedTokenAt300()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string sql = @"SELECT 1 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1`, 2 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2`,
3 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3`, 4 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4`,
5 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5`, 6 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6`;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
public void UseOldSyntaxGivesWarning()
{
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
string connStr = conn.ConnectionString + ";old syntax=yes;pooling=false";
MySqlConnection conn2 = new MySqlConnection(connStr);
conn2.Open();
Assert.IsTrue(listener.Find("Use Old Syntax is now obsolete") != 0);
conn2.Close();
Trace.Listeners.Clear();
}
public void QuotedTokenAt300()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string sql = @"SELECT 1 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1`, 2 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2`,
3 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3`, 4 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4`,
5 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5`, 6 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6`;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
public void ProviderNormalizingQuery()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
StringBuilder sql = new StringBuilder("SELECT '");
for (int i = 0; i < 400; i++)
sql.Append("a");
sql.Append("'");
MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(5, listener.Strings.Count);
Assert.IsTrue(listener.Strings[1].EndsWith("SELECT ?", StringComparison.OrdinalIgnoreCase));
}
public void NotReadingEveryRow()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.Read();
listener.Clear();
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0);
reader.Read();
reader.Read();
listener.Clear();
Assert.IsFalse(reader.NextResult());
Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public void NotReadingEveryField()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.GetInt32(0); // access the first field
reader.Read();
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(listener.Find("Fields not accessed: name") != 0);
reader.Read();
listener.Clear();
Assert.AreEqual("Test3", reader.GetString(1));
Assert.IsFalse(reader.NextResult());
Assert.IsTrue(listener.Find("Fields not accessed: id") > 0);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public void NotReadingEveryField()
{
executeSQL("CREATE TABLE Test (id int, name VARCHAR(200))");
executeSQL("INSERT INTO Test VALUES (1, 'Test1')");
executeSQL("INSERT INTO Test VALUES (2, 'Test2')");
executeSQL("INSERT INTO Test VALUES (3, 'Test3')");
executeSQL("INSERT INTO Test VALUES (4, 'Test4')");
#if !NETCORE10
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
#endif
string sql = "SELECT * FROM Test; SELECT * FROM Test WHERE id > 2";
MySqlCommand cmd = new MySqlCommand(sql, Connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
reader.GetInt32(0); // access the first field
reader.Read();
Assert.True(reader.NextResult());
reader.Read();
Assert.Equal("Test3", reader.GetString(1));
Assert.False(reader.NextResult());
}
#if !NETCORE10
Assert.Equal(12, listener.Strings.Count);
Assert.True(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test; SELECT * FROM Test WHERE id > 2"));
Assert.True(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.True(listener.Strings[3].Contains("Usage Advisor Warning: Skipped 2 rows. Consider a more focused query."));
Assert.True(listener.Strings[4].Contains("Usage Advisor Warning: The following columns were not accessed: name"));
Assert.True(listener.Strings[5].Contains("Resultset Closed. Total rows=4, skipped rows=2, size (bytes)=32"));
Assert.True(listener.Strings[6].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.True(listener.Strings[7].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.True(listener.Strings[8].Contains("Usage Advisor Warning: Skipped 1 rows. Consider a more focused query."));
Assert.True(listener.Strings[9].Contains("Usage Advisor Warning: The following columns were not accessed: id"));
Assert.True(listener.Strings[10].Contains("Resultset Closed. Total rows=2, skipped rows=1, size (bytes)=16"));
Assert.True(listener.Strings[11].Contains("Query Closed"));
#endif
}
public void QuotedTokenAt300()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string sql = @"SELECT 1 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1`, 2 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2`,
3 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3`, 4 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4`,
5 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5`, 6 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6`;";
using (var logConn = new MySqlConnection(Connection.ConnectionString + ";logging=true"))
{
logConn.Open();
MySqlCommand cmd = new MySqlCommand(sql, logConn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
}
public void NotReadingEveryField()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.GetInt32(0); // access the first field
reader.Read();
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(listener.Find("Fields not accessed: name") != 0);
reader.Read();
listener.Clear();
Assert.AreEqual("Test3", reader.GetString(1));
Assert.IsFalse(reader.NextResult());
Assert.IsTrue(listener.Find("Fields not accessed: id") > 0);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void ProviderNormalizingQuery()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
StringBuilder sql = new StringBuilder("SELECT '");
for (int i = 0; i < 400; i++)
{
sql.Append("a");
}
sql.Append("'");
MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(5, listener.Strings.Count);
Assert.IsTrue(listener.Strings[1].EndsWith("SELECT ?"));
}
public void NotReadingEveryField()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string sql = "SELECT * FROM Test; SELECT * FROM Test WHERE id > 2";
MySqlCommand cmd = new MySqlCommand(sql, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
reader.GetInt32(0); // access the first field
reader.Read();
Assert.IsTrue(reader.NextResult());
reader.Read();
Assert.AreEqual("Test3", reader.GetString(1));
Assert.IsFalse(reader.NextResult());
}
Assert.AreEqual(12, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test; SELECT * FROM Test WHERE id > 2"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: Skipped 2 rows. Consider a more focused query."));
Assert.IsTrue(listener.Strings[4].Contains("Usage Advisor Warning: The following columns were not accessed: name"));
Assert.IsTrue(listener.Strings[5].Contains("Resultset Closed. Total rows=4, skipped rows=2, size (bytes)=32"));
Assert.IsTrue(listener.Strings[6].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[7].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[8].Contains("Usage Advisor Warning: Skipped 1 rows. Consider a more focused query."));
Assert.IsTrue(listener.Strings[9].Contains("Usage Advisor Warning: The following columns were not accessed: id"));
Assert.IsTrue(listener.Strings[10].Contains("Resultset Closed. Total rows=2, skipped rows=1, size (bytes)=16"));
Assert.IsTrue(listener.Strings[11].Contains("Query Closed"));
}
public void SimpleLogging()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
Assert.AreEqual(4, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Resultset Closed. Total rows=4, skipped rows=4, size (bytes)=32"));
Assert.IsTrue(listener.Strings[3].Contains("Query Closed"));
}
public void UpdateSimple()
{
using (testEntities context = new testEntities())
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
Product pc = null;
try
{
pc = new Product();
pc.Name= "Acme";
context.AddToProducts(pc);
context.SaveChanges();
pc.Name = "Acme 2";
context.SaveChanges();
}
finally
{
#if CLR4
context.Products.DeleteObject(pc);
#endif
}
// Check sql
Regex rx = new Regex(@"Query Opened: (?<item>UPDATE .*)", RegexOptions.Compiled | RegexOptions.Singleline);
foreach( string s in listener.Strings )
{
Match m = rx.Match(s);
if (m.Success)
{
CheckSql(m.Groups["item"].Value, SQLSyntax.UpdateWithSelect);
Assert.Pass();
}
}
Assert.Fail();
}
}
public void Warnings()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(5))");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO Test VALUES (1, 'abcdef')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(9, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: INSERT IGNORE INTO Test VALUES (1, 'abcdef')"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0"));
Assert.IsTrue(listener.Strings[2].Contains("Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0"));
Assert.IsTrue(listener.Strings[3].Contains("Query Opened: SHOW WARNINGS"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Opened: field(s) = 3, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[5].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=55"));
Assert.IsTrue(listener.Strings[6].Contains("Query Closed"));
Assert.IsTrue(listener.Strings[7].Contains("MySql Warning: Level=Warning, Code=1265, Message=Data truncated for column 'name' at row 1"));
Assert.IsTrue(listener.Strings[8].Contains("Query Closed"));
}
private void CacheServerPropertiesInternal(bool cache)
{
string connStr = ConnectionSettings.ConnectionString +
String.Format(";logging=true;cache server properties={0}", cache);
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlTrace.Switch.Level = System.Diagnostics.SourceLevels.All;
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
using (MySqlConnection c2 = new MySqlConnection(connStr))
{
c2.Open();
KillConnection(c2);
}
KillConnection(c);
}
int count = listener.CountLinesContaining("SHOW VARIABLES");
Assert.Equal(cache ? 1 : 2, count);
}
public void SimpleTableCaching()
{
execSQL("CREATE TABLE test (id INT, name VARCHAR(20), name2 VARCHAR(20))");
execSQL("INSERT INTO test VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;table cache=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test", c);
cmd.CommandType = CommandType.TableDirect;
ConsumeReader(cmd);
// now run the query again but this time it shouldn't generate a call to the database
ConsumeReader(cmd);
}
Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void NotReadingEveryRow()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.Read();
listener.Clear();
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0);
reader.Read();
reader.Read();
listener.Clear();
Assert.IsFalse(reader.NextResult());
Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void ProcedureCache()
{
if (Version < new Version(5, 0)) return;
// open a new connection using a procedure cache
string connStr = GetConnectionString(true);
connStr += ";procedure cache size=25;logging=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
// install our custom trace listener
GenericListener myListener = new GenericListener();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int x = 0; x < 10; x++)
{
execSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
cmd.CommandType = CommandType.StoredProcedure;
for (int y = 0; y < 20; y++)
{
cmd.ExecuteNonQuery();
}
}
// remove our custom trace listener
System.Diagnostics.Trace.Listeners.Remove(myListener);
// now see how many times our listener recorded a cache hit
Assert.AreEqual(190, myListener.Find("from procedure cache"));
Assert.AreEqual(10, myListener.Find("from server"));
}
}
public void MultipleThreads()
{
GenericListener myListener = new GenericListener();
ManualResetEvent ev = new ManualResetEvent(false);
ArrayList threads = new ArrayList();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int i = 0; i < 20; i++)
{
ParameterizedThreadStart ts = new ParameterizedThreadStart(MultipleThreadsWorker);
Thread t = new Thread(ts);
threads.Add(t);
t.Start(ev);
}
// now let the threads go
ev.Set();
// wait for the threads to end
int x = 0;
while (x < threads.Count)
{
while ((threads[x] as Thread).IsAlive)
Thread.Sleep(50);
x++;
}
}
public void UseOldSyntaxGivesWarning()
{
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
string connStr = conn.ConnectionString + ";old syntax=yes;pooling=false";
MySqlConnection conn2 = new MySqlConnection(connStr);
conn2.Open();
Assert.IsTrue(listener.Find("Use Old Syntax is now obsolete") != 0);
conn2.Close();
Trace.Listeners.Clear();
}
public void BatchUpdatesAndDeletes()
{
execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
execSQL("INSERT INTO test VALUES (1, 'boo'), (2, 'boo'), (3, 'boo')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;allow batch=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateBatchSize = 100;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["name"] = "boo2";
dt.Rows[1]["name"] = "boo2";
dt.Rows[2]["name"] = "boo2";
da.Update(dt);
}
Assert.AreEqual(1, listener.Find("Query Opened: UPDATE"));
}
private void CacheServerPropertiesInternal(bool cache)
{
string connStr = GetPoolingConnectionString() +
String.Format(";logging=true;cache server properties={0}", cache);
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlTrace.Switch.Level = System.Diagnostics.SourceLevels.All;
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
using (MySqlConnection c2 = new MySqlConnection(connStr))
{
c2.Open();
KillConnection(c2);
}
KillConnection(c);
}
int count = listener.CountLinesContaining("SHOW VARIABLES");
Assert.AreEqual(cache ? 1 : 2, count);
}
public void ProcedureCache()
{
// open a new connection using a procedure cache
string connStr = GetConnectionString(true);
connStr += ";procedure cache size=25;logging=true";
MySqlConnection c = new MySqlConnection(connStr);
try
{
c.Open();
// install our custom trace listener
GenericListener myListener = new GenericListener();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int x = 0; x < 10; x++)
{
execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
execSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
cmd.CommandType = CommandType.StoredProcedure;
for (int y = 0; y < 20; y++)
{
cmd.ExecuteNonQuery();
}
}
// remove our custom trace listener
System.Diagnostics.Trace.Listeners.Remove(myListener);
// now see how many times our listener recorded a cache hit
Assert.AreEqual(190, myListener.Find("from procedure cache"));
Assert.AreEqual(10, myListener.Find("from server"));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
c.Close();
for (int x = 0; x < 10; x++)
execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
}
}
public void ConcurrencyCheck()
{
using (MovieDBContext db = new MovieDBContext())
{
db.Database.ExecuteSqlCommand(
@"DROP TABLE IF EXISTS `test3`.`MovieReleases`");
db.Database.ExecuteSqlCommand(
@"CREATE TABLE `MovieReleases` (
`Id` int(11) NOT NULL,
`Name` varbinary(45) NOT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
try
{
MovieRelease mr = db.MovieReleases.Create();
mr.Id = 1;
mr.Name = "Commercial";
db.MovieReleases.Add(mr);
db.SaveChanges();
mr.Name = "Director's Cut";
db.SaveChanges();
}
finally
{
db.Database.ExecuteSqlCommand(@"DROP TABLE IF EXISTS `MovieReleases`");
}
// Check sql
Regex rx = new Regex(@"Query Opened: (?<item>UPDATE .*)", RegexOptions.Compiled | RegexOptions.Singleline);
foreach (string s in listener.Strings)
{
Match m = rx.Match(s);
if (m.Success)
{
CheckSql(m.Groups["item"].Value, SQLSyntax.UpdateWithSelect);
Assert.Pass();
}
}
Assert.Fail();
}
}
