Jump to content

Best Way to be sure DB has ALL current tables, etc


Recommended Posts

try a clean install to a new DB, then query both DB's listing all tables/columns.. left join the results, match on table name / column name.. and look for anything non matching (where something = NULL). you can use the same strategy for index comparisons as well. Takes a bit of time to setup, but works. there's probably an easier way. Could be useful to have a DB health check tool for CubeCart.. I may write one at some point, along with the other billions of things on the pending list.

 

What's the behaviour?

Edited by Noodleman
Link to comment
Share on other sites

There will be error messages in PHP's error log copied over from the database server's response when:
* a table is missing
* a named column is missing - especially in the WHERE part of the query

CubeCart will simply have data go missing or not retrieved when:
* one or more, or all columns (SELECT *) are retrieved
* a column is missing when inserting or updating records

 

Edited by bsmither
Link to comment
Share on other sites

Thank you both, but this is so obvious to the two of you, but doesn't tell me enough "how to". For instance, what is "left join"? HOW do I get CC to attempt a retrieve of all columns (SELECT*)?

The behavior that's plaguing  me is https://github.com/cubecart/v6/issues/2004

This is on the latest commit of 6.2.1. I do have one public site at this stage of CC, but it's far from stock: https://dirtybutterestates.com . I got confused at some point and upgraded the live site unintentionally - luckily this site has extremely low traffic. I've tried it with and without plugins enabled, with and without cookie dialogue, with cookies OK'd or Blocked,  guest or registered. I've also tried it with countries that do have states, others that do not, and also optionals. No permutation I've tried made a difference.

Link to comment
Share on other sites

I mean to say, when CubeCart issues a query to retrieve an entire record (that is, all the columns), that query takes the form of:

SELECT * FROM CubeCart_inventory WHERE product_id = 1

The asterisk is a shortcut for 'all columns'. Therefore, if the table is missing a column, the 'google_category' for example, CubeCart won't be aware of it.

The symptom of missing table columns would simply be that there will be no value shown in the display.

 

Link to comment
Share on other sites

I have two windows open: manually looking at structure one table at a time, comparing what was stock files, but originally based on my plush database (our main store) with a brand new install.

I am finding some differences so far and changing the old "stock" to match the 100% stock - but haven't seen an actual missing table yet.

Is there a way to overwrite structure only, other than manually one line at a time? Is that what "join" is??

Link to comment
Share on other sites

The "left join" trick is assuming one has a higher level of database mastery than most.

Overwriting the structure (the database's schema) other than one tweak at a time also assumes a high level of database mastery.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...