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 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 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 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 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')"); GenericListener listener = new GenericListener(); 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 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")); }
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")); }
/// <summary> /// Bug #57641 Substring out of range exception in ConsumeQuotedToken /// </summary> public void QuotedTokenAt300() { GenericListener listener = new GenericListener(); 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() { GenericListener listener = new GenericListener(); 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 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')"); GenericListener listener = new GenericListener(); 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 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")); }
private void CacheServerPropertiesInternal(bool cache) { string connStr = GetPoolingConnectionString() + String.Format(";logging=true;cache server properties={0}", cache); GenericListener listener = new GenericListener(); 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() { 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 NoIndexUsed() { execSQL("INSERT INTO Test VALUES (1, 'Test1')"); execSQL("INSERT INTO Test VALUES (2, 'Test1')"); execSQL("INSERT INTO Test VALUES (3, 'Test1')"); execSQL("INSERT INTO Test VALUES (4, 'Test1')"); GenericListener listener = new GenericListener(); 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 ConnectionStringExpiry() { execSQL("CREATE TABLE test3 (id INT, name VARCHAR(20), name2 VARCHAR(20))"); execSQL("INSERT INTO test3 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')"); GenericListener listener = new GenericListener(); 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("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); } Assert.AreEqual(2, listener.Find("Resultset Opened: field(s) = 3")); }