ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

今日在 AWS 上创建数据库表时,突然出现这种问题。

-> create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
-> update_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP,

就是这两行。完整的报错是 ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause。

mysql –version 一看,我晕。aws yum install 的 mariadb 的版本居然是 5.5 的。于是我删除已经安装的版本。

sudo yum remove mariadb mariadb-server

重新采用 docker 来安装比较新的 8 版本。

启动MySQL实例
docker run –name=mysql1 -v mysql:/var/lib/mysql -p 3306:3306 –restart on-failure -d mysql/mysql-server:8.0

获得管理员的密码
docker logs mysql1 2>&1 | grep GENERATED

修改管理员的密码
docker exec -it mysql1 mysql -uroot -p

输入密码:老密码。
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’;

创建并授权用户
create user ‘admin’@’116.234.36.104’ identified with mysql_native_password by ‘ffdd123’;
create user ‘admin’@’116.234.36.104’ identified by ‘ffdd123’;
grant all privileges on projectdb.* to ‘admin’@’116.234.36.104’;

flush privileges;

备份数据。

docker exec -it mysql1 mysqldump -uadmin -p projectdb > projectdb_bk_20230311.db

修复数据
docker exec -it mysql1 mysqldump -uprojectdb -p projectdb
docker exec -it mysql1 mysql -uroot -p projectdb < /backup/mysqldump/db_name.db