> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Managing Extensions

> Monitor, troubleshoot, and manage VillageSQL extensions

## Viewing Installed Extensions

Query installed extensions using the INFORMATION\_SCHEMA view:

```sql theme={null}
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;
```

**Output:**

```
+----------------+-------------------+-----------------+----------------------+--------------------+-----------------------+
| EXTENSION_NAME | EXTENSION_VERSION | PENDING_VERSION | PENDING_REQUESTED_AT | PENDING_LAST_ERROR | PENDING_LAST_ERROR_AT |
+----------------+-------------------+-----------------+----------------------+--------------------+-----------------------+
| vsql_complex   | 0.0.1             | NULL            | NULL                 | NULL               | NULL                  |
| vsql_uuid      | 0.0.3             | NULL            | NULL                 | NULL               | NULL                  |
+----------------+-------------------+-----------------+----------------------+--------------------+-----------------------+
```

**Usage:**

* Use in both interactive sessions and scripts
* Standard SQL interface compatible with MySQL tools

***

## Checking Extension Functions

Verify extension functions work after installation:

```sql theme={null}
-- Test a function directly
SELECT complex_abs('(1.0,2.0)');
```

***

## Extension Directory

Check where VillageSQL looks for `.veb` files:

```sql theme={null}
SHOW VARIABLES LIKE 'veb_dir';
```

**List available extensions:**

```bash theme={null}
ls -la /path/to/veb_dir/*.veb
```

### Configuring veb\_dir

To change the extension directory location, set `veb_dir` in your MySQL configuration file:

**my.cnf / my.ini:**

```ini theme={null}
[mysqld]
veb_dir=/custom/path/to/extensions/
```

**Requirements:**

* Path must be absolute (not relative)
* Directory must exist before server start
* MySQL user must have read permissions on the directory
* Only one `veb_dir` is supported (cannot have multiple paths)
* Changes require server restart to take effect

**Verify after restart:**

```sql theme={null}
SHOW VARIABLES LIKE 'veb_dir';
```

***

## Troubleshooting

### Quick Reference

| Issue                                     | Quick Fix                                                                    |
| ----------------------------------------- | ---------------------------------------------------------------------------- |
| Extension not found                       | Verify `.veb` file exists in veb\_dir with correct name                      |
| Permission denied                         | Check permissions: `chmod 644 extension.veb`                                 |
| Cannot uninstall: types in use            | Attempt `UNINSTALL EXTENSION`; the error identifies blocking columns by name |
| Version mismatch                          | Restart server to clear cache                                                |
| Extension shows old behavior after update | `UNINSTALL` then `INSTALL`; clear `.veb_expansion_cache/` if needed          |
| Cannot compare types X and Y              | Both sides must use the same custom type                                     |
| Unable to implicitly cast non-custom type | The literal or column being compared is not compatible with the custom type  |

### Extension Not Found

**Error:** `ERROR 3219 (HY000): VEB file not found: my_extension.veb`

**Debug steps:**

```bash theme={null}
# 1. Check veb_dir location
mysql -u root -p -e "SHOW VARIABLES LIKE 'veb_dir';"

# 2. List .veb files
ls -la /path/to/veb_dir/

# 3. Verify filename matches extension name
# File: my_extension.veb
# Install: INSTALL EXTENSION my_extension;

# 4. Check permissions
ls -l /path/to/veb_dir/my_extension.veb
sudo chmod 644 /path/to/veb_dir/my_extension.veb
```

### Function Not Available After Install

**Error:** `FUNCTION my_func does not exist`

**Debug steps:**

```sql theme={null}
-- 1. Verify extension installed
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS WHERE EXTENSION_NAME = 'my_extension';
```

### Extension Shows Old Behavior After Update

**Symptom:** After replacing a `.veb` file and reinstalling, the extension still
runs old code.

**Cause:** VillageSQL expands `.veb` files into `{datadir}/.veb_expansion_cache/` on first load. If you
copy a new `.veb` without first running `UNINSTALL EXTENSION`, the server continues
using the previously-expanded `.so` that is already loaded in memory.

**Solution:** Always follow the full UNINSTALL → replace → INSTALL cycle:

```sql theme={null}
UNINSTALL EXTENSION my_extension;
```

Then replace the `.veb` file in `veb_dir` and reinstall:

```sql theme={null}
INSTALL EXTENSION my_extension;
```

If the extension still shows old behavior, clear the expansion cache before reinstalling:

