<div dir="ltr">Nova is having a related issue, where we are hitting issues in our unit tests.<div><br></div><div><a href="https://bugs.launchpad.net/nova/+bug/1328997">https://bugs.launchpad.net/nova/+bug/1328997</a><br></div>
<div><br></div><div><span style="color:rgb(0,0,0);font-family:monospace;font-size:medium;white-space:pre-wrap">Stderr: 'ERROR: database "openstack_citest" is being accessed by other users\\nDETAIL: There are 1 other session(s) using the database.\\n\''</span><br>
</div><div><span style="color:rgb(0,0,0);font-family:monospace;font-size:medium;white-space:pre-wrap"><br></span></div><div><span style><font color="#000000" face="monospace" size="3"><span style="white-space:pre-wrap"><a href="http://logs.openstack.org/76/98376/1/gate/gate-nova-python27/d2a0593/console.html">http://logs.openstack.org/76/98376/1/gate/gate-nova-python27/d2a0593/console.html</a></span></font><br>
</span></div><div><span style="color:rgb(0,0,0);font-family:monospace;font-size:medium;white-space:pre-wrap"><br></span></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jun 9, 2014 at 4:18 PM, Devananda van der Veen <span dir="ltr"><<a href="mailto:devananda.vdv@gmail.com" target="_blank">devananda.vdv@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Mike,<br>
<br>
For the "typical" case, your proposal sounds reasonable to me. That<br>
should protect against cross-session locking while still getting the<br>
benefits of testing DML without committing to disk.<br>
<br>
The issue I was originally raising is, of course, the "special" case<br>
-- testing of migrations -- which, I think, could be solved in much<br>
the same way. Given N test runners, create N empty schemata, hand each<br>
migration-test-runner a schema from that pool. When that test runner<br>
is done, drop and recreate that schema.<br>
<br>
AIUI, Nodepool is already doing something similar here:<br>
<a href="https://git.openstack.org/cgit/openstack-infra/nodepool/tree/nodepool/tests/__init__.py#n71" target="_blank">https://git.openstack.org/cgit/openstack-infra/nodepool/tree/nodepool/tests/__init__.py#n71</a><br>
<br>
Regards,<br>
Devananda<br>
<div class="HOEnZb"><div class="h5"><br>
<br>
<br>
On Mon, Jun 9, 2014 at 12:58 PM, Mike Bayer <<a href="mailto:mbayer@redhat.com">mbayer@redhat.com</a>> wrote:<br>
><br>
> On Jun 9, 2014, at 1:08 PM, Mike Bayer <<a href="mailto:mbayer@redhat.com">mbayer@redhat.com</a>> wrote:<br>
><br>
>><br>
>> On Jun 9, 2014, at 12:50 PM, Devananda van der Veen <<a href="mailto:devananda.vdv@gmail.com">devananda.vdv@gmail.com</a>> wrote:<br>
>><br>
>>> There may be some problems with MySQL when testing parallel writes in<br>
>>> different non-committing transactions, even in READ COMMITTED mode,<br>
>>> due to InnoDB locking, if the queries use non-unique secondary indexes<br>
>>> for UPDATE or SELECT..FOR UPDATE queries. This is done by the<br>
>>> "with_lockmode('update')" SQLAlchemy phrase, and is used in ~10 places<br>
>>> in Nova. So I would not recommend this approach, even though, in<br>
>>> principle, I agree it would be a much more efficient way of testing<br>
>>> database reads/writes.<br>
>>><br>
>>> More details here:<br>
>>> <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html" target="_blank">http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html</a> and<br>
>>> <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html" target="_blank">http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html</a><br>
>><br>
>> OK, but just to clarify my understanding, what is the approach to testing writes in parallel right now, are we doing CREATE DATABASE for two entirely distinct databases with some kind of generated name for each one? Otherwise, if the parallel tests are against the same database, this issue exists regardless (unless autocommit mode is used, is FOR UPDATE accepted under those conditions?)<br>
><br>
> Took a look and this seems to be the case, from oslo.db:<br>
><br>
> def create_database(engine):<br>
> """Provide temporary user and database for each particular test."""<br>
> driver = <a href="http://engine.name" target="_blank">engine.name</a><br>
><br>
> auth = {<br>
> 'database': ''.join(random.choice(string.ascii_lowercase)<br>
> for i in moves.range(10)),<br>
> # ...<br>
><br>
> sqls = [<br>
> "drop database if exists %(database)s;",<br>
> "create database %(database)s;"<br>
> ]<br>
><br>
> Just thinking out loud here, I’ll move these ideas to a new wiki page after this post. My idea now is that OK, we provide ad-hoc databases for tests, but look into the idea that we create N ad-hoc databases, corresponding to parallel test runs - e.g. if we are running five tests concurrently, we make five databases. Tests that use a database will be dished out among this pool of available schemas. In the *typical* case (which means not the case that we’re testing actual migrations, that’s a special case) we build up the schema on each database via migrations or even create_all() just once, run tests within rolled-back transactions one-per-database, then the DBs are torn down when the suite is finished.<br>
><br>
> Sorry for the thread hijack.<br>
><br>
><br>
><br>
> _______________________________________________<br>
> OpenStack-dev mailing list<br>
> <a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
> <a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
</div></div></blockquote></div><br></div>