Tests and temp tables

[Larry Martell]

I have a django app that uses a temp table. In the real world this is
no issue, as each invocation of the app runs in its own MySQL session
so there cannot be any conflict with the temp tables. But in my tests
there are multiple requests sent, and apparently they are all in the
same session, as on the second request I get an error because the temp
table already exists. I tried logging out between requests, and I
tried creating a new Client instance for each request, but I still got
the error. Then I tried deleting the Client object, but I got Client
object has no attribute __del__.

What I can do so that each request in a test has its own MySQL session?

[Jani Tiainen]

Instead of Django standard TestCase (which internally wraps all in single transaction and makes transactions as a no-op), you should use TransactionalTestCase.

https://docs.djangoproject.com/en/1.7/topics/testing/tools/#transactiontestcase


--
Jani Tiainen

[Larry Martell]

Thanks for the reply Jani. We're using 1.5 and I don't think this is
available in that version. We'll probably be moving to 1.6 soon
though, and it is there. But I'm not sure how this will be useful to
me. The docs say "A TransactionTestCase resets the database after the
test runs by truncating all tables." My test code is something like
this:

# load test data
# login
response = self.client.get('...')
self.assertEqual(response.status_code, 200)
# collect results

response = self.client.get('...')
self.assertEqual(response.status_code, 200)
# collect results

and so on. I need the temp table to get dropped between each get call.
The best way to do this is if each is in its own MySQL session. Having
the table truncated doesn't really accomplish this. I think I will
have to modify the code to drop the temp table after it's used. I hate
to muck with working production code to accommodate a test, but I also
don't want the test branch to have a different code base from the
production branch. I'll probably go with the former option.

[Carl Meyer]

Hi Larry,

On 02/05/2015 06:57 AM, Larry Martell wrote:
> On Thu, Feb 5, 2015 at 7:29 AM, Jani Tiainen <red...@gmail.com> wrote:
>> On Tue, 3 Feb 2015 19:38:31 -0500
>> Larry Martell <larry....@gmail.com> wrote:
>>
>>> I have a django app that uses a temp table. In the real world this is
>>> no issue, as each invocation of the app runs in its own MySQL session
>>> so there cannot be any conflict with the temp tables. But in my tests
>>> there are multiple requests sent, and apparently they are all in the
>>> same session, as on the second request I get an error because the temp
>>> table already exists. I tried logging out between requests, and I
>>> tried creating a new Client instance for each request, but I still got
>>> the error. Then I tried deleting the Client object, but I got Client
>>> object has no attribute __del__.
>>>
>>> What I can do so that each request in a test has its own MySQL session?
>>
>>
>> Instead of Django standard TestCase (which internally wraps all in single transaction and makes transactions as a no-op), you should use TransactionalTestCase.
>>
>> https://docs.djangoproject.com/en/1.7/topics/testing/tools/#transactiontestcase
>
> Thanks for the reply Jani. We're using 1.5 and I don't think this is
> available in that version. We'll probably be moving to 1.6 soon
> though, and it is there.

TransactionTestCase has been around for quite a long time (since 1.1,
IIRC). It's definitely in 1.5.

> But I'm not sure how this will be useful to
> me. The docs say "A TransactionTestCase resets the database after the
> test runs by truncating all tables." My test code is something like
> this:
>
> # load test data
> # login
> response = self.client.get('...')
> self.assertEqual(response.status_code, 200)
> # collect results
>
> response = self.client.get('...')
> self.assertEqual(response.status_code, 200)
> # collect results
>
> and so on. I need the temp table to get dropped between each get call.
> The best way to do this is if each is in its own MySQL session. Having
> the table truncated doesn't really accomplish this. I think I will
> have to modify the code to drop the temp table after it's used. I hate
> to muck with working production code to accommodate a test, but I also
> don't want the test branch to have a different code base from the
> production branch. I'll probably go with the former option.

I agree, I don't think TransactionTestCase will help with this situation.

I find the production design a bit questionable, but taking it as a
given that it meets your needs adequately and you don't want to change
it, you could also address this problem by simply dropping the temp
table in a tearDown() method, no?

Carl

[Larry Martell]

On Thu, Feb 5, 2015 at 10:53 AM, Carl Meyer <ca...@oddbird.net> wrote:
> Hi Larry,
>
> On 02/05/2015 06:57 AM, Larry Martell wrote:
>> On Thu, Feb 5, 2015 at 7:29 AM, Jani Tiainen <red...@gmail.com> wrote:
>>> On Tue, 3 Feb 2015 19:38:31 -0500
>>> Larry Martell <larry....@gmail.com> wrote:
>>>
>>>> I have a django app that uses a temp table. In the real world this is
>>>> no issue, as each invocation of the app runs in its own MySQL session
>>>> so there cannot be any conflict with the temp tables. But in my tests
>>>> there are multiple requests sent, and apparently they are all in the
>>>> same session, as on the second request I get an error because the temp
>>>> table already exists. I tried logging out between requests, and I
>>>> tried creating a new Client instance for each request, but I still got
>>>> the error. Then I tried deleting the Client object, but I got Client
>>>> object has no attribute __del__.
>>>>
>>>> What I can do so that each request in a test has its own MySQL session?
>>>
>>>
>>> Instead of Django standard TestCase (which internally wraps all in single transaction and makes transactions as a no-op), you should use TransactionalTestCase.
>>>
>>> https://docs.djangoproject.com/en/1.7/topics/testing/tools/#transactiontestcase
>>
>> Thanks for the reply Jani. We're using 1.5 and I don't think this is
>> available in that version. We'll probably be moving to 1.6 soon
>> though, and it is there.
>
> TransactionTestCase has been around for quite a long time (since 1.1,
> IIRC). It's definitely in 1.5.

