caused by com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception unknown column

Question

I use springboot + mybatis and run with the SpringJUnit4ClassRunner, use the default database h2, and the scripts:

create table sql:

create TABLE IF NOT EXISTS `test_company` (
  `id`          bigint   unsigned  not null auto_increment,
  `pid`         bigint   DEFAULT null COMMENT '',
  `outer_id`    bigint DEFAULT null COMMENT '',
  `name`        VARCHAR(256) DEFAULT NULL COMMENT '',
  `parent_company_name` VARCHAR(256) DEFAULT NULL COMMENT '',
  `user_id` bigint DEFAULT NULL COMMENT '用户ID',
  `user_name` VARCHAR(64) DEFAULT NULL COMMENT '',
  `type`        INTEGER DEFAULT null COMMENT '',
  `level`       INTEGER DEFAULT null COMMENT '',
  `status`      INTEGER DEFAULT null COMMENT ''
);

the model Company:

@Data
@NoArgsConstructor
public class Company implements Serializable {

    private static final long serialVersionUID = -5822686079080905768L;
    private Long id;    private Long pid;
    private Long outerId;
    private String name;
    private String parentCompanyName;
    private Long userId;
    private String userName;
    private Integer type;
    private Integer level;    
    private Integer status;
}

the resultMap of mapper:

<resultMap id="CompanyMap" type="Company">
    <id column="id" property="id"/>
    <result column="pid" property="pid"/>
    <result column="outer_id" property="outerId"/>
    <result column="name" property="name"/>
    <result column="parent_company_name" property="parentCompanyName"/>
    <result column="user_id" property="userId"/>
    <result column="user_name" property="userName"/>
    <result column="type" property="type"/>
    <result column="level" property="level"/>
    <result column="status" property="status"/>
</result>

some sqls:

<sql id="tb">
    test_company
</sql>

<sql id="cols_all">
    id, <include refid="cols_exclude_id" />
</sql>

<sql id="cols_exclude_id">
    pid, outer_id, `name`, `parent_company_name`, user_id, user_name, `type`, `level`, status
</sql>

<sql id="vals">
    #{pid}, #{outerId}, #{name}, #{parentCompanyName}, #{userId}, #{userName}, #{type}, #{level}, #{status}
</sql>

create sql:

<insert id="create" parameterType="Company" keyProperty="id" useGeneratedKeys="true">
    INSERT INTO
    <include refid="tb" />
    (<include refid="cols_exclude_id" />)
    VALUES
    (<include refid="vals" />)
</insert>

it runs well before there is not the column parent_company_name,

but when I added the column parent_company_name and rerun the DAO method with create it had this error

    org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'parent_company_name' in 'field list'
### The error may involve Company.create-Inline
### The error occurred while setting parameters

my IDE was IntelliJ IDEA, I don't know if it caused by the cache or something else, I just added a new column and checked all sql scripts was correct.

score 0 · Accepted Answer · answered Aug 07 '16 at 02:18

From what I understand you are using spring boot and writing migration scripts and I am assuming this is flyway or some other migration tool. One thing to note is that once a migration script runs it won't re-run if you did not configure it to do validation on migrate or any other configuration the migration might be using.

So you should check your database to see if the column was actually created. If it wasn't then you will have to write a second migration script to alter the existing table instead of inserting the parent_company_name column to the initial script. thus your version 2 migration will have:

ALTER TABLE `test_company` ADD COLUMN `parent_company_name` VARCHAR(256) DEFAULT NULL COMMENT '';

Alternatively if you are using flyway and you are still in development mode, you can do a validate on migrate and set clean on validation error to true so that it cleans the database and re runs your migration script.

thanks, test by h2, it will create a table in my local server of mysql, and should add your sql `alert table ...` to update database. — Jarvis, Aug 08 '16 at 01:49
@BenD if this answer works for you. kindly mark as correct answer. — Akintayo Jabar, Aug 08 '16 at 09:09

caused by com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception unknown column

1 Answers1