MySQL Bugs: #4942: Object reference not set to an instance of an object

Bug #4942	Object reference not set to an instance of an object
Submitted:	8 Aug 2004 6:32	Modified:	14 Oct 2004 14:28
Reporter:	David Richardson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:		OS:	Windows (Winodws XP & Red Hat 9)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I have the following query:

SELECT
	`SubscriptionMember`.`pkID`				AS SubscriptionMemberID,
	`SubscriptionMember`.`fkSubscription`			AS SubscriptionID,
	`SubscriptionMember`.`StartDate`			AS StartDate,
	`SubscriptionMember`.`EndDate`				AS EndDate,
	`SubscriptionMember`.`fkSubscriptionMemberEndReason`	AS EndReason,
	EndReasonDescription.Description			AS EndReasonDescription,
	EndReasonAbbreviation.Description			AS EndReasonAbbreviation,
	`SubscriptionMemberEndReason`.`TypeCode`		AS EndReasonTypeCode,
	`SubscriptionMember`.`fkSubscriptionMemberType`		AS MemberType,
	TypeDescription.`Description`				AS MemberTypeDescription,
	TypeAbbreviation.`Description`				AS MemberTypeAbbreviation,
	`SubscriptionMemberType`.`TypeCode`			AS MemberTypeCode,
	`SubscriptionMember`.`DateCreated`			AS DateCreated,
	`SubscriptionMember`.`fkCreatedBy`			AS CreatedByID,
	CreationUser.`UserName`					AS CreatedByName,
	`SubscriptionMember`.`DateModified`			AS DateModified,
	`SubscriptionMember`.`fkModifiedBy`			AS ModifiedByID,
	ModificationUser.`UserName`				AS ModifiedByName,
	`SubscriptionMember`.`RowVersion`			AS RowVersion
FROM
	`SubscriptionMember` LEFT OUTER JOIN
	`SubscriptionMemberType`
	     ON `SubscriptionMember`.`fkSubscriptionMemberType` = `SubscriptionMemberType`.`pkID` INNER JOIN
	`TextString` AS TypeDescription
	     ON `SubscriptionMemberType`.`DescriptionID` = TypeDescription.`GroupID` INNER JOIN
	`TextString` AS TypeAbbreviation
	     ON `SubscriptionMemberType`.`ShortDescriptionID` = TypeAbbreviation.`GroupID` LEFT OUTER JOIN
	`SubscriptionMemberEndReason`
	     ON `SubscriptionMember`.`fkSubscriptionMemberEndReason` = `SubscriptionMemberEndReason`.`pkID` LEFT OUTER JOIN
	`TextString` AS EndReasonDescription
	     ON `SubscriptionMemberEndReason`.`DescriptionID` = EndReasonDescription.`GroupID` 
	     AND EndReasonDescription.`fkLanguage` = 1 LEFT OUTER JOIN
	`TextString` AS EndReasonAbbreviation
	     ON `SubscriptionMemberEndReason`.`ShortDescriptionID` = EndReasonAbbreviation.`GroupID` 
	     AND EndReasonAbbreviation.`fkLanguage` = 1 INNER JOIN
	`UserInfo` AS ModificationUser
		  ON `SubscriptionMember`.`fkModifiedBy` = ModificationUser.`pkID` INNER JOIN
	`UserInfo` AS CreationUser
		  ON `SubscriptionMember`.`fkCreatedBy` = CreationUser.`pkID` 
WHERE
	`SubscriptionMember`.`pkID` = @intSubscriptionMember;

When I execute this query against the database directly, I get the results I expect.  However, when I execute the query against the database, through the ByteFX library and return the results in an IDataReader object I get some strange behaviour; the EndReasonDescription, MemberTypeDescription, ModifiedByName and CreatedByName fields contain null object references, not the varchar that I expect.

How to repeat:
The following is the table structure being used.

