System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: a4d26010eb8a240e82979f1d1d7f852d40a1dc78
Title: System.AccessViolationException: Attempted to read or write protected memory.
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Native_Assembly Resolution: External_Bug
Last Modified: 2011-09-16 01:56:22
Version Found In: 1.0.74
Description:
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: =============================================

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]);

============================================= The following class can reproduce the problem: =============================================

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();
            }
        }
    }

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!


anonymous added on 2011-09-15 12:08:15 UTC:
I uploaded the mentioned VS2008 test solution here: http://www.2shared.com/file/wMwCSvXA/SQLiteBug.html


anonymous added on 2011-09-15 14:47:58 UTC:
Verified to be a problem in the native SQLite library. Tested with SQLite 3.7.7.1 and SQLite 3.7.8 (6b657ae75035eb10b0ad640998d3c9eadfdffa6e).


mistachkin added on 2011-09-16 01:55:53 UTC:
See ticket http://www.sqlite.org/src/info/002caede898.


mistachkin added on 2011-09-16 01:56:22 UTC:
Closing as external.