I thought it was not in 1.5 because I went to
https://docs.djangoproject.com/en/1.5/topics/testing/tools/#transactiontestcase
and got a 404.

>> But I'm not sure how this will be useful to
>> me. The docs say "A TransactionTestCase resets the database after the
>> test runs by truncating all tables." My test code is something like
>> this:
>>
>> # load test data
>> # login
>> response = self.client.get('...')
>> self.assertEqual(response.status_code, 200)
>> # collect results
>>
>> response = self.client.get('...')
>> self.assertEqual(response.status_code, 200)
>> # collect results
>>
>> and so on. I need the temp table to get dropped between each get call.
>> The best way to do this is if each is in its own MySQL session. Having
>> the table truncated doesn't really accomplish this. I think I will
>> have to modify the code to drop the temp table after it's used. I hate
>> to muck with working production code to accommodate a test, but I also
>> don't want the test branch to have a different code base from the
>> production branch. I'll probably go with the former option.
>
> I agree, I don't think TransactionTestCase will help with this situation.
>
> I find the production design a bit questionable,

Why do you say that? I recently added the temp table for performance.
I have a query that was taking over an hour. When I broke it into 2
queries using a temp table it then ran in 10 seconds. If I've
introduced something bad in my app, I'd really like to know about
that.

> but taking it as a
> given that it meets your needs adequately and you don't want to change
> it, you could also address this problem by simply dropping the temp
> table in a tearDown() method, no?

A tearDown() method would be run after the test, right? I am sending
multiple requests within one test.

Thanks!
-larry

[Carl Meyer]

On 02/05/2015 11:20 AM, Larry Martell wrote:
> On Thu, Feb 5, 2015 at 10:53 AM, Carl Meyer <ca...@oddbird.net> wrote:
>> TransactionTestCase has been around for quite a long time (since 1.1,
>> IIRC). It's definitely in 1.5.
>
> I thought it was not in 1.5 because I went to
> https://docs.djangoproject.com/en/1.5/topics/testing/tools/#transactiontestcase
> and got a 404.

Ah, yeah; I think the docs just moved around.

>>> and so on. I need the temp table to get dropped between each get call.
>>> The best way to do this is if each is in its own MySQL session. Having
>>> the table truncated doesn't really accomplish this. I think I will
>>> have to modify the code to drop the temp table after it's used. I hate
>>> to muck with working production code to accommodate a test, but I also
>>> don't want the test branch to have a different code base from the
>>> production branch. I'll probably go with the former option.
>>
>> I agree, I don't think TransactionTestCase will help with this situation.
>>
>> I find the production design a bit questionable,
>
> Why do you say that? I recently added the temp table for performance.
> I have a query that was taking over an hour. When I broke it into 2
> queries using a temp table it then ran in 10 seconds. If I've
> introduced something bad in my app, I'd really like to know about
> that.

I don't know if it's bad or not, given your needs, but introducing the
constraint "every request must run in its own MySQL session which is
shut down after the request" would give me pause, since reusing
connections (using the built-in Django connection-reuse feature, which
isn't in 1.5 yet I don't think, or using an external connection pooler)
is often a quick and easy performance win. And because having every
request leave the connection in an unusable state makes testing
harder/slower, too. It just feels wrong :-)

I don't know exactly why restructuring the query made it faster. In
PostgreSQL I would usually use a CTE (WITH clause) to allow the same
type of query decomposition without needing an actual temp table; not
sure if MySQL supports those. (I recently reworked an ORM query using
joins to raw SQL with CTEs and saw a similar many-orders-of-magnitude
performance improvement, because the version with joins was internally
generating a result-set that grew with the square of the size of a
particular table.) But if the temp table is the only approach that
works, I would probably prefer to have my production code clean it up to
make session reuse possible. (Ideally, by encapsulating the creation and
destruction of the temp table in a database stored procedure, so it
becomes what it ought to be, a query implementation detail, not
something that application logic needs to be concerned about.)

>> but taking it as a
>> given that it meets your needs adequately and you don't want to change
>> it, you could also address this problem by simply dropping the temp
>> table in a tearDown() method, no?
>
> A tearDown() method would be run after the test, right? I am sending
> multiple requests within one test.

Can you restructure your tests to avoid that? The more focused a test
is, usually the better.

If not, then I think you just need a utility method to destroy the temp
table, which you either call explicitly from your tests after each
request, or perhaps build into a subclass of the test client so it's
automated. Although a bit ugly, I think that's still better than trying
to reconnect to the database for each test; that'll kill your
test-running speed. (But I'd still prefer fixing the problem at the source.)

Carl

[Larry Martell]

I changed the main code to drop the temp table. This made the tests
run, and also is much cleaner. Thanks for the impetus to do this.

> I don't know exactly why restructuring the query made it faster.

The main table being selected from is largish - 38 million rows with
282 columns - and it's joined with 6 other tables.

> In PostgreSQL I would usually use a CTE (WITH clause) to allow the same
> type of query decomposition without needing an actual temp table; not
> sure if MySQL supports those. (I recently reworked an ORM query using
> joins to raw SQL with CTEs and saw a similar many-orders-of-magnitude
> performance improvement, because the version with joins was internally
> generating a result-set that grew with the square of the size of a
> particular table.)

I wasn't familiar with CTEs - I just read up on them. I don't think
MySQL supports those.