Skip to content
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

Closed
leandromorgado opened this issue Jun 19, 2018 · 14 comments · Fixed by #370 or #398
Closed

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

leandromorgado opened this issue Jun 19, 2018 · 14 comments · Fixed by #370 or #398
Labels

Comments

@leandromorgado
Copy link

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)

@maxbube maxbube added the bug label Nov 22, 2018
@davidducos
Copy link
Member

Similar to #274

@davidducos
Copy link
Member

#370 will allow us to implement any export or import strategy.

@davidducos davidducos added this to the Release 0.10.9 milestone Jul 16, 2021
@davidducos davidducos linked a pull request Aug 18, 2021 that will close this issue
@davidducos
Copy link
Member

Hi @leandromorgado Can you check again configuring the variables #370 ?

@davidducos
Copy link
Member

Closing without confirmation

@leandromorgado
Copy link
Author

Hey @davidducos,

sorry was on vacation and couldn't get to this before now.

I compiled the new version and tried with the following options but I'm still running in to the same problem.

Maybe the config file is not correctly specified?

root@vagrant:~/mydumper# cat /root/mydumper/myloader_config.ini
[myloader_variables]
SQL_MODE='NO_AUTO_VALUE_ON_ZERO'

root@vagrant:~/mydumper# ./myloader --version
myloader 0.10.8, built against MySQL 5.7.35
root@vagrant:~/mydumper# ./myloader --threads=4 --overwrite-tables  --verbose 3 --directory=/root/mydumper/DUMPS/ --config=/root/mydumper/myloader_config.ini
...
** Message: 17:18:36.668: Creating table mysql.proc

** (myloader:8229): CRITICAL **: 17:18:36.669: Error restoring mysql.proc from file mysql.proc-schema.sql: Invalid default value for 'modified'


** (myloader:8229): CRITICAL **: 17:18:36.670: Error restoring mysql.proc from file mysql.proc-schema.sql: Invalid default value for 'modified'

@davidducos
Copy link
Member

Hi @leandromorgado, you can check if SQL_MODE is being set from the general log if you are just testing.

@leandromorgado
Copy link
Author

@davidducos , I don't see it in the general log.

root@vagrant:~/mydumper# ./myloader --threads=4 --overwrite-tables  --verbose 3 --directory=/root/mydumper/DUMPS/ --config=/root/mydumper/myloader_config.ini

...
2021-08-23T17:47:25.482083Z        36 Query     SHOW CREATE DATABASE `mysql`
2021-08-23T17:47:25.482741Z        36 Query     DROP TABLE IF EXISTS `mysql`.`proc`
2021-08-23T17:47:25.482857Z        36 Query     DROP VIEW IF EXISTS `mysql`.`proc`
2021-08-23T17:47:25.483200Z        36 Query     USE `mysql`
2021-08-23T17:47:25.483410Z        36 Query     /*!40101 SET NAMES binary*/
2021-08-23T17:47:25.483481Z        36 Query     /*!40014 SET FOREIGN_KEY_CHECKS=0*/
2021-08-23T17:47:25.483526Z        36 Query     /*!40103 SET TIME_ZONE='+00:00' */
2021-08-23T17:47:25.483657Z        36 Query     CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',

I'm not very confident in the config file I created (not sure it's correct syntax/format).
I now tried with lower case sql_mode but got the same results.
Does it look okay to you?

root@vagrant:~/mydumper# cat myloader_config.ini
[myloader_variables]
sql_mode='NO_AUTO_VALUE_ON_ZERO'

@davidducos davidducos linked a pull request Aug 24, 2021 that will close this issue
@davidducos
Copy link
Member

Sorry @leandromorgado, there was a bug... #398 fix it.

@leandromorgado
Copy link
Author

No worries @davidducos .
I tried with the new version and unfortunately, I still don't see the SQL mode session var being set when running myloader. I've attached a file with the test results.
mydumper_142_test_results.txt

@aashishmahajan
Copy link

Hi, I am trying to use mydumper/myloader approach to move 100+ gigs of data from given environment to target environment, While doing so, I am running into the
(myloader:14622): CRITICAL **: 07:46:14.451: Error restoring: CREATE ALGORITHM=UNDEFINED DEFINER={username}@{ip} SQL SECURITY DEFINER VIEW {tableNm}``

As I am moving a lot of data, I am using the --compress function to create compressed dump files, as per the notes shared in #84 and other discussion post, I am unable to find the right approach to remove/delete/comment/ignore the DEFINERS from sql.gz files.
is there any recommended approach that can be used?

@davidducos davidducos reopened this Dec 27, 2021
@aashishmahajan
Copy link

@davidducos : thanks for opening up the issue again. really appreciate the help. To add to the previous concern, if I unload un-compressed SQL dump files. the bigger problem in scope is, if I am running subprocess.call([("sed -i '' 's/DEFINER=[^*]*\*/\*/g' " + filePath)], shell = True) to remove the definer statement, my files still error out during removal, as the dump files have fields with special character and adds a time complexity of couple of hours to go over 700tables in given schema. Having an option of --skip-definer at the time of creation is something that would be helpful, I have tried using --no-views and bunch of other option but still struggling, the solution is something I am building up, and would be used at any given time against 200gb+ data on average.

@davidducos
Copy link
Member

@aashishmahajan, We are going to follow the --skip-definer option in: #378

@leandromorgado, I changed some things related to the config file option, actually, that option is not available anymore and we use --defaults-file. Can you test latest version and let me know if it is working properly.

@davidducos
Copy link
Member

@leandromorgado,
I have checked and you can use:

[myloader_variables]
SQL_MODE = <WHAT EVER MODE YOU LIKE>

In your --defaults-file

@shankhashubhradas-sureify
Copy link

What about?

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysql.proc' does
n't exist

The above error got resolved by the given command:
CREATE TABLE proc( -> dbchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', -> namechar(64) NOT NULL DEFAULT '', -> typeenum('FUNCTION','PROCEDURE') NOT NULL, -> specific_namechar(64) NOT NULL DEFAULT '', -> languageenum('SQL') NOT NULL DEFAULT 'SQL', -> sql_data_accessenum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL', -> is_deterministicenum('YES','NO') NOT NULL DEFAULT 'NO', -> security_typeenum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER', -> param_listblob NOT NULL, -> returnslongblob NOT NULL, -> bodylongblob NOT NULL, -> definerchar(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', -> createdtimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> modifiedtimestamp NOT NULL DEFAULT '0000-00-00 00:00:00', -> sql_modeset('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 '', -> commenttext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -> character_set_clientchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, -> collation_connectionchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, -> db_collationchar(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';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
5 participants