Mark Cordell's blog

Math Problem: Evenly spaced points on a sphere - Part 3

2011-11-17T00:05:00.001-08:00

Continued from Part 2

Let’s try to find a way to systematically divide the triangles on a sphere into smaller triangles in such a way that the resulting triangles are as evenly spaced as possible, starting with either a tetrahedron, an octahedron, or an icosahedron. One thing we can do is place a new point in the center of each triangle. This is called a Kleetope. It is not possible to create a distribution of points that is as evenly spaced as possible by producing successive Kleeptopes because the resulting triangles will become less and less like equilateral triangles. Some points will be very close to each other and other points will be further apart.

Another thing we can do is split each triangle into four triangles by placing new points at the midpoint of each triangle edge.

When you subdivide a triangle into four triangles on the surface of a sphere, the four triangles are not all the same size. The center one is larger. But as you keep subdividing and the triangles get smaller, the curvature of the sphere makes less and less of a difference and the size of the subdivided triangles become more uniform. So if you can show that a set of points after some number of subdivisions are as evenly spaced as possible, then further subdivisions will also be as evenly spaced as possible. Let’s find the size of the center triangle after subdividing.

Use the spherical law of cosines twice, once for the bigger triangle, and once for the smaller triangle. We’ll assume the radius of the sphere is 1.

Solve for cos(t).

Try to simplify using the pythagorean trigonometric identity and the double angle formula.

Substitute for cos(t) in the smaller triangle.

When the big triangle (2 a) is very small, x is barely bigger than a. When 2 a is 2 pi / 9 (or 40°), x is about 5% bigger than a. When 2 a is 4 pi / 9 (or 80°), x is about 24% bigger than a. Finally, when 2 a is 2 pi / 3 (or 120°), x is twice the size of a, which is the same size as the original triangle. This is the case when the three original points are all evenly spaced around the equator, and the three new points are placed on the midpoints, which are also on the equator.

My guess is that you could take a tetrahedron, an octahedron, or an icosahedron, and iteratively split each triangle into four triangles, and you’d end up with an arrangement of points at each iteration that is as evenly spaced as it could be. Additionally, you could form the Kleeptope one time for each of these iterative arrangements and the arrangement of points would be as evenly spaced as they could be.

Iteratively splitting the tetrahedron

points	edges	faces	distance between adjacent points
4	6	4	109.4712°
10	24	16	54.7356° and 90°
34	96	64	various

Forming the Kleetope for each iteration of the tetrahedron

points	edges	faces	distance between adjacent points
8	18	12	109.4712° and something
26	72	48	various
98	288	192	various

Iteratively splitting the octahedron

points	edges	faces	distance between adjacent points
6	12	8	90°
18	48	32	45° and 60°
66	192	128	various

Forming the Kleetope for each iteration of the octahedron

points	edges	faces	distance between adjacent points
14	36	24	90° and something
50	144	96	various
194	576	384	various

Iteratively splitting the icosahedron

points	edges	faces	distance between adjacent points
12	30	20	63.435°
42	120	80	31.717° and 36°
162	480	320	various

Forming the Kleetope for each iteration of the icosahedron

points	edges	faces	distance between adjacent points
32	90	60	63.435° and something
122	360	240	various
482	1440	960	various

So it looks like we can make a lot of arrangements of points that are probably as evenly spaced as possible, and we can make them larger and larger without limit.

However, it may not possible to mathematically specify an arrangement of points that is as evenly spaced as possible for any number of points n, because when n is a large prime, the optimal arrangement of points is probably unrelated to any patterns of points for any smaller n.

Math Problem: Evenly spaced points on a sphere - Part 2

2011-11-13T09:13:00.000-08:00

Continued from Part 1

In an ideal case, we would be able to position all the points around the sphere so that they formed equilateral triangles and all the adjacent points were exactly the same difference apart.

