UTF8 tables

[keat]

One of my recently updated carts has a number of UTF8 tables in the database.

What are these, and are they safe to remove ?

[Al Brookbanks]

That indicates quite a significant failure on upgrade. 

[keat]

 

Site appears to work OK, although i've not put it in to production yet

[keat]

I've restored a copy of the V3 and am running the upgrade to 6.1.5 again.

The site was reporting it's self as 3.0.18 however, I've no idea how accurate this is, as all these V3's are hybrids.

Could that make a difference ?

 

[Al Brookbanks]

If you want to submit a support ticket I can take a look for you. I don't know what the problem is without looking deeper. A modified database shouldn't cause an upgrade problem so long as existing columns haven't changed and only new tables and columns have been added. 

[keat]

This particular site has been messed with, updated, moved servers so many times, I'm not surprised that there would be errors.

I re-ran the upgrade, but there are still a number of UTF8 tables.

I'm now going to perform a clean install and compare.

[bsmither]

There is a step in the database schema upgrade cycles where each table has a new table created with a _utf8 suffix and having a utf8 character set and collation. Then, all the data is copied over from the existing table to the utf8 table relying on the database server to correct regional character set high-order bytes (like the British Pound currency symbol) to the appropriate utf8 byte pair. Then, the original table is dropped and the utf8 table is renamed back to the original table's name.

For the result to be that there are tables with the original names and related tables with the utf8 suffix suggests that:
1. That step ran out of time
2. The username accessing the database for this step has insufficient privileges

No.1 is unlikely.

No.2 requires that the user have DROP privileges so that the utf8 tables can be renamed. That can't happen if the original tables are still there.

(During schema upgrades, CubeCart has little to no code to log and ultimately report any errors that may have occured.)

In your hosting control panel, have the MySQL Admin Tool examine the privileges of the user CubeCart uses to access the database. Using phpMyAdmin, examine the structure of a non-utf8 table, CubeCart_category, for example. If this table is still 'latin', then reason No.2 is likely.

 

[keat]

There were about a dozen utf8 tables, all of which were empty.

I performed a clean install and made a note of the database tables then compared against my updated site.

The database user had full privileges. 

I can't see anything missing from the site.

I seem to recall that my original site did this a year ago.

I updated another v3 last week which is sitting on a different server and this has no rogue utf8 tables.

 

[bsmither]

Empty utf8-suffixed tables. These tables, even empty, should have utf8 characterset and collation parameters.

The other tables common to CC3 -- were their charactersets and collations 'utf8' or 'latin'?

 

[keat]

I believe the originals are Latin Swedish.I could take a look in the morning as this is only a dummy run

[Al Brookbanks]

I think there could be a fail here (or somewhere around there) if the existing table collation is not as expected causing and SQL error.

https://github.com/cubecart/v6/blob/master/setup/setup.upgrade.php#L126

I think certain collations will fail. I've seen this before but it's rare.

[keat]

I've just jumped on the server and taken a quick look at the original V3, and all tables witch exception to one are latin1_swedish_ci, what ever that means.

There is one table (cats_idx) which is UTF8

 

On one of the test updates, I took a sneaky look in PHP MYAdmin to see what tables were being created, and somewhere around  V4 upgrade processes, I could see tables with a UTF8 suffix

[bsmither]

It would be just fantastic if your account could enable General Logging on the database server (would need a VPS complete with database as part of it).

Then go through an upgrade sequence. The queries logged should show what tables are being found, created, data copied, dropped and renamed.

 

[keat]

Brian, the logs should be in your email.

Incidentally, I'm happy that the upgrade copied all the data, just maybe didn't remove the UTF8 suffixed tables, as the site appears to work,

 

 

 

[bsmither]

The upgrade code, when making UTF8 tables, is looking for 'latin1' anywhere in the stated CREATE statement.

"You have investigated the table structure and see that your tables are 'latin1_swedish_ci'. But this is the COLLATION, not the CHARSET.)

So, this is good. I see statements such as:

CREATE TABLE `CubeCart_Downloads_utf8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) NOT NULL DEFAULT '0',
  `cart_order_id` varchar(32) NOT NULL DEFAULT '',
  `noDownloads` int(11) NOT NULL DEFAULT '0',
  `expire` int(11) NOT NULL DEFAULT '0',
  `productId` int(11) NOT NULL DEFAULT '0',
  `accessKey` varchar(10) NOT NULL DEFAULT '',
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Evidently, however, the command $db->misc() (via $db->_sqlDebug) is returning false because there is no following statement "SHOW TABLES LIKE CubeCart_Downloads_utf8". (The CubeCart_system_error_log may be present by this time. So, look in there.)

So, the next step is to find the database server's error log. Find out if there is an error about the above statement.

[keat]

I already deleted the test install as I'm using the domain, so I've no cubecart error logs, and in the server error logs, I can see me building the database but not much after this.