U4-4621 - Losing data when using IIS Express and SQL CE

Created by Johan Benjaminsson 04 Apr 2014, 15:22:50 Updated by Sebastiaan Janssen 24 Mar 2016, 12:23:23

Duplicates: U4-7996

Relates to: U4-7996

Relates to: U4-1649

Relates to: U4-2001

Sometimes when restarting the server i lose SQL data. I have tried in Umbraco 7.1 beta and the released version and both have the problem. Jan Skogvaard have seen this behavior on both 4 and 6 as well. http://our.umbraco.org/forum/umbraco-7/using-umbraco-7/49713-Losing-data-on-server-restart

Comments

Dan Bramall 11 Apr 2014, 10:49:17

I see this as critical. It seems that if working with IIS Express and SQLCe, data in your project (anything: document types, data types, content - the whole lot) can just suddenly disappear without recourse.

This has happened several times to me, mostly 'just' losing a few content nodes or document types, but it just wiped out half a day of yesterdays work, v7.1.1. So I won't be touching SQLCe again - despite it being the default database on install. It makes me worried that more people will encounter this since the new installer channels people down the SQLCe route.


Peter Slight 14 Apr 2014, 10:44:42

Experienced this same problem on 7.1.1.

Developing with Webmatrix3, created a bundle of document Types, then created a number of templates. Navigating around the "settings" section of the interface all seemed to be saved and working as I'd expect.

It seemed like it lost everything after I navigated to the "Content" section, but it's possible that I restarted the local server.

When trying to look at the umbraco.sdf file in webmatrix and looking at the cmsDocumentType table it would crash Web Matrix.


Douglas Robar 14 Apr 2014, 15:25:35

Some background:

This issue is definitely related to the combination of WebMatrix3 and SQLCE. Use VS, IISExpress from command line, real IIS, SQLexpress or full SQLserver and you don't get this problem. It is only the combination of WebMatrix 3 (not 2) and SQLCE. Seems like it must be a bug for MS.

SQLCE allows only a single connection to the database. The website has that. WM3 has continually had problems with the db editor trying to be clever about how to get its commands into that single connection. They've done it but it isn't bullet proof, obviously.

My advice: don't use the 'Database' section of WebMatrix when using SQLCE, not even to look at the database.

Further, SQLCE has, by default, a 10 second flush interval. Meaning, database activity is cached for up to 10sec before being flushed to the database. If WebMatrix3 is shutdown or crashes within this 10sec period it is possible (though not assured) that some data might not be written to the database. Again, this is ONLY the case with the combination of WM3 & SQLCE. Any other combination of web server and database will not exhibit this potential data loss situation.

Unfortunately, you can't force SQLCE to write to the database immediately. The shortest interval is 1 second. Performance will suffer slightly by reducing the flush interval but I've not found it objectionable and the potential benefits are worthwhile. What you want to do is add "flush interval=1" to your umbracoDbDSN connectionString, such as:

Having said this, I have seen the rare case when WebMatrix dies even after the 1 second flush time has elapsed and data loss can happen. Even so, this is your best protection to date, short of using something other than WebMatrix (consider full IIS, IISexpress.exe from command line, or from VisualStudio) or using SQLserver or SQLserver Express.

I also advocate regular backups of development sites. I've never messed up a site more thoroughly than on a development machine when I thought I knew what I was doing but made a mistake (my favorite goof? deleting a document type that had content associated with it while "cleaning up" a site just before it went live).

Where's this leave us? In some ways that's up to you and how you want to mitigate this WebMatrix 3 bug. For me, I love WebMatrix and I love SQLCE. I just don't tend to use them together except when I need to run a quick experiment. For any site that I'm going to be developing diligently I move to IIS and keep SQLCE until I have a reason to upscale to SQLserver Express. But that's just me. You might find running under Visual Studio is a better choice. Or using SQLserver Express from the very start with WebMatrix.

Hope this "from the trenches" information helps.

cheers, doug.


Jeavon Leopold 13 Oct 2014, 09:22:37

For reference this issue also occurs when using Visual Studio with IIS Express and SQL CE so I would take a guess and say it's IIS Express v7.5 and SQL CE that is the combination with the problems...


Funka! 24 Oct 2014, 17:29:41

