You can configure a MySQL database for an application modeled in MDriven Designer, generate its schema through the MySQL persistence mapper, and account for MySQL-specific SQL differences.

When to use this page

Use this page when you are developing against a locally installed MySQL server and want MDriven Designer to create or evolve the application's database schema. A persistence mapper maps the classes in your model to relational database tables and translates persistence operations to database-specific SQL. See Documentation:Persistence mappers for the mapper concept and the available built-in mappers.

For an MDrivenServer deployment packaged with Docker Compose, use Documentation:Configuring Production Databases PostgreSQL MySQL MSSQL instead. For a Synology installation, see HowTos:MDriven on Synology.

Prerequisites

Install both of the following for local development:

• A MySQL server. The server hosts the database that MDriven will use.
• The MySQL connector required by your MDriven environment.

The connector alone is not a database server. You need a running MySQL server before MDriven can connect or generate a database.

Create a development database

Create an empty database before configuring the mapper. The following example uses a database named my1.

1. Open an elevated command prompt.
2. Change to the MySQL server bin directory. A typical installation path is C:\Program Files\MySQL\MySQL Server <version>\bin.
3. Start the MySQL command-line client:

mysql.exe

1. Authenticate with the credentials configured for your MySQL installation. Do not assume that the root password is root; use the password assigned when MySQL was installed.
2. At the mysql> prompt, create and select the database:

CREATE DATABASE my1;
USE my1;

The USE my1; command confirms that the client can select the database. MDriven will create its tables in this selected database when you generate the database schema.

Configure the MySQL persistence mapper

In MDriven Designer, configure the project to use a MySQL persistence mapper.

1. Change the project's persistence mapper to PersistenceMapperMySql.
2. Ensure that the project property EcoProject1.PersistenceMapper refers to the MySQL mapper instance, for example PersistenceMapperMySql1.
3. Set the mapper connection string to the MySQL database.

For the local database created above, the connection string format is:

server=localhost;database=my1;User Id=root;password=root; Charset=utf8

Replace root and the password with the account and password configured on your MySQL server. For example, if you create an application-specific MySQL account, use that account in User Id and its password in password.

Setting	Example	Purpose
server	localhost	The MySQL server host for local development.
database	my1	The database in which MDriven creates and updates tables.
User Id	root	The MySQL account used for the connection.
password	root	The password for that account. Replace the example value with your configured password.
Charset	utf8	The character set requested by the connection.

Generate code and the database

After configuring the mapper and connection string:

1. Generate the application code if it has not already been generated.
2. Compile the project.
3. Generate the database.

Database generation derives the schema from the model. The MDriven Framework can update the database schema as the model changes while preserving existing data where supported; this capability is called database evolution.

Resolve MySQL-specific schema generation settings

Filtered indexes

If database generation reports an error related to filtered indexes, set SupportsFilteredIndex to false on the MySQL persistence mapper configuration. This prevents the mapper from generating filtered-index constructs that the target MySQL setup does not support.

TOP queries use LIMIT in MySQL

Microsoft SQL Server commonly limits a result set with TOP:

SELECT TOP 15 * FROM stuff

MySQL uses LIMIT instead:

SELECT * FROM stuff LIMIT 15

Configure the MySQL mapper's SQL database configuration so generated limited queries use MySQL syntax:

Property	Value
SqlTopKeyword	Empty string
SqlTopQuerySuffix	limit {0}

For example, when MDriven needs the first 15 rows, the empty SqlTopKeyword avoids emitting TOP 15, and SqlTopQuerySuffix emits limit 15.

Production and container deployments

Do not copy a local localhost connection string into a Docker deployment without checking the deployment configuration. In the supplied Docker Compose setup, MySQL and MDrivenServer run on a shared internal network. Configure the connection type and alternate connection string in MDrivenServer as described in Documentation:Configuring Production Databases PostgreSQL MySQL MSSQL.

The MySQL Docker deployment stores database data in /mysql-data on the host. Its initialization scripts and database credentials are located under databases/mysql in the deployment package. For advanced service, health-check, and volume details, see Documentation:Advanced Docker Compose Configuration for MDriven.

See also

• Documentation:Persistence mappers
• Documentation:Configuring Production Databases PostgreSQL MySQL MSSQL
• HowTos:MDriven on Synology
• Documentation:Advanced Docker Compose Configuration for MDriven
• Documentation:Support options