Bug: project dump may generate invalid SQL

[fabianeichinger]

I'm running into a syntax error when trying to import a dump generated with shopware-cli project dump from a running shop.

The error message is

ERROR 1054 (42S22) at line 900419: Unknown column '0x' in 'field list'

I was able to track it down to a single INSERT row value:

--
-- Structure for table `plugin`
--

DROP TABLE IF EXISTS `plugin`;
CREATE TABLE `plugin` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `base_class` varchar(255) NOT NULL,
  `composer_name` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `managed_by_composer` tinyint(1) NOT NULL DEFAULT 0,
  `path` varchar(255) DEFAULT NULL,
  `autoload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`autoload`)),
  `author` varchar(255) DEFAULT NULL,
  `copyright` varchar(255) DEFAULT NULL,
  `license` varchar(255) DEFAULT NULL,
  `version` varchar(255) NOT NULL,
  `upgrade_version` varchar(255) DEFAULT NULL,
  `icon` mediumblob DEFAULT NULL,
  `installed_at` datetime(3) DEFAULT NULL,
  `upgraded_at` datetime(3) DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq.name` (`name`),
  UNIQUE KEY `uniq.baseClass` (`base_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Data for table `plugin` -- 28 rows
--

LOCK TABLES `plugin` WRITE;

INSERT INTO `plugin` (`id`, `name`, `base_class`, `composer_name`, `active`, `managed_by_composer`, `path`, `autoload`, `author`, `copyright`, `license`, `version`, `upgrade_version`, `icon`, `installed_at`, `upgraded_at`, `created_at`, `updated_at`) VALUES
…
( B3A79CC674AA4B0FA469990E17709954, '<name>', '<base class>', '<composer name>', '1', '0', '<path>', '<autoload>', NULL, NULL, '<license>', '<version>', NULL, 0x, '2022-09-05 14:26:11.174', NULL, '2022-09-05 14:26:07.872', '2025-02-04 11:07:58.664' ),
…
UNLOCK TABLES;

UNLOCK TABLES;

Note that the value for column icon is 0x. This is apparently invalid syntax.

A SQL browser exported the same row as

( X'B3A79CC674AA4B0FA469990E17709954', '<name>', '<base class>', '<composer name>', '1', '0', '<path>', '<autoload>', NULL, NULL, '<license>', '<version>', NULL, '', '2022-09-05 14:26:11.174', NULL, '2022-09-05 14:26:07.872', '2025-02-04 11:07:58.664' ),

Replacing that single line makes the import succeed.

[shyim]

can you provide an sql dump of only plugin table so i can reproduce this and also mysql server version?

[fabianeichinger]

@shyim I may share a minimal example that reproduces the error.

You can import this orig.sql into a clean database in whatever way you prefer, it will execute just fine.

DROP TABLE IF EXISTS `plugin`;
CREATE TABLE `plugin` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `base_class` varchar(255) NOT NULL,
  `composer_name` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `managed_by_composer` tinyint(1) NOT NULL DEFAULT 0,
  `path` varchar(255) DEFAULT NULL,
  `autoload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`autoload`)),
  `author` varchar(255) DEFAULT NULL,
  `copyright` varchar(255) DEFAULT NULL,
  `license` varchar(255) DEFAULT NULL,
  `version` varchar(255) NOT NULL,
  `upgrade_version` varchar(255) DEFAULT NULL,
  `icon` mediumblob DEFAULT NULL,
  `installed_at` datetime(3) DEFAULT NULL,
  `upgraded_at` datetime(3) DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq.name` (`name`),
  UNIQUE KEY `uniq.baseClass` (`base_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `plugin` (`id`, `name`, `base_class`, `composer_name`, `active`, `managed_by_composer`, `path`, `autoload`, `author`, `copyright`, `license`, `version`, `upgrade_version`, `icon`, `installed_at`, `upgraded_at`, `created_at`, `updated_at`) VALUES
(X'B3A79CC674AA4B0FA469990E17709954', 'FooBar', 'Foo\\Bar\\FooBar', 'foo/bar', 1, 0, 'custom/plugins/FooBar/', '{\"psr-4\":{\"Foo\\\\FooBar\\\\\":\"src\\/\"}}', NULL, NULL, 'MIT', '1.0.0', NULL, '', '2022-09-05 14:26:11.174', NULL, '2022-09-05 14:26:07.872', '2025-01-24 06:51:38.125');

Then run ./shopware-cli project dump --database … with the parameters you need in your local environment.

Generated dump.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

--
-- Structure for table `plugin`
--

DROP TABLE IF EXISTS `plugin`;
CREATE TABLE `plugin` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `base_class` varchar(255) NOT NULL,
  `composer_name` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `managed_by_composer` tinyint(1) NOT NULL DEFAULT 0,
  `path` varchar(255) DEFAULT NULL,
  `autoload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`autoload`)),
  `author` varchar(255) DEFAULT NULL,
  `copyright` varchar(255) DEFAULT NULL,
  `license` varchar(255) DEFAULT NULL,
  `version` varchar(255) NOT NULL,
  `upgrade_version` varchar(255) DEFAULT NULL,
  `icon` mediumblob DEFAULT NULL,
  `installed_at` datetime(3) DEFAULT NULL,
  `upgraded_at` datetime(3) DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq.name` (`name`),
  UNIQUE KEY `uniq.baseClass` (`base_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Data for table `plugin` -- 1 rows
--

LOCK TABLES `plugin` WRITE;

INSERT INTO `plugin` (`id`, `name`, `base_class`, `composer_name`, `active`, `managed_by_composer`, `path`, `autoload`, `author`, `copyright`, `license`, `version`, `upgrade_version`, `icon`, `installed_at`, `upgraded_at`, `created_at`, `updated_at`) VALUES
( 0xb3a79cc674aa4b0fa469990e17709954, 'FooBar', 'Foo\\Bar\\FooBar', 'foo/bar', '1', '0', 'custom/plugins/FooBar/', '{\"psr-4\":{\"Foo\\\\FooBar\\\\\":\"src\\/\"}}', NULL, NULL, 'GPL', '1.0.0', NULL, 0x, '2022-09-05 14:26:11.174', NULL, '2022-09-05 14:26:07.872', '2025-01-24 06:51:38.125' );
UNLOCK TABLES;

SET FOREIGN_KEY_CHECKS = 1;

Finally try importing the dump.sql from shopify-cli, and you'll get the error mentioned earlier.

MariaDB 10.11.8
shopware-cli 0.5.2