18.  MySQL

18.1. Using Query Hints with MySQL
18.2. Known issues with MySQL

Example 2.15.  Example properties for MySQL

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

18.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();

18.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.