Just a note for anyone looking to upgrade their Moneris-HPP installation to use a more recent version of MySQL. The file Moneris.sql, besides the double-declaration mentioned above:
DROP TABLE IF EXISTS `moneris_orderids`;
CREATE TABLE IF NOT EXISTS `moneris_orderids` (
`moneris_orderid` varchar(99) NOT NULL,
`orders_id` int(11) NOT NULL,
`trans_name` varchar(20) NOT NULL,
`bank_approval_code` varchar(8) NOT NUL L,
[B] `bank_transaction_id` int(18) NOT NULL,[/B]
`response_code` varchar(3) NOT NULL,
`iso_code` int(2) NOT NULL,
`message` varchar(100) NOT NULL,
`cardholder` varchar(40) NOT NULL,
`response_variables` blob NOT NULL,
[B] `bank_transaction_id` int(10) NOT NULL,[/B]
`f4l4` varchar(11) NOT NULL,
PRIMARY KEY (`orders_id`,`moneris_orderid`),
KEY `idx_moneris_orderids_orders_id` (`orders_id`)
);
```
also defines the bank_transaction_id as an integer value. Unfortunately, MySQL integer values can range from +/- 2147483647, but those values come back as 18-character values.
When you try to stuff those values into that integer-defined field for those more recent versions of MySQL, you might receive error logs similar to
PHP Fatal error: 1264:Out of range value for column 'bank_transaction_id' at row 1
You'll also notice (viewing the moneris_orderids table) that all the bank_transaction_id values are 2147483647!
The correction is to define that field as either **char(18)** or, for some expansion, **varchar(20)**. You can safely make the field format change in phpMyAdmin, since that value is recorded but never used.