CREATE TABLE `textstring` (
  `pkID` bigint(20) NOT NULL auto_increment,
  `GroupID` bigint(20) NOT NULL default '0',
  `Description` varchar(255) NOT NULL default '',
  `fkLanguage` bigint(20) NOT NULL default '0',
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkCreatedBy` bigint(20) NOT NULL default '0',
  `DateModified` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkModifiedBy` bigint(20) NOT NULL default '0',
  `RowVersion` timestamp(14) NOT NULL,
  PRIMARY KEY  (`pkID`),
  UNIQUE KEY `pkID` (`pkID`),
  UNIQUE KEY `ixuGroupID_fkLanguage` (`GroupID`,`fkLanguage`),
  KEY `ixfkCreatedBy` (`fkCreatedBy`),
  KEY `ixfkModifiedBy` (`fkModifiedBy`),
  KEY `ixfkLanguage` (`fkLanguage`),
  KEY `GroupID` (`GroupID`),
  KEY `ixLanguage_GroupID` (`fkLanguage`,`GroupID`),
  CONSTRAINT `TextString_fkCreatedBy` FOREIGN KEY (`fkCreatedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `TextString_fkLanguage` FOREIGN KEY (`fkLanguage`) REFERENCES `languagetype` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `TextString_fkModifiedBy` FOREIGN KEY (`fkModifiedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE `subscriptionmembertype` (
  `pkID` bigint(20) NOT NULL auto_increment,
  `DescriptionID` bigint(20) NOT NULL default '0',
  `ShortDescriptionID` bigint(20) NOT NULL default '0',
  `TypeCode` varchar(10) default NULL,
  `StartDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `EndDate` datetime default NULL,
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkCreatedBy` bigint(20) NOT NULL default '0',
  `DateModified` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkModifiedBy` bigint(20) NOT NULL default '0',
  `RowVersion` timestamp(14) NOT NULL,
  PRIMARY KEY  (`pkID`),
  UNIQUE KEY `pkID` (`pkID`),
  KEY `ixfkCreatedBy` (`fkCreatedBy`),
  KEY `ixfkModifiedBy` (`fkModifiedBy`),
  KEY `ixDescriptionID` (`DescriptionID`),
  KEY `ixShortDescriptionID` (`ShortDescriptionID`),
  CONSTRAINT `SubscriptionMemberType_fkCreatedBy` FOREIGN KEY (`fkCreatedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberType_fkDescription` FOREIGN KEY (`DescriptionID`) REFERENCES `textstring` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberType_fkModifiedBy` FOREIGN KEY (`fkModifiedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberType_fkShortDescription` FOREIGN KEY (`ShortDescriptionID`) REFERENCES `textstring` (`pkID`) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE `subscriptionmemberendreason` (
  `pkID` bigint(20) NOT NULL auto_increment,
  `DescriptionID` bigint(20) NOT NULL default '0',
  `ShortDescriptionID` bigint(20) NOT NULL default '0',
  `TypeCode` varchar(10) default NULL,
  `StartDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `EndDate` datetime default NULL,
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkCreatedBy` bigint(20) NOT NULL default '0',
  `DateModified` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkModifiedBy` bigint(20) NOT NULL default '0',
  `RowVersion` timestamp(14) NOT NULL,
  PRIMARY KEY  (`pkID`),
  UNIQUE KEY `pkID` (`pkID`),
  KEY `ixfkCreatedBy` (`fkCreatedBy`),
  KEY `ixfkModifiedBy` (`fkModifiedBy`),
  KEY `ixDescriptionID` (`DescriptionID`),
  KEY `ixShortDescriptionID` (`ShortDescriptionID`),
  CONSTRAINT `SubscriptionMemberEndReason_fkCreatedBy` FOREIGN KEY (`fkCreatedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberEndReason_fkDescription` FOREIGN KEY (`DescriptionID`) REFERENCES `textstring` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberEndReason_fkModifiedBy` FOREIGN KEY (`fkModifiedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMemberEndReason_fkShortDescription` FOREIGN KEY (`ShortDescriptionID`) REFERENCES `textstring` (`pkID`) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE `subscriptionmember` (
  `pkID` bigint(20) NOT NULL auto_increment,
  `fkSubscription` bigint(20) NOT NULL default '0',
  `fkRole_SubscriptionMember` bigint(20) NOT NULL default '0',
  `fkSubscriptionMemberType` bigint(20) NOT NULL default '0',
  `StartDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `EndDate` datetime default NULL,
  `fkSubscriptionMemberEndReason` bigint(20) default NULL,
  `ExternalReferenceID` varchar(50) default NULL,
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkCreatedBy` bigint(20) NOT NULL default '0',
  `DateModified` datetime NOT NULL default '0000-00-00 00:00:00',
  `fkModifiedBy` bigint(20) NOT NULL default '0',
  `RowVersion` timestamp(14) NOT NULL,
  PRIMARY KEY  (`pkID`),
  UNIQUE KEY `pkID` (`pkID`),
  UNIQUE KEY `fkSubscription_fkRole_StartDate` (`fkSubscription`,`fkRole_SubscriptionMember`,`StartDate`),
  KEY `ixfkCreatedBy` (`fkCreatedBy`),
  KEY `ixfkModifiedBy` (`fkModifiedBy`),
  KEY `ixRoleSubscriptionMember` (`fkRole_SubscriptionMember`),
  KEY `ixSubscription` (`fkSubscription`),
  KEY `ixMemberEndReason` (`fkSubscriptionMemberEndReason`),
  KEY `ixMemberType` (`fkSubscriptionMemberType`),
  KEY `fkSubscription` (`EndDate`,`fkSubscription`),
  KEY `EndDate` (`EndDate`),
  KEY `ixSubscriptionMember2` (`fkSubscription`,`fkRole_SubscriptionMember`),
  KEY `ixSubscriptionMember1` (`EndDate`,`pkID`,`fkRole_SubscriptionMember`),
  KEY `fkRole_SubscriptionMember` (`fkSubscription`,`fkRole_SubscriptionMember`,`EndDate`),
  KEY `SubscriptionMember_Subscription` (`fkRole_SubscriptionMember`,`fkSubscription`),
  CONSTRAINT `SubscriptionMember_fkCreatedBy` FOREIGN KEY (`fkCreatedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMember_fkModifiedBy` FOREIGN KEY (`fkModifiedBy`) REFERENCES `userinfo` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMember_fkRole` FOREIGN KEY (`fkRole_SubscriptionMember`) REFERENCES `role` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMember_fkSubscription` FOREIGN KEY (`fkSubscription`) REFERENCES `subscription` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMember_fkSubscriptionMemberEndReason` FOREIGN KEY (`fkSubscriptionMemberEndReason`) REFERENCES `subscriptionmemberendreason` (`pkID`) ON DELETE CASCADE,
  CONSTRAINT `SubscriptionMember_fkSubscriptionMemberType` FOREIGN KEY (`fkSubscriptionMemberType`) REFERENCES `subscriptionmembertype` (`pkID`) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE `userinfo` (
  `pkID` bigint(20) NOT NULL auto_increment,
  `UserName` varchar(100) NOT NULL default '',
  `Password` varchar(50) default NULL,
  `ReminderPhrase` varchar(50) default NULL,
  `StartDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ExpiryDate` datetime default NULL,
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `DateModified` datetime NOT NULL default '0000-00-00 00:00:00',
  `RowVersion` timestamp(14) NOT NULL,
  PRIMARY KEY  (`pkID`),
  UNIQUE KEY `UserName` (`UserName`),
  UNIQUE KEY `ixpkID_UserID_Password` (`pkID`,`UserName`,`Password`),
  UNIQUE KEY `pkID` (`pkID`,`UserName`,`Password`),
  KEY `UserID` (`UserName`,`Password`,`pkID`)
) TYPE=InnoDB;

I forgot to say that I am using ByteFX 0.76 library.

Hi,

I have a similar issue too:

		public void FindFirstRec()
		{
			/* this Method finds the first entry in the header table and returns
			 * the record ID because I want the queue to work among the FIFO principles */

			string SQLQuery="SELECT __headerID, AUFNR FROM header ORDER BY __headerID ASC";
			mycmd = new MySqlCommand(SQLQuery, mycon);
			// ExecuteReader returns a dataset
			MySqlDataReader myReader = mycmd.ExecuteReader();
			MessageBox.Show("Waiting for myReader");
			if (myReader.HasRows)
			{
				myReader.Read();
				RecID=myReader.GetInt32(0); // gets the first field which is a INT type
				OrderNumber=myReader.GetString(1);
				Console.WriteLine (Convert.ToString(RecID)+"    "+OrderNumber); // debug information to console
			}
			myReader.Close(); // closes the Reader
		}

1)
The statement: if (myReader.HasRows) often, not always, returns false, although there is 1 record in the respective table.

2) 
If I would not check with "HasRows" the statement "OrderNumber=myReader.GetString(1)" would through following error: "Object reference not set to an instance of an object"

3) As I mentioned, this happens mostly, but sometimes it works. If it works than only after the program got restarted.

4) The method works reliably if there are at least 2 records in the table. (if only 1 record, then it is often ignored as already mentioned).

5) If there are at least 2 records, then it identifies record 2 as the 1st record.

I would appreciate very much your help on this. Otherwise I wouldn't be able to finish my work. Please just let me know if I did a mistake or if it needs a bugfix on your end.

Here's the complete class (just for your reference):

######################################################
######################################################

using System;
using System.Threading;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;
using ByteFX.Data.MySqlClient;

namespace ASReporter
{

	/* This class is designed to move jobs from header to header_spooler tables.
	 * It happens record by record. Once a record is "computed" the next record
	 * can be taken from the header table. Reason is to make sure that there is
	 * alway only 1 print job stored in the spooler tables (header_spooler 
	 * and detail_spooler). */

	public class computeJob
	{
		string DataSource;
		string Database;
		string UserID;
		string Password;
		string MyConString;
		int RecID;
		string OrderNumber;
		MySqlConnection mycon;
		MySqlCommand mycmd;

		public void ConnectDatabase()
		{
			// set connection and open mysql database
			DataSource	= "localhost";
			Database	= "ASReporter";
			UserID		= "";
			Password	= "";
			
			// create connection string
			MyConString = "Data Source=" + DataSource + 
				";Database=" + Database +
				";User ID=" + UserID +
				";Password=" + Password;
			mycon = new MySqlConnection(MyConString);
			mycon.Open(); // open database based on defined connection
			
			
		}

		public void FindFirstRec()
		{
			/* this Method finds the first entry in the header table and returns
			 * the record ID because I want the queue to work among the FIFO principles */

			string SQLQuery="SELECT __headerID, AUFNR FROM header ORDER BY __headerID ASC";
			mycmd = new MySqlCommand(SQLQuery, mycon);
			// ExecuteReader returns a dataset
			MySqlDataReader myReader = mycmd.ExecuteReader();
			MessageBox.Show("Waiting for myReader");
			if (myReader.HasRows)
			{
				myReader.Read();
				RecID=myReader.GetInt32(0); // gets the first field which is a INT type
				OrderNumber=myReader.GetString(1);
				Console.WriteLine (Convert.ToString(RecID)+"    "+OrderNumber); // debug information to console
			}
			myReader.Close(); // closes the Reader
		}
		
		
		public void MoveHeader()
		{
			string CopyHeaderRec="INSERT INTO header_spooler SELECT * FROM header WHERE __headerID="+this.RecID; 
			// update command object with Query String
			mycmd = new MySqlCommand(CopyHeaderRec, mycon); 
			mycmd.ExecuteNonQuery(); // execute Query
			mycmd.Dispose();
			string DeleteHeaderRec="DELETE FROM header WHERE __headerID="+this.RecID; 
			mycmd = new MySqlCommand(DeleteHeaderRec, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();
		}

		public void MoveDetail()
		{
			string CopyDetailRec="INSERT INTO detail_spooler SELECT * FROM detail WHERE _headerID="+this.RecID; 
			// update command object with Query String
			mycmd = new MySqlCommand(CopyDetailRec, mycon); 
			mycmd.ExecuteNonQuery(); // execute Query
			mycmd.Dispose();
			string DeleteDetailRec="DELETE FROM detail WHERE _headerID="+this.RecID; 
			mycmd = new MySqlCommand(DeleteDetailRec, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();

		}

		public void SendRecord()
		{
			MessageBox.Show("Gedruckt wird Auftragsnr: " 
				+ this.OrderNumber + " Job-ID: " + Convert.ToString(this.RecID));
		}

		public  void CloseDBConnection()
		{
			mycmd.Dispose(); // close connection and destroy command object
			mycon.Close();
			mycon.Dispose();
			mycmd=null;
			mycon=null;

		}

		public void ClearSpooler()
		{
			string ClrSpooler="DELETE FROM header_spooler;" +
							   "DELETE FROM detail_spooler;";
			mycmd = new MySqlCommand(ClrSpooler, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();
		}

		public computeJob()
		{

			this.ConnectDatabase();	// opens database
			this.FindFirstRec();	// finds first record in header (FIFO principle)
			this.MoveHeader();		// moves found header record to header_spooler table
			this.MoveDetail();		// moves corresponding detail records to detail_spooler table
			this.SendRecord();		// performs action (i.e. printing a report based on records
									// in header_spooler and detail_spooler
			this.ClearSpooler();    // After job is processed it needs to be removed from Spooler;
			this.CloseDBConnection(); // closes db connection
		}
	}
}

Forgot to mention that I am on ByteFX 0.76, Windows XP (Whistler), MySql 4.0.16-nt, AMD 2600+, 1GB Memory, Visual C#.net (.net framework 1.1)

Couple of other details that I should have included.

I am using C# with the .NET Framework 1.1.

I have executed the statement against both MySql running on Windows XP SP1 and Red Hat Linux 9, with the same results.

Can you isolate a particular row that is causing the problem?  Does the problem row have a datetime column with an all zero value like '0000-00-00'?  if so, that might be the problem since the system DateTime object can't reader that value.  Please report back with an example data row that is causing the problem.

Thanks Reggie.  I did have a couple of records that had a datetime and they had a value of "0000-00-00 00:00:00".  I have changed them and it seems to work.

Perhaps Microsoft should be putting something in the DateTime struct to represent an empty DateTime, much like string.Empty.

Thanks a bunch.

Well, I do have a table with no datetime columns or what so ever.
the table is:
Tbl_Usr_Users:
Usr_UserId (int(10))
Usr_App_ApplicationId (int(10))
Usr_NickName (varchar(45))
Usr_LoginName (varchar(45))
Usr_Password (varchar(45))

with one record in it (Usr_UserId = 1 and Usr_App_ApplicationId = 1) I get the same strange behaviour:

with the datareader I can get the first 2 values (oRdr.Item("Usr_UserId") and oRdr.Item("Usr_App_ApplicationId"))
but when I want to get the nickname this way (oRdr.Item("Usr_NickName") or oRdr.Item(2)) I get the same error: object not set...blablabla.

Please help!

Sven

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Marking this closed because the problem data was the zero datetimes.  Note that this is a serious issue and one that I am trying to solve in a reasonable way.

Sven,  the example you gave is very simple and considering the size of my test suite hard for me to believe that there isn't more going on here.  Please make sure your data isn't actually null and if you think you still have a problem, open a new bug report and post as many details as possible about the issue and I'll get back to you.

Thanks