Introduction
A small shop is adding pickup points. Its older checkout supplies order ID, customer, integer cents and a note; a new checkout also supplies a pickup code. Existing orders and their item relationships must remain usable. A default keeps an omitted column compatible, but does not tell us where a historical customer actually chose to collect an order.
You will deliver expand.sql and an expanded live order table, use both prepared clients, then observe the real boundary between data transactions and MySQL DDL. Prerequisites are basic terminal navigation and MySQL SELECT/WHERE, INSERT/UPDATE, columns, primary keys and defaults. No Python or application development is required. This independent unit provides its own private Oracle MySQL and Terminal; the generic database service is unrelated.
Work only in /home/labex/project/pickup-desk. You may add the documented pickup column to orders, and update/extend only the isolated ddl_demo for the demonstration. Preserve all original order IDs, customers, amounts, notes, items, points and approval tables, plus the supplied tools. Only the three named practice orders below may be added. This is a controlled maintenance window, not an online, lock-free or zero-downtime migration. The author's time estimate includes reading and ordinary mistakes; it has not been timed with beginners.
Expand the schema while both clients keep working
In this step, you will create a normal legacy order, add a compatible pickup column, and read real orders created by both client versions.
Begin in Terminal, at the shell prompt. cd changes the current directory; the absolute path works from any starting directory. ./shop runs a prepared command in that directory. These are shell commands, not input at a MySQL prompt:
cd /home/labex/project/pickup-desk
./shop status
./shop points
The original headers are 101 / Sam / 2100, 102 / Sam / 3400 and 103 / Lee / 0, with their own notes and items. The two Sam orders are different identities. Amounts are integer cents, and zero is valid. status and show read actual stored values; they never migrate a record or substitute a pickup choice.
The prepared legacy client accepts old order_id customer total_cents sku quantity. It inserts a header using explicit old column names and an item in the same native transaction. A successful call commits; a native SQL error stops the batch and closes its connection, rolling back pending data changes. Create the allowed ordinary order, then read it:
./shop old 201 Mina 1600 MUG 1
./shop show 201
Its header contains ID 201, Mina, 1600 and Created by legacy client; its detail contains MUG, quantity 1. The returned pickup_column: false states that this field is not deployed. The new client has the same arguments followed by a pickup code. The next command is intentionally expected to fail with native error 1054, unknown column pickup_code, and nonzero exit status. It must not leave a header or detail behind:
./shop new 203 Pia 1900 CARD 2 garden
./shop show 203
The read shows empty header/items. An absent column is a schema error, not an unknown pickup code or a successful empty checkout.
Add the new nullable column with an explicit default of counter. ALTER TABLE changes a definition; ADD COLUMN adds the field. VARCHAR(16) stores a short pickup code. NULL temporarily permits an explicit unknown value during this expansion phase. DEFAULT 'counter' supplies the compatibility value when an old client omits the column. It also appears on preexisting rows after this expansion, so it is not proof of historical choice. The next independent lab handles approved history before final rules are tightened.
Save a complete SQL file. cat > expand.sql replaces this one file. The quoted here-document marker 'SQL' makes the shell copy the lines literally until the final standalone SQL. A semicolon ends each SQL statement:
cat > expand.sql <<'SQL'
ALTER TABLE orders ADD COLUMN pickup_code VARCHAR(16) NULL DEFAULT 'counter';
SQL
apply runs the saved file unchanged in one native MySQL batch connection with the course migration role. It does not infer, backfill or repair missing SQL:
./shop apply expand.sql
./db -e "SHOW COLUMNS FROM orders;"
./db connects to this course's private database; -e executes the following quoted SQL and exits. The double quotes keep spaces and the semicolon inside one shell argument. Read the pickup column's type, nullable status and default: varchar(16), YES, counter. Other original fields remain present.
Use the legacy client again, and retry the previously rejected new order:
./shop old 202 Omar 1800 BAG 1
./shop new 203 Pia 1900 CARD 2 garden
./shop show 201
./shop show 202
./shop show 203
./shop status
Orders 201 and 202 contain the actual stored default counter; 203 contains garden. All headers and real item rows remain. The original three orders now display counter as the expansion default; do not claim that every historical order was collected there. The prepared approval map remains untouched.
For an ordinary ./shopp typo, run ls and use ./shop with the correct name. If SQL reports a syntax error, inspect cat expand.sql, correct the complete saved statement, and apply it only if the column is still absent. Error 1060 on a repeated expansion means the column already exists; inspect SHOW COLUMNS instead of dropping it or resetting the database. Repeating a successful client order produces a duplicate-ID error: use show for the existing order, rather than inventing another ID. Keep the named allowed orders and original data.
Observe what a rollback cannot undo
In this step, you will observe a native ALTER boundary on a separate demonstration table while keeping the order system intact.
Continue in Terminal, in /home/labex/project/pickup-desk. ddl_demo is an explicitly disposable one-row teaching table, separate from all orders. Its initial note is original:
./db -e "SELECT * FROM ddl_demo;"
A transaction groups data changes until COMMIT or ROLLBACK. START TRANSACTION begins one; UPDATE changes the selected row; ROLLBACK normally discards its uncommitted data changes. MySQL DDL (data definition language), including ALTER TABLE, has an implicit commit boundary. ALTER commits preceding work before changing the definition and is not undone by a later user ROLLBACK. Atomic DDL recovery is not the same as making user DDL rollbackable.
The following file deliberately changes only this demo note before adding a nullable extra integer column. It then requests ROLLBACK. Predict whether the note and new column will remain before running it:
cat > ddl-demo.sql <<'SQL'
START TRANSACTION;
UPDATE ddl_demo SET note='committed before ALTER' WHERE id=1;
ALTER TABLE ddl_demo ADD COLUMN extra INT NULL;
ROLLBACK;
SQL
./shop apply ddl-demo.sql
./db -e "SELECT * FROM ddl_demo;"
./db -e "SHOW COLUMNS FROM ddl_demo;"
The row still has note committed before ALTER, and extra is NULL. SHOW COLUMNS still lists extra. The earlier data UPDATE was committed by ALTER, and the new definition also survives. ROLLBACK returning successfully did not undo them. This is actual server behavior, not a fabricated output file.
Read the real orders again:
./shop show 101
./shop show 201
./shop show 203
Their original header values, notes, items and stored pickup codes remain. Never perform this demonstration on an order note. A real migration separates a transactional data backfill from later tightening DDL and plans its maintenance/repair boundary accordingly; placing every ALTER inside BEGIN does not provide automatic rollback.
The demo file is a one-time script from the stated initial definition. If you accidentally run it twice, extra already exists. Inspect the actual demo definition and row; do not drop it to make a second run appear fresh. Restore a mistyped file by copying its complete block before the first successful apply. This unit ends with compatible expansion and an honest DDL observation, not a claim of deployment under continuous production traffic.
Summary
You delivered a compatible order-table expansion, observed real old/new client writes, and preserved original order details. An isolated native experiment showed why a successful ROLLBACK cannot undo ALTER or its preceding committed data change. Historical pickup choices still require the approved mapping taught next.
