Issue with Authorize.net AIM updating table - error 1366 incorrect integer value

[DrByte]

Observations:

1. Not sure why your site is receiving an empty value ('') as Transaction ID. Would be helpful to see the logged response data. See later below.

2. Even if it is getting an empty value for transaction ID, the PHP code in the module (line 744) checks to see if the transaction_id value is an empty string, and if it's not then it allows it to be put through. Now, it's treating it as "BIGINT(20)", which (in simple terms) is just "a very long integer of up to 20 digits". And, as far as the AIM spec goes, that's exactly what they say to treat it as.
Now, for other gateways that "emulate" the AIM protocol, such as E-processing Network, they've been sending transaction_id values with letters too, and longer than 20 chars. Hence the discussion several posts back to change it away from BIGINT(20) to a VARCHAR (character/string) field.
Now, if you try to put a string, even empty, into a field that's supposed to be INT/BIGINT/TINYINT then it's gonna throw the error you're reporting.
So, if you did change the code to have it handle varchar (and assuming the database allowed the change to take effect (you didn't confirm the actual new structure of the field from phpMyAdmin after making the PHP change)), then even trying to put an empty string into a varchar/string field, would not throw the error you're reporting.

3. Back to line 744 again. If it finds that the transaction_id *is* an empty string value, then line 746 kicks in and sets the value to "NULL", which ZC v155b/v155c will translate into a literal NULL value in the query being sent to the db. And, since the field is configured to accept a null then no error would be thrown.
But the SQL queries you've been quoting don't show it being treated as a null anyway. They all show '' (empty string) as the value it's trying to insert.

So, again, we're back to two problems:
a) it's checking for an empty value, and finding it "not empty", so it's treating it as a string
b) and trying to insert an empty string into a number-only field.


v155c does NOT have the change from BIGINT(20) to VARCHAR(32). Maybe it should have (for the sake of clones like E-Processing merchants), but still the issue remains that something odd is in the data being received as transaction_id.
While changing the code from BIGINT to VARCHAR again should bandage it, the problem still remains that your site isn't firing the null when it should.


It would be helpful to see the actual response coming back from Authorize.net. This can be obtained by turning on Debug Log To File in the AIM module settings. Then do a transaction, and look in the /logs/ folder for the AIM log file/s it generated. You might already have some if the switch is already set to log to file.

Also, making the bigint(20) to varchar(32) change again on lines 761, 763 would be good. Then log into your Admin and click Edit on the AIM module settings (such as to set the debug log setting). This will fire lines 761,763 to make the change in the db. Then look at the transaction_id field in the authorizenet table in your db, using phpMyAdmin, and check whether the change took place in the table's structure.

Armed with the results of these last two items, it may be easier to troubleshoot from this end.

[SPython]

Observations:

1. Not sure why your site is receiving an empty value ('') as Transaction ID. Would be helpful to see the logged response data. See later below.

2. Even if it is getting an empty value for transaction ID, the PHP code in the module (line 744) checks to see if the transaction_id value is an empty string, and if it's not then it allows it to be put through. Now, it's treating it as "BIGINT(20)", which (in simple terms) is just "a very long integer of up to 20 digits". And, as far as the AIM spec goes, that's exactly what they say to treat it as.
Now, for other gateways that "emulate" the AIM protocol, such as E-processing Network, they've been sending transaction_id values with letters too, and longer than 20 chars. Hence the discussion several posts back to change it away from BIGINT(20) to a VARCHAR (character/string) field.
Now, if you try to put a string, even empty, into a field that's supposed to be INT/BIGINT/TINYINT then it's gonna throw the error you're reporting.
So, if you did change the code to have it handle varchar (and assuming the database allowed the change to take effect (you didn't confirm the actual new structure of the field from phpMyAdmin after making the PHP change)), then even trying to put an empty string into a varchar/string field, would not throw the error you're reporting.

3. Back to line 744 again. If it finds that the transaction_id *is* an empty string value, then line 746 kicks in and sets the value to "NULL", which ZC v155b/v155c will translate into a literal NULL value in the query being sent to the db. And, since the field is configured to accept a null then no error would be thrown.
But the SQL queries you've been quoting don't show it being treated as a null anyway. They all show '' (empty string) as the value it's trying to insert.

So, again, we're back to two problems:
a) it's checking for an empty value, and finding it "not empty", so it's treating it as a string
b) and trying to insert an empty string into a number-only field.


v155c does NOT have the change from BIGINT(20) to VARCHAR(32). Maybe it should have (for the sake of clones like E-Processing merchants), but still the issue remains that something odd is in the data being received as transaction_id.
While changing the code from BIGINT to VARCHAR again should bandage it, the problem still remains that your site isn't firing the null when it should.


