使用spring jdbc时，MySQL auto_increment 10而不是1

I have been following this guide for a personnal project and I am facing this weird problem : http://www.tutorialspoint.com/spring/spring_jdbc_example.htm

我一直在关注个人项目的这个指南，我正面临着这个奇怪的问题：http：//www.tutorialspoint.com/spring/spring_jdbc_example.htm

When inserting values with spring jdbc library, the id's values jump from 1 to 11 to 21... etc. Although, when manually querying the database with a simple insert, the id are working as intended, ie : 1,2,3, etc...

当使用spring jdbc库插入值时，id的值从1跳到11跳到21 ...等等。虽然，当使用简单插入手动查询数据库时，id正在按预期工作，即：1,2,3，等等...

Here is the infos :

这是信息：

spring-jdbc 4.2.3 spring-context 4.3.3 mysql-connector-java 5.1.37

Database : MySQL Community Server(GPL) 5.5.45-log hosted on Azure with ClearDB add on.

数据库：在Azure上托管的MySQL社区服务器（GPL）5.5.45-log，添加了ClearDB。

Create table :

创建表格：

CREATE TABLE runners(
   id   INT NOT NULL AUTO_INCREMENT,
   firstName VARCHAR(20) NOT NULL,
   lastName VARCHAR(20) NOT NULL,
   location VARCHAR(20) NOT NULL,
   PRIMARY KEY (ID)
);

When I am querying : SHOW VARIABLES LIKE 'auto_inc%'; , I get : auto_increment_increment 1 auto_increment_offset 1

当我查询时：显示变量如'auto_inc％'; ，我得到：auto_increment_increment 1 auto_increment_offset 1

Here is the code :

这是代码：

Runner.java

public class Runner{
    private Integer id;
    private String firstName;
    private String lastName;
    private String location;

    public Integer getId(){
        return this.id;
    }

    public String getFirstName(){
        return this.firstName;
    }

    public String getLastName(){
        return this.lastName;
    }

    public String getLocation(){
        return this.location;
    }

    public void setId(int id){
        this.id = id;
    }

    public void setFirstName(String firstName){
        this.firstName = firstName;
    }

    public void setLastName(String lastName){
        this.lastName = lastName;
    }

    public void setLocation(String location){
        this.location = location;
    }
}

RunnerDAO.java

public interface RunnerDAO {

       public void setDataSource(DataSource ds);

       public void create(String firstName, String lastName, String location);

       public Runner getRunner(Integer id);

       public List<Runner> listRunners();

       public void delete(Integer id);

       public void update(Integer id, String firstName, String lastName, String location);
}

RunnerJDBCTEmplate.java

public class RunnerJDBCTemplate implements RunnerDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;

   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }

   public void create(String firstName, String lastName, String location) {
      String SQL = "insert into runners (firstName, lastName,location) values (?, ?, ?)";

      jdbcTemplateObject.update( SQL, firstName, lastName, location);
      System.out.println("Created Record FirstName = " + firstName + " LastName = " + lastName+ " Location = " + location);
      return;
   }

   public Runner getRunner(Integer id) {
      String SQL = "select * from runners where id = ?";
      Runner runner = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new RunnerMapper());
      return runner;
   }

   public List<Runner> listRunners() {
      String SQL = "select * from runners";
      List <Runner> runners = jdbcTemplateObject.query(SQL,new RunnerMapper());
      return runners;
   }

   public void delete(Integer id){
      String SQL = "delete from runners where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }

   public void update(Integer id, String firstName, String lastName, String location){
      String SQL = "update runners set firstName = ?,lastName = ?, location = ? where id = ?";
      jdbcTemplateObject.update(SQL, firstName, lastName, location , id);
      System.out.println("Updated Record with ID = " + id );
      return;
   }
}

RunnerMapper.java

public class RunnerMapper implements RowMapper<Runner> {
   public Runner mapRow(ResultSet rs, int rowNum) throws SQLException {
      Runner runner = new Runner();
      runner.setId(rs.getInt("id"));
      runner.setFirstName(rs.getString("firstName"));
      runner.setLastName(rs.getString("lastName"));
      runner.setLocation(rs.getString("location"));
      return runner;
   }
}

When I run this main.java :

当我运行这个main.java时：

public static void main(String[] args) throws Exception {
        ApplicationContext context = 
                 new ClassPathXmlApplicationContext("Beans.xml");

          RunnerJDBCTemplate runnerJDBCTemplate = 
          (RunnerJDBCTemplate)context.getBean("RunnerJDBCTemplate");

          System.out.println("------Records Creation--------" );
          runnerJDBCTemplate.create("John", "Doe", "1234 street");
          runnerJDBCTemplate.create("Jane", "Dude", "5678 boulevard");
          runnerJDBCTemplate.create("Johnny", "Duh", "111 avenue");
    }
}

I get the following output in my database

我在我的数据库中得到以下输出

1   John    Doe 1234 street
11  Jane    Dude    5678 boulevard
21  Johnny  Duh 111 avenue

I tried SET @@auto_increment_increment=1;

我试过SET @@ auto_increment_increment = 1;

Again, when querying manually everything is fine.

再次，当手动查询一切都很好。

Somebody has a clue ? Thanks !

有人有线索吗？谢谢！

EDIT

编辑

I tried an other mysql hosting and now it works perfectly. It seems like Azure ClearDb Addon is the source of the problem. Still, if someone has an explanation, it will be greatly appreciated.

我尝试了另一个mysql托管，现在它完美无缺。似乎Azure ClearDb Addon是问题的根源。不过，如果有人有解释，我们将不胜感激。

2 个解决方案

#1

I believe Azure is using the same ClearDB as Heroku. So the this answer here is the best explanation Heroku MySQL Auto Increment

我相信Azure使用与Heroku相同的ClearDB。所以这里的答案是Heroku MySQL Auto Increment的最佳解释

In short, it's a ClearDB strategy to prevent collision http://www.cleardb.com/developers/help/faq#general_16

简而言之，它是一种防止碰撞的ClearDB策略http://www.cleardb.com/developers/help/faq#general_16

#2

Try running the "SET @@auto_increment_increment=0" in your code, rather than running it manually. Make sure you run it, before running any DML.

尝试在代码中运行“SET @@ auto_increment_increment = 0”，而不是手动运行它。在运行任何DML之前，请确保运行它。

By the way, you can investigate the root cause, by logging every query that is being sent to MySQL, via:

顺便说一句，您可以通过记录发送到MySQL的每个查询来调查根本原因：

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

-- run your java code, and wait for it to finish

SET GLOBAL general_log = 'OFF';
SELECT * FROM mysql.general_log;

This gives you the queries that have been executed by your code (or any other code running in the background).

这为您提供了代码（或在后台运行的任何其他代码）执行的查询。

#1