LS4 : WCF Ria + MySQL - max user connections error

Hey there. 

I've deployed my SL4 application for testing with some friends of mine. 

I'm using multiple WCF RIA services in my solution. I've run into a problem with exceeding maximum user connections numbers (which apparently is smaller then I've expected)

 

Here's a base class for all of the services within the solution :

---

public abstract class CiopanDomainService : LightSpeedDomainService<DomainUnitOfWork>

    {

        protected override LightSpeedContext<DomainUnitOfWork> CreateLightSpeedContext()

        {

            return DomainContext.Context;

        }

 

        private DomainUnitOfWork _UnitOfWork;

        public DomainUnitOfWork UnitOfWork

        {

            get

            {

                if (_UnitOfWork == null)

                {                    

                    _UnitOfWork = CreateTypedUnitOfWork();

                    System.Diagnostics.Debug.WriteLine("{0}::CreateTypedUnitOfWork", this.GetType().Name);

                }

 

                return _UnitOfWork;

            }

        }

 

        private CloseableConnectionStrategy _CloseableConnection;

        public CloseableConnectionStrategy CloseableConnection

        {

            get

            {

                if (_CloseableConnection == null)

                {

                    _CloseableConnection = new CloseableConnectionStrategy(LightSpeedContext);

                    System.Diagnostics.Debug.WriteLine("{0}::Creating Closable Connection", this.GetType().Name);

                }

 

                return _CloseableConnection;

            }

        }

    }

---

The ClosableConnectionStrategy is taken from your forum here

 

Here's an implementation of the smallest of my services :

---

[EnableClientAccess]

    public class NewsPostsService : CiopanDomainService

    {        

        [Query]        

        public IQueryable<NewsPost> GetNewsPosts()

        {

            using (UnitOfWork.ConnectionStrategy = CloseableConnection)

            {

                return UnitOfWork.NewsPosts.OrderByDescending(np => np.CreatedDateUTC).Take(25);

            }

        }

 

        [Insert]

        [RequiresRole("Administrator")]

        public void AddNewsPost(NewsPost item)

        {

            using (UnitOfWork.ConnectionStrategy = CloseableConnection)

            {

                item.CreatedDateUTC = DateTime.UtcNow;

                UnitOfWork.Add(item);

                UnitOfWork.SaveChanges();

            }

        }

 

        [Delete]

        [RequiresRole("Administrator")]

        public void DeleteNewsPost(NewsPost item)

        {

            using (UnitOfWork.ConnectionStrategy = CloseableConnection)

            {

                var itm = UnitOfWork.FindById<NewsPost>(item.Id);

                UnitOfWork.Remove(itm);

                UnitOfWork.SaveChanges();

            }

        }        

    }

 

---

 

When the application starts up ~4 db are created in the MySQL server (3x Services + a custom role provider, all using LS. Connection strategy is used everywhere).

I've conducted an experiment - and Loaded the method marked with [Query] multiple times. In the mean time I was Server Connections in the MySQL administrator. 

The old connections seemed to be persistent + some times I noticed their count rising steadily. 

 

Could you explain this behavior ? I need to work around this so that I run into no trouble with my host.

Regards

 

When the application starts up ~4 db are created in the MySQL server (3x Services + a custom role provider, all using LS. Connection strategy is used everywhere).

 

I'm sorry - I meant circa 4 connections/connection threads in the db are created in the MySQL server.

I went a bit further, and did some reading on MySQL's data provider. I was calling IDbConnection.Close() explicitely - with no effect. So I started to suspect MySQL data provider or the database itself. After some reading it turned out that if I add "pooling=false" to the connection string the "closed" connections will no longer persist.

 

Right now I'm seeing a single connection alive at all times and that's most likely the connection coming from the role provider. Seems that I've managed to solve the problem - leaving the solution in here just in case anyone runs into it in the future.

Btw. I'd love a comment on the pattern I'm using above

using(UnitOfWork.ConnectionStrategy = ClosableConnection)

{

...

}

I spoke to soon, it appears that pooling=false doesn't fix the issue. I could really use some help with this - as I'm running out of user connections to MySQL quickly.