MySQL Bugs: #4991: Inconsistent behavior of MySqlDataReader class ?

Bug #4991	Inconsistent behavior of MySqlDataReader class ?
Submitted:	11 Aug 2004 14:43	Modified:	14 Oct 2004 14:20
Reporter:	Peter Snizek	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	MySQL 4.0.16, .NET 1.1, ByteFx 0.76	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Dear Sir,

I am quite desperate with an issue I'm currently facing concerning the MySqlDataReader class.

What I did:
I wrote a method to read the first record of a table, using the MySqlDataReader.Reader()method.

Issue Description:
It often happens, that the class doesn't find the first record although there is 1 (and only 1) in the table. Then, suddenly, the same method finds the record and the program works fine.
Usually, if it finds the first (and only record) in the table at all, then it is after a program restart. But, strange enough, not always.
I'm reading actually the first 2 columns of the table by using .GetInt32(0) for the first column and .GetString(1) for the second column. 
The whole issue becomes now even more strange to me. If myReader.Read() doesn't return any record and .GetInt32(0) followed by .GetString(1) is applied, then .GetString(1) throws an error while GetInt32(0) doesn't throw nothing (although being first).

Meanwhile I have built in a lot of debug code in my class to watch the behavior closely. Whenever an error is thrown by .GetInt32 I checked the table (MySQL Control Center) to check if there's really no record. But, as stated already, there is ALWAYS a record. Even the HasRow Property returns FALSE if the MySqlDataReader class thinks there is no record. But again, there was always a record (always only 1) at the point of time when the error happens.

IF there are 2 ore more records, then it seems to work. The error seems to happen only if there is 1 (and only 1) record in the table.

I have submitted the issue including my commented code to bugs.mysql.com under the issue ID# 4965.

Meanwhile I am pretty sure that this is not my programming mistake due to 2 reasons:
1. The error happens also right after a restart of my application (although not as often).
2. The error can't be forced reliably.

I also don't think that it is an issue with MySql since the MySQL Control Center was able to see and query the record at any time (even at times, my application didn't see the record).

So far I have enjoyed to work with the ByteFX adaptor and I feel that you guys have done a great job.

I would very much appreciate help regarding this issue as it is a showstopper for my project right now.

Thanks and with best regards

Peter Snizek

How to repeat:
Use my MySqlDataReader to read the 1st record out of a table with only 1 record.

Issue closed. Problem caused by bad thread construction.

I am having the same issue, so I am curious how the thread was being created badly.

Also, I have recently converted from the CoreLab .NET provider to ByteFX.  My code worked correctly with this issue with CoreLab, but now it is not working.  This seems to indicate that it could be a problem with ByteFX and not with my code.

Answer to Matthew Aznoe:

I have simply removed the thread constructor and am starting the classes now as  non-threads. Since then, the issue never occured again.

The thing is that there are 2 threads which matter:
The 1st one imports data from 2 flat files into Mysql.
The 2nd thread checks if any new records available (based on what thread 1 imported). 
If yes, it takes the 1st entry, copies it into "spooling" tables where it is used by a reporting tool and deletes the records from the source table.
Because both threads were working concurrently, I believe that it could happen
that thread 2 (spooling) was faster (or earlier) than thread 1 (dataimport).
What I will do is, that I will make 1 thread out of both threads and manage spooling etc.. based on self defined "events", instead of having 2 threads working concurrently. A self defined event will trigger the execution of the spooling class in future when appropriate, rather than having 2 parallel threads running dumb in circles.

What I still don't understand is the behavior of the GetString method. I still don't understand why no error got thrown.

Could you describe your issue?

Cheers, Peter

After further investigation, it appears the problem is not related to threading at all.  The problem appears to be with returning values of type time where "00:00:00" is being returned as NULL.  I will create this as a new bug.

I've experienced the same problem.

The reader fails and returns the following error:
The DateTime represented by the string is out of range.

