MySQL Connector/Python

MySQL Disconnect on query

Asked by Warwick Prince

I have a very confusing issue which I have finally isolated to a minimum amount of code.

The environment is a MySQL community server 5.1.latest running on Win2000 server (Also tried 2003 but the 2000 is our standard workhorse tester)
Python 2.6.4
SQLAlchemy 0.6
Client code running under XP (The destination customer requires this)
DB is innodb

All works fine for virtually any standard query. I can repeat over and over with no issues. If I start a transaction (in SQLAlchemy, I create a "Session()" instance and then do a session.query.count() it still all works. I then issue a real query and get results as expected. This can be repeated over and over OK.. If I start another process (i.e. a completely new multiprocess.process) on the same XP client and run the exact same code again, I get the following error;

InterfaceError: (InterfaceError) 2055: Lost connection to MySQL server at '192.168.50.2:3306', system error: 10054 u'SELECT products.`Group` AS `products_Group`, products.`Product` AS `products_Product`, products.`Description` AS `products_Description`, products.`Description2` AS `products_Description2`, products.`ShowDescOnInv` AS ..... rest of query

But ONLY on the query that gets a real result - the .count query will still work, and any NON transaction based queries still work!! Basically, it breaks the transaction by somehow issuing a transaction that MySQL considers "bad" and is waiting for it to be rolled back.

Not only that, but I then get the same error immediately in the original process! MySQL is then "broken" as it returns a error for any new connections that request a transaction that You can not connect until the invalid transaction is rolled back.. Firstly, NO updates have been made to the DB at all, only very simple queries, and secondly, given the connection is now down on both clients, how would one roll it back anyway!?

This does not appear to be an SQLAlchemy issue, as it is performing at it's most basic level. i.e. Create a session, perform a basic one table query, fail. This ONLY fails if another connection is created on the same XP even though it's in a completely different context (process).

So - the question is - "Do you have any known issues with connections (TCP) under Windows (Specifically XP)? Have you seen this type of "concurrency" issue with connections from the same client to the server causing a massive failure on the MySQL server?

Help! :-) I've tried everything. (Except that last one that fixes it obviously)

Cheers
Warwick

Question information

Language:
English Edit question
Status:
Open
For:
MySQL Connector/Python Edit question
Assignee:
Geert JM Vanderkelen Edit question
Last query:
Last reply:
Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#1

Hello Warwick,

Couple of things:
1) What is the version of MySQL Connector/Python? What version of MySQL _exactly_?
2) How 'big' is the result set you are trying to get? Any 'FOR UPDATE' in that SELECT?
3) What do you mean with 'server failure'? I see a client error in your question.
4) I would like to see the "error for any new connections that request a transaction".

Cheers,

Geert

Revision history for this message
Warwick Prince (warwickp) said : 		#2

Hi Geert

Answers that I have now, more to follow; It's late in Australia, so I'll get more in the morning. :-)

1) MySQL Connector version 0.1.5.devel MySQL version: 5.1 Community
2) Result is one row. Query is on a table of only 1500 rows, but the result has a limit of 1 - No updates what-so-ever. Only SELECT. Query is on a table with 130 or so columns, all with a "ColumnName AS tableName_CoumnName" in the construction, so the query length is reasonably large'ish. (Although nothing really in the scheme of things). Don't forget, the query and everything about it works, until I do the same thing from another process. (or Thread - tried both)
3) Any attempt to start a new transaction on the server gets a "Connection Failed - Can not connect until bad transaction rolled back" (Or words to that effect - I will confirm exact error message) One must restart MySQL to clear this error, as no client can send a rollback! This includes a transaction from a third party MySQL database management software that does not use the connector. The MySQL server gets into the state of not being able to have any more transactions created until this supposed "bad" one gets rolled back.
4) I will supply the actual errors tomorrow after a re-run on the situation.

Cheers
Warwick

On 10/10/2010, at 9:00 PM, Geert JM Vanderkelen wrote:

> Your question #128616 on MySQL Connector/Python changed:
> https://answers.launchpad.net/myconnpy/+question/128616
>
> Status: Open => Needs information
>
> Geert JM Vanderkelen requested for more information:
> Hello Warwick,
>
> Couple of things:
> 1) What is the version of MySQL Connector/Python? What version of MySQL _exactly_?
> 2) How 'big' is the result set you are trying to get? Any 'FOR UPDATE' in that SELECT?
> 3) What do you mean with 'server failure'? I see a client error in your question.
> 4) I would like to see the "error for any new connections that request a transaction".
>
> Cheers,
>
> Geert
>
> --
> To answer this request for more information, you can either reply to
> this email or enter your reply at the following page:
> https://answers.launchpad.net/myconnpy/+question/128616
>
> You received this question notification because you are a direct
> subscriber of the question.

Revision history for this message
Warwick Prince (warwickp) said : 		#3

Hi Geert

It appears that the issue is isolated to XP! I had a feeling that this may be the case, as I had been using the connector with SQLAlchemy with great success on Windows 2003 server (with a different project) and realised that I was doing many of the same things in that project and they were working fine.

Today, I moved the failing project from XP and on to the Win2003 server and it all just worked! I have spent DAYS on this... :-( Anyway, it would be still very good to know why it does not work on XP, and possibly just knowing that fact will help in the diagnoses. I suspect some socket failure with the raw connection to MySQL.. Some shared resource conflict.. Something like that..

Cheers
Warwick

Warwick Prince
Managing Director
mobile: +61 411 026 992
skype: warwickprince

phone: +61 7 3102 3730
fax: +61 7 3319 6734
web: www.mushroomsys.com

On 10/10/2010, at 9:00 PM, Geert JM Vanderkelen wrote:

> Your question #128616 on MySQL Connector/Python changed:
> https://answers.launchpad.net/myconnpy/+question/128616
>
> Status: Open => Needs information
>
> Geert JM Vanderkelen requested for more information:
> Hello Warwick,
>
> Couple of things:
> 1) What is the version of MySQL Connector/Python? What version of MySQL _exactly_?
> 2) How 'big' is the result set you are trying to get? Any 'FOR UPDATE' in that SELECT?
> 3) What do you mean with 'server failure'? I see a client error in your question.
> 4) I would like to see the "error for any new connections that request a transaction".
>
> Cheers,
>
> Geert
>
> --
> To answer this request for more information, you can either reply to
> this email or enter your reply at the following page:
> https://answers.launchpad.net/myconnpy/+question/128616
>
> You received this question notification because you are a direct
> subscriber of the question.

Can you help with this problem?

Provide an answer of your own, or ask Warwick Prince for more information if necessary.

To post a message you must log in.