VSTS Test Runner and the Case Of The Missing Temp Directory

[blakeyrat]

Ok so I'm developing a program where all the development tools live in VSTS, including the repository and CI stuffs.

When I do a commit, I'd like VSTS to run a bunch of unit tests (or maybe integration tests, don't be pedantic). The problem is: most of them rely on having access to the DB schema via. Entity Framework.

To solve this, I put the detached database in the solution itself and wrote a quick and dirty little function in the test runner to copy it somewhere and create a connectionString that tells EntityFramework where to find it:

private static void SetupDB()
		{
			// Copy database to temp folder
			var dbName = "XXX.mdf";

			var solutionPath = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName;
			var dbSourcePath = solutionPath + "\\TestDB\\" + dbName;
			var dbDestinationFolder = Path.GetTempPath() + Guid.NewGuid();
			Directory.CreateDirectory(dbDestinationFolder);
			var dbDestinationPath = dbDestinationFolder + "\\" + dbName;

			File.Copy(dbSourcePath, dbDestinationPath);

			// Create a connection string to the new DB
			var dbConnectionString = "Data Source=(localdb)\\MSSQLLocalDB; Integrated Security=true; Initial Catalog=XXX; AttachDbFileName=" + dbDestinationPath + ";";

			EntityConnectionStringBuilder entityStringBuilder = new EntityConnectionStringBuilder();
			entityStringBuilder.Provider = "System.Data.SqlClient";
			entityStringBuilder.ProviderConnectionString = dbConnectionString;
			entityStringBuilder.Metadata = "res://*/XXXData.csdl|res://*/XXXData.ssdl|res://*/XXXData.msl";

			connectionString = entityStringBuilder.ConnectionString;
		}

Now this worked locally. To my surprise, it also worked in the VSTS build agent. Woot problem solved!

Nope.

Something happened starting monday of this week and this solution no longer works... Friday I had a build with 100% passing tests, but every day since then the pass rate has been much lower, because all tests that rely on DB access are failing. Shit.

The error is:

Test method XXXTest.XXX.XXX threw exception: 
System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Could not open new database 'XXX'. CREATE DATABASE is aborted.
Cannot attach the file 'd:\a\_temp\028c2374-d4f3-4d1c-ae6f-77a9e7281e12\XXX.mdf' as database 'XXX'.
File activation failure. The physical file name "d:\a\_temp\028c2374-d4f3-4d1c-ae6f-77a9e7281e12\XXX_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.

The code above didn't change. The test agent queue is set to "Hosted VS2017" and hasn't changed between Friday and Monday.

Now the interesting thing about that error is that it's not complaining about the database file's path (the .mdb), but about the log's file path (.ldf). It seems to be bitching that the temp path is read-only while the database file is being attached, but literally milliseconds before that happens the exact same test project just wrote a file to that exact path so I'm not sure how that's happening or why.

VSTS is kind of a black box, so I'm not sure where to go from here. (It works locally on 100% of the machines I've tested with zero preconfigurations required, other than having VS2017 installed, which these agents supposedly have.)

So questions:

1. What changed to make this test not work?
2. Is there a correct way to put integration tests that require mounting databases in VSTS that won't break even in a million years?
3. If so, what is it?

[Magus]

I won't be able to provide much help here, but: Microsoft can essentially update the agents whenever they want, however they want, unless you make your own agents to use instead. Thus, yes, things will randomly change. Usually it isn't a problem, but essentially I wouldn't trust file locations too much; if you can embed the file and use it based on paths from TestContext, I would trust that slightly more.

Another thing to look at is the build itself: it can run commands of various different types. If you can set up your database through that, it might be more stable. I imagine there must be a 'set up database' step available, but I could certainly be wrong about that.

[blakeyrat]

@magus said in VSTS Test Runner and the Case Of The Missing Temp Directory:

Usually it isn't a problem, but essentially I wouldn't trust file locations too much; if you can embed the file and use it based on paths from TestContext, I would trust that slightly more.

The DB lives in SolutionFolder\TestDB\XXX.mdf

The reasons I make a copy into temp are:

