MySQL Bugs: #36855: intermittent exception in table adapter

Bug #36855	intermittent exception in table adapter
Submitted:	21 May 2008 16:19	Modified:	17 Jul 2008 14:06
Reporter:	Ray Rizzuto	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.1.4.0	OS:	Windows (Server 2003 R2)
Assigned to:		CPU Architecture:	Any
Tags:	exception, invalid read closed, table adapter

Description:
I have a table adapter with the following query:

On startup, the application uses the table adapter to read an entire table into memory.  I have 9 instances of the app. starting daily at the same time.  Occasionally one or two of the apps will take the following exception:

System.TypeInitializationException: The type initializer for 'SusexBlotter.SymbolToOpra' threw an exception. ---> MySql.Data.MySqlClient.MySqlException: Invalid attempt to Read when reader is closed.
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at MySql.Data.MySqlClient.Driver.LoadCharacterSets()
   at MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection)
   at MySql.Data.MySqlClient.NativeDriver.Configure(MySqlConnection conn)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SusexBlotter.insadminDataSetTableAdapters.ins_flat_optionsTableAdapter.GetData()
   at SusexBlotter.SymbolToOpra..cctor()
   --- End of inner exception stack trace ---
   at SusexBlotter.SymbolToOpra.get_Instance()
   at SusexBlotter.Program.Main(String[] args)
 Inner Exception: MySql.Data.MySqlClient.MySqlException: Invalid attempt to Read when reader is closed.
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at MySql.Data.MySqlClient.Driver.LoadCharacterSets()
   at MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection)
   at MySql.Data.MySqlClient.NativeDriver.Configure(MySqlConnection conn)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SusexBlotter.insadminDataSetTableAdapters.ins_flat_optionsTableAdapter.GetData()
   at SusexBlotter.SymbolToOpra..cctor()
   at SusexBlotter.SymbolToOpra.get_Instance()
   at SusexBlotter.Program.Main(String[] args)

How to repeat:
This occurs sporadically.  Since this is nested deeply within the table adapter, I am at a loss to see that the issue isn't related to something the connector or generated code is doing.

Sorry - development system is XP Pro SP2, but the error occurred on a deployed system running Server 2003 R2

It is possible the DB Server closed the connection during the read.

In the case where I saw this, 9 instances of the application were started within 6 seconds of each other.  8 of them complete reading the db via the table adapter ~ 1 minute later.  The one instance that throws the exception does so after ~45 seconds - i.e. well into the read of the table, but before the read was complete.

Here's the query, which I forgot to include previously:

SELECT     ssa_options.opt_susqus_symbol, ssa_options.opt_opra_symbol, ssa_cboe_product_ids.cboe_product_id
FROM         ssa_options LEFT OUTER JOIN                      ssa_cboe_product_ids 
ON ssa_options.opt_instrument_id = ssa_cboe_product_ids.instrument_id

I have seen other cases of different exceptions, all apparently (not yet confirmed) due to a connection issue to the MySQL server:

MySql.Data.MySqlClient.MySqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

Hi Ray and thanks for your report.

I would like to posses that kind of magic to guess what's happening just by looking into trace... Please attach self sufficient test case exhibiting this behavior so I can check.
In the meantime, please update to 5.1.7 and see if error persists.

Hi!

I do not see a 5.1.7 version of the connector on the web page yet, but I will try it as soon as it is available.

I will see if I can come up with a test case.  However, that is not a simple task for an intermittent issue.  Is there any way I can better instrument my application to capture more detail when this does occur again?  It hasn't happened for several days now.

Ray

FYI: I have seen this behavior with 5.1.6.0.

> Is there any way I can better instrument my application to capture more detail when this does occur again?  It hasn't happened for several days now.

Ray, try adding "logging=true" to your connection string. Also, starting server with --log and providing general query log + error log from server around the time error occurs might help (together with logging output).

True, 5.1.7 is not out yet, sorry.

Waiting on your test case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".