Level 29
AFAIK, mariaDB doesn't have a datatype json ? (Some one correct me if wrong). To create a dynamic column you can try using $table->binary()
Edit: it available starting with 10.0.16
Jan 7, 2016
16
Level 2
$table->json() generates an error when running migrations.
Hello, I've encountered an annoying error here while trying to make a json field(mariaDB).
Here's the code
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('username')->unique();
$table->string('email')->unique();
$table->string('password', 60);
$table->string('verification_token', 30)->nullable();
// $table->json('settings'); This causes an error, commenting it out solves it
$table->rememberToken();
$table->timestamps();
});
}
[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version fo
r the right syntax to use near 'json not null, `remember_token` varchar(100) null, `created_at` timestamp not nu' at line 1 (SQL: create table `users` (`id` int
unsigned not null auto_increment primary key, `username` varchar(255) not null, `email` varchar(255) not null, `password` varchar(60) not null, `verification_tok
en` varchar(30) null, `settings` json not null, `remember_token` varchar(100) null, `created_at` timestamp not null, `updated_at` timestamp not null) default cha
racter set utf8 collate utf8_unicode_ci)
I'm using Laravel 5.2, are there any extra steps I need to take?
Level 2
I'm running version 10.0.23
Level 1
Yeah I have the same issue using MariaDB 10.1.10, no fix AFAIK
Level 11
Same issue on MySQL (5.6.27-0ubuntu1)
2 likes
Level 41
Same issue on MySQL (5.6.27-0ubuntu1)
You need MySQL 5.7.8+ to use the json column type.
4 likes
Level 47
I believe the Json type in migrations was translated to text. Since 5.2 it's actually Json type now. Either way if your db doesn't support Json just use text instead of varchar or Json. It will still store Json.
2 likes
Level 2
Aye, text will do. Thank's for the answers @all.
Level 1
I had the same problem and was able to solve it by adding a little logic to the create statement. This works for me with a standard MySQL Server in prod and dev, and sqlite for testing.
if ((DB::connection()->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') && version_compare(DB::connection()->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION), '5.7.8', 'ge')) {
$table->json('settings');
} else {
$table->text('settings');
}
5 likes
Level 34
Try this one:
$table->json('settings')->nullable()
1 like
Level 13
text it's not json type on mysql or mariadb. For me same problem, and use version 10.1.17 On this version MariaDB support json type columns. Also try, but it fails
$table->json('media')->nullable();
Level 30
I am having the same Issue with 10.1.16-MariaDB Homebrew. Any Solutions to get a json column type?
Level 5
Level 2
FYI I am using Server version: 10.2.5-MariaDB Homebrew and am STILL getting this error. Tried with and without ->nullable() too
I was pretty sure this is finally supposed to be supported in 10.2.x but I guess not! So annoying...
Level 8
MariaDB will not support JSON column types until 10.3
1 like
Level 1
MariaDB JSON Datatype is available from Version 10.2.7+ . https://mariadb.com/kb/en/library/json-data-type/
Also, this is a good article regarding MariaDB perspective on the JSON. https://mariadb.com/resources/blog/json-mariadb-102
1 like
Level 1
Expanding on @ryan.dial 's solution :
$driver = DB::connection()
->getPdo()
->getAttribute(PDO::ATTR_DRIVER_NAME);
// MySQL Earliest Supported Version
// https://dev.mysql.com/doc/refman/5.7/en/json.html
$requiredVersion = '5.7.8';
$myVersion = DB::connection()
->getPdo()
->getAttribute(PDO::ATTR_SERVER_VERSION);
// MariaDB Earliest Supported Version
// https://mariadb.com/kb/en/library/json-data-type/
if (strpos($myVersion, 'MariaDB') !== false) {
$requiredVersion = '10.2.7-MariaDB';
$myVersion = DB::select('SELECT VERSION() as version')[0]->version;
}
// Because PDO::ATTR_SERVER_VERSION will return "5.5.5-XX.X.X-MariaDB" instead of just "XX.X.X-MariaDB" , also determines if you just have pure mysql or mariadb
// namespace : Symfony\Component\Console\Output\ConsoleOutput;
$output = new ConsoleOutput();
$output->writeln("Driver : " . $driver);
$output->writeln("Current Version : " . $myVersion);
$output->writeln("Required Version : " . $requiredVersion);
if ( ($driver == 'mysql') && version_compare($myVersion, $requiredVersion, 'ge') ) {
$output->writeln($yourfieldname . "will be created as json");
$table->json($yourfieldname);
} else {
$output->writeln($yourfieldname . "will be created as text");
$table->text($yourfieldname);
}
1 like
Please or to participate in this conversation.