Runtime System.Exception when ContextSchemaPath is defined

[keyset]

Description

When the ContextSchemaPath locally cached schema file is defined and present, SQLProvider returns System.Exception: ! when performing database operations at runtime.

Repro steps

1. Generate a local schema file with ContextSchemaPath as described in the documentation

2. Debug the project

3. Perform a database operation. The following exception is raised:

System.Exception: !
   at FSharp.Data.Sql.Providers.MySql.findClrType@91-1.Invoke(String _arg1)
   at FSharp.Data.Sql.Providers.MySql.createParam[a](String name, Int32 i, a v)
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MySql.clo@350-280.Invoke(Tuple`2 tupledArg)
   at Microsoft.FSharp.Collections.SeqModule.Fold[T,TState](FSharpFunc`2 folder, TState state, IEnumerable`1 source)
   at FSharp.Data.Sql.Providers.MySqlProvider.createInsertCommand(IDbConnection con, StringBuilder sb, SqlEntity entity)
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MySql.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@949-3.Invoke(SqlEntity e)
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)
   at FSharp.Data.Sql.Providers.MySqlProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities, TransactionOptions transactionOptions, FSharpOption`1 timeout)
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.f@1-69(SqlDataContext __, IDbConnection con, Unit unitVar0)
   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()
   at MyProject.DoDatabaseStuff(DbRow _row) in C:\Users\Me\Code\MyProject\Db.fs:line 65
Stopped due to error

Expected behavior

The type provider should not attempt to load the ContextSchemaPath at runtime since it is not a data source and will never work. It should only load at design-time and compile-time to allow development and compilation (build server) without a full-time database connection.

Known workarounds

Workarounds are to either not use the ContextSchemaPath at all, or delete the cached schema file each time debugging is needed.

Related information

• Used database: MySQL 5.6
• Operating system Window 10 version 1803
• Branch: 1.1.42
• .NET Runtime: 4.5.2
• Performance information, links to performance testing scripts : N/A

[Thorium]

What is the scenario that you want to run cached queries?
Related to CI builds?

[keyset]

@Thorium I'm confused by your questions. I'm not trying to run a cached query, nor am I specifically using CI. I'm just trying to use the cached provided types without affecting runtime. If there's more information or clarification I can provide, let me know.

[Thorium]

I'm just wondering why would you execute the offline code at runtime?
If your database is offline, what would you benefit to execute the code anyway?

[piaste]

@Thorium

The offline code, that is the line SchemaCache.LoadOrEmpty which looks for the JSON file and loads it, gets executed at runtime anyway, that's the problem.

I encountered the same issue - I have tried a simple patch but it's having odd side effects on Mono, if it's a blocking issue for @keyset I could submit the PR anyway though.

[keyset]

@piaste I tried that same code change, but that context is used design- and compile-time as well despite the runtime file name. I haven't been able to figure out a working solution 🙁

[piaste]

@keyset Yeah, I finally figured out the same thing last night. The 'odd behaviour on Mono' I had encountered was just that the cache had been disabled and the MSSQL instance unavailable from the CI server 🤦‍♂️ The fact that createProvider is called once in the runtime files and once in the design time files misled me, I thought design time would only run through the latter.

Maybe the correct way is to look at the invocations defined in https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/SqlDesignTime.fs#L729, and only the runtime ones (the ones taking runtimeConnStr?) need to have the contextSchemaPath argument set to ""?

[Thorium]

#L729: By the way I hate that method as adding parameters causes combinatorial explosion.
Any ideas for better parameter-handling? Some kind of record with default values maybe?

[piaste]

@Thorium

Well, yes. We can just generate all possible combinations of explicit and default parameters. Currently that makes for 32 overloads, which is still tolerable - as in, doesn't seem to slow down Visual Studio.

Take a look: it currently tests green. Had to introduce a magic number for cmdTimeout because I couldn't deal with the Expr type testing. Also the bitflag is very un-idiomatic, but I find it simpler than a generator function.

This should also solve the issue (passes empty string to contextSchemaPath at runtime), but I need to test that.

EDIT: I realized you probably disliked the explosion in overloads as much as the code smell from all that duplication. If we're changing the signature altogether, I think connectionString definitely needs to stay as a separate parameter: that would spare a breaking change to many basic users who don't use any other parameters. transactionOptions could deserve a parameter slot as well. The other arguments can certainly be stuffed into an options object if desired.

[Thorium]

I fixed the stored procedures to work with ContextSchemaPath.

But another problem I found out was that when I have a two-project-solution, only the active project's schema is saved when I call the save method.

I could deal with this by saving 2 files separately and merging them somehow. How should this work? E.g. Save as is, but when loading, having a semicolon-separator in the context-schema-path static parameter?

[dohly]

Faced with the similar problem. Without ContextSchemaPath it works well.My version is 1.1.49

[Thorium]

I fixed my problem by calling design-time SaveContextSchema() for both projects separately (changed the ContextSchemaPath between) to generate 2 different files and then I used F# interactive to call
FSharp.Data.Sql.Common.OfflineTools.mergeCacheFiles to merge the files and started to use the merged file for ContextSchemaPath.