MySQL GetDataContext uses original DB even after switching connection strings

[AlfranKeith]

trafficstars

Hi, what am I missing here? Connection strings in config.yaml.

type ClientDb = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL,ClientConnString,ResolutionPath = ResoPath, 
                                        IndividualsAmount = IndivAmount, UseOptionTypes = UseOptTypes>

let ClientCtx= ClientDb.GetDataContext Config.CfgTestB.Db.ConnectionString

let Staff = ClientCtx.TestA.Staff |> Seq.toArray

The result of the query is from database TestA. I have checked the connection strings for errors.

Same schema databases. DB names are TestA and TestB same schema.

Initial ClientConnectionString is TestA.

Not quite sure as to why this is happening. Any help would be appreciated. Thanks.

[Thorium]

Hmm... Looks valid if Config.CfgTestB.Db.ConnectionString is a string... Try to add Owner parameter, will it make any difference?

type ClientDb = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL,ClientConnString,ResolutionPath = ResoPath, 
                                        IndividualsAmount = IndivAmount, UseOptionTypes = UseOptTypes, Owner="TestA">

let ClientCtx= ClientDb.GetDataContext Config.CfgTestB.Db.ConnectionString

let Staff = ClientCtx.TestA.Staff |> Seq.toArray

[AlfranKeith]

Thanks for the input. I think it may have been due to my using of the root user for both connection strings just with a different DB. Not really sure though, but once I switched it over to db specific users, it's gone and shown me this error message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM testa.staff as s' at line 1

So I think it's now pointing to the correct db but still referencing TestA instead of TestB Is this possibly a MariaDB thing?

[Thorium]

Shouldn't be, Can you tell the actual SQL syntax giving the exception?

FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %O")

I'm also using MariaDB. But SQLProvider does a lot of caching, and for that reason my initial recommendation was to use type ClientDb1 and type ClientDb2 to point different databases if you use both and switch the context on the fly.

But if you have a single-database-software just configuring to e.g. a debug and release databases, everything should be fine as you currently have.

[AlfranKeith]

Hi, I'm currently grabbing staff names, and this is the query being generated and an exception.

SELECT `staff`.`Name` as `Name` FROM testa.staff as `staff`

MySqlException: SELECT command denied to user 'dummyb'@'localhost' for table 'staff'

It is now using the TestB connection string, but it is referencing the TestA table.

What is it I am doing wrong? As I understand it, it's ClientCtx.TestA in the code due to the literal connection string and needing to feed it the DB schema, but I thought it would resolve to use the proper Table name for generated SQL once I passed in a runtime connection string.

Sorry, new to all this. I'm basically just doing discovery for a new project using Suave.

The db connection will need to change depending on the office of the logged in user, which is why I need the switching.

[Thorium]

