Migrate tool panics for tables with Double datatype of mysql

pranavshah01 · November 21, 2020, 8:17am

Report a Dgraph Bug

panic: detected unsupported type double on column column_name

What version of Dgraph are you using?

v20.07.2

Have you tried reproducing the issue with the latest release?

This is latest release

What is the hardware spec (RAM, OS)?

MacOS Catalina (10.15.7 19H15)

Steps to reproduce the issue (command/config used to run Dgraph).

dgraph migrate --host hostname --port portnumber --config config.properties --output_schema schema.txt --output_data sql.rdf

Content of config.properties
user = db_user
password = pwd
db = dbname

Expected behaviour and actual result.

Command should run successfuly and

Experience Report for Feature Request

Note: Feature requests are judged based on user experience and modeled on Go Experience Reports. These reports should focus on the problems: they should not focus on and need not propose solutions.

What you wanted to do

Create schema file and data dump from mysql to import to dgraph

What you actually did

Unable to complete the migration

Why that wasn’t great, with examples

n/a

Any external references to support your case

n/a

Blockquote

BenW · March 29, 2021, 10:04am

I’m also having this problem with the migrate tool. I’ve migrated my postgres database to MySQL, but I keep getting errors about panic: detected unsupported type unknown on column <column> when trying to use the migrate CLI tool (to generate RDF) on this MySQL db… and it’s usually the id/foreign_key columns. I’ve tried migrating these columns as int, bigint, bigint unsigned, int(64), int(11). Running out of ideas.

❯ dgraph migrate --db flashcards_prod --host mariadb-123-0.cloudclusters.net --password passw0rd --port 12345 --user prod_user --output_schema schema.txt --output_data sql.rdf
[Decoder]: Using assembly version of decoder
Page Size: 4096
overwriting the file schema.txt (y/N)? y
overwriting the file sql.rdf (y/N)? y
Dumping table images
panic: detected unsupported type unknown on column id

goroutine 1 [running]:
github.com/dgraph-io/dgraph/x.Panic(...)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/x/error.go:81
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.getColumnValues(0xc0000eed00, 0x5, 0x8, 0xc000036500, 0x5, 0x8, 0xc000394b00, 0x8, 0xc000394b00, 0x0, ...)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/utils.go:123 +0x60c
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTable(0xc00008c500, 0xc0004dc60a, 0x6, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:113 +0x5c5
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTables(0xc00008c500, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:73 +0x165
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.generateSchemaAndData(0xc00008c500, 0x7ffeefbff5a2, 0xa, 0x7ffeefbff5bb, 0x7, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:179 +0x125
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.run(0xc00033eea0, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:123 +0x668
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.init.0.func1(0xc000588000, 0xc0001a60e0, 0x0, 0xe)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:44 +0x31
github.com/spf13/cobra.(*Command).execute(0xc000588000, 0xc0001a6000, 0xe, 0xe, 0xc000588000, 0xc0001a6000)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:830 +0x2c2
github.com/spf13/cobra.(*Command).ExecuteC(0x5ccd640, 0x0, 0x0, 0x0)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:914 +0x30b
github.com/spf13/cobra.(*Command).Execute(...)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:864
github.com/dgraph-io/dgraph/dgraph/cmd.Execute()
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/root.go:72 +0x85
main.main()
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/main.go:102 +0x172

chewxy · March 29, 2021, 10:52am

Oh wow. I wasn’t even aware we had a migration tool. I will look into this

BenW · March 29, 2021, 9:10pm

It would seem that it needs to be int

github.com

dgraph-io/dgraph/blob/98f68280ef84a947dbd722069d59e2f892e4c040/dgraph/cmd/migrate/datatype.go#L45-L55


      
          	sqlTypeToInternal = make(map[string]dataType)
          	sqlTypeToInternal["int"] = intType
          	sqlTypeToInternal["tinyint"] = intType
          	sqlTypeToInternal["varchar"] = stringType
          	sqlTypeToInternal["text"] = stringType
          	sqlTypeToInternal["date"] = datetimeType
          	sqlTypeToInternal["time"] = datetimeType
          	sqlTypeToInternal["datetime"] = datetimeType
          	sqlTypeToInternal["float"] = floatType
          	sqlTypeToInternal["double"] = doubleType
          	sqlTypeToInternal["decimal"] = floatType

EDIT: I’ve made sure all bigints are converted to ints when migrated into MySQL, however, now I’m getting the following error:

Dumping table reps
while writing data file: while dumping table reps: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'iteration,score,should_repeat,updated_at,user_id from reps' at line 1

Pretty certain this error originates here: dgraph/dgraph/cmd/migrate/dump.go at 98f68280ef84a947dbd722069d59e2f892e4c040 · dgraph-io/dgraph · GitHub

If I run the query manually in PHPMyAdmin I don’t have any problems:

EDIT:

Having a column named interval seemed to cause the SQL error above, I renamed it and the issue went away.
I experienced another error:

Dumping table flashcard_sides
panic: detected unsupported type unknown on column content

goroutine 1 [running]:
github.com/dgraph-io/dgraph/x.Panic(...)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/x/error.go:81
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.getColumnValues(0xc00028e400, 0x6, 0x8, 0xc0004d6040, 0x6, 0x8, 0xc000192000, 0x8, 0xc000192000, 0x0, ...)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/utils.go:123 +0x60c
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTable(0xc0000967d0, 0xc0002a6060, 0xf, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:113 +0x5c5
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTables(0xc0000967d0, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:73 +0x165
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.generateSchemaAndData(0xc0000967d0, 0x7ffeefbff5a2, 0xa, 0x7ffeefbff5bb, 0x7, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:179 +0x125
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.run(0xc00013cfc0, 0x0, 0x0)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:123 +0x668
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.init.0.func1(0xc000674c80, 0xc0001de0e0, 0x0, 0xe)
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:44 +0x31
github.com/spf13/cobra.(*Command).execute(0xc000674c80, 0xc0001de000, 0xe, 0xe, 0xc000674c80, 0xc0001de000)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:830 +0x2c2
github.com/spf13/cobra.(*Command).ExecuteC(0x5ccd640, 0x0, 0x0, 0x0)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:914 +0x30b
github.com/spf13/cobra.(*Command).Execute(...)
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:864
github.com/dgraph-io/dgraph/dgraph/cmd.Execute()
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/root.go:72 +0x85
main.main()
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/main.go:102 +0x172

Changing the db to utf8_unicode_ci seemed to make this error go away:

Now experiencing the following error:

Dumping table constraints flashcards
ignoring the constraint because of error when getting ref label: found invalid nullint
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.getValue
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/table_guide.go:172
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.createLabel
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/table_guide.go:204
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*sqlRow).getRefLabelFromConstraint
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:283
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).outputConstraints
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:210
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTableConstraints
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:159
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.(*dumpMeta).dumpTables
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/dump.go:80
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.generateSchemaAndData
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:179
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.run
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:123
github.com/dgraph-io/dgraph/dgraph/cmd/migrate.init.0.func1
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/migrate/run.go:44
github.com/spf13/cobra.(*Command).execute
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:830
github.com/spf13/cobra.(*Command).ExecuteC
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:914
github.com/spf13/cobra.(*Command).Execute
	/Users/brew/Library/Caches/Homebrew/go_mod_cache/pkg/mod/github.com/spf13/cobra@v0.0.5/command.go:864
github.com/dgraph-io/dgraph/dgraph/cmd.Execute
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/cmd/root.go:72
main.main
	/private/tmp/dgraph-20210224-74311-389b6w/dgraph-20.11.2/dgraph/main.go:102
runtime.main
	/usr/local/Cellar/go/1.16/libexec/src/runtime/proc.go:225
runtime.goexit
	/usr/local/Cellar/go/1.16/libexec/src/runtime/asm_amd64.s:1371
ignoring the constraint because of error when getting ref label: found invalid nullint

This error spits out an infinite amount of traces at an insanely fast rate, presumably because Go is extremely fast and the script is stuck in a loop.

Relevant line of code: dgraph/dgraph/cmd/migrate/table_guide.go at 98f68280ef84a947dbd722069d59e2f892e4c040 · dgraph-io/dgraph · GitHub

EDIT: You know what, I think the last one isn’t actually a problem, the stack trace is just being logged to stdout because the err is included as an argument to Println statement on this line: dgraph/dgraph/cmd/migrate/dump.go at 98f68280ef84a947dbd722069d59e2f892e4c040 · dgraph-io/dgraph · GitHub

I let it run and it seems to have worked. Just need to figure out how to upload it to Slash GraphQL.

Topic		Replies	Views
Migrate: Assigns many predicates with datatype 'unknown' Dgraph dgraph , investigate , area:operations	0	622	May 29, 2020
SQL migration tool: shenanigans and solutions Showcase dataset	0	757	October 31, 2020
Error 1054: Unknown column 'my_column' in 'where clause' Dgraph	2	845	February 19, 2022
Migration Tool - Migration Documentation	0	502	February 5, 2021
The migrate package should support multiple database drivers, not only MySQL Dgraph dgraph , kind:enhancement , area:tools , area:operations	7	911	October 6, 2021