mysqldatabase-migrationdata-lossmysqlupgrade

MySQL issue: columns set to DEFAULT = CURRENT_TIMESTAMP got overwritten during sql import, on entire database

A couple of months ago I migrated to another mysql server, and I did an export and import through the phpMyAdmin web interface. I just realized that I just lost all the "creation dates" for multiple tables, as they all got overwritten with what I believe is the date of the import operation.

Not a big deal right now, as it's all still development test data, but this would've been a massive loss during production (if I ever needed to migrate again).

Any ideas as to why this happened, or how to prevent it from happening again? As I mentioned in the title, the columns' 'type' were all set to Datetime, 'default' set to CURRENT_TIMESTAMP, and I just noticed that 'extra' is set to DEFAULT_GENERATED (this might be an indication of the problem, as I've never seen this before).

Note: Current version is 8.0.20, and I believe the older server was on 5.6. Also, I'm pretty sure I left alone all the export/import settings. I remember the import threw a complaint, but I believe it had something to do with the default sql tables.

EDIT

So, I just looked into the .sql file that was generated by the Export, and I can see the INSERT INTO (..) VALUES (...) doesn't include the creation_date column or any of its data. So something must've been wrong on export. Any ideas what to look for on my next export?

Solution

  • Thanks all for the comments. This is the info at the top of the .sql file:

    -- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 24, 2021 at 07:42 PM
-- Server version: 8.0.20
-- PHP Version: 7.3.11

    So it seems I exported from 8.0.20...weird, I was sure it was 5.6. Anyway, the problem seems to be exactly what @Solarflare mentioned, and seems to be fixed on a new version of phpMyAdmin.