GoAdmio

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

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:

[!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:


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