1. So the tests always run on a "fresh" database without leftover data from the last test run
2. So the copied database file will be outside the purview of Git, and thus I won't be able to accidentally commit it to the repo by clicking wrong

On the VSTS server, point 1) doesn't apply since it always has a fresh copy anyway-- so I could copy the database and keep it in the solution folder and see if the SQL engine can attach from there maybe? But it'd be nice to solve both point 1) and 2) in one fell swoop like I'm doing now...

[blakeyrat]

Found this article which seems to provide some solid guidance on where to put files:

How to: Deploy Files for Tests

Problem is, while I can easily add the post-build task (and it won't pollute Git because /bin is ignored), how do I then retrieve that path for setting up the connection string? Still finding that path shouldn't be too tough, right.

[Magus]

@blakeyrat I still recommend doing whatever file operation you're doing with the VSTS build definition if possible, but I think you should be able to get something working with what you're looking at too.

[blakeyrat]

@blakeyrat Ok turned out that problem was easy to solve. The solution --

In the test project's build directions, add this post-build step:

xcopy /Y /S "$(SolutionDir)TestDB\XXX.mdf" "$(TargetDir)"

Then change your test runner init to look like this:

private static void SetupDB()
		{
			// Copy database to temp folder
			var dbName = "XXX.mdf";

			var solutionPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
			var dbDestinationPath = Path.Combine(solutionPath, dbName);

			// Create a connection string to the new DB
			var dbConnectionString = "Data Source=(localdb)\\MSSQLLocalDB; Integrated Security=true; Initial Catalog=XXX; AttachDbFileName=" + dbDestinationPath + ";";

			EntityConnectionStringBuilder entityStringBuilder = new EntityConnectionStringBuilder();
			entityStringBuilder.Provider = "System.Data.SqlClient";
			entityStringBuilder.ProviderConnectionString = dbConnectionString;
			entityStringBuilder.Metadata = "res://*/XXXData.csdl|res://*/XXXData.ssdl|res://*/XXXData.msl";

			connectionString = entityStringBuilder.ConnectionString;
		}

I've verified that every build copies a "fresh" database and doesn't reuse the old one in bin/Debug.

Now to see if it works on VSTS...

EDIT: Nope that doesn't work locally now, because while the build copies a new .mdb file, it leaves behind the old .ldf file... when you try to attach SQL bitches that the log file doesn't match the database file of the same name.

So now I need to find a command line to delete the log file if present, sigh.

[Unperverted Vixen]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

So now I need to find a command line to delete the log file if present, sigh.

get-childitem *.ldf | remove-item

[blakeyrat]

Ok, so I added this:

del /Q "$(TargetDir)XXX_log.ldf"

Now the problem (there's always a new problem, isn't there?) is that after running the tests, VS doesn't detach the database file, so the .mdf sticks around even if you do Clean (because the file's in-use.)

However, the tests do work because they're written so that they run even if the database isn't blank, and also SQL Server is (apparently?) ok with another process deleting its log file for an in-use database, which is weird.

Now deciding if this counts as "good enough"...

[blakeyrat]

I got it all set how I want it, push the changes to VSTS and:

The log cannot be rebuilt when the primary file is read-only.

FUCK!

Same exact error, the only difference is the file path. Ok, so what the fuck is going on here?

[Tsaukpaetra]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

Now the problem (there's always a new problem, isn't there?) is that after running the tests, VS doesn't detach the database file, so the .mdf sticks around even if you do Clean (because the file's in-use.)

I was going to suggest this in response to the original problem. Thanks for validating my perceptions.

[Tsaukpaetra]

@tsaukpaetra said in VSTS Test Runner and the Case Of The Missing Temp Directory:

the original problem.

So in addition to adding a step to detach the database, other internet posts have indicated a possible permissions issue; I'm assuming the use VSTS uses isn't mysteriously losing rights to write that file?

[blakeyrat]

@tsaukpaetra I think they made a change to the cloud build servers which had the effect of removing write permissions from their copies of SQL Server. I can't think of any other explanation.

[blakeyrat]

This thing is still a huge thorn in my side. Bumping to see if anybody new reads it and has a suggestion?

[TheCPUWizard]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

This thing is still a huge thorn in my side. Bumping to see if anybody new reads it and has a suggestion?

I would recommend not trying to clone a DB [.mdf] especially if an .ldf is (or has been) present... Many alternatives, some of which are:

1. REestore to the $(targetDir) from .BAK
2. Create in $(TargetDir) from DACPAC

Also, debugging such issues is a lot easier on a local agent (you get one free local pipeline)...

Finally, for EF projects, and CI type builds, I would recommend looking at any one of the methods of using an I'm-memory database provider. Not only does it avoid the issue, it is much faster, and makes it easier to test conditions that are very difficult with a "Real" database.

[blakeyrat]

@thecpuwizard said in VSTS Test Runner and the Case Of The Missing Temp Directory:

Create in $(TargetDir) from DACPAC

Can you elaborate on that? We've now created a database project for this, which does generate a DACPAC file on build.

[TheCPUWizard]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

@thecpuwizard said in VSTS Test Runner and the Case Of The Missing Temp Directory:

Create in $(TargetDir) from DACPAC

Can you elaborate on that? We've now created a database project for this, which does generate a DACPAC file on build.

Add a PowerShell Task.... The PS script might look like (I lifted this from a forum, no warrantee; but it should get you started)

$PublishPath = "Path for the log"
$dacpac = "dbname.dacpac"
$publishProfile = "dbname.xml" # Publish profile xml for Publishing the database project

# Database Publish
$publish = ".\sqlpackage.exe /Action:Publish /Sourcefile:$dacpac /pr:'$publishProfile'"

Invoke-Expression $publish | Out-File $PublishPath

[TheCPUWizard]

General info on SqlPackage: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

[blakeyrat]

@thecpuwizard Awesome; that might be exactly what I need to fix this.

Still pisses me off that VSTS broke my original solution, though. Which was fine!!!

[TheCPUWizard]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

@thecpuwizard Awesome; that might be exactly what I need to fix this.

Still pisses me off that VSTS broke my original solution, though. Which was fine!!!

It [VSTS] may have - the risk is a side effect of being in an environment that continually updates. I use VSTS heavily, 99.5% of the time I use a Private Build Agent for multiple reasons [control being high, but not the very top, of the list]

On the other hand, it might not have (based only on the limited information)...

a) Have there been any updates [commits/check-ins) to the MDF file since you started having the problem??
b) VSTS (and even TFS) have documented information about permissions. In older releases (and by older I mean a few weeks/months ago), some of the items were not enforced, allowing people to easily (and without warning) do "unsupported things". As these loopholes closed, things did break for many ---- it has been a heated discussion of who had the responsibility...

[blakeyrat]

@thecpuwizard I'm sure that's what happened to me.

Anyway I'll dig into this DACPAC restore thing, because it also needs to run that task on the local developer's machine when they run unit tests so just adding it to the VSTS CI instructions isn't enough.

[TheCPUWizard]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

@thecpuwizard I'm sure that's what happened to me.

Anyway I'll dig into this DACPAC restore thing, because it also needs to run that task on the local developer's machine when they run unit tests so just adding it to the VSTS CI instructions isn't enough.

Let me know if there is anything I can do to help [feel free to chat, I am not diligent about monitoring threads here]....

If you can, I would strongly suggest looking at the ability to eliminate the dependency on an actual persisten DB...

My current favorite (It has been for over a year, but things evolve) is: https://www.nuget.org/packages/Effort.EF6/

[blakeyrat]

@thecpuwizard said in VSTS Test Runner and the Case Of The Missing Temp Directory:

My current favorite (It has been for over a year, but things evolve) is: https://www.nuget.org/packages/Effort.EF6/

Effort is basicly

Bzzt.

I'm not installing a dependency from someone who can't spell the word "basically".

[TheCPUWizard]

@blakeyrat said in VSTS Test Runner and the Case Of The Missing Temp Directory:

I'm not installing a dependency from someone who can't spell the word "basically".

GRab the source code, then you can do a direct review [that is what I do] and hence no dependency as one can be completely independent.....

[For binary only types I agree with you about dependencies, even when they do not proof read.]

[ben_lubar]

@thecpuwizard fixed your code for you. :D