0
DROP TABLE IF EXISTS `migration_versions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migration_versions` (
  `version` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8mb4_unicode_520_ci
/*!40101 SET character_set_client = @saved_cs_client */;

is giving me this error:

#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 'SET character_set_client = @saved_cs_client */' at line 5

This is the original (unedited) exported statement. The only thing I did was change collations to utf8mb4_unicode_520_ci.

DROP TABLE IF EXISTS `migration_versions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migration_versions` (
  `version` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

Any ideas anyone?

4
  • #1. Does non-edited script executes successfully? #2. Does this script (dump) is obtained from the database posessed on the MySQL server? What are precise versions of src and dst servers? #3. Try to import original file then alter the table and change column/table collations with additional ALTER TABLE statement.
    – Akina
    Commented Jun 8, 2022 at 4:45
  • @Akina I am able to import the original/unaltered w/out issue. Original server that exported: Server version: 10.3.23-MariaDB-cll-lve - MariaDB Server, and server being imported into: Server version: 10.8.3-MariaDB-1:10.8.3+maria~jammy-log - mariadb.org binary distribution Commented Jun 8, 2022 at 5:14
  • 1
    This looks like your destination server is hosted on .nix whereas you have edited the file using the editor on Windows which results in line terminators mixing (both LF and CR/LF are present).. or something similar. If you have some HEX viewer then look at the bytes immediately before the SET statement which is citated in the error message. Also ensure that the edited file is NOT saved as utf with BOM.
    – Akina
    Commented Jun 8, 2022 at 5:24
  • @Akina I have to admit, that's pretty astute that you noticed that, lol. You're correct, but it was all one line originally, and I just pasted the version above to make it easier to read here on SE. A single line that long can get a bit annoying to read. Commented Jun 9, 2022 at 4:10

1 Answer 1

1

The Create Table statement needs to be terminated with a semi-colon (after your new Collation sequence).

The 40101 comments will be included or not based on your DBMS version.
In this case, they're clearly being included, so your script is effectively this:

DROP TABLE IF EXISTS `migration_versions`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `migration_versions` (
  `version` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  PRIMARY KEY (`version`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8mb4_unicode_520_ci 
SET character_set_client = @saved_cs_client;

Without the statement terminator, it's trying to treat the Set statement as just another clause in the Create Table statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.