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

Percona Toolkit tools fail to detect columns with the word GENERATED as part of the comment

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Fixed
    • 3.0.10
    • 3.0.11
    • None

    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.

      Attachments

        Issue Links

          Activity

            People

              carlos.salguero Carlos Salguero (Inactive)
              carlos.salguero Carlos Salguero (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 hours
                  4h

                  Smart Checklist