Laravel #6 - How to fix SQLSTATE error [42000]: Syntax error or access violation: 1071 Specified key was too long in laravel

What Will I Learn?

---

Errors are often encountered while running a program and are no exception in laravel and this is very disturbing and slowing down our work. So in this tutorial, we will try to fix errors that may occur in this project. Here's what you'll learn:

• you will learn to find the source of SQLSTATE errors [42000]
• you will learn to fix SQLSTATE error [42000]

Requirements

---

To follow this tutorial. You only need to provide some tools. o this is the list of the requirements for the user:

• Xampp 5.6.3
• Framework Laravel 5.4
• Composer
• Atom text editor

Difficulty

---

• Intermediate

Preliminary

---

Errors are Error, Incorrect, or Malfunction. With the occurrence of this error, the execution of the command does not match what is expected. On some occurrence, if an error occurs, the current command who be working will be stopped because it can not continue, because the server is not running yet. But in other circumstances, the work can still continue, because the errors that occur do not affect the system and can be ignored. Just like our mistakes. Some commands have been executed but there are commands that can not be executed and errors occur.

SQLSTATE error [42000] is a warning that appears when there is an error in the process and this affects the existing database and 42000 is the number used to recognize the error that occurred and it is indicated for syntax errors or access violations. This error can occur because there are some incorrect syntax we make or not listed in the system and usually the error occurs because there are other errors as the trigger associated with the error displayed on the screen and you need to know the error on display is not the real problem in this case but it will be a clue to find the actual error.

Tutorial Content : Practice Fixing error SQLSTATE [42000]

---

In this tutorial, we will discuss how to fix the error and as usual, we will finish in 3 steps. We start from making the error, then look for the source of error, and fix the error.

Step 1: Making eror SQLSTATE [42000]

---

The first, we will discuss the error below and we will start trying to make something and display the error.

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email))

Now we will create the error as above and we will try to create a table so we can get the error it. First, create a migration file named table_error. So use the command below to create a migration file.

php artisan make:migration table_error

After that open the migration file and add this code:

  public function up()
    {
        Schema::create('eror', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('No.Error',60);
            $table->varchar('name',60);
            $table->text('category');
            $table->text('information');
            $table->varchar('Description',60);
            $table->text('solution');
            $table->timestamps();
        });
    }

After that, use this script to execute migration files that have been edited

php artisan migrate

Here is an output the command prompt after running:

You can see the output above. It says Migration table created successfully. But why error?. Now we already have the error and in the next step we will find out why this can happen.

Step 2: Looking for the source of error SQLSTATE [42000]

---

Before looking for it. We look again the following output and you will find the table user. Why can the table user appear while we create table_error?

After looking at it I assume if the error is in file migration and it turns out I am right. I'll show you how I found the error. Open the atomic text editor and then open the laravel folder into it. After that go to database/migrations .This is my migration file view and you can see there is create_users_table file. So I think this is the source of the problem and I delete that file and a new problem arises when I execute again using php artisan merge.

This is the output at the command prompt after I delete the create_user migration file and the results are similar to before but there are different there. Now appears table password_resets

So I think this is the same as the previous user. Because I see it also in the migration folder. So I delete it and now inside the migration folder there is only 1 migration file left and that is table_eror but other errors appear but I think this is the source of the problem. This is the cause of the previous error. Although Migration table created successfully. This is the migration folder view now.

The following is the output at the command prompt after the migration file has one left in the folder and is executed

Now we have found the problem. it turns out the varchar method cannot be used in the migration file and this is the cause of all previous errors. migration user files that we have previously deleted have execution and because there is an error then the system does re-execute the user file while the table user was successfully created so that there was an error. well in the next step we will fix this error.

Step 3: Fixing error SQLSTATE [42000]

---

Now open the table_error migration file and then replace it with the code below that I have fixed:

    public function up()
    {
        Schema::create('eror', function (Blueprint $table) {
            $table->integer('eror_id');
            $table->increments('id');
            $table->string('nameeror',60);
            $table->text('category');
            $table->text('information');
            $table->string('Description',60);
            $table->text('solution');
            $table->timestamps();
        });
    }

After that, use this script to execute migration files that have been edited

php artisan migrate

Here is an output from the command prompt after running:

I replace the varchar method into the string<and then the object No.Error becomes eror_id and I put it in the first position. then I change the object name to name error and the results do not appear again error and the table successfully created and now we see the column of the table and you will find interesting facts.

From the table structure above we can see. All string methods change to varchar after the file migration in execution. this is what I want to show you. File migration can not use varchar but you can use string for varcharmethod because it will change after going into the database and remember this to avoid this kind of error again.

Conclusion

---

SQLSTATE error [42000] can happen because there is an error in the migration file we created so that the system will automatically execute the migration file on it that has already been executed previously so this error can occasion. In our case, the error we made is using the varchar method and the solution by replacing the varchar into a string.

Curriculum

---

• Laravel #5 - How to Use Rename table with schema in laravel
• Laravel #4 - How to Use Feature Dropping and Rollback with Migration in Laravel
• Laravel #3 - How to Use Maintenance Mode with composer in Laravel
• Laravel #2 - How to Modifications a table database with PHP artisan migration in laravel
• Laravel #1 - How to Create a Table Database in Laravel With PHP Artisan Migration

---

Posted on Utopian.io - Rewarding Open Source Contributors

---