Application being migrated to MemSQL is permanently looking for a 'mysql' database


#1

Reposting from the public Slack channel for user Simon.

One of are applications that we wanted to move to MemSQL is permanently looking for a ‘mysql’ database. This tool doesn’t have a MemSQL driver, only MySql.

So I tried to create manually a “mysql” database which worked fine, but the tool is now looking for some table in this db, like the proc table (https://mariadb.com/kb/en/library/mysqlproc-table/) and probably some others.
We now had the idea to create views to get the same data out of the “memsql” database.

Unfortunately, MemSQL doesnt allow to create cross table views:
"ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'cross-database reference in view definition' is not supported by MemSQL."

Do you have any similiar use case where some tools are looking for a “mysql” database? If so, what was your workaround?


#2

I can tell you that cross-database views are a common feature request and they are on our roadmap. [I am a MemSQL product manager.]


#3

What connector or application are you using? I have seen this error from the .NET MySQL connector. For that, setting the connection option CheckParameters to false will solve the problem. Other connectors may have similar configuration settings.

If you happen to be using the .NET MySQL connector, see my answer below copied from my answer at https://stackoverflow.com/questions/52598354/memsql-error-1049-while-executing-procedure/52599458#52599458.

Try setting the connection option CheckParameters to false.

The .NET MySQL driver tries to query the mysql database to get information about the parameters of the stored procedure, and this won’t work with MemSQL.

From https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored.html:

When you call a stored procedure (in versions before the MySQL 8.0 release series), the command object makes an additional SELECT call to determine the parameters of the stored procedure. You must ensure that the user calling the procedure has the SELECT privilege on the mysql.proc table to enable them to verify the parameters. Failure to do this will result in an error when calling the procedure.

This is the default behavior, but it can be changed with a connection option:

From https://dev.mysql.com/doc/connector-net/en/connector-net-6-10-connection-options.html

CheckParameters , Check Parameters

Default: true

Indicates if stored routine parameters should be checked against the server.

and this option for older versions of the client:

UseProcedureBodies , Use Procedure Bodies , procedure bodies

Default: true

When set to true, the default value, Connector/NET expects the body of the procedure to be viewable. This enables it to determine the parameter types and order. Set the option to false when the user connecting to the database does not have the SELECT privileges for the mysql.proc (stored procedures) table or cannot view INFORMATION_SCHEMA.ROUTINES, and then explicitly set the types of all the parameters before the call and add the parameters to the command in the same order as they appear in the procedure definition.

This option was deprecated in Connector/NET 6.3.7 and removed in Connector/NET 6.10.4; use the Check Parameters option instead.