Level 73
What does your migration look like?
May 20, 2021
5
Level 1
Question about Laravel migration query to MySQL
Hi,
I'm trying to migrate an existing Laravel project to MySQL on Vitess.
When I tried php artisan migrate, the following error was happened.
SQLSTATE[HY000]: General error: 1105 unknown error: syntax error at position 55 near 'users_id_primary' (SQL: alter table users add primary key users_id_primary(id))
The queries sent by Laravel were here;
CreateUsersTable: create table `users` (`id` char(36) not null, `name` varchar(255) not null, `email` varchar(255) not null, `email_verified_at` timestamp null, `password` varchar(255) not null, `language_settings` varchar(255) null, `avatar` varchar(255) null, `provider` varchar(20) null, `provider_id` varchar(255) null, `access_token` varchar(255) null, `remember_token` varchar(100) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
CreateUsersTable: alter table `users` add primary key `users_id_primary`(`id`)
...
And I checked the first query succeeded by accessing MySQL.
So the connection between Laravel and MySQL was established correctly.
In the case without Vitess, the migration succeeded so this seemed to be happened because I tried to send the query to Vitess.
However, the 2nd query alter table `users` add primary key `users_id_primary`(`id`) should be alter table `users` add primary key (`id`) instead.
And the latter query should work on Vitess as well.
Then I have 2 questions.
- Does anyone have any good ideas to modify the query? I wonder some libraries would work for it.
- Why Laravel Query Builder insert
users_id_primarybefore(`id`)? As far as I looked up, the query to create unique key will be the same style.
Thank you for your any helps!
Level 1
Thanks for reply!
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->uuid('id');
$table->primary('id');
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->string('language_settings')->nullable();
$table->string('avatar')->nullable();
$table->string('provider', 20)->nullable();
$table->string('provider_id')->nullable();
$table->string('access_token')->nullable();
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
Level 1
I also tried
$table->uuid('id')->primary();
instead of
$table->uuid('id');
$table->primary('id');
But the query and result were same.
Level 73
It should be enough to use
$table->uuid('id');
Level 1
Yeah I understand what you mean but next query should be alter table `users` add unique `users_email_unique`(`email`) so I think I need to modify the queries themselves.
Please or to participate in this conversation.