VSTS Test Runner and the Case Of The Missing Temp Directory



  • 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?


  • 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.



  • @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...



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

    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.



  • @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 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.


  • And then the murders began.

    @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



  • 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"...



  • 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?


  • Impossible Mission Players - A

    @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.


  • Impossible Mission Players - A

    @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?



  • @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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.