option_matrix corrupted

[keat]

Any thoughts on why the option_matrix table has defaulted to stock please.

Matrix options should have contained the product code, so for example a black pen, would have had a product code of 'pen-black'

But this morning, lots of products had reverted to the standard Cubecart default of '-1'

so pen-black, pen-blue, pen-red, became pen-1, pen-2, pen-3

[bsmither]

Please verify that the data in the database has or has not changed. Using an external database utility (such as phpMyAdmin), look directly at the Cubecart_option_matrix table. Do all rows have the generic format for 'product_code'? Or, for your 'pen' example, is there still the custom codes, but only when showing this data in admin is it the generic format?

[keat]

I performed a database restore this afternoon, but before I did so, I took a copy of the whole database.

I've just looked at the option_matrix table, and where it should have had 4300 entries, I've spotted that it only had 27.

So this explains why my products reverted to the cubecart std format, but now I need to understand where the other 4273 entries went.

[keat]

we have a huge plugin installed which does something with custom pricing collected from our internal server.

Whilst I don't believe it would have caused this, I can't rule it out, so I've also contacted the dev.

[bsmither]

Is your hosting environment open to you? That is, if needed, are you able to configure the database server to maintain a query log?

It's too late now, but if so, and if you have a lingering fear of whether this might happen again, getting the database server to make daily or weekly (depending how busy your site is) log rotation might be something to consider.

Ask your dev if their plugin sends a resolution message to ACP->adminLog().

Edited April 25, 2023 by bsmither

[keat]

Dev has ruled out anything from his scripts that could cause this.

As regards the hosting environment, its a rented whm/cpanel server with full root access.

Can you suggest which log file (if exists) may give us some clues ?

Edited April 26, 2023 by keat

[bsmither]

So you are fairly confident the dev people has rightfully denied responsibility. But you seem to be saying that this plugin does write things to the CubeCart database. That begs the question, does the plugin use CubeCart's collection of database functions (the 'Database' class), or does it access the database using it's own connections.

The following pages are from the MariaDB database server - a drop-in replacement for Oracle's MySQL database server.

This page discusses how to enable the database Binary Log. This log only has queries that actually change the data or database schema. It is more complicated to enable, rotate, and read. It is used primarily to get subordinate servers to stay current with the Main server.

https://mariadb.com/kb/en/binary-log/

The General Query Log logs everything. It is easy to enable, rotate, and read.

https://mariadb.com/kb/en/general-query-log/

Check the configuration file to see if any log files are enabled (possibly at /etc/mysql/my.cnf).

 

Edited April 26, 2023 by bsmither

[keat]

The mod has been working for maybe going on a year and I've never seen such problem.

Devs say that his mod wouldn't affect the matrix table, although I am aware that it references that table to collate product codes.

 

I already checked mysqld.log and don't see anything of any significance

Edited April 26, 2023 by keat

[bsmither]

Is 'mysqld.log' the name of the General Query Log?

And is it huge in filesize?

 

 

Edited April 26, 2023 by bsmither

[keat]

Mysqld.log is about 7gb in size, so not overly massive, maybe a bit bigger than it ought to be.

There isn't really much in it to be fair, and what is in there is pretty consistant throughout the life span.

What I have spotted is that the option_matrix table contains prices, which can only come via the mod.

The mod runs out of hours. Considering it was fine on Monday evening and not on Tuesday, I can only decduce that the nightly routine screwed up somewhere.

So I am leaning towards the mod at the moment.

[bsmither]

The General Query Log on my server has Date/Time when the connection to the client is made. So, I would look at the entries about that time, scanning for UPDATE `CubeCart_option_matrix`or ALTER TABLE `CubeCart_option_matrix`. Why ALTER TABLE? Mostly inconceivable that the column 'product_code' would be removed, then re-added, but I've seen weirder things.

Having that data in the whole column go missing without the database logging it in the General Query Log, is beyond belief.

Edited April 27, 2023 by bsmither