Framework: Use JSON data type in MariaDB got error

[jsleetw]

I use

$table->json('tags')->nullable();

in migrate,

The 'mysql' version is 10.1.14-MariaDB.

in MariaDB json colume called COLUMN_JSON(dyncol_blob)

https://mariadb.com/kb/en/mariadb/column_json/

[IlluminateDatabaseQueryException]
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 for the right syntax to use near 'json null) default character set utf8
collate utf8_unicode_ci' at line 1 (SQL: create table pages (id int unsigned not null auto_increment primary k
ey, title varchar(255) null, tags json null) default character set utf8 collate utf8_unicode_ci)

[PDOException]
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 for the right syntax to use near 'json null) default character set utf8
collate utf8_unicode_ci' at line 1

[andrey-helldar]

And MySQL 5.7.12 too.

IlluminateDatabaseQueryException thrown with message "SQLSTATE[HY000]: General error: 2036 (SQL: select * from api_logs)"

CREATE TABLE api_logs (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
query json DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Schema::create('api_logs', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id');
$table->json('query')->nullable();
$table->timestamps();
});

Laravel 5.2.32
OS: CentOS 7
PHP 7.0.6
MySQL 5.7.12
NGINX 1.9.15

On local PC OK:
OS: Windows 10
PHP 7.0.6
MySQL 5.7.12
NGINX 1.9.12

Query:

MYSITEApiLog::get();

Error:

Illuminate Database QueryException (HY000)
SQLSTATE[HY000]: General error: 2036 (SQL: select * from api_logs)

[andrey-helldar]

It looks like I'm wrong upgraded MariaDB 5.5 to MySQL 5.7...

mysql -v
MySQL 5.7.12

But...

$link = mysqli_connect($host, $user, $password, $database)
or die("ERROR " . mysqli_error($link));

Resulted:

`mysqli Object
(
[affected_rows] => 0
[client_info] => 5.5.47-MariaDB
[client_version] => 50547
...
[server_info] => 5.7.12
[server_version] => 50712
...

[schulzefelix]

No one else running in this problem? I tried it on a fresh Forge Server and local with MariaDB (10.1.14 & 10.1.13).
Laravel Framework version 5.2.32

Or is there an other clean way to shift with an existing project, migrations & database to MariaDB ?

[Codeklopper]

I am experiencing this problem as well. Saw that $table->json('tags')->nullable(); always evaluates to 'json' in IlluminateDatabaseSchemaGrammarsMySqlGrammar (line 485). But the json column data type is not supported in MariaDB 10.1.x and lower. It will be available in version 10.2 (which is already in Alpha).

I wrote all my tests with a SQLite database, where the SQLite grammar evaluates to 'text', Because all my tests passed i changed my 'json' columns to 'text'.

Another way would be to somehow make use of the dynamic columns in MariaDB. and select json data with COLUMN_JSON. Use 'blob' as datatype for your column in that case.

[andrey-helldar]

MariaDB 10.2 supported JSON. (Alpha version. Not recommended by Maria to production server. Only testing.)

MariaDB 10.1 do not support JSON (

[GrahamCampbell]

This is a similar issue to having old MySQL versions. We made this change in 5.2. Either upgrade to a new version, or, just don't use the json type in migrations. If you really want to use that in the migration, and want the old database version, you could stay on Laravel 5.1 LTS.

[andrey-helldar]

No no no no no))) Only new version. Only hardcore )))

[GrahamCampbell]

;)

[jhdxr]

@GrahamCampbell is it possible to return json/blob/text based on the mysql version?

[morrislaptop]

Trying this on 10.2 doesn't work either...

[arctic-ice-cool]

Using text isn't a workaround for the modern reasons to use json in the first place. Although this is a MariaDB problem to resolve, I am looking forward to a wider support of the emerging json datatype.

[rtheobald]

Looks like MariaDB JSON data type support has been pushed back. In MariaDB's Jira It is no longer set as fixed in 10.2. Best I can tell from looking at the dependant tasks, it is going to be at least 10.3

[morrislaptop]

@rtheobald link?

[rtheobald]

JSON support blocked by JSON data type which is blocked by Add a plugin to field types (column types) and which is in turn blocked by (among others) Wrong result for (int_expr IN (mixture of signed and unsigned expressions)) which is scheduled to be fixed in release 10.3

[shrutiakki]

Error
SQL query: Documentation

select col1, col, value
from test
unpivot
(
value
for col in (val1, val2, val3, val4, val5)
) unpivot
LIMIT 0, 25
MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(

value
for col in (val1, val2, val3, val4, val5)
) unpivot
LIMIT 0, 25' at line 4

what will be the error am not getting please help me

[raakesh]

@shrutiakki json type not supported in MariaDB. MySQL 5.7.8 supports json type (for more information link ). Install MySQL 5.7.8+

[monaye]

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

[j0131n]

As per mariadb version 10.1.32 and lower it seems like mariadb does not support json data type I am still unsure if it is available in version 10.2.7+.

but here's a simple workaround to get through this.

change json data type into text and then run your migration again.

[Dolphet]

As per mariadb version 10.1.32 and lower it seems like mariadb does not support json data type I am still unsure if it is available in version 10.2.7+.

but here's a simple workaround to get through this.

change json data type into text and then run your migration again.

thx it helps me

[kinwebdesign]

Sooooo... what version of Laravel works with Twill??? If none, how do I modify it to work?????????

[irvv17]

Sooooo... what version of Laravel works with Twill??? If none, how do I modify it to work?????????

Is not about Laravel Version
is about your MYSQL version bro