2

Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior?

Table created:

CREATE TABLE `ACL` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(512) DEFAULT NULL,
  `property` varchar(512) DEFAULT NULL,
  `accessType` varchar(512) DEFAULT NULL,
  `permission` varchar(512) DEFAULT NULL,
  `principalType` varchar(512) DEFAULT NULL,
  `principalId` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

SHOW CREATE TABLE ACL:

CREATE TABLE `ACL` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `property` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `accessType` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `principalType` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `principalId` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

Is there any way to get exactly what was used to create the table in the first place?

  • 1
    Write a script file that pipes the output of mysqldump to sed and remove the string DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci - missing a ; (semi-colon) at the end after InnoDB - no? That way, you still have the same number of keystrokes for the whole operation! Your script would be mysqldump ... my_database_stuff... > my_file.sql followed by the line sed -i 's/ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci//g' my_file.sql – Vérace Sep 25 '17 at 20:28
  • 1
    Why do you need it? I ask because there may be a workaround that solves the real problem, instead of the attempted solution. – Rick James Sep 25 '17 at 20:43
  • @RickJames Good point - you're right, it's not a real problem, just a desire to keep things small and not unnecessarily verbose. The issue stems from tables with table level collate/charset that don't match some columns and wanting to convert all tables to utf8mb4/_unicode_ci. We had a mix of latin1 and general_ci collations and I had the wrong assumption as to what a "clean" uniform file should look like. Thanks for pointing out this was a style bias on my part. I should let the tool do this. – Brian Sep 26 '17 at 20:33
  • 1
    @Brian - I appreciate your desire for a clean look. I don't have any better solution than Vérace's Comment. If you need to check a bunch of tables, see information_schema.COLUMNS. – Rick James Sep 26 '17 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.