MYSQL JSON类型 当数据中某字段值为NULL时生成的sql有错

关键错误 EventColumn[index=3,columnType=12,columnName=external,columnValue={"upstreams": NULL},isNull=false,isKey=false,isUpdate=true] 详细错误日志

对于json字段 NULL不是json规范的值 null才是

pid:1 nid:1 exception:setl:com.alibaba.otter.node.etl.load.exception.LoadException: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into test.sc_affiliated_enterprise(update_at , create_at , external , borrower_id , business_mode , business_license_number , company_name , legal_representative , relation_legal_representative , business_time , business_scale , business_address , business_time_current_address , signboard_name , cooperate_business_time , cooperate_business_scale , delivery_method , province , city , district , business_licence , turnover_last_year , expected_turnover_this_year , inventory_value , number_of_employees , business_address_area , business_address_lease_time , peak_season , sales_level , pos_code , channel_level , channel_desc , annual_rent , decoration_cost , equipment_cost , initial_purchase_amount , id) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update update_at=values(update_at) , create_at=values(create_at) , external=values(external) , borrower_id=values(borrower_id) , business_mode=values(business_mode) , business_license_number=values(business_license_number) , company_name=values(company_name) , legal_representative=values(legal_representative) , relation_legal_representative=values(relation_legal_representative) , business_time=values(business_time) , business_scale=values(business_scale) , business_address=values(business_address) , business_time_current_address=values(business_time_current_address) , signboard_name=values(signboard_name) , cooperate_business_time=values(cooperate_business_time) , cooperate_business_scale=values(cooperate_business_scale) , delivery_method=values(delivery_method) , province=values(province) , city=values(city) , district=values(district) , business_licence=values(business_licence) , turnover_last_year=values(turnover_last_year) , expected_turnover_this_year=values(expected_turnover_this_year) , inventory_value=values(inventory_value) , number_of_employees=values(number_of_employees) , business_address_area=values(business_address_area) , business_address_lease_time=values(business_address_lease_time) , peak_season=values(peak_season) , sales_level=values(sales_level) , pos_code=values(pos_code) , channel_level=values(channel_level) , channel_desc=values(channel_desc) , annual_rent=values(annual_rent) , decoration_cost=values(decoration_cost) , equipment_cost=values(equipment_cost) , initial_purchase_amount=values(initial_purchase_amount) , id=values(id)]; Data truncation: Invalid JSON text: "Invalid value." at position 14 in value for column 'sc_affiliated_enterprise.external'.; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Invalid JSON text: "Invalid value." at position 14 in value for column 'sc_affiliated_enterprise.external'. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:625) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:617) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:545) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:462) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Invalid JSON text: "Invalid value." at position 14 in value for column 'sc_affiliated_enterprise.external'. at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042) at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more :----------------- - PairId: 60 , TableId: 118 , EventType : I , Time : 1562122637000 - Consistency : , Mode :

---Pks EventColumn[index=0,columnType=-5,columnName=id,columnValue=973,isNull=false,isKey=true,isUpdate=true] ---oldPks

---Columns EventColumn[index=1,columnType=93,columnName=update_at,columnValue=2019-01-20 15:32:09,isNull=false,isKey=false,isUpdate=true] EventColumn[index=2,columnType=93,columnName=create_at,columnValue=2019-01-20 15:32:09,isNull=false,isKey=false,isUpdate=true] EventColumn[index=3,columnType=12,columnName=external,columnValue={"upstreams": NULL},isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=-5,columnName=borrower_id,columnValue=1202,isNull=false,isKey=false,isUpdate=true] EventColumn[index=5,columnType=12,columnName=business_mode,columnValue=PERSONAL,isNull=false,isKey=false,isUpdate=true] EventColumn[index=6,columnType=12,columnName=business_license_number,columnValue=xxxx,isNull=false,isKey=false,isUpdate=true] EventColumn[index=7,columnType=12,columnName=company_name,columnValue=xx,isNull=false,isKey=false,isUpdate=true] EventColumn[index=8,columnType=12,columnName=legal_representative,columnValue=xx,isNull=false,isKey=false,isUpdate=true] EventColumn[index=9,columnType=12,columnName=relation_legal_representative,columnValue=SELF,isNull=false,isKey=false,isUpdate=true] EventColumn[index=10,columnType=12,columnName=business_time,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=11,columnType=12,columnName=business_scale,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=12,columnType=12,columnName=business_address,columnValue=内黄县马上乡潭头村安濮路西侧,isNull=false,isKey=false,isUpdate=true] EventColumn[index=13,columnType=12,columnName=business_time_current_address,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=14,columnType=12,columnName=signboard_name,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=15,columnType=12,columnName=cooperate_business_time,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=16,columnType=12,columnName=cooperate_business_scale,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=17,columnType=12,columnName=delivery_method,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=18,columnType=12,columnName=province,columnValue=410000,isNull=false,isKey=false,isUpdate=true] EventColumn[index=19,columnType=12,columnName=city,columnValue=410500,isNull=false,isKey=false,isUpdate=true] EventColumn[index=20,columnType=12,columnName=district,columnValue=410527,isNull=false,isKey=false,isUpdate=true] EventColumn[index=21,columnType=12,columnName=business_licence,columnValue=37276829,isNull=false,isKey=false,isUpdate=true] EventColumn[index=22,columnType=4,columnName=turnover_last_year,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=23,columnType=4,columnName=expected_turnover_this_year,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=24,columnType=4,columnName=inventory_value,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=25,columnType=12,columnName=number_of_employees,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=26,columnType=12,columnName=business_address_area,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=27,columnType=12,columnName=business_address_lease_time,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=28,columnType=-4,columnName=peak_season,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=29,columnType=12,columnName=sales_level,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=30,columnType=12,columnName=pos_code,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=31,columnType=12,columnName=channel_level,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=32,columnType=12,columnName=channel_desc,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=33,columnType=4,columnName=annual_rent,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=34,columnType=4,columnName=decoration_cost,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=35,columnType=4,columnName=equipment_cost,columnValue=,isNull=true,isKey=false,isUpdate=true] EventColumn[index=36,columnType=4,columnName=initial_purchase_amount,columnValue=,isNull=true,isKey=false,isUpdate=true]

原提问者GitHub用户sheiy