Running mysqldump to backup produces an error

General Discussions - English
1

I attempt to backup a single table with the following command, but I got an error:

mysqldump -h0 -P9306 -B manticore  --tables fictive_slack1

/*!999999\- enable the sandbox mode */ 
-- MariaDB dump 10.19  Distrib 10.11.8-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.8 d17bd2b6b@24112202

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Error: 'P01: syntax error, unexpected SELECT near 'SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='manticore' AND TABLE_NAME IN ('fictive_slack1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME'' when trying to dump tablespaces
mysqldump: Couldn't execute 'SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'fictive_slack1'': P01: syntax error, unexpected identifier near 'DATABASE() AND table_name = 'fictive_slack1'' (1064)

When I run simply mysqldump -h0 -P9306 -B manticore, then an another kind of error is shown:

/*!999999\- enable the sandbox mode */ 
-- MariaDB dump 10.19  Distrib 10.11.8-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.8 d17bd2b6b@24112202

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Error: 'P01: syntax error, unexpected SELECT near 'SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('manticore'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME'' when trying to dump tablespaces

--
-- Current Database: `manticore`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `manticore`;

USE `manticore`;
mysqldump: Got error: 1064: "P02: syntax error, unexpected identifier near 'LOCK TABLES `aaa` READ /*!32311 LOCAL */,`aaa_audit1` READ /*!32311 LOCAL */,`aaa_cat1` READ /*!32311 LOCAL */,`aaa_note1` READ /*!32311 LOCAL */,`aaa_tag1` READ /*!32311 LOCAL */,`bbb` READ /*!32311 LOCAL */,`bbb_audit1` READ /*!32311 LOCAL */,`bbb_cat1` READ /*!32311 LOCAL */,`bbb_note1` READ /*!32311 LOCAL */,`bbb_tag1` READ /*!32311 LOCAL */,`fictive` READ /*!32311 LOCAL */,`fictive_audit1` READ /*!32311 LOCAL */,`fictive_cat1` READ /*!32311 LOCAL */,`fi.." when using LOCK TABLES

I had a feeling the recent versions of mariadb’s mysqldump may have a thing that manticore doesn’t like, so I tested with mysqldump 5.7.44, and apparently I could backup fictive_slack1 table just fine.

2

With what mariadb’s mysqldump version did it fail? Did you have Buddy running (can be checked with show version - if it fails, it means it’s not running)?

3

It’s MariaDB dump 10.19

I didn’t have buddy running before, but now I added it to the searchd block:

buddy_path = manticore-executor -n /usr/share/manticore/modules/manticore-buddy/src/main.php

However, it’s the same error again. Do I need the buddy to make the backup?

4

It’s MariaDB dump 10.19

Thanks. I can’t find a docker image of 10.19 though - https://hub.docker.com/_/mariadb/tags?name=10.19

How do I install it? I can’t reproduce with mariadb 10.11 like this:

# sanitize network and containers
docker stop manticore
docker stop db-test
docker network rm test_network

# update manticore:dev
docker pull manticoresearch/manticore:dev

# create a network for mariadb <-> manticore
docker network create test_network

# run mariadb to use the client
docker run --rm -d --network=test_network --platform linux/amd64 --name db-test -e MYSQL_ROOT_PASSWORD=my-secret-pw mariadb:10.11 bash -c "tail -f /dev/null" > /dev/null
docker stop manticore; sleep 5; docker run -d --rm --network=test_network --name=manticore -e EXTRA=1 manticoresearch/manticore:dev

# create a dummy table in manticore
docker exec -it manticore mysql -e "show status like 'version'; drop table if exists t; create table t"; 

# dump 
docker exec db-test mariadb-dump -hmanticore -P9306 manticore t > dump.sql

# restore
docker exec -i db-test mariadb -hmanticore -P9306 manticore < dump.sql

now I added it to the searchd block:

You don’t have to enable it manually, you just need to not disable it, since Buddy is running by default if it’s installed (e.g. using apt install manticore-extra) and if there’s a binary/http listener (Manticore Search Manual: Server settings > Searchd)

Do I need the buddy to make the backup?

In some versions - yes.

5

It’s turned out mariadb-dump is 10.19 in the mariadb’s 10.11 docker image:

snikolaev@dev2:~$ docker exec db-test mariadb-dump --version
mariadb-dump  Ver 10.19 Distrib 10.11.10-MariaDB, for debian-linux-gnu (x86_64)

Then the question is if you can reproduce the issue using the dockers way I showed and let me know how I can reproduce it on my end? Or perhaps the issue is just solved in the dev version of Manticore - Manticore Search Manual