New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mydumper can't recreate mysql.proc + mysql.events due to 5.7 sql_mode #142

Open
leandromorgado opened this Issue Jun 19, 2018 · 0 comments

Comments

Projects
None yet
1 participant
@leandromorgado

leandromorgado commented Jun 19, 2018

When restoring to an empty datadir, mydumper/myloader can not recreate the following tables mysql.proc + mysql.events due to a stricter default 5.7 sql_mode. The root of the problem is demonstrated below:

mysql> show create table mysql.proc\G
*************************** 1. row ***************************
       Table: proc
Create Table: CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL DEFAULT '',
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` longblob NOT NULL,
  `body` longblob NOT NULL,
  `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table mysql.proc;
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql
Database changed
mysql> CREATE TABLE `proc` (
    ->   `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    ->   `name` char(64) NOT NULL DEFAULT '',
    ->   `type` enum('FUNCTION','PROCEDURE') NOT NULL,
    ->   `specific_name` char(64) NOT NULL DEFAULT '',
    ->   `language` enum('SQL') NOT NULL DEFAULT 'SQL',
    ->   `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
    ->   `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
    ->   `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
    ->   `param_list` blob NOT NULL,
    ->   `returns` longblob NOT NULL,
    ->   `body` longblob NOT NULL,
    ->   `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    ->   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
    ->   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `body_utf8` longblob,
    ->   PRIMARY KEY (`db`,`name`,`type`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
ERROR 1067 (42000): Invalid default value for 'modified'

mysql> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                        |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `proc` (
    ->   `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    ->   `name` char(64) NOT NULL DEFAULT '',
    ->   `type` enum('FUNCTION','PROCEDURE') NOT NULL,
    ->   `specific_name` char(64) NOT NULL DEFAULT '',
    ->   `language` enum('SQL') NOT NULL DEFAULT 'SQL',
    ->   `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
    ->   `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
    ->   `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
    ->   `param_list` blob NOT NULL,
    ->   `returns` longblob NOT NULL,
    ->   `body` longblob NOT NULL,
    ->   `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
    ->   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
    ->   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ->   `body_utf8` longblob,
    ->   PRIMARY KEY (`db`,`name`,`type`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
Query OK, 0 rows affected (0.02 sec)

The mysqldump program gets around this by relaxing the sql_mode at the start and reverting it to the original value at the end. The relevant part of the mysqldump file is:

-- MySQL dump 10.13  Distrib 5.7.18, for osx10.12 (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.7.20
...
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
...
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

However, the mydumper output files do not change the sql_mode and thus fail when restoring these tables (other tables possibly affected, I only found these):

root@ubuntu-xenial:~# mydumper -V
mydumper 0.9.5, built against MySQL 5.7.21-21

root@ubuntu-xenial:~# mydumper --ask-password --threads 8 --outputdir ./mydumper --build-empty-files --triggers --routines --events --verbose 2
root@ubuntu-xenial:~# service mysql stop
root@ubuntu-xenial:~# rm -rf /var/lib/mysql/*
root@ubuntu-xenial:~# echo "" > /var/log/mysql/error.log
root@ubuntu-xenial:~# mysqld --initialize-insecure --user=mysql
root@ubuntu-xenial:~# service mysql start
root@ubuntu-xenial:~# myloader --threads=4 --overwrite-tables  --verbose 3 --directory=/root/mydumper
...
** Message: Dropping table or view (if exists) `mysql`.`proc`
** Message: Creating table `mysql`.`proc`

** (myloader:9807): CRITICAL **: Error restoring mysql.proc from file mysql.proc-schema.sql: Invalid default value for 'modified'
** Message: Dropping table or view (if exists) `mysql`.`help_relation`


** Message: Dropping table or view (if exists) `mysql`.`event`
** Message: Creating table `mysql`.`event`

** (myloader:9807): CRITICAL **: Error restoring mysql.event from file mysql.event-schema.sql: Invalid default value for 'modified'
** Message: Dropping table or view (if exists) `mysql`.`tables_priv`

If I add the SQL_MODE manually to the files, the restore works fine:

root@ubuntu-xenial:~# head -n5  mydumper-2018-06-19_22\:30\:34/mysql.event-schema.sql mydumper-2018-06-19_22\:30\:34/mysql.proc-schema.sql
==> mydumper-2018-06-19_22:30:34/mysql.event-schema.sql <==
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

==> mydumper-2018-06-19_22:30:34/mysql.proc-schema.sql <==
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Perhaps the sql_mode can be relaxed in mydumper/myloader in a similar way, at least for mysql.* system tables? (not sure if it makes sense for user data)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment