[PT-1570] pt-archiver fails to detect columns with the word GENERATED as part of the comment Created: 14/Jun/18  Updated: 26/Jun/18  Resolved: 14/Jun/18

Status: Done
Project: Percona Toolkit
Component/s: None
Affects Version/s: 3.0.10
Fix Version/s: 3.0.11

Type: Bug Priority: Medium
Reporter: Carlos Salguero Assignee: Carlos Salguero
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: 0 minutes
Time Spent: 4 hours
Original Estimate: Not Specified


 Description   

Forum question originating this issue: https://www.percona.com/forums/questions-discussions/percona-toolkit/51592-pt-archiver-giving-error-the-following-columns-exist-in-dest-but-not-source

How to reproduce:
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) */

Command:

pt-archiver --source h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=doubleoptin_audit --dest=h=127.0.0.1,P=5639,u=msandbox,p=msandbox,D=test,t=doubleoptin_audit --where " 1=1 "

Output

The following columns exist in --dest but not --source: tso_id

Both tables have the tso_id but pt-archiver is not detecting that column in the source table.



 Comments   
Comment by Carlos Salguero [ 26/Jun/18 ]

The problem was that TableParser.pm was confusing the word GENERATED inside a column comment, as part of the column definition

Generated at Mon Jul 16 21:47:52 UTC 2018 using JIRA 7.3.6#73017-sha1:51437cf70ba5689aadb808c1cc05a46d676f5739.