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! 2017-10-30 12:05 GMT+01:00 Lukas Eder <[email protected]>: > 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 > > 2017-10-26 9:30 GMT+02:00 Niclas Hedhman <[email protected]>: > >> 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 >> >> -- >> 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.