```bash theme={null}
rm -rf {datadir}/.veb_expansion_cache/my_extension/
```

```sql theme={null}
INSTALL EXTENSION my_extension;
```

***

### Cannot Uninstall Extension

**Error:** `Cannot uninstall extension: types in use`

**Solution:**

```sql theme={null}
-- Attempt uninstall; the error identifies blocking columns by name
UNINSTALL EXTENSION my_extension;
-- If blocked: ERROR HY000: Cannot drop extension `my_extension` as 1 column(s) depend on it,
--             e.g. mydb.mytable.my_column has type MYTYPE

-- Drop or alter the identified column(s), then retry
DROP TABLE mydb.mytable;
-- OR
ALTER TABLE mydb.mytable DROP COLUMN my_column;

UNINSTALL EXTENSION my_extension;
```

### Library Loading Errors

**Error:** `Cannot load library: undefined symbol`

**Causes:**

* Missing library dependencies
* ABI compatibility mismatch
* Incorrect MySQL version

**Debug:**

```bash theme={null}
# Check library dependencies (Linux)
ldd {datadir}/.veb_expansion_cache/my_extension/<sha256>/lib/my_extension.so

# Check library dependencies (macOS)
otool -L {datadir}/.veb_expansion_cache/my_extension/<sha256>/lib/my_extension.so
```

### Extension Load Errors

**Error:** `Failed to load VEF extension 'extension_name'`

**Cause:** The `.veb` file failed to load at the entry-point stage — for
example a missing or invalid `vef_register` symbol, or a corrupt `.veb` file.

### Extension Name Validation Errors

**Error:**

```text theme={null}
ERROR 3219 (HY000): Manifest name 'manifest_name' does not match VEB basename 'filename'
```

**Cause:** The extension name in `manifest.json` doesn't match the VEB filename.

**Debug steps:**

1. **Check VEB filename matches manifest:**
   ```bash theme={null}
   # VEB filename: my_extension.veb
   # manifest.json should have:
   {
     "name": "my_extension",  # Must match VEB filename (without .veb)
     ...
   }
   ```

2. **Verify manifest.json name field:**
   ```bash theme={null}
   # Extract and check manifest from VEB
   tar -xOf /path/to/veb_dir/my_extension.veb manifest.json | grep name
   ```

**Solution:**

Both names must be identical, using underscores (see [Extension Naming Conventions](/docs/mysql-9.7/dev/install#extension-naming-conventions)):

* VEB filename: `my_extension.veb`
* manifest.json: `"name": "my_extension"`

**Common mistakes:**

* Using hyphens in manifest: `"name": "my-extension"` ❌
* VEB filename doesn't match: `my-extension.veb` vs `"name": "my_extension"` ❌

**Correct example:**

```json theme={null}
// manifest.json
{
  "name": "my_extension",
  "version": "1.0.0"
}
```

```cpp theme={null}
// extension.cc
VEF_GENERATE_ENTRY_POINTS(
  make_extension()
    .func(...)
)
```

```bash theme={null}
# VEB filename
my_extension.veb
```

### Custom Type Comparison Errors

**Error:** `Cannot compare types X and Y in =`

**Cause:** Both sides of the comparison are custom types but from different types or extensions.

```sql theme={null}
-- Example: comparing COMPLEX with UUID in a WHERE clause
SELECT * FROM t WHERE complex_col = uuid_col;
-- ERROR: Cannot compare types vsql_complex.COMPLEX and vsql_uuid.uuid in =
```

**Solution:** Ensure both sides of a comparison use the same custom type. If you need to compare across types, convert one side explicitly using the appropriate type conversion function.

***

**Error:** `Unable to implicitly cast a non-custom type during compare with a custom type in =`

**Cause:** One side of the comparison is a custom type column and the other is a value (literal or column) that cannot be automatically converted to that type.

```sql theme={null}
-- Example: comparing a custom type with an integer literal
SELECT * FROM t WHERE complex_col = 42;
-- ERROR: Unable to implicitly cast a non-custom type during compare...
```

**Solution:** String literals are automatically cast to the custom type using the type's encode function. For other types (integers, floats), use an explicit conversion function:

```sql theme={null}
-- Use a string literal instead (auto-cast works)
SELECT * FROM t WHERE complex_col = '(1.0,2.0)';

-- Or use the type's from_string method explicitly
SELECT * FROM t WHERE complex_col = COMPLEX::from_string('(1.0,2.0)');
```

***

## Monitoring Extension Usage

### Query Performance

Track VDF execution times using performance\_schema:

```sql theme={null}
-- Enable statement instrumentation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement%';

-- Query VDF execution times
SELECT
    DIGEST_TEXT,
    COUNT_STAR as executions,
    ROUND(SUM_TIMER_WAIT/1000000000, 2) as total_ms,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%complex_%'
ORDER BY total_ms DESC
LIMIT 10;
```

### Custom Type Usage

Track which tables use custom types:

```sql theme={null}
-- Find all columns using custom extension types
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%.%'
ORDER BY DATA_TYPE, TABLE_SCHEMA, TABLE_NAME;
```

***

## Updating Extensions

To change an installed extension to a different version, use `ALTER EXTENSION`.
It validates the new version against your existing data and applies the change
the next time the server restarts. A manual uninstall and reinstall remains
available as an alternative.

### Changing an Extension Version

`ALTER EXTENSION` changes an installed extension to a different version,
applied at the next server restart:

```sql theme={null}
ALTER EXTENSION update_test VERSION '1.2.0' AT RESTART;
```

<Note>
  `ALTER EXTENSION` requires the `EXTENSION_ADMIN` privilege (or `SUPER` as a
  fallback), the same gate as `INSTALL EXTENSION` and `UNINSTALL EXTENSION`. See
  [Required Privilege](/docs/mysql-9.7/dev/install#required-privilege).
</Note>

VillageSQL resolves the target version on disk and runs a compatibility
precheck before accepting the change. The target VEB must exist in the
extension directory as `<name>-<version>.veb` (here, `update_test-1.2.0.veb`);
a missing file is rejected:

```
ERROR 3219 (HY000): VEB file not found: update_test-9.9.9.veb
```

The precheck looks for known incompatibilities that would break stored data —
for example a change to a custom type's persisted length:

```
ERROR 3219 (HY000): Cannot update extension 'update_test': type 'COUNTER'
persisted_length changed from 4 to 8 -- existing stored data would be corrupted
```

A second precheck rejects a version that would drop a custom type still in
use by a table column:

```
ERROR 3219 (HY000): Cannot update extension 'update_test': type 'COUNTER' is
being dropped but column mydb.mytable.mycol depends on it
```

Once accepted, the change applies at the next restart. Until then,
`INFORMATION_SCHEMA.EXTENSIONS` reports the current version alongside the
target it will change to:

```sql theme={null}
SELECT EXTENSION_NAME, EXTENSION_VERSION, PENDING_VERSION
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'update_test';
```

```
+----------------+-------------------+-----------------+
| EXTENSION_NAME | EXTENSION_VERSION | PENDING_VERSION |
+----------------+-------------------+-----------------+
| update_test    | 1.0.0             | 1.2.0           |
+----------------+-------------------+-----------------+
```

`INFORMATION_SCHEMA.EXTENSIONS` reports a scheduled change in four columns:

| Column                  | Meaning                                                             |
| ----------------------- | ------------------------------------------------------------------- |
| `PENDING_VERSION`       | Version the extension will change to at the next restart, or `NULL` |
| `PENDING_REQUESTED_AT`  | When the change was requested                                       |
| `PENDING_LAST_ERROR`    | Message from a failed change, or `NULL`                             |
| `PENDING_LAST_ERROR_AT` | When that failure was recorded, or `NULL`                           |

One scheduled change is tracked at a time:

* **Cancel a scheduled change** by requesting the current version again:
  ```sql theme={null}
  ALTER EXTENSION update_test VERSION '1.0.0' AT RESTART;
  -- Note: Cleared pending update for extension 'update_test'
  --       (target matches current version '1.0.0')
  ```
* **Requesting the current version when nothing is scheduled** does nothing:
  ```sql theme={null}
  ALTER EXTENSION update_test VERSION '1.0.0' AT RESTART;
  -- Note: Extension 'update_test' is already at version '1.0.0'
  ```
* **A different target is refused while a change is scheduled** — cancel the
  existing one first:
  ```
  ERROR 3219 (HY000): Extension 'update_test' already has a pending update;
  clear it with ALTER EXTENSION update_test VERSION '1.0.0' AT RESTART before
  queueing a different version
  ```

The `AT RESTART` clause applies the change during the next server startup.

### After a Successful Restart

On the next restart the pending change applies: `EXTENSION_VERSION` becomes the
target and `PENDING_VERSION` clears. For the change scheduled above
(`update_test` `1.0.0` → pending `1.2.0`):

```sql theme={null}
SELECT EXTENSION_NAME, EXTENSION_VERSION, PENDING_VERSION
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'update_test';
```

```
+----------------+-------------------+-----------------+
| EXTENSION_NAME | EXTENSION_VERSION | PENDING_VERSION |
+----------------+-------------------+-----------------+
| update_test    | 1.2.0             | NULL            |
+----------------+-------------------+-----------------+
```

Any `custom_columns` and stored-procedure-parameter rows referencing the old
version are rewritten during the same restart, so dependent tables and routines
keep working with no manual migration.

<Note>
  The change applies during restart, not on a live connection — the values above
  are the post-restart state.
</Note>

### Recovering from a Pending Version Change at Startup

A scheduled version change is applied during the next server startup. If a
pending action can't be applied, the server can fail to start while persisting
its pending-update decisions. Use `--villagesql-skip-extension-updates` when a
queued `ALTER EXTENSION ... AT RESTART` is blocking startup and you need the
server up before you can clear it.

`--villagesql-skip-extension-updates` is a `mysqld` startup flag. When set, the
server bypasses processing of pending `ALTER EXTENSION ... AT RESTART` actions:
each extension loads at its currently-installed version, and its pending action
is left intact on disk. The flag mutates nothing itself — it only skips applying
the pending actions for that one startup.

When the flag is set and at least one extension has a pending action, the server
logs a single warning at startup naming how many were bypassed:

```text theme={null}
--villagesql-skip-extension-updates is set: bypassing N pending extension update(s). Extensions load at their currently-installed version. Query INFORMATION_SCHEMA.EXTENSIONS to see the pending actions; clear each via ALTER EXTENSION <name> VERSION '<current>' AT RESTART, then restart without the flag.
```

<Steps>
  <Step title="Start the server with the flag">
    In production, pass it as a normal `mysqld` command-line option or add it
    under `[mysqld]` in `my.cnf`:

    ```bash theme={null}
    mysqld --villagesql-skip-extension-updates
    ```

    In the dev harness, pass it through with `--`:

    ```bash theme={null}
    ./villagesql start -- --villagesql-skip-extension-updates
    ```

    **Success signal:** the server starts, and the error log contains the
    `bypassing N pending extension update(s)` warning shown above.
  </Step>

  <Step title="Identify the pending actions">
    ```sql theme={null}
    SELECT EXTENSION_NAME, EXTENSION_VERSION, PENDING_VERSION, PENDING_LAST_ERROR
    FROM INFORMATION_SCHEMA.EXTENSIONS
    WHERE PENDING_VERSION IS NOT NULL;
    ```

    ```text theme={null}
    +--------------+-------------------+-----------------+--------------------+
    | EXTENSION_NAME | EXTENSION_VERSION | PENDING_VERSION | PENDING_LAST_ERROR |
    +--------------+-------------------+-----------------+--------------------+
    | my_extension   | 1.0.0             | 2.0.0           | NULL               |
    +--------------+-------------------+-----------------+--------------------+
    ```

    **Success signal:** each row's `PENDING_VERSION` is the target you need to
    clear; `PENDING_LAST_ERROR` shows why it failed to apply.
  </Step>

  <Step title="Clear each pending action">
    For every extension returned above, request its current version to cancel
    the queued change (the cancel mechanism described under
    [Changing an Extension Version](#changing-an-extension-version)):

    ```sql theme={null}
    ALTER EXTENSION my_extension VERSION '1.0.0' AT RESTART;
    ```

    **Success signal:** a `Cleared pending update for extension '<name>'
            (target matches current version '<current>')` note is returned, and
    re-running the previous query shows `PENDING_VERSION` is `NULL`.
  </Step>

  <Step title="Restart without the flag">
    Restart the server normally, omitting `--villagesql-skip-extension-updates`.

    **Success signal:** the server starts and the
    `bypassing N pending extension update(s)` warning no longer appears in the
    log.
  </Step>
</Steps>

### Manual Update Process

1. **Uninstall the current version:**
   ```sql theme={null}
   UNINSTALL EXTENSION extension_name;
   ```

2. **Replace the .veb file:**
   ```bash theme={null}
   # Remove old .veb file
   sudo rm /path/to/veb_dir/extension_name.veb

   # Copy new .veb file
   sudo cp new_extension_name.veb /path/to/veb_dir/
   ```

3. **Install the new version:**
   ```sql theme={null}
   INSTALL EXTENSION extension_name;
   ```

4. **Verify the update:**
   ```sql theme={null}
   SELECT EXTENSION_VERSION
   FROM INFORMATION_SCHEMA.EXTENSIONS
   WHERE EXTENSION_NAME = 'extension_name';
   ```

<Warning>
  **Data Safety:** If tables use custom types from the extension, you must drop or alter those tables before uninstalling. Back up your data first.
</Warning>

**Example:**

```sql theme={null}
-- Find columns using vsql_complex types before updating
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE 'vsql_complex.%';

-- If columns exist, back up data and drop/alter them first
-- Then proceed with update
UNINSTALL EXTENSION vsql_complex;
-- (replace .veb file)
INSTALL EXTENSION vsql_complex;
```

***

## Cleaning Up

### Remove Orphaned Expansion Directories

VillageSQL expands `.veb` files to `{datadir}/.veb_expansion_cache/{name}/{sha256}/`. Old versions accumulate over time.

```bash theme={null}
# List expansion directories (replace {datadir} with your actual datadir path)
ls -la {datadir}/.veb_expansion_cache/

# Compare with installed extensions
mysql -u root -p -e "SELECT EXTENSION_NAME FROM INFORMATION_SCHEMA.EXTENSIONS;"

# Find the actual SHA256 directory name for a specific extension
ls {datadir}/.veb_expansion_cache/my_extension/

# Remove the unused SHA256 directory using the name shown above
rm -rf {datadir}/.veb_expansion_cache/my_extension/<sha256-from-ls>/
```

<Note>
  Server restart automatically cleans up orphaned expansion directories.
</Note>

***

## Replication

ROW format binlog is the recommended setting for custom types. INSERT, UPDATE,
DELETE, and ALTER TABLE operations on custom type columns all replicate
correctly in ROW format.

`INSTALL EXTENSION` is not replicated — each server manages its own extensions.
Install the extension on every replica before replication starts, using the same
version as the source. The server enforces exact version matching; a version
mismatch stops replication.

If a replica encounters a custom type it doesn't recognize, replication stops at
the DDL statement — on `CREATE TABLE` or `ALTER TABLE`, before any dependent DML
is applied. Install the correct extension version, then resume:

```sql theme={null}
INSTALL EXTENSION my_extension;
START REPLICA SQL_THREAD;
```

`mysqldump` preserves fully qualified custom type names in the output. Logical
restores work as long as the extension is installed on the target server before
importing the dump.

<Warning>
  Behavior with the Clone plugin, XtraBackup, and InnoDB Cluster / Group
  Replication is not yet tested. Test your restore path before relying on it
  in production.
</Warning>

***

## Using Extensions with Docker

When running VillageSQL in Docker, mount a local directory as `veb_dir` so you can add `.veb` files from the host without rebuilding the container.

**Docker Compose example:**

```yaml theme={null}
services:
  villagesql:
    image: villagesql/server:stable
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    ports:
      - "3306:3306"
    volumes:
      - ./extensions:/usr/lib/veb
    command: --veb_dir=/usr/lib/veb
```

Copy a `.veb` file into `./extensions/` on the host, then install from SQL:

```sql theme={null}
INSTALL EXTENSION my_extension;
```

To verify the directory the running server is using:

```sql theme={null}
SHOW VARIABLES LIKE 'veb_dir';
```

***

## Getting Help

If you encounter issues not covered here:

1. **Check Error Log:** Most extension errors are logged with details
2. **Review Extension Docs:** Extension-specific troubleshooting may exist
3. **Ask on Discord:** Join the [VillageSQL Discord](https://discord.gg/KSr6whd3Fr)
4. **File an Issue:** Report bugs on [GitHub Issues](https://github.com/villagesql/villagesql-server/issues)

***

## Next Steps

<CardGroup cols={2}>
  <Card title="System Reference" icon="book" href="/docs/mysql-9.7/dev/reference">
    Query system tables and views
  </Card>

  <Card title="Uninstall Extensions" icon="trash" href="/docs/mysql-9.7/dev/uninstall">
    Remove extensions safely
  </Card>

  <Card title="Extension Architecture" icon="sitemap" href="/docs/mysql-9.7/dev/architecture">
    Understand the internals
  </Card>
</CardGroup>