a MariaDB database with tables in INNODB format was backed up to mysqldumb and should now be restored using mysql (ssh).

Unfortunately I get the following error:

ERROR 1064 (42000) at line 139: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'PAGE_CHECKSUM=1' at 
line 10

Line 139 says:

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1;

The tables were previously in the Aria engine but are now InnoDB as you can see.

How can I remove the PAGE_CHECKSUM=1? The file is 5GB in size and it would be good if PAGE_CHECKSUM=1 was gone before the mysqldump.

Thank you very much.

CodePudding user response：

You'll have to remove those strings from the file using an editor before you can import it. You're right, small-scale text editors probably will have a hard time on a 5GB file. But there are editors that can do it, for instance vim or emacs.

Or you can use sed to filter out the strings you don't want:

sed -e 's/PAGE_CHECKSUM=1//' dumpfile.sql | mysql ...options

(I assume that the string doesn't occur anywhere else in the file, like in the data itself.)

CodePudding user response：

Use sed to remove PAGE_CHECKSUM=1:

sed 's/PAGE_CHECKSUM=1//' dumpfile.sql | mysql --user username -p --host hostname databasename

CodePudding user response：

use sed on linux like:

root@localhost:~# sed -i 's/ PAGE_CHECKSUM=1;/;/g' page.sql

this will first change your file. backup before