I should be more specific about what I mean by adjacent points. Draw a line connecting the two points that are closest to each other. Then draw a line connecting the next two points that are the next closest to each other and where the line wouldn’t cross any other existing lines. Continue along like this until no more lines can be drawn. The points with a line connecting them are considered to be adjacent.

In the ideal case, we can divide the surface area of the sphere by the number of triangles to find the surface area along the sphere of each triangle.

Let’s find the relationship between the number of points, the number of triangle edges, and the number of triangular faces. A tetrahedron has four points, six edges, and four faces. Whenever you add another point that is not in the same spot as an existing point, it will either fall along an existing edge or on a face. In either case, there will be three more edges and two more faces.

points	edges	faces
4	6	4
5	6+3	4+2
6	6+3+3	4+2+2
n	3 (n - 2)	2 (n - 2)

So in the ideal case, the spherical surface area A of each triangle is

Now let’s find the relationship between the distance 'd' along the surface of the sphere between two adjacent points and the spherical surface area of the triangle anchored by these two points. We can use Girard's theorem to find the relationship between the spherical vertex angle 't' and the spherical surface area A, and we can use the spherical law of cosines to find the relationship between the distance 'd' and the vertex angle 't'.

Use the spherical law of cosines.

Solve for a.

First use the Pythagorean trigonometric identity to substitute.

Group the terms.

Use the quadratic formula.

Simplify.

We can ignore the case when cos(a) = 1 because that is when a = 0 or 2 π, which is when the two points are in the same location.

Now use Girard's theorem.

Solve for t.

Substitute for A.

Now substitute this for t in the equation for cos(a) above.

In most cases we will not be able to position the points into perfect equilateral triangles of this size because the triangles will start to overlap or leave gaps with each other, but we can do it for n=4 the tetrahedron, n=6 the octahedron, and n=12, the icosahedron.

In the other cases, we can measure the amount of unevenness U by looking at the difference between the ideal distance between adjacent points 'd' and the actual distance between adjacent points dk. Define U as the sum of the squares of these differences:

For the tetrahedron, octahedron, and icosahedron, U = 0. For the other cases, we will try to position the points so that U is as small as possible.

So the next questions are whether there is a way to systematically position the points to minimize U, and given a configuration of points, how can you tell if U for this configuration is the lowest possible.

You can probably show that U is a local minimum if moving any one point by some small distance delta in any direction causes U to increase, but how do you determine whether that local minimum is the lowest minimum possible?

One way to explore this further is to build a computer simulation of marbles around the surface of a sphere that are all magnetically opposed to each other. Or better yet, let the force between any two adjacent points be proportional to the square of the difference between the actual distance between the points and the ideal distance between the points, so that the points are always being pushed towards the ideal distance.

Continue to Part 3

Math Problem: Evenly spaced points on a sphere - Part 1

2011-10-29T23:17:00.000-07:00

If place points around the circumference of a circle, it is not difficult to position them so that they're evenly spaced.

n	angle	angle	name
3	2π / 3	120°	triangle
4	π / 2	90°	square
5	2π / 5	72°	pentagon
n	2π / n	360 / n	n-gon

But suppose you want to position points around the surface of a sphere so that they're evenly spaced.

What is the angle between adjacent points? Is it possible to position the points so that the angle is the same between all adjacent points on the sphere?

Let's look at some examples. For n=4, you have a tetrahedron.

According to wikipedia, the angle between adjacent points is arccos(-1/3) or about 109.4712°.

For n=6, you have an octahedron. The angle between adjacent points is π / 2 or 90°.

To summarize and expand:

n	faces	tri. faces	edges	tri. edges	angle	angle	name
4	4	4	6	6	arccos(-1/3)	109.4712°	tetrahedron
6	8	8	12	12	π / 2	90°	octahedron
8	6	12	12	18	multiple angles		cube
12	20	20	30	30	2 arctan(2/(1+√5))	63.435°	icosahedron
n	?	?	?	?	?	?

