[sqlite] Odd exception when creating a connection object

[Roberts, Barry]

Hi,

We have a large C# application which uses the System.Data.SQLite.Core NuGet 
package. We have been running with version 1.0.107.0 since it was released 
without any issues. Last week we updated to version 1.0.110.0, and now we are 
seeing occasional odd errors appearing (fortunately we are testing and have not 
released). The following error is the most puzzling, this is coming from code 
that has been working fine before upgrade. Internally we use the 
SQLiteConnectionBuilder to ensure the connection string is formatted correctly 
before passing to a new SQLiteConnection instance. Any ideas what can cause the 
issue below, given that the only thing changed is the sqlite assemblies?

InvalidOperationException: code = Misuse (21), message = 
System.Data.SQLite.SQLiteException (0x87EF): bad parameter or other API 
misuse
Failed to configure managed assembly logging.
   at System.Data.SQLite.SQLiteLog.Initialize(String className)
   at System.Data.SQLite.SQLiteConnection..ctor(String connectionString, 
Boolean parseViaFramework)

We have parse via framework set to true.

Kind Regards,
Barry Roberts.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Re] Odd exception when creating a connection object

[Roberts, Barry]

Hi,

I have just taken a look at the source code between the 1.0.107.0 and 1.0.110.0 
drivers, and there are a lot of changes in the SQLiteLog.Initialize code area, 
and how the SQLiteConnection now calls into it. We are seeing more of the same 
exceptions thrown during testing with 110 (we never had this with 107), as 
shown below, which seems to indicate the initialization code is not operating 
in a thread safe manner within a process. I would rather the maintainers take a 
look at it, as they are aware of why it was altered.


InvalidOperationException: code = Misuse (21), message = 
System.Data.SQLite.SQLiteException (0x87EF): bad parameter or other API 
misuse

Failed to configure managed assembly logging.

   at System.Data.SQLite.SQLiteLog.Initialize(String className)

   at System.Data.SQLite.SQLiteConnection..ctor(String connectionString, 
Boolean parseViaFramework)

Kind Regards,
Barry Roberts.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd exception when creating a connection object

[Roberts, Barry]

Hi Joe,

We are not using multiple app domains within a process. 

Within the process where the errors are coming from we have

* N databases being accessed, typically between 5-100 separate databases
* Each database has a dedicated handler, to ensure we only ever invoke 1 writer 
at a time to a database.
* All communication to the database is going through the SQLite C# code, 
connections always via the SQLiteConnection instance.

The code follows the form of

Using(var connection = connectionFactory.CreateConnection())
{
Connection.Open();
   // do something useful in here.
}

There is 1 connection factory per database. The create connection call above 
does the following

   /// 
public IDbConnection CreateConnection()
{
EnsureExists(); // Create the file if not there.

return new 
SQLiteConnection(ConnectionStringBuilder.ConnectionString, true);
}

  [NotNull]
private DbConnectionStringBuilder ConnectionStringBuilder
{
get
{
var builder = new SQLiteConnectionStringBuilder
{
DataSource = m_Factory.DatabasePath(),
Version = 3,
DefaultTimeout = 60,
PageSize = m_ConnectionOptions.PageSize,
JournalMode = SQLiteJournalModeEnum.Persist,
BinaryGUID = true,
FailIfMissing = true,
SyncMode = m_ConnectionOptions.SynchronizationMode,
Pooling = m_ConnectionOptions.UseConnectionPool,
};

return builder;
}
}

Typically connection pooling will be on.

At application start, each of the database handlers will verify once that the 
table schema in the database is ok. In order to do that they first open a 
connection as per above, that is when the exception tends to occur. The 
application internally is multi-threaded, each of the database handlers will 
execute concurrently (they are scheduled onto the thread pool), so many 
connection objects will get instantiated very close to each other time wise.

Unfortunately the problem seems to be very difficult to replicate reliably. 
Generally we are seeing around about a 5% failure rate at application start. 
Any further info, ask away.

