↑↓ selectEnter openEsc close

    1. Elbuz

    Updating your site with your own SQL queries through Elbuz Tunnel

    17 min readUpdated

    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

    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.

    TabWhen 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.

    The SQL queries window of an Elbuz Tunnel template with the update tabs and several UPDATE queries
    1Update tabs
    2;;; separator
    1. Tabs — queries run after the update, before the update, or open a link.
    2. 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.

    The main fork is whether the field contains “http”. The URL branch leaves no trace in the log; the script branch writes to the log and can stop the run
    YesNoNoYesYesNoYesNoDid the site update and your queriesfinish without errors?Is the “Open link”field filled in?The URLs and the scriptare not calledThe run continuesDoes the field textcontain “http”?Each line is a separate URL.Elbuz requests itand waits up to 15 secondsThe response isn't checked,nothing is written to the logLog: “Run PHP script(webhook) after update”The tunnel module runsthe script on the hostingDid the modulereturn an error?“Export stopped!”
    • 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:

    1. data is loaded into the temporary tables etrade_*_temp;
    2. “Running SQL before updating” — your “Before update” tab;
    3. “Updating site” — the CMS driver moves the data to the working tables;
    4. photos are downloaded from the cloud to the hosting;
    5. “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.

    Where exactly in the run your queries execute. The yellow diamond is the fork: in the custom queries mode there's no “Updating site” step at all
    Supported CMS“MySQL (SQL customquery)”Data is loaded into temporarytables etrade_*_temp“Running SQLbefore updating”Which targetis in the template?“Updating site”: the CMS drivermoves data to the working tablesThis step doesn't exist —you update the working tablesyourselfPhotos are downloadedfrom the cloud to the hosting“Executing SQL queriesafter update”
    • 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

    SymptomWhat 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 zipThe 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 siteMost 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 runrow_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 hoursThe site tables have no index on uuid. Every join becomes a full scan
    Each product variant became a separate product pageThe 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

    Was this article helpful?

    Missing a feature? Tell us.

    We will add it for free if other users need it too.

    If only you need it, we will agree on it separately.

    Article contents

    Suggest an improvement

    Try Elbuz free