> ## 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 |
+------------------+-------------------+
| vsql_complex     | 0.0.1             |
| vsql_uuid        | 0.0.3             |
+------------------+-------------------+
```

**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:** `Extension 'my_extension' not found`

**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 Name Validation Errors

**Error:** `Failed to load VEF extension 'extension_name'` with log message `Extension name mismatch`

**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](/mysql-8.4/0.0.5/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;
```

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
```

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

Custom types require ROW format binlog. STATEMENT and MIXED modes are not
supported for tables with custom type columns. 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="/mysql-8.4/0.0.5/reference">
    Query system tables and views
  </Card>

  <Card title="Uninstall Extensions" icon="trash" href="/mysql-8.4/0.0.5/uninstall">
    Remove extensions safely
  </Card>

  <Card title="Extension Architecture" icon="sitemap" href="/mysql-8.4/0.0.5/architecture">
    Understand the internals
  </Card>
</CardGroup>