[feature] Stream/Dump without foreign keys

[syerad]

First of all thank you for this amazing tool!

For the future it would be nice to add the possibility
to stream/dump data without the foreign keys.
In the "standard" mysql it looks like this:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Thank you in Advance :)

[BohuTANG]

Hi, this issue maybe duplicate with #3 Please set the foreign variable in MySQL by yourself berfor dumping.

On Thu, Jun 7, 2018 at 22:09 Raphael Syed ***@***.***> wrote: First of all thank you for this amazing tool! For the future it would be nice to add the possibility to stream/dump data without the foreign keys. In the "standard" mysql it looks like this: /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; Thank you in Advance :) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#12>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKgrDExa4Ju3uvjR1WXayfyJbAQ8Ktsks5t6TQogaJpZM4UefBb> .

-- BohuTANG "The great artist is the simplifier."--- *Vincent Van Gogh*

[syerad]

Wouldn't it be a nice option to do it just for one dump?

[BohuTANG]

Sure, it's on the plan.
Thanks.

[BohuTANG]

@syerad

Hi, mydumper/mystreamer has add vars flag, we can set as:

./bin/mydumper/mystreamer -h 192.168.0.253 -P 3306 -u test -p test -db mydb  -vars "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0" -o mydb.sql

[xqdd]

Recurrence

[root@VM_0_17_centos ~]# ./sync.sh 
 2019/04/03 16:48:48.384138 streamer.go:31:    	 [INFO] 	streaming.database[airbaggy].schema...
panic: Cannot delete or update a parent row: a foreign key constraint fails (errno 1217) (sqlstate 23000)

goroutine 1 [running]:
common.AssertNil(0x5e0100, 0xc420097900)
	/home/ubuntu1804/go-mydumper/src/common/common.go:75 +0x4a
common.streamTableSchema(0xc42007c430, 0x7ffc4c8c57db, 0x8, 0x7ffc4c8c57db, 0x8, 0x0, 0x0, 0x7ffc4c8c57eb, 0xd, 0x1, ...)
	/home/ubuntu1804/go-mydumper/src/common/streamer.go:42 +0x6f6
common.Streamer(0xc42007c430, 0xc4200ee100)
	/home/ubuntu1804/go-mydumper/src/common/streamer.go:168 +0x4cf
main.main()
	/home/ubuntu1804/go-mydumper/src/mystreamer/main.go:79 +0x40a

[BohuTANG]

@xqdd

Hi, what's the code version of mystreamer?

[xqdd]

[BohuTANG]

@xqdd

This is the foreign key constraint issue caused by MySQL, please try:

-vars "SET GLOBAL FOREIGN_KEY_CHECKS=0" 

[xqdd]

Not work, I excute this and it work by using set comand directly

I think the -var paramatter haven't been excuted

Here is a temporary database, you can use it for debug:

./mystreamer -vars "set FOREIGN_KEY_CHECKS=0"  \
 -h 154.8.160.136 -P 3307 -u root -p secret -o  -db temp1 -table my_wx_mp_user \
 -2h 154.8.160.136 -2P 3307 -2u root -2p secret  -2db temp2

[BohuTANG]

@xqdd

Thanks for your information, fixed: 3d6f763
Please pull the master and build again.