0

I'm trying to import an SQL db, exported from another place. I have the SQL create script. I am not sure what might be going wrong...

My mariadb version is

select version();
+----------------+
| version()      |
+----------------+
| 10.5.8-MariaDB |
+----------------+
1 row in set (0.000 sec)

Table create script:

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: db
-- Generation Time: Jan 04, 2021 at 05:59 PM
-- Server version: 8.0.22
-- PHP Version: 7.4.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mytestdb`
--
CREATE DATABASE IF NOT EXISTS `mytestdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `mytestdb`;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int NOT NULL,
  `category` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.category'))) VIRTUAL NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.popular'))) VIRTUAL NOT NULL,
  `imageUrl` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.imageUrl'))) VIRTUAL NOT NULL,
  `json` json NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Error:

ERROR 1064 (42000) at line 33: 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 'NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(...' at line 3
4
  • 1
    what you are trying is only allowed in MySQL 8.0.x and not in Mariadb
    – nbk
    Jan 4, 2021 at 19:23
  • What version of MySQL/MariaDB are you trying to load into?
    – Rick James
    Jan 4, 2021 at 23:29
  • @RickJames I have it in the question - 10.5.8-MariaDB Jan 5, 2021 at 8:17
  • For Mariadb, NOT NULL is not accepting for VIRTUAL columns. Please try after removing NOT NULL. percona.com/blog/2016/03/04/… Jan 5, 2021 at 11:14

1 Answer 1

1

I'm not an expert in Mariadb but I think you cannot do it. From the documentation on generated columns

Generated columns can be referenced in the INSERT, UPDATE, and DELETE statements. However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or DEFAULT. If a generated column is explicitly set to any other value, then the outcome depends on whether strict mode is enabled in sql_mode. If it is not enabled, then a warning will be raised and the default generated value will be used instead. If it is enabled, then an error will be raised instead.

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.