So what's the answer? I don't know, but one thing we have learned is that a cube's points are not all evenly spaced. The angles between them follow a regular pattern. There are twelve edges with an angle between the points of 2 arcsin(1/√3) ≈ 70.528°, and if you include all triangle edges, there are six edges with an angle between the points of 2 arcsin(√2/√3) ≈ 109.4712°.

Continue reading Part 2

Silverlight freezing when calling XMLHttpRequest?

2009-07-18T16:57:00.000-07:00

I had a javascript handler for a Silverlight event that modifies the XAML, uses XMLHttpRequest synchronously, then modifies the XAML again, and I noticed that the browser would sometimes not show the first XAML change while the XMLHttpRequest was pending.

Example XAML:

<?xml version="1.0" encoding="utf-8" ?> 
<Canvas xmlns="http://schemas.microsoft.com/client/2007">
  <TextBlock Name="Txt1" Canvas.Left="25" Canvas.Top="25" FontSize="12" >Click the box</TextBlock>
  <Rectangle Name="Rect1" Canvas.Left="25" Canvas.Top="40" Width="200" Height="35" Fill="#8080FF" MouseLeftButtonDown="rect1_onMouseDown" />
</Canvas>

Example javascript:

function rect1_onMouseDown(sender, mouseEventArgs) {
  var txt1 = sender.findName("Txt1");
  var rect1 = sender.findName("Rect1");

  txt1.text = "In Progress...";
  rect1.Cursor = "Wait";

  var xmlhttp = new XMLHttpRequest();
  xmlhttp.open("GET", "long-running-url.asp", false);
  xmlhttp.send(null);

  rect1.Cursor = "Default";
  txt1.text = "Complete.";
}

Firefox shows "In Progress..." and then "Complete", but IE waits until everything is finished and then shows "Complete". So when exactly does Silverlight redraw itself when changes are made?

The answer is that changes are not drawn immediately after they are made. They are made the next available chance when no scripts are actively running.

Example that does not redraw on either Firefox or IE.

function rect1_onMouseDown(sender, mouseEventArgs) {
  var txt1 = sender.findName("Txt1");
  var rect1 = sender.findName("Rect1");

  txt1.text = "In Progress...";
  rect1.Cursor = "Wait";

  // keep busy for 0.5 sec
  var start = new Date();
  while ((new Date()).getTime() - start.getTime() < 500) {
    // do nothing
  }
  
  rect1.Cursor = "Default";
  txt1.text = "Complete.";
}

So if you want a redraw while an XMLHttpRequest is pending, do it asynchronously.

function rect1_onMouseDown(sender, mouseEventArgs) {
  var txt1 = sender.findName("Txt1");
  var rect1 = sender.findName("Rect1");

  txt1.text = "In Progress...";
  rect1.Cursor = "Wait";

  var fnOnStateChange = function() {
    if (xmlhttp.readyState == 4) {
      rect1.Cursor = "Default";
      txt1.text = "Complete.";
    }
  };
  var xmlhttp = new XMLHttpRequest();
  xmlhttp.onreadystatechange = fnOnStateChange;
  xmlhttp.open("GET", "long-running-url.asp", true);
  xmlhttp.send(null);
}

Here are a few more examples shown live. The first and third will be redrawn inconsistently and the second one will always redraw during the intermediate "In Progress..." stage. Click the download links below to see the source code for this.

Download source.html, source.xaml

Calling MySQL stored procedures from C# with Connector/Net

2008-12-12T21:08:00.000-08:00

Have ever got this error when calling a stored procedure?

