Mysql Syntax Failing

[XThenuwara]

trafficstars

`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

Error Log

ROOT_ERROR:
SyntaxError: Expected ")" or fields but "`" found.
    at peg$buildStructuredError (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\parse\mysqlParser.js:1119:12)
    at Object.peg$parse [as parse] (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\parse\mysqlParser.js:10294:11)
    at Function.parseMySQLToJSON (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\parse\Parser.js:44:38)
    at Function.parse (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\parse\Parser.js:73:32)
    at Object._import [as import] (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\import\index.js:15:37)
    at C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\import.js:40:68
    at C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\utils.js:52:23
    at Array.forEach (<anonymous>)
    at generate (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\utils.js:48:14)
    at importHandler (C:\Users\YasasT\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\import.js:40:27)
{"start":{"offset":14148,"line":332,"column":3},"end":{"offset":14149,"line":332,"column":4}}

[ortonomy]

Lol, welcome to the party! Several more issues about this...

[ortonomy]

https://github.com/holistics/dbml/issues/272

[ortonomy]

https://github.com/holistics/dbml/issues/254

[ortonomy]

https://github.com/holistics/dbml/issues/217

[ortonomy]

https://github.com/holistics/dbml/issues/141

[trasherdk]

Weird. I don't see similar behavior.

The command is:

$ sql2dbml dbml/src/assets_available.sql --mysql -o dbml/dst/assets_available.dbml

input

CREATE TABLE `assets_available` (
  `asset` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `altname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `decimals` int(10) unsigned NOT NULL,
  `display_decimals` int(10) unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `assets_available_asset_unique` (`asset`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

output

Table "assets_available" {
  "asset" varchar(255) [not null]
  "class" varchar(255) [not null]
  "altname" varchar(255) [default: NULL]
  "decimals" int(10) [not null]
  "display_decimals" int(10) [not null]
  "active" tinyint(1) [not null, default: "0"]
  "created_at" timestamp [not null, default: `CURRENT_TIMESTAMP`]

  Indexes {
    asset [unique, name: "assets_available_asset_unique"]
  }
}

And reverse:

$ dbml2sql dbml/dst/assets_available.dbml --mysql -o dbml/src/test.sql

output

-- SQL dump generated using DBML (dbml-lang.org)
-- Database: MySQL
-- Generated at: 2022-08-24T07:19:43.035Z

CREATE TABLE `assets_available` (
  `asset` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `altname` varchar(255) DEFAULT NULL,
  `decimals` int(10) NOT NULL,
  `display_decimals` int(10) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT "0",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE UNIQUE INDEX `assets_available_asset_unique` ON `assets_available` (`asset`);

Could it be because current_timestamp() is not valid SQL syntax?

Check out Automatic Initialization and Updating for TIMESTAMP and DATETIME

[baolequoc]

Thank you for reporting, We have collected the issue and will try to improve it in the future. Have a nice day!

[baolequoc]

@trasherdk Can you tell me what version of MySQL you are using? And do you have any suggestions for us to improve this? Currently I am using MySQL 8.0.27 and it works fine with the above syntax.

[baolequoc]

@ortonomy Do you have any suggestions for ON UPDATE current_timestamp() or just need to ignore them?

[trasherdk]

@baolequoc I'm on version:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.39, for Linux (x86_64) using  EditLine wrapper

The following breaks dbml on updatedAt expected ) but found u. (Table generated by prisma)

CREATE TABLE sveltekit.player (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(191) NOT NULL,
  position VARCHAR(191) NOT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;

ALTER TABLE sveltekit.player 
  ADD UNIQUE INDEX player_name_key(name);

The ON UPDATE CURRENT_TIMESTAMP(3) was added by me, post creation.