Ok I think this is a MySQL-only-problem in SQLProvider: usually database tables are listed as schema.tablename or owner.tablename but as there is no schema nor table-owner concept in MySQL, it uses database name as schema ( https://stackoverflow.com/a/11618350 ) and that's why this is happening.

[Thorium]

Looking from the source code, Owner-parameter has to be empty for this scenario.

[Thorium]

...and verified that this is an issue.

[AlfranKeith]

Thank you for the help. I've decided to switch to PostgreSQL (with Npgsql 3.1.10) and things have been working fine.

[rmunn]

The problem

I'm running into the same problem: I have dbMain and dbAlt databases in MySQL with the same "shape" (same tables, same columns, etc. -- trying to avoid using the word "schema" here to avoid confusion). In the API I'm building, there's a query parameter that allows you to select from the alternate database instead of the default main database. All my query code looks like this:

let ctx = sql.GetDataContext connString
query {
    for user in ctx.dbMain.Users do
    join mail in !! ctx.dbMain.EmailAddresses on (user.Id = mail.UserId)
    select (user, mail.Address)
}

When I run a query using the connection string "Server=localhost;Database=dbMain;Uid=...;Pwd=..." everything works as expected. But when I run the query using the connection string "Server=localhost;Database=dbAlt;Uid=...;Pwd=...", the generated SQL still looks like:

SELECT `user`.`id` as '`user`.`id`', ... ,`user`.`updated_on` as '`user`.`updated_on`',`mail`.`address` as '`mail`.`address`' FROM dbMain.users as `user` LEFT OUTER JOIN  `dbMain`.`email_addresses` as `mail` on `user`.`id` = `mail`.`user_id` 

(With many columns trimmed to make that shorter). Note how the schema is still dbMain. For most user accounts this didn't make a difference, but some user accounts have different email addresses in the main and alternate databases, and I can only get the main DB's addresses out of my query even when the connection string specifies the alternate DB.

Failed workaround attempts

If I add an Owner = "dbMain;dbAlt" parameter to my SqlDataProvider definition, then I get two sets of table definitions available to my queries: ctx.dbMain.Users and ctx.dbAlt.Users, but they are treated as two different types that are not compatible with each other (which does make sense; after all, just because two databases have tables with the same names and column definitions does not mean that I want to treat them interchangeably; one database might be admins and one might be unprivilegedUsers and it would be a security hole to write a query against unprivilegedUsers that should have been written against admins).

I have not, so far, been able to find an SqlProvider parameter that lets me say "Do NOT add a schema name to tables internally; instead, treat both dbMain.Users and dbAlt.Users as having an empty schema, and use plain users as the table name instead of dbMain.users". Is there any way to do that? Unfortunately for me, moving from MySQL to a better database like Postgres is not an option in my case, as I have to support legacy code that was written in MySQL-only fashion.

Proposed solution / new feature

The only solution I can see for my use case, short of dropping SqlProvider and rewriting all my queries manually in SQL, is to have some control over the schema name that SqlProvider will use. For example, it would be nice (for me at least) to be able to specify an Owner = "" parameter to SqlProvider (as distinct from not specifying the Owner parameter at all). By explicitly specifying an empty string in the Owner parameter, I'd like the schema to be blank, and the data type to be called sql.dataContext.usersEntity instead of sql.dataContext.dbMain.usersEntity.

Now, maybe my problem (two MySQL databases, and can't convert to Postgres) is rare enough that it's not worth putting in the extra work to solve it. If so, that's fair enough, and I'll just convert my code to write explicit SQL instead of using SqlProvider. But I'd like to at least propose this feature idea and see if it makes sense for anyone else's use case and/or would cause any problems elsewhere in the SqlProvider code.

[Thorium]

Another workaround would be running the dbAlt on a separate instance (machine/server) as another main.

[Thorium]

The idea of SQLProvider is be strongly typed, to not cause errors in SQL-queries, but how can strong typing expected between different databases? How would we know that the shape of alt is the shape of main?

[rmunn]

In theory, SQLProvider could read the table schemas, compare them, and say "Hey, dbMain.Users has the same column definitions as dbAlt.Users, we can make them the same type". In practice it might not be practical to do all that comparison. So it might not work to have the data type be sql.dataContext.usersEntity without a dbMain in there.

BUT... I'd still like for the MySQL connector (at least) to be able to take a parameter that says "Make the schema name blank", so that SQL queries refer to SELECT ... FROM 'users' rather than SELECT ... FROM 'dbMain'.'users'. If it did just that much, I could still have the dbMain.usersEntity data type, and I could write my queries to run against a "Database=dbAlt" connection string with the dbMain data type. The onus would be on me to make sure the database I was connecting to was type-compatible with the database I wrote the queries against... but that's already the case, isn't it? After all, I don't do my dev work on the production box. So I'm already giving SqlProvider one model database to build types from, then running GetDataContext with a connection string for a different database in production. So I don't think it's too different to want to say "Hey, SqlProvider. I want to run these queries against two different connection strings from two different databases, without having the database name in the SQL that you build. I promise they're type compatible."

With a different database like Postgres, I could do that by having a dbMain schema inside the dbAlt database, which would ensure that SqlProvider built SQL that will work for me. With MySQL, my only choice is to have a way to tell SqlProvider "Please don't include the "schema" name (really the DB name) in the SELECT query this time; I'm voluntarily opting out of some of the type safety that would provide."

[rmunn]

In other words, I'd like my queries to still be built against dataContext.dbMain.Users, which is not what I wrote in my first comment. (I was writing in haste at the time I finished up that comment, and got it slightly wrong in the explanation of the feature I was asking for). I don't need the schema name left out of the F# code. But I do need the generated SQL to be able to run against different databases, and the only way to do that in MySQL is to have the schema name left out of the generated SQL so I get SELECT ... FROM users rather than FROM dbMain.users.

[rmunn]

As for the workaround of running dbAlt on a separate instance, I considered that, but the devops guys are saying "One instance is already pushing our Amazon instance's memory limits pretty hard; we don't have the resources to run two MySQL database instances on the same sever, and we don't want to spend the money for a second server just for this one relatively-small project. Can't the library you're using just leave off the schema name in the SQL queries?"

So while that would be a totally viable solution, it's not one that's available to me, I'm afraid.

[Thorium]

Yes, sounds fair. :-)