Hope that helps,
Barry Roberts. 

Message: 7
Date: Tue, 14 May 2019 16:34:16 -0400
From: "Joe Mistachkin" 
To: "'General Discussion of SQLite Database'"

Subject: Re: [sqlite] Odd exception when creating a connection object
Message-ID: <3EA5A76974A44B60A3EC32B67C8C6D5A@LACHRYMOSE>
Content-Type: text/plain;   charset="us-ascii"


Barry Roberts wrote:
> 
> InvalidOperationException: code = Misuse (21), message = 
> System.Data.SQLite.SQLiteException (0x87EF): bad parameter or other
API 
> misuse
> 

Are you using multiple AppDomains?  Do you have other threads in the process
accessing SQLite via its native API while the connections are being created?

Any details you could provide in order to reproduce this could be useful.

--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ADO.NET driver upgrade puzzle

[Roberts, Barry (FINTL)]

Hi,

We have a C# application which logs data in real-time to sqlite files. The 
application is multi-threaded, and typically logs 200-500 data streams 
simultaneously. The logging rate per stream is 1Hz to 1000Hz, depending on the 
type of data coming in. Each data stream is logged to its own db3 file. This 
means that there are generally 200-500 connections active, and we run with 
connection pooling on.  Each data stream typically buffers for 2-3 seconds then 
writes the buffer to the respective db3 file. All processing with the 
application is multi-threaded.

I am currently looking at updating the version of System.Data.SQLite we are 
running, but running into some issues. The application currently runs 1.0.80.0, 
and I am testing with 1.0.105.1, the code changes to our app were fairly 
minimal. The reason we were looking to upgrade is that performance profiling 
was showing a high degree of lock contention within the old driver code, 
primarily due to a static lock in the old driver. The newer driver uses locks 
based on the connection handle, which removes the lock contention we were 
seeing, which leads to a significant improvement in CPU usage.

When logging with the old driver there are no issues, other than the lock 
contention.
When logging with the new driver, the system sometimes locks up, or marks one 
or more of the db3 as malformed.

After much testing, this seems to occur when we prepare statements on the 
sqlite command. If I disable the IDbCommand.Prepare() calls, the driver appears 
to be stable. Structurally our code is fairly standard, and makes extensive use 
of "using" blocks. We only hold the connection and transaction open for the 
minimal time possible, releasing the connection back to the pool.

Using (connection)
Open connection
Using (begin transaction)
Using (create command)
Prepare insert command
Transfer data
Commit transaction

One of the stability tests I run involves 2 processes logging to the same data 
sets (db3) files, because we do have multi-process access onto the db3 files. 
Connections are configured to use journal mode "persist".

Should we not use prepare calls when running connection pooling, or do you have 
any other recommendations we should take note of.

Kind Regards,
Barry Roberts.

b.robe...@fugro.com | www.fugro.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADO.NET driver upgrade puzzle

[Roberts, Barry (FINTL)]

Hi Clemens,

As per my original post, all C# access code is making extensive use of "using" 
statements. However we do obviously rely on the connection pool being thread 
safe, because many threads are writing to different databases (connections) 
concurrently.

There is no direct relationship between threads and databases. However I do 
enforce that within a process only one thread can be writing to a specific 
database (because sqlite does not support parallel writing) at a time. All 
threads are running on the Pfx thread pool. As data is received from a device 
it is assigned to a specific data stream. If the buffer for the stream is then 
full a write action is assigned to the thread pool. When that runs it will call 
into the ADO.NET code, and send the data to the database for that connection. 
Here is some test code, that shows how we access the connections normally. 
Imagine that the "Insert" method is called whenever we have data to flush for a 
specific database, and there are normally 200-500 database connections active. 
Obviously at the end of the using statements the connection is effectively 
handed back to the connection pool, which is outside of my direct control.

public void Insert([NotNull] IEnumerable records)
{
ArgumentHelper.AssertNotNull(records, "records");
var sb = new StringBuilder();

sb.AppendLine("INSERT INTO");
sb.AppendLine(m_TableName);
sb.AppendLine("(identity, time, binary)");
sb.AppendLine("VALUES");
sb.AppendLine("(@identity, @time, @binary)");

using (IDbConnection connection = CreateConnection())
{
connection.Open();

using (IDbTransaction transaction = 
connection.BeginTransactionSerializable())
{
using (IDbCommand command = CreateCommand(connection))
{
command.Transaction = transaction;
command.CommandText = sb.ToString();
command.Parameters.Add(new SQLiteParameter("@identity", 
DbType.Int32));
command.Parameters.Add(new SQLiteParameter("@time", 
DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@binary",   
DbType.Binary));

if (m_Factory.Configuration.UsePreparedStatements)
{
command.Prepare();
}

var stream = new ByteStream();

IDataParameterCollection parameters = 
command.Parameters;
IDataParameter parameter01 = ((IDbDataParameter) 
parameters[1]);
IDataParameter parameter02 = ((IDbDataParameter) 
parameters[2]);

foreach (var record in records)
{
stream.Clear();
record.Serialize(stream);

parameter01.Value = record.TimeOfValidity.TaiTicks;
parameter02.Value = stream.GetContents();
command.ExecuteNonQuery();
}
}

try
{
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}

[NotNull]
   public IDbConnection CreateConnection()
   {
   var connection = new SQLiteConnection
   {
   ConnectionString = 
m_Builder.ConnectionString
   };

   return connection;
   }

[NotNull]
public IDbCommand CreateCommand([NotNull] IDbConnection connection)
   {
   IDbCommand command = new 
SQLiteCommand();
   command.Connection = 
connection.NotNull("connection");
   return command;
   }



Kind Regards,
Barry Roberts.

b.robe...@fugro.com | www.fugro.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADO.NET driver upgrade puzzle

[Roberts, Barry (FINTL)]

Hi,

I am aware of the sqlite FAQ, and especially the comments regarding file 
systems. We only ever use NTFS file system, and never have any FAT or FAT32 
file systems. Given that the old 1.0.80.0 driver is totally stable, and the 
newer one is not, on the same exact hardware, I do not think the issue is 
hardware related.

I enforce that the writer to a specific database can only run 1 at a time, this 
is done via the C# Interlocked mechanism which has always proved to be very 
robust. There is a separate logger object instance per database, and within 
each of those the writing is controlled with the interlock. This allows the 
application to buffer for longer if the writing to disk encounters a short term 
slow down.

As per Clemens suggestion I could replace the use of the connection pool and 
hold a connection open per database. The code can be structured to do that, 
because object instances are not shared between database logger instances. 
However I would have preferred to use the connection pool, because that allows 
me to open late/close early on my connection usage, which is generally better 
for maintenance and how the production code is currently strutured. I could 
restructure the code, but that is not really desirable in production code, 
simply due to an ADO.NET driver update.

I have been running my test application for days now without issues. This is 
configured with pooling on, statement preparation off. If I enable statement 
preparation the system will fail after an hour or two. So it looks like our 
production code needs to run the same, and remove the IDbCommand.Prepare() 
calls. The old driver works well but has lots of lock contention, the new one 
resolves that issue, but is not as stable in some configurations. It is a 
trade-off, I need the new driver to improve performance, but will trade off the 
command prepare calls, to enable driver stability.

Kind Regards,
Barry Roberts.
b.robe...@fugro.com | www.fugro.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is db size an issue?

[Roberts, Barry (FINTL)]

We have logged live data into databases that are over 200GB without any issues. 
I don't think SQLite will be the issue itself, as it is merely limited by the 
file system. The more likely issue is when you need to transfer or backup the 
file, it can get unwieldy if it is too large.

Kind Regards,
Barry Roberts.

b.robe...@fugro.com | www.fugro.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma integrity_check throwing exception

[Roberts, Barry (FINTL)]

Hi,

Assume I have a database which is "malformed" due to rowid xxx missing from a 
table index. In the System.Data.SQLite.dll 1.0.80.0 C# driver the following 
code would return the reason, I would get a list of the rowid problems allowing 
me to log them.

private static IEnumerable IntegrityCheck(SQLiteConnection 
connection)
{
using (var command = new SQLiteCommand(connection))
{
command.CommandTimeout = 0;
command.CommandText = "pragma integrity_check";

using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return reader.GetString(0);
}
}
}
}

However using a later driver such as 1.0.105.1 the ExecuteReader() call throws 
an exception saying the database is malformed. That is not very helpful and 
stops me running the integrity check. If I replace the integrity_check with 
quick_check it works and returns ok, so access to the file is ok, just some 
internal indexes are messed up. Is there any way to get the above working in 
the later drivers?

Kind Regards,
Fugro Intersite.

Barry Roberts.
b.robe...@fugro.com | www.fugro.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# pragma integrity_check call throwing exception

[Roberts, Barry (FINTL)]

Hi,

I asked the following question a few weeks ago, but did not get any responses, 
hoping someone may have an idea.

We are currently running System.Data.SQLite.dll 1.0.80.0 and would like to 
upgrade to a newer version. However there are a variety of issues we have run 
into, most of which I have now resolved, however one remains. Assume I have a 
database which is "malformed" due to rowid xxx missing from a table index. 
Using the 1.0.80.0 driver the following code would return the results from the 
pragma integrity_check call.

private static IEnumerable IntegrityCheck(SQLiteConnection 
connection)
{
using (var command = new SQLiteCommand(connection))
{
command.CommandTimeout = 0;
command.CommandText = "pragma integrity_check";

using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return reader.GetString(0);
}
}
}
}

