Is authorize.net transaction id large enough?

I just received an email from authorized.net that says...

"
The transaction ID, or x_trans_id, is specified as a 10-digit integer. If you use the transaction ID in any of your own applications or databases, it is critical that you verify that your system is architected to accept a value that exceeds 2,147,483,647. Failure to accommodate values larger than 2,147,483,647 will result in your system's inability to accept Authorize.Net transactions.
** ...**
If you need to make updates to your transaction ID architecture, you must do so prior to September 1, 2008.
"

I don't know if they are sending us this message now because they changed the length of transaction id or something else. But does anyone know if this is a problem or not if I'm using ZenCart's Authorize.net payment module?

From my testing on MySQL 4.1 and 5.0, Zen Cart v1.3.8a users will not witness any obvious ill effects as a result of change to longer Transaction IDs.

It is true that the data stored in the "authorizenet" table, which archives details of historical transactions, will not properly accommodate the longer lengths; however, it seems that the only side-effect is that the number gets stored literally as 2147483647. While that makes that particular value meaningless if it's supposed to be a higher value, at least it will not cause interruptions in customer shopping experiences. Additionally, since the Transaction ID value is stored elsewhere as a text-only comment in a text field, the location where the store administrator would actually see the Transaction ID for purposes of refunds etc will not be adversely affected.

For those who desire it, the quick solution to allow the full transaction_id to be stored in the authorizenet table is to simply run this SQL command on their database:```
ALTER TABLE authorizenet CHANGE COLUMN transaction_id transaction_id varchar(20) NOT NULL default '';

Zen Cart versions newer than v1.3.8a will include this fix.

Thanks for the info. I've applied the sql patch and it works.

DrByte:

For those who desire it, the quick solution to allow the full transaction_id to be stored in the authorizenet table is to simply run this SQL command on their database:```
ALTER TABLE authorizenet CHANGE COLUMN transaction_id transaction_id varchar(20) NOT NULL default '';

> Zen Cart versions newer than v1.3.8a will include this fix
Why VARCHAR(20)? Why not BIGINT (or, better yet, BIGINT UNSIGNED)? Only eight bytes, remains an integer, more efficient both in storage space and CPU time. And it will take a *long* time to reach BIGINT, let alone BIGINT UNSIGNED, capacity (even signed BIGINT is big enough to hold the estimated number of total subatomic particles in the Universe, with room to spare)!

BIGINT has been around since at least MySQL 3, and so is definitely in both 4.1 and 5.x.

DrByte:

From my testing on MySQL 4.1 and 5.0, Zen Cart v1.3.8a users will not witness any obvious ill effects as a result of change to longer Transaction IDs.
Hello! Am I right that this statement is applicable also to v1.3.7?

how about for us old zencart users? Will we see ill effects? I'm stuck on version 1.25 because of the customization done to my site. To apply the patch is easy but does it help the older users as well? Thanks in advance!

Yes, you can apply the same SQL change to prior versions.

Hi there - I'm not a programmer, so apologies for my probably clueless-sounding queries:

• I have Zen Cart 1.3.7. Do I need to apply the SQL patch, or will 1.3.7 continue to run just fine after authorize.net's changes?

• If I need to apply the SQL patch... how do I do that?

Thanks!

CKlemow:

• I have Zen Cart 1.3.7. Do I need to apply the SQL patch, or will 1.3.7 continue to run just fine after authorize.net's changes?

• If I need to apply the SQL patch... how do I do that?

Thanks!

If you are not using Authorize.net as payment processor then you do not require the SQL patch regardless of what version you are at.

Since the database field is too narrow in version 1.3.8, it would most likely be too narrow in version 1.3.7 as well -- and hence the patch is applicable.

There would be no harm by applying the patch even when you do not need it. Should you decide to apply the SQL patch, you do so by ...

1. Log into admin interface.
2. Do a backup of your database using phpmyadmin or the ZenCart Database Backup Plugin as described here
3. Go to Tools -> Install SQL Patch.
4. Paste DrByte's SQL statement mention in the thread above.
5. Click Send.

Thanks! We are indeed using authorize.net. Their email is what sent me scurrying over here to check.

Just followed your instructions and I THINK I did it, but I don't know how to tell if'n it worked. I clicked on send, and a moment later, above the box, i got a message saying "Query Results," with a copy of the patch text underneath.

I also noticed afterwards that below the box, there was this warning in red:

"NOTE: Zen Cart database-upgrade scripts should NOT be run from this page. Please upload the new zc_install folder and run the upgrade from there instead for better reliability."

(The warning is there whenever I go back to the page, so I guess it was always there; I just didn't notice it 'cause I was just following the instructions by rote.)

That approach is fine.

DrByte:

That approach is fine.

Thanks!

JoelMMCC:

Why VARCHAR(20)? Why not BIGINT (or, better yet, BIGINT UNSIGNED)? Only eight bytes, remains an integer, more efficient both in storage space and CPU time. And it will take a long time to reach BIGINT, let alone BIGINT UNSIGNED, capacity (even signed BIGINT is big enough to hold the estimated number of total subatomic particles in the Universe, with room to spare)!

