Jump to content

Welcome, Guest!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

  • 0
the.rampage.rado

DB Schema in Core updater

Question

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 :) )

Share this post


Link to post
Share on other sites

16 answers to this question

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

Than if everything works you are good to go.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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...

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)
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 by toplakd

Share this post


Link to post
Share on other sites
  • 0
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;

 

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

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 by the.rampage.rado

Share this post


Link to post
Share on other sites
  • 0
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 🙂  

 

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

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 by the.rampage.rado
  • Like 1

Share this post


Link to post
Share on other sites
  • 0

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...