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 2017-11-12 3:53 GMT+01:00 Niclas Hedhman <[email protected]>: > 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 > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
