Laravel SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

Question

i am very new in Laravel and this is my first project in Laravel.As usual, first of all i am developing a full user authentication system.I can registered an single user,can send an user verification email and after clicking that link i can activate a new user account, can login and can logout.But after that whenever i am trying to registered another new user and after clicking the verification link , i am facing an exception which is,

Illuminate \ Database \ QueryException
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key     'users_code_unique' (SQL: update `users` set `code` = , `active` = 1, `updated_at` = 2014-07-   25 04:26:06 where `id` = 41)

now this is my route.php,

<?php

Route::get('/',array(
     'as'   =>'home',
     'uses' =>'HomeController@index'
     ));

Route::get('/signin',array(
    'as'        =>'signin',
    'uses'      =>'AccountController@signinGet'
    ));

Route::get('/signup',array(
    'as' => 'signup',
    'uses' => 'AccountController@signupGet'
    ));

/* 

/* 
/Authenticated Group
*/
 Route::group(array('before' => 'auth'),function(){
 /* 
 /Sign Out(GET)
*/

 Route::get('/signout',array
    (
        'as' => 'signout',
        'uses' => 'AccountController@signoutGet'
    ));

 });

/* 
/UnAuthenticated Group
*/
Route::group(array('before' => 'guest'),function(){

/* CSRF Protect*/
Route::group(array('before' => 'csrf'),function(){
        /*
        / Create Account(POST)
        */
        Route::post('/signup',array(
                'as'=> 'signup',
                'uses'=>'AccountController@signupPost'
            ));

        /*
        / Sign In(POST)
        */

        Route::post('/signin',array(
                'as' => 'signin-post',
                'uses' => 'AccountController@signinPost'
            ));
 });

 /* 
 / Sign In (GET) 
 */

 Route::get('/signin',array(
        'as' => 'signin',
        'uses' => 'AccountController@signinGet'
    ));

 /* 
 /Create Account(GET) 
 */
 Route::get('/signup',array(
        'as' => 'signup',
        'uses'=> 'AccountController@signupGet'
    ));
 Route::get('signup/account/activate/{code}',array(
        'as'        =>'activate-account',
        'uses'      =>'AccountController@activatePost'
    ));
 });
 ?>

and this is my AccountController

<?php

class AccountController extends \BaseController {

public function signinGet()
{
    return View::make('account.signin');
}

public function signinPost(){

    $validator = Validator::make(Input::all(),array(
            'email' => 'required|email',
            'password' => 'required'
        ));

    if($validator->fails()){
        //redirect to the signin page
        return Redirect::route('signin')
                ->withErrors($validator)
                ->withInput();
    }else{
        //Attempt user singin

        $auth = Auth::attempt(array
            (
                'email' => Input::get('email'),
                'password' => Input::get('password'),
                'active' => 1
            ));

        if($auth){
            //Redirect To intented URL
            return Redirect::intended('/');
        }
        else
        {

            return Redirect::route('signin')
                                ->with('global','The username or password you provided is wrong or account not activated!');
        }

    }

            return Redirect::route('signin')    
                                ->with('global','There is a problem Signing You in.');
}


/**
 * Show the form for creating a new resource.
 *
 * @return Response
 */

public function signupGet()
{
    return View::make('account.signup');
}

public function signupPost()
{
    $validator = Validator::make(Input::all(), array(

        'email'             => 'required|max:255|email|unique:users',
        'username'          => 'required|min:3|unique:users',
        'password'          => 'required|min:6',
        'password_again'    =>  'required|same:password'

        )
    );

    if($validator->fails())
    {
        return Redirect::route('signup')
            ->withErrors($validator)
            ->withInput();
    }else
    {
        $email          = Input::get('email');
        $username       = Input::get('username');
        $password       = Input::get('password');

        //Activation Code
        $code = str_random(60);

        $user = User::create(array(
                    'email'     => $email,
                    'username'  => $username,
                    'password'  => Hash::make($password),
                    'code'      => $code,
                    'active'    => 0                
                    )
        );

        if($user){
            //User Activation Code Creation
            Mail::send('emails.auth.activate', array('link' => URL::route('activate-account',$code), 'username' => $username),function($message) use ($user)
                {
                    $message->to($user->email,$user->username)->subject('Activate Your Account');
                });

            return Redirect::route('signup')
                            ->with('global','Your Account has been created! We have sent you an email to activate your account.Please Check the both the Inbox and Spam Folder.');

        }

    }


    //return 'This is a Post Result';
}

public function activatePost($code){

    $user = User::where('code','=',$code)->where('active','=',0);
    if($user->count()){
        $user = $user->first();

        $user->active = 1;
        $user->code = '';
        if($user->save()){
            return Redirect::route('home')
                            ->with('global','Activated!.You can sign in now!'); 
        }
    }

    else{
        return Redirect::route('signup')
                        ->with('global','Sorry!We could not activate your acount,please try again later.');
    }
}


public function signoutGet(){

    Auth::logout();
    return Redirect::route('home');
}
}
?>

and this is my create user migration file

<?php

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

class CreateUsersTable extends Migration {

public function up()
{
    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('username',255)->unique();
        $table->string('email',255)->unique();
        $table->string('password',60);
        $table->string('password_temp',60);
        $table->string('code',60)->unique();
        $table->integer('active');
        $table->timestamps();
    });
}

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

}
?>

and this is my user.php

<?php

use Illuminate\Auth\UserTrait;
use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableTrait;
use Illuminate\Auth\Reminders\RemindableInterface;

class User extends Eloquent implements UserInterface, RemindableInterface {


    public function getRememberToken()
    {
        return $this->remember_token;
    }

    public function setRememberToken($value)
    {
        $this->remember_token = $value;
    }

    public function getRememberTokenName()
    {
        return 'remember_token';
    }

protected $fillable = array('email','username','password','password_temp','code','active');

use UserTrait, RemindableTrait;

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



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

}
?>

now whats the problem?

Answer 1

Integrity constraint violation Duplicate entry problems are due to database schema inconsistence being detected by laravel.

Integrity constraint violation Duplicate entry problems are likely to occur when the database schema was preserved by laravel migration, and then the db scheme is updated somehow outside of laravel migration's control (eg. developers manually (without via performing a laravel migration) alter the db schema in a db client OR the db scheme is updated by importing a piece of SQL dump that is inconsistent with the db scheme -- as a result of that, the importing unexpectedly updates the laravel-migration-preserved db schema.

To avoid the second kind of harm, be careful when importing SQL dump data into db: ensure the SQL dump data is consistent with the current db schema before your SQL dump is imported.

Overall, when doing laravel development, the db schema should always be preserved by laravel migration to avoid db schema inconsistence.

Answer 2

your problem can use validator to check. simply use as this:

use Validator;
use Request;
//...
//unique will pre check the key code weather if unique in tbl_name
public function yourfunc(Request $request) {
    // set the rules to check
    $rules = ['code'=>'required|unique:tbl_name'];
    $validator = Validator::make($request->all(), $rules);
    if ($validator->fails()) {
       // handler errors
       $erros = $validator->errors();
    }
    //... everything is ok here
}

you can explore more at laravel validation

Answer 3

There a few things you need to do to improve your code. But duplicate entry usually happens when you set a column to unique and tries to re inset the same data into another row. The most time it get confusing is when you check your table and and find the column empty. Whoop! When a column is set to unique and empty, it means no other column can contain empty data.

In simple terms the form of the column can not be duplicated, either null or with data.

Answer 4

Make sure your code field is nullable, then instead of setting it's value to empty string, make it null:

$code = null;

Then you will be able to save it as NULL (MySQL) while it remains unique.

---

Also change this one:

 $user = User::where('code','=',$code)->where('active','=',0);
   if($user->count()){
     $user = $user->first();

To:

 $user = User::where('code','=',$code)->where('active','=',0)->first();
   if(count($user)){

You don't need to call db twice, just check if returned result is not null (count will do), meaning it returned a User object.

Answer 5

I found it. You've set code column as unique, though you're setting it to empty string after user click an activation link. And there already is a row in table with code=''; so it throws an error. The problem is here (activatePost):

$user->code = '';

So either don't empty it, set it to something else or set db colums as not unique.

I would leave the code without emptying it and additionally I would check if user was activated - a simple if in activatePost. Maybe it's a good idea to verify user not only according to code, but also with a hashed id in link.