Ignore unknown system variables from `DATABASE_URL`

[dossy]

I'm using mysqljs/mysql for my MySQL database client library, and when using the DATABASE_URL connection string, setting additional configuration options are implemented as query parameters on the URL.

For example, mine look like this:

DATABASE_URL=mysql://user:pass@host:port/database?connectionLimit=100&supportBigNumbers=true

Apparently, dbmate chokes on those configuration options:

$ bin/dbmate up
Error: Error 1193: Unknown system variable 'connectionLimit'

For now, I can strip off those bits and dbmate works great, but it would be super convenient if dbmate itself could just ignore the options that it doesn't know about and/or doesn't recognize, instead of throwing a fatal error.

...

After spending a few minutes looking at the code, I'm starting to suspect that the error is really coming upstream from dbmate, from go-sql-driver/mysql, when dbmate tries to open the connection on line 70 of pkg/driver/mysql/mysql.go.

Yup, looks like go-sql-driver/mysql just takes the parameters from the database URL and blindly passes it along to MySQL, which is generating the "Error 1193" response.

Oh well, I guess I'll just have to trim those from the DATABASE_URL when invoking dbmate, but maybe someone has a clever idea to solve this in a better way, so I'm going to open this issue anyway, just in case. At least this way this is documented somewhere, and might benefit someone else who runs into the same issue and wonders why ...

[amacneil]

Hmm, that's annoying. So is the connectionLimit param specific to mysqljs?

Dbmate is designed to "just work" with your existing connection url, so I could be convinced that we should just drop any params like this which are used by common libraries if MySQL is going to choke on them anyway.

[dossy]

So is the connectionLimit param specific to mysqljs?

Yes, as far as I can tell.

I suppose having a list of all the actual valid MySQL session variables, and removing any from the parsed database URL before passing it along to go-sql-driver/mysql through sql.Open(), would be a bit of a maintenance headache if MySQL adds or removes variables in future versions, but it would certainly enhance the dbmate developer/user experience, and that list of variables likely won't change that frequently to really make this a maintenance issue.

[dossy]

Actually, on second thought, dbmate probably shouldn't do this; suppose you actually DO want to pass a parameter down to go-sql-driver/mysql which implements its own set of parameters to configure the connection ... then if dbmate filtered those out of the DATABASE_URL, then users of dbmate wouldn't be able to control the underlying go-sql-driver/mysql connection.

Yeah, I think in this particular case, leaving the responsibility to the dbmate user to ensure that the DATABASE_URL only contains parameters that both go-sql-driver/mysql understands or is a valid MySQL server variable is the best actual behavior.

Or, if you really want to get fancy, dbmate could filter the parameters to only the list which consists of the union of parameters recognized by both go-sql-driver/mysql and the MySQL server variables ...

[amacneil]

The other option is instead of a list of supported parameters, we could just strip this one parameter and any others that people come across. That way you will still get an error for a typo etc, but for a known issue like this one we can just ignore it. It seems unlikely that MySQL itsef would ever have a connectionLimit option like that.

[gregwebs]

yeah, I would close this. dbmate should do as little manipulation as possible of the env variables.

[dossy]

yeah, I would close this. dbmate should do as little manipulation as possible of the env variables.

I agree, go-sql-driver/mysql should really just silently ignore parameters that it doesn't recognize, or raise them as warnings and not errors, to help users identify when they've typo'ed a parameter vs. including one that is meant for a different consumer of the connection string.