Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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 this issue Nov 10, 2023 · 1 comment

Comments

@jianli-idgcapital
Copy link

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
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant