MySQL 5.7只读表无唯一行标识符问题求助

Hey there! I’ve run into this exact snag with MySQL imports before, so let’s get your sources and entries tables back to writable shape quickly.

What’s Causing the Read-Only Lock? #

That warning spells it out clearly: your tables don’t have a primary key or a NOT NULL unique index. Most database tools (including custom programs like yours) rely on these unique identifiers to safely handle write, update, or delete operations—without them, they lock the table as read-only to avoid data corruption or messy inconsistencies.

Step-by-Step Fixes #

1. First, Check Your Table Structure

Let’s map out what fields you have to work with. Run these commands in your MySQL client:

DESC sources;
DESC entries;

Look for fields that are already unique (like a twitter_id, entry_id, or auto-incrementing id column) that can serve as your unique identifier.

2. Add a Primary Key (Recommended)

If you have an existing field that’s meant to be unique (e.g., an id column), turn it into a primary key:

-- Fix the sources table
ALTER TABLE sources ADD PRIMARY KEY (id);

-- Fix the entries table
ALTER TABLE entries ADD PRIMARY KEY (id);

If there’s no existing unique field, add a new auto-incrementing primary key:

-- Add auto-increment ID to sources
ALTER TABLE sources ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

-- Add auto-increment ID to entries
ALTER TABLE entries ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

3. Use a NOT NULL Unique Index (Alternative)

If you don’t want an auto-increment ID, but have a field that’s naturally unique (like twitter_handle or entry_uuid), modify it to be NOT NULL and add a unique index:

-- For sources (using twitter_id as an example)
ALTER TABLE sources MODIFY COLUMN twitter_id VARCHAR(255) NOT NULL;
ALTER TABLE sources ADD UNIQUE INDEX idx_sources_twitter_id (twitter_id);

-- For entries (using entry_uuid as an example)
ALTER TABLE entries MODIFY COLUMN entry_uuid VARCHAR(36) NOT NULL;
ALTER TABLE entries ADD UNIQUE INDEX idx_entries_entry_uuid (entry_uuid);

4. Verify the Fix

Run DESC sources; and DESC entries; again—you should see the primary key marked with PRI or the unique index listed. Then test your custom program: it should now let you write to the tables without issues.

Prevent This in the Future #

When exporting tables from other schemas, make sure to include the full table structure (including primary keys/unique indexes) instead of just raw data. Most export tools (like mysqldump) do this by default, but double-check your export command to avoid missing these critical constraints next time.

内容的提问来源于stack exchange，提问作者devnullius