Migrating to MySQL

If you plan to migrate to MySQL then next advices could be helpful for you:

1. Be famous how AUTO_INCREMENT works in MySQL
   MySQL 5.0 Reference Manual :: 3 Tutorial :: 3.6 Examples of Common Queries :: 3.6.9 Using AUTO_INCREMENT
   
   Check how SQL Mode NO_AUTO_VALUE_ON_ZERO affects AUTO_INCREMENT
   MySQL 5.0 Reference Manual :: 5 MySQL Server Administration :: 5.1 The MySQL Server :: 5.1.7 Server SQL Modes
   
   Inserting 0 to auto-numbered collumn gives next result, i.e. 0 will be replaced by next autonumbered value.
   
   


2. JPA Generator
   
   MySQL doesn't support:

   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "xxx")

   
   It will throw exception:

   Caused by: org.hibernate.MappingException: Dialect does not support sequences

   
   It should be changed to

   @GeneratedValue(strategy = GenerationType.AUTO, generator = "xxx")



3. The default transaction isolation level of InnoDB is REPEATABLE READ
   MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.8 The InnoDB Transaction Model and Locking
   


4. MySQL date/time types have bad precision
   MySQL 5.0 Reference Manual :: 10 Data Types :: 10.5 Data Type Storage Requirements
   
   So think about changing data type to Long
   


5. Be famous with Restrictions on Subqueries
   MySQL 5.0 Reference Manual :: F Restrictions and Limits :: F.3 Restrictions on Subqueries
   In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
   

   
   DELETE FROM t WHERE ... (SELECT ... FROM t ...);
   UPDATE t ... WHERE col = (SELECT ... FROM t ...);
   {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

   
   Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
   

   
   UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

   
   Here the prohibition does not apply because the result from a subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.
   
   Some useful functions for JPA/Hibernate that supports this wrapping are listed below:
   

   private static final Pattern SUBQUERY_ON_MUTATING_TABLE_PATTERN = Pattern.compile("^(.*?)(\\(\\s*SELECT\\s+)(.*?)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);
       
   public static int wrappedExecuteUpdate(EntityManager entityManager, Query query, Map<String, Object> parameters) {
       SessionFactoryImplementor sessionFactory = getHibernateSessionFactoryImplementor(entityManager);
   
       // if (!sessionFactory.getDialect().supportsSubqueryOnMutatingTable()) {
       if (sessionFactory.getDialect() instanceof MySQLDialect) {
           return doesntSupportSubqueryOnMutatingTable(sessionFactory, entityManager, query, parameters);
       } else {
           return supportsSubqueryOnMutatingTable(entityManager, query, parameters);
       }
   }
       
   private static int supportsSubqueryOnMutatingTable(EntityManager entityManager, Query query, Map<String, Object> parameters) {
       for (String p : parameters.keySet()) {
           query.setParameter(p, parameters.get(p));
       }
       return query.executeUpdate();
   }
       
   private static int doesntSupportSubqueryOnMutatingTable(SessionFactoryImplementor sessionFactory, EntityManager entityManager, Query query, Map<String, Object> parameters) {
       org.hibernate.impl.QueryImpl queryImpl = getHibernateQueryImpl(query);
       
       QueryTranslatorImpl qt = (QueryTranslatorImpl) new ASTQueryTranslatorFactory().createQueryTranslator(
           "tempTableWrapping", queryImpl.getQueryString(), null, sessionFactory);
       
       qt.compile(null, false);
       String sql;
       if (qt.getSqlAST().needsExecutor()) {
           sql = qt.collectSqlStrings().get(0).toString();
       } else {
           sql = qt.getSQLString();
       }
       
       // org.hibernate.ejb.QueryImpl could handle Collection parameters right ONLY for named parameters
       // (setParameterList supported only for named parameters)
       // so we need to switch from positional parameters to named parameters
       // (we have positional parameters after QueryTranslator.compile)
       ParameterTranslations pt = qt.getParameterTranslations();
       sql = switchToNamedParameters(sql, pt);
       
       Query newQuery = entityManager.createNativeQuery(getTempTableWrapping(sql, "_t"));
       
       for (String p : parameters.keySet()) {
           newQuery.setParameter(p, parameters.get(p));
       }
       return newQuery.executeUpdate();
   }
       
   @SuppressWarnings("unchecked")
   private static String switchToNamedParameters(String sql, ParameterTranslations pt) {
       String[] parts = sql.split("\\?");
       for (String p : (Set<String>) pt.getNamedParameterNames()) {
           int locations[] = pt.getNamedParameterSqlLocations(p);
           for (int l : locations) {
               parts[l] = parts[l] + ":" + p;
           }
       }
       StringBuffer sb = new StringBuffer();
       for (int i = 0; i < parts.length; i++) {
           sb.append(parts[i]);
       }
       return sb.toString();
   }

   
   Also is not supported:
   

   
   WHERE (X, Y) IN (SELECT X, Y FROM ...)