MySql.Data.MySqlClient.MySqlException: SELECT command denied to user 'your_user'@'localhost' for table 'proc'
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket () [0x00000] 
  at MySql.Data.MySqlClient.NativeDriver.ReadResult (System.UInt64& affectedRows, System.Int64& lastInsertId) [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet () [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00000]

Let me explain what is going on here. The first thing Connector/Net does when you call a stored procedure is select the full text of the stored procedure from the server, parse it, and cache information about the parameters. It then uses that information to build the statement for the actual stored procedure call.

It does this by either calling
SELECT * FROM mysql.proc WHERE db LIKE 'mydb_name' AND name LIKE 'MyProcedure';

or by calling
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'mydb_name' AND ROUTINE_NAME LIKE 'MyProcedure';

Why does it do this?

Let me give you a little background. Microsoft SQL Server has a nice feature in that it gives you a lot of flexibility when you call stored procedure. The syntax is
EXEC MyProcedure @MyParam1=123, @MyParam2='blah';

The parameters can appear in any order and any of the parameters can be specified as optional in the stored procedure. This is nice when you want to add a new parameter to a stored procedure and release it to your website without taking downtime. First you run an ALTER statement to release the new stored procedure with a new optional parameter, then you release the new application code which passes the new parameter to the stored procedure. Everything works fine when the application code and stored procedure are not in sync because the new parameter is optional.

MySQL's syntax is different. It is
CALL MyProcedure(123, 'blah');

The parameters have to appear in the right order and there is no support for optional parameters.

Now let's look at how stored procedures are called from the application code. Calling Microsoft SQL Server from C# code looks like this

string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) {
  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("MyProcedure", conn);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.Add("@MyParam1", System.Data.SqlDbType.Int).Value = 123;
  cmd.Parameters.Add("@MyParam2", System.Data.SqlDbType.VarChar, 40).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}

MySQL Connector/Net mimics this syntax fairly closely.

string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("MyProcedure", conn);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.Add("@MyParam1", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 123;
  cmd.Parameters.Add("@MyParam2", MySql.Data.MySqlClient.MySqlDbType.String).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}

So in order for Connector/Net to figure out what order to put the parameters when it creates the CALL statement it gets the stored procedure definition from the server and looks at the order that the parameters appear in the original CREATE PROCEDURE statement.

There are a couple of problems with this.

The account the application code is running under may not have select permissions on mysql.proc and if you are using shared web hosting it may not be possible to give your account these permissions.
There is a performance penalty for retrieving and storing the stored procedure bodies.

So what I recommend doing is building the CALL statement yourself as a parameterized query.

My recommended way to call MySQL stored procedures

string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "CALL MyProcedure(@MyParam1, @MyParam2);";
  cmd.Parameters.AddWithValue("@MyParam1", 123);
  cmd.Parameters.AddWithValue("@MyParam2", "blah");
  // Or if you prefer this style:
  //cmd.Parameters.Add("@MyParam1", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 123;
  //cmd.Parameters.Add("@MyParam2", MySql.Data.MySqlClient.MySqlDbType.String).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}

Connector/Net will go through the CommandText and replace @MyParam1 and @MyParam2 with the values you provide so the statement that gets sent to the MySQL server is just CALL MyProcedure(123, 'blah'); It's a good idea to do it with parameters because the library code will properly escape single quote characters. If you don't do this, the SQL statement could end up with syntax errors, or worse, end users might be able to run malicious SQL queries against your MySQL server.

Output parameters

Connector/Net does not handle parameters with Direction = System.Data.ParameterDirection.Output unless CommandType = System.Data.CommandType.StoredProcedure. In our case we're using CommandType.Text so we can only use ParameterDirection.Input.

So what do we do if we want to call a stored procedure that has an output parameter?

We use MySQL User Variables. This is the statement we will send to the MySQL server.
CALL MyProcedure(@MyOutputParam1, @MyOutputParam2, 123, 'blah');
SELECT @MyOutputParam1, @MyOutputParam2;

But before we can make this work there are a couple things we need to take care of. One is that we need to include Allow User Variables=true; in the connection string.

Server=localhost; Port=3306; Database=your_database; Uid=your_user; Pwd=your_password; Connect Timeout=30; Allow User Variables=true;

If we don't do this Connector/Net will throw an exception. This is because it checks that the parameter names in the CommandText match up with the parameters that have been added to the list and it doesn't have a way to distinguish between user variables and parameters. If we set Allow User Variables=true; it will skip this check.

The other thing we have to take care of is the data type of the user variables. Connector/Net does not handle result sets with user variables in them properly.

