pt-archiver giving error : The following columns exist in --dest but not --source:

Polyglot Projects Percona Toolkit
1

Hi,

I’m running the pt-archiver on a table and I’m getting the error : The following columns exist in --dest but not --source: {column_name}

However, the column does exist in both the source and the destination tables. Is there a known issue with this error message? If not, what could I possibly look at t fix this?

Thanks.

2

Hi,

I don’t remember any related issue.
Would it be possible for you to share your table structure (SHOW CREATE TABLE), MySQL version and how many slaves do you have?
I would like to try to reproduce it.

Regards

3

Thanks for the reply.

Source table: (Server version: 5.6.36-82.1-log Percona Server (GPL), Release 82.1, Revision 1a00d79)

CREATE TABLE doubleoptin_audit (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘The unique id of the audit record.’,
source enum(‘VIAMEDIA’,‘VODACOM’) NOT NULL COMMENT ‘Transaction originator’,
tso_id int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘An internally generated transaction. To be used in conjunction with the reference_number to define a single MESH transaction.’,
msisdn varchar(21) NOT NULL COMMENT ‘MSISDN against which the service was actually provisioned.’,
operation_code enum(‘SUBSCRIBE’,‘TERMINATE’,‘PURCHASE’,‘REINFORM’,‘RETRY’,‘DECLINE’,‘CONFIRM’,‘EXPIRE’,‘ERROR’) NOT NULL COMMENT ‘The code pertinent to the operation which should be performed.’,
channel enum(‘SMS’,‘USSD’,‘WAP’,‘MESH’) NOT NULL COMMENT ‘The Channel (bearer type) associated with this subscription.’,
reference_number varchar(22) NOT NULL COMMENT ‘The Reference Number associated with this subscription (AuthReqRef).’,
service_id varchar(40) NOT NULL COMMENT ‘The Service Identifier associated with this subscription.’,
service_name varchar(100) NOT NULL COMMENT ‘The name of the service associated with this subscription.’,
price varchar(20) DEFAULT ‘’ COMMENT ‘The price of the service in decimal format.’,
billing_frequency enum(‘’,‘ONCE’,‘DAY’,‘WEEK’,‘MONTH’,‘YEAR’) DEFAULT ‘’ COMMENT ‘The billing frequency of the service associated with this subscription.’,
url varchar(200) DEFAULT ‘’ COMMENT ‘The WAP URL of the service associated with this subscription. Used for backward compatibility.’,
callback varchar(500) DEFAULT ‘’ COMMENT ‘The callback url to execute for the associated subscription. (e.g. call to BillAuth with the status of the subscription)’,
txn_id varchar(38) DEFAULT NULL COMMENT ‘The unique id of the transaction.’,
ext_asset_id varchar(80) DEFAULT NULL COMMENT ‘The external identifier of the service subscription. This identifier will need to be passed to the RAB billing system (as the Hv parameter) to complete billing for the service. This parameter is only populated for ad-hoc requests.’,
audit_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘The timestamp when the transaction happened.’,
co_id varchar(4) DEFAULT NULL COMMENT ‘The CoID of the transaction that failed on RAB.’,
co_key varchar(9) DEFAULT NULL COMMENT ‘The CoKey of the transaction that failed on RAB.’,
customer_sms_id int(10) unsigned DEFAULT NULL COMMENT ‘The customer sms id in the customer_sms table.’,
custom_message_id int(10) unsigned DEFAULT NULL COMMENT ‘The custom message id in the custom_message table.’,
failed_reference_number varchar(22) DEFAULT NULL COMMENT ‘The reference number of the failed transaction (AuthReqRef).’,
PRIMARY KEY (id),
KEY idx_msisdn (msisdn),
KEY idx_audit_date (audit_date)
) ENGINE=InnoDB AUTO_INCREMENT=19355846 DEFAULT CHARSET=latin1 COMMENT=‘Vodacom Double Opt-In transaction audit’

Destination table: (Server version: 5.6.36-82.1-log Percona Server (GPL), Release 82.1, Revision 1a00d79)

