Schema Synchronisation Tool generates an invalid SQL

[sibwy]

Reproducible case:

1. Server versions: MySQL 8.0.34 (comparing between two databases on the same server)
2. SQLyog version: v13.2.0
3. Database charset and collation:

   source: utf8mb4 and utf8mb4_0900_ai_ci target: utf8mb4 and utf8mb4_unicode_ci

Create the below table on source table and sync this table to the target database using 'Schema Synchronisation Tool',

CREATE TABLE `api` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `genHasParseDate` TINYINT(1) GENERATED ALWAYS AS ((`parseDate` IS NOT
 NULL)) STORED,
 `parseDate` DATETIME NULL  , 
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The schema sync tool generates an invalid SQL statement and therefore returns the below error, "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'arseDatetinyint(1) GENERATED ALWAYS AS ((parseDate` is not null)) STORED , ' at line 4"

The user report can be found in SF: 01010243

[gravitiq-cm]

👍

[gravitiq-cm]

any rough idea when this might be investigated?

[KrunchMuffin]

at least yours has the proper quotes, double quotes won't work

Executed SQL Statement : CREATE TABLE "ac" (
  "id" bigint unsigned NOT NULL AUTO_INCREMENT,
  "code" int unsigned NOT NULL,
  "description" varchar(255) NOT NULL,
  "recurring" tinyint(1) NOT NULL,
  "active" tinyint(1) NOT NULL,
  "ac_classification" int unsigned NOT NULL,
  "ac_type" int unsigned NOT NULL,
  "ac_priority" int unsigned NOT NULL,
  "tte_1" int unsigned DEFAULT NULL,
  "tte_2" int unsigned DEFAULT NULL,
  "tte_3" int unsigned DEFAULT NULL,
  "cpte_1" int unsigned DEFAULT NULL,
  "cpte_2" int unsigned DEFAULT NULL,
  "cpte_3" int unsigned DEFAULT NULL,
  "et_1" varchar(255) DEFAULT NULL,
  "et_2" varchar(255) DEFAULT NULL,
  "et_3" varchar(255) DEFAULT NULL,
  "auto_cancel" tinyint(1) NOT NULL DEFAULT '0',
  "daily_threshold" smallint unsigned DEFAULT NULL,
  "cp_threshold" int unsigned DEFAULT NULL,
  "auto_open_service_delay_minutes" smallint unsigned DEFAULT NULL,
  "auto_open_ac" bigint unsigned DEFAULT NULL,
  "ttc" int unsigned NOT NULL,
  "crew_position" int unsigned NOT NULL,
  PRIMARY KEY ("id"),
  KEY "ac_active_index" ("active"),
  KEY "ac_recurring_index" ("recurring"),
  KEY "ac_auto_cancel_index" ("auto_cancel"),
  KEY "ac_daily_threshold_index" ("daily_threshold"),
  KEY "ac_auto_open_service_delay_minutes_index" ("auto_open_service_delay_minutes"),
  KEY "ac_code_index" ("code"),
  KEY "ac_ac_classification_id_fk" ("ac_classification"),
  KEY "ac_ac_id_fk" ("auto_open_ac"),
  KEY "ac_ac_priority_id_fk" ("ac_priority"),
  KEY "ac_ac_type_id_fk" ("ac_type"),
  KEY "ac_crew_position_id_fk" ("cpte_1"),
  KEY "ac_crew_position_id_fk_0" ("crew_position"),
  KEY "ac_crew_position_id_fk_2" ("cpte_2"),
  KEY "ac_crew_position_id_fk_3" ("cpte_3"),
  KEY "ac_crew_position_id_fk_4" ("cp_threshold"),
  KEY "ac_ttc_id_fk" ("ttc"),
  KEY "ac_tte_id_fk" ("tte_1"),
  KEY "ac_tte_id_fk_2" ("tte_2"),
  KEY "ac_tte_id_fk_3" ("tte_3"),
  CONSTRAINT "ac_ac_classification_id_fk" FOREIGN KEY ("ac_classification") REFERENCES "ac_classification" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_ac_id_fk" FOREIGN KEY ("auto_open_ac") REFERENCES "ac" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_ac_priority_id_fk" FOREIGN KEY ("ac_priority") REFERENCES "ac_priority" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_ac_type_id_fk" FOREIGN KEY ("ac_type") REFERENCES "ac_type" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_crew_position_id_fk" FOREIGN KEY ("cpte_1") REFERENCES "crew_position" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_crew_position_id_fk_0" FOREIGN KEY ("crew_position") REFERENCES "crew_position" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_crew_position_id_fk_2" FOREIGN KEY ("cpte_2") REFERENCES "crew_position" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_crew_position_id_fk_3" FOREIGN KEY ("cpte_3") REFERENCES "crew_position" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_crew_position_id_fk_4" FOREIGN KEY ("cp_threshold") REFERENCES "crew_position" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_ttc_id_fk" FOREIGN KEY ("ttc") REFERENCES "ttc" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_tte_id_fk" FOREIGN KEY ("tte_1") REFERENCES "tte" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_tte_id_fk_2" FOREIGN KEY ("tte_2") REFERENCES "tte" ("id") ON UPDATE CASCADE,
  CONSTRAINT "ac_tte_id_fk_3" FOREIGN KEY ("tte_3") REFERENCES "tte" ("id") ON UPDATE CASCADE
) 
Error Number : 1064 
Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"ac" (
  "id" bigint unsigned NOT NULL AUTO_INCREMENT,
  "code" int unsigned NOT' at line 1