cmd02.ExecuteReader() call throws a native exception in
System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at System.Data.SQLite.UnsafeNativeMethods.sqlite3_step(IntPtr stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
=============================================
The Database:
=============================================
<verbatim>
CREATE TABLE "InvItem"
(
invItemID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
rowStatus INTEGER(1) NOT NULL,
remoteID TEXT(50),
successorInvItemID UNIQUEIDENTIFIER,
rootInvItemID UNIQUEIDENTIFIER NOT NULL,
registeredByUserID INTEGER(4),
registerTime DATETIME NOT NULL,
itemSource INTEGER(1) NOT NULL,
itemState INTEGER(1) NOT NULL,
storageLocationID UNIQUEIDENTIFIER,
responsiblePersonID UNIQUEIDENTIFIER,
deviceID UNIQUEIDENTIFIER,
barCode TEXT(128),
costCenterID UNIQUEIDENTIFIER,
amount REAL,
articleID TEXT(50),
dutyState TEXT(50),
manufactureNumber TEXT(50),
note TEXT(400),
purchaseTime DATETIME,
purchaseValue TEXT(50),
extraData01 TEXT(255),
extraData02 TEXT(255),
extraData03 TEXT(255),
extraData04 TEXT(255),
extraData05 TEXT(255),
extraData06 TEXT(255),
extraData07 TEXT(255),
extraData08 TEXT(255),
extraData09 TEXT(255),
extraData10 TEXT(255),
containerInvItemID UNIQUEIDENTIFIER,
manufacturerName TEXT(50),
color TEXT(20),
size TEXT(20),
kartonKod TEXT(20),
amountUnit TEXT(10),
supplierID UNIQUEIDENTIFIER,
warrantyStart DATETIME,
warrantyEnd DATETIME,
invVersionID INTEGER(4) NOT NULL DEFAULT 1,
extraData11 TEXT(255),
extraData12 TEXT(255),
extraData13 TEXT(255),
extraData14 TEXT(255),
extraData15 TEXT(255),
extraData16 TEXT(255),
extraData17 TEXT(255),
extraData18 TEXT(255),
extraData19 TEXT(255),
extraData20 TEXT(255));
CREATE INDEX [idx_InvItem_BarCode] ON [InvItem] ([barCode]);
CREATE INDEX [idx_InvItem_BarCode_RowStatus] ON [InvItem] ([barCode], [rowStatus]);
CREATE INDEX [idx_InvItem_DeviceID] ON [InvItem] ([deviceID]);
CREATE INDEX [idx_InvItem_ItemState] ON [InvItem] ([itemState]);
CREATE INDEX idx_InvItem_containerInvItemID ON InvItem (containerInvItemID);
CREATE INDEX [idx_InvItem_rowStatus] ON [InvItem] ([rowStatus]);
CREATE INDEX [idx_InvItem_rowStatus_successorInvItemID] ON [InvItem] ([rowStatus], [successorInvItemID]);
CREATE INDEX [idx_InvItem_storageLocationID] ON [InvItem] ([storageLocationID]);
CREATE TABLE InvItemChangeSuggestion
(iicsID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
invItemID UNIQUEIDENTIFIER NOT NULL,
registeredOn TEXT(1) NOT NULL,
registeredByUserID INTEGER(4),
fulfillmentID UNIQUEIDENTIFIER,
registerTime DATETIME NOT NULL,
note TEXT(255), htoID UNIQUEIDENTIFIER, bizID UNIQUEIDENTIFIER);
CREATE INDEX [idx_IICS_InvItemID] ON [InvItemChangeSuggestion] ([invItemID]);
CREATE TABLE "InvItemPropValueChangeSuggestion"
(
iicsID UNIQUEIDENTIFIER NOT NULL,
propID INTEGER(4) NOT NULL,
apprVersion INTEGER(4) NOT NULL,
val TEXT(255),
apprTime DATETIME,
PRIMARY KEY (iicsID, propID)
);
CREATE INDEX [idx_IIPVCS_PropID_Val] ON [InvItemPropValueChangeSuggestion] ([propID], [val]);
</verbatim>
=============================================
The following class can reproduce the problem:
=============================================
<verbatim>
public static class SQLiteBug
{
#region SQLite CommandTexts
private static string CmdText01 = @"SELECT invItemID, propID, val FROM
(
SELECT invItemID, propID, registerTime, val
FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
WHERE InvItemChangeSuggestion.iicsID = InvItemPropValueChangeSuggestion.iicsID
AND (propID IN ( -7, -1, -6, -2, -15 ))
-- még nem jóváhagyott javaslat
AND apprVersion < 0
AND invItemID IN
(
SELECT invItemID FROM InvItem
WHERE (invVersionID = 1)
AND (rowStatus = 1)
AND invItemID IN
(
-- ha eredetileg illeszkedik a filter-re
SELECT invItemID FROM InvItem
WHERE (barCode = 'LGB16854')
AND (invVersionID = 1)
AND (rowStatus = 1)
UNION
-- ha a módosítások után a(z) barCode illeszkedik a filter-re
SELECT invItemID FROM
(
SELECT invItemID, val FROM
(
SELECT invItemID, propID, val
FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
WHERE InvItemChangeSuggestion.iicsID = InvItemPropValueChangeSuggestion.iicsID
AND propID = -4
AND apprVersion <> 0
ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
)
WHERE (val = 'LGB16854')
EXCEPT
-- a(z) barCode eredetileg illeszkedett, de a módosítások után már nem illeszkedik a filter-re
SELECT invItemID FROM
(
SELECT invItemID, val FROM
(
SELECT invItemID, propID, val
FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
WHERE InvItemChangeSuggestion.iicsID = InvItemPropValueChangeSuggestion.iicsID
AND propID = -4
AND apprVersion <> 0
ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
)
WHERE (val <> 'LGB16854')
)
)
ORDER BY registerTime ASC
)
GROUP BY invItemID, propID";
private static string CmdText02 = @"SELECT invItemID, storageLocationID, responsiblePersonID, deviceID, costCenterID, COALESCE(changeSuggCount, 0) AS changeSuggCount, containerInvItemID
FROM InvItem
LEFT OUTER JOIN
(
SELECT iicsID, invItemID AS iiID, COUNT(*) AS changeSuggCount FROM InvItemChangeSuggestion
GROUP BY iiID
) AS ChangeSugg ON ChangeSugg.iiID = InvItem.invItemID
LEFT OUTER JOIN
(
SELECT iicsID AS iicsID2, propID, val FROM InvItemPropValueChangeSuggestion
WHERE (propID = -4)
AND (val = 'AB123987')
ORDER BY apprVersion DESC
LIMIT 1
) AS PropValChangeSugg ON PropValChangeSugg.iicsID2 = ChangeSugg.iicsID
WHERE
(rowStatus = 1 AND (val = 'AB123987'))
OR ((invVersionID = 1)
AND (rowStatus = 1)
AND invItemID IN
(
-- ha eredetileg illeszkedik a filter-re
SELECT invItemID FROM InvItem
WHERE (barCode = 'AB123987')
AND (invVersionID = 1)
AND (rowStatus = 1)
UNION
-- ha a módosítások után a(z) barCode illeszkedik a filter-re
SELECT invItemID FROM
(
SELECT invItemID, val FROM
(
SELECT invItemID, propID, val
FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
WHERE InvItemChangeSuggestion.iicsID = InvItemPropValueChangeSuggestion.iicsID
AND propID = -4
AND apprVersion <> 0
ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
)
WHERE (val = 'AB123987')
EXCEPT
-- a(z) barCode eredetileg illeszkedett, de a módosítások után már nem illeszkedik a filter-re
SELECT invItemID FROM
(
SELECT invItemID, val FROM
(
SELECT invItemID, propID, val
FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
WHERE InvItemChangeSuggestion.iicsID = InvItemPropValueChangeSuggestion.iicsID
AND propID = -4
AND apprVersion <> 0
ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
)
WHERE (val <> 'AB123987')
))";
#endregion SQLite CommandTexts END
public static void MethodToReproduceSQLiteBUG(System.Data.IDbConnection conn)
{
if (conn == null)
throw new ArgumentNullException("conn");
bool closeConnection = false;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
closeConnection = true;
}
try
{
// running command 01
using (System.Data.IDbCommand cmd01 = conn.CreateCommand())
{
cmd01.CommandText = CmdText01;
using (System.Data.IDataReader reader = cmd01.ExecuteReader())
{
while (reader.Read())
{
Guid invItemID = reader.GetGuid(0);
int propID = reader.GetInt32(1);
string val = reader.IsDBNull(2) ? null : reader.GetString(2);
// DO SOMETHING WITH THE VALUES TO PREVENT THE OPTIMIZER
// TO CUT OUT THE reader.GetXXX CALLS
if (propID < 10)
val = invItemID.ToString();
}
//reader.Close();
}
}
using (System.Data.IDbCommand cmd02 = conn.CreateCommand())
{
cmd02.CommandText = CmdText02;
using (System.Data.IDataReader reader = cmd02.ExecuteReader()) //<-- THIS LINE THROWS native exception at System.Data.SQLite.UnsafeNativeMethods.sqlite3_step
{
while (reader.Read())
{
object[] values = new object[6];
int valCount = reader.GetValues(values);
// DO SOMETHING WITH THE VALUES TO PREVENT THE OPTIMIZER
// TO CUT OUT THE reader.GetValues CALL
values[0] = valCount;
}
//reader.Close();
}
}
}
catch (Exception ex)
{
// The thrown native exception is not catched here on CompactFramework 3.5,
// because the process is killed by the framework
System.Diagnostics.Debug.WriteLine(ex.ToString());
System.Console.WriteLine(ex.ToString());
}
finally
{
if (closeConnection)
conn.Close();
}
}
}
</verbatim>
I can send you a small VS2008 solution with projects for Full .NET Framework 3.5 and Compact Framework 3.5 and a test database if you send me an email.
(I could not attach it here)
My test solution shows that the old (1.0.66) version of System.Data.SQLite.dll does NOT throw this exception, but the new (1.0.74) does.
Please debug and correct this error as ASAP!
<hr /><i>anonymous added on 2011-09-15 12:08:15 UTC:</i><br />
I uploaded the mentioned VS2008 test solution here:
http://www.2shared.com/file/wMwCSvXA/SQLiteBug.html
<hr /><i>anonymous added on 2011-09-15 14:47:58 UTC:</i><br />
Verified to be a problem in the native SQLite library. Tested with SQLite 3.7.7.1 and SQLite 3.7.8 (6b657ae75035eb10b0ad640998d3c9eadfdffa6e).
<hr /><i>mistachkin added on 2011-09-16 01:55:53 UTC:</i><br />
See ticket [http://www.sqlite.org/src/info/002caede898].
<hr /><i>mistachkin added on 2011-09-16 01:56:22 UTC:</i><br />
Closing as external.
|
