fsprojects
Runtime System.Exception when ContextSchemaPath is defined
Description
When the ContextSchemaPath locally cached schema file is defined and present, SQLProvider returns System.Exception: ! when performing database operations at runtime.
Repro steps
-
Generate a local schema file with ContextSchemaPath as described in the documentation
-
Debug the project
-
Perform a database operation. The following exception is raised:
System.Exception: !
at [email protected](String _arg1)
at FSharp.Data.Sql.Providers.MySql.createParam[a](String name, Int32 i, a v)
at <StartupCode$FSharp-Data-SqlProvider>[email protected](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
What is the scenario that you want to run cached queries? Related to CI builds?
@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.
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?
@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.
@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 🙁
@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 ""?
#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?
@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.
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?
Faced with the similar problem. Without ContextSchemaPath it works well.My version is 1.1.49
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.