I too am concerned that you went from an 'int' to a 'varchar', to store integer data. Can you expound on why that choice? Have you set in stone what type of field will be in v1.4? I'm concerned what will happen when I upgrade if v1.4's is not a matching type of field.

Is it best to run this SQL command from the Zen Cart admin or from phpMyAdmin at the host account admin level

Either is fine.

Just to clarify the advice here some more:

There is another way to make the required column change, which many users may find simpler than DrByte's excellent SQL query, if you have phpMyAdmin available for your server.

Most *nix / Apache Web hosts provide you with phpMyAdmin. If your site uses cPanel as a control panel, you will probably find phpMyAdmin listed under the MySQL / databases section, next to the tools to create a MySQL database.

You can also download phpMyAdmin from http://www.phpmyadmin.net/home_page/downloads.php

The phpMyAdmin steps:

1. Log into phpMyAdmin
2. Select your ZenCart database from the left-hand menu. If there is a link with your database's name, click that link; if there is a pull-down menu, select your database name. If you have done this correctly, you will see a list of table names appear.
3. Click the link for your authorizenet table; again, if you used the default ZenCart settings for your install, the table will be named zen_authorizenet.
4. The right-hand pane now shows details for the Authorize.net table. If it isn't already selected, click the Structure tab in the right-hand pane.
5. Find the column named transaction_id and click the pencil icon to its right. The right-hand page changes to a form.
6. Find the pull-down menu next to Type: and choose BIGINT.
7. If there is a number in the Length / Values textbox, remove that value, so the box is blank.
8. Leave all other boxes and pull-downs as they are.
9. Click the Save button.

This accomplishes the required change.

If you choose to use the excellent SQL query provided by DrByte, you may need to modify it if you specified a table prefix, such as zen_, when you installed ZenCart.

If you used the "default" install of ZenCart, it probably appended the zen_ prefix to your tables. Or, you may have specified a different table prefix. If so, the updated SQL query is:

ALTER TABLE zen_authorizenet MODIFY transaction_id BIGINT NOT NULL;

Where "zen_" needs to be changed to whatever table prefix you used, if you used a different table prefix in your install.

Please note that my query is not exactly the same as DrByte's but it accomplishes the same task.

At the risk of offending DrByte -- which is not my intention, and my apologies to him if he takes offense -- I want to remove the concerns voiced by some about the change from INT to VARCHAR in his query.

The executive summary is, using VARCHAR to store the Transaction ID is fine from both Authorize.net's view and ZenCart's view.

The larger discussion:

When Authorize.net processes a credit card transaction, it assigns a numeric Transaction ID to the item as a way to uniquely identify the transaction in its systems.

Because of the way databases work, it is convenient for Authorize.net to generate a Transaction ID that is numeric. That number serves as a unique key -- a value in the database that is only associated with that transaction, so any time they ask for that Transaction ID, they know they are getting information related only to that transaction.

However, ZenCart generates its own unique keys to identify your orders. It doesn't need to use the Authorize.net Transaction ID to identify your orders.

ZenCart just stores the Transaction ID as a courtesy to you, so that you can more easily identify which transactions relate to which orders when you work in the Authorize.net and ZenCart administration areas.

Because ZenCart is just storing the Transaction ID for you, and not working with it as a unique key, the format in which the Transaction ID is stored doesn't matter, from a programming standpoint.

I can't say why ZenCart decided to store these Transaction IDs as signed integers, except that because Authorize.net was generating Transaction IDs as a signed INT(10), they would store them as a signed INT(10).

DrByte's approach in converting the signed INT(10) to a VARCHAR(20) basically says, "Since ZenCart doesn't actually use the Transaction ID as a key; since ZenCart basically treats the transaction_id column as a string; since a VARCHAR(20) field can hold a plenty-big number that Authorize.net won't hit for a good, long time; and since using a VARCHAR field is going to help me overcome data-collision errors if, for example, Authorize.net sends a garbled or unexpected Transaction ID some day, why not use VARCHAR instead of INT?"

It's an eminently reasonable question and an entirely acceptable approach.

I have stuck with using a BIGINT rather than a VARCHAR because some day, there may be a need for ZenCart to work with that column as a numeric value; however, absent any indication of that being the case, DrByte's VARCHAR conversion is fine.

I went into phpMyAdmin to change the table structure and noticed that this table is empty. Is there a setting that I need to change so that the Authorize.net transaction id is recorded?

Thanks, Marianne

Everytime I try & install the SQL patch I crash the server. Is there any other way I can get this Authorize.net thing to work again. here is my ZenCart version information...

Zen Cart 1.3.0.2
Database Patch Level: 1.3.8
v1.3.7 [2008-09-16 16:50:53] (Version Update 1.3.6->1.3.7)
v1.3.6 [2008-09-16 16:50:32] (Version Update 1.3.5->1.3.6)
v1.3.5 [2008-09-16 16:50:17] (Version Update 1.3.0.2->1.3.5)
v1.3.0.1 [2006-07-22 02:06:43] (Version Update 1.3.0->1.3.0.1)
v1.3.0.2 [2006-07-22 02:06:43] (Version Update 1.3.0.1->1.3.0.2)
v1.3.0.1 [2006-05-07 13:34:32] (Fresh Installation)
v1.3.0.1 [2006-05-07 13:34:32] (Fresh Installation)