I added my thoughts on this today to the bottom of page 2 in the [Our.umbraco thread|http://our.umbraco.org/forum/umbraco-7/using-umbraco-7/49713-Losing-data-on-server-restart?p=1#comment196648] referenced in this issue's description.


Casper Schmidt 28 Nov 2014, 21:58:07

I have the same problem with 7.1.6 running on IIS 7.5 (not express). I have been working on my first Umbraco site for some weeks now but suddenly today I have problems saving my macro, content type etc. I tried adding a simple gallery macro (following the free Umbraco.TV guide) but my changes to the empty macro template keeps disappearing. I copy/paste the script, make some changes (personal preferences), click save and I'm told that it was saved without errors. Then I go to some other place (other macro, settings page or content page) and back to the macro - everything is gone and the template is just the empty template again! Really annoying!


Casper Schmidt 28 Nov 2014, 22:19:18

Okay weird, recycling the app-pool and logging in again some old version of the macroscript is shown. After updating it to the right script saving it now works correctly, Yay!


Alex 18 Dec 2014, 13:58:03

I have been using using Visual Studio with IIS Express and SQL CE and let me let you this is absolutely the most flawed setup I have ever came across. I am constantly losing data, with things disappearing in the back office but still remaining visible in the front end. I installed this as the default option with Umbraco 7.2 and have had nothing but issues. I would rather wait for a 5 min install rather losing countless hours of work on a daily basis and having to spend ages trying to fix things. Do people at Umbraco not test things as SQL CE is a flawed bit of technology. I HATE SQL CE get rid of it.


Alan Mac Kenna 18 Dec 2014, 14:28:08

Hi Alex. The simple answer is don't use IIS Express. Hook up your install to full-blown IIS. SQL CE works much better with that. I have no problems with dataloss in that setup.


Casper Schmidt 18 Dec 2014, 16:04:50

Alan As noted in my comments the issue appears with full-blown IIS as well.


Alan Mac Kenna 18 Dec 2014, 16:53:52

There seems to have been a comment removed from the thread Casper - Alex was saying earlier that he had an IIS Express and CE setup that was causing him data-loss issues. Mine wasn't related to your comments.


Casper Schmidt 18 Dec 2014, 17:16:49

I know, but your comment suggests that the problem disappear when switching from express to full-blown, which my comments (and experience) contradicts.


Alan Mac Kenna 18 Dec 2014, 20:02:03

Yep absolutely, I was just suggesting that he try switch to IIS from Express (if he hadn't already) as I experienced data-loss with Express and CE in the past that were resolved by this. His problem may fit into your context better but the comment didn't elaborate much on specifics of the issue. Anyway, hopefully he has resolved it now and you too. Cheers.


Funka! 18 Dec 2014, 20:31:06

This issue should be renamed to "Losing data when using SQL CE" because, as noted, it can happen in either full IIS or IIS Express.


Nicholas Westby 28 Jan 2016, 05:10:50

This is still happening. It happened to me about 5 times in a row in Umbraco 7.3.5. You can see my latest commits in this repo where I mention that this is happening (the SQL CE DB is in the repo, credentials are admin/adminadmin): https://github.com/Nicholas-Westby/ditto-resolver-invoke-error/commits/master

Also, somebody else has been seeing this on Umbraco 7.4: https://our.umbraco.org/forum/umbraco-7/using-umbraco-7/74238-changes-not-being-saved-in-version-74

@Funka! Your idea that it may be an unclosed transaction makes a lot of sense to me: https://our.umbraco.org/forum/umbraco-7/using-umbraco-7/49713-Losing-data-on-server-restart#comment-196648

SQL Server CE doesn't get a lot of love, so it makes sense to me that there could be a longstanding bug in which Umbraco does not close a transaction.

By the way, I'm running directly from Visual Studio 2013 with the latest updates.


Sebastiaan Janssen 24 Mar 2016, 12:23:23

Just found this linked to a forum post. We're quite sure that this has been fixed by an update from Microsoft as of 7.3.8 - see: http://issues.umbraco.org/issue/U4-7996


Priority: Normal

Type: Bug

State: Duplicate

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.4.0, 7.3.5

Due in version:

Sprint:

Story Points:

Cycle: