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.

General

Escalation

General

Escalation

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*/

Environment

MySQL 5.7.21

pt-online-schema-change 3.0.6

Smart Checklist

Activity

Show:

Lucas Martins Felipe February 15, 2018 at 1:42 PM

Thank you very much!

I tried for my error and it worked.

Carlos Salguero February 8, 2018 at 12:34 PM

MyISAM is going to be deprecated.

There is a workaround explained in the comments.

Carlos Salguero February 8, 2018 at 12:33 PM

That's a MySQL specific error, not a pt-online-schema-change problem.

As a workaround, you can add the error code to the list of ignored errors but be aware this was not properly tested.

https://github.com/percona/percona-toolkit/blob/3.0/bin/pt-online-schema-change#L8213-L8223

my %ignore_code = (
   # Error: 1592 SQLSTATE: HY000 (ER_BINLOG_UNSAFE_STATEMENT)
   # Message: Statement may not be safe to log in statement format. 
   # Ignore this warning because we have purposely set statement-based
   # replication.
   1592 => 1,
   # Error: 1062 SQLSTATE: 23000 ( ER_DUP_ENTRY )
   # Message: Duplicate entry '%ld' for key '%s'
   # MariaDB 5.5.28+ has this as a warning; See https://bugs.launchpad.net/percona-toolkit/+bug/1099836
   1062 => 1,
   1218 => 1, # <--- HERE !!!
);

I tried that myself on a MySQL 5.7 sandbox this way:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0,00 sec)

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `lte_cell_avail` (
`PERIOD_START_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`DATEDAY` date ,
`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) */;

then I added 10K random rows to the table:

mysql_random_data_load -h 127.1 -P 12345 -u msandbox -p msandbox test lte_cell_avail 10000

and then I ran osc

bin/pt-online-schema-change h=127.1,P=12345,u=msandbox,p=msandbox,D=test,t=lte_cell_avail --execute --alter 'add column fint int'
Found 2 slaves:
karl-OMEN -> 127.0.0.1:12346
karl-OMEN -> 127.0.0.1:12347
Will check slave lag on:
karl-OMEN -> 127.0.0.1:12346
karl-OMEN -> 127.0.0.1:12347
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 `test`.`lte_cell_avail`...
Creating new table...
Created new table test._lte_cell_avail_new OK.
Altering new table...
Altered `test`.`_lte_cell_avail_new` OK.
2018-02-08T09:20:51 Creating triggers...
2018-02-08T09:20:51 Created triggers OK.
2018-02-08T09:20:51 Copying approximately 10000 rows...
2018-02-08T09:20:51 Copied rows OK.
2018-02-08T09:20:51 Analyzing new table...
2018-02-08T09:20:51 Swapping tables...
2018-02-08T09:20:51 Swapped original and new tables OK.
2018-02-08T09:20:51 Dropping old table...
2018-02-08T09:20:51 Dropped old table `test`.`_lte_cell_avail_old` OK.
2018-02-08T09:20:51 Dropping triggers...
2018-02-08T09:20:51 Dropped triggers OK.
Successfully altered `test`.`lte_cell_avail`.

Table after running osc

mysql> show create table lte_cell_avail\G
*************************** 1. row ***************************
Table: lte_cell_avail
Create Table: CREATE TABLE `lte_cell_avail` (
`PERIOD_START_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`DATEDAY` date NOT NULL,
`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',
`fint` int(11) DEFAULT NULL,
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`)
) ENGINE=MyISAM 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) */
1 row in set, 1 warning (0,00 sec)

Rows are still in the table:

mysql> select count(*) from lte_cell_avail;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0,00 sec)

Notice I changed the defaults for the datetime & date fields since '0000-00-00' is invalid.

I would recommend to try it first on a testing environment.

Regards

Won't Do

Details

Assignee

Carlos Salguero(Deactivated)

Reporter

Lucas Martins Felipe

Priority

Medium

Story Points

Original estimate

Time tracking

1h logged3w 4d 7h remaining

Smart Checklist

Created February 8, 2018 at 8:58 AM

Updated March 4, 2024 at 4:35 PM

Resolved February 8, 2018 at 12:34 PM

Configure

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.

Description

Environment

Smart Checklist

Activity

Lucas Martins Felipe February 15, 2018 at 1:42 PM

Carlos Salguero February 8, 2018 at 12:34 PM

Carlos Salguero February 8, 2018 at 12:33 PM

Details

Assignee

Reporter

Priority

Components

Affects versions

Labels

Story Points

Original estimate

Time tracking

Smart Checklist