To see for yourself, try running this query:

SET @a = true, @b = 222, @c = -1, @d = 'abcd';
SELECT @a, @b, @c, @d, true, 222, -1, 'abcd';

Connector/Net returns the first three columns in the result set as byte[] and sets the contents of the byte array to be the raw bytes sent back by the MySQL server. The fourth column is a normal string and the rest of the columns are all handled properly.

The MySQL Command Line Client, on the other hand, handles user variables properly. You can see it here.

mysql> SET @a = true, @b = 222, @c = -1, @d = 'abcd';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a, @b, @c, @d, true, 222, -1, 'abcd';
+------+------+------+------+------+-----+----+------+
| @a   | @b   | @c   | @d   | TRUE | 222 | -1 | abcd |
+------+------+------+------+------+-----+----+------+
| 1    | 222  | -1   | abcd |    1 | 222 | -1 | abcd |
+------+------+------+------+------+-----+----+------+
1 row in set (0.00 sec)

I was curious what was going on so I intercepted the response sent back by the MySQL server to see if I could tell why Connector/Net was failing. It sends the column definitions first and then the contents of each row. I could see that the row data was the same for column @a and column TRUE, column @b and column 222, and so on, but the header containing the column definitions were different. I didn't investigate further and instead looked for a workaround.

Here is what I did to make it work.

CALL MyProcedure(@MyOutputNum1, @MyOutputString2, 123, 'blah');
SELECT CAST(@MyOutputNum1 AS SIGNED), @MyOutputString2;

If your parameters are not signed integers you will need to cast them as something else. Read about using CAST and CONVERT. Note that SIGNED and UNSIGNED are both 64 bit integers.

Now that I know what to do I can create the application code.

string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "CALL MyProcedure(@MyOutputNum1, @MyOutputString2, ?MyParam1, ?MyParam2); SELECT CAST(@MyOutputNum1 AS SIGNED), @MyOutputString2;";
  // I am using the ?param style to make it easy to differentiate between user variables and parameters. @MyParam1 would also work.
  cmd.Parameters.AddWithValue("?MyParam1", 123);
  cmd.Parameters.AddWithValue("?MyParam2", "blah");
  conn.Open();
  MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader();
  // If MyProcedure returns a result set that will come first so you will need: 
  //   while (rdr.Read()) {...} 
  //   and rdr.NextResult();
  // Now get the output parameters.
  long myOutputNum1 = -1;
  string myOutputString2 = null;
  if (rdr.Read()) {
    int i = 0;
    if (rdr.FieldCount > i && !rdr.IsDBNull(i)) myOutputNum1 = rdr.GetInt64(i);
    i++;
    if (rdr.FieldCount > i && !rdr.IsDBNull(i)) myOutputString2 = rdr.GetString(i);
  }
}

That worked.

Javascript and C# encryption

2008-12-06T10:50:00.000-08:00

If you are unable to use SSL and you want to use encryption, one option is to use javascript to encrypt and send your data. Then on the server side you can decrypt the data, process it, and send back an encrypted response to be decrypted by javascript.

I have created a page which does this. I used AES (also known as Rijndael) for the private key encryption algorithm. The .NET framework has a built-in class library implementation of this called System.Security.Cryptography.RijndaelManaged and there are some javascript implementations out there on the web. I copied one of them and made some modifications to it, such as adding Base64 encoding.

The private key is hard coded in the server side code and must be entered into a textbox on the page by the user.

Download the code

Running T-SQL queries from your browser against Microsoft SQL Server

2008-12-06T09:53:00.000-08:00

In a previous blog post I wrote about how to run MySQL queries from your browser. I thought I would post the source code for running T-SQL queries against Microsoft SQL Server as well, in case anyone wants it.

Download source code

I also made a version that encrypts data before transmitting it.

Running MySQL queries from your browser

2008-12-05T20:25:00.000-08:00

Download the code. You will also need to download the Connector/Net binaries.

