Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread "Attempted to read or write protected memory" error
Thu, Sep 22 2011 8:15 PMPermanent Link

TonyWood

Hi All

I'm trying to develop a tool to help our operators migrate large data sets to ElevateDB. This is a retail application, so the migration is typically carried out overnight, under time pressure. The data has to be converted, cleansed, constrained and inserted into ElevateDB. The tool, written in VB.net) will pick up and parse SQL script files, sending the individual queries to ElevateDB using .NETs OdbcCommand.ExecuteNonQuery and OdbcCommand.ExecuteReader methods. Initially i am testing with a local server, the live tool will use a remote server. This is a single threaded application.

Anyway all is well until I try to execute big scripts. The one in question failing after about 17,000 lines. The error I get is :  

Error processing command : <Valid INSERT statement that looks very similar to the previous few hundred accepted statements>.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Continue Processing ?   [Yes]  [No]

If i elect to continue, i get the same error on the next statement.

I've seen this error elsewhere in this forum, but not sure if it's relevant, because i'm not using DBISAM or (AFAIK) dbodbc driver version 3 (i.m using Elevate 2 ODBC Driver edbodbc.dll). The disk partitions are not full and i don't think i have memory issues.

Any ideas chaps?
Thanks in advance
Thu, Sep 22 2011 9:18 PMPermanent Link

TonyWood

TonyWood wrote:

I tried inserting a 200ms delay between query lodgements. The script progressed further to line 31930 ( query # 8609) which was the innocuous :

INSERT INTO "Suburb" (Suburb,State,Postcode) VALUES ('DRY CREEK','SA','5094')

and then failed with the same error as before
Thu, Sep 22 2011 10:00 PMPermanent Link

Raul

Team Elevate Team Elevate


Haven't used EDB with .net but couple of options using google:

- Is the .net framework fully patched?

- does stack trace show where does the error  originate from more precisely

- MS has this KB article that may apply: http://support.microsoft.com/kb/923028

- There are couple of reports that (not with EDB) that mention that once they removed all comments from sql script it ran fine.

- finally the obvious question is how easy would it be to break the large script into couple of smaller ones - does it finish execution ok ?

Raul
Fri, Sep 23 2011 12:05 AMPermanent Link

TonyWood

Thanks for the quick response, Raul

As you hinted, I now see that the error is returned from the Windows system or .Net, not from ElevateDB

With that revelation I tried closing and re-opening the ODBCConnection after every 100 queries and the error went away.

Thanks again for your help
Tony Wood
Fri, Sep 23 2011 3:49 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< Anyway all is well until I try to execute big scripts. The one in
question failing after about 17,000 lines. The error I get is :   >>

I suspect that the problem is related to the garbage collection in the .NET
memory manager, or just a general issue with the size of the script.

What is the total number of lines in the script ?

--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Sep 29 2011 3:16 AMPermanent Link

TonyWood

Hi Tim

That script has about 50,000 lines and about 16,000 queries and can take up to 2hours to run via EDB manager on a large data set. It's the biggest of several used in data migration, which in total may take up to 6 or 7 hours. Prior to this is a (non-sql) data conversion step which also may take a few hours worse case.

I've managed to overcome the original problem by re-opening the ODBCConnection object at least after 1000 queries. Also i needed to re-open it after 'Drop' and 'Create Index' commands to work around locking issues.

What i'm ultimately attempting to do is to minimise the total elapsed time by having multiple instances / threads simultaneously loading the target databases, in the cases where parallelism is easy to spot and safe to execute.

thanks for your response
Tony Wood
Mon, Oct 3 2011 9:23 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< I've managed to overcome the original problem by re-opening the
ODBCConnection object at least after 1000 queries. Also i needed to re-open
it after 'Drop' and 'Create Index' commands to work around locking issues.
>>

What locking issues were those ?  There shouldn't be any issues with locking
with respect to any DDL statements, at least as related to the
ODBCConnection object.

--
Tim Young
Elevate Software
www.elevatesoft.com
Tue, Oct 4 2011 12:15 AMPermanent Link

TonyWood

"Tim Young [Elevate Software]" wrote:

>> What locking issues were those ?  There shouldn't be any issues with locking
>> with respect to any DDL statements, at least as related to the
>> ODBCConnection object.

i seem to get the "Error #300 Cannot Lock the (temporary)? table <TableName> in the schema Default for exclusive access" after DROP {TRIGGER, COLUMN, FUNCTION, INDEX, TABLE} or CREATE INDEX

the (pseudo) code looks like :

oODBCConnection.Open()

while (more commands to execute)
   Dim command As New OdbcCommand(sqlCOmmand, oODBCConnection)
   Dim rowsAffected As Integer = command.ExecuteNonQuery()
end while

oODBCConnection.Close()

As i said, It's not a big deal to me as i can work-around the issue by closing then re-opening the ODBCConnection when an offending query is detected  

thanks again
Tony Wood
Tue, Oct 11 2011 2:38 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<<  seem to get the "Error #300 Cannot Lock the (temporary)? table
<TableName> in the schema Default for exclusive access" after DROP {TRIGGER,
COLUMN, FUNCTION, INDEX, TABLE} or CREATE INDEX >>

Is the word "temporary" actually present in the error message ?

<< the (pseudo) code looks like : >>

The code looks fine, but what really matters is what SQL statements are
being executed.  I just tried altering a table using an ODBCCommand, and
then tried to open, and then alter, the same table in the EDB Manager, all
without doing anything further with the .NET application after it executed
the OdbcCommand.ExecuteNonQuery() method call, and it works fine here with
2.05 B11.

--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Oct 13 2011 3:04 AMPermanent Link

TonyWood

Hi Tim, thanks for your responses. I've changed my client code from VB .Net to Perl on Cygwin which seems to have 'fixed' these locking issues.

The word 'temporary' was only appearing in the error message after an attempt to DROP TABLE on a temp table. In the other cases it didn't appear.

Here's an example of the kind of stuff that was provoking the error in VB .Net, each command would be submitted individually thru ODBC:

CREATE TEMPORARY TABLE dataCleanTmp(t INT NOT NULL)
INSERT INTO dataCleanTmp VALUES(1)
INSERT INTO
           Promotion (BusinessCode , PromotionCode , PromotionName , Description)
           SELECT '001' , 0 , '' , ''
           FROM dataCleanTmp
           WHERE NOT EXISTS(SELECT * FROM Promotion WHERE BusinessCode='001' OR PromotionCode=0)
           RANGE 1 TO 1
DROP TABLE dataCleanTmp  << get locking error here, typically after hundreds of error free queries

(this isn't my SQL, i'm trying to write software which will automatically lodge any valid SQL commands it finds in a random script)

Also, I no longer have to reopen the connection after every 1000 or whatever queries.
thanks again for your help
Tony Wood
Image