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, Sep 10 '19 at 18:54
Please try to set `'strict' => 'false'` in the connection properties (config/database.php) — dparoli, Sep 10 '19 at 18:59
@spencer7593 my tavis ci is failing due to the issue. is it possible to use mariadb on travis ci? — Shiv, Sep 10 '19 at 19:00
Reference: https://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, Sep 10 '19 at 19:08

computercarguy · Answer 1 · 2019-09-10T19:17:25.167

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 '19 at 19:17

answered Sep 10 '19 at 18:47

computercarguy

2,173
1
13
27

Unfortunately, this advice would work if the OP was creating the schema manually, but the Laravel migrations are being used instead. – aynber Sep 10 '19 at 18:51
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. – Tim Lewis Sep 10 '19 at 18:52
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) – spencer7593 Sep 10 '19 at 18:56

score 0 · Answer 2 · answered Sep 10 '19 at 19:01

0

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


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

answered Sep 10 '19 at 19:01

dılo sürücü

3,821
1
26
28

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. – computercarguy Sep 10 '19 at 19:20
Read the link https://stackoverflow.com/questions/289727/which-mysql-data-type-to-use-for-storing-boolean-values/289767 – dılo sürücü Sep 10 '19 at 20:51

Laravel Migration Returning Invalid SQL

2 Answers2