Bug #2543 Stored Procedure Inserts Nulls
Submitted: 28 Jan 2004 7:35 Modified: 6 Feb 2004 8:49
Reporter: Mike Willhite Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0 Alpha OS:Windows (Windows 2000)
Assigned to: Dean Ellis CPU Architecture:Any

[28 Jan 2004 7:35] Mike Willhite
Created an application in VB6 which truncates a 360,000 row table and reloads it using a stored procedure. While the code is running I'm developing other stored procedures in NaviCat 5.1.1. and ocassionally checking on the running process by selecting all records from the table. 

During the Other Stored Procedure Creations or the selecting all records from the table being loaded, the running procedure will start inserting all Null fields, every field in every record will be null. Connection to the database is still there and no errors returned from the database. 

If I let it run it will enter thousands of Null filled records.

I using a DSNLess connection and MySQL ODBC 2.50.39

Here is the stored procedure:

CREATE PROCEDURE CCHInsertDetail (IN strGeocode char(10), IN strInOutCity char(1), IN strInOutLocal char(1),
IN decTaxRate decimal(8,2), IN strLocation char(8), IN strTaxBase char(5), IN decMaxTax decimal(8,2), IN decExcessRate decimal(8,2),
IN strEffDate char(8), IN strTaxAuth char(1), IN strTaxType char(2), IN strTaxCat char(2), IN strDescr char(30), IN decUseTaxRate decimal(8,2),
IN decUExcesRate decimal(8,2), IN decFee decimal(10, 2), IN strUnitType char(2), IN decMaxUnits decimal(8,2), IN strMaxType char(2),
IN strPassFlag char(1), IN strPassType char(2), IN strBaseType char(2), IN decUnitBase decimal(15,2), IN decExcessFee decimal(10,2),
IN strThreshType char(1), IN strUnitAOTtax char(1))

LANGUAGE SQL  SQL SECURITY DEFINER  COMMENT 'Inserts CCH Detail.txt Data Into dpcDetail Table'

INSERT INTO dpcdetail (geocode, inoutcity, inoutlocal, taxrate, location, taxbase, maxtax, excessrate, effdate, taxauth,
taxtype, taxcat, descr, usetaxrate, uexcesrate, fee, unittype, maxunits, maxtype, passflag,
passtype, basetype, unitbase, excessfee, threshtype, unitaotax)
(strGeocode, strInOutCity, strInOutLocal,
decTaxRate, strLocation, strTaxBase, decMaxTax, decExcessRate,
strEffDate, strTaxAuth, strTaxType, strTaxCat, strDescr, decUseTaxRate,
decUExcesRate, decFee, strUnitType, decMaxUnits, strMaxType,
strPassFlag, strPassType, strBaseType, decUnitBase, decExcessFee,
strThreshType, strUnitAOTtax)


How to repeat:
Start inserting data using the stored procedure and then perform other actions in the database.

Suggested fix:
Don't Know
[6 Feb 2004 8:49] Dean Ellis
I cannot repeat this against the current 5.0 development sources.

It is possible that you are seeing an issue that has already been corrected...  Can you submit a small test application which demonstrates this behavior?  It may be that my particular test code simply did not perform some step which is required in order to trigger this.