1

I am trying to export data from mysql 5.7 in to mysql 5.5 server. The problem that i am facing is that 5.5 does not support more than one ON UPDATE CURRENT_TIMESTAMP, but the 5.7 has multiple timestamp collumns. The questions would be: is there a way to disable ON UPDATE CURRENT_TIMESTAMP im mysqldum only, without touching the actual database. I have tried --compatible=mysql323 and --compatible=mysql40 but these modes introduce other issues of compatibility, like MODE=InnoDB instead of ENGINE=InnoDB. Any help or advice would be greatly appreciated.

Auris
  • 311
  • 1
  • 3
  • 14

2 Answers2

1

Use the 5.5 version of mysqldump to create the dump. It might work. Or it might get into other troubles.

5.5 had a very limited set of defaults for TIMESTAMP columns. Even if you could turn off the incompatible syntax, would you get a CREATE TABLE that will work.

This may be only the tip of the iceberg. What do you intend to do on the version 5.5 server? Maybe it is just for doing analytics? And you won't be doing INSERTs or UPDATEs, so it does not matter about the defaults?

Plan B:

mysqldump ... --no-data

then edit out the defaults for TIMESTAMPs and feed the result to mysql. The tables will be built, but not yet populated.

mysqldump ... --no-create | mysql ...

to load the data. (Caveat: I am not sure of the --no parameter's spelling)

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Hi Rick, the 5.5 is for querying only (no data writes) so the actual default functionality is not that important to me. I have tried the 5.5 mysqldump but it does not want to play ball 5.7 DB. – Auris Jun 20 '19 at 08:49
  • @Auris - I added another approach. – Rick James Jun 20 '19 at 13:34
  • Hi, the plan B may be a more viable solution for me. Thnx for the suggetion. – Auris Jun 24 '19 at 14:30
0

You already found the compatibility flag for the dump, and it does not have that specific of a version.

Upgrade the destination database to 5.7 or later. If this is not feasible, write a program to do any needed transforms and import it. Extract from the running database, or transform the 5.7 dumps.

(In general, forward compatibility with any significant changes is difficult. Usually you need the new version of some software to read new data structures and syntax.)

John Mahowald
  • 32,050
  • 2
  • 19
  • 34