[Resolved] varchar (18)

[keat]

Could anyone recommend an sql command that would change the varchar(18) to varchar(30) on all tables which contain the column cart_order_id.

[keat]

Rather than being reactive, is it possible for me to be proactive and edit anything in the upgrade process.

cart_order_id was always varchar =30 in CC3.

In fact it's still varchar 30 in some of the V6 tables, but in other it's varchar 18.

My remaining v3 sites all have at least 20 characters, which is causing me issues when I update.

I still have one large V3 site to update, so making changes before the update would make life easier.

[Dirty Butter]

I'm certainly not knowledgeable, but IF there is no upgrade script in the upgrade scripts of all intervening versions of your next upgrade that deals with cart_order_id, it seems like your varchar change would not be messed up on upgrade.

I only found 3 tables with cart_order_id in it, not counting email_log and email_content_backup. Maybe you have more because of mods/plugins? Otherwise it would be fairly easy to edit the tables and change the varchar on each one.

[keat]

The problem is that characters are being chopped off during the upgrade process, resulting in order summary and order inventory not having the correct order_id, which in turn means that the customers order history doesn't work.

I can get around this with an export and some fancy database editing, but it takes time, effort and prone to human error.

[Dirty Butter]

Have you manually changed to 30 and still have the issue with a test order? If so, that means there is a place in CC code that is creating the order_id to that size. Oh how we miss Bsmither!!

What version are you on?

[bsandall]

Open PHPMyAdmin, select your CubeCart database and open the direct SQL query tab.

Type:

```
SELECT table_name FROM information_schema.columns WHERE column_name='cart_order_id';

```

That will show you all of the tables that have this column. You could also find this by selecting the information_schema 'database' and clicking the 'columns' table.

Next, for each of those tables, you need to run the following query:

```
ALTER TABLE table_name CHANGE COLUMN cart_order_id cart_order_id VARCHAR(30) NOT NULL;

```

You could also change them from within PHPMyAdmin under the `Structure` tab, if you are more comfortable doing it that way.

[Dirty Butter]

I use an old v5 mod that creates sequential order numbers, and I have to edit this section of order.class.php with each upgrade:

	/**
	 * Validate order ID PROBLEM WITH SEQUENTIAL ORDER NUMBER - solved by commenting out this new 6.0.8 section and not using tests in cubecart.class.php
	 *
	 * @param string $order_id
	 * @return bool
	NOTHING GOES HERE
	public static function validOrderId($order_id) {
		if(preg_match('#^[0-9]{6}-[0-9]{6}-[0-9]{4}$#i', $order_id)) {
			return true;
		}
		return false;
	}
 	END OF DELETED SECTION TO MAKE SEQUENTIAL ORDER NUMBERS WORK
 	 */

 

There's also several tweaks in cubecart.class.php that have to do with validating the order number:

			/* 6.1.7 version before Sequential Order tweak
			if (isset($_GET['cart_order_id']) && Order::validOrderId(trim($_GET['cart_order_id']))) {
			*/
			//Sequential Order Number tweak for Lookup Order
	   		if (isset($_GET['cart_order_id'])){
			//end Sequential Order Number tweak for Lookup Order

Maybe you can do something similar to keep your longer id's from being chopped.

[bsandall]

Also, if you are in the process of upgrading an older store, you can manually edit the upgrade scripts before running them to set the varchar limit to whatever you want on those columns.

Likewise, if you are doing a fresh install, you can edit the database install script prior to installing.

[keat]

I wouldn't mind knowing how to modify the upgrade script, this would save me a lot of trouble moving forward.

 

Thanks

 

[keat]

Using Notepad++ and scanning the whole of the cubecart download I find numerous instances of     `cart_order_id` VARCHAR(18)

If I changed these (using notepad++) en mass, to     `cart_order_id` VARCHAR(30) , would this suffice for the update ?

  422 hits in 27 files

sorry, 15 hits in 3 files

[bsandall]

8 hours ago, keat said:

Using Notepad++ and scanning the whole of the cubecart download I find numerous instances of     `cart_order_id` VARCHAR(18)

If I changed these (using notepad++) en mass, to     `cart_order_id` VARCHAR(30) , would this suffice for the update ?

Assuming you are talking about the file in setup/db/install AND you are doing a fresh install, then yes.

You would then need to import your data from your previous CubeCart database, taking care not to overwrite the table structure.

If you are talking about any of the files in setup/db/upgrade, it will work so long as each of those lines is an ALTER TABLE query which, judging by the syntax, I assume they are.

At any rate, changing the 18 to 30 won't hurt anything even if it doesn't work as intended, but it seems like it will.

[keat]

Ill have a play on a mirrored site first.

[keat]

Using Notepadd++ I changed cart_order_id VACHAR (18) to (30) in the downloaed files, uploaded and then ran the upgrade script.

This seemed to work.

[Dirty Butter]

Fantastic!