It would be helpful to see the actual response coming back from Authorize.net. This can be obtained by turning on Debug Log To File in the AIM module settings. Then do a transaction, and look in the /logs/ folder for the AIM log file/s it generated. You might already have some if the switch is already set to log to file.

Also, making the bigint(20) to varchar(32) change again on lines 761, 763 would be good. Then log into your Admin and click Edit on the AIM module settings (such as to set the debug log setting). This will fire lines 761,763 to make the change in the db. Then look at the transaction_id field in the authorizenet table in your db, using phpMyAdmin, and check whether the change took place in the table's structure.

Armed with the results of these last two items, it may be easier to troubleshoot from this end.

I always have debugging turned on. The result of myDEBUG-*.log for the last transaction with STRICT enabled is the same in pertinent respects to my post of November 1st in this thread. I include the AIM_Debug_*.log below. The problem I am now encountering is that I edited authorizenet_aim.php per your instructions substituting varchar(32) for bigint(20) and then updated the module in admin - but mysql continues to show bigint(20) as the transaction id in the table. Restarting the mysql database does not resolve this. What am I missing?

MariaDB [spzcatalog]> show columns from authorizenet;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | int(11) | NO | | 0 | |
| order_id | int(11) | NO | | 0 | |
| response_code | int(1) | NO | | 0 | |
| response_text | varchar(255) | NO | | | |
| authorization_type | varchar(50) | NO | | | |
| transaction_id | bigint(20) | YES | | NULL | |
| sent | longtext | NO | | NULL | |
| received | longtext | NO | | NULL | |
| time | varchar(50) | NO | | | |
| session_id | varchar(255) | NO | | | |
+--------------------+------------------+------+-----+---------+----------------+

AIM_Debug.log:
Dec-27-2016 11:02:56
=================================

Response Code: 1.
Response Text: This transaction has been approved.

