create table with COLLATE error

[GalenGao]

when create table with COLLATE,it is wrong:
mysql> CREATE TABLE data_gps_htest (
-> CAR_ID VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> IO_STTS VARCHAR(16) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> CAR_STTS VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> ALARM_STTS VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> EXTS VARCHAR(4000) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> GPS_TYPE VARCHAR(18) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> SGN_STTS VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',
-> TMN_MILE INT(11) NULL DEFAULT NULL,
-> INDEX DATA_GPS_H_IDX (CAR_ID)
-> )
-> COLLATE='utf8_bin'
-> ROW_FORMAT=COMPRESSED;
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 79 near 'collate'

[GalenGao]

ROW_FORMAT=COMPRESSED
add it and also error

[hustjieke]

ACK

[roshanjonah]

Any updates on this? Getting the same error? In the meantime, is there a way around this? Thanks

[hustjieke]

Any updates on this? Getting the same error? In the meantime, is there a way around this? Thanks
Yeah, we're doing work on it.
We need a bit more time to finish this work, maybe one day or two.

[hustjieke]

reference: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

1. complete more columnn options, add STORAGE DEFAULT which is not descriped on official 5.7 document, but actually mysql support it.
   see: https://github.com/mysql/mysql-server/blob/5.7/sql/sql_yacc.yy#L6953
   and extra option suppored by mysql: ON UPDATE ...
   see: https://github.com/mysql/mysql-server/blob/5.7/sql/sql_yacc.yy#L6888

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [STORAGE {DISK | MEMORY}]
      [ON UPDATE NOW_SYM]
  | data_type
      [COLLATE collation_name]
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']

Currently we do not support column option [reference_definition], [GENERATED ALWAYS] AS (expr)
also : some DEFAULT now_or_signed_literal and SERIAL_SYM DEFAULT VALUE_SYM options not in 5.7 document, but acctually mysql support them.
see:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_yacc.yy#L6897
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_yacc.yy#L6887

2. complete more table options.

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]

Currently we do not support table options:
UNION [=] (tbl_name[,tbl_name]...)

[roshanjonah]

I am getting..
panic: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 439 near 'collate' (errno 1149) (sqlstate 42000)

Do you think it's relating to this same issue? This occured when trying to import using..
./bin/myloader -h XXX -P 3308 -u root -p 'XXX' -d dumper-sql/ -o

[hustjieke]

I am getting..
panic: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 439 near 'collate' (errno 1149) (sqlstate 42000)

Do you think it's relating to this same issue? This occured when trying to import using..
./bin/myloader -h XXX -P 3308 -u root -p 'XXX' -d dumper-sql/ -o

Yes, we now didn't support column option collate ..., it will be resolved in this issue.

[BohuTANG]

@hustjieke

Has the problem been solved by #628 ?

[hustjieke]

@hustjieke

Has the problem been solved by #628 ?

Yes, the problem has been solved.

[BohuTANG]

@GalenGao @roshanjonah

Please check it again with the master banch.
Thanks.