MDEV-17429 mysqldump uses 10.3 options with pre-10.3 servers and breaks

To reproduce the error:

10.2

1) Create a file to use in mysql client (test_MDEV-17429.sql)

use test;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
CREATE TABLE t1(t int);

CREATE TABLE t2(t datetime);

#DELIMITER //
CREATE TRIGGER test_trigger
  BEFORE INSERT on t1 FOR EACH ROW INSERT INTO t2 VALUES (now());
#DELIMITER //
#DELIMITER ;
#show tables;
#SHOW TRIGGERS;
#SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT * FROM t1, t2;

2) Execute the file:

> 2.1 From cmd 

```bash
$ ./client/mysql < test_mdev1729.sql
```

Note: It is not working when DELIMITER is used since it is expecting ; anel@ubuntu:~/workspace/mariadb/mariadb-server-10.2$ ./client/mysql < test_mdev1729.sql ERROR 1064 (42000) at line 9: 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 'DELIMITER' at line 1

2.2 From source

$ source test_mdev1729.sql

3) Execute mysqldump

./client/mysqldump --all-databases -u root > dump_10.2.sql

10.3

1) Execute mysqldump

./client/mysqldump -u root test --quick --routines --triggers --no-create-info --skip-lock-tables --no-data --compress < dump_10.2.sql

Without the patch error is produced:


l@ubuntu:~/workspace/mariadb/mariadb-server-10.3$ ./client/mysqldump -u root test --quick --routines --triggers --no-create-info --skip-lock-tables --no-data --compress < dump_10.2.sql
-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version   10.2.25-MariaDB-debug

/!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_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 routines for database 'test'

mysqldump: Couldn't execute 'SHOW PACKAGE STATUS WHERE Db = 'test'': 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 'PACKAGE STATUS WHERE Db = 'test'' at line 1 (1064)


> With patch applied there is no error

anel@ubuntu:~/workspace/mariadb/mariadb-server-10.3$ ./client/mysqldump -u root test --quick --routines --triggers --no-create-info --skip-lock-tables --no-data --compress < dump_10.2.sql -- MySQL dump 10.17 Distrib 10.3.17-MariaDB, for Linux (x86_64)

-- Host: localhost Database: test

-- Server version 10.2.25-MariaDB-debug

-- -- Dumping routines for database 'test'

/!40103 SET TIME_ZONE=@OLD_TIME_ZONE /;

/!40101 SET SQL_MODE=@OLD_SQL_MODE /; /!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /; /!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /; /!40111 SET SQL_NOTES=@OLD_SQL_NOTES /;

-- Dump completed on 2019-06-20 2:34:34


### Links
[[MDEV-17429](https://jira.mariadb.org/browse/MDEV-17429)]
[[patch](https://github.com/mariadb/server/commit/83978c0b700453183c32d314a70bdc323dedfd62)]

One needed to have a valid test case. Key notes are:

we can create a .opt file with switches in in it like -V-10.2.1, indicating to cheat mtr that we are running as 10.2 server. In this case the .opt file should be in the same directory as .test file and we don't need to include it. It doesn't work with --source include/something.opt
There is another way something like this :
```
--source include/have_dbug.inc
set debug_dbug='+d,emulate_mariadb_102'; # need to see what this does ? 
# do something with 10.2
--exec $MYSQL_DUMP --routines test
set debug_dbug='-d,emulate_mariadb_102';
```
On the server side, we should include code DBUG_EXECUTE_IF("emulate_mariadb_102", ...) We need to put this code in the place where the server sends its version to the client. So in case of emulate_mariadb_102 enabled, it should replace the real version number to say 10.2.1. Debuging from mysqldump I have seen that server version is called from mariadb_server_version_id(MYSQL *mysql) which is using mysql->server_version variable which holds information about the server.
```
mysql_get_server_version (mysql=0x555555b49cc0 <mysql_connection>) at libmariadb/libmariadb/mariadb_lib.c:
2608
mariadb_server_version_id (mysql=0x555555b49cc0 <mysql_connection>) at libmariadb/libmariadb/mariadb_lib.c
:2593
```

Using regex_replace for non-deterministic tests. From buildbot we found errors which we needed using valid regex expression to change


--- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
+-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Win32 (AMD64)

--- Dump completed on 2019-06-20 14:59:54 +-- Dump completed on 2019-06-21 0:03:12


More about regex_replace and `std::ECMAscript` [regex-1] and [regex-2].
* In general we can use `sql_mode=DEFAULT` at the end. Be carefull with version `100300` instead of `103000`

#### Link
[[updated-patch-1](https://github.com/MariaDB/server/commit/fae1439e76868664d4c17083794136e5b373f67f)]
[[updated-patch-2](https://github.com/MariaDB/server/commit/e2f6beefc8624fb0243f705e7a3e783946ae8f7e)]
[[update-patch-3](https://github.com/MariaDB/server/commit/6facc1f4c070015025896c6ebb203340753ab738)]
[[update-patch-4](https://github.com/MariaDB/server/commit/3a66e3183cd444fab43b7832b8590e7039a6bc5d)]
[[regex-1](http://www.cplusplus.com/reference/regex/ECMAScript/)]
[[regex-2](https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_MYSQL_TEST_COMMANDS.html)]

an3l / my_playground