19. MySQL
Prev | Appendix 2. Supported Databases | Next

## 19. MySQL

Example 2.15. Example properties for MySQL

    openjpa.ConnectionDriverName: com.mysql.jdbc.Driver
    openjpa.ConnectionURL: jdbc:mysql://SERVER_NAME/DB_NAME

### 19.1. Using Query Hints with MySQL

MySQL has support for "query hints", which are keywords embedded in SQL that provide some hint for how the query should be executed. These hints are usually designed to provide suggestions to the MySQL query optimizer for how to efficiently perform a certain query, and aren't typically needed for any but the most intensive queries. OpenJPA supports hints to be placed between SELECT keyword and column list.

Example 2.16. Using MySQL Hints

    Query query = em.createQuery(...);
    query.setHint("openjpa.hint.MySQLSelectHint", "SQL_NO_CACHE");
    List results = query.getResultList();

### 19.2. Known issues with MySQL

- The default table types that MySQL uses do not support transactions, which will prevent OpenJPA from being able to roll back transactions. Use the `InnoDB` table type for any tables that OpenJPA will access.

- MySQL does not support sub-selects in versions prior to 4.1, so some operations (such as the `isEmpty()` method in a query) will fail due to this.

- Rollback due to database error or optimistic lock violation is not supported unless the table type is one of the MySQL transactional types. Explicit calls to `rollback()` before a transaction has been committed, however, are always supported.

- Floats and doubles may lose precision when stored in some datastores.

- When storing a field of type `java.math.BigDecimal`, some datastores will add extraneous trailing 0 characters, causing an equality mismatch between the field that is stored and the field that is retrieved.

- When using large result sets with MySQL there are a number of documented limitations. Please read the section titled "ResultSet" in the "MySQL JDBC API Implementation Notes". The net effect of these limitations is that you will have to read all of the rows of a result set (or close the connection) before you can issue any other queries on the connection, or an exception will be thrown. Setting openjpa.FetchBatchSize to any value greater than zero will enable streaming result sets.

- The use of LOB streaming is limited. When reading LOB data from the database, the MySQL JDBC driver will actually load all the data into memory at the same time.

- As of MySQL 5.7 the `DATETIME` data type supports sub-second fractions. The default of MySQL is to use no fractions. The number of fractions can be explicitly set via scale: `@Column(scale=3)` will lead to a `DATETIME(3)` column.

Prev | Up | Next
18. Microsoft FoxPro | Home | 20. MariaDB