can't access mysql data on successive attempts RSS

• • Reply
  

  vmayer

  Member

  1 Points

  2 Posts

  can't access mysql data on successive attempts

  Jun 09, 2012 10:47 PM|LINK

  Hi, I have mysql running locally on my machine, and I just started building my first ASP.NET website using data from a database on this machine. The application currently only queries the database; it doesn't modify it. When the application starts up, multiple connections get made to the database (both in serial and in parallel), and queries in the form of simple statements and stored procedures get called. Each connection should result in a disconnection, although I'm not positive.

  I'm debugging through the Visual Studio IDE. The first time I run (after having restarted the mysql daemon), everything seems to work fine. All of the data is correctly returned from the database.

  
  But if I stop and restart the debugger, I can no longer access the data from the database. I don't get any errors in Visual Studio when I attempt to open the connection, but when I do "DbCommand.ExecuteReader(...)" thereby executing my stored procedure, I get an error that a stored procedure of this name is not in my database.

  Likewise, if I do a "DbCommand.ExecuteReader(...)" on a simple SQL statement like "select * from Room", the DbDataReader returned has HasRows set to false.
  Any thoughts on why? I'm new to this, so it could be something dumb.

  My web.config includes these lines:
  <connectionStrings> <add name="wheresMySh_t" connectionString="data source=localhost;Initial Catalog=wheresMySh_t;user id=root;password=thepassword" providerName="MySql.Data.MySqlClient" /> </connectionStrings>
  <appSettings><add key="factory" value="MySql.Data.MySqlClient"/> </appSettings>
  <assemblies><add assembly="MySql.Data, Version=5.0.8.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/></assemblies>
  <DbProviderFactories> <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.0.8.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D" /> </DbProviderFactories>

  Every time I query a table I get a connection with this code:

  
  DbConnection conn;
  string factoryStr = WebConfigurationManager.AppSettings["factory"];
  DbProviderFactory factory = DbProviderFactories.GetFactory(factoryStr);
  conn = factory.CreateConnection();
  conn.ConnectionString = WebConfigurationManager.ConnectionStrings["wheresMySh_t"].ConnectionString;
  conn.Open();

  I then issue a simple query:
  DbCommand cmd = factory.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandType = System.Data.CommandType.Text;
  cmd.CommandText = "SELECT * FROM Room";
  DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
  if (!reader.HasRows)
  {
     return null;
  }
  The first time I run with the debugger (after restarting the mysql daemon), the reader has HasRows=true (as it should). However, upon successive debugging attempts, HasRows is set false.

  I don't know if it will provide any clues, but here's what the end of my MySql General Query Log looks like:

  120609 14:37:40 1 Init DB wheresmysh_t
  1 Query call GetAllContainers ()
  1 Init DB wheresmysh_t
  1 Query SELECT * FROM Room
  120609 14:37:41 1 Init DB wheresmysh_t
  1 Query SET SQL_SELECT_LIMIT=1
  1 Query call GetPerson ('larrychan1@yahoo.com')
  1 Query SET SQL_SELECT_LIMIT=-1
  1 Init DB wheresmysh_t
  1 Query call GetPersons ()
  120609 14:38:51 1 Init DB wheresmysh_t
  1 Query call GetAllContainers ()
  1 Init DB wheresmysh_t
  1 Query SELECT * FROM Room
  1 Init DB wheresmysh_t
  1 Query SET SQL_SELECT_LIMIT=1
  1 Query call GetPerson ('larrychan1@yahoo.com')
  1 Query SET SQL_SELECT_LIMIT=-1
  1 Init DB wheresmysh_t
  1 Query call GetPersons ()
  120609 14:39:07 1 Init DB wheresmysh_t
  1 Query call GetAllContainers ()
  1 Init DB wheresmysh_t
  1 Query SELECT * FROM Room
  120609 14:39:08 1 Init DB wheresmysh_t
  1 Query SET SQL_SELECT_LIMIT=1
  1 Query call GetPerson ('larrychan1@yahoo.com')
  1 Query SET SQL_SELECT_LIMIT=-1
  1 Init DB wheresmysh_t
  1 Query call GetPersons ()

  I couldn't find any help online on how to interpret this file. So, I have no idea whether it looks right or not. Like what does the number identify? And should there be a "Close" statement or something? Sorry, I know this isn't a mysql forum, but maybe somebody will know.

  Here's what's in the Mysql Error log:

  120609 14:37:20 [Note] Plugin 'FEDERATED' is disabled.
  120609 14:37:20 InnoDB: Initializing buffer pool, size = 76.0M
  120609 14:37:20 InnoDB: Completed initialization of buffer pool
  120609 14:37:20 InnoDB: Started; log sequence number 0 44233
  120609 14:37:20 [Note] Event Scheduler: Loaded 0 events
  120609 14:37:20 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
  Version: '5.1.63-community-log' socket: '' port: 3306 MySQL Community Server (GPL)
  120609 14:39:20 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Normal shutdown

  Any ideas? Thank you!

• • Reply
  

  markfitzme

  Star

  14369 Points

  2220 Posts

  Re: can't access mysql data on successive attempts

  Jun 09, 2012 11:03 PM|LINK

  The way you have it set, the connection will not automatically be closed. Are you retrieving binary data from the database? If not, don't use SequentialAccess. The purpose is to let you stream certain items that contain binary data out. To ensure the connection gets closed, use the CommandBehavior.CloseConnection enumeration instead.

  When you check for reader.HasRows, you should also check to see if a) the reader is not null, and b) that the reader is open. If either of these cases are fales your code will blow up on the if(!reader.HasRows)

  I'm not sure, but perhaps the sequentialaccess commandbehavior has some issues with the particular mysql driver. Not sure, just a stab in the dark really. I would definitely try the CloseConnection enumeration instead. Also, don't forget to dispose of your datareader when done to ensure that other objects also get released. That will help free up the connection and resources faster.

   

  Don't forget to mark useful responses as Answer if they helped you towards a solution.
  • Marked as answer by vmayer on Jun 10, 2012 06:53 PM
• • Reply
  

  vmayer

  Member

  1 Points

  2 Posts

  Re: can't access mysql data on successive attempts

  Jun 10, 2012 01:25 AM|LINK

  Great advice! Thank you! I will try to modify the code tomorrow and let you know.