MySQL dialect isn't creating VARCHAR fields for Java String types

[Niclas Hedhman]

Lukas,

sorry for not getting back sooner with a testcase.

While writing this, I tested some more, and found additional information.

IF String.class is passed as type, the CHAR data type without length is chosen.

IF SQLDataType.VARCHAR is passed as type, the CHAR data type without length is chosen

IF SQLDataType.VARCHAR(10) is passed as type, the VARCHAR(10) data type is chosen

I don't think this is appropriate behavior, especially for Java String class. For other dialects (postgres, sqlite, H2 and Derby) it does the right thing.

-o-o-o-o-

Setup of MYSQL;

docker run --name mysql -e MYSQL_ROOT_PASSWORD="" -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -e MYSQL_DATABASE=jdbc_test_db -p 3306:3306 -d mysql

And then run the code below. The First insertInto() will succeed and the second insertInto() will fail, becase the createTable() will produce the following SQL statement;

create table `TESTTABLE`(`_identity` char null)

I was digging quite a lot in the JOOQ code to find this, but I can't figure out how it hangs together, and it seems that large sections are generated and not handcoded. So I gave up.

package org.hedhman.niclas;

import com.mysql.cj.jdbc.MysqlDataSource;
import javax.sql.DataSource;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.DataType;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.Schema;
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.SQLDataType;
import org.jooq.impl.ThreadLocalTransactionProvider;
import org.junit.Test;

public class Experiment
{
    @Test
    public void test2()
        throws Exception
    {
        String host = "127.0.0.1";
        int port = 3306;
        DataSource dataSource = rawDataSource(host,port);
        Settings settings = new Settings().withRenderNameStyle( RenderNameStyle.QUOTED );
        SQLDialect dialect = SQLDialect.MYSQL;
        Schema schema = DSL.schema( DSL.name( "POLYGENE" ) );

        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 );
//        Field<String> identityColumn = DSL.field( DSL.name( "_identity" ), SQLDataType.VARCHAR );
//        Field<String> identityColumn = DSL.field( DSL.name( "_identity" ), SQLDataType.VARCHAR(10) );

        Name tableName  = DSL.name( "TESTTABLE" );

        dsl.transaction( c -> {
            dsl.createTable( tableName )
               .column( identityColumn )
               .execute();
        });

        dsl.transaction( c -> {
            Table<Record> table = DSL.table( tableName );
            dsl.insertInto( table )
               .set( identityColumn, "1" )
               .execute();
        });

        dsl.transaction( c -> {
            Table<Record> table = DSL.table( tableName );
            dsl.insertInto( table )
               .set( identityColumn, "12" )
               .execute();
        });
    }

    private DataSource rawDataSource( String host, int port )
        throws Exception
    {
        MysqlDataSource datasource = new MysqlDataSource();
        datasource.setUser( "root" );
        datasource.setPassword( "" );
        datasource.setUrl( "jdbc:mysql://" + host + ":" + port + "/jdbc_test_db" );
        return datasource;
    }
}

--

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

[Lukas Eder]

Hi Niclas,

Thanks for your message. Yes you're right, that's a bug: https://github.com/jOOQ/jOOQ/issues/6745

But the fix isn't very easy, unfortunately. MySQL needs a VARCHAR length, but the total length of a row is 64kb: https://stackoverflow.com/a/13506920/521799

So, what length should jOOQ choose by default? 256 bytes? We'll have to pick something, but this will need a bit more thought. But clearly, "char" is incorrect.

Thanks,
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.

[Niclas Hedhman]

Hi again,

I have been busy with other things, and the issue with jOOQ and MySql/MariaDb has not been an important one, but I would like to hear your feedback. I updated to latest version in Apache Polygene and enabled my tests, and the generated CHAR (instead of a VARCHAR) is still there.

Is it that you don't consider it a bug, have another opinion on what should be done, or simply too busy with other things?

Cheers

Niclas

[Lukas Eder]

Hi Niclas,

We have tons of open issues and this one is an issue that has seen little attention, so it's really not a priority right now. It wasn't for you either :)

Coincidentally, I've seen a similar problem just recently where a customer wanted to have PostgreSQL's TEXT type translated to Oracle VARCHAR2(4000), but jOOQ instead translates it to CLOB (which isn't a bug like this one, but certainly related). The proper solution to this problem would be a new SPI that allows for influencing how one type is translated to another between dialects, or even within a dialect:

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

In your case, a quick fix would be to post process the generated SQL and replace the varchar reference by what you need it to be, e.g. by regex pattern matching inside of an ExecuteListener.

I hope this helps,

Lukas

--