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

in #utopian-io7 years ago (edited)

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:
eror1.PNG

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?
eror2.png

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.

eror3.png

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

eror4.png

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.

image.png

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

eror5.png

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:
image.png

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.

eror5.png

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




Posted on Utopian.io - Rewarding Open Source Contributors

Sort:  

Thank you for the contribution. It has been approved.

  • I did approve, but you should fix your grammar. Otherwise, it won't be formal as stated in rules. Reason for my approval is your post showed how to reproduce it, detect it, fix it. And this is the preferable way of teaching something. If you need assistance with grammar, you can use Grammarly or like programs.

You can contact us on Discord.

[utopian-moderator]

Thanks mod @yokunjon I will fix my grammar. I will use Grammarly like your suggestion.😀

Hey @iwaydi I am @utopian-io. I have just upvoted you!

Achievements

  • You have less than 500 followers. Just gave you a gift to help you succeed!
  • Seems like you contribute quite often. AMAZING!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

Coin Marketplace

STEEM 0.23
TRX 0.22
JST 0.037
BTC 98660.69
ETH 3408.24
USDT 1.00
SBD 3.18