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.

Improve this question
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/… Commented Jan 17, 2019 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 Commented Jan 17, 2019 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. Commented Jan 17, 2019 at 15:42

2 Answers 2

Reset to default
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

Improve this answer
0
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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.