DB Schema in Core updater

[the.rampage.rado 39]

How safe is to apply the fixes there?

I have 3 shops on TB, 2 of them I've updated recently to edge and the schema check is working again. This time there are lots and lots of 'fixes' most of them are size related:

 

	Column ps_access.id_profile has data type int(10) unsigned instead of int(11) unsigned

But some of them offer to change the encoding:

 

Table ps_store should use character set utf8mb4/utf8mb4_unicode_ci instead of utf8/utf8_general_ci

And some of them I don't even know what they do:

Column ps_product_attribute.ecotax has data type decimal(17,6) instead of decimal(20,6)

 

 

The most conserning things are the encoding related. As far as I read the proposed one is better but could there be some some data corruption if I apply those fixes?

(backups are always made, no need to advise on that :) )

[yaniv14 118]

I dont know how the core updater code will work when doing those updates.

But in general you shouldn't have any issue doing those changes in the DB.

No data will be damaged or lost if the process will finished as it should.

[Traumflug 812]

Increasing the size of a table data type can never hurt other than needing slightly more disk space.

There is one case where it wants to decrease decimal(21,6) to decimal(20,6), which is safe as well. All prices are stored in decimal(20,6), so having one of many fields with an additional digit makes no distinction.

[the.rampage.rado 39]

OK, thanks @Traumflug!

What about the encoding? Can some characters get corrupted (I'm using Cyrillic mostly).

[toplakd 190]

Make backup of the complete shop to subdomain and test updating on backup.

Than if everything works you are good to go.

[the.rampage.rado 39]

I'll start to do them... :)

992 fixes in just one of the shops... :)

[toplakd 190]

Something similar that I had.

I think I had about 38 pages 🙂 clicked them twice. Once on backup shop, than tested for 2 days, than applied everything on live shop 🙂

But my database started as PS 1.3.8 back in January 2011

[the.rampage.rado 39]

Different primary key in table ps_page_cache

Does not want to fix itself. What does it do and what I can do about fixing it? :)

 

50 minutes later, on 2 devices ..... so far - no observable change...

[toplakd 190]

16 minutes ago, the.rampage.rado said:

Different primary key in table ps_page_cache

Had same thing, I know I modified it manually in database, but forgot what exactly I modified 😞

p.s.:

I think I compared the page_cache table to virgin TB install and modified it manually.

Edited June 12, 2020 by toplakd

[datakick 1,276]

18 minutes ago, the.rampage.rado said:

Does not want to fix itself. What does it do and what I can do about fixing it? 🙂

Could you run this statement in phpmyadmin and show results:

SHOW CREATE TABLE ps_page_cache;

 

[the.rampage.rado 39]

 

I don't know if this is of any help?

[the.rampage.rado 39]

And here's the table structure:

[datakick 1,276]

3 minutes ago, the.rampage.rado said:

 

I don't know if this is of any help?

Not really. I need to see the complete result, not truncated. There's a settings for this in phpmyadmin -- see answer here https://dba.stackexchange.com/questions/175426/output-getting-truncated-in-phpmyadmin

[the.rampage.rado 39]

SHOW CREATE TABLE ps_page_cache



ps_page_cache	CREATE TABLE `ps_page_cache` (
  `id_page_cache` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cache_hash` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_currency` int(11) unsigned DEFAULT NULL,
  `id_language` int(11) unsigned DEFAULT NULL,
  `id_country` int(11) unsigned DEFAULT NULL,
  `id_shop` int(11) unsigned DEFAULT NULL,
  `cache` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `cache_size` int(10) unsigned DEFAULT NULL,
  `entity_type` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_entity` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_page_cache`),
  KEY `cache_hash` (`cache_hash`),
  KEY `id_currency` (`id_currency`),
  KEY `id_language` (`id_language`),
  KEY `id_country` (`id_country`),
  KEY `id_shop` (`id_shop`),
  KEY `id_entity` (`id_entity`),
  KEY `entity_type` (`entity_type`),
  KEY `cache_combo` (`cache_hash`,`id_currency`,`id_language`,`id_country`,`id_shop`)
) ENGINE=InnoDB AUTO_INCREMENT=2451 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci	

 

Edited June 12, 2020 by the.rampage.rado

[datakick 1,276]

12 hours ago, the.rampage.rado said:

SHOW CREATE TABLE ps_page_cache



ps_page_cache	CREATE TABLE `ps_page_cache` (
  `id_page_cache` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cache_hash` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_currency` int(11) unsigned DEFAULT NULL,
  `id_language` int(11) unsigned DEFAULT NULL,
  `id_country` int(11) unsigned DEFAULT NULL,
  `id_shop` int(11) unsigned DEFAULT NULL,
  `cache` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `cache_size` int(10) unsigned DEFAULT NULL,
  `entity_type` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_entity` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_page_cache`),
  KEY `cache_hash` (`cache_hash`),
  KEY `id_currency` (`id_currency`),
  KEY `id_language` (`id_language`),
  KEY `id_country` (`id_country`),
  KEY `id_shop` (`id_shop`),
  KEY `id_entity` (`id_entity`),
  KEY `entity_type` (`entity_type`),
  KEY `cache_combo` (`cache_hash`,`id_currency`,`id_language`,`id_country`,`id_shop`)
) ENGINE=InnoDB AUTO_INCREMENT=2451 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci	

 

Thank you, this will help fix the coreupdater issue.

Until then, you should do this:

1) execute this in phpmyadmin

drop table ps_page_cache;

2) go to core updater and let it re-create the page for you from scratch

 

PS: this 'fix' can be used for table <PREFIX>page_cache only. If anyone encounter similar issue with other tables, please don't 'drop' them 🙂  

 

[the.rampage.rado 39]

Great! Thank you onem ore time @datakick ! Perfect as always!
 

Can we expect the same amount of differences in other tables aswell? (in this one -4 rows)

Edited June 12, 2020 by the.rampage.rado

[Pedalman 57]

I hit the fix button for all the hundreds of critical recommendations in the hope all will be more fine than it was. But one table is resistent to auto fix 🙂

 

 	Different unique key `id_product_2` in table ps_specific_price

What is the recommendation how to deal with that one?