phpMyAdmin has a place for you to run MySQL queries but there are a few reasons you might want to have a your own admin page on your site for running ad hoc queries.

You might want your queries to run under a different user than the one used by phpMyAdmin. See here for why you may need to run your stored procedure creation scripts this way.
You might want to test a query going through Connector/Net.
Maybe you just want a way to run queries faster without having to log in.

Here is a snippet from a simple page I created to run MySQL queries against my site.

protected void btnSubmit_OnClick(object sender, System.EventArgs e) {
  if (txtKey.Text == "some random string") {
    StringBuilder sb = new StringBuilder();
    using (MySqlConnection conn = new MySqlConnection("Server=localhost; Port=3306; ...")) {
      MySqlCommand cmd = new MySqlCommand(txtSql.Text, conn);
      conn.Open();
      MySqlDataReader rdr = cmd.ExecuteReader();
      while (...) {
        ...
        sb.Append(results of query);
        ...
      }
    }
    litOutput.Text = sb.ToString();
  }
}

Download the real code

Security considerations

Remember to put in a random string for your key in the page.
You may want to remove or disable the page when you are not using it.
Use ssl encryption whenever you visit the page. If you don't have an ssl certificate for your site you can create a self signed certificate in cPanel if your hosting provider lets you. Mine lets me create and import certificates but they don't let me turn on ssl for my site unless I pay for an upgrade so I cannot actually do anything with them. A self signed certificate will give you a warning in your browser but the transmission will still be encrypted. If you are like me and can't enable ssl for your site another option is to encrypt the data in javascrypt before sending it to your server side code.
Download source code of a version that uses encryption.

MySQL stored procedure permissions and cPanel

2008-11-26T19:31:00.000-08:00

To set up my site I created all my MySQL tables and stored procedures by running a script in phpMyAdmin, and then I deployed my application files. This resulted in the following error:

execute command denied to user 'my_user'@'localhost' for routine 'my_database.MyProc'
Description: HTTP 500. Error processing request.

Stack Trace: 

MySql.Data.MySqlClient.MySqlException: execute command denied to user 'my_user'@'localhost' for routine 'my_database.MyProc'
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket () [0x00000] 
  at MySql.Data.MySqlClient.NativeDriver.ReadResult (System.UInt64& affectedRows, System.Int64& lastInsertId) [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet () [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00000]

The problem is that cPanel only allows you to grant permissions for a subset of actions.

 SELECT                       CREATE
 INSERT                       ALTER
 UPDATE                       DROP
 DELETE                       LOCK TABLES
 INDEX                        REFERENCES
 CREATE TEMPORARY TABLES      CREATE ROUTINE

The key one that they inexplicably did not add is EXECUTE.

CREATE ROUTINE, ALTER ROUTINE, and EXECUTE were all added to MySQL in version 5.0.3 and have been available since March 2005. I am using shared web hosting that was set up with the latest version of MySQL (version 5.0.67, which came out in August 2008) and the latest version of cPanel (version 11 with the latest build from November 2008). So if I do a little math in my head I can see that sometime in the last three and a half years, the people who make cPanel became aware that MySQL added support for stored procedures and realized that they needed to add a way for people to grant MySQL users the CREATE ROUTINE permission but they still haven't done anything about the EXECUTE permission.

It is not an ideal situation.

What I would like is to have an admin user that has full permissions to do everything on a particular database -- including make schema changes and create stored procedures -- and another user that only has permission to SELECT from tables and EXECUTE routines. The user with the limited permissions is the one that I put in my web.config and use from my web application code.

So I tried granting the permissions by manually executing the command in phpMyAdmin.

GRANT EXECUTE ON my_database.* TO my_user@localhost;

It didn't work.

#1044 - Access denied for user 'my_admin_user'@'localhost' to database 'my_database'

This is another thing that cPanel has not done correctly. My admin user should have been set up with the GRANT permission on my_database. Now there is no way for me to give a user EXECUTE permission. It won't even work if I try using the DEFINER syntax in MySQL.

DELIMITER $$

DROP PROCEDURE IF EXISTS MyProc$$

CREATE DEFINER = my_user@localhost PROCEDURE MyProc()
BEGIN

-- <procedure body>

END $$

DELIMITER ;

I get an error.

#1227 - Access denied; you need the SUPER privilege for this operation

So what do I do?

The Solution

Create the stored procedures using the account with limited permissions. MySQL keeps track of which account was used to create each stored procedure and automatically allows that account permission to execute it.

Here is a step by step guide to the process.

Go into cPanel and check the box for CREATE ROUTINE for your my_user account.
Upload a script to your website that will create your stored procedures using my_user.
Here is a script that you can use.
Call your script from your browser.
Remove the script from your website.
Go into cPanel and uncheck the box for CREATE ROUTINE.

Xml Deserialization: a home grown method

2008-11-22T19:33:00.000-08:00

Download source code
If you have ever called System.Xml.Serialization.XmlSerializer.Deserialize and got this exception: System.InvalidOperationException: '' was not expected, you know that getting the xml namespaces right for deserialization can sometimes be a trial and error process.

If you have access to the class you want to deserialize to you can add a custom attribute to define the xml namespace to use for serialization.

[System.Xml.Serialization.XmlTypeAttribute(Namespace="MyNamespace")]
public class MyClass {
    ...
}

If the class is in one of the built in libraries or in a third party library and you can't modify it you can modify the xml to add a namespace attribute instead.

<MyClass xmlns="MyNamespace">

Another alternative is to use different deserialization code that is more forgiving about things like xml namespaces and case sensitivity. I have created one that uses the XML DOM parser System.Xml.XmlDocument and System.Reflection to iterate through the public fields and properties of an object and find the xml elements and attributes that best match the field names.

// simplified snippet
public static T XmlStrToObjBestWeCan<T>(string xml) {
    System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
    doc.LoadXml(xml);
    foreach (System.Reflection.FieldInfo field in typeof(T).GetFields()) {
        foreach (object attribute in field.GetCustomAttributes(typeof(System.Xml.Serialization.XmlElementAttribute), false)) {
            ...
        }
        foreach (System.Xml.XmlNode child in doc.DocumentElement.ChildNodes) {
            ...
        }

Download complete file

You can download the code and include it in your project to see how it compares to the built in deserializer.

ASP.NET 1.1 and 2.0 on Mono

2008-11-20T21:00:00.000-08:00

One of the first problems I had when setting up my ASP.NET site on Linux and Mono was that the server I was using was configured to use ASP.NET 1.1 instead of 2.0. This showed up as an error in my Page directive about the Title attribute.

Parser Error

Description: Error parsing a resource required to service this request. Review your source file and modify it to fix this error.

Parser Error Message: Unknown attribute: Title

Source Error:

Line 1: <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>

So I removed the Title attribute and I got new error.

Line 1: <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" %>

Version Information: Mono Version: 1.1.4322.2032; ASP.NET Version: 1.1.4322.2032

That was when I noticed the version number at the bottom. It 1.1 instead of 2.0. Just to make sure that Mono wasn't using a versioning system that was different from Microsoft's I created a simple page that used a common .NET 2.0 feature.

<%@ Page Language="C#" %>
<html>
<head><title></title></head>
<body>
<div>System.Version = <%= Environment.Version.ToString() %></div>
<div>List<string>.Count = <%= (new System.Collections.Generic.List<string>()).Count.ToString() %></div>
</body>
</html>

It got a compilation error.

error CS1644: Feature `generics' is not available in Mono mcs compiler. Consider using Mono gmcs compiler instead

Once I knew what the problem was I had to figure out how to fix it. I found a description of what to do here. You have to change the MonoServerPath setting from <path>/mod-mono-server to <path>/mod-mono-server2 in the config file that contains it. This may be mod_mono.conf or httpd.conf or some other file. I am not sure where it is commonly placed.

I am using shared web hosting so I don't have access to change or even view those files, so I contacted my web hosting company (Ubiquity) and asked them to change it. They had me try changing it in my .htaccess file first, which I tried and found that it didn't work. The commands to start up the mod mono server so that it can handle ASP.NET requests must happen in a certain sequence in the config files so tacking it on after startup in an .htaccess file didn't work. It caused HTTP 500 errors for any requests to my directory. It may be possible to set up a fully working sequence of mod mono startup commands in an .htaccess file in order to override the ASP.NET version for a particular directory. I tried a number of different combinations while I was waiting for my hosting company to change it at the server level but none of the things I tried worked.

They wanted to change it at the server level anyway because they advertise supporting ASP.NET 2.0 and I think that is how they had intended to set it up. I suspect that the commonly downloaded version of Mono is still set up to use ASP.NET 1.1 by default instead of 2.0. If that is the case they should change that so people who install it don't have to go find the setting and change it themselves.

Setting up my site on Mono

2008-11-19T10:08:00.000-08:00

I got my MySQL database set up through cPanel and PhpMyAdmin without any difficulty. I maintain re-runnable setup scripts for all my tables and stored procedures which makes life easier.

I got FTP set up by downloading WinSCP for my FTP client and setting up a key in cpanel so I could use SFTP. Then I started uploading some files.

I set up a file deployment batch file which would create a releasable package for easy copying. Then I uploaded all my web site files and tried it out.

It didn't work. The problem was that the server was configured to use ASP.NET 1.1 instead of 2.0. (See more about what I did to identify and fix this problem here.)

I opened a support ticket with my web hosting company (Ubiquity) and exlained the situation to them and asked them to change it at the server level to use ASP.NET 2.0 by default. They did, but it took them a couple days to do it and get it working. I am guessing that most of the people that use their shared web hosting are not using Mono because it seemed like the support guys hadn't run into this issue before. If anyone else runs into it now they will probably be faster at fixing it, or better yet, they may change their default setup for new servers so that it works the right way automatically.

Once the ASP.NET 1.1 / 2.0 issue was resolved I uploaded my main project to my directory again and gave it another try. That was when I encountered the next problem: my stored procedures I created in MySQL were not working. I'll put up new blog entry at some point explaining what the issue was there and how I resolved it. Stay tuned.

Choosing a web hosting provider

2008-11-19T09:44:00.000-08:00

There are not too many companies that are offer shared web hosting with Mono. One of the ones that does is Ubiquity and that is who I decided to go with. Their price was pretty good at $5.95 a month for a one year commitment with a 45 day money back guarantee plus free domain registration. That is $71.40 up front.

I liked their feature set and the comments on the web about them seemed mostly favorable.

After I went through their sign-up process I was excited to try it out and start playing around with it but when I tried the IP address they gave me it was not set up yet. I kept trying over the next couple days. I looked through their website to find out how long it would take and I saw that it could take up to 72 hours for some set ups and if it still wasn't set up after that to enter a support ticket.

So I entered a support ticket and a few hours later someone got back to me and said it was working fine for them. I emailed them back that I was still unable to access it and that ping and tracert were not working. A few hours later they emailed me back and said my IP address had been blocked on their firewall and that it should be working now.

Doh. I should have opened up a ticket sooner.

It was still a ways away from getting things working at that point though. Read more.

Adventures in Mono

2008-11-12T22:40:00.000-08:00

Last week I got my TwixT website working well enough that I decided it was time to share with the world. I developed it in C# and ASP.Net, with a MySQL database and a Silverlight 1.0 app to for interactive game play.

The idea behind the website was to make a place where people can play TwixT each other interactively, or play against the computer.

I had it working pretty well on my local computer but to go live with it I needed a web hosting provider. The first thing I needed to decide was whether to choose a Linux or Windows based hosting. I decided to go with Linux because it was a little cheaper and from what I had read about Mono, it had almost all of the features in .Net 2.0, plus I thought it would be interesting to try.

Read more about how I chose a web hosting provider and tried to get my site working on Mono.