[Resolved] 6.1.3 Upgrade script MySQL Index change problem

[Dirty Butter]

I always upgrade manually, which means running the queries in MySQL.

I get the error message ./error.html on an otherwise blank page when I try to run the INDEX changes:

ALTER TABLE `CubeCart_alt_shipping` DROP INDEX `id`, ADD PRIMARY KEY (`id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_alt_shipping_prices` DROP INDEX `id`, ADD PRIMARY KEY (`id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_category_language` DROP INDEX `id`, ADD PRIMARY KEY (`translation_id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_downloads` DROP INDEX `id`, ADD PRIMARY KEY (`digital_id`) USING BTREE;
ALTER TABLE `CubeCart_inventory_language` DROP INDEX `id`, ADD PRIMARY KEY (`translation_id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_modules` DROP INDEX `module_id`, ADD PRIMARY KEY (`module_id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_filemanager` ADD INDEX(`filepath`); #EOQ
ALTER TABLE `CubeCart_filemanager` ADD INDEX(`filename`); #EOQ

The rest of the changes in 6.1.2.sql work properly.

I don't have a clue how to make these changes manually in the database.

[bsmither]

You can copy/paste the entire thing in admin, Maintenance, Database Query tab.

Be sure to prepend any table prefix you may have.

"I get the error message ./error.html on an otherwise blank page"

I don't know what that means.

[bsandall]

Are you running the MySQL queries via phpMyAdmin? That's what I did, and while I got errors due to the fact that those indexes didn't exist in my tables (I may have already ran those particular queries), I didn't get blank page.

I would simply check the indexes for each of those tables:

SHOW INDEX FROM `CubeCart_alt_shipping`

That will list all existing indexes for that table, and you can compare them to what is expected based on the SQL queries above. Do that for each affected table.

[Dirty Butter]

1 hour ago, bsmither said:

"I get the error message ./error.html on an otherwise blank page"

I don't know what that means.

This shows in the top left corner in tiny print when I run the query from the Maintenance tab.

/error.html

Nothing else shows - no CC code at all.

I do not use a prefix

I think I figured out how to edit the tables - was successful with our test site, anyway. Fingers crossed as I attempt it on live sites.

Our database was first created in v3 something, if that has anything to do with it.

 

[Dirty Butter]

Do I have the structure correct for order_notes?

[bsandall]

It's hard to say what Al intended - in the install script, the table is created with a combined key named 'admin_id' on three columns: 'admin_id', 'cart_order_id', and 'time'.

However, in the upgrade script, the 'time' column is dropped, re-added, and given its own index.

So, my table has 'admin_id' on columns 'admin_id' + 'cart_order_id', and does NOT have the individual 'cart_order_id' index, but is otherwise the same as yours.

It's not likely to have a huge impact either way, but if you ran the actual scripts as they are written, I believe you should have ended up with what I have.

[Dirty Butter]

I did TRY to run them as written - they just didn't all work for me. I'll fiddle with mine some more.

[bsandall]

I didn't mean to imply you didn't - I did as well and ran into some issues.

Personally I wouldn't worry about the indexes on this table, but if you want them to match:

ALTER TABLE `CubeCart_order_notes` DROP INDEX `admin_id`;
ALTER TABLE `CubeCart_order_notes` DROP INDEX `cart_order_id`;
ALTER TABLE `CubeCart_order_notes` ADD INDEX `admin_id` (`admin_id`, `cart_order_id`);

Or, if you'd prefer to have your table structure match the current install script:

ALTER TABLE `CubeCart_order_notes` DROP INDEX `admin_id`;
ALTER TABLE `CubeCart_order_notes` DROP INDEX `cart_order_id`;
ALTER TABLE `CubeCart_order_notes` DROP INDEX `time`;
ALTER TABLE `CubeCart_order_notes` ADD INDEX `admin_id` (`admin_id`, `cart_order_id`, `time`);

 

[Dirty Butter]

Well somewhere along the way I messed up and lost all my times for order_notes. I have a backup, so I used that. The times are back in cPanel, but in admin they all show 12/31/1969. Is the problem in this 6.1.1 version table or more likely something wrong with the admin/source code?

[bsandall]

The problem is CubeCart_order_notes no longer has a TIMESTAMP column - `time` is stored as a simple integer, whereas your database backup still has the old timestamp format. You can convert TIMESTAMP into INT by running a portion of the 6.1.2.sql upgrade script:

# add a temporary column for the conversion:
ALTER TABLE `CubeCart_order_notes` ADD `time_tmp` INT(11) NOT NULL AFTER `time`; #EOQ

# set the data in that column to the INT version of each existing TIMESTAMP:
UPDATE `CubeCart_order_notes` SET `time_tmp` = UNIX_TIMESTAMP(`time`); #EOQ

# Drop the old column and rename the new one:
ALTER TABLE `CubeCart_order_notes` DROP `time`; #EOQ
ALTER TABLE `CubeCart_order_notes` CHANGE `time_tmp` `time` INT(11) UNSIGNED NOT NULL; #EOQ

# This is the part we discussed before where the it adds the incorrect index
# Instead of running this query, you may want to run the one I wrote earlier.
# ALTER TABLE `CubeCart_order_notes` ADD INDEX( `time`); #EOQ
# i.e.:
#    ALTER TABLE `CubeCart_order_notes` DROP INDEX `admin_id`;
# followed by either:
#    ALTER TABLE `CubeCart_order_notes` ADD INDEX `admin_id` (`admin_id`, `cart_order_id`);
# or:
#    ALTER TABLE `CubeCart_order_notes` ADD INDEX `admin_id` (`admin_id`, `cart_order_id`, `time`);

 

[Dirty Butter]

Thanks!! It's back to normal now with the times showing on all but a few that came in between the backup and live databases. I really appreciate your help @bsandall!!