Qovery / Replibyte

Seed your development database with real data ⚡️
https://www.replibyte.com
GNU General Public License v3.0
4.16k stars 128 forks source link

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

Open jianli-idgcapital opened 12 months ago

jianli-idgcapital commented 12 months ago

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:secret@192.168.120.14:3306/growth_project # you can use $DATABASE_URL datastore: local_disk: dir: /Users/replibyte destination: connection_uri: mysql://root:secret@127.0.0.1:33060/test-backup # you can use $DATABASE_URL

jianli-idgcapital commented 12 months ago

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.