mysql Range按时间分区问题 Table has no partition for value 737669

由于负责公司的移动打卡系统功能的开发与维护，20190902突然移动打卡服务器打卡成功，数据确进不到数据库中，查询服务日志发现问题所在：

2019-09-02 09:13:12,914 INFO PunchCreator (PunchCreator.java:80) - staffPunchesInsert has been invoked.staffId:92554046719926272,motive:0,egress:0,location:沈阳市浑南新区高歌路2-1号沈阳昂立信息园A座418房间,punchTime:2019-09-02 09:13:12
2019-09-02 09:13:12,916 INFO PunchCreator (PunchCreator.java:102) - -----------1-------------------userNo:X-ZN002448,userName:齐国立
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ insert into StaffPunches(staffId,motive,egress,location,punch_time)  values(?,?,?,?,?)  ]; SQL state [HY000]; error code [1526]; (conn:251545) Table has no partition for value 737669; nested exception is java.sql.SQLException: (conn:251545) Table has no partition for value 737669
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
        at com.aspirecn.awas.core.punch.PunchCreator.staffPunchesInsert(PunchCreator.java:103)
        at com.aspirecn.awas.core.punch.PunchCreator.createStaffPunches(PunchCreator.java:248)
        at sun.reflect.GeneratedMethodAccessor42.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
        at com.sun.proxy.$Proxy18.createStaffPunches(Unknown Source)
        at com.alibaba.dubbo.common.bytecode.Wrapper4.invokeMethod(Wrapper4.java)
        at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)
        at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)
        at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)
        at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)
        at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
        at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113)
        at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84)
        at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170)
        at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)
        at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: (conn:251545) Table has no partition for value 737669
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:179)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:118)
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:250)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:224)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:192)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
        ... 39 more
Caused by: java.sql.SQLException: Table has no partition for value 737669
Query is:  insert into StaffPunches(staffId,motive,egress,location,punch_time)  values(?,?,?,?,?)  , parameters [92554046719926272,0,0,'沈阳市浑南新区高歌路2-1号沈阳昂立信息园A座418房间','2019-09-02 09:13:12']
        at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:146)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:221)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:218)
        ... 45 more
2019-09-02 09:13:12,920 ERROR PunchCreator (PunchCreator.java:110) - error occurs when inserting into StaffPunches table.
2019-09-02 09:13:12,920 INFO PunchCreator (PunchCreator.java:252) - createStaffPunches has completed.

 

问题原因：

    range按时间进行分区后，分区只记录到20190901日，超过这个日期记录是不会进入到系统中，所以报错了...

解决办法：

         发现数据库打卡记录并不是很多，所以进行去掉分区，进行数据备份

 

后续如果有好的分区方式我会第一时间跟大家进行分享！！！