Recently in MySql Category

A quick and dirty hack (written in C#) to solve the issue of MySql query log files getting huuuge:

System.ServiceProcess.ServiceController sc = new ServiceController("mysql", "servername");
sc.Stop();
Console.WriteLine("Stopping");
sc.WaitForStatus(ServiceControllerStatus.Stopped, new TimeSpan(0, 0, 30));
Console.WriteLine("Stopped");
System.IO.File.Delete(@"\\servername\C$\Program Files\MySQL\MySQL Server 5.0\data\ServerName.log");
Console.WriteLine("Starting");
sc.Start();
sc.WaitForStatus(ServiceControllerStatus.Running, new TimeSpan(0, 0, 30));
Console.WriteLine("Started");

I have this compiled into a CruiseControl task that runs at 7am every day, no more massive log files and a lot easier than trying to decipher the MySql documentation. Plus, I've got some other odds and ends of maintenance that I've shoe-horned in. Not necessarily elegant, but it works!

[The solution was discovered via this entry in the mysql.com forums]

I your MySql script contains code like

"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"

You'll need to add "Allow User Variables=true" to your connection string. This can be achieved by manually adding it to the string, or, if using a MySqlConnectionStringBuilder, something similar to the following:

var builder = new MySqlConnectionStringBuilder
{
     Database = database,
     Server = Server,
     Password = Password,
     UserID = Username
};
builder.Add("Allow User Variables", true);

Make sure you're using at least version 5.2.6 of the MySql Connector/Net though! The forum posting states that it works with 5.2.2, but it didn't work with the copy of 5.2.2 I hav, but did with 5.2.6.

Other useful links from the forums:

Re: How do i run a *.sql script from my c# environment?

One of the useful system stored procedures in Sql Server is the sp_spaceused sproc for, funnily enough, seeing how much space a table or database is using. I recently found a MySql equivalent in the MySql Forums, 'Stored procedure for reporting database/object storage usage'.

For some reason, some paths (there doesn't appear to be a determining factor as to what they are) when passed to the MySql.exe process cause an error message similar to:

ERROR: Unknown command '\b'

Unfortunately, it doesn't seem to be limited to this specific '\b' - as that seems to be whatever is immediately after the "C:" in the path to the sql file to process, so:

mysql.exe -uUSERNAME -pPASSWORD -hHOST_IP_ADDRESS DATABASE_NAME_HERE -e "source c:\buildserver\source\Databases\DatabaseName\src\sql\schema\tables\tableName.sql

Generates the message '\b', if the c:\buildserver folder is renamed to aardvark, the message "ERROR: Unknown command '\a'" is returned.

The Solution

The solution appears to be to replace all the "\" with "/" in the path that you're passing in. Yeup, that simple. So, say you have a property called "filename" in a nant script, you could pre-process it with:

<property name="target" value="${string::replace(filename, '\', '/')}"/>

Which should give you a filename that MySql can stomach