Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) #326

choval · 2020-04-09T21:44:32Z

Describe the environment
macos catalina 10.15.3

Manticore Search version
Manticore 3.4.0 0686d9f@200326 release

OS version
Darwin xxxxxx 19.3.0 Darwin Kernel Version 19.3.0: Thu Jan 9 20:58:23 PST 2020; root:xnu-6153.81.5~1/RELEASE_X86_64 x86_64

Build version:
manticore-3.4.0-200326-0686d9f0-release-osx10.14.4-x86_64-bin.tar.gz

Describe the problem:
I'm trying to run mysqldump on an RTINDEX, but just can't run it at all since sphinxql cannot ignore the first mysql specific comment that mysqldump throws at the connection.

mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE='' */': sphinxql: syntax error, unexpected $end near '(null)' (1064)

Steps to reproduce:

#!/bin/sh         
     
# These are ignored correctly     
mysql -h 127.0.0.1 -P 9306 -e "/* IGNORED CORRECTLY */"     
mysql -h 127.0.0.1 -P 9306 -e "SELECT /*! IGNORED TOO */ 1"     
mysql -h 127.0.0.1 -P 9306 -e ""     
     
# This triggers an error, instead of ignoring.     
mysql -h 127.0.0.1 -P 9306 -e "/*! TRIGGER ERROR */"

Messsages from log files:

Nothing in the searchd.log and query.log, the following is the error thrown by the mysql41 protocol.

ERROR 1064 (42000) at line 1: sphinxql: syntax error, unexpected $end near '(null)'

The text was updated successfully, but these errors were encountered:

tomatolog · 2020-04-09T21:55:35Z

could you provide you index config and mysqldump command that you use?

choval · 2020-04-09T22:44:14Z

Config

searchd {
binlog_path = ./binlog
data_dir = ./data
pid_file = ./searchd.pid
log = ./searchd.log
query_log = ./query.log
listen = 9306:mysql41
}

Index

CREATE TABLE names (name text);

mysqldump

mysqldump -h 127.0.0.1 -P 9306 manticore names

Keep in mind this error is not related to an index, see the steps I posted, it's using a plain query call. It just doesn't get properly interpreated when it has a line with only a mysql specific comment.
Mysqldump errors out because it tries to send a few mysql specific comments before starting to dump.

tomatolog · 2020-04-10T11:21:02Z

yes I understand. Just wanted to check whole commands flow and not ping-pong each command fix

githubmanticore · 2020-04-15T04:59:13Z

➤ Sergey Nikolaev commented:

FFR: mysqldump does many more things that Manticore might not understand. Even if I disable all which can be disabled I still see the following queries from mysqldump:

root@dev ~ # echo "" > /tmp/mysql.log; mysqldump -f --no-set-names --no-tablespaces --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset -utest test t|wc -l; cat /tmp/mysql.log |grep Query  
23  
2020-04-15T04:56:56.933761Z	   15 Query	/*!40100 SET @@SQL_MODE='' */  
2020-04-15T04:56:56.933824Z	   15 Query	/*!40103 SET TIME_ZONE='+00:00' */  
2020-04-15T04:56:56.933900Z	   15 Query	SHOW VARIABLES LIKE 'gtid\_mode'  
2020-04-15T04:56:56.934833Z	   15 Query	SHOW VARIABLES LIKE 'ndbinfo\_version'  
2020-04-15T04:56:56.935532Z	   15 Query	SHOW TABLES LIKE 't'  
2020-04-15T04:56:56.935693Z	   15 Query	LOCK TABLES `t` READ /*!32311 LOCAL */  
2020-04-15T04:56:56.935756Z	   15 Query	show table status like 't'  
2020-04-15T04:56:56.935937Z	   15 Query	SET SQL_QUOTE_SHOW_CREATE=1  
2020-04-15T04:56:56.935959Z	   15 Query	SET SESSION character_set_results = 'binary'  
2020-04-15T04:56:56.935978Z	   15 Query	show create table `t`  
2020-04-15T04:56:56.936018Z	   15 Query	SET SESSION character_set_results = 'utf8'  
2020-04-15T04:56:56.936047Z	   15 Query	show fields from `t`  
2020-04-15T04:56:56.936219Z	   15 Query	show fields from `t`  
2020-04-15T04:56:56.936416Z	   15 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`  
2020-04-15T04:56:56.936545Z	   15 Query	SET SESSION character_set_results = 'binary'  
2020-04-15T04:56:56.936564Z	   15 Query	use `test`  
2020-04-15T04:56:56.936599Z	   15 Query	select @@collation_database  
2020-04-15T04:56:56.936626Z	   15 Query	SHOW TRIGGERS LIKE 't'  
2020-04-15T04:56:56.936801Z	   15 Query	SET SESSION character_set_results = 'utf8'  
2020-04-15T04:56:56.936820Z	   15 Query	UNLOCK TABLES  
``

choval · 2020-04-16T18:22:46Z

My objective was to make backups of the rtindexes, I'll be copying/snapshot the datadir instead.

Feel free to close the issue, the mysql specific comment only query would make it clear mysqldump wont' work on manticore.

Thanks for looking into it :-)

manticoresearch · 2020-04-24T05:36:31Z

Even though mysqldump might be not the best solution for backups in general it might work in some cases:

not too big indexes. Since mysqldump runs just a single query it doesn't work well for bigger tables. Even in mysql
when all is in docstore. Obviously mysqlump can't decode data from inverted index

we've decided to give it a try by skipping the queries it issues that do not make sense for Manticore. So I'm reopening the issue.

barryhunter · 2020-04-24T11:03:00Z

I know this is taking about getting the actual mysqldump working, but a while ago I made this:
https://github.com/barryhunter/fakedump/

It's a PHP script that kinda mimics mysqldump, intended so can run arbitrary queries to dump a fake table. (think of like dumping a view -with the data to recreate as a real table)

It works for dumping a RT index :)

fakedump -h192.168.3.22:9306 fake geopointrt --schema=0 --lock=0

the 'fake' is just because the script expects a database name, not applicable to searchd. It uses a temporary table to create the schema, so that doesnt work, but easily disabled.

Anyway, it might not exactly fit needs, but though post it in case. Its a relative short PHP script, so quite tweakable.

(eg if have more than 1000 (ie maxmatches) documents, will need extending to dump bigger. maybe even to run queries in a loop)

sanikolaev · 2022-11-17T11:52:33Z

FYI: there's now tool manticore-backup which can be used for backups. More info here #821 (comment)

Integration with mysqldump is another story and still in plans. The new tools makes a physical backup while the mysqldump approach would give a logical backup.

githubmanticore · 2023-01-30T11:28:35Z

➤ Don Hardman commented:

It seems like mysqldump should be supported now, but it is worth checking against different types and different versions; maybe still missing something: manticoresoftware/executor#24

githubmanticore · 2023-03-24T10:29:06Z

➤ Sergey Nikolaev commented:

Testing on `perf`

Version

root@perf ~ # searchd -v 
Manticore 6.0.5 a5de0c119@230323 dev (columnar 2.0.5 8171c1a@230320) (secondary 2.0.5 8171c1a@230320)

Issues

Errors on any mysqldump

root@perf ~ # mysqldump -u mysqldump -P9306 -h0 test -t t > test.sql 
Error: Couldn't read status information for table t () 
mysqldump: Couldn't execute 'use `test`': sphinxql: syntax error, unexpected USE near 'use `test`' (1064) 
root@perf ~ #

The dump looks ok, but doesn't include CREATE TABLE:

root@perf ~ # cat test.sql 
-- MySQL dump 10.13  Distrib 5.7.41, for Linux (x86_64) 
-- 
-- Host: 0    Database: test 
-- ------------------------------------------------------ 
-- Server version	6.0.5 a5de0c119@230323 dev (columnar 2.0.5 8171c1a@230320) (secondary 2.0.5 8171c1a@230320) 
 
/*!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 utf8 */; 
/*!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 */; 
 
-- 
-- Dumping data for table `t` 
-- 
 
LOCK TABLES `t` WRITE; 
/*!40000 ALTER TABLE `t` DISABLE KEYS */; 
INSERT INTO `t` VALUES (794696817211080705,'abc'); 
/*!40000 ALTER TABLE `t` ENABLE KEYS */; 
UNLOCK TABLES; 
root@perf ~ #

Therefore -d results in no data and no schema:

root@perf ~ # mysqldump -d -u mysqldump -P9306 -h0 test -t t 
-- MySQL dump 10.13  Distrib 5.7.41, for Linux (x86_64) 
-- 
-- Host: 0    Database: test 
-- ------------------------------------------------------ 
-- Server version	6.0.5 a5de0c119@230323 dev (columnar 2.0.5 8171c1a@230320) (secondary 2.0.5 8171c1a@230320) 
 
/*!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 utf8 */; 
/*!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 */; 
Error: Couldn't read status information for table t () 
mysqldump: Couldn't execute 'use `test`': sphinxql: syntax error, unexpected USE near 'use `test`' (1064) 
root@perf ~ #

_abc can't be backed up

root@perf ~ # mysqldump -u mysqldump -P9306 -h0 test -t _abc > /dev/null 
Error: Couldn't read status information for table _abc () 
mysqldump: Couldn't execute 'show fields from `_abc`': You have an error in your query. Please, double-check it. (1064)

can't back up mva

root@perf ~ # mysql -P9306 -h0 -e "drop table if exists t; CREATE TABLE t (id bigint,name text,data multi64,data2 multi64); insert into t values(0,'abc',(1,2),(3,4));" 
 
root@perf ~ # mysqldump -u mysqldump -P9306 -h0 test -t t > t.sql 
Error: Couldn't read status information for table t () 
mysqldump: Couldn't execute 'use `test`': sphinxql: syntax error, unexpected USE near 'use `test`' (1064) 
 
root@perf ~ # cat t.sql|grep INSERT 
INSERT INTO `t` VALUES (794696817211080707,'1,2','3,4','abc'); 
 
# should be (1,2), not '1,2'. We probably need to handle it with Buddy. 
 
root@perf ~ # mysql -P9306 -h0 < t.sql 
ERROR 1064 (42000) at line 24: row 1, column 3: non-MVA value specified for a MVA column

Idea

Currently a query from mysqldump looks like this:

mysql> show queries; 
+------+-----------------------------------------------+----------+-----------------+ 
| id   | query                                         | protocol | host            | 
+------+-----------------------------------------------+----------+-----------------+ 
|  287 | show queries                                  | mysql    | 127.0.0.1:44770 | 
|  288 | select                                        | http     | 127.0.0.1:54694 | 
|  282 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `test` | mysql    | 127.0.0.1:44760 | 
+------+-----------------------------------------------+----------+-----------------+ 
3 rows in set (0.01 sec)

What if we make the searchd fail when it detect such a query (e.g. by the presence of /*!40001 SQL_NO_CACHE */) and it then gets routed to Buddy, so Buddy can replace it with more sane queries like select * from test where id > ... and id <= ...? This way we can overcome the 100M docs limit and perhaps(!) save a lot of RAM.

githubmanticore · 2023-03-29T06:16:03Z

➤ Sergey Nikolaev commented:

FFR:

This is wrong:

root@perf ~ # mysqldump -u mysqldump -P9306 -h0 test -t t > test.sql

One has to do either mysqldump ... manticore or mysqldump --all-databases ...

donhardman · 2023-05-23T06:21:16Z

Great news!

The latest development version of Manticore Search now supports mysqldump. We highly recommend using the MariaDB version for now as we are still actively working on resolving some issues with the MySQL original version.

sanikolaev · 2023-05-30T13:44:48Z

Mysql's mysqdump looks ok too in the latest dev version. The dev documentation can be found here https://manual.manticoresearch.com/dev/Securing_and_compacting_a_table/Backup_and_restore#Restore-by-using-manticore-backup-tool

Closing the issue as done.

sanikolaev closed this as completed Apr 17, 2020

manticoresearch reopened this Apr 24, 2020

sanikolaev moved this to Todo in Manticore public roadmap (large tasks only planned for the nearest future, not bugfixes / smaller issues) Jan 25, 2023

sanikolaev added this to Manticore public roadmap (large tasks only planned for the nearest future, not bugfixes / smaller issues) Jan 25, 2023

sanikolaev moved this from Todo to In Progress in Manticore public roadmap (large tasks only planned for the nearest future, not bugfixes / smaller issues) Jan 25, 2023

manticoresoftware deleted a comment from githubmanticore Feb 23, 2023

sanikolaev changed the title ~~Line with only mysql specific comment returns an error instead of ignoring~~ Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) Apr 3, 2023

githubmanticore added the waiting Waiting for the original poster (in most cases) or something else label Apr 11, 2023

sanikolaev closed this as completed May 30, 2023

sanikolaev moved this from In Progress to Complete, pending release in Manticore public roadmap (large tasks only planned for the nearest future, not bugfixes / smaller issues) May 30, 2023

sanikolaev moved this from Complete, pending release to Released in Manticore public roadmap (large tasks only planned for the nearest future, not bugfixes / smaller issues) Sep 27, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) #326

Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) #326

choval commented Apr 9, 2020 •

edited by githubmanticore

Loading

tomatolog commented Apr 9, 2020

choval commented Apr 9, 2020

tomatolog commented Apr 10, 2020

githubmanticore commented Apr 15, 2020

choval commented Apr 16, 2020

manticoresearch commented Apr 24, 2020

barryhunter commented Apr 24, 2020

sanikolaev commented Nov 17, 2022

githubmanticore commented Jan 30, 2023

githubmanticore commented Mar 24, 2023

githubmanticore commented Mar 29, 2023

donhardman commented May 23, 2023

sanikolaev commented May 30, 2023

Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) #326

Line with only mysql specific comment returns an error instead of ignoring (mysqldump integration) #326

Comments

choval commented Apr 9, 2020 • edited by githubmanticore Loading

tomatolog commented Apr 9, 2020

choval commented Apr 9, 2020

tomatolog commented Apr 10, 2020

githubmanticore commented Apr 15, 2020

choval commented Apr 16, 2020

manticoresearch commented Apr 24, 2020

barryhunter commented Apr 24, 2020

sanikolaev commented Nov 17, 2022

githubmanticore commented Jan 30, 2023

githubmanticore commented Mar 24, 2023

Testing on perf

Version

Issues

Errors on any mysqldump

_abc can't be backed up

can't back up mva

Idea

githubmanticore commented Mar 29, 2023

donhardman commented May 23, 2023

sanikolaev commented May 30, 2023

choval commented Apr 9, 2020 •

edited by githubmanticore

Loading

Testing on `perf`