How to fix the «Unknown column 'is_visible' in 'where clause'» failure on the «Stored Payment Methods» click inside a customer's account?

Issues
, , , ,
1

/vault/cards/listaction/

Column not found: 1054 Unknown column ‘is_visible’ in ‘where clause’, query was: SELECT main_table.* FROM vault_payment_token AS main_table WHERE ((customer_id = ‘1’)) AND ((is_visible = 1)) AND ((is_active = 1)) AND ((expires_at > ‘2017-06-05 00:00:00’))

0 vendor\magento\zendframework1\library\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
1 lib\internal\Magento\Framework\DB\Statement\Pdo\Mysql.php(93): Zend_Db_Statement_Pdo->_execute(Array)
2 vendor\magento\zendframework1\library\Zend\Db\Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
3 vendor\magento\zendframework1\library\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
4 vendor\magento\zendframework1\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
5 lib\internal\Magento\Framework\DB\Adapter\Pdo\Mysql.php(515): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
6 lib\internal\Magento\Framework\DB\Adapter\Pdo\Mysql.php(578): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `main_ta...', Array)
7 vendor\magento\zendframework1\library\Zend\Db\Adapter\Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
8 lib\internal\Magento\Framework\Data\Collection\Db\FetchStrategy\Query.php(21): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array)
9 lib\internal\Magento\Framework\Data\Collection\AbstractDb.php(774): Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(Object(Magento\Framework\DB\Select), Array)
10 lib\internal\Magento\Framework\Data\Collection\AbstractDb.php(670): Magento\Framework\Data\Collection\AbstractDb->_fetchAll(Object(Magento\Framework\DB\Select))
11 lib\internal\Magento\Framework\Data\Collection\AbstractDb.php(574): Magento\Framework\Data\Collection\AbstractDb->getData()
12 lib\internal\Magento\Framework\Data\Collection\AbstractDb.php(559): Magento\Framework\Data\Collection\AbstractDb->loadWithFilter(false, false)
13 lib\internal\Magento\Framework\Data\Collection.php(331): Magento\Framework\Data\Collection\AbstractDb->load()
14 app\code\Magento\Vault\Model\PaymentTokenRepository.php(99): Magento\Framework\Data\Collection->getItems()
15 app\code\Magento\Vault\Model\PaymentTokenManagement.php(163): Magento\Vault\Model\PaymentTokenRepository->getList(Object(Magento\Framework\Api\SearchCriteria))
16 app\code\Magento\Vault\Model\CustomerTokenManagement.php(50): Magento\Vault\Model\PaymentTokenManagement->getVisibleAvailableTokens('1')
17 app\code\Magento\Vault\Block\Customer\PaymentTokens.php(96): Magento\Vault\Model\CustomerTokenManagement->getCustomerSessionTokens()
18 app\code\Magento\Vault\Block\Customer\PaymentTokens.php(56): Magento\Vault\Block\Customer\PaymentTokens->getCustomerTokens()
19 generated\code\Magento\Vault\Block\Customer\CreditCards\Interceptor.php(37): Magento\Vault\Block\Customer\PaymentTokens->getPaymentTokens()
20 app\code\Magento\Vault\view\frontend\templates\cards_list.phtml(9): Magento\Vault\Block\Customer\CreditCards\Interceptor->getPaymentTokens()
2

The bug is caused by this low quality commit:

https://github.com/magento/magento2/commit/5803a7589ed8d4d0c835cbd4fe607d58810bc907#diff-af526e86068169abb7aa423d6120679b

It adds the is_visible column to the vault_payment_token table straight in the InstallSchema script instead on the right implementation through the UpgradeSchema script.

Magento 2 company should teach its employees how to change the database schema in the proper way.

3

How to fix

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

DROP TABLE IF EXISTS `vault_payment_token`;
CREATE TABLE `vault_payment_token` (
  `entity_id` int(10) UNSIGNED NOT NULL COMMENT 'Entity Id',
  `customer_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Customer Id',
  `public_hash` varchar(128) NOT NULL COMMENT 'Hash code for using on frontend',
  `payment_method_code` varchar(128) NOT NULL COMMENT 'Payment method code',
  `type` varchar(128) NOT NULL COMMENT 'Type',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `expires_at` timestamp NULL DEFAULT NULL COMMENT 'Expires At',
  `gateway_token` varchar(255) NOT NULL COMMENT 'Gateway Token',
  `details` text COMMENT 'Details',
  `is_active` tinyint(1) NOT NULL COMMENT 'Is active flag',
  `is_visible` tinyint(1) NOT NULL COMMENT 'Is visible flag'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Vault tokens of payment';

DROP TABLE IF EXISTS `vault_payment_token_order_payment_link`;
CREATE TABLE `vault_payment_token_order_payment_link` (
  `order_payment_id` int(10) UNSIGNED NOT NULL COMMENT 'Order payment Id',
  `payment_token_id` int(10) UNSIGNED NOT NULL COMMENT 'Payment token Id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order payments to vault token';

ALTER TABLE `vault_payment_token`
  ADD PRIMARY KEY (`entity_id`),
  ADD UNIQUE KEY `VAULT_PAYMENT_TOKEN_HASH_UNIQUE_INDEX_PUBLIC_HASH` (`public_hash`),
  ADD UNIQUE KEY `UNQ_54DCE14AEAEA03B587F9EF723EB10A10` (`payment_method_code`,`customer_id`,`gateway_token`),
  ADD KEY `VAULT_PAYMENT_TOKEN_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` (`customer_id`);

ALTER TABLE `vault_payment_token_order_payment_link`
  ADD PRIMARY KEY (`order_payment_id`,`payment_token_id`),
  ADD KEY `FK_4ED894655446D385894580BECA993862` (`payment_token_id`);

ALTER TABLE `vault_payment_token`
  MODIFY `entity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity Id';

ALTER TABLE `vault_payment_token`
  ADD CONSTRAINT `VAULT_PAYMENT_TOKEN_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE;

ALTER TABLE `vault_payment_token_order_payment_link`
  ADD CONSTRAINT `FK_4ED894655446D385894580BECA993862` FOREIGN KEY (`payment_token_id`) REFERENCES `vault_payment_token` (`entity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_CF37B9D854256534BE23C818F6291CA2` FOREIGN KEY (`order_payment_id`) REFERENCES `sales_order_payment` (`entity_id`) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
4

See also: