Data truncated for column 'transaction_id' at row 1 - eprocessing

TLS 1.2 forced a change in hosting and we all love migrations...

Godaddy is the host and the Installtron tool was used to migrate from classic to Cpanel (linux). Original site was 1.5.1 on MySQL 5.5.43-37.2-log PHP 5.2

New site is 1.5.1 on MySQL 5.6.30-cll-lve PHP 5.6

Site appears to function correctly until a sale confirmation is attempted via authorizenet AIM
The Following error occurs:

PHP Fatal error: 1265:Data truncated for column 'transaction_id' at row 1 :: insert into zc_authorizenet (id, customer_id, order_id, response_code, response_text, authorization_type, transaction_id, sent, received, time, session_id) values (NULL, 1, 5, 1, 'This transaction has been approved.', 'AUTH_CAPTURE', '20161015231255-01141078-519', 'Array\n( XXXXXALL THE NORMAL STUFFXXXXX ) in REMOVED REAL PATH /includes/classes/db/mysql/query_factory.php on line 120

transaction_id is set to BIGINT NULL and matches previous/old site. Running same insert on old database is successful.

Any ideas?

Observations:

A. The error you posted indicates a transaction from customer #1, and order #5. That suggests to me that this is an empty database, and not the database you "migrated" from the previous server.
Is that correct?

B. '20161015231255-01141078-519' is not an integer or big-int value. It's a string. The hyphens make it no longer just a number.
Maybe Authorize.net is returning very different values for you?

C. Your MySQL version changed from 5.5 to 5.6, and probably has a strict mode enabled which no longer just ignores values that are too long (previously it probably was just chopping off whatever was too long). The actual data in your old database will reveal whether it's been missing the storing of all the digits (and the added non-int hyphens).

DrByte:

Observations:

A. The error you posted indicates a transaction from customer #1, and order #5. That suggests to me that this is an empty database, and not the database you "migrated" from the previous server.
Is that correct?

B. '20161015231255-01141078-519' is not an integer or big-int value. It's a string. The hyphens make it no longer just a number.
Maybe Authorize.net is returning very different values for you?

C. Your MySQL version changed from 5.5 to 5.6, and probably has a strict mode enabled which no longer just ignores values that are too long (previously it probably was just chopping off whatever was too long). The actual data in your old database will reveal whether it's been missing the storing of all the digits (and the added non-int hyphens).

A. Started with the same DB, but cleared the records to see if there was something bad in the imported data. Reusing the same query for testing thus the #5.

B. Yes, since that was the same data I get back on the original system it was confusing how BIGINT was ingesting. Data from the original system shows it is truncated at the first hypen.

C. That was my next check was strict based on other research today. I will try that now and let you know.

Thanks.

Well, it is a strict mode issue and Godaddy won't allow shared servers to be set without it. Where, besides the authorizenet table, will that value need to be modified? Changing it to anything else prevents the edit function in orders from displaying.

1265: Data truncated for column 'transaction_id' at row 26 :: ALTER TABLE zc_authorizenet CHANGE transaction_id transaction_id bigint(20) default NULL

Correct error message;

1265: Data truncated for column 'transaction_id' at row 1 :: ALTER TABLE zc_authorizenet CHANGE transaction_id transaction_id bigint(20) default NULL

some other observations:

1. i have no idea where you got that insert string in you OP. everything that I am seeing suggests transaction_id is a bigInt data type. so something seems adrift with that insert statement.
2. if you were to turn OFF the storage of authorize.net data, that first error would now disappear as you would now not be storing that data.
3. your later errors suggest to me, that you do have data in the table. data getting truncated at row 1 indicates data ia already there, and why if the data structure is already bigInt would it now be getting truncated? that makes no sense. are you sure the table is empty? and that the field is currently set to bigInt(20)?
4. if you are convinced that the insert statement is correct, and you want to continue storing the data, it strikes me you would have to do a couple of things. both are in the authorizenet payment module. you would have to change the function tableCheckup in 2 spots, changing bigint(20) to varchar(32) and then you would need to change the insert statement. although in looking at the insert statement, it is already getting bound as a string, so you would not need to do anything there.

good luck!

What's even more interesting is that I was just looking at a site running v1.5.5 that's using the AIM module with MySQL 5.6.33 and it's neither throwing those errors, nor getting numbers longer than 10 digits, even though the field is set to allow for 20.

So another puzzle piece is to determine why you're getting transaction_id values of '20161015231255-01141078-519' when it's documented to get up to 20 digits and yet you're getting 27 including two hyphens.

dr b
totally concur! exactly what i'm saying in my point 1 above

Umm, another observation: ZC 1.5.1 and PHP 5.6???

carlwhat:

some other observations:

1. i have no idea where you got that insert string in you OP. everything that I am seeing suggests transaction_id is a bigInt data type. so something seems adrift with that insert statement.
2. if you were to turn OFF the storage of authorize.net data, that first error would now disappear as you would now not be storing that data.
3. your later errors suggest to me, that you do have data in the table. data getting truncated at row 1 indicates data ia already there, and why if the data structure is already bigInt would it now be getting truncated? that makes no sense. are you sure the table is empty? and that the field is currently set to bigInt(20)?
4. if you are convinced that the insert statement is correct, and you want to continue storing the data, it strikes me you would have to do a couple of things. both are in the authorizenet payment module. you would have to change the function tableCheckup in 2 spots, changing bigint(20) to varchar(32) and then you would need to change the insert statement. although in looking at the insert statement, it is already getting bound as a string, so you would not need to do anything there.

good luck!

That is what is coming from the authorizenet module and showing up it the error log. It is supposed to be BIGINT data but that is what comes back from eprocessing's authorizenet emulator.
2. Was hoping to avoid turning it off as it does provide me the ability to refund or void transactions easier by storing it.
3. I started seeing this with known good transactions already in the table. I have gone back to another one of my carts with a ton of transactions and see the same data. Field is set to BIGINT.
4. Tried that yesterday, but have other tables collapsing because of it. Payment modules won't even open up with varchar in that table. Trying to find where that is being called at to figure out what it is doing.

eproccesing authorize net emulator. That is the format that has always come back from them. I went back a year in another cart and see the same returns. The database has been truncating it all the while with no complaints.

mc12345678:

Umm, another observation: ZC 1.5.1 and PHP 5.6???

Is there some issue with that combo?

it still makes no sense.

i have no idea what this eprocessing emulator is. and whether it is modifying the field.

if it has been truncated as you say all this time, how could you possibly issue a void or a refund? you do not have the complete id therefore authorize.net should error out when you send the truncated id.

this problem is solvable. try issuing a void with the full id v the truncated id. let's look at the responses.

finally if you login to your authorize.net dashboard you should be able to view your transactions. what is the value of the id there?

good luck.

IMMQ:

Is there some issue with that combo?
Server requirements

Thing is, supposedly there would be one or more problems before getting to this checkout stage, so there may have already been some assumptions made that the store has been modified sufficiently to work with PHP 5.6...

IMMQ:

eproccesing authorize net emulator.
Okay, so your AIM module isn't actually using the Authorize.net spec. It's connecting to the E-Processing gateway, which is emulating AIM, but with its own alterations, which don't totally adhere to the actual AIM specification.
Lovely.

So, ya, because of how your gateway works, you'll need to change your authorizenet_aim module to not force it to be a bigint(20) field. Something like varchar(32) as was suggested earlier would do.

(And for the sake of future posts, it would help to have known you were using e-processing instead of actual Authorize.net.)

DrByte:

Okay, so your AIM module isn't actually using the Authorize.net spec. It's connecting to the E-Processing gateway, which is emulating AIM, but with its own alterations, which don't totally adhere to the actual AIM specification.
Lovely.

So, ya, because of how your gateway works, you'll need to change your authorizenet_aim module to not force it to be a bigint(20) field. Something like varchar(32) as was suggested earlier would do.

(And for the sake of future posts, it would help to have known you were using e-processing instead of actual Authorize.net.)

What is nuts is that this has worked fine for several years. Godaddy's choice to not put TLS 1.2 on their classic hosting servers means a lot of work still for me.

Sorry about that, too close to the problem and forgot that tidbit about eprocessing.

Thanks for all the help. For now storage is off until I can track down everything that touches that field as bigint. At least when I move over the big store I will know exactly what has to be modded.

IMMQ:

For now storage is off until I can track down everything that touches that field as bigint.
Just that one section that carlwhat mentioned earlier.

  function tableCheckup() {
    global $db, $sniffer;
    $fieldOkay1 = (method_exists($sniffer, 'field_type')) ? $sniffer->field_type(TABLE_AUTHORIZENET, 'transaction_id', 'bigint(20)', true) : -1;
    if ($fieldOkay1 !== true) {
      $db->Execute("ALTER TABLE " . TABLE_AUTHORIZENET . " CHANGE transaction_id transaction_id bigint(20) default NULL");
    }
  }

change both "bigint(20)" to "varchar(32)"

DrByte:

Just that one section that carlwhat mentioned earlier.

function tableCheckup() {
global $db, $sniffer;
$fieldOkay1 = (method_exists($sniffer, 'field_type')) ? $sniffer->field_type(TABLE_AUTHORIZENET, 'transaction_id', 'bigint(20)', true) : -1;
if ($fieldOkay1 !== true) {
$db->Execute("ALTER TABLE " . TABLE_AUTHORIZENET . " CHANGE transaction_id transaction_id bigint(20) default NULL");
}
}

> change both "bigint(20)" to "varchar(32)"

FYI: this change is built-in to v1.5.5d, released yesterday.