H2 mode MySQL- COLLATE utf8_bin not supported

[GoogleCodeExporter]

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.Start h2 with MODE=MySQL
2.CREATE TABLE `test` (
  `stem` varchar(2000) COLLATE utf8_bin DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=982524 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3.The returned error is : Caused by: org.h2.jdbc.JdbcSQLException: Syntax error 
in SQL statement "CREATE TABLE ""TEST"" ( ""STEM"" VARCHAR(2000) COLLATE[*] 
UTF8_BIN DEFAULT NULL, ) ENGINE=INNODB AUTO_INCREMENT=982524 DEFAULT 
CHARSET=UTF8 COLLATE=UTF8_BIN "; expected "FOR, UNSIGNED, NOT, NULL, AS, 
DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, 
NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, 
NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE `test` ( `stem` varchar(2000) COLLATE utf8_bin DEFAULT NULL, ) 
ENGINE=InnoDB AUTO_INCREMENT=982524 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 
[42001-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:171)
    at org.h2.message.DbException.getSyntaxError(DbException.java:196)
    at org.h2.command.Parser.getSyntaxError(Parser.java:491)
    at org.h2.command.Parser.read(Parser.java:2853)
    at org.h2.command.Parser.readIfMore(Parser.java:809)
    at org.h2.command.Parser.parseCreateTable(Parser.java:5367)
    at org.h2.command.Parser.parseCreate(Parser.java:3872)
    at org.h2.command.Parser.parsePrepared(Parser.java:324)
    at org.h2.command.Parser.parse(Parser.java:279)
    at org.h2.command.Parser.parse(Parser.java:255)
    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:428)
    at org.h2.engine.Session.prepareCommand(Session.java:377)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:186)
    ... 51 more

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system, file
system, and virtual machine?
- H2 v1.3.172 (SVN 4956)
- Windows 7 64bit
- java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

Do you know a workaround?
No
What is your use case, meaning why do you need this feature?

How important/urgent is the problem for you?

Please provide any additional information below.

Original issue reported on code.google.com by queeq...@gmail.com on 16 Jul 2013 at 12:57

[GoogleCodeExporter]

Hi Guys,

May I have your feedback on this one ?

Thanks

Original comment by queeq...@gmail.com on 19 Jul 2013 at 6:01

[GoogleCodeExporter]

Why don't you just set the default correctly and use:

CREATE TABLE `test` (
  `stem` varchar(2000) DEFAULT NULL,
)

? That would be much simpler.

Original comment by thomas.t...@gmail.com on 20 Jul 2013 at 5:29

[GoogleCodeExporter]

This is 
(a) a pretty major feature, 
and
(b) completely non-sql-standard,
so no, sorry, I don't think we're going to be implementing it anytime soon.

Specifically, it would require two things we currently do not support
(a) per-column collations.
(b) the very weird MySQL UTF8_BIN charset

The only I can suggest for now is that you convert this to a VARBINARY datatype 
when you're using H2.

Original comment by noelgrandin on 22 Jul 2013 at 8:56

[GoogleCodeExporter]

It's not the plan for H2 to be a MySQL clone, so I wouldn't want such a feature.

Original comment by thomas.t...@gmail.com on 28 Jul 2013 at 9:34

• Changed state: WontFix