Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1512

pt-online-schema-change --data-dir fails to change directory

    XMLWordPrintable

    Details

      Description

      Hi,

      We are testing the use of pt-online-schema-change to move a 15 GB table in the near future.

      I created a copy of a small table on one of our dev boxes, as follows

      [root@localhost mysql]# ls -ltrh camspatial1/curation_automated_group_routes*
      rw-rr-. 1 mysql mysql 8.5K Mar 8 15:08 camspatial1/curation_automated_group_routes.frm
      rw-rr-. 1 mysql mysql 6.3M Mar 8 15:17 camspatial1/curation_automated_group_routes.MYI
      rw-rr-. 1 mysql mysql 5.1M Mar 8 15:17 camspatial1/curation_automated_group_routes.MYD

      CREATE TABLE ms_curation_automated_group_routes LIKE curation_automated_group_routes;
      ALTER TABLE ms_curation_automated_group_routes DISABLE KEYS;
      INSERT INTO ms_curation_automated_group_routes SELECT * FROM curation_automated_group_routes;
      ALTER TABLE ms_curation_automated_group_routes ENABLE KEYS;

      I then ran the following command, this failed, I them updated the toolkit to 3.0.8-1, running on 5.7.21-20, and it failed again.

      [root@localhost mysql]# pt-online-schema-change --data-dir="/viewranger/data/mysql_old" --execute D=camspatial1,t=ms_curation_automated_group_routes -u root -p;
      No slaves found. See --recursion-method if host localhost.localdomain has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Altering `camspatial1`.`ms_curation_automated_group_routes`...
      Creating new table...
      Created new table camspatial1._ms_curation_automated_group_routes_new OK.
      2018-03-23T16:57:15 Creating triggers...
      2018-03-23T16:57:16 Created triggers OK.
      2018-03-23T16:57:16 Copying approximately 133481 rows...
      2018-03-23T16:57:17 Copied rows OK.
      2018-03-23T16:57:17 Analyzing new table...
      2018-03-23T16:57:17 Swapping tables...
      2018-03-23T16:57:17 Swapped original and new tables OK.
      2018-03-23T16:57:17 Dropping old table...
      2018-03-23T16:57:17 Dropped old table `camspatial1`.`_ms_curation_automated_group_routes_old` OK.
      2018-03-23T16:57:17 Dropping triggers...
      2018-03-23T16:57:17 Dropped triggers OK.
      Successfully altered `camspatial1`.`ms_curation_automated_group_routes`.
      [root@localhost mysql]# pwd
      /viewranger/data/mysql
      [root@localhost mysql]# ls -ltrh camspatial1/ms*
      rw-r----. 1 mysql mysql 8.5K Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.frm
      rw-r----. 1 mysql mysql 2.6M Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.MYD
      rw-r----. 1 mysql mysql 2.8M Mar 23 16:57 camspatial1/ms_curation_automated_group_routes.MYI

      Tables are still in the old directory.

      Am I, as they say, doing it wrong ?

      Mike

       

      2018-03-23T16:24:27.306461Z 21302 Connect root@localhost on camspatial1 using Socket
      2018-03-23T16:24:27.306581Z 21302 Query set autocommit=1
      2018-03-23T16:24:27.306945Z 21302 Query SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'
      2018-03-23T16:24:27.307336Z 21302 Query SET SESSION innodb_lock_wait_timeout=1
      2018-03-23T16:24:27.307402Z 21302 Query SHOW VARIABLES LIKE 'lock_wait_timeout'
      2018-03-23T16:24:27.307653Z 21302 Query SET SESSION lock_wait_timeout=60
      2018-03-23T16:24:27.307711Z 21302 Query SHOW VARIABLES LIKE 'wait_timeout'
      2018-03-23T16:24:27.307954Z 21302 Query SET SESSION wait_timeout=10000
      2018-03-23T16:24:27.308026Z 21302 Query SELECT @@SQL_MODE
      2018-03-23T16:24:27.308095Z 21302 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'/
      2018-03-23T16:24:27.308166Z 21302 Query SELECT @@server_id /!50038 , @@hostname/
      2018-03-23T16:24:27.309906Z 21302 Query SHOW VARIABLES LIKE 'wsrep_on'
      2018-03-23T16:24:27.310209Z 21302 Query SHOW VARIABLES LIKE 'version%'
      2018-03-23T16:24:27.310556Z 21302 Query SHOW ENGINES
      2018-03-23T16:24:27.310748Z 21302 Query SHOW VARIABLES LIKE 'innodb_version'
      2018-03-23T16:24:27.311231Z 21302 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
      2018-03-23T16:24:27.311549Z 21302 Query SELECT @@SERVER_ID
      2018-03-23T16:24:27.311629Z 21302 Query SHOW GRANTS FOR CURRENT_USER()
      2018-03-23T16:24:27.311956Z 21302 Query SHOW FULL PROCESSLIST
      2018-03-23T16:24:27.312144Z 21302 Query SHOW SLAVE HOSTS
      2018-03-23T16:24:27.312774Z 21302 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
      2018-03-23T16:24:27.312979Z 21302 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
      2018-03-23T16:24:27.313225Z 21302 Query SELECT CONCAT(@@hostname, @@port)
      2018-03-23T16:24:27.826508Z 21302 Query SHOW VARIABLES
      2018-03-23T16:24:28.748993Z 21302 Query SHOW TABLES FROM `camspatial1` LIKE 'ms_curation_automated_group_routes'
      2018-03-23T16:24:28.751017Z 21302 Query SELECT VERSION()
      2018-03-23T16:24:28.751457Z 21302 Query SHOW TRIGGERS FROM `camspatial1` LIKE 'ms_curation_automated_group_routes'
      2018-03-23T16:24:28.753769Z 21302 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
      2018-03-23T16:24:28.754034Z 21302 Query USE `camspatial1`
      2018-03-23T16:24:28.754347Z 21302 Query SHOW CREATE TABLE `camspatial1`.`ms_curation_automated_group_routes`
      2018-03-23T16:24:28.754872Z 21302 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
      2018-03-23T16:24:28.756076Z 21302 Query EXPLAIN SELECT * FROM `camspatial1`.`ms_curation_automated_group_routes` WHERE 1=1
      2018-03-23T16:24:28.758361Z 21302 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
      2018-03-23T16:24:28.758572Z 21302 Query USE `camspatial1`
      2018-03-23T16:24:28.758816Z 21302 Query SHOW CREATE TABLE `camspatial1`.`ms_curation_automated_group_routes`
      2018-03-23T16:24:28.759292Z 21302 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
      2018-03-23T16:24:28.760077Z 21302 Query CREATE TABLE `camspatial1`.`_ms_curation_automated_group_routes_new` (
      2018-03-23T16:24:29.635422Z 21302 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
      2018-03-23T16:24:29.635499Z 21302 Query USE `camspatial1`
      2018-03-23T16:24:29.635590Z 21302 Query SHOW CREATE TABLE `camspatial1`.`_ms_curation_automated_group_routes_new`
      2018-03-23T16:24:29.635909Z 21302 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
      2018-03-23T16:24:29.636404Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.637109Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.637406Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.637699Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.637997Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.638294Z 21302 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'camspatial1' AND EVENT_OBJECT_TABLE = 'ms_curation_automated_group_routes'
      2018-03-23T16:24:29.639052Z 21302 Query CREATE TRIGGER `pt_osc_camspatial1_ms_curation_automated_group_routes_del` AFTER DELETE ON `camspatial1`.`ms_curation_automated_group_routes` FOR EACH ROW DELETE IGNORE FROM `camspatial1`.`_ms_curation_automated_group_routes_new` WHERE `camspatial1`.`_ms_curation_automated_group_routes_new`.`groupid` <=> OLD.`groupid` AND `camspatial1`.`_ms_curation_automated_group_routes_new`.`routeid` <=> OLD.`routeid`
      2018-03-23T16:24:29.837146Z 21302 Query CREATE TRIGGER `pt_osc_camspatial1_ms_curation_automated_group_routes_upd` AFTER UPDATE ON `camspatial1`.`ms_curation_automated_group_routes` FOR EACH ROW BEGIN DELETE IGNORE FROM `camspatial1`.`_ms_curation_automated_group_routes_new` WHERE !(OLD.`groupid` <=> NEW.`groupid` AND OLD.`routeid` <=> NEW.`routeid`) AND `camspatial1`.`_ms_curation_automated_group_routes_new`.`groupid` <=> OLD.`groupid` AND `camspatial1`.`_ms_curation_automated_group_routes_new`.`routeid` <=> OLD.`routeid`;REPLACE INTO `camspatial1`.`_ms_curation_automated_group_routes_new` (`groupid`, `routeid`) VALUES (NEW.`groupid`, NEW.`routeid`);END
      2018-03-23T16:24:30.037796Z 21302 Query CREATE TRIGGER `pt_osc_camspatial1_ms_curation_automated_group_routes_ins` AFTER INSERT ON `camspatial1`.`ms_curation_automated_group_routes` FOR EACH ROW REPLACE INTO `camspatial1`.`_ms_curation_automated_group_routes_new` (`groupid`, `routeid`) VALUES (NEW.`groupid`, NEW.`routeid`)
      2018-03-23T16:24:30.239048Z 21302 Query EXPLAIN SELECT * FROM `camspatial1`.`ms_curation_automated_group_routes` WHERE 1=1
      2018-03-23T16:24:30.239732Z 21302 Query SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) ORDER BY `groupid`, `routeid` LIMIT 1 /*first lower boundary/
      2018-03-23T16:24:30.239955Z 21302 Query SELECT /!40001 SQL_NO_CACHE */ `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX (`PRIMARY`) WHERE `groupid` IS NOT NULL AND `routeid` IS NOT NULL ORDER BY `groupid`, `routeid` LIMIT 1 /*key_len/
      2018-03-23T16:24:30.240118Z 21302 Query EXPLAIN SELECT /!40001 SQL_NO_CACHE */ * FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX (`PRIMARY`) WHERE `groupid` = '3' AND `routeid` >= '15372' /*key_len/
      2018-03-23T16:24:30.240337Z 21302 Query EXPLAIN SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '3') OR (`groupid` = '3' AND `routeid` >= '15372')) ORDER BY `groupid`, `routeid` LIMIT 999, 2 /*next chunk boundary/
      2018-03-23T16:24:30.240548Z 21302 Query SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '3') OR (`groupid` = '3' AND `routeid` >= '15372')) ORDER BY `groupid`, `routeid` LIMIT 999, 2 /*next chunk boundary/
      2018-03-23T16:24:30.240916Z 21302 Query EXPLAIN SELECT `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '3') OR (`groupid` = '3' AND `routeid` >= '15372')) AND ((`groupid` < '741') OR (`groupid` = '741' AND `routeid` <= '58564')) LOCK IN SHARE MODE /explain pt-online-schema-change 26113 copy nibble/
      2018-03-23T16:24:30.241235Z 21302 Query INSERT LOW_PRIORITY IGNORE INTO `camspatial1`.`_ms_curation_automated_group_routes_new` (`groupid`, `routeid`) SELECT `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '3') OR (`groupid` = '3' AND `routeid` >= '15372')) AND ((`groupid` < '741') OR (`groupid` = '741' AND `routeid` <= '58564')) LOCK IN SHARE MODE /pt-online-schema-change 26113 copy nibble/
      2018-03-23T16:24:30.244348Z 21302 Query SHOW WARNINGS
      2018-03-23T16:24:30.244530Z 21302 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
      2018-03-23T16:24:30.244746Z 21302 Query EXPLAIN SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '741') OR (`groupid` = '741' AND `routeid` >= '58566')) ORDER BY `groupid`, `routeid` LIMIT 158886, 2 /*next chunk boundary/
      2018-03-23T16:24:30.244893Z 21302 Query SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '741') OR (`groupid` = '741' AND `routeid` >= '58566')) ORDER BY `groupid`, `routeid` LIMIT 158886, 2 /*next chunk boundary/
      2018-03-23T16:24:30.270882Z 21302 Query SELECT /!40001 SQL_NO_CACHE */ `groupid`, `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) ORDER BY `groupid` DESC,`routeid` DESC LIMIT 1 /*last upper boundary/
      2018-03-23T16:24:30.271012Z 21302 Query EXPLAIN SELECT `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '741') OR (`groupid` = '741' AND `routeid` >= '58566')) AND ((`groupid` < '39173') OR (`groupid` = '39173' AND `routeid` <= '80171')) LOCK IN SHARE MODE /explain pt-online-schema-change 26113 copy nibble/
      2018-03-23T16:24:30.271280Z 21302 Query INSERT LOW_PRIORITY IGNORE INTO `camspatial1`.`_ms_curation_automated_group_routes_new` (`groupid`, `routeid`) SELECT `groupid`, `routeid` FROM `camspatial1`.`ms_curation_automated_group_routes` FORCE INDEX(`PRIMARY`) WHERE ((`groupid` > '741') OR (`groupid` = '741' AND `routeid` >= '58566')) AND ((`groupid` < '39173') OR (`groupid` = '39173' AND `routeid` <= '80171')) LOCK IN SHARE MODE /pt-online-schema-change 26113 copy nibble/
      2018-03-23T16:24:31.015700Z 21302 Query SHOW WARNINGS
      2018-03-23T16:24:31.016002Z 21302 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
      2018-03-23T16:24:31.016429Z 21302 Query ANALYZE TABLE `camspatial1`.`_ms_curation_automated_group_routes_new` /* pt-online-schema-change */
      2018-03-23T16:24:31.037290Z 21302 Query RENAME TABLE `camspatial1`.`ms_curation_automated_group_routes` TO `camspatial1`.`_ms_curation_automated_group_routes_old`, `camspatial1`.`_ms_curation_automated_group_routes_new` TO `camspatial1`.`ms_curation_automated_group_routes`
      2018-03-23T16:24:31.443377Z 21302 Query DROP TABLE IF EXISTS `camspatial1`.`_ms_curation_automated_group_routes_old`
      2018-03-23T16:24:31.444593Z 21302 Query DROP TRIGGER IF EXISTS `camspatial1`.`pt_osc_camspatial1_ms_curation_automated_group_routes_del`
      2018-03-23T16:24:31.444728Z 21302 Query DROP TRIGGER IF EXISTS `camspatial1`.`pt_osc_camspatial1_ms_curation_automated_group_routes_upd`
      2018-03-23T16:24:31.444800Z 21302 Query DROP TRIGGER IF EXISTS `camspatial1`.`pt_osc_camspatial1_ms_curation_automated_group_routes_ins`
      2018-03-23T16:24:31.444973Z 21302 Query SHOW TABLES FROM `camspatial1` LIKE '_ms_curation_automated_group_routes_new'
      2018-03-23T16:24:31.445689Z 21302 Quit

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            marblerun Michael Shield
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist