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).

Quote:

---

Originally Posted by 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???

Quote:

---

Originally Posted by 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.