【Doris踩坑记】java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.

共计 5554 个字符，预计需要花费 14 分钟才能阅读完成。

Doris数据库执行建表语句，报错提示如下：

ERROR 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@62] get db connection error 
com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
    at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:57) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    at com.baidu.bce.edap.jobs.JobManager.lambda$startJob$1(JobManager.java:133) [com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_211]
    at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_211]
Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[na:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[na:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[na:na]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[na:na]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[na:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483) ~[na:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441) ~[na:na]
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[na:na]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[na:na]
    at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:46) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    ... 4 common frames omitted
20
INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@342] job purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915 is finished with FAILED status on RemoteAgent at 1684285152819
21
INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@253] start to close db connection
22
INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@255] close sql statement
23
INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@260] close db connection
24
ERROR 2023-05-17 08:59:12.825 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@139] startJob jobId:[purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915] error
com.baidu.bce.edap.jobs.JobException: com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
    at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:64) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    at com.baidu.bce.edap.jobs.JobManager.lambda$startJob$1(JobManager.java:133) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_211]
    at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_211]
Caused by: com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
    at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:57) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    ... 4 common frames omitted
Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[na:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[na:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[na:na]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[na:na]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[na:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483) ~[na:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441) ~[na:na]
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[na:na]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[na:na]
    at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:46) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na]
    ... 4 common frames omitted

日志关键信息：

Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.

问题原因：

注意⚠️：AGGREGATE KEY (….) 括号里面的维度列顺序要严格按照，上文建表字段的顺序保持一致

原始SQL(按照列创建顺序比对下来，我的SQL在指定AGGREGATE KEY列时，字段丢失，顺序错乱都会出现这个错误)：

CREATE TABLE `purchase_costs_analysis` (
  `secondary_company` ....,
  `tertiary_company` .....,
  `project` varchar(30) .....,
  `purchase_method`  .....,
  `material_category`  .....,
  `purchase_category`  .....,
  `purchase_model`  .....,
  `subcontract_type`  .....,
  `subcontract_model`  .....,
  `factory_direct_purchase`  .....,
  `created_time`  .....,
  `labor_professional_subcontract_total_standard_cost` decimal(22, 8) SUM  .....,
  `labor_professional_subcontract_total_purchase_amount` decimal(22, 8) SUM  .....,
  `material_equipment_total_standard_cost` decimal(22, 8) SUM  .....,
  `material_equipment_subcontract_total_purchase_amount` decimal(22, 8) SUM  .....
) ENGINE = OLAP 
AGGREGATE KEY(`secondary_company`,`tertiary_company`,`project`,`purchase_method`,`material_category`,`purchase_model`,`subcontract_type`,`subcontract_model`,`factory_direct_purchase`,`created_time`) 
DISTRIBUTED BY HASH(`project`);