druid 0.14.1-incubating create table failed when starting the Coordinator

[HeIsRicky]

druid 0.14.1-incubating mysql 5.6

• Following sql has been executed

  -- create a druid database, make sure to use utf8mb4 as encoding
  CREATE DATABASE druid DEFAULT CHARACTER SET utf8mb4;
  
  -- create a druid user
  CREATE USER 'druid'@'localhost' IDENTIFIED BY 'diurd';
  
  -- grant the user all the permissions on the database we just created
  GRANT ALL PRIVILEGES ON druid.* TO 'druid'@'localhost';
  
  SET GLOBAL innodb_file_format = Barracuda;
  SET GLOBAL innodb_file_per_table = ON;
  innodb_large_prefix = 1

• common.runtime.properties

  ...
  druid.extensions.loadList=["druid-histogram", "druid-datasketches", "druid-lookups-cached-global", "druid-hdfs-storage", "mysql-metadata-storage"]
  
  druid.metadata.storage.type=mysql
  druid.metadata.storage.connector.connectURI=jdbc:mysql://rw.moni- 
  druid101680.mysql.dba.vivo.lan:11005/druid
  druid.metadata.storage.connector.user=rw_druid
  druid.metadata.storage.connector.password=...
  ...

• Error

  java.sql.SQLException: Index column size too large. The maximum column size is 767 bytes.

[dene14]

@HeIsRicky your problem is in selected charset. urf8mb4 is advised option nowadays for mysql but it breaks columns sizing in some apps. You can use utf8_general_ci instead.

[AngryUbuntuNerd]

This solution doesnt work anymore since 0.17 as Druid seems to need keys that are too long even in utf8.

That breaks it for MySQL 5.6 or lower. Higher versions of MySQL are not affected as the default key size is bigger.

A fix for 5.6 could be to use innodb_large_prefix but this requires Druid to create tables with a different InnoDB row format (COMPACT is default). See https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html

[github-actions[bot]]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 4 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the dev@druid.apache.org list. Thank you for your contributions.

[github-actions[bot]]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.