Thursday, February 26, 2009

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

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

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

    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++) {
    return sb.toString();

    Also is not supported:

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

No comments: