an error in your SQL syntax; check the manual that corresponds to your MySQL server version

All we need is an easy explanation of the problem, so here it is.

I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6
while creating a table it is showing me this error

ERROR 1064 (42000) at line 25 in file: 'name.sql': 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 'sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
 ' at line 17
Query OK, 0 rows affected (0.001 sec)

error is in the line ‘sha2(uuid(),256)’

CREATE TABLE `User` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `PIN` bigint(20) DEFAULT NULL,
  `LatestInviteStatus` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LatestInviteDate` date DEFAULT NULL,
  `LatestInviteUUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I cannot figure out what it is on workbench it says
sha2 is not valid at this position

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your CREATE TABLE will fail because there is no PIN column mentioned in KEY `idxPIN` (`PIN`) line.

MySQL 5.6 does not allow an expression as default value. Use trigger:

CREATE TABLE `Customer` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
-- -------------
`PIN` INT,
-- -------------
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER set_uuid
BEFORE INSERT 
ON Customer
FOR EACH ROW
SET NEW.UUID = COALESCE(NEW.UUID, sha2(uuid(),256));

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply