Merge lp:~percona-toolkit-dev/percona-toolkit/add-sandbox-for-5.7-1403900 into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.13

[Frank Cizmich]

Added new config file and system tables creation files specific for MySQL 5.7
Also tweaked a couple of sakila tables so they work with the new default sql_mode's

[Daniel Nichter (daniel-nichter)]

Should work. Perhaps it's time PT looks at modernizing this. Long time ago we sandboxed MySQL, and rolled our own sandboxing scripts. Today we should probably using VMs/virtualization, or something less hackish and more standard.

review: Approve

=== modified file 'sandbox/sakila.sql'
--- sandbox/sakila.sql	2012-12-05 22:05:41 +0000
+++ sandbox/sakila.sql	2014-12-18 19:51:36 +0000
@@ -6,6 +6,7 @@
 SET UNIQUE_CHECKS=0;
 SET FOREIGN_KEY_CHECKS=0;
 
+
 CREATE TABLE `actor` (
   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
   `first_name` varchar(45) NOT NULL,
@@ -370,7 +371,7 @@
 FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
         JOIN film_actor ON film.film_id = film_actor.film_id
 	JOIN actor ON film_actor.actor_id = actor.actor_id
-GROUP BY film.film_id;
+GROUP BY film.film_id, category.name;
 
 CREATE VIEW nicer_but_slower_film_list
 AS
@@ -381,7 +382,7 @@
 FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
         JOIN film_actor ON film.film_id = film_actor.film_id
 	JOIN actor ON film_actor.actor_id = actor.actor_id
-GROUP BY film.film_id;
+GROUP BY film.film_id, category.name;
 
 CREATE VIEW staff_list
 AS
 
=== added directory 'sandbox/servers/5.7'
=== added file 'sandbox/servers/5.7/data.tar.gz'
 Binary files sandbox/servers/5.7/data.tar.gz	1970-01-01 00:00:00 +0000 and sandbox/servers/5.7/data.tar.gz	2014-12-18 19:51:36 +0000 differ
=== added file 'sandbox/servers/5.7/my.sandbox.cnf'
--- sandbox/servers/5.7/my.sandbox.cnf	1970-01-01 00:00:00 +0000
+++ sandbox/servers/5.7/my.sandbox.cnf	2014-12-18 19:51:36 +0000
@@ -0,0 +1,29 @@
+[client]
+user     = msandbox
+password = msandbox
+port     = PORT
+socket   = /tmp/PORT/mysql_sandboxPORT.sock
+
+[mysqld]
+port                       = PORT
+socket                     = /tmp/PORT/mysql_sandboxPORT.sock
+pid-file                   = /tmp/PORT/data/mysql_sandboxPORT.pid
+basedir                    = PERCONA_TOOLKIT_SANDBOX
+datadir                    = /tmp/PORT/data
+key_buffer_size            = 16M
+innodb_buffer_pool_size    = 16M
+innodb_data_home_dir       = /tmp/PORT/data
+innodb_log_group_home_dir  = /tmp/PORT/data
+innodb_data_file_path      = ibdata1:10M:autoextend
+innodb_log_file_size       = 5M
+log-bin                    = mysql-bin
+relay_log                  = mysql-relay-bin
+log_slave_updates
+server-id                  = PORT
+report-host                = 127.0.0.1
+report-port                = PORT
+log-error                  = /tmp/PORT/data/mysqld.log
+innodb_lock_wait_timeout   = 3
+general_log
+general_log_file           = genlog
+lower_case_table_names     = 0
 
=== added file 'sandbox/servers/5.7/system_idb_tables.sql'
--- sandbox/servers/5.7/system_idb_tables.sql	1970-01-01 00:00:00 +0000
+++ sandbox/servers/5.7/system_idb_tables.sql	2014-12-18 19:51:36 +0000
@@ -0,0 +1,149 @@
+USE `mysql`;
+
+CREATE TABLE IF NOT EXISTS `innodb_index_stats` (
+  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `stat_value` bigint(20) unsigned NOT NULL,
+  `sample_size` bigint(20) unsigned DEFAULT NULL,
+  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
+  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
+
+CREATE TABLE IF NOT EXISTS `innodb_table_stats` (
+  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `n_rows` bigint(20) unsigned NOT NULL,
+  `clustered_index_size` bigint(20) unsigned NOT NULL,
+  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
+  PRIMARY KEY (`database_name`,`table_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
+
+CREATE TABLE IF NOT EXISTS `slave_master_info` (
+  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
+  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
+  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
+  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The host name of the master.',
+  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
+  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
+  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
+  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
+  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
+  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
+  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
+  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
+  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
+  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
+  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
+  `Heartbeat` float NOT NULL,
+  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
+  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
+  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
+  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
+  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
+  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
+  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
+  PRIMARY KEY (`Host`,`Port`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
+
+CREATE TABLE IF NOT EXISTS `slave_relay_log_info` (
+  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
+  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
+  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
+  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
+  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
+  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
+  `Number_of_workers` int(10) unsigned NOT NULL,
+  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
+  PRIMARY KEY (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
+
+
+CREATE TABLE IF NOT EXISTS `slave_worker_info` (
+  `Id` int(10) unsigned NOT NULL,
+  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
+  `Relay_log_pos` bigint(20) unsigned NOT NULL,
+  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
+  `Master_log_pos` bigint(20) unsigned NOT NULL,
+  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
+  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
+  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
+  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
+  `Checkpoint_seqno` int(10) unsigned NOT NULL,
+  `Checkpoint_group_size` int(10) unsigned NOT NULL,
+  `Checkpoint_group_bitmap` blob NOT NULL,
+  PRIMARY KEY (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
+
+
+CREATE TABLE IF NOT EXISTS `help_category` (
+  `help_category_id` smallint(5) unsigned NOT NULL,
+  `name` char(64) NOT NULL,
+  `parent_category_id` smallint(5) unsigned DEFAULT NULL,
+  `url` text NOT NULL,
+  PRIMARY KEY (`help_category_id`),
+  UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help categories';
+
+CREATE TABLE IF NOT EXISTS `help_keyword` (
+  `help_keyword_id` int(10) unsigned NOT NULL,
+  `name` char(64) NOT NULL,
+  PRIMARY KEY (`help_keyword_id`),
+  UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help keywords';
+
+CREATE TABLE IF NOT EXISTS `help_relation` (
+  `help_topic_id` int(10) unsigned NOT NULL,
+  `help_keyword_id` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='keyword-topic relation';
+
+CREATE TABLE IF NOT EXISTS `help_topic` (
+  `help_topic_id` int(10) unsigned NOT NULL,
+  `name` char(64) NOT NULL,
+  `help_category_id` smallint(5) unsigned NOT NULL,
+  `description` text NOT NULL,
+  `example` text NOT NULL,
+  `url` text NOT NULL,
+  PRIMARY KEY (`help_topic_id`),
+  UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help topics';
+
+CREATE TABLE IF NOT EXISTS `time_zone` (
+  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
+  PRIMARY KEY (`Time_zone_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones';
+
+CREATE TABLE IF NOT EXISTS `time_zone_leap_second` (
+  `Transition_time` bigint(20) NOT NULL,
+  `Correction` int(11) NOT NULL,
+  PRIMARY KEY (`Transition_time`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Leap seconds information for time zones';
+
+CREATE TABLE IF NOT EXISTS `time_zone_name` (
+  `Name` char(64) NOT NULL,
+  `Time_zone_id` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`Name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone names';
+
+CREATE TABLE IF NOT EXISTS `time_zone_transition` (
+  `Time_zone_id` int(10) unsigned NOT NULL,
+  `Transition_time` bigint(20) NOT NULL,
+  `Transition_type_id` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`Time_zone_id`,`Transition_time`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone transitions';
+
+CREATE TABLE IF NOT EXISTS `time_zone_transition_type` (
+  `Time_zone_id` int(10) unsigned NOT NULL,
+  `Transition_type_id` int(10) unsigned NOT NULL,
+  `Offset` int(11) NOT NULL DEFAULT '0',
+  `Is_DST` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `Abbreviation` char(8) NOT NULL DEFAULT '',
+  PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone transition types';
+
+