2

In the table eav_attribute_option_swatch (the one that holds the swatch options for the attributes) I have about 6k records.
Some are color codes, others are paths to images and most of them are null (in the value column).
But something, somewhere, sometime, somehow, deletes them and I don't know what, where, when or how.
It one point I randomly realize that in the frontend the configurable products don't show swatches anymore and I can see a regular dropdown with the options.
The table is not completely cleared. So far when it happened, there were somewhere between 1 and 10 records remaining. I could not find anything special about these except for one. There is always a record with option_id = 0. (not sure if relevant).

The main problem is not that it happens. The problem is that I cannot reproduce it.
What I've tried so far:

  • Save random attributes that have or don't have swatches.
  • Saved the attribute with the most options (2k+). I thought it may have something to do with max post size or max input vars. This works as expected.
  • Delete one attribute.
  • Delete one option
  • Delete and add a swatch at the same time.
  • Changed the attribute type from swatch to something else.
  • ... and back to swatch
  • Banging my head on the table.

None of the above helped (specially the last one).

I'm not looking for a full solution, though that would be great.
I need other examples (code or actions) that might touch the eav_attribute_option_swatch table.

Note:
I do have some 3rd party extensions installed and a few more that were developed internally. But I'm just looking for ideas here.

3
  • Can you turn on the mysql general log, to search for the guilty query? It would tell you how the table was being altered, and you could match the timestamp with the http log to see what page load caused it (assuming it's not kicked off my a cron). If the server is too busy for the general log, how about something like this stackoverflow.com/questions/779230/…
    – Richard
    Jan 17 '19 at 15:30
  • @Richard. Trying that. But I cannot reproduce it even on the server where this happens from time to time. And I don't think I can afford to leave the query log on for a few days. It will probably fill the disk way before that. But the secondary table approach might work
    – Marius
    Jan 17 '19 at 15:32
  • On second thought , not sure if the trigger approach will help. I would see at one point that something got deleted from the swatch table, but I still won't know what triggered that.
    – Marius
    Jan 17 '19 at 15:42
1

If you get desperate, something like this

#!/bin/bash

USER=root
PASS=pass
DB=db
LOWRECORDS=100
LOG="/var/log/mysql/general.log"



if [ -e "iamdone" ]; then
    exit 0
fi

COUNT=`mysql -u $USER --password=$PASS -D $DB -N -s -e "SELECT count(*) from eav_attribute_option_swatch;"`

if [[ $COUNT -gt $LOWRECORDS ]]; then
    mv "${LOG}" "${LOG}.tmp"
    mysqladmin -u $USER --password=$PASS flush-logs
    rm "${LOG}.tmp"
else
    `mysql -u $USER --password=$PASS -D $DB -N -s -e "SET GLOBAL general_log = 'OFF';"`
    touch "iamdone"
    echo "I am done"
fi

in your cron every 5 minutes will keep rotating the log out until there's < 100 records in the table, then turn off logging, email you (assuming you get cron output) and turn of the log

1
  • +1 for ingenuity. I'll try it out
    – Marius
    Jan 18 '19 at 7:00
0

With the help of the community... the problem is, as reported here, the incorrect query to delete the option swatches when an attribute is changed from swatch to dropdown.
The query to delete the swatches is build with ->where('option_id', $optionId) instead of ->where('option_id = ?', $optionId).
Fixed it for now with an around plugin for \Magento\Swatches\Model\ResourceModel\Swatch::clearSwatchOptionByOptionIdAndType

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.