keat Posted February 14, 2017 Share Posted February 14, 2017 One of my recently updated carts has a number of UTF8 tables in the database. What are these, and are they safe to remove ? Quote Link to comment Share on other sites More sharing options...
Al Brookbanks Posted February 14, 2017 Share Posted February 14, 2017 That indicates quite a significant failure on upgrade. Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 Site appears to work OK, although i've not put it in to production yet Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 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 ? Quote Link to comment Share on other sites More sharing options...
Al Brookbanks Posted February 14, 2017 Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 14, 2017 Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 14, 2017 Share Posted February 14, 2017 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'? Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 I believe the originals are Latin Swedish.I could take a look in the morning as this is only a dummy run Quote Link to comment Share on other sites More sharing options...
Al Brookbanks Posted February 14, 2017 Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted February 14, 2017 Author Share Posted February 14, 2017 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 Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 14, 2017 Share Posted February 14, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted February 15, 2017 Author Share Posted February 15, 2017 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, Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 15, 2017 Share Posted February 15, 2017 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. Quote Link to comment Share on other sites More sharing options...
keat Posted February 16, 2017 Author Share Posted February 16, 2017 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. 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.