【Doris踩坑记】java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
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`);
正文到此结束