Massive unit test vs MySQL

[Richard Wesley]

I don't know if this is really a Python question of a MySQL question,
but i am hopen that there is enough overlap that someone can help me ;-)

I have a unit test suite for our server that loads a clean database
image for many of the tests. I use

p = os.popen('mysql -u uid -ppassword mydatabase', 'w')
p.write(sql_commands)

to load the database.

While the tests are running, I find that mysql has a growing number of
"Sleeping" threads in the 'show processlist' table. Towards the end of
the test suite, the database loads start to fail with a MySQL error of
"ERROR 1040: Too many connections".

So my question would be, is there any way to shut down the child process
so that MySQL notices and cleans up after itself before it runs out of
threads?

TIA,

 

[Stephan Schaumann]

I don't know if this is really a Python question of a MySQL question,
but i am hopen that there is enough overlap that someone can help me ;-)

I have a unit test suite for our server that loads a clean database
image for many of the tests. I use

p = os.popen('mysql -u uid -ppassword mydatabase', 'w')
p.write(sql_commands)

Do you p.close() when you are finished? Otherwise the connection opend by
mysql will stay open.

 

[Neil Padgen]

I have a unit test suite for our server that loads a clean database
image for many of the tests. I use

p = os.popen('mysql -u uid -ppassword mydatabase', 'w')
p.write(sql_commands)

Your pipe is still open at this point, and therefore you have a
connection to the database open.

So my question would be, is there any way to shut down the child
process so that MySQL notices and cleans up after itself before it
runs out of threads?

p.close()

-- Neil

 

[Richard Wesley]

Your pipe is still open at this point, and therefore you have a
connection to the database open.


p.close()

Yeah, I tried this, but it had no effect.

The odd thing is that the process list cleans up _immediately_ when the
script terminates. Maybe this is some sort of gc problem?

 

[Neil Padgen]

Yeah, I tried this, but it had no effect.

The odd thing is that the process list cleans up _immediately_ when
the
script terminates. Maybe this is some sort of gc problem?

What is in sql_commands? Maybe there is a semicolon missing from the
end, which might be causing mysql to wait for input.

-- Neil

--

 

[Richard Wesley]

What is in sql_commands? Maybe there is a semicolon missing from the
end, which might be causing mysql to wait for input.

I doubt it - it is generated by mysqldump. And there is no problem from
the command line.

--

- rmgw

<http://www.trustedmedianetworks.com/>

----------------------------------------------------------------------------
Richard Wesley Trusted Media Networks, Inc.

"I found it at the bottom of a locked filing cabinet, in a disused
lavatory with a sign on the door saying 'Beware of the leopard.'"
- Douglas Adams, _The Hitchhiker's Guide to the Galaxy_

 

[Richard Wesley]

As you aren't using the Python MySQL module and just piping commands to
an OS process then its likely to be a MySQL problem.

However, its hard to figure out what you're doing wrong because you
don't tell us what you are doing. What, for instance, are the contents
of 'sql_commands'?

They are the output of a mysqldump command that was read in from a file.

Generally, if a database complains about too many connections it is
because you are explicitly (and repeatedly) connecting to it and then
not releasing those connections when you are finished with them.

Yes, that is the problem, but it is not clear how to release the
connections. They get released immediately when the script terminates.
I am wondering of it is a gc problem.

Then again, if you are just loading test data into a clean database I'd
suggest using some of the MySQL utilities to back up and restore and not
writing your own code. Have a look at mysqldump
(http://www.mysql.com/doc/en/mysqldump.html)

That is what I am doing.

 

[Neil Padgen]

I doubt it - it is generated by mysqldump. And there is no problem
from the command line.

I use exactly the same approach to set up my unit tests, but I have a
different way to get the mysqldump data into the database:

os.system('mysql database_name < dumpfile')

-- Neil

--

 

[Skip Montanaro]

Richard> Yes, that is the problem, but it is not clear how to release
Richard> the connections. They get released immediately when the script
Richard> terminates. I am wondering of it is a gc problem.

How about you explicitly tack an explicit

exit;

onto the end of the input?

% mysql -u concerts -h localhost -p concerts
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 440197 to server version: 3.23.41-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit;
Bye

Skip

 

[Richard Wesley]

I use exactly the same approach to set up my unit tests, but I have a
different way to get the mysqldump data into the database:

os.system('mysql database_name < dumpfile')

Sadly, this did not work, nor did garbage collection (gc.collect()).

For some reason, mysql is holding onto a whole lot of connections from
my script until the script terminates.

 

[Richard Wesley]

Richard> Yes, that is the problem, but it is not clear how to release
Richard> the connections. They get released immediately when the script
Richard> terminates. I am wondering of it is a gc problem.

How about you explicitly tack an explicit

exit;

Yes, no effect. And gc.collect() has no effect either.

--

- rmgw

<http://www.trustedmedianetworks.com/>

----------------------------------------------------------------------------
Richard Wesley Trusted Media Networks, Inc.

"'You don't know how to manage Looking-glass cakes,' the Unicorn remarked.
'Hand it round first, and cut it afterwards.'"
- Lewis Carroll, _Through The Looking-Glass And What Alice Found There_

 

[Richard Wesley]

Sadly, this did not work, nor did garbage collection (gc.collect()).

For some reason, mysql is holding onto a whole lot of connections from
my script until the script terminates.

OK, I'm an idiot. It had nothing to do with the reload script. The
connections were from the TestCase subclass I have that instantiates a
connection management object. It appears that the unittest module does
not dispose of the objects it instantiates until the end of the run, so
you need to REALLY clean up in the tearDown method. I discovered this
by making a persistent connection for the reload operation and noticing
that the number of connections still increased.

Thanks to all who responded.

--
Best regards,

Richard Wesley
Co-President, Electric Fish, Inc.
<http://www.electricfish.com/>
(v) +1-206-493-1690x210
(f) +1-206-493-1697
(h) +1-206-632-4536
(m) +1-206-409-4536

 

[Neil Padgen]

Richard> OK, I'm an idiot. It had nothing to do with the reload
Richard> script. The connections were from the TestCase subclass
Richard> I have that instantiates a connection management object.
Richard> It appears that the unittest module does not dispose of
Richard> the objects it instantiates until the end of the run, so
Richard> you need to REALLY clean up in the tearDown method. I
Richard> discovered this by making a persistent connection for the
Richard> reload operation and noticing that the number of
Richard> connections still increased.

Doh! I should have thought of this, as I've had exactly the same
problem in the past.

Thanks for posting your solution.

-- Neil