dump restore failed for reason ERROR 1231 (42000) at line 3265: Variable 'time_zone' can't be set to the value of 'NULL'

[jianli-idgcapital]

Dears,

I have create a dump file with replibyte. The total db size is about 2GB and one of the tables is about 1.5GB. The dump file was created successful and I also tried to restore the dump file to a SQL successful. But when I tried to restore the dump file to another database, it's failed due to the error as below.

ERROR 1231 (42000) at line 3265: Variable 'time_zone' can't be set to the value of 'NULL'

I tried to go through the mysql log and I found it's failed at this statement.
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Seems to me that the previous 2 SQL about timezone was executed sucessful.
2023-11-10T02:28:13.929888Z 124 Query /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /
2023-11-10T02:28:13.930585Z 124 Query /!40103 SET TIME_ZONE='+00:00' /
2023-11-10T02:32:14.851536Z 134 Query /!40103 SET TIME_ZONE=@OLD_TIME_ZONE */

After restore, I found there's about 24xxx rows in the big table and I found there's about 11000 rows inserted.

I doubt it's because the failed statement was executed in a new session. That may leads to that error. I can recreate that error by executing that statement in a new session.

I'm not sure what I can try for now. Could you please help take a look if it's an issue or there's anything wrong during my process?

Thanks

PS: my config file looks like below. Very simple version.

source:
connection_uri: mysql://root:[email protected]:3306/growth_project # you can use $DATABASE_URL
datastore:
local_disk:
dir: /Users/replibyte
destination:
connection_uri: mysql://root:[email protected]:33060/test-backup # you can use $DATABASE_URL

[jianli-idgcapital]

Add some more details

I tried to restore another DB instance and succeed. This new DB also contains those datetime column which uses default current_timestamp. I found below statements in the log.

2023-11-10T06:53:31.074836Z 16 Query /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /
2023-11-10T06:53:31.076028Z 16 Query /!40103 SET TIME_ZONE='+00:00' /
2023-11-10T06:58:08.342169Z 16 Query /!40103 SET TIME_ZONE=@OLD_TIME_ZONE */

Is that number "16" a session number? In my previous log, I found those 3 statements are with 2 different numbers.