H2 mode MySQL- partial key 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 `sentences` (
  `id` int(11) NOT NULL,
  `sentence` varchar(2000) NOT NULL,
   KEY `unique_sentence` (`id`,`sentence`(255)),
) ENGINE=InnoDB AUTO_INCREMENT=982524 DEFAULT CHARSET=utf8;

3.The returned error is : Caused by: org.h2.jdbc.JdbcSQLException: Syntax error 
in SQL statement "CREATE TABLE ""SENTENCES"" ( ""ID"" INT(11) NOT NULL, 
""SENTENCE"" VARCHAR(2000) NOT NULL, KEY ""UNIQUE_SENTENCE"" 
(""ID"",""SENTENCE""([*]255)), ) ENGINE=INNODB AUTO_INCREMENT=982524 DEFAULT 
CHARSET=UTF8 "; expected "ASC, DESC, NULLS, ,, )"; SQL statement:
CREATE TABLE `sentences` ( `id` int(11) NOT NULL, `sentence` varchar(2000) NOT 
NULL, KEY `unique_sentence` (`id`,`sentence`(255)), ) ENGINE=InnoDB 
AUTO_INCREMENT=982524 DEFAULT CHARSET=utf8 [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.parseIndexColumnList(Parser.java:769)
    at org.h2.command.Parser.parseAlterTableAddConstraintIf(Parser.java:5162)
    at org.h2.command.Parser.parseCreateTable(Parser.java:5300)
    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 1:09

[GoogleCodeExporter]

Hi,

Are those MySQL issues actual use cases, meaning does it actually block you 
from using H2?

If not, I suggest to not fix them.

If yes, could you provide patches?

Original comment by thomas.t...@gmail.com on 16 Jul 2013 at 5:18

[GoogleCodeExporter]

Hi,

We are trying to use H2 mode MySQL as a test DB. The production DB being MySQL. 
So far all the issues I've posted are real cases. For some of them we have work 
arounds but for some others we don't (like this one for example).  

Thanks

Original comment by queeq...@gmail.com on 16 Jul 2013 at 11:42

[GoogleCodeExporter]

The workaround for this one is to use a computed column:
http://h2database.com/html/features.html#computed_columns

I don't think this is something we are going to work in the near future, so 
marking it as PatchesWelcome.

Original comment by noelgrandin on 17 Jul 2013 at 6:47

• Changed state: PatchesWelcome