keat Posted April 26, 2017 Share Posted April 26, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted April 27, 2017 Author Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2017 Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted April 27, 2017 Author Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2017 Share Posted April 27, 2017 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? Quote Link to comment Share on other sites More sharing options...
bsandall Posted April 27, 2017 Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2017 Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
bsandall Posted April 27, 2017 Share Posted April 27, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted April 28, 2017 Author Share Posted April 28, 2017 I wouldn't mind knowing how to modify the upgrade script, this would save me a lot of trouble moving forward.  Thanks  Quote Link to comment Share on other sites More sharing options...
keat Posted April 28, 2017 Author Share Posted April 28, 2017 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 Quote Link to comment Share on other sites More sharing options...
bsandall Posted April 28, 2017 Share Posted April 28, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted April 28, 2017 Author Share Posted April 28, 2017 Ill have a play on a mirrored site first. Quote Link to comment Share on other sites More sharing options...
keat Posted May 2, 2017 Author Share Posted May 2, 2017 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. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted May 2, 2017 Share Posted May 2, 2017 Fantastic! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.