However the reader seems to fail after returning a certain number of rows rather than on a specific record.  In this case the reader failed on the 37th row.

Here is the 37th Row, it doesn't appear to have a problem with the date field?

+--------+-------------+--------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+
| NewsID | CountryCode | Title                                                                                                              | StartDate  | EndDate    | Text                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | Active | Author |
+--------+-------------+--------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+
|     36 | AU          | The Cosmic Psychos hit Falls                                                                                       | 2000-08-25 | 2000-09-25 |<P><EM>Local Correspondent, Clive Dickerson reports from Falls Creek</EM></P> <P>Social life at Falls continues in its usual frantic way welcoming the Cosmic Psychos at the Man last Tuesday - good to see the old fellas still rockin' on and drawing the crowds!&nbsp; The ZZ Top look-alike Mixer added to their raunchy image...<BR>&nbsp;<BR>Saturday had everyone checking out who would qualify for the the Mogul Masters and the finals were held under lights on Saturday night on the summit - the event pulled big crowds again and Frying Pan patrons had a great view that most likely grew dimmer with all the alcohol as the night progressed!<BR>&nbsp;<BR>Unfortunately [ ! ! ] there were no big bingles or mighty stacks just a few bales but the crowd were not disappointed as the competition itself was heavy duty with big international competitors [Canadians, Frenchies, Germans] not forgetting the Buller Team and the Hotham Team.&nbsp;&nbsp; Jade Vaughan, Canadian took out first place in the Men's moguls with local Falls rider Tom Costa taking 6th place;&nbsp;&nbsp; women's Moguls finalist was Jane Sexton.&nbsp;&nbsp;&nbsp; Heavy night - great ...<BR>&nbsp;<BR>Wednesday we welcomed the Quicksilver pro Team arriving for the 1/4 pipe competition for locals and pro teams - more details later.&nbsp; We have a front travelling in with a promise of an even bigger snowfall for the comp but its being held at the top of the Gully Chair so will go ahead no matter what conditions.&nbsp; Log in next week for more Falls news .....</P>|      1 |      0 |
+--------+-------------+--------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+

=================================

		public void GetNews()
		{
			// Create a reader and set to null
			MySqlDataReader myReader = null;

			// Create a connection and set to null
			MySqlConnection myConn = null;
			
			MySqlConnection mySqlCon = new MySqlConnection("Server=localhost;Database=btworld;User ID=dbuser;Password=password");
			mySqlCon.Open();

			// Assign the connection
			myConn = mySqlCon;

			// Build the sql statement
			string mySql = "SELECT * FROM tblNews";

			// Create a command
			MySqlCommand myCmd = myConn.CreateCommand();
			// Set the command text
			myCmd.CommandText = mySql;
			// Execute the reader
			myReader = myCmd.ExecuteReader();
			
			int newsId = 0;
			string countryCode = null;
			string title = null;
			string startDate = null;
			string endDate = null;
			string text = null;
			int active = 0;
			int author = 0;

			while (myReader.Read())
			{
				newsId = myReader.GetInt16(0);
				countryCode = myReader.GetValue(1).ToString();
				title = myReader.GetValue(2).ToString();
				startDate = myReader.GetDateTime(3).ToString();
				endDate = myReader.GetDateTime(4).ToString();
				text = myReader.GetValue(5).ToString();
				active = myReader.GetInt32(6);
				author = myReader.GetInt32(7);

				Response.Write(newsId);
			}
			
			// Clean up the connection if it is still open
			if(myConn != null) myConn.Dispose();
		}

I'm not sure if this is still an issue or not. If this is still causing someone trouble, can you post a schema and example data row that is causing the problem?

Hi, I placed additional information at this posting: http://bugs.mysql.com/bug.php?id=5097

It appears that you have seen it there.

Thanks.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I have marked this as can't repeat because the dialogue indicated it was a problem with datetime or time fields and that the user would post as a new bug (which I believe has already occured).  if anyone believes this bug should remain open, please let me know.