Bad format for Timestamp ‘203‘ in column 1

原创已于 2022-11-08 15:56:20 修改 · 793 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #sql #数据库

于 2022-11-07 15:46:52 首次发布

mysql 同时被 2 个专栏收录

2 篇文章

订阅专栏

mybatis

2 篇文章

订阅专栏

博客内容描述了作者在数据库操作中遇到的一个错误：在尝试查询时，系统尝试将第一列的ID（主键，int类型）转换为Timestamp，导致报错。问题出现在11月份的数据上，删除11月数据后错误消失。作者尝试了不用Mybatis的QueryWrapper，改用XML写SQL，但问题依旧。通过查看源码发现字段被当作Date处理。最后，作者发现实体类缺少包含ID的构造函数，添加后解决了问题。

突然数据库报错，完整报错内容：

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'id' from result set.  Cause: java.sql.SQLException: Bad format for Timestamp '203' in column 1.
; SQL []; Bad format for Timestamp '203' in column 1.; nested exception is java.sql.SQLException: Bad format for Timestamp '203' in column 1.] with root cause

意思是，第一列的id转timestamp出现问题，可问题是，第一列id是主键int型的啊，我擦了

查询方式

QueryWrapper<QualityKfzsdata> wrapper = Wrappers.query();
wrapper.select("id, pushdate, region, region_code, pushmonth, sum(qlthl) as qlthl,         
        sum(fnthl) as fnthl, sum(jhzs) as jhzs, sum(thsc) as thsc");
wrapper.groupBy("pushmonth", "region_code");
List<QualityKfzsdata> list = kfzsdataService.list(wrapper);

数据库：

这件事儿牛逼就牛逼在，查11月份之前的3个月数据都好使，有11月份的数据了就不好使了。邪了门了。

我依次实验了几种情况，只要把11月份数据删除，就不报错。是不是和字段类型啥的有关。

有没有可能是mybatis有bug了，我不用QueryWrapper查询，改用mapper.xml写sql语句：

SELECT id, pushdate, region, region_code, pushmonth, SUM(qlthl) AS qlthl, SUM(fnthl) AS fnthl, SUM(jhzs) AS jhzs, SUM(thsc) AS thsc FROM quality_xxxx GROUP BY pushmonth, region_code

也tm不好使，那是咋回事儿呢，id明明是整型，为啥要转换timestamp呢，去看了眼报错的源码：

protected Timestamp getTimestampFast(int columnIndex, byte[] timestampAsBytes, int offset, int length, Calendar targetCalendar, TimeZone tz, boolean rollForward, MySQLConnection conn, ResultSetImpl rs) throws SQLException {
        try {
            Calendar sessionCalendar = conn.getUseJDBCCompliantTimezoneShift() ? conn.getUtcCalendar() : rs.getCalendarInstanceForSessionOrNew();
            boolean allZeroTimestamp = true;
            boolean onlyTimePresent = false;

            int year;
            for(year = 0; year < length; ++year) {
                if (timestampAsBytes[offset + year] == 58) {
                    onlyTimePresent = true;
                    break;
                }
            }

            for(year = 0; year < length; ++year) {
                byte b = timestampAsBytes[offset + year];
                if (b == 32 || b == 45 || b == 47) {
                    onlyTimePresent = false;
                }

                if (b != 48 && b != 32 && b != 58 && b != 45 && b != 47 && b != 46) {
                    allZeroTimestamp = false;
                    break;
                }
            }

            if (!onlyTimePresent && allZeroTimestamp) {
                if ("convertToNull".equals(conn.getZeroDateTimeBehavior())) {
                    return null;
                } else if ("exception".equals(conn.getZeroDateTimeBehavior())) {
                    throw SQLError.createSQLException("Value '" + StringUtils.toString(timestampAsBytes) + "' can not be represented as java.sql.Timestamp", "S1009", this.exceptionInterceptor);
                } else {
                    return !rs.useLegacyDatetimeCode ? TimeUtil.fastTimestampCreate(tz, 1, 1, 1, 0, 0, 0, 0) : rs.fastTimestampCreate((Calendar)null, 1, 1, 1, 0, 0, 0, 0);
                }
            } else if (this.metadata[columnIndex].getMysqlType() == 13) {
                return !rs.useLegacyDatetimeCode ? TimeUtil.fastTimestampCreate(tz, StringUtils.getInt(timestampAsBytes, offset, 4), 1, 1, 0, 0, 0, 0) : TimeUtil.changeTimezone(conn, sessionCalendar, targetCalendar, rs.fastTimestampCreate(sessionCalendar, StringUtils.getInt(timestampAsBytes, offset, 4), 1, 1, 0, 0, 0, 0), conn.getServerTimezoneTZ(), tz, rollForward);
            } else {
                int year = false;
                int month = false;
                int day = false;
                int hour = 0;
                int minutes = 0;
                int seconds = 0;
                int nanos = 0;
                int decimalIndex = -1;

                int numDigits;
                for(numDigits = 0; numDigits < length; ++numDigits) {
                    if (timestampAsBytes[offset + numDigits] == 46) {
                        decimalIndex = numDigits;
                        break;
                    }
                }

                int i;
                if (decimalIndex == offset + length - 1) {
                    --length;
                } else if (decimalIndex != -1) {
                    if (decimalIndex + 2 > length) {
                        throw new IllegalArgumentException();
                    }

                    nanos = StringUtils.getInt(timestampAsBytes, offset + decimalIndex + 1, offset + length);
                    numDigits = length - (decimalIndex + 1);
                    if (numDigits < 9) {
                        i = (int)Math.pow(10.0D, (double)(9 - numDigits));
                        nanos *= i;
                    }

                    length = decimalIndex;
                }

                int month;
                int day;
                boolean hasDash;
                switch(length) {
                case 2:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                    if (year <= 69) {
                        year += 100;
                    }

                    year += 1900;
                    month = 1;
                    day = 1;
                    break;
                case 3:
                case 5:
                case 7:
                case 9:
                case 11:
                case 13:
                case 15:
                case 16:
                case 17:
                case 18:
                case 27:
                case 28:
                default:
                    throw new SQLException("Bad format for Timestamp '" + StringUtils.toString(timestampAsBytes) + "' in column " + (columnIndex + 1) + ".", "S1009");
                case 4:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                    if (year <= 69) {
                        year += 100;
                    }

                    month = StringUtils.getInt(timestampAsBytes, offset + 2, offset + 4);
                    day = 1;
                    break;
                case 6:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                    if (year <= 69) {
                        year += 100;
                    }

                    year += 1900;
                    month = StringUtils.getInt(timestampAsBytes, offset + 2, offset + 4);
                    day = StringUtils.getInt(timestampAsBytes, offset + 4, offset + 6);
                    break;
                case 8:
                    hasDash = false;

                    for(i = 0; i < length; ++i) {
                        if (timestampAsBytes[offset + i] == 58) {
                            hasDash = true;
                            break;
                        }
                    }

                    if (hasDash) {
                        hour = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                        minutes = StringUtils.getInt(timestampAsBytes, offset + 3, offset + 5);
                        seconds = StringUtils.getInt(timestampAsBytes, offset + 6, offset + 8);
                        year = 1970;
                        month = 1;
                        day = 1;
                    } else {
                        year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 4);
                        month = StringUtils.getInt(timestampAsBytes, offset + 4, offset + 6);
                        day = StringUtils.getInt(timestampAsBytes, offset + 6, offset + 8);
                        year -= 1900;
                        --month;
                    }
                    break;
                case 10:
                    hasDash = false;

                    for(i = 0; i < length; ++i) {
                        if (timestampAsBytes[offset + i] == 45) {
                            hasDash = true;
                            break;
                        }
                    }

                    if (this.metadata[columnIndex].getMysqlType() != 10 && !hasDash) {
                        year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                        if (year <= 69) {
                            year += 100;
                        }

                        month = StringUtils.getInt(timestampAsBytes, offset + 2, offset + 4);
                        day = StringUtils.getInt(timestampAsBytes, offset + 4, offset + 6);
                        hour = StringUtils.getInt(timestampAsBytes, offset + 6, offset + 8);
                        minutes = StringUtils.getInt(timestampAsBytes, offset + 8, offset + 10);
                        year += 1900;
                    } else {
                        year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 4);
                        month = StringUtils.getInt(timestampAsBytes, offset + 5, offset + 7);
                        day = StringUtils.getInt(timestampAsBytes, offset + 8, offset + 10);
                        hour = 0;
                        minutes = 0;
                    }
                    break;
                case 12:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 2);
                    if (year <= 69) {
                        year += 100;
                    }

                    year += 1900;
                    month = StringUtils.getInt(timestampAsBytes, offset + 2, offset + 4);
                    day = StringUtils.getInt(timestampAsBytes, offset + 4, offset + 6);
                    hour = StringUtils.getInt(timestampAsBytes, offset + 6, offset + 8);
                    minutes = StringUtils.getInt(timestampAsBytes, offset + 8, offset + 10);
                    seconds = StringUtils.getInt(timestampAsBytes, offset + 10, offset + 12);
                    break;
                case 14:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 4);
                    month = StringUtils.getInt(timestampAsBytes, offset + 4, offset + 6);
                    day = StringUtils.getInt(timestampAsBytes, offset + 6, offset + 8);
                    hour = StringUtils.getInt(timestampAsBytes, offset + 8, offset + 10);
                    minutes = StringUtils.getInt(timestampAsBytes, offset + 10, offset + 12);
                    seconds = StringUtils.getInt(timestampAsBytes, offset + 12, offset + 14);
                    break;
                case 19:
                case 20:
                case 21:
                case 22:
                case 23:
                case 24:
                case 25:
                case 26:
                case 29:
                    year = StringUtils.getInt(timestampAsBytes, offset + 0, offset + 4);
                    month = StringUtils.getInt(timestampAsBytes, offset + 5, offset + 7);
                    day = StringUtils.getInt(timestampAsBytes, offset + 8, offset + 10);
                    hour = StringUtils.getInt(timestampAsBytes, offset + 11, offset + 13);
                    minutes = StringUtils.getInt(timestampAsBytes, offset + 14, offset + 16);
                    seconds = StringUtils.getInt(timestampAsBytes, offset + 17, offset + 19);
                }

                return !rs.useLegacyDatetimeCode ? TimeUtil.fastTimestampCreate(tz, year, month, day, hour, minutes, seconds, nanos) : TimeUtil.changeTimezone(conn, sessionCalendar, targetCalendar, rs.fastTimestampCreate(sessionCalendar, year, month, day, hour, minutes, seconds, nanos), conn.getServerTimezoneTZ(), tz, rollForward);
            }
        } catch (RuntimeException var23) {
            SQLException sqlEx = SQLError.createSQLException("Cannot convert value '" + this.getString(columnIndex, "ISO8859_1", conn) + "' from column " + (columnIndex + 1) + " to TIMESTAMP.", "S1009", this.exceptionInterceptor);
            sqlEx.initCause(var23);
            throw sqlEx;
        }
    }

中间一段只要length超过2，那么就报错 throw new SQLException("Bad format for Timestamp...

也就是说这个字段就是被当做date类型传过来的，邪门了。

去看了眼实体类，有一个构造函数

public QualityKfzsdata(Date pushdate, String pushmonth, String region, String regionCode, long thsc, long qlthl, long fnthl, long jhzs) {
        this.pushdate = pushdate;
        this.pushmonth = pushmonth;
        this.region = region;
        this.regionCode = regionCode;
        this.thsc = thsc;
        this.qlthl = qlthl;
        this.fnthl = fnthl;
        this.jhzs = jhzs;
    }

没有id，mybatis是不是默认来寻找这个构造函数，当做从resultset拿数据的映射关系了，立马又加了个构造函数，把id放里了

public QualityKfzsdata(Integer id, Date pushdate, String pushmonth, String region, String regionCode, long thsc, long qlthl, long fnthl, long jhzs) {
        this.id = id;
        this.pushdate = pushdate;
        this.pushmonth = pushmonth;
        this.region = region;
        this.regionCode = regionCode;
        this.thsc = thsc;
        this.qlthl = qlthl;
        this.fnthl = fnthl;
        this.jhzs = jhzs;
    }

哎呀我去，这回好使了，我可真是个小天才。