You have an error in your SQL syntax [...]

[PikaDude]

I've made a database called 'pxls' in MariaDB and entered the login credentials in the pxls.conf. When I try to run the pxls-1.0-SNAPSHOT.jar and I get the following error:

Exception in thread "main" org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLSyntaxErrorException: (conn:69) 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 '6),mod_action BOOLEAN NOT NULL DEFAULT false)' at line 1
Query is: CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false) [statement:"CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false)", located:"CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false)", rewritten:"CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false)", arguments:{ positional:{}, named:{}, finder:[]}]
at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1338)
at org.skife.jdbi.v2.Update.execute(Update.java:56)
at org.skife.jdbi.v2.sqlobject.UpdateHandler$2.value(UpdateHandler.java:67)
at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:79)
at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:224)
at org.skife.jdbi.v2.sqlobject.SqlObject$3.intercept(SqlObject.java:133)
at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$3923935c.createPixelsTable()
at space.pxls.data.Database.(Database.java:34)
at space.pxls.App.main(App.java:57)
Caused by: java.sql.SQLSyntaxErrorException: (conn:69) 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 '6),mod_action BOOLEAN NOT NULL DEFAULT false)' at line 1
Query is: CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false)
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:139)
at org.mariadb.jdbc.internal.util.ExceptionMapper.getException(ExceptionMapper.java:101)
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwAndLogException(ExceptionMapper.java:77)
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:226)
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:233)
at org.mariadb.jdbc.MariaDbClientPreparedStatement.execute(MariaDbClientPreparedStatement.java:163)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1327)
... 8 more
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: 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 '6),mod_action BOOLEAN NOT NULL DEFAULT false)' at line 1
Query is: CREATE TABLE IF NOT EXISTS pixels (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,x INT UNSIGNED NOT NULL,y INT UNSIGNED NOT NULL,color TINYINT UNSIGNED NOT NULL,who INT UNSIGNED,time TIMESTAMP NOT NULL DEFAULT now(6),mod_action BOOLEAN NOT NULL DEFAULT false)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1144)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1076)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1031)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:203)
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:224)
... 12 more

I'm running Linux 3.13.0-91-generic (or Ubuntu 14.04, depends on what you mean), Java 1.8.0_121 and Apache Maven 3.0.5.
Any ideas?

[Doxylamin]

Please add the following information:
OS: (Win/Lin/Other)
Java version:
Maven version:

[PikaDude]

I'm running Linux 3.13.0-91-generic (or Ubuntu 14.04, depends on what you mean), Java 1.8.0_121 and Apache Maven 3.0.5.

[dylanrenwick]

As a side-note, the "Can't perform that action at this time" error is due to adblocker, it seems to interfere with github a lot.

[PikaDude]

Already figured that out, but thanks though.

[Sorunome]

This means you are using an old mariadb server. For installation on that you can replace all instaces of now(6) with now() in the DAO.java file

[PikaDude]

That didn't seem to help...

[Sorunome]

well, what does mysql --version give you?

[PikaDude]

mysql Ver 15.1 Distrib 5.5.54-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[Sorunome]

Yep, that one is way out-of-date, as if you are using ubuntu 14.04. So either modify the DBO.java file manually like i said above (and re-build with mvn) or upgrade your mariadb version

[PikaDude]

Thanks! It's working now.