david-ducos-percona/myloader_pt-osc

DONT USE SCRIPT, POSSIBLE DATA LOSS

Opened this issue · 0 comments

mysql> select count(*) from bc.ohlcv;
+----------+
| count(*) |
+----------+
|920384718 |
+----------+
1 row in set (0.00 sec)
./myloader_py.sh -D bc -T ohlcv --chunk-size 10000 -t 40 --alter 'DROP INDEX idx2 , ADD INDEX idx2(exchange ASC, pair ASC, ts_close ASC, close ASC)'
Sun Jun  7 23:59:16 UTC 2020: Starting pt-osc 
ERROR 1054 (42S22) at line 1: Unknown column 'id' in 'field list'
Sun Jun  7 23:59:16 UTC 2020: Creating the INSERT INTO files
Sun Jun  7 23:59:16 UTC 2020: All pids finished
Sun Jun  7 23:59:16 UTC 2020: Starting myloader

** (myloader:79380): CRITICAL **: 23:59:16.630: Error restoring bc.ohlcv from file bc.ohlcv.1.sql: Unknown column 'id' in 'where clause'

** (myloader:79380): CRITICAL **: 23:59:16.630: Error restoring bc.ohlcv from file bc.ohlcv.0.sql: Unknown column 'id' in 'where clause'
Sun Jun  7 23:59:16 UTC 2020: Removing pause file
Sun Jun  7 23:59:16 UTC 2020: Waiting to pt-osc to rename tables...

# A software update is available:
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
Starting a dry run.  `bc`.`ohlcv` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `bc`.`_ohlcv_new` (
  `exchange` varchar(255) NOT NULL,
  `pair` varchar(30) NOT NULL,
  `interval_min` smallint unsigned NOT NULL,
  `ts` int NOT NULL,
  `open` decimal(17,8) NOT NULL,
  `high` decimal(17,8) NOT NULL,
  `low` decimal(17,8) NOT NULL,
  `close` decimal(17,8) NOT NULL,
  `vol` varchar(255) DEFAULT NULL,
  `dt_open` datetime GENERATED ALWAYS AS (from_unixtime(`ts`)) VIRTUAL,
  `dt_close` datetime GENERATED ALWAYS AS (from_unixtime((`ts` + (`interval_min` * 60)))) VIRTUAL,
  `ts_close` int GENERATED ALWAYS AS ((`ts` + (`interval_min` * 60))) VIRTUAL,
  UNIQUE KEY `historical_klines_trader_name_pair_dt_interval_min_uindex` (`exchange`,`pair`,`interval_min`,`ts`),
  KEY `idx1` (`exchange`,`pair`,`ts`),
  KEY `idx2` (`exchange`,`pair`,`interval_min`,`ts_close`,`close`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
Created new table bc._ohlcv_new OK.
Altering new table...
ALTER TABLE `bc`.`_ohlcv_new` DROP INDEX idx2 , ADD INDEX idx2(exchange ASC, pair ASC, ts_close ASC, close ASC)
Altered `bc`.`_ohlcv_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `bc`.`_ohlcv_new` (`exchange`, `pair`, `interval_min`, `ts`, `open`, `high`, `low`, `close`, `vol`) SELECT `exchange`, `pair`, `interval_min`, `ts`, `open`, `high`, `low`, `close`, `vol` FROM `bc`.`ohlcv` FORCE INDEX(`historical_klines_trader_name_pair_dt_interval_min_uindex`) WHERE ((`exchange` > ?) OR (`exchange` = ? AND `pair` > ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` > ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` = ? AND `ts` >= ?)) AND ((`exchange` < ?) OR (`exchange` = ? AND `pair` < ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` < ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` = ? AND `ts` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 78742 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `exchange`, `exchange`, `pair`, `exchange`, `pair`, `interval_min`, `exchange`, `pair`, `interval_min`, `ts` FROM `bc`.`ohlcv` FORCE INDEX(`historical_klines_trader_name_pair_dt_interval_min_uindex`) WHERE ((`exchange` > ?) OR (`exchange` = ? AND `pair` > ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` > ?) OR (`exchange` = ? AND `pair` = ? AND `interval_min` = ? AND `ts` >= ?)) ORDER BY `exchange`, `pair`, `interval_min`, `ts` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `bc`.`pt_osc_bc_ohlcv_del`
DROP TRIGGER IF EXISTS `bc`.`pt_osc_bc_ohlcv_upd`
DROP TRIGGER IF EXISTS `bc`.`pt_osc_bc_ohlcv_ins`
2020-06-07T23:59:13 Dropping new table...
DROP TABLE IF EXISTS `bc`.`_ohlcv_new`;
2020-06-07T23:59:13 Dropped new table OK.
Dry run complete.  `bc`.`ohlcv` was not altered.
mysql> select count(*) from bc.ohlcv;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Despite bc.ohlcv was not altered. in log, table was altered, index has been replaced and data 100% lost.