Large Auto Increment IDs

问题:

I'm having a strange issue with some auto-inc IDs on a table, where instead of going up by 1 each time, it seems to be going up by 10 each time.

I'm using the

• ClearDB MySQL Addon for Heroku
• PHP 5.5.15
• Apache 2.4.10
• Laravel dev branch (4.something)

I've created the database via artisan's migrate functionality, My migration for the database table is

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCheckinTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('checkins', function(Blueprint $table)
        {
            $table->increments('id');

            $table->integer('visitor_id');
            $table->integer('meeting_id');

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('checkins');
    }

}

However when Im creating a new entry via this

    $this->checkin = new Checkin;
    $this->checkin->visitor_id = $this->id;
    $this->checkin->meeting_id = $this->nextMeetingId();
    $this->checkin->save();

The Checkin Class looks like

<?php

class Checkin extends Eloquent {

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'checkins';

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = array('updated_at', 'visitor_id');

    protected $fillable = array();

    public function meeting(){
        return $this->hasOne('Meeting','id', 'meeting_id');
    }

    public function client(){
        return $this->hasOne('Visitor','id','visitor_id');
    }

}

However after F5ing and adding multiple entries the database table now looks like

id  visitor_id meeting_id updated_at created_at

1   1   0   2014-08-04 21:25:25 2014-08-04 21:25:25
11  1   0   2014-08-04 21:35:54 2014-08-04 21:35:54
21  1   0   2014-08-04 21:35:57 2014-08-04 21:35:57
31  1   0   2014-08-04 21:35:59 2014-08-04 21:35:59
41  1   0   2014-08-04 21:36:01 2014-08-04 21:36:01
51  1   0   2014-08-04 21:36:03 2014-08-04 21:36:03

As you can see the id's is going up by 10 each time rather than 1.

So if anybody knows the reason for this, please update me :)

Many thanks

回答1:

As an answer for others experiencing this issue.

The reason for the big steps was due to a MySQL Configuration that ClearDB had implimented.

Their reasoning for doing this is listed here: http://www.cleardb.com/developers/help/faq#general_16 (Thanks Razor for this)

If you need to investigate your increment settings yourself you can run the following query

SHOW VARIABLES LIKE 'auto_inc%';

this will output something like

auto_increment_increment    10
auto_increment_offset   1

So you can see the reason for my jumps was because this was set to 10.

As @Juergen d said you should be able to change this if you need to by the following queries.

SET @@auto_increment_increment=1
SET GLOBAL auto_increment_increment=1;

I however didnt change this setting as ClearDB set it for a reason, and I was only querying this incase it was something i had mis-configured.

Case Closed, Thanks to everybody for their input.