MySQL Support Release Notes
Introduction
MySQL the well known open DBMS is now available as a Genexus application platform. Java, .NET, Visual FoxPro and Visual Basic Generators support this DBMS in 9.0 version.
MySQL is a full featured DBMS and
GeneXus fully supports it (no known limitation exists, at the time of this writing, that makes any Genexus feature not to be implemented on MySQL).
MySQL applications can have:
- Transactional integrity
- Row level locking
- Referential integrity
- Optimized reorganizations (using alter table add/drop/change column)
- Blob datatype
Requirements
MySQL Engines
MySQL supports different engines to manage data store and indexes.
The engine Genexus uses is InnoDB (
http://dev.mysql.com/doc/mysql/en/InnoDB.html). The option is generated at the moment of table reorganization/creation.
The most important reason we chose InnoDB is row level locking support.
In version 3.23 in Linux, InnoDB is disabled by default. To enable it, the minimum required setting is innodb_data_file_path=ibdata1:10M:autoextend in the file /etc/my.cnf.
There are other parameters, but this is enough to enable it. Without this setting transactional integrity doesn't work. From 4.0 version and on, InnoDB is enabled by default.
More info in
http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html.
Mysql and Connector Versions
Tests were made using version 3.23.58 of MySQL, and also version 4.0.
Version 3.23 is the minor version we know it works correctly with GX.
We have tested it in Java using the MySQL Connector/J JDBC driver, available for download in MySQL download page.
With JDBC driver 3.0, you need the SUN virtual machine (SUN JDK 1.4 or higher is required). If you use 2.0 version, you can also use Microsoft virtual machine.
Genexus Configuration
First of all, select 'MySQL' in the 'Model Properties' dialog.
Java generator
In 'DBMS Options' dialog, JDBC, select 'MySQL JDBC Driver (Type 4)'. Configure the properties for database name, server, user and password, and then configure the 'Execution Options', including the JDBC driver ( mysql-connector-java-3.0.11-stable-bin.jar in the case of the 3.0 version ) in 'Classpath'. You must select 'Sun SDK' for 'Platform'.
Net Generator
Install Mysql Driver for .Net from the following link:
http://sourceforge.net/projects/mysqldrivercs
You have to install driver and SDK.
After installing it, you will have:
- mysql.dll - this is a .NET native library to access MySQL (also called libmysql.dll)
- MySQLDriverCS.dll - this library is the ADO .Net data provider (developed by the project MySQLDriverCS) and is distributed with GeneXus . It is automatically copied to the bin directory.
The current version of MySQLDriverCS.dll is compatible with Mysql Driver for .Net 3.23.32 and upper. Nevertheless, it is recommended to install the new version of Mysql Driver for .Net.
Mysql overview
Nullvalue
The GX nullvalue for dates datatype is '1000-01-01', and for datetimes datatype is '1000-01-01 00:00:00' (they are the minimum values that MySQL can store for each datatype).
Considerations:
Empty values for chars in Mysql
http://www.gxtechnical.com/main/hviewsac.aspx?2,3,14,18935
Joins
The default join type is Outer.
Mapping between GX and MySQL datatypes
| GX datatype |
| mySQL datatype
|
|
| N(X) | X<5 | smallint
|
| N(X) | X>=5 X<7 | mediumint
|
| N(X) | X>=7 X<10 | int
|
| N(X) | X>=10 | bigint
|
| N(X,Y) |
| numeric(X,Y)
|
| CHAR(X) | X<=255 | (national)char
|
| VCHAR(X) |
| (national)varchar
|
| LVCHAR(X) | X<=65535 | (national)text
|
| LVCHAR(X) | X<16777215, X>65535 | (national)mediumText
|
| LVCHAR(X) | X>16777215 | (national)longtext
|
| DATE |
| date
|
| DATETIME |
| datetime |
Notes:
1. national - This option is used when the DBMS option 'Enable national language support' is set to 'Yes'.
2. The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255
http://dev.mysql.com/doc/refman/5.1/en/char.html
3. Mysql documentation says:
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
There's a
GeneXus model property named "MySQL version" with the following values:
- 4.x to 5.0.2
- 5.0.3 or higher
The length of varchars greater than 255 depends on the value specified in that model property.
mySQL limits
| Maximum Table name length | 64
|
| Maximum Index name length | 64
|
| Maximum Column name length | 64 |
Downloads
You can download the Mysql server realease, and Graphical administration tools, from the following link:
http://dev.mysql.com/downloads/
Another one: DBManager
http://www.dbtools.com.br/EN/dbmanagerpro.php
Java:
MySQL Connector/J JDBC driver download is available from here:
http://dev.mysql.com/downloads/
.NET:
Mysql Driver for .Net:
http://sourceforge.net/projects/mysqldrivercs
ODBC access:
MySQL Connector/ODBC 3.51 Downloads
http://dev.mysql.com/downloads/connector/odbc/3.51.html
Considerations
- The Val function cannot be evaluated in the server, this is a limitation of the DBMS
- In win platforms, using ODBC access, after inserting data in the form, if you "get" the data from the database, the character fields are truncated to only one character (however, in the database the data is correct). The problem is the ODBC driver. You should use 3.51.11 or upper.
- Unique index allows duplicates with null values. SAC 20301
External News
Oracle purchased InnoDB!!