Our system would get a list of the rowid problems allowing it to log them and 
inform the user. I am currently testing using the 1.0.105.1 driver, however the 
ExecuteReader() call (above) throws an exception saying the database is 
malformed. That is not very helpful and effectively stops the system obtaining 
integrity check information. If I replace the integrity_check with quick_check 
it works and returns ok, so access to the file is ok, just some internal 
indexes are messed up. Is there any way to get the above working in the later 
drivers, or can someone tell me which driver update changed this behaviour?

Kind Regards,
Fugro Intersite.

Barry Roberts.
b.robe...@fugro.com | www.fugro.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C# pragma integrity_check call throwing exception

[Roberts, Barry (FINTL)]

Simon,


> Your text makes it look like you think that that kind of corruption affects 
> only existing rows.  This is not the case.  If you continue to write to a 
> database which shows this problem, you can lose more of the existing rows 
> and/or the new data you're trying to write.  The proper reaction to any such 
> errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or 
> REINDEX then check that the data is still consistent and plausible.

I am aware that other issues may exist. If the integrity_check did not return 
"ok" we would then try and fix the issues using the command line tooling. 
Generally if/when there is an issue it is reasonably minor, and a VACUUM or 
REINDEX fixes it.  My concern was that the newer C# driver is showing different 
behaviour to the old driver when tested against the exact same sqlite file (I 
have unit tests covering this scenario).  I have to justify changing our source 
code because of it.

Keith,


> Have you run "PRAGMA integrity_check;" from the command line shell against 
> the same database and does it return rows or just throw the same exception?


Thanks for the suggestion, I have just run a test database that is "corrupted" 
using the latest sqlite3 download

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open entities-corrupt.db3
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> pragma quick_check;
ok
sqlite> REINDEX;
sqlite> pragma quick_check;
ok
sqlite> pragma integrity_check;
ok
sqlite>

So it looks like the actual engine is doing this now. This helps me justify 
altering our code base, to work with the revised behaviour.

Kind Regards,
Fugro Intersite.

Barry Roberts.
b.robe...@fugro.com | www.fugro.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users