Bulk insert assumed that the placeholders will always be at the end

[MichaelBui]

Current regex only accept placeholders at the end of the query:

var valueBracketReg = regexp.MustCompile(`\([^(]*.[^(]\)$`)

This doesn't work with INSERT .... ON DUPLICATE KEY UPDATE....

[kazz187]

same
#657

[aarengee]

Solved :
Problem : #667 (comment)

Solution : #667 (comment)

[powerman]

This is a regression since 18 Nov 2020 b63be72. Actually this issue is tracked in a lot of places: #505 (comment), #612 (comment), #690, #712, #705, #667 (comment), #657...

[jmoiron]

This should work after #718 / v1.3.2, please reopen if you are still having issues.

[Al2Klimov]

Wanted to manifest this issue's fix by https://github.com/Al2Klimov/sqlx/commit/a3897d3439c167a9e6f7b3cafb8e8e51d831dd36, but the tests fail:

➜  sqlx git:(feature/mysql-upsert) go test -race ./...
Disabling PG tests:
    dial tcp [::1]:5432: connect: connection refused
Disabling MySQL tests:
    invalid DSN: missing the slash separating the database name--- FAIL: TestFixBounds (0.00s)
    --- FAIL: TestFixBounds/named_syntax_w/_upsert (0.00s)
        named_test.go:375: mismatched results
    --- FAIL: TestFixBounds/mysql_syntax_w/_upsert (0.00s)
        named_test.go:375: mismatched results
FAIL
FAIL	github.com/jmoiron/sqlx	0.474s
ok  	github.com/jmoiron/sqlx/reflectx	(cached)
ok  	github.com/jmoiron/sqlx/types	(cached)
FAIL
➜  sqlx git:(feature/mysql-upsert)

@jmoiron TL;DR: it's still not fixed, but I seem not to have the permission to re-open this one.

CC @lippserd

[abraithwaite]

Interesting. I noticed you changed the regex slightly, but not in a way which would cause issues for those tests.

Looking more closely, it appears that MySQL overloads the VALUES keyword for ON DUPLICATE KEY clauses.

A workaround would be to use the new. syntax for MySQL, if you're using a recent version of MySQL.

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

[Al2Klimov]

if you're using a recent version of MySQL

That’s exactly the point! We – @Icinga – can’t enforce our customers' MySQL versions as they usually stick to their Distro's package sources.

[SenselessA]

Still not fixed, I have same problem with 1.3.3 version sqlx

my example:
movies *[]models.Movie

_, err := tx.NamedExec(" INSERT INTO movies (id, kinopoisk_id) VALUES (:id, :kinopoisk_id) ON CONFLICT (id) DO UPDATE SET (id, kinopoisk_id) = (:id, :kinopoisk_id); ", movies)