Sending to Authorizenet: Array
(
[x_login] => *******
[x_tran_key] => *******
[x_relay_response] => FALSE
[x_delim_data] => TRUE
[x_delim_char] => |
[x_encap_char] => *
[x_version] => 3.1
[x_solution_id] => A1000003
[x_method] => CC
[x_amount] => 0.71
[x_currency_code] => USD
[x_market_type] => 0
[x_card_num] => XXXXXXXXXXXXXXXX6370
[x_exp_date] => ****
[x_card_code] => ****
[x_email_customer] => TRUE
[x_email_merchant] => TRUE
[x_cust_id] => 1
[x_invoice_num] => 106-XcMaPY
[x_first_name] => Harold
[x_last_name] => Pinter
[x_company] => Harold Pinter Movies
[x_address] => 53 Somewhere Street
[x_city] => New York
[x_state] => New York
[x_zip] => 10000
[x_country] => United States
[x_phone] => 212-410-4610
[x_email] => [email protected]
[x_ship_to_first_name] => Harold
[x_ship_to_last_name] => Pinter
[x_ship_to_address] => 53 Somewhere Street
[x_ship_to_city] => New York
[x_ship_to_state] => New York
[x_ship_to_zip] => 10000
[x_ship_to_country] => United States
[x_description] => Deli Pint 16oz (qty: 1)
[x_recurring_billing] => NO
[x_customer_ip] => 73.2.56.325
[x_po_num] => Dec-27-2016 11:02:52
[x_freight] => 0.00
[x_tax_exempt] => FALSE
[x_tax] => 0.06
[x_duty] => 0

[SPython]

Ignore last comment about varchar(32) not showing as the transaction id. I have logged back into the server and now it shows. Odd. I will now enable STRICT_TRANS_TABLES, restart the database, run a transaction and see if I get the same error.

[DrByte]

Two things:

Do you have multiple authorize.net modules enabled?

The log you last posted only shows the request "sent" array, not the "response received".

[SPython]

Two things:

Do you have multiple authorize.net modules enabled?

The log you last posted only shows the request "sent" array, not the "response received".

Yes - just realized my error in pasting. Now with VARCHAR(32) and STRICT_TRANS_TABLES enabled the transaction goes through without error.
Below is the full AIM_Debug.log from this successful transaction:
Dec-28-2016 07:35:03
=================================

Response Code: 1.
Response Text: This transaction has been approved.

Sending to Authorizenet: Array
(
[x_login] => *******
[x_tran_key] => *******
[x_relay_response] => FALSE
[x_delim_data] => TRUE
[x_delim_char] => |
[x_encap_char] => *
[x_version] => 3.1
[x_solution_id] => A1000003
[x_method] => CC
[x_amount] => 0.71
[x_currency_code] => USD
[x_market_type] => 0
[x_card_num] => XXXXXXXXXXXXXXXX7510
[x_exp_date] => ****
[x_card_code] => ****
[x_email_customer] => TRUE
[x_email_merchant] => TRUE
[x_cust_id] => 1
[x_invoice_num] => 107-WbyAoy
[x_first_name] => Harold
[x_last_name] => Pinter
[x_company] => Harold Pinter Movies
[x_address] => 436 East 20th Street
[x_city] => New York
[x_state] => New York
[x_zip] => 10024
[x_country] => United States
[x_phone] => xxxxxxxx
[x_email] => [email protected]
[x_ship_to_first_name] => Harold
[x_ship_to_last_name] => Pinter
[x_ship_to_address] => 436 East 20th Street
[x_ship_to_city] => New York
[x_ship_to_state] => New York
[x_ship_to_zip] => 10024
[x_ship_to_country] => United States
[x_description] => Deli Pint 16oz (qty: 1)
[x_recurring_billing] => NO
[x_customer_ip] => 34.5.96.243
[x_po_num] => Dec-28-2016 07:35:01
[x_freight] => 0.00
[x_tax_exempt] => FALSE
[x_tax] => 0.06
[x_duty] => 0
[x_device_type] => 8
[x_allow_partial_Auth] => FALSE
[Date] => December 28, 2016, 7:35 pm
[IP] => 34.5.96.243
[Session] => ebfiga0n6qtbkvpioerldp8n95
[x_type] => AUTH_CAPTURE
[url] => https://secure2.authorize.net/gateway/transact.dll
)


Results Received back from Authorizenet: Array
(
[0] => Response from gateway
[1] => 1
[2] => 1
[3] => 1
[4] => This transaction has been approved.
[5] => 033514
[6] => Y
[7] => 20333891394
[8] => 107-WbyAoy
[9] => Deli Pint 16oz (qty: 1)
[10] => 0.71
[11] => CC
[12] => auth_capture
[13] => 1
[14] => Harold
[15] => Pinter
[16] => Harold Pinter Movies
[17] => 436 East 20th Street
[18] => New York
[19] => New York
[20] => 10024
[21] => United States
[22] => xxxxxxxxx
[23] =>
[24] => [email protected]
[25] => Harold
[26] => Pinter
[27] =>
[28] => 436 East 20th Street
[29] => New York
[30] => New York
[31] => 10024
[32] => United States
[33] => 0.06
[34] => 0.00
[35] => 0.00
[36] => FALSE
[37] => Dec-28-2016 07:35:01
[38] => 5F02AD93F165BEC6AADA6C207FAC9679
[39] => M
[40] =>
[41] =>
[42] =>
[43] =>
[44] =>
[45] =>
[46] =>
[47] =>
[48] =>
[49] =>
[50] =>
[51] => XXXX7510
[52] => Visa
[53] =>
[54] =>
[55] =>
[56] =>
[57] =>
[58] =>
[59] =>
[60] =>
[61] =>
[62] =>
[63] =>
[64] =>
[65] =>
[66] =>
[67] =>
[68] =>
[69] => December 28, 2016, 7:35 pm
[70] => 34.5.96.243
[71] => ebfiga0n6qtbkvpioerldp8n95
[72] => 04DA7F1E6340B3AE995D15BEB93F85EF5128359E58E747FB70903006B83900BBB01EB749F5B06D02 8D32B457031BF7F3765612B0F04530777A72D2474D83DE40
[Expected-MD5-Hash] => 5F02AD93F165BEC6AADA6C207FAC9679
[HashMatchStatus] => PASS
)


(*snip*)

[SPython]

Two things:

Do you have multiple authorize.net modules enabled?

The log you last posted only shows the request "sent" array, not the "response received".

I posted a full reply but a notice appeared that it needed to be approved by a moderator. I have only one module of Authorizenet enabled - the AIM module. Also with varchar(32) and STRICT_TRANS_TABLE enabled the transaction goes through without error. I included the full authorize aim log in the post awaiting approval by your moderator. (My error in the previous post in pasting a partial log.)

[DrByte]

Curious. The transaction ID there is '20333891394'.

[SPython]

Curious. The transaction ID there is '20333891394'.

Excuse my ignorance - what does that signify?

Apropos - I now seem to have a new problem. When I login to ZenCart Admin the Payment Modules are not listed. I get:

Payment Modules
WARNING: An Error occurred, please refresh the page and try again.
Modules Sort Order Orders Status Action

Refreshing the browser, deleting the cache, logging in and out of the desktop does not help. The files are all there on the server and the permissions have not changed. I do not see that editing a couple of lines in one file would cause this. Any idea?