reid
Renumber a table's first column as a clean 1, 2, 3… auto-increment primary key by dropping and re-creating it, which changes every row's ID
- Command:
/go/reid - Category: Databases
- Required arguments:
<database>and<table> - Output: Plain text
- Last updated: 2026-11-21
On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also
Overview
After many rows are deleted, an auto-increment ID column has gaps (1, 2, 7, 8, 20…). /go/reid closes the gaps by giving every row a new ID, counting up from 1 with no gaps.
/go/reid rebuilds the ID column of one table. It:
- reads the MariaDB root password from GoAdmio's
config.php - checks that the table exists
- takes the table's first column as the ID column
- drops that column, then adds it back as
INT NOT NULL AUTO_INCREMENTand makes it the primary key
[!WARNING] Every row gets a new ID, and the first column's old values are lost. Anything that refers to the old IDs (other tables, links, application code) will point to the wrong rows. The command also assumes the first column is the ID. If it isn't, that column's data is deleted. Take a backup first (see Before you run it).
Quick start
# Renumber the ID column of the "orders" table in the "shop" database
/go/reid shop orders
Syntax
/go/reid <database> <table>
| Form | Behavior |
|---|---|
/go/reid <database> <table> |
Renumbers the first column of <database>.<table> from 1 upward. |
There are no --option flags. Both arguments are required and must be given in this order. The names are put into the SQL without quoting, so a name with a hyphen or other special character fails.
Options
| Argument | Required | Default | Notes |
|---|---|---|---|
<database> |
Yes | — | The MariaDB/MySQL database name, as shown by SHOW DATABASES. |
<table> |
Yes | — | The table to renumber. Its first column must be the ID. |
Output
| Outcome | What you get |
|---|---|
| Table found | Re-sorting <column> Column in <table> (<database> Database)... |
| Table not found, or the check query failed | Table <database>.<table> doesn't exist. and exit status 1. |
| A step fails | The MariaDB error message. The command does not check for it and does not stop. |
| Finished | No message. |
Notes on the output:
- There is no success message. If you see no error after the
Re-sortingline, both steps ran. Table ... doesn't exist.is printed for any failure of the first query, including a wrong password inconfig.phpor MariaDB being down, because the real error is hidden.- Every run is logged with a timestamp and its arguments to
/var/log/go/reid.log.
Examples
# Check the table's columns and keys first. The first column listed must be the ID.
mariadb -uroot -p -e "DESCRIBE shop.orders;"
# Back up the table, then renumber it
mariadb-dump -uroot -p shop orders > /root/shop-orders.sql
/go/reid shop orders
# Confirm the result
mariadb -uroot -p -e "SELECT MIN(id), MAX(id), COUNT(*) FROM shop.orders;"
After a successful run, MAX(id) equals COUNT(*).
Before you run it
| Check | Why |
|---|---|
| The first column is the auto-increment ID. | The command drops the first column, whatever it is. |
| Nothing else uses the old IDs. | Foreign keys, other tables, URLs, and application records won't match the new IDs. |
| You have a backup of the table. | The change can't be undone. Use mariadb-dump, or copytable, copydb, or backup. |
| The site that uses the table is quiet. | The table is rebuilt twice, which takes time on a large table and can block writes. |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
Table <db>.<table> doesn't exist. |
Wrong database or table name, a name with special characters, a wrong root password in config.php, or MariaDB not running. |
Check the names with SHOW TABLES, and test the connection with mariadb -uroot -p. |
Multiple primary key defined |
The primary key is on a different column, so the first column was dropped but could not be re-added. | The column is gone. Restore the table from your backup. |
| A foreign key error while dropping | Another table references the column. | Nothing changed, because the later step then fails with a duplicate-column error. Remove or handle the foreign key first. |
| The first column's data is gone | It was not the ID column. | Restore the table from your backup. |
The ID column is now a signed INT |
The column is always re-created as INT NOT NULL AUTO_INCREMENT, so BIGINT, UNSIGNED, comments, and defaults are lost. |
Restore the type with ALTER TABLE ... MODIFY. |
| Indexes on the ID column are missing | Dropping the column also drops its indexes. Only the primary key is re-created. | Re-create the indexes. |
| Links or related records are wrong afterwards | IDs changed. | Restore the table from your backup, or update everything that refers to the old IDs. |
| The command is slow and the site hangs | Large table rebuilt twice. | Run it during a quiet period. |
Details
What happens on each run
flowchart TD
A["reid database table"] --> B["Log the call, read the root password from config.php"]
B --> C{"select count(*) works?"}
C -- "No" --> X["Print: Table doesn't exist, exit 1"]
C -- "Yes" --> D["List the table's columns"]
D --> E["First column = ID column"]
E --> F["Print: Re-sorting ..."]
F --> G["ALTER TABLE: DROP the ID column"]
G --> H["ALTER TABLE: ADD ID column INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY"]
The two SQL statements
ALTER TABLE <database>.<table> DROP <id column>;
ALTER TABLE <database>.<table> ADD <id column> INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (<id column>);
These are two separate statements, and MariaDB commits each one immediately. If the second fails, the table stays without its ID column.
How the new IDs are assigned
MariaDB numbers the rows 1, 2, 3… in the order it reads them from the table. That is usually the old ID order or the order the rows were inserted, but it is not guaranteed. The next ID that will be generated is one more than the number of rows. On an empty table, the counter starts again at 1.
Which column is the ID
The command reads the table's column names from INFORMATION_SCHEMA.COLUMNS and treats the first one as the ID. It does not check that the column is a primary key or auto-increment.
Security
The root password is read from GoAdmio's config.php and passed to mariadb on the command line, so it can be visible in the process list while the command runs.
See also
| Command | Description |
|---|---|
copytable |
Copy a MySQL/MariaDB table to a remote server, optionally into a different database name |
copydb |
Copy a database into a new database and user for a hosting account |
mysqlbackup |
Back up MariaDB/MySQL to a remote server via rsync |
backup |
Back up one or all GoAdmio accounts |
repair |
Repair the tables of one database, or of all databases |
index |
Add missing indexes to MariaDB/MySQL columns found in logged queries |
mysql |
Create, edit, or delete a database and user for a hosting account |