Occasionally while editing products in VirtueMart, I'll notice some problem such as a misspelled word that I suspect I'll find in several other products. I'm working with over 1,000 products so I'll switch to PHPMyAdmin to do a regular expression search. In the past that has quickly provided a list of products to be corrected.
If the results show that there are candidates in the #_virtuemart_products_en_gb database table, I try to open that table for inspection in PHPMyAdmin. Now I am receiving this error message: #1054 - Unknown column 'id' in 'order clause.' Of course, the table does not have an id column. This is new behavior, perhaps since the most recent VirtueMart revision.
If I try to browse that table in PHPMyAdmin, I receive the same error message. I can successfully perform a 'manual' search by constructing a MySQL query in the SQL tab of PHPMyAdmin by omitting or modifying the order clause.
I have reinstalled VirtueMart. I have Enabled the database Update tools. In VirtueMart Tools, I have Installed/Updated the Tables. I have Optimized the database. I have updated the collation of the VirtueMart tables from the old utf8_general_ci to utf8mb4_unicode_ci.
(The optimization, by the way, did not remove 6.4 KiB of overhead from the 1.5 MB #_virtuemart_products_en_gb table. Also, the overhead in the #_virtuemart_product_medias table was not removed.)
The #1054 error remains. What shall I do next?
20220604 Additional Information
I have recently discovered that the error displayed on my local site is not shown on the public server. Perhaps you might find a clue in this listing.
The site running on a Kubuntu computer (exhibits error):
VirtueMart 4.0.2 10661
Joomla 3.10.9
PHP 7.4.16
MySQL 8.0.27
PHPMyAdmin 5.0.4deb2~bpo10+1+focal1
Database Collation: utf8mb4_unicode_ci
Table Collation: utf8mb4_unicode_ci
PHP extension: mysqli curl mbstring
Database Server type: MySQL
Database Version: 8.0.27-0ubuntu0.21.04.1 - (Ubuntu)
The site running on the public host server (works as expected):
VirtueMart 4.0.2 10661
Joomla 3.10.9
PHP 7.4.29
MySQL version unknown
PHPMyAdmin 4.9.7
Database Collation: utf8mb4_unicode_ci
Table collation: utf8mb4_unicode_ci
PHP extension: mysqli curl mbstring
Database Server type: Percona Server
Database Version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619
Looks for me like an error oh phpmyadmin, which expects that there is an column named id, which is in your case virtuemart_product_id.
Thank you for the hint! I went into the configuration of my PHPMyAdmin program to fix it.
The path to the setting varies but in my PHPMyAdmin version 5.0.4deb2~bpo10+1+focal1, it is:
Home > Settings > Main Panel button > Browse Mode tab. Scroll down to and uncheck "Remember table's sorting checkbox."