public void BulkLoadEscapingAsync()
{
executeSQL("CREATE TABLE BulkLoadEscapingAsyncTest (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + ",col1\tstill col1,col2");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadEscapingAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadEscapingAsyncTest", Connection);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("col1still col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2].ToString().Trim());
}).Wait();
}
public void BulkLoadColumnOrderAsync()
{
executeSQL(@"CREATE TABLE BulkLoadColumnOrderAsyncTest (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250), n3 VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col3,col2,col1");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadColumnOrderAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadColumnOrderAsyncTest", Connection);
Assert.Equal(20, dt.Rows.Count);
Assert.Equal("col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2]);
Assert.Equal("col3", dt.Rows[0][3].ToString().Trim());
}).Wait();
}
public void BulkLoadSimple()
{
executeSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.Equal(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.Equal(200, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}
public void BulkLoadEscaping()
{
ExecuteSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + ",col1\tstill col1,col2");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("col1still col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2].ToString().Trim());
}
public void BulkLoadSimpleAsync()
{
ExecuteSQL("CREATE TABLE BulkLoadSimpleAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadSimpleAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.Local = true;
loader.LoadAsync().ContinueWith(loadResult =>
{
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadSimpleAsyncTest", Connection);
Assert.AreEqual(dataLoaded, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}).Wait();
}
protected void CleanupDatabase()
{
using (var root = GetConnection(true))
{
executeSQL("SET GLOBAL max_allowed_packet=" + MaxPacketSize, root);
executeSQL("SET GLOBAL SQL_MODE = STRICT_ALL_TABLES", root);
var data = Utils.FillTable("SHOW DATABASES", root);
foreach (DataRow row in data.Rows)
{
string name = row[0].ToString();
if (!name.StartsWith(BaseDBName))
{
continue;
}
executeSQL(String.Format("DROP DATABASE IF EXISTS `{0}`", name), root);
}
data = Utils.FillTable(String.Format("SELECT user,host FROM mysql.user WHERE user LIKE '{0}%'", BaseUserName), root);
foreach (DataRow row in data.Rows)
{
if (Version >= new Version("5.7"))
{
executeSQL(String.Format("DROP USER IF EXISTS '{0}'@'{1}'", row[0].ToString(), row[1].ToString()), root);
}
else
{
executeSQL(String.Format("DROP USER '{0}'@'{1}'", row[0].ToString(), row[1].ToString()), root);
}
}
executeSQL("FLUSH PRIVILEGES", root);
}
}
public void ShowCreateTable()
{
executeSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
TestDataTable dt = Utils.FillTable("SHOW CREATE TABLE Test", Connection);
Assert.Equal(1, dt.Rows.Count);
Assert.Equal(2, dt.Columns.Count);
}
public void NullAsAType()
{
TestDataTable dt = Utils.FillTable(@"SELECT 'localhost' as SERVER_NAME,
null as CATALOG_NAME, database() as SCHEMA_NAME", Connection);
Assert.True(dt.Rows[0][0].GetType() == typeof(string));
Assert.Equal(DBNull.Value, dt.Rows[0][1]);
Assert.True(dt.Rows[0][2].GetType() == typeof(string));
}
public void CommentSymbolInTableName()
{
executeSQL("CREATE TABLE Test (`PO#` int(11) NOT NULL auto_increment, " +
"`PODate` date default NULL, PRIMARY KEY (`PO#`))");
executeSQL("INSERT INTO Test ( `PO#`, `PODate` ) " +
"VALUES ( NULL, '2006-01-01' )");
string sql = "SELECT `PO#` AS PurchaseOrderNumber, " +
"`PODate` AS OrderDate FROM Test";
TestDataTable dt = Utils.FillTable(sql, Connection);
Assert.Equal(1, dt.Rows.Count);
}
public void BulkLoadConflictOptionReplaceAsync()
{
ExecuteSQL("CREATE TABLE BulkLoadConflictOptionReplaceAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col1");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.Local = true;
loader.LoadAsync().Wait();
path = Path.GetTempFileName();
sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col2");
}
sw.Flush();
sw.Dispose();
loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
loader.Local = true;
loader.LoadAsync().Wait();
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadConflictOptionReplaceAsyncTest", Connection);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col2", dt.Rows[0][1].ToString().Trim());
}
public void BulkLoadReadOnlyFileAsync()
{
executeSQL("CREATE TABLE BulkLoadReadOnlyFileAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadReadOnlyFileAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.LoadAsync().ContinueWith(loadResult =>
{
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadReadOnlyFileAsyncTest", Connection);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}).Wait();
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void BulkLoadReadOnlyFile()
{
ExecuteSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void BulkLoadColumnOrder()
{
ExecuteSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col3,col2,col1");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(20, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
protected void CleanupDatabase()
{
using (var root = GetConnection(true))
{
ExecuteSQL("SET GLOBAL max_allowed_packet=" + MaxPacketSize, root); // Need to fix for BlobTest.BlobBiggerThanMaxPacket
ExecuteSQL("SET GLOBAL SQL_MODE = STRICT_ALL_TABLES", root);
ExecuteSQL("SET GLOBAL connect_timeout=600", root);
ExecuteSQL("SET GLOBAL net_read_timeout=6000", root);
ExecuteSQL("SET GLOBAL net_write_timeout=6000", root);
ExecuteSQL("SET @@global.time_zone='SYSTEM'", root);
var data = Utils.FillTable("SHOW DATABASES", root);
foreach (DataRow row in data.Rows)
{
string name = row[0].ToString();
if (!name.StartsWith(BaseDBName))
{
continue;
}
ExecuteSQL(String.Format("DROP DATABASE IF EXISTS `{0}`", name), root);
}
data = Utils.FillTable(String.Format("SELECT user,host FROM mysql.user WHERE user LIKE '{0}%'", BaseUserName), root);
foreach (DataRow row in data.Rows)
{
if (Version >= new Version("5.7"))
{
ExecuteSQL(String.Format("DROP USER IF EXISTS '{0}'@'{1}'", row[0].ToString(), row[1].ToString()), root);
}
else
{
ExecuteSQL(String.Format("DROP USER '{0}'@'{1}'", row[0].ToString(), row[1].ToString()), root);
}
}
ExecuteSQL("FLUSH PRIVILEGES", root);
}
}
[TestCase(false, "c:/SymLink/", false)] // symbolic link
public void BulkLoadUsingSafePath(bool allowLoadLocalInfile, string allowLoadLocalInfileInPath, bool shouldPass)
{
DirectoryInfo info;
bool isSymLink = false;
if (!string.IsNullOrWhiteSpace(allowLoadLocalInfileInPath))
{
info = new DirectoryInfo(allowLoadLocalInfileInPath);
isSymLink = info.Attributes.HasFlag(FileAttributes.ReparsePoint);
}
Connection.Settings.AllowLoadLocalInfile = allowLoadLocalInfile;
Connection.Settings.AllowLoadLocalInfileInPath = allowLoadLocalInfileInPath;
ExecuteSQL(string.Format("CREATE TABLE `{0}`.Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))", Connection.Database), true);
// create the external file to be uploaded
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
// create another path to test against unsafe directory
Directory.CreateDirectory("otherPath");
// copy the external file to our safe path
Directory.CreateDirectory("tmp/data");
if (!File.Exists("tmp/data/file.tmp"))
{
File.Copy(path, "tmp/data/file.tmp");
}
string filePath = allowLoadLocalInfile ? path : Path.GetFullPath("tmp/data/file.tmp");
MySqlBulkLoader loader = new MySqlBulkLoader(Connection)
{
TableName = "Test",
FileName = filePath,
Timeout = 0,
Local = true
};
if (shouldPass)
{
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
else if (isSymLink && !Directory.Exists(allowLoadLocalInfileInPath))
{
Assert.Ignore("For the symbolic link test to run, it should be manually created before executing it.");
}
else
{
var ex = Assert.Throws <MySqlException>(() => loader.Load());
if (allowLoadLocalInfileInPath == " " || allowLoadLocalInfileInPath is null)
{
if (Version > new Version(8, 0))
{
Assert.AreEqual("Loading local data is disabled; this must be enabled on both the client and server sides", ex.Message);
}
else
{
Assert.AreEqual("The used command is not allowed with this MySQL version", ex.Message);
}
}
else
{
StringAssert.Contains("allowloadlocalinfileinpath", ex.Message);
}
}
File.Delete(path);
Directory.Delete("tmp", true);
Directory.Delete("otherPath");
}