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

[Dirty Butter]

I have a test site that has 100% stock code. But I have behavior that Al cannot replicate. I need to be sure I haven't missed some query change in the database tables. Any suggestions?

[Noodleman]

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 May 31, 2018 by Noodleman

[bsmither]

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 May 31, 2018 by bsmither

[Dirty Butter]

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.

[bsmither]

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.

 

[Dirty Butter]

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??

[bsmither]

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.

[Dirty Butter]

HA! Well high level mastery is certainly NOT in my bailiwick! I'll keep working on it a few tables at a time then. Thanks for the explanations!