0

From this, MySQL load data infile command works well with hexadecimal delimiter like X'01' or X'1e' in my case. But the same command can't be run with same command load data infile on MemSQL.

I tried specifying various forms of of the same delimiter \x1e like:

  • '0x1e' or 0x1e
  • X'1e'
  • '\x1e' or 'x1e'

All the above don't work and throw either syntax error or other error like this:

This is like the delimiter can't be resolved correctly:

mysql> load data local infile '/container/data/sf10/region.tbl.hex' into table REGION CHARACTER SET utf8 fields terminated by '\x1e' lines terminated by '\n';
ERROR 1261 (01000): Row 1 doesn't contain data for all columns

This is syntax error:

mysql> load data local infile '/container/data/sf10/region.tbl.hex' into table REGION CHARACTER SET utf8 fields terminated by 0x1e lines terminated by '\n';   
ERROR 1064 (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 near '0x1e lines terminated by '\n'' at line 1
mysql>

The data is actually delimited by non-printable hexadecimal character of \x1e and line terminated by regular \n. Use cat -A can see the delimited characters as ^^. So the delimiter should be correct.

$  cat -A region.tbl.hex 
0^^AFRICA^^lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to $
1^^AMERICA^^hs use ironic, even requests. s$

Are there a correct way to use hex values as delimiter? I can't find such information in documentation.

For the purpose of comparison, hex delimiter (0x1e) can work well on MySQL:

mysql> load data local infile '/tmp/region.tbl.hex' into table region CHARACTER SET utf8 fields terminated by 0x1e lines terminated by '\n';
Query OK, 5 rows affected (0.01 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
robert
  • 397
  • 1
  • 3
  • 14
  • I came here from AWS Aurora Mysql 5.6 and am happy to now be importing using this: `LOAD DATA FROM S3 's3://mybucket/myfile.txt' INTO TABLE mytable FIELDS TERMINATED BY 0x1f IGNORE 4 LINES;` - no quoting – WEBjuju Apr 04 '19 at 16:46

1 Answers1

0

MemSQL supported hex delimiters as of 6.7, of the form in the last code block in your question. Prior to that, you would need the literal quoted 0x1e character in your sql string, which is annoying to do from a CLI. If youre on an older version you may need to upgrade.

Joseph Victor
  • 819
  • 6
  • 16
  • The version I am testing is 6.5.17. It should be new enough. If I ran the statement working on MySQL, it would complain syntax error. It seems MemSQL likes delimiter being quoted. Do you mind share a working example command? Thanks – robert Jan 23 '19 at 15:33
  • Em, looks like my version is still not new enough. I will give the 6.7 a try tomorrow. Do you mean `fields terminated by '0x1e'` is the way to go? – robert Jan 23 '19 at 15:38
  • 1
    `fields terminated by 0x1e` without quote on MemSQL 6.7 can work – robert Jan 24 '19 at 17:17