dotConnect for MySQL Frequently Asked Questions
What is the difference between the Standard and Professional editions?
The Standard Edition represents a fully-featured ADO.NET data provider with design time support and advanced classes. The Professional Edition adds more classes, tools, technologies, and integration capabilities. The following list briefly enumerates the main advantages of Professional Edition.
- TheEntity Framework support allows you to employ the latest conceptual technology from Microsoft.
- TheLinqConnect support allows you to focus on objects instead of relational data in database application development.
- TheMySqlLoader class serves to load external data into the MySQL database extremely fast.
- TheMySqlDump class serves to store a database or its parts as a script and to restore database from the generated script.
- TheDataLink class serves for universal cross-form data binding.
- The powerful MySqlDataTable component as an all-in-one table data access solution.
- TheMySqlDataSet class allows using typed and untyped provider-specific datasets.
- DataSet Wizard greatly simplifies the process of generating datasets in your application.
- DataSet Manager and DataSet Editor help managing datasets.
- Support for dbMonitor that performs the per-component tracing of database events such as SQL statement execution, commit, rollback, etc.
- TheEnterprise Library support allows you to take advantage of the Data Access Application Block functionality.
- TheASP.NET providers support allows using the dotConnect for MySQL in ASP.NET provider models.
- Windows Workflow Foundation support includes MySQL Workflow Instance Store and Workflow Tracking
- Sync Framework support allows using dotConnect for MySQL for synchronizing MySQL data with other data sources.
To test this functionality, use the Trial Edition, which incorporates all aforementioned features.
What is the difference between the Professional and Developer editions?
The Developer Edition includes everything from the Professional Edition plus the Standard Edition of dbForge Fusion for MySQL, an advanced add-in for designing databases and easily manipulating data and schema objects from your IDE. dbForge Fusion for MySQL greatly speeds up the development process every time you have to interact with your database.
What is the difference between the Express and Standard editions?
Express Edition is a free data provider with basic connectivity features. Standard Edition represents a fully-featured ADO.NET data provider with design time support and advanced classes.
The following list briefly enumerates the main advantages of Standard Edition.
- SSL and SSH support allows you to establish secure network connections
- The Compression protocol is used to reduce network traffic
- Embedded Server can be used by applications that do not require multi-user work with data
- Connection through proxy servers can be used to connect to server in another network
- Connection through HTTP tunnel can be used if you need to connect to a client machine shielded by firewall that allows HTTP connections
- The MySqlScript class serves to execute series of SQL statements separated by special symbols
- The MySqlMonitor class monitors dynamic SQL execution in applications that use dotConnect for MySQL
- The MySqlDependency class tracks changes on the server
- The MySqlSelectStatement class represents the MySQL SELECT statement
- Design-time features allow you to move the development process from run time to design time, which is way more comfortable
- DotNetNuke support allows DotNetNuke to work with MySQL server
- The InterForm technology allows referencing components that reside on different forms of a WinForms application
- Migration Wizard helps to migrate your project from Microsoft ODBC and OLEDB data providers to dotConnect for MySQL.
Installation and deployment
I installed dotConnect for MySQL trial, then I have bought and installed the purchased version, but something went wrong.
Make sure you have uninstalled the trial version correctly. There should be no old (trial) assemblies present in your system. Uninstall dotConnect for MySQL and check your Global Assembly Cache and local folders for Devart.* assemblies. Remove all Devart.* files and reinstall dotConnect for MySQL.
How do I upgrade to a newer version of dotConnect for MySQL?
Close all running IDEs and help collections, then run the installation package of the newer version. You will be prompted to uninstall the previous version; agree and then proceed to installing the newer build.
You say, there are sample projects, but I can't find any.
Probably you installed dotConnect for MySQL not in its typical mode. Please select typical when installing the product. All samples are installed by default in the \Program Files\dotConnect\MySQL\Samples folder.
Are there any restrictions for Visual Studio Express editions?
Yes, there are some. First, the provider add-in (and hence the menu item) is not available. Second, components are not registered in the toolbox automatically (though you can do it manually). Finally, advanced Visual Studio integration (DDEX ) is not available, except Microsoft Web Developer Express 2008 and 2010.
How do I deploy my application?
To deploy an application written with dotConnect for MySQL, you need to:
- deploy the Devart runtime assemblies used by your application;
- include the technical license resource;
- in case your application uses provider factories, you also need to expose dotConnect for MySQL to the system.
How to install dotConnect for MySQL on a machine that does not have Visual Studio or Delphi installed?
Select the "Minimal" type when prompted by the installer.
How do I upgrade my projects to dotConnect for MySQL 5.00?
In the 5.00 release there were some important name changes in the product. Particularly,
assemblies, namespaces, invariant name, and some types were changed as follows:
"CoreLab.Data" became "Devart.Data";
"CoreLab.MySql" became "Devart.Data.MySql";
"MySqlDirectFactory" became "MySqlProviderFactory";
Invariant name became "Devart.Data.MySql";
Your projects must be updated to be compatible with dotConnect for MySQL 5.0. Here
is the list of files you should update:
*.csproj, *.vbproj - references to assemblies
*.cs, *.vb - namespaces
*.licx - namespaces and assembly names
Web.config - assembly names and web provider types
This is done automatically when you invoke the Upgrade wizard from the Visual Studio menu: Tools | MySQL | Upgrade Wizard.
I have received a message that Devart.Data is incompatible...
Devart data providers use a common assembly (Devart.Data.dll), so it is necessary to install compatible providers versions. We release our ADO.NET data providers in a single pack, thus releases from the same date should be compatible. Also, Devart data providers should warn you before installation if an incompatible version of Devart.Data.dll was detected.
I have received the following message - "error 0175: The specified store provider cannot be found in the configuration, or is not valid..". What does it mean and what should I do?
You should put the Devart.Data, Devart.Data.MySql, and Devart.Data.MySql.Entity.EF<Version> assemblies in the Bin folder of your application (or place these assemblies in the Global Assembly Cache). The <Version> here is the version of Entity Framework used in your application. For Entity Framework v1 the assembly is called Devart.Data.MySql.Entity.EF1.dll, for Entity Framework v4 it is Devart.Data.MySql.Entity.EF4.dll, for Entity Framework v5 and v6 it is Devart.Data.MySql.Entity.EF5.dll or Devart.Data.MySql.Entity.EF6.dll respectively. For Entity Framework Core it is Devart.Data.MySql.Entity.EFCore.dll. Make sure that your application is licensed correctly (take a look at the Licensing article for more information).
Information about provider registering is available in the Deployment article.
Subscriptions and Upgrades
I have a registered version of dotConnect for MySQL. Will I need to pay to upgrade to future versions?
Since dotConnect for MySQL 4.00, all upgrades to future versions are free to users with an active dotConnect for MySQL Subscription.
Users that have a registration for versions of dotConnect for MySQL prior to MyDirect .NET 4.00 can upgrade to dotConnect for MySQL 5.00 to jump in on the Subscription Program.
What are the benefits of the dotConnect for MySQL Subscription Program?
Users with a valid dotConnect for MySQL Subscription get the following benefits:
- Access to new versions of dotConnect for MySQL when they are released
- Access to all dotConnect for MySQL updates and bug fixes
- Product support through the dotConnect for MySQL Priority Support program
- Notification of new product versions
Priority Support is an advanced product support program which offers you expedited individual assistance with dotConnect for MySQL-related questions from the dotConnect for MySQL developers themselves. Priority Support is carried out over email and has a two business day response policy.
The dotConnect for MySQL Subscription Program is available for registered users of dotConnect for MySQL 4.00 and higher.
Can I use my version of dotConnect for MySQL after my Subscription expires?
Yes, you can. dotConnect for MySQL version licenses are perpetual.
Do I need a new subscription to use dotConnect for MySQL 8?
No, you don't. Provided that you have an active subscription, you will be able to download dotConnect for MySQL 8 from Customer Portal.
What is the licensing you use?
dotConnect for MySQL uses .NET component licensing. To build an operational application, dotConnect for MySQL requires a valid license. If no license is available, dotConnect for MySQL does not work.
How to embed the license into my application?
Usually you do not have to care about embedding the license in your application.
When you install dotConnect for MySQL, the system is configured so that licensing
is done transparently. The license is automatically added to the project when you
place a MySqlConnection component onto a form.
In some cases, you have to manually add the license. It is necessary for console applications, class libraries, some ASP.NET applications and existing projects initially built with a dotConnect for MySQL version that did not use component licensing, that is, 3.20 and older. For instructions on how to add the license manually, refer to the Licensing topic in the dotConnect for MySQL help.
On opening the connection, I get an exception saying "License not found...". What should I do?
This generally indicates that license information cannot be found, or you try to apply it in some way that does not suit the kind of application. For example:
- The licenses.licx file, required for dotConnect for MySQL to function properly, cannot be found.
- The licenses.licx file is not added as an embedded resource.
- The App_Licenses.dll file is not found in ASP.NET applications.
To fix the problem, determine the type of your application and read the corresponding section of the "Licensing" topic.
On opening the connection, I get an exception saying "License not valid...". What should I do?
This means that license information is found but it is incorrect. It usually happens in the following cases:
- The project was earlier compiled with an old or trial version of dotConnect for MySQL.
- The licenses.config file used by a class library does not refer to the launched executable.
- The App_Licenses.dll file belongs to other application or needs to be recompiled.
- Something is wrong with the operating system or installation of dotConnect for MySQL.
If you encounter one of these problems, do the following (the numbers of actions correspond to the numbers of the problems in the above list):
- Delete all files from the bin and obj folders and rebuild the project.
- Specify the executable in the licenses.config file.
- Rebuild App_Licenses.dll for the current application.
Perform the following steps:
- uninstall dotConnect for MySQL;
- remove all Devart.* and policy.*.Devart.* files from the GAC;
- clear the Program Files\Devart\dotConnect and Program Files\Common Files\Devart\dotConnect folders;
- re-install dotConnect for MySQL.
Deployed application worked fine some time, but at some moment it started to say "Sorry, your trial period has expired". But I do not use trial version any more.
Probably, you compiled the application with the Trial edition of dotConnect for MySQL, and deployed it with assemblies from a non-trial version. In this case, time limit is actual as well. To eliminate the problem, just recompile the project.
How to license ASP.NET applications?
To support server-side compilation you ought to have the special assembly App_Licenses.dll in the Bin directory of your site. To create this assembly, execute the Build Runtime Licenses command in the licenses.licx file context menu in the Solution Explorer view. The required assembly will be created automatically.
Note that this is not necessary for precompiled ASP.NET applications (if nothing is compiled on the server by user request). Precompiled applications are licensed as usual applications.
How to make dotConnect for MySQL work with SQL Server 2008 Business Intelligence Solutions?
To do this, install dotConnect for MySQL on the same computer where SQL Server 2008 resides.
I am having problems with the licensing in my project... What am I to do?
If any problems with licensing occurred in your project, you should use the License Information dialog to resolve them. You can access it from the Visual Studio menu: Tools | MySQL | License Information.
Do end-users need a license?
No, end-users of your application do not require any license.
I call MySqlConnection.Open(), then Close(). The physical connection is still visible by the server. Isn't it a bug?
No, it is not. This is the Connection Pooling feature. The actual connection is not closed in order to be used later by your application. This boosts performance greatly.
I get the following exception: "Timeout expired. The timeout period elapsed..." What to do?
The full text of the exception is "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." It clearly states that you have run out of connections available. To solve the problem, increase the Max Pool Size connection string parameter value. The default value is 100. Another way to get extra connections is to clear one or all of the pools. Or you can turn connection pooling off using the Pooling connection string parameter.
Method MySqlConnection.Open() succeeds, but MySqlCommand.Execute() leads to exception. How to check the connection for validity?
This happens because connections in the pool may become invalid for some reasons. Use the MySqlConnection.Ping() method to ensure that the connection is alive.
How to prevent possible pooling problems?
You can clear the pool explicitly. Generally, the pool is cleared automatically when connections are idle or closed by the server. To force the operation, call the ClearPool or ClearAllPools methods. Another solution is to disable the pool completely (set Pooling=false in the connection string). If this does not help, the problem is not caused by pooling.
How to open documentation on dotConnect for MySQL?
There are several ways to open dotConnect for MySQL documentation:
- Use the appropriate shortcut in the Start menu, for instance, Start - Programs - Devart dotConnect for MySQL - Documentation .
- Use the following command in the IDE: Tools - MySQL - dotConnect for MySQL Help .
- Place the cursor on some class from Devart.Data.MySql and press F1.
There is also the same documentation in the chm format. You can download it from our web-site at the following address: www.devart.com/dotconnect/mysql/dcmysql.chm
I can't see the documentation in Visual Studio Help Collection.
Correctly installed documentation appears as a separate node in the combined collection of Visual Studio and Borland Delphi. If you can't find the dotConnect for MySQL node there, it is because topics filtering is enabled. To reveal the node, set the filter to "Devart Documentation", or "(unfiltered)", or "Visual C#", etc.
Another possible reason is that you installed some IDE after the installation of dotConnect for MySQL. In this case, use aforementioned methods to access documentation.
All topics in the CHM file are blank.
This can happen due to some patches from Microsoft installed on your system. Please read this page to find possible solutions to the problem. In most cases, it is enough to right-click the CHM file, select Properties, click the "Unblock" button and click "Apply" to show the contents.
Are LINQ to SQL and Entity Framework supported?
The Professional Edition of dotConnect for MySQL supports both LinqConnect (formerly called LINQ to MySQL) and the latest version of ADO.NET Entity Framework.
Where do I start with Entity Framework or LinqConnect?
For your convenience, there are brief tutorials that you can use to get acquainted with the technologies:
Are there any demo projects to play with?
Yes, dotConnect for MySQL includes several Entity Framework and LinqConnect samples in C# and Visual Basic. The samples demonstrate how to use several entities and a relationship between them. You can also download a separate Entity Framework Query Samples package, which is a standard Microsoft demo with added MySQL connectivity.
How to obtain the autogenerated value of an autoincrement field after the INSERT statement?
There are several ways to do it:
- Use the MySqlCommand.InsertId property.
- Execute the SELECT last_insert_id() statement.
I get a concurrency error when modifying records using MySqlDataAdapter or MySqlDataTable. What can be wrong?
This can happen when MySqlCommandBuilder generates a query without a primary key field. To avoid it, use a custom update command instead of the one generated by MySqlCommandBuilder. This problem should not occur if the primary key field participates in the SELECT statement.
No components can be found in the toolbox.
This can happen if the installation encountered some problems. You can add the components to the toolbox manually. This procedure is described in the Installation article in the documentation. Note that the Microsoft Web Developer 2008 Express edition does not allow working with the toolbox at all.
dotConnect for MySQL components in Visual Studio .NET 2008 are grey...
Make sure you're in the Component Designer view. Do not confuse with the Designer view. To switch between the views, use the context menu of the .aspx file in Solution Explorer. This relates to Web projects only.
dotConnect for MySQL says it uses a different version of assemblies.
Delete all the policy files used by dotConnect for MySQL from the GAC.
SQL Server Integration Service (SSIS) can't get the list of tables in my MySQL server.
Can I use dotConnect for MySQL assemblies in the x64 process?
dotConnect for MySQL assemblies are built with the /platform:anycpu option. So, you can use the provider's assemblies on any platform (x86 or x64). For more information, refer to https://msdn.microsoft.com/en-us/library/zekwfyz4.aspx
General issues related to MySQL server
It looks like MySQL server does not process BLOB fields that are bigger than some limit...
The amount of data that can be transferred in single query is determined by the
server variable max_allowed_packet. By default, this value is approximately
1 MB, which means that you can download or upload a BLOB value with the size of
1MB maximum. To increase this limit, assign a greater value to the max_allowed_packet
variable, for instance:
This fragment of the my.ini file sets the max_allowed_packet value to 8 megabytes.
I get the "Net packets out of order" message. What is this?
This means that you try to use incompatible server and client versions, which can
happen when you're not in the Direct mode (the MySqlConnection.Direct property
is false). Check that the libmysql.dll library corresponds to your server.
Another reason why you can receive this message is having some general troubles with the network. Check network segments and configurations.
I get the "Commands out of sync" message. What is this?
This message is displayed when you do not close MySqlDataReader and try to execute another query on the same connection. To avoid the problem, you can apply either of the following methods:
- Close MySqlDataReader.
- Close MySqlConnection.
- Set the MySqlCommand.FetchAll property to true.
Cannot connect to server on %host name%...
This is actual for the mobile edition. Use the IP of the server instead of its DNS name. It is recommended to use the internal IP address. For information on how to address a target machine, consult your network administrator.
I use transactions but nothing happens when I issue ROLLBACK. Why?
In MySQL not every storage engine supports transactions. Probably, you use one of them. To use transactions, switch to some other storage engine, for example, InnoDB.
How to get a result set from a stored procedure?
This can be done in two ways. First, you can set MySqlCommand.CommandType to StoredProcedure and CommandText to the name of the procedure. Second, you can set MySqlCommand.CommandType to Text and CommandText to the CALL statement, for example, "CALL MyProc()", where MyProc is the name of the procedure. After either setup, issue the MySqlCommand.ExecuteReader() method.
If you need to retrieve just a scalar value from a stored function, you can do it through the parameter with Direction set to System.Data.ParameterDirection.ReturnValue. For an example on how to do it, refer to the "Using Parameters" article in the dotConnect for MySQL documentation.
What about performance?
We regularly carry out performance tests on different databases with miscellaneous environments. The tests show that dotConnect for MySQL is much better than ODBC, OLEDB, and Connector/Net. Besides that, dotConnect for MySQL has many more features than any data provider, including Connector/Net.
Non-latin characters in my data are not retrieved correctly.
Use the UTF-8 encoding. To enable it, set the Connection.Unicode property to true or include Unicode=true; in the connection string. Keep in mind, however, that this affects performance. So it is better to synchronize the default client encoding and database objects encoding if possible.
How to connect to a server behind a firewall if the port I require is blocked?
Use SSH tunnelling. It is described in the Using Secure Connections article of the dotConnect for MySQL documentation.
How to specify which libmysqld.dll my application should use?
The application searches for this library exactly like for any other library. This is described in the reference to the SDK LoadLibrary function. It may be a good idea to distribute libmysqld.dll in the same folder with your application exe file to be sure that the server will be loaded.
How to provide start-up parameters for an Embedded server?
This can be done in two ways: either by using the my.ini file or specifying data as parameters in the connection string. The following example demonstrates how to employ the connection string for this purpose:
User Id=root; Server Parameters=\"--basedir=c:/servers/embedded/;
For more information, refer to the "Using Embedded Server" article in the dotConnect for MySQL documentation. For additional information on what can be specified as a start-up parameter, refer to the MySQL Server documentation.
What else do I need to get my Embedded server up and running (besides libmysqld.dll)?
It is recommended that you copy the "share" folder from the server installation to the directory where libmysqld.dll resides (basedir in the example above). Additionally, create a folder for data inside the same directory (datadir in the example above). You may want to reproduce the folder structure from your server installation.
What types of secure connections are supported?
dotConnect for MySQL supports SSL (Secure Socket Layer) and SSH (Secure SHell) connections. Detailed information on the subject can be found in the Using Secure Connections article of the dotConnect for MySQL documentation.
What encryption protocols are supported?
You can use the following protocols in SSL connections: DES(40), DES(56), DES(168), AES(128), AES(256), RC4(40), RC4(128), RC2(40). In SSH connections, you can use DES(168), Blowfish, and AES(128)
I have set up an SSL connection but it does not work. Why?
Probably, your server does not have SSL support. Check this using the following SQL statement:
SHOW VARIABLES LIKE 'have_openssl'
If the server returns NO, please visit MySQL site for instructions on how to start up the required server from scratch.
I'm getting "A socket operation encountered a dead network" when trying to connect with Mobile Edition in run time.
Make sure that you have synchronized your device with a workstation because some synchronization tool (e.g., Microsoft ActiveSync or Windows Mobile Device Center) should be used when you work with a mobile device or emulator. Before deployment, make Connect and Cradle to the emulator you want to use via Device Emulator Manager (the Tools menu in Visual Studio). After cradle the ActiveSync icon in the tray should become green (indication that synchronization is successful). In the case of Windows Mobile Device Center, its Sync Center should return the "Connected" and "Sync completed" statuses.
When deploying an application compiled with a trial version to a mobile device, I'm getting the "trial period expired" exception. What's wrong?
The possible reason may be that the system date/time of the device is not synchronised with the date/time on your workstation; hence, the problem can be resolved by setting the date/time identical to the one on the workstation.