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

pt-online-schema-change stop with error 1287 mysql: The partition engine, used by table XXX is deprecated and will be removed in a future release. Please use native partitioning instead.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Won't Fix
    • Affects Version/s: 3.0.6
    • Fix Version/s: None
    • Component/s: Code
    • Environment:

      MySQL 5.7.21

      pt-online-schema-change 3.0.6

    • Units:
      100
    • Story Points:
      8

      Description

      I'am facing this problem on my database.

      All my database is using MYISAM engine and I can't change that.

      Is there something to workaround this error, or ignore it?

      The pt-online-schema-change command and output is

      [root@NETCHARTDB001 ~]# pt-online-schema-change --default-engine --execute --user=movi_arg --ask-pass --print --no-check-alter --nodrop-old-table --max-load=Threads_running=100 --critical-load=Threads_running=150 --alter "DROP PRIMARY KEY, ADD PRIMARY KEY (DATEDAY,DN_MRBTS,DN_LNBTS,DN_LNCEL,DN_MCC,DN_MNC,PERIOD_START_TIME)" D=nokia_oss_stats_4g_timbrasil_dum,t=lte_cell_avail,h=localhost
      Enter MySQL password:
      No slaves found. See --recursion-method if host NETCHARTDB001 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 `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`...
      Creating new table...
      CREATE TABLE `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` (
      `PERIOD_START_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `DATEDAY` date NOT NULL DEFAULT '0000-00-00',
      `PERIOD_DURATION` int(11) DEFAULT NULL,
      `DN_PLMN` char(9) NOT NULL DEFAULT '',
      `DN_MRBTS` int(11) NOT NULL DEFAULT '-1',
      `DN_LNBTS` int(11) NOT NULL DEFAULT '-1',
      `DN_LNCEL` int(11) NOT NULL DEFAULT '-1',
      `DN_MCC` int(11) NOT NULL DEFAULT '-1',
      `DN_MNC` int(11) NOT NULL DEFAULT '-1',
      `M8020C0` int(11) DEFAULT NULL,
      `M8020C1` int(11) DEFAULT NULL,
      `M8020C10` int(11) DEFAULT NULL,
      `M8020C11` int(11) DEFAULT NULL,
      `M8020C12` int(11) DEFAULT NULL,
      `M8020C2` int(11) DEFAULT NULL,
      `M8020C3` int(11) DEFAULT NULL,
      `M8020C4` int(11) DEFAULT NULL,
      `M8020C5` int(11) DEFAULT NULL,
      `M8020C6` int(11) DEFAULT NULL,
      `M8020C7` int(11) DEFAULT NULL,
      `M8020C8` int(11) DEFAULT NULL,
      `M8020C9` int(11) DEFAULT NULL,
      `netchart_count` int(11) DEFAULT '1',
      PRIMARY KEY (`DATEDAY`,`DN_PLMN`,`DN_MRBTS`,`DN_LNBTS`,`DN_LNCEL`,`DN_MCC`,`DN_MNC`,`PERIOD_START_TIME`),
      KEY `alarm_index` (`PERIOD_START_TIME`,`DN_MRBTS`,`DN_LNBTS`,`DN_LNCEL`)
      ) DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (to_days(dateday))
      (PARTITION p20180121 VALUES LESS THAN (737081) ENGINE = MyISAM,
      PARTITION p20180122 VALUES LESS THAN (737082) ENGINE = MyISAM,
      PARTITION p20180123 VALUES LESS THAN (737083) ENGINE = MyISAM,
      PARTITION p20180124 VALUES LESS THAN (737084) ENGINE = MyISAM,
      PARTITION p20180125 VALUES LESS THAN (737085) ENGINE = MyISAM,
      PARTITION p20180126 VALUES LESS THAN (737086) ENGINE = MyISAM,
      PARTITION p20180127 VALUES LESS THAN (737087) ENGINE = MyISAM,
      PARTITION p20180128 VALUES LESS THAN (737088) ENGINE = MyISAM,
      PARTITION p20180129 VALUES LESS THAN (737089) ENGINE = MyISAM,
      PARTITION p20180130 VALUES LESS THAN (737090) ENGINE = MyISAM,
      PARTITION p20180131 VALUES LESS THAN (737091) ENGINE = MyISAM,
      PARTITION p20180201 VALUES LESS THAN (737092) ENGINE = MyISAM,
      PARTITION p20180202 VALUES LESS THAN (737093) ENGINE = MyISAM,
      PARTITION p20180203 VALUES LESS THAN (737094) ENGINE = MyISAM,
      PARTITION p20180204 VALUES LESS THAN (737095) ENGINE = MyISAM,
      PARTITION p20180205 VALUES LESS THAN (737096) ENGINE = MyISAM,
      PARTITION p20180206 VALUES LESS THAN (737097) ENGINE = MyISAM,
      PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = MyISAM,
      PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = MyISAM,
      PARTITION p20180209 VALUES LESS THAN (737100) ENGINE = MyISAM,
      PARTITION p20180210 VALUES LESS THAN (737101) ENGINE = MyISAM) */
      Created new table nokia_oss_stats_4g_timbrasil_dum._lte_cell_avail_new OK.
      Altering new table...
      ALTER TABLE `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` DROP PRIMARY KEY, ADD PRIMARY KEY (DATEDAY,DN_MRBTS,DN_LNBTS,DN_LNCEL,DN_MCC,DN_MNC,PERIOD_START_TIME)
      Altered `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` OK.
      2018-02-06T16:43:34 Creating triggers...
      2018-02-06T16:43:34 Created triggers OK.
      2018-02-06T16:43:34 Copying approximately 22975510 rows...
      INSERT LOW_PRIORITY IGNORE INTO `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` (`period_start_time`, `dateday`, `period_duration`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `m8020c0`, `m8020c1`, `m8020c10`, `m8020c11`, `m8020c12`, `m8020c2`, `m8020c3`, `m8020c4`, `m8020c5`, `m8020c6`, `m8020c7`, `m8020c8`, `m8020c9`, `netchart_count`) SELECT `period_start_time`, `dateday`, `period_duration`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `m8020c0`, `m8020c1`, `m8020c10`, `m8020c11`, `m8020c12`, `m8020c2`, `m8020c3`, `m8020c4`, `m8020c5`, `m8020c6`, `m8020c7`, `m8020c8`, `m8020c9`, `netchart_count` FROM `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` FORCE INDEX(`PRIMARY`) WHERE ((`dateday` > ?) OR (`dateday` = ? AND `dn_plmn` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` = ? AND `period_start_time` >= ?)) AND ((`dateday` < ?) OR (`dateday` = ? AND `dn_plmn` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` = ? AND `period_start_time` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 25939 copy nibble*/
      SELECT /*!40001 SQL_NO_CACHE */ `dateday`, `dateday`, `dn_plmn`, `dateday`, `dn_plmn`, `dn_mrbts`, `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `period_start_time` FROM `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` FORCE INDEX(`PRIMARY`) WHERE ((`dateday` > ?) OR (`dateday` = ? AND `dn_plmn` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` = ? AND `period_start_time` >= ?)) ORDER BY `dateday`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `period_start_time` LIMIT ?, 2 /*next chunk boundary*/
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 7+00:56:41 rem
      2018-02-06T18:02:06 Rows are copying very slowly. --chunk-size has been automatically reduced to 1. Check that the server is not being overloaded, or increase --chunk-time. The last chunk selected 1 rows and took 1.101 seconds to execute.
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 131+07:25:09 remain
      Pausing because Threads_running=110.
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 131+18:11:30 remain
      Pausing because Threads_running=108.
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 132+04:28:17 remain
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 134+05:34:57 remain
      Pausing because Threads_running=112.
      Pausing because Threads_running=103.
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 134+19:03:33 remain
      Copying `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail`: 0% 135+00:24:43 remain
      Pausing because Threads_running=111.
      Pausing because Threads_running=116.
      Pausing because Threads_running=104.
      Pausing because Threads_running=103.
      Pausing because Threads_running=112.
      2018-02-07T00:06:11 Dropping triggers...
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_del`
      2018-02-07T00:16:41 Error dropping trigger: 2018-02-07T00:16:41 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_del`"] at /usr/bin/pt-online-schema-change line 11198.
      
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_upd`
      2018-02-07T00:27:08 Error dropping trigger: 2018-02-07T00:27:08 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_upd`"] at /usr/bin/pt-online-schema-change line 11198.
      
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_ins`
      2018-02-07T00:37:29 Error dropping trigger: 2018-02-07T00:37:29 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_ins`"] at /usr/bin/pt-online-schema-change line 11198.
      
      2018-02-07T00:37:29 To try dropping the triggers again, execute:
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_del`
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_upd`
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_ins`
      Not dropping the new table `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` because dropping these triggers failed:
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_del`
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_upd`
      DROP TRIGGER IF EXISTS `nokia_oss_stats_4g_timbrasil_dum`.`pt_osc_nokia_oss_stats_4g_timbrasil_dum_lte_cell_avail_ins`
      These triggers must be dropped before dropping `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new`, else writing to `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` will cause MySQL error 1146 (42S02): "Table `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` doesn't exist".
      `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` was not altered.
      2018-02-07T00:06:11 Error copying rows from `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` to `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new`: 2018-02-07T00:06:11 Copying rows caused a MySQL error 1287:
      Level: Warning
      Code: 1287
      Message: The partition engine, used by table 'nokia_oss_stats_4g_timbrasil_dum._lte_cell_avail_new', is deprecated and will be removed in a future release. Please use native partitioning instead.
      Query: INSERT LOW_PRIORITY IGNORE INTO `nokia_oss_stats_4g_timbrasil_dum`.`_lte_cell_avail_new` (`period_start_time`, `dateday`, `period_duration`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `m8020c0`, `m8020c1`, `m8020c10`, `m8020c11`, `m8020c12`, `m8020c2`, `m8020c3`, `m8020c4`, `m8020c5`, `m8020c6`, `m8020c7`, `m8020c8`, `m8020c9`, `netchart_count`) SELECT `period_start_time`, `dateday`, `period_duration`, `dn_plmn`, `dn_mrbts`, `dn_lnbts`, `dn_lncel`, `dn_mcc`, `dn_mnc`, `m8020c0`, `m8020c1`, `m8020c10`, `m8020c11`, `m8020c12`, `m8020c2`, `m8020c3`, `m8020c4`, `m8020c5`, `m8020c6`, `m8020c7`, `m8020c8`, `m8020c9`, `netchart_count` FROM `nokia_oss_stats_4g_timbrasil_dum`.`lte_cell_avail` FORCE INDEX(`PRIMARY`) WHERE ((`dateday` > ?) OR (`dateday` = ? AND `dn_plmn` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` > ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` = ? AND `period_start_time` >= ?)) AND ((`dateday` < ?) OR (`dateday` = ? AND `dn_plmn` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` < ?) OR (`dateday` = ? AND `dn_plmn` = ? AND `dn_mrbts` = ? AND `dn_lnbts` = ? AND `dn_lncel` = ? AND `dn_mcc` = ? AND `dn_mnc` = ? AND `period_start_time` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 25939 copy nibble*/
      

      :

        Attachments

          Activity

            People

            Assignee:
            carlos.salguero Carlos Salguero
            Reporter:
            Lucas Lucas Martins Felipe
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 4 weeks
                4w
                Remaining:
                Remaining Estimate - 3 weeks, 4 days, 7 hours
                3w 4d 7h
                Logged:
                Remaining Estimate - 3 weeks, 4 days, 7 hours
                1h

                  Smart Checklist