Friday, May 1, 2009

Largely unknown connection string parameters in the MySQL .NET Connector

I've recently had some problems finding a way to use user-variables in the MySQL .NET Connector, and after looking in the source code I found a connection string parameter which would allow this, even though I could not find it documented anywhere. To help others with these largely undocumented and therefore largely unknown connection string parameters, I'll supply this list of them here.

Allow User Variables
Values: True or False
Default value: False
Should the provider expect user variables to appear in the SQL statements.

Use Affected Rows
Values: True or False
Default value: False
Defines if the returned affected row count should reflect affected rows instead of found rows.

Functions Return String
Values: True or False
Default value: False
Defines if all server functions should be treated as returning strings.

Logging
Values: True or False
Default value: False
When set to True diagnostics messages are printed to the Trace stream.

Use Usage Advisor
Values: True or False
Default value: False
If True inefficient database operations are logged to the Trace stream. Inefficient queries are queries which:
  • Doesn't read all rows in a resultset.
  • Aren't using any indexes.
  • Are using a bad index.
  • A rowset being accessed using SequentialRead and had to load all remaning columns.
  • If all columns in a query weren't accessed.
  • If you are performing an unnecessary conversion.


Driver Type
Values: Native, Client or Embedded
Default value: Native
Specifies the type of driver to use for the connection. Native means using TCP/IP, Client means using the client library and Embedded means using an embedded MySQL server.

Allow Zero Datetime
Values: True or False
Default value: False
Defines if zero datetime values are supported.

Convert Zero Datetime
Values: True or False
Default value: False
If set to True illegal datetime values are automatically converted to DateTime.MinValue.

Procedure Cache Size
Values: An integer
Default value: 25
Indicate how many stored procedure can be cached at one time. Settings this value to zero disables the procedure cache.

Use Performance Monitor
Values: True or False
Default value: False
Specifies if Windows performance counters should be updated during execution.

Ignore Prepare
Values: True or False
Default value: True
Setting this instructs the provider to ignore any attempts to prepare a command.

Use Procedure Bodies
Values: True or False
Default value: True
Indicates if stored procedure bodies will be available for parameter detection. Settings this to True speeds up reading information about procedure and functions schemas.

Auto Enlist
Values: True or False
Default value: True
Specifies if the connection automatically should enlist in an active connection, if there are any.

Respect Binary Flags
Values: True or False
Default value: True
Indicates if binary flags on column metadata should be respected.

Treat Tiny As Boolean
Values: True or False
Default value: True
Should the provider treat TINYINT(1) columns as boolean.

Interactive Session
Values: True or False
Default value: False
Specifies in the session should be considered interactive. Which basically means that the server should treat this like a command line session, and use a different timeout, interactive_timeout, instead of wait_timeout.

All these variables works in the newest version of the .NET Connector, 6.0, but most of them also works in version 5.1 and 5.2.