MySQL dialect insist on CHAR types...

[Niclas Hedhman]

Hi again,

JOOQ version; 3.10.1

I now have my JOOQ backed Entity Store extension in Apache Polygene passing our test suites for Postgresql, SQLite, H2 and Derby. That is pretty cool for us.

BUT MySQL/MariaDb doesn't pass and it is down to the use of CHAR instead of VARCHAR for String fields.

I promised a complete test case;

start Docker instance of MySQL;

    docker run \

        -d \

        --name mariadb \
        -e MYSQL_ROOT_PASSWORD=testing \
        -e MYSQL_DATABASE=testdb \
        -p 3306:3306 \
        mysql

Then run the testcase below.

You will have created a table

    create table if not exists `ENTITIES`(`_identity` char null)

and then an exception on insertion of data;

    insert into `ENTITIES` (`_identity`) values ('123')

org.jooq.exception.DataAccessException: SQL [insert into `ENTITIES` (`_identity`) values (?)]; Data truncation: Data too long for column '_identity' at row 1

If you change to a single character value instead of "123", it will execute without exception.

Even if I change the field declaration to be (instead of String.class)

    Field<String> identityColumn = DSL.field( DSL.name( "_identity" ), SQLDataType.VARCHAR );

I get the same result.

I don't know if you can force MySQL into some other behavior, but I think that is not the right way to go. I don't understand why CHAR was chosen in the first place. if there was some clever reason...

Cheers

Niclas

-o-o-o-

package org.hedhman.niclas;

import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.Table;
import org.jooq.TransactionProvider;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.TableImpl;
import org.jooq.impl.ThreadLocalTransactionProvider;
import org.junit.Test;

public class Experiment
{
    @Test
    public void test1()
        throws Exception
    {
        String host = "127.0.0.1";
        int port = 3306;
        DataSource dataSource = dbcpDataSource( host, port );
        Settings settings = new Settings().withRenderNameStyle( RenderNameStyle.QUOTED );
        SQLDialect dialect = SQLDialect.MARIADB;

        ConnectionProvider connectionProvider = new DataSourceConnectionProvider( dataSource );
        TransactionProvider transactionProvider = new ThreadLocalTransactionProvider( connectionProvider, false );
        Configuration configuration = new DefaultConfiguration()
            .set( dialect )
            .set( connectionProvider )
            .set( transactionProvider )
            .set( settings );

        DSLContext dsl = DSL.using( configuration );

        Field<String> identityColumn = DSL.field( DSL.name( "_identity" ), String.class );
        Name entitiesTableName = DSL.name( "ENTITIES" );
        Table<Record> entitiesTable = new TableImpl<Record>( entitiesTableName );
        dsl.transaction( t -> {

            dsl.createTableIfNotExists( entitiesTable )
               .column( identityColumn )
               .execute();
        });

        dsl.transaction( t -> {
            dsl.insertInto( entitiesTable )
               .set( identityColumn, "123" )
               .execute();
        });
    }

    private DataSource dbcpDataSource( String host, int port )
        throws Exception
    {
        BasicDataSource pool = new BasicDataSource();

        String driverClass = "com.mysql.jdbc.Driver";
        Class.forName( driverClass );
        pool.setDriverClassName( driverClass );
        pool.setUrl( "jdbc:mysql://" + host + ":" + port + "/testdb" );
        pool.setUsername( "root" );
        pool.setPassword( "testing" );
        pool.setDefaultAutoCommit( false );
        return pool;
    }
}

--

Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java

[Lukas Eder]

Thank you very much for your report.

This is a bug indeed. I've created issue #6745 for this:

https://github.com/jOOQ/jOOQ/issues/6745

jOOQ should attempt to create the biggest possible VARCHAR column size (65535) for String types of unknown lengths.

In the meantime, the safest choice is to specify the type explicitly (always, regardless of database) by using something like SQLDataType.VARCHAR(50).

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

I remember again now, why jOOQ didn't add a default maximum column length. It's because of MySQL's maximum row size limit, which is also 65535:

https://stackoverflow.com/a/13506920/521799

So, there's not really a good default to choose, when writing DDL. Another reason why you should pick one yourself!