Csharp-sqlite+ADO.NET = Valentines Day Win!

As part of my work on OpenSimulator, we ran into an issue with the sqlite/native wrapper in Mono.Data.SQLiteClient so..  to get to the bottom of it..     I molded the Mono.Data.SQLiteClient over top of csharp-sqlite to enable full ADO.NET support with the managed sqlite library, csharp-sqlite.

It turns out, that while csharp-sqlite isn't quite ready for prime time, it's good enough for basic function.

The result is you can now use csharp-sqlite in ADO.NET.

The following zip file contains a modified csharp-sqlite.   The namespace for which is csSQLite and Community.Data.SQLite.   It also contains contains a set of ADO.NET classes to make use of csharp-sqlite; http://teravus.wmcv.com/googletester/csharp-sqliteado.zip

Open The Community.Data.SQLiteClient folder and load the solution.   It's already configured to load the csharp-sqlite project.   When you compile, the result of both builds will be in the bin folder in the csharp-sqlite/Community.Data.SQLiteClient folder.

Before you jump in, just a word of warning, this doesn't seem to be able to successfully load sqlite databases created with the native sqlite library yet.

For more information on csharp-sqlite, you can visit the project home page at: http://code.google.com/p/csharp-sqlite/

To see my e-mail to the C# SQLite google group containing all of the information, you can visit the message at: http://groups.google.com/group/csharp-sqlite/browse_thread/thread/e775361c98307176

 It turns out that the OpenSimulator issue was a sqlite file locking issue that's manifesting itself as an ugly error message.    So, while this was an interesting diversion and got us one step closer to figuring out what went wrong with the native library version in OpenSimulator, it doesn't immediately solve the issue with the SQLite database connector in OpenSimulator with Mono 2.4.3 and above.

Lately, I've been getting a lot of reports of being unable to log into various websites.   

Most people, however, don't have an issue.     Just a few.  

So, I set out to locate the common element here.    

It turns out that some browsers occasionally get errors in the index of cookies that cause them not to expire from the cookie folder but, when the browser reads the cookie from the cookie store, it immediately expires them and doesn't send them back to the server.    Unfortunately, it also doesn't get rid of the cookie in the cookie store!   This 'state' of cookie doesn't allow the browser to accept a new cookie from the server.   This presents itself to users in the inability to log-in to a website or a website's inability to remember the user if they've checked 'remember me'.

You might think that simply clearing cookies would solve the issue.    In some cases, it does.     In some cases, it doesn't.    

When clearing cookies fails to resolve the problem, there's a workaround for Internet Explorer, the user needs to open an Explorer window to the user profile directory on their computer.   Commonly (on windows XP) , this is C:\Documents And Settings\YourUsername.    From there, go to the 'Cookies' folder/Directory.     Delete all of the items in there except Desktop.ini and Index.dat.  

There's also a workaround for FireFox:
Tools > Options > Advanced > Network > Offline Storage (Cache): "Clear Now"
Tools > Options > Privacy > Cookies: "Show Cookies"

It's also possible that the file cookies.sqlite that stores the cookies is corrupted.
You can try to rename or delete cookies.sqlite and cookies.txt in your Profile Folder.

See http://kb.mozillazine.org/Profile_folder_-_Firefox for more information about how to find the profile folder on your computer 

Two days ago, I got a call from the customer service of one client that they were getting an ASP .NET error page (with the custom error page) when they were trying to use the account administrative service to manage user account data through a secure ASP.NET application.     With ASP .NET on a windows box, the actual error message will appear in the Event Log in the Application section so I took a look there.

The error message was an InvalidOperationException when using a System.Data.SqlClient.SqlConnection Open method.      The issue was that it wasn't able to get a pooled connection from the .NET SQL pool.    When recycling the application pool for this application in the IIS manager, the page would load up to the point when it required SQL Connections and then would wait.   I waited for a minute or two and it continued to try to load.   Upon refreshing the page, the ASP .NET error page appeared again.     It was clear that the server was unable make a connection with the database and that the connection pool was being exausted.    This wasn't immediately picked up by the application monitoring systems because they're on the public side of the server cluster.    

What struck me as odd was that the application is designed to handle those kinds of InvalidOperationExceptions gracefully by creating a new connection.   The idea here, is that; while, it's important to make sure that all database resources are managed and properly closed and disposed, it would be impossible to crash the application by exausting the sql connection pool.

The default in this application is to use a pooled connection but if the pooled connections are unavailable and .NET throws an InvalidOperationException on SqlConnection.Open, modify the connection string to inform .NET to create a new unpooled connection.

        private SqlConnection GetSQLConnection(Database db, bool pooled)
        {
            SqlConnection dbconn = null;

            lock (openConnections)
            {
                if (openConnections.ContainsKey(db))
                {
                    dbconn = openConnections[db];
                }
                else
                {
                    string connectionstring = ConnectionStrings[(int)db];
                   
                    if (pooled)
                        connectionstring += ";Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;Connection Timeout=45";
                    else
                        connectionstring += ";Pooling=false;Connect Timeout=45;Connection Timeout=45";
                    dbconn = new SqlConnection(ConnectionStrings[(int)db]);
                    openConnections.Add(db, dbconn);
                }
            }
            return dbconn;
        }

The important bit about that code is:

• If the connection is pooled, append the pool size and pool timeout settings to the connection string.
• If the connection is not to be pooled, make sure to tell the .NET library to skip the pool.

Then, the connection is opened inside a Try/Catch for InvalidOperationException.   If an error is thrown by the try/catch, it gets a non-pooled connection.

                    try
                    {
                        conn.Open();
                    }
                    catch (InvalidOperationException)
                    {
                        // Try to start a non pooled database connection.
                        conn = GetSQLConnection(db, false);
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                    }

When everyone is doing their job, this extra protection is not needed and rarely, if ever, executes.   40 database connections simultaniously open is Slashdot Effect territory.   It doesn't happen often.

This bit of code was acting exactly as designed.  It just so happens however, that a Microsoft Update got applied a few hours earlier and the server was waiting for the next planned outage window to reboot.  After rebooting the server, the server was once again able to resume normal operations.

Shortly after, I started getting complaints that a separate application wasn't allowing users to log-in.      After debugging for a bit, it turned out that the state was no longer being kept in the cluster for users.  This issue was on a different server in the cluster.   When a user 'switched' which 'behind the scenes' server in the cluster, the server didn't know what the user did last and therefore reported to the application that the user wasn't logged in.   It just so happened that a Microsoft Update got applied several hours earlier and the server was waiting for the next planned outage window to reboot.     After restarting the second server, I stopped getting complaints.

So far, there's been no re-occurance.   All servers in the cluster have been updated and rebooted.      I can't wait to see what sort of system specific, untestable situation will happen on the next round of 'Microsoft Updates'

Yay, It turns out that there were two bugs with regards to terse updates.    The first one was in SceneObjectPart.SendScheduledUpdates().    In some cases, it didn't clear the UpdateSchedule using ClearUpdateSchedule().   This means that objects that failed the duplicate test were never getting the m_updateFlag set back to zero.       In this state, the call to ScheduleTerseUpdate would fail because of the m_updateFlag < 1 test.  It would skip entirely over the code that schedules terse updates.

The second update was adding:

Velocity.ApproxEquals(Vector3.Zero, VELOCITY_TOLERANCE) ||

to the duplicate check in SendScheduledUpdates().   This ensures that Updates with the velocity <0,0,0> get sent.    Without that, the Zero velocity vector isn't guarenteed to get out.    Thirdly, I added a check in the ODEPrim.Velocity Getter.  If _ZeroFlag, then return <0,0,0> for the velocity.   If _zeroFlag is set, it has come to a rest regardless of what the tiny amount of velocity it might have.   Here, I think the averaging of the previous velocities was working against us.     "returnVelocity.X = (m_lastVelocity.X + _velocity.X)/2;"   will return a higher velocity when the object is stopped then it should for the last update with a zero velocity.   This causes the object to keep moving.    The averaging was put in place to make object motion appear more smooth.