Elbuz Tunnel runs your SQL queries in the site database on every run: this updates a site on a CMS that isn't in the list or adds what the standard update lacks.
When you need this:
- Your site is custom-built — the tunnel loads data into temporary tables; your queries decide where it goes.
- You need to rewrite SEO URLs of existing products — an after-update query rewrites URLs the CMS driver leaves alone.
- You need to clear the site cache after the update — the “Open link” tab calls a URL or a script.
Where to start: tunnel template card → “Additional” → “SQL queries”.
What your own queries can do
- A site on your own CMS — the “MySQL (SQL custom query)” mode: the tunnel delivers the data, you write the merge.
- Extending a supported CMS — SEO URLs of existing products, your own fields, price rounding.
- A script after the update — a URL or a PHP script, for example to clear the cache.
- Temporary tables reference — what arrived from Elbuz and in which columns.
Your isn't in the list. The CMS list has eight options; the last is “MySQL (SQL custom query)”. It's for a custom-built site or a rare platform: the tunnel delivers data to your site database, and your queries say where it goes next. One requirement: the site runs on MySQL and you have access to its database.
Your CMS is in the list, but the update needs more. The standard scenario covers the catalog, prices, stock, and photos. What it doesn't do: rewrite SEO URLs of existing products, apply your own price formula, or fill a non-standard field your developer once added to a site table. You write these additions as queries too, in the same window.
How it works: temporary tables
The key to everything: the tunnel doesn't write straight into your tables. A run has two stages.
Stage one. Elbuz creates its own temporary tables in your site database, named like etrade_product_temp, etrade_category_temp, etrade_manufacturer_temp, and loads into them what you selected for export. It's a full copy of the Elbuz data in an SQL-friendly shape: one row per product, category, or attribute.
Stage two. The data moves from the temporary tables to the site's working tables — oc_product, wp_posts, whatever yours are called. This stage is what the CMS driver does. In the “MySQL (SQL custom query)” mode there's no such driver, so you write stage two yourself.
Hence the main principle: your queries join a temporary table to a working table. Almost every query in this article looks like “take etrade_*_temp, find the matching row in the site table, update it”.
The link isn't the numeric id — it differs between Elbuz and the site — but the uuid: a string identifier Elbuz assigns to each record for good. Temporary tables have a uuid column, and the goal of preparation is to have the same uuid in the site table.
Queries run in your site's live database — with no preview, no undo, and no “Are you sure?” prompt. A wrong UPDATE without WHERE breaks the catalog at once. If SQL is new to you, order the setup from Elbuz support or bring in your developer.
The app doesn't check queries: DROP TABLE is saved just like UPDATE, and an error only surfaces during the run — queries that ran before it stay applied.
How to write queries in the “SQL queries” window
It opens from the tunnel template card: the “Additional” column on the right, the “SQL queries” row with a database icon. In an export template it has three tabs.
| Tab | When it runs |
|---|---|
| “After update” opens first | After the CMS driver has moved the data to the working tables. The main place for your queries: the data is already on the site, and you can refine it |
| “Before update” | After the temporary tables are loaded but before the move to the working tables. The place for preparation: match existing records, adjust data in the temporary tables themselves |
| “Open link” | Not SQL. Here you enter a URL or a path to a PHP script to call after the update — for example, to clear the site cache |
In an import template (when you pull data from the site instead) there's one tab — “SQL queries to import data from the site”. There you write SELECT queries the tunnel uses to fetch categories, products, and the rest from your database.
1
Update tabs2
;;; separator- Tabs — queries run after the update, before the update, or open a link.
- The ;;; separator ends each query so the module can run them one by one.
The editor has MariaDB syntax highlighting and line numbers. There's no “Save” button: a second after you stop typing, the text goes to the server on its own, and once the server accepts it, “Saved:” with the time flashes under the field for five seconds. This saving can't be undone, so keep drafts of complex queries in a separate file.
Queries in the field are separated not by a plain ; but by ;;; — three characters in a row. Put one separator at the end of every query, including the last:
UPDATE oc_product SET price = CEIL(price);;;
Use a single semicolon, and the whole batch goes to the site as one query and fails. Comments -- and # don't work either: line breaks are stripped before sending, so a comment “swallows” the rest of the query, which silently does nothing. Write explanations outside the field.
Since module version 3.2.0, both limits are gone: you can separate queries with a plain ;, and comments work properly. The “Check Connection” button shows your module version. While the hosting has an older one, use ;;; and no comments; this also works with newer versions.
Your batch goes to the site as a separate request, exactly as you wrote it. The app sets session options — empty SQL_MODE, large selects allowed, a raised group_concat_max_len, up to 2 GB of memory for temporary tables, and innodb_lock_wait_timeout of 300 seconds — before its own update queries, but not before your batch.
If your queries need any of this, add the matching line at the start of the field, for example SET SESSION group_concat_max_len = 99999999;;;.
The “Open link” tab fires at the end, after the site update steps and your “After update” queries, and only if all of that finished without errors: if the upload to the site or one of your queries failed, or the run was stopped, neither URLs nor the script are called. What it does depends on what's in the field.
If the text contains http, the whole content is treated as a list of URLs: each line is a separate URL, Elbuz requests it, waits up to 15 seconds, and doesn't read the response.
If there's no http, the text is a path to a PHP script on the hosting (../myfile.php), run by the tunnel module: the step shows in the log, and a module error stops the run. Don't mix a URL and a path: with one URL in the field, the path is treated as a URL too and fails.
- Action or result
- Decision: the path depends on the answer
Where your queries fit into the run
The order of steps is visible in the run log, in the Operations Center. For a site on a supported CMS it is:
- data is loaded into the temporary tables
etrade_*_temp; - “Running SQL before updating” — your “Before update” tab;
- “Updating site” — the CMS driver moves the data to the working tables;
- photos are downloaded from the cloud to the hosting;
- “Executing SQL queries after update” — your “After update” tab.
In the “MySQL (SQL custom query)” mode, step 3 doesn't exist at all. The run looks like this: temporary tables loaded → “before update” queries run → photos downloaded → “after update” queries run. All the work with the site's working tables is in your two fields.
- Action or result
- Decision: the path depends on the answer
A site that isn't in the list: preparation
For a CMS from the list, the driver creates the uuid column; for your own CMS, you do. Once, before the first run, give the site a uuid column — it stores the record identifier from Elbuz. The example is for OpenCart; replace oc_ with your table prefix and add the tables you need.
ALTER TABLE oc_category ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_product ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_manufacturer ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_attribute ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_attribute_group ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
Next, fill the new column for what's already on the site. If the site catalog existed before Elbuz, the simplest option is to use the site's own numeric identifier as the uuid:
UPDATE oc_category SET uuid = category_id WHERE uuid = '';;;
UPDATE oc_product SET uuid = product_id WHERE uuid = '';;;
UPDATE oc_manufacturer SET uuid = manufacturer_id WHERE uuid = '';;;
UPDATE oc_attribute SET uuid = attribute_id WHERE uuid = '';;;
Run these two sets once — in phpMyAdmin or through the “Before update” tab on the first run. The index on uuid is required: without it, every join of a temporary table to a working table becomes a full scan, and a run on tens of thousands of products takes unbearably long.
In the “MySQL (SQL custom query)” mode, site database backup doesn't work: the template checkbox is on, but no copy is made, and the log lacks “A backup copy of the site database has been created:”. Back up with your hosting tools — right before the first run, while the queries are untested.
How to write a merge: match, update, create
Step 1. Match what's already on the site
The goal of this step is to set two fields in the temporary tables: the record's numeric id on the site and the row_exist flag. After that, each row knows whether to update or create a record.
row_exist = 1 means “this record already exists on the site”, row_exist = 0 means “it needs to be created”. Keep these queries in the “Before update” tab.
Categories first — find the site record by uuid, then the parent category by uuid_parent, and only then set the flag:
UPDATE etrade_category_temp t, oc_category c
SET t.category_id = c.category_id WHERE t.uuid = c.uuid;;;
UPDATE etrade_category_temp t, oc_category c
SET t.parent_id = c.category_id WHERE t.uuid_parent = c.uuid;;;
UPDATE etrade_category_temp SET row_exist = 1 WHERE category_id > 0;;;
Then products. Besides their own uuid, they get a category and a manufacturer — you'll need these for the insert:
UPDATE etrade_product_temp t, oc_product p
SET t.product_id = p.product_id WHERE t.uuid = p.uuid;;;
UPDATE etrade_product_temp t, oc_category c
SET t.category_id = c.category_id WHERE t.category_uuid = c.uuid;;;
UPDATE etrade_product_temp t, oc_manufacturer m
SET t.manufacturer_id = m.manufacturer_id WHERE t.manufacturer_uuid = m.uuid;;;
UPDATE etrade_product_temp SET row_exist = 1 WHERE product_id > 0;;;
And manufacturers — the same way, by uuid:
UPDATE etrade_manufacturer_temp t, oc_manufacturer m
SET t.manufacturer_id = m.manufacturer_id WHERE t.uuid = m.uuid;;;
UPDATE etrade_manufacturer_temp SET row_exist = 1 WHERE manufacturer_id > 0;;;
The same approach works for links and attributes: etrade_product_to_category_temp is matched by product_uuid and category_uuid, etrade_attribute_temp by uuid and block_uuid, etrade_product_attribute_temp by product_uuid and attribute_uuid (its own row identifier is called row_uuid, not uuid).
Step 2. Update existing records
Everything marked row_exist = 1 is updated with a regular UPDATE joined on the numeric id you just set. These queries go into the “After update” tab.
UPDATE oc_product p, etrade_product_temp t
SET p.date_modified = NOW(),
p.manufacturer_id = t.manufacturer_id,
p.model = t.model,
p.sku = t.mpn,
p.price = t.price,
p.quantity = t.quantity,
p.status = t.status,
p.weight = t.weight
WHERE t.row_exist = 1 AND t.product_id = p.product_id AND t.type_id = 1;;;
UPDATE oc_product_description d, etrade_product_temp t
SET d.name = t.name, d.description = t.description_full
WHERE t.row_exist = 1 AND t.product_id = d.product_id
AND t.type_id = 1 AND d.language_id = 1;;;
The type_id = 1 condition is there for a reason: etrade_product_temp holds option products with type_id = 2 next to regular products. If you don't filter them out, each variant goes to the site as a separate product page.
Step 3. Create new records
What's left with row_exist = 0 needs to be created on the site. Order matters: first create the record, then immediately read its new numeric id back into the temporary table — otherwise the next queries won't know what to attach the description, photos, and links to.
INSERT INTO oc_category (date_added, sort_order, status, uuid)
SELECT NOW(), sort_order, status, uuid
FROM etrade_category_temp WHERE row_exist = 0 GROUP BY uuid;;;
UPDATE etrade_category_temp t, oc_category c
SET t.category_id = c.category_id
WHERE t.row_exist = 0 AND t.uuid = c.uuid;;;
INSERT INTO oc_category_description (category_id, description, language_id, meta_description, meta_keyword, name)
SELECT category_id, description_short, 1, meta_description, meta_keyword, name
FROM etrade_category_temp WHERE row_exist = 0;;;
Next, rebuild the category hierarchy — the parent is found by uuid_parent — and create manufacturers and products the same way. For photos in etrade_image_temp, set item_id by item_uuid and row_type, which says whose photo it is: product, category, manufacturer, attribute, attribute_block, or article. Only then write the file name into the site table.
Matching → update → insert → re-matching new records → links. If you swap insert and matching, new products end up without a description and photos on the first run and get duplicated on the second: they already have a uuid, but you set row_exist in the wrong place.
Extending the update of a supported CMS
When your CMS is in the list, the driver does the matching and inserting, and your queries only add what the scenario lacks. Here are the most common cases.
Rewrite SEO URLs of existing products. For OpenCart, URLs are set only for new records; to update existing ones, you need a separate query. For OpenCart 3:
UPDATE oc_seo_url su
INNER JOIN etrade_product_temp e
ON e.product_id = CAST(SUBSTRING(su.query FROM 12) AS UNSIGNED)
SET su.keyword = e.seo_url
WHERE e.row_exist = 1 AND e.seo_url != '' AND su.query LIKE 'product_id=%';;;
For categories, in the same query change the source table to etrade_category_temp, the condition to category_id=%, and the SUBSTRING offset to 13. For OpenCart 2, use oc_url_alias instead of oc_seo_url.
Use your own value instead of the standard one. For example, write the internal Elbuz SKU to the “Model” field on the site, and gtin to the barcode:
UPDATE oc_product p, etrade_product_temp t
SET p.model = t.sku, p.sort_order = t.sort_order, p.upc = t.gtin
WHERE p.product_id = t.product_id;;;
Bring over what the scenario doesn't transfer. The full description and the instructions description into non-standard columns, the minimum order quantity, rounding the price up:
UPDATE oc_product SET price = CEIL(price);;;
UPDATE etrade_product_temp t, oc_product p
SET p.minimum = t.order_minimum WHERE t.uuid = p.uuid;;;
UPDATE oc_product SET stock_status_id = 5 WHERE quantity = 0;;;
Scenarios
The site is custom-built, and there's no ready-made driver for it
What to do. In the tunnel template card, in the “Select CMS” block, choose “MySQL (SQL custom query)” in the “Select your CMS” list and enter the database parameters manually. Add uuid columns to the site (preparation). Keep matching queries in the “Before update” tab, and update and insert queries in the “After update” tab.
What you get. The tunnel loads the selected data into the temporary tables etrade_*_temp, runs your “Before update” queries, and at the end the “After update” queries. The temporary tables stay in the site database after the run — they show exactly what arrived from Elbuz.
What won't happen. The “Updating site” and “Synchronizing item IDs with the site database” steps don't exist in this mode: no row in the working tables changes unless your query changes it. No backup of the site database is made, even with the “Create a backup copy of the site database” checkbox on.
An OpenCart 3 store renamed product SEO URLs and wants the new URLs on the site
What to do. The CMS in the template stays “OpenCart”, version 3. In the “After update” tab, add the query from “Extending the update of a supported CMS” that rewrites oc_seo_url from etrade_product_temp.
What you get. The OpenCart driver sets URLs for products that don't have a single row in oc_seo_url on the site yet, and your query rewrites the URLs of existing products at the end of the run.
What won't happen. Without your query, an existing product URL on the site won't change, no matter how many times you change the SEO URL in Elbuz. Products with an empty SEO URL in Elbuz aren't touched by the example query — the e.seo_url != '' condition keeps their old URL.
You need to clear the site cache after every update
What to do. In the “Open link” tab, in the “Open link or execute PHP script (run after update)” field, enter the URL of the cache-clearing script — one per line if there are several. Or a path to a PHP file on the hosting, for example ../myfile.php.
What you get. If the site update and your queries finished without errors, Elbuz requests each URL, or the tunnel module runs the script at the path. A script path leaves the line “Run PHP script (webhook) after update” in the log.
What won't happen. The log says nothing about whether a URL worked: the response isn't checked, and Elbuz waits no longer than 15 seconds. If the field has at least one URL with http, a file path on the next line won't run (see the diagram).
OpenCart 3 with options or families: your query filters option products
What to do. The template has “Options” or “Product Families” selected, and your queries contain a type_id = 1 or type_id = 2 condition. Put queries that need to tell a variant from a regular product in the “Before update” tab.
What you get. For such a template, unless the HPM module is enabled in “Additional settings”, the app appends one more query to your “Before update” queries: UPDATE etrade_product_temp SET type_id = 1 WHERE type_id = 2. Each variant goes to the site as a separate product page. Your queries run before this line and still see type_id = 2.
What won't happen. In the “After update” tab, there are no products with type_id = 2 anymore: a type_id = 1 condition there won't filter out any variant, and type_id = 2 won't find any row.
What to do when something goes wrong
| Symptom | What to do |
|---|---|
| The log shows “Error! Need mode LOAD DATA LOCAL INFILE! Variable local_infile: OFF” | Data arrives at the site as a CSV file, and the module loads it into a temporary table with LOAD DATA LOCAL INFILE — which your hosting forbids. In php.ini, set mysqli.allow_local_infile = On; in my.cnf, add local-infile=ON to the [mysqld] and [mysql] sections, and restart MySQL. To check: SHOW VARIABLES LIKE 'local_infile' |
| “mysqli_query(): LOAD DATA LOCAL INFILE forbidden” | The same, but disabled on the PHP side: the mysqli.allow_local_infile parameter in php.ini is commented out with a semicolon — uncomment it and set it to On |
| The run fails with a mention of zip | The hosting lacks the PHP zip extension. Install it (php-zip) and restart the web server. The “Check Connection” button shows which extensions are installed |
| The query ran, but nothing changed on the site | Most often, a missing ;;; in the previous query: the batch merged into one line. The second cause: the query is in the “Before update” tab, but the data it touches appears on the site later |
| Products are duplicated with every run | row_exist wasn't set: either the site table has no uuid column, or it's empty for old records, or the matching query comes after the insert query |
| The run takes hours | The site tables have no index on uuid. Every join becomes a full scan |
| Each product variant became a separate product page | The update and insert queries lack AND t.type_id = 1 — option products went along with regular products |
| “Query sql_query is empty” | Queries go to the site in a single POST parameter, and it arrived empty. Almost always the cause is Suhosin, a PHP security extension on the site's hosting: it truncates POST values that are too long. Raise suhosin.post.max_value_length (and suhosin.request.max_value_length too) in the site's PHP settings. If you can't access them, temporarily reduce the size of one run by narrowing the category list — but that's a workaround, not a fix |
FAQ
Why are queries separated by three semicolons instead of one?
All the text goes to the site in one package, and the tunnel module splits it into separate queries, while a plain ; can also appear inside values. Since module version 3.2.0, the parser handles quotes and comments, and a plain ; is enough. The old ;;; still works.
Can I write comments in the field?
Since module version 3.2.0, yes. In older versions, line breaks are stripped before sending, and a comment “swallows” the rest of the query; there, write explanations outside the field.
Do my queries run in the Elbuz database or in the site database?
In your site database. Elbuz only passes the text to the tunnel module, and the module runs it with the permissions of the database user set in the connection settings. That's why tables in queries use the site prefix (oc_product), not the Elbuz prefix.
Can I roll back a wrong query?
No. Queries run in the live database without a transaction, and the interface has no undo. The only way back is a site database backup: the tunnel makes it for supported CMSs; in the “MySQL (SQL custom query)” mode, make it with your hosting tools.
I chose a CMS from the list. Can I still add my own queries?
Yes, and that's the most common use of the window. The driver does its part, and your queries from the “After update” tab run at the end — when the data on the site is already updated. You don't need to rewrite the whole scenario for this.
Where to find it and who has access
- Menu
- Data export → Elbuz Tunnel template card → “Additional” → “SQL queries”
- Access right
- a separate permission for the “SQL queries” window
- Prepare
- a configured tunnel, access to the site database, and knowledge of
Without the window permission, the form won't open, and an attempt to save a query shows a red message, “No access to tunnel SQL queries”. The text isn't saved.
Related topics
- For the big picture of the tunnel — How to export products to your site database.
- If you only need to put an Elbuz field into a non-standard site field — “Additional settings” without SQL.
- If the tunnel isn't connected yet — How to connect a site to Elbuz Tunnel.
- If you need to pull data from the site with SELECT queries — Importing a catalog through Elbuz Tunnel.
