Doris相关问题处理和异常处理

1、decimal 字段异常。

wangqiaowq

2110人浏览 · 2023-08-23 17:48:28

wangqiaowq · 2023-08-23 17:48:28 发布

1、decimal 字段异常

修改为

2、连接超时

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet successfully received from the server was 1,068 milliseconds ago. The last packet sent successfully to the server was 1,068 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:520)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:700)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:639)
	at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:987)
	at com.mysql.cj.NativeSession.execSQL(NativeSession.java:666)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930)
	... 18 common frames omitted
Caused by: java.net.SocketException: Socket closed
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
	at java.net.SocketInputStream.read(SocketInputStream.java:171)
	at java.net.SocketInputStream.read(SocketInputStream.java:141)
	at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
	at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
	at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
	at java.io.FilterInputStream.read(FilterInputStream.java:133)
	at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:514)
	... 23 common frames omitted

3、doris修改表名

alter table dws_monthly_future_forecast rename dws_monthly_future_forecast_back;

4、内存超限制

SHOW VARIABLES LIKE "%mem_limit%";

MySQL [dorisonline]> SHOW VARIABLES LIKE "%mem_limit%";
+------------------------------------------+------------+
| Variable_name | Value |
+------------------------------------------+------------+
| broadcast_hashtable_mem_limit_percentage | 0.2 |
| exec_mem_limit | 2147483648 |
+------------------------------------------+------------+
2 rows in set (0.00 sec)
SET GLOBAL exec_mem_limit = 8589934592;

SET GLOBAL exec_mem_limit = 4294967296;

245 内存已不足
新增了247节点，重新建表执行成功

5、修改表字段类型

修改列类型
alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
alter table ads_actual_forecast_sales_ratio modify column `future_1_month_sale` decimalv3(25, 12) NULL COMMENT '预测未来一月销售金额';
alter table ads_actual_forecast_sales_ratio modify column `total_money_month` decimalv3(25, 12) NULL COMMENT '实际销售金额';
alter table ads_actual_forecast_sales_ratio modify column `actual_forecast_sales_ratio` decimalv3(25, 12) NULL COMMENT '预测和实际销售金额占比';

alter table ads_actual_forecast_sales_ratio modify column `future_1_month_sale` decimalv3(25, 12) NULL COMMENT '预测未来一月销售金额';

alter table dws_new_customers modify column `in_to_first_datediff_time` int(20) comment '录入到首单时间差';

6、doris where 中的条件判断

IF(判断语句，v1，v2)

select dt,
ym,
(year(dt) - year(cast(concat(ym, '-01') as date))) * 12 + month(dt) -
month(cast(concat(ym, '-01') as date)) month_diff,
total_money_month
from ads_sale_money_contract_analyse_month
where dt = '2023-08-31' and IF('2023-08-31' = LAST_DAY('2023-08-31'), ym <= '2023-08', ym < '2023-08')

7、修改表名

ALTER TABLE table1 RENAME table2;

ALTER TABLE ads_sale_money_contract_analyse_month RENAME ads_sale_money_contract_analyse_month_back;

8、timeout when waiting for send fragments RPC. Wait(sec): 5, host: xxx(ip)

批量插入数据时遇到的超时错误，默认超时时间为300s，可以登录到mysql客户端用命令自定义修改query_timeout参数，我这里改成了3000s就不报错了。

SHOW VARIABLES LIKE “%query_timeout%”;
SET query_timeout = 3000;

Caused by: java.sql.SQLException: timeout when waiting for send fragments RPC. Wait(sec): 5, host: 192.168.1.246

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1031)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)

at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeUpdate(SqlTask.java:312)

at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:210)

at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:161)

... 9 common frames omitted

10、增加字段

ALTER TABLE dwd_contractlist_area ADD COLUMN menuarea_origin_gate2 int(11) NULL COMMENT '级别' AFTER hl_gettime;
ALTER TABLE dwd_contractlist_area ADD COLUMN menuarea_origin_menuname text NULL COMMENT 'menuarea表区域名称' AFTER menuarea_origin_gate2;
ALTER TABLE dwd_contractlist_area ADD COLUMN menuarea_big_area int(11) NULL COMMENT '大区id' AFTER menuarea_origin_menuname;
ALTER TABLE dwd_contractlist_area ADD COLUMN menuarea_big_area_menuname text NULL COMMENT '大区名称' AFTER menuarea_big_area;

11、Doris修改表的评论

alter table dws_potential_customer_amount_base modify comment '当前12个月内的客户名称及月份差分析表';

12、删除分区

PARTITION p0 VALUES [('0000-01-01'), ('2023-07-28'))

alter table dws_cost_money_year drop partition p0;
alter table dws_rmf_customer_value_analysis drop partition p0;

ALTER TABLE dws_cost_money_year SET ("dynamic_partition.enable" = "false")

alter table dws_cost_money_year drop partition P20230731;

alter table dws_rmf_customer_value_analysis drop partition P20230731;

alter table ads_cost_money_month drop partition p0;

alter table ads_rmf_customer_value_analysis drop partition p0;

alter table ads_cost_money_year drop partition p0;

alter table ads_sale_money_company_analyse_month drop partition p0;