How to fix ER_TABLE_MUST_HAVE_VISIBLE_COLUMN (4028): Table must have at least one visible column in MySQL

-- This will FAIL with ER_TABLE_MUST_HAVE_VISIBLE_COLUMN:
CREATE TABLE users (
    id INT PRIMARY KEY INVISIBLE,
    email VARCHAR(255) INVISIBLE,
    created_at TIMESTAMP INVISIBLE
);

-- This will SUCCEED - id is visible:
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) INVISIBLE,
    created_at TIMESTAMP INVISIBLE
);

-- Check which columns are currently visible
SELECT 
    COLUMN_NAME,
    EXTRA
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'your_database'
    AND TABLE_NAME = 'your_table'
    AND EXTRA NOT LIKE '%INVISIBLE%';

-- Example result showing visible columns:
-- +-------------+-------+
-- | COLUMN_NAME | EXTRA |
-- +-------------+-------+
-- | id          |       |
-- | username    |       |
-- +-------------+-------+

-- Safe to make username invisible since id will remain visible:
ALTER TABLE your_table 
    ALTER COLUMN username SET INVISIBLE;

-- This would FAIL if id is the only visible column:
ALTER TABLE your_table 
    ALTER COLUMN id SET INVISIBLE;

-- Explicitly set visibility for clarity
CREATE TABLE products (
    product_id INT PRIMARY KEY VISIBLE,
    product_name VARCHAR(255) VISIBLE,
    internal_notes TEXT INVISIBLE,
    debug_data JSON INVISIBLE,
    legacy_field VARCHAR(100) INVISIBLE
);

-- When altering columns, use SET VISIBLE to ensure visibility:
ALTER TABLE products 
    ALTER COLUMN product_id SET VISIBLE;

-- Multiple columns can be modified in one statement:
ALTER TABLE products 
    MODIFY COLUMN product_name VARCHAR(255) VISIBLE,
    MODIFY COLUMN internal_notes TEXT INVISIBLE;

-- BAD: Migration will fail
CREATE TABLE IF NOT EXISTS audit_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY INVISIBLE,
    user_id INT INVISIBLE,
    action VARCHAR(50) INVISIBLE,
    timestamp DATETIME INVISIBLE
);

-- GOOD: At least one column is visible
CREATE TABLE IF NOT EXISTS audit_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- Visible
    user_id INT INVISIBLE,
    action VARCHAR(50) INVISIBLE,
    timestamp DATETIME INVISIBLE
);

-- Or make multiple columns visible for better data access:
CREATE TABLE IF NOT EXISTS audit_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    timestamp DATETIME,
    debug_info JSON INVISIBLE,          -- Only debug fields invisible
    internal_flags VARCHAR(100) INVISIBLE
);

-- Check the current table structure
DESC your_table;

-- Or get detailed visibility information:
SHOW CREATE TABLE your_table;

-- Make a column visible using ALTER COLUMN:
ALTER TABLE your_table 
    ALTER COLUMN column_name SET VISIBLE;

-- Alternative syntax using MODIFY COLUMN:
ALTER TABLE your_table 
    MODIFY COLUMN column_name VARCHAR(255) VISIBLE;

-- Or using CHANGE COLUMN (allows renaming):
ALTER TABLE your_table 
    CHANGE COLUMN old_name new_name INT VISIBLE;

-- Verify the change:
SELECT COLUMN_NAME, EXTRA 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'your_table' 
ORDER BY ORDINAL_POSITION;