CREATE TABLE doubleoptin_audit (
id int(11) unsigned NOT NULL COMMENT ‘The unique id of the audit record.’,
source enum(‘VIAMEDIA’,‘VODACOM’) NOT NULL COMMENT ‘Transaction originator’,
tso_id int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘An internally generated transaction. To be used in conjunction with the reference_number to define a single MESH transaction.’,
msisdn varchar(21) NOT NULL COMMENT ‘MSISDN against which the service was actually provisioned.’,
operation_code enum(‘SUBSCRIBE’,‘TERMINATE’,‘PURCHASE’,‘REINFORM’,‘RETRY’,‘DECLINE’,‘CONFIRM’,‘EXPIRE’,‘ERROR’) NOT NULL COMMENT ‘The code pertinent to the operation which should be performed.’,
channel enum(‘SMS’,‘USSD’,‘WAP’,‘MESH’) NOT NULL COMMENT ‘The Channel (bearer type) associated with this subscription.’,
reference_number varchar(22) NOT NULL COMMENT ‘The Reference Number associated with this subscription (AuthReqRef).’,
service_id varchar(40) NOT NULL COMMENT ‘The Service Identifier associated with this subscription.’,
service_name varchar(100) NOT NULL COMMENT ‘The name of the service associated with this subscription.’,
price varchar(20) DEFAULT ‘’ COMMENT ‘The price of the service in decimal format.’,
billing_frequency enum(‘’,‘ONCE’,‘DAY’,‘WEEK’,‘MONTH’,‘YEAR’) DEFAULT ‘’ COMMENT ‘The billing frequency of the service associated with this subscription.’,
url varchar(200) DEFAULT ‘’ COMMENT ‘The WAP URL of the service associated with this subscription. Used for backward compatibility.’,
callback varchar(500) DEFAULT ‘’ COMMENT ‘The callback url to execute for the associated subscription. (e.g. call to BillAuth with the status of the subscription)’,
txn_id varchar(38) DEFAULT NULL COMMENT ‘The unique id of the transaction.’,
ext_asset_id varchar(80) DEFAULT NULL COMMENT ‘The external identifier of the service subscription. This identifier will need to be passed to the RAB billing system (as the Hv parameter) to complete billing for the service. This parameter is only populated for ad-hoc requests.’,
audit_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘The timestamp when the transaction happened.’,
co_id varchar(4) DEFAULT NULL COMMENT ‘The CoID of the transaction that failed on RAB.’,
co_key varchar(9) DEFAULT NULL COMMENT ‘The CoKey of the transaction that failed on RAB.’,
customer_sms_id int(10) unsigned DEFAULT NULL COMMENT ‘The customer sms id in the customer_sms table.’,
custom_message_id int(10) unsigned DEFAULT NULL COMMENT ‘The custom message id in the custom_message table.’,
failed_reference_number varchar(22) DEFAULT NULL COMMENT ‘The reference number of the failed transaction (AuthReqRef).’,
PRIMARY KEY (id,audit_date),
KEY idx_msisdn (msisdn),
KEY idx_audit_date (audit_date)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(audit_date))
SUBPARTITION BY HASH ( MONTH(audit_date))
SUBPARTITIONS 12
(PARTITION p2011 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p2012 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION p2013 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION p2014 VALUES LESS THAN (2015) ENGINE = MyISAM,
PARTITION p2015 VALUES LESS THAN (2016) ENGINE = MyISAM,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = MyISAM,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = MyISAM,
PARTITION p2018 VALUES LESS THAN (2019) ENGINE = MyISAM,
PARTITION p2019 VALUES LESS THAN (2020) ENGINE = MyISAM,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = MyISAM) */

The column it’s complaining about is ‘tso_id’.

There are 2 slaves.

4

Hi Carlos,

Any news?

5

Still nothing?

6

Hi,

I verified the error and I’ve created [URL][PT-1570] Percona Toolkit tools fail to detect columns with the word GENERATED as part of the comment - Percona JIRA to track it.

I’ll let you know when it is fixed or if I found any workaround to make it work for you until it gets fixed.

Thanks.

7

The problem is the word generated in the column comment is confusing the table parser and since it believes that column is a GENERATED type column, it is ignoring it.
The workaround is to change the column comment, removing the generated word.

Regards

8

It’s been fixed.
You can download the latest version directly from out GitHub repo: [url]https://raw.githubusercontent.com/percona/percona-toolkit/3.0/bin/pt-archiver[/url]

Regards

9

Thanks very much for your help Carlos.

Regards