Laravel Migration Returning Invalid SQL

Question

I am running Laravel 6.0.2 and my migration up method is as follows:

Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email')->unique();
            $table->string('password');
            $table->boolean('admin')->default(false);
            $table->boolean('manager')->default(false);
            $table->rememberToken();
            $table->timestamps();
});

create table `users` (
  `id` int unsigned not null auto_increment primary key,
  `first_name` varchar(255) not null,
  `last_name` varchar(255) not null,
  `email` varchar(255) not null,
  `password` varchar(255) not null,
  `admin` tinyint(1) not null default ('0'),
  `manager` tinyint(1) not null default ('0'),
  `remember_token` varchar(100) null,
  `created_at` timestamp null,
  `updated_at` timestamp null
) default character set utf8mb4 collate 'utf8mb4_unicode_ci'

I have run that SQL on an online syntax checker and that is giving me and error on the admin tinyint(1) not null default ('0'), line.

I am not sure whether this is a Laravel 6.0.2 bug as it seemed to be working before that update.

Has anyone run into this issue and know of the fix?

The SQL syntax looks valid for MariaDB 10.3. Which "online syntax checker" is reporting the error? Does that "online syntax checker" enforce the same rules as MariaDB? Does MariaDB report a syntax error? (Seems like what needs to be fixed is the "online syntax checker", not Laravel or MariaDB) — spencer7593
– spencer7593, Commented Sep 10, 2019 at 18:54
Please try to set 'strict' => 'false' in the connection properties (config/database.php) — dparoli
– dparoli, Commented Sep 10, 2019 at 18:59
Reference: docs.travis-ci.com/user/database-setup/#mariadb i'm not familiar with travis ci. The generated SQL syntax is valid for MariaDB 10.3.x. It may be also valid in MariaDB 10.2.x — spencer7593
– spencer7593, Commented Sep 10, 2019 at 19:08

computercarguy · Accepted Answer · 2019-09-10 19:17:25Z

1

Well, this is pretty easy to fix and pretty easy to overlook. My guess is you've been looking at it for too long.

Your DEFAULTs don't need parenthesis or the quotes. The quotes are only there for strings, not ints, and the parens are only needed if it's a subquery (which isn't likely would actually work, but I haven't tried it).

http://www.w3webtutorial.com/mysql/mysql-default-constraint.php

edited Sep 10, 2019 at 19:17

answered Sep 10, 2019 at 18:47

computercarguy

2,5002 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aynber Over a year ago

Unfortunately, this advice would work if the OP was creating the schema manually, but the Laravel migrations are being used instead.

Tim Lewis Over a year ago

Keep in mind the SQL seen in the question is generated from the Schema::create() function; it's not as simple as "omitting the quotes" per-say.

spencer7593 Over a year ago

The SQL syntax appears to be valid at least for MariaDB 10.3, so the SQL doesn't necessarily need to be fixed. (also, don't link to w3fools)

dılo sürücü · Accepted Answer · 2019-09-10 19:01:05Z

0

In mysql boolean type is tinyint 0 false 1 true ...


$table->boolean('admin')->default(0);

answered Sep 10, 2019 at 19:01

dılo sürücü

4,2692 gold badges31 silver badges32 bronze badges

2 Comments

computercarguy Over a year ago

Do you have any documentation on why it should be 0 instead of false? I understand that it's evidently translating false to a string of 0 incorrectly, but any documented information as to why? Adding that could make this go from a good answer to The Answer.

dılo sürücü Over a year ago

Read the link stackoverflow.com/questions/289727/…

Collectives™ on Stack Overflow

Laravel Migration Returning Invalid SQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related