How to handled DateTime Fields in MySQL when updating from MySQL 5 to MySQL 8 - hmislk/hmis GitHub Wiki

-- ============================================ -- FIX FOR APPROVEAT COLUMN IN roseth.BILL -- MySQL 5 to MySQL 8 Upgrade Fix -- ============================================

-- Step 1: Create backup column ALTER TABLE roseth.BILL ADD COLUMN APPROVEAT_BACKUP TIMESTAMP NULL;

-- Step 2: Copy existing data to backup UPDATE roseth.BILL SET APPROVEAT_BACKUP = APPROVEAT;

-- Step 3: Drop the problematic column ALTER TABLE roseth.BILL DROP COLUMN APPROVEAT;

-- Step 4: Recreate column allowing NULL values ALTER TABLE roseth.BILL ADD COLUMN APPROVEAT DATETIME NULL;

-- Step 5: Restore data from backup UPDATE roseth.BILL SET APPROVEAT = APPROVEAT_BACKUP;

-- Step 6: Verify data restored correctly SELECT COUNT(*) AS total, SUM(CASE WHEN APPROVEAT IS NOT NULL THEN 1 ELSE 0 END) AS with_data FROM roseth.BILL;

-- Step 7: Drop backup column (only after verification) ALTER TABLE roseth.BILL DROP COLUMN APPROVEAT_BACKUP;

Explanation:

• MySQL 8 enforces strict mode by default - when your JPA/application passes NULL explicitly, it won't fall back to the default value
• The fix changes APPROVEAT from NOT NULL with default to NULL allowed
• This way, when your application doesn't set a value, it can accept NULL without error