TRUNCATE TABLE {table_name} RESTART IDENTITY can't reset auto-augment primary

[xiaoyingzhi]

Version

• h2-database verson：1.4.200

  <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.4.200</version>
  </dependency>
  

• JDK：jdk1.8.0_201

Problem

When using the TRUNCATE TABLE {table_name} RESTART IDENTITY command to initialize a table, the auto-augment primary key is not reset if there is a data in the table.

Show The Code

To start, insert a piece of data

• Insert a piece of data
• TRUNCATE table
• Insert four data to table
• print: id start with 2, not 1,This is not what was expected

    private static final String JDBC_URL = "jdbc:h2:E:/Java/H2Test/user";

    private static final String USER = "root";

    private static final String PASSWORD = "root";

    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        Statement statement = conn.createStatement();

        //create table
        statement.execute("DROP TABLE IF EXISTS USER_INF");
        statement.execute("CREATE TABLE USER_INF(id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), sex VARCHAR(10))");
        
        //first, insert a data
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('tom', 'man') ");
        //statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('jack', 'man') ");
        //result
        ResultSet resultSet = statement.executeQuery("select * from USER_INF");
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + ", " + resultSet.getString("name") + ", " + resultSet.getString("sex"));
        }

        //TRUNCATE table
        statement.execute("TRUNCATE TABLE USER_INF RESTART IDENTITY");
        System.out.println("===========================================================");

        //insert
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('tom', 'man') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('jack', 'women') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('marry', 'man') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('lucy', 'man') ");
        //result
        resultSet = statement.executeQuery("select * from USER_INF");
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + ", " + resultSet.getString("name") + ", " + resultSet.getString("sex"));
        }

        statement.close();
        conn.close();
    }
}

1, tom, man
===========================================================
2, tom, man
3, jack, women
4, marry, man
5, lucy, man

---

To start, insert two pieces of data

• Insert two pieces of data
• TRUNCATE table
• Insert four data to table
• print: id start with 1,This is to be expected

public class H2Test {

    private static final String JDBC_URL = "jdbc:h2:E:/Java/H2Test/user";

    private static final String USER = "root";

    private static final String PASSWORD = "root";

    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        Statement statement = conn.createStatement();

        //create table
        statement.execute("DROP TABLE IF EXISTS USER_INF");
        statement.execute("CREATE TABLE USER_INF(id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), sex VARCHAR(10))");

        //insert two pieces of data
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('tom', 'man') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('jack', 'man') ");
        //result
        ResultSet resultSet = statement.executeQuery("select * from USER_INF");
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + ", " + resultSet.getString("name") + ", " + resultSet.getString("sex"));
        }

        //TRUNCATE
        statement.execute("TRUNCATE TABLE USER_INF RESTART IDENTITY");
        System.out.println("===========================================================");

        //insert
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('tom', 'man') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('jack', 'women') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('marry', 'man') ");
        statement.executeUpdate("INSERT INTO USER_INF (`name`,`sex`) VALUES('lucy', 'man') ");
        //result
        resultSet = statement.executeQuery("select * from USER_INF");
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + ", " + resultSet.getString("name") + ", " + resultSet.getString("sex"));
        }

        statement.close();
        conn.close();
    }
}

1, tom, man
2, jack, man
===========================================================
1, tom, man
2, jack, women
3, marry, man
4, lucy, man

Other

The following image was taken from the official document，This is an official document, and I think it's problematic. Maybe it needs to be flagged so the user knows

The last

Based on the latest code, this problem has been fixed, and maybe a new version should be released. After all, it's been a year since the last release. Isn't it? hhh

[katzyn]

If this issue is already fixed, there is nothing to do with it. There will be no patch releases for 1.4.200.

Release plans for H2 2.0 are discussed in #2491.

Please note that you're using a legacy (even for 1.4.200) syntax; it should only be used in MySQL compatibility mode. In other modes standard-compliant ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY should be used instead.