bulk_create not compatible with mariadb

[jfberry]

Describe the bug Using bulk_create to insert and update values on mariadb causes incompatible sql code to be generated

To Reproduce

   users = [
        model.User(user_id="1",name="James",email="[email protected]"),
        model.User(user_id="2",name="Claire",email="[email protected]")
    ]

    await model.User.bulk_create(users, update_fields=["name","email"], on_conflict="user_id", batch_size=3)

Expected behavior The SQL generated and passed is:

INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','[email protected]') 
    AS `new_User` ON DUPLICATE KEY UPDATE `name`='email',`name`='email'

(as it happens I am not sure why name=email is listed twice when I would expect name=name, email=email - so not even sure this would completely work on mysql)

But, on mariadb, the syntax needed is:

INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','[email protected]') 
     ON DUPLICATE KEY UPDATE `name`=VALUES(name),`email`=VALUES(email)

Additional context Mysql 8 has deprecated VALUES - it does work, but gives a warning (as I understand it), this might require a dialect configuration. Or accept that VALUES works on both mysql and mariadb, even though it generates a warning on mysql

[long2ice]

Did you try latest develop?

[jfberry]

Switched to develop branch.

SQL for execution is:

INSERT INTO `user` (`user_id`,`name`,`email`) VALUES ('1','James','[email protected]') AS `new_user` ON DUPLICATE KEY UPDATE `name`=`new_user`.`name`,`name`=`new_user`.`name`,`email`=`new_user`.`email`,`email`=`new_user`.`email`

For MYSQL8 this has a duplicate 'email'=... (but would probably work)

However, for Mariadb this is invalid syntax - see above needs to use VALUES rather than an alias as this syntax is not supported.

[jfberry]

You add this in pypika like this:

                q = q.on_duplicate_key_update(field, Values(field))

[capcom6]

Hello.

I have the same issue with MariaDB.

So instead of a single bulk_create:

await User.bulk_create(
    [User(id=user.user_id, phone=user.phone) for user in users],
    on_conflict=["id"],
    update_fields=["phone"],
)

I use a combination of bulk_create and bulk_update:

db_users = [User(id=user.user_id, phone=user.phone) for user in users]
await User.bulk_create(
    db_users,
    ignore_conflicts=True,
)
await User.bulk_update(
    db_users,
    fields=["phone"],
)

Of course, this method has some performance issues because we are also updating newly inserted records too. Therefore, it is preferable to use the VALUES() function in the query, as suggested above.