voyager databases and MariaDB server

Question

10.1.28-MariaDB

I'm trying to make a new table in the voyager admin panel but i keep getting the error:

generic.exception: An exception occurred while executing 'CREATE TABLE newReport (id INT UNSIGNED AUTO_INCREMENT NOT NULL, owner_id INT DEFAULT NULL, title VARCHAR(166) DEFAULT NULL, description text DEFAULT NULL, report json DEFAULT NULL, created_at timestamp null DEFAULT NULL, updated_at timestamp null DEFAULT NULL, deleted_at timestamp null DEFAULT NULL, INDEX newreport_owner_id_index (owner_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB': 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 DEFAULT NULL, created_at timestamp null DEFAULT NULL, updated_at timestamp ' at line 1

or some variant of a SQL syntax error depending on what I'm trying.

I'm using Xampp and haven't installed a MariaDB server do i need to use a MariaDB server? or is my problem something else?

screenshot of the table

if i remove the json data type the error becomes:

generic.exception: An exception occurred while executing 'CREATE TABLE newReport (id INT UNSIGNED AUTO_INCREMENT NOT NULL, owner_id INT DEFAULT NULL, title VARCHAR(166) NOT NULL, description text NOT NULL, report text NOT NULL, created_at timestamp DEFAULT 'CURRENT_DATE', updated_at timestamp null DEFAULT NULL, deleted_at timestamp null DEFAULT NULL, INDEX newreport_owner_id_index (owner_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB': SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at'

Possible duplicate. See if this helps. stackoverflow.com/questions/42425667/… — SS_DBA
If i remove the json coulomb i still get the same error (minuses the "json DEFAULT NULL" part) I don't think my problem is specifically json data type related — crffty
Can you post your Create Table statement? It's hard to see. — SS_DBA
as stated in the question I'm using the voyager admin panel (not writing statements), please view "screenshot of the table" — crffty

spencer7593 spencer7593 · Accepted Answer · 2017-11-07T21:09:54

The JSON datatype was introduced in MySQL 5.7.

This datatype is not available in earlier versions of MySQL (5.6 or earlier), and isn't implemented in MariaDB yet (at least up to version 10.2, but MariaDB has added some JSON functions e.g. JSON_VALID.)

EDIT

MariaDB adds support for JSON datatype in version 10.2.7

https://mariadb.com/kb/en/library/json-data-type/

To check the version of the MySQL/MariaDB server you are connected to:

 SHOW VARIABLES LIKE 'version'

As a test, you might replace the JSON datatype with another datatype you know is supported, eg. VARCHAR(20) or TEXT.

If json datatype isn't supported in your MySQL/MariaDB server, you can use another text type.

Looks to me like the error is flagging a problem in the SQL syntax at 'json. And that's where we'd expect to find a valid datatype. My suggestion as a test, replacing that with a known good datatype, is intended to determine if that is what the problem is.

From the error message, it appears that the server is MariaDB (not MySQL)

We see the statement being executed:

CREATE TABLE newReport 
( id          INT UNSIGNED AUTO_INCREMENT NOT NULL
, owner_id    INT DEFAULT NULL
, title       VARCHAR(166) DEFAULT NULL
, description text DEFAULT NULL
, report      json DEFAULT NULL
, created_at  timestamp null DEFAULT NULL
, updated_at  timestamp null DEFAULT NULL
, deleted_at  timestamp null DEFAULT NULL
, INDEX newreport_owner_id_index (owner_id)
, PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
ENGINE = InnoDB

And the error

: 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

and where the error is being flagged

'json DEFAULT NULL ...

EDIT

replacement of json with text changes the error to

1067 Invalid default value for 'created_at'

looks strange, because what was in the statement with syntax error at json was

  created_at  timestamp null DEFAULT NULL
                                     ^^^^

and that would have worked, but for some reason that is now changed to

  created_at timestamp null DEFAULT 'CURRENT_DATE' 
                                    ^^^^^^^^^^^^^^

That string value 'CURRENT_DATE' is not a valid value for a timestamp. Looks like we intended to reference the keyword CURRENT_TIMESTAMP, not a string literal

  created_at timestamp null DEFAULT CURRENT_TIMESTAMP
                                    ^^^^^^^^^^^^^^^^^

voyager databases and MariaDB server

1 Answers