pymysql; insert on duplicate key update fails

[wardancer84]

i dont want to go to deep here so...i want to do the above stated kind of query with pymysql...

relevant piece of code:

sql = "INSERT INTO entries (node_id, attrib_id, value, unique_id) VALUES (%d, %d, '%s', '%s') AS new \
                   ON DUPLICATE KEY UPDATE \
                   node_id = CASE WHEN new.unique_id <> unique_id THEN new.node_id ELSE node_id END, \
                   attrib_id =  CASE WHEN new.unique_id <> unique_id THEN new.attrib_id ELSE attrib_id END, \
                   value =  CASE WHEN new.unique_id <> unique_id THEN new.value ELSE value END, \
                   unique_id = new.unique_id" % \
                   (self.get_or_create_node_id(node_name), self.get_or_create_attrib_id(attrib_name), value, hash,)
        self.run_query(sql)

which results in this query string:

INSERT INTO entries (node_id, attrib_id, value, unique_id) VALUES (6688, 932, '8.0.0.636', 'e8ff77aff84da8f7755bdb9218e7a963') AS new ON DUPLICATE KEY UPDATE node_id = CASE WHEN new.unique_id <> unique_id THEN new.node_id ELSE node_id END, attrib_id =  CASE WHEN new.unique_id <> unique_id THEN new.attrib_id ELSE attrib_id END, value =  CASE WHEN new.unique_id <> unique_id THEN new.value ELSE value END, unique_id = new.unique_id;

the db refuses to do it hence its valid sql which i proofed against some sql validator

Error:
2022-06-28 12:11:47,172 [ERROR] Something went wrong during db interaction: (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 'AS new                    ON DUPLICATE KEY UPDATE                    node_id ...' at line 1")

table:

MariaDB [aix_registry_dev]> describe entries;
+-----------+--------------+------+-----+---------------------+-------------------------------+
| Field     | Type         | Null | Key | Default             | Extra                         |
+-----------+--------------+------+-----+---------------------+-------------------------------+
| id        | int(11)      | NO   | PRI | NULL                | auto_increment                |
| node_id   | int(11)      | NO   | MUL | NULL                |                               |
| attrib_id | int(11)      | NO   | MUL | NULL                |                               |
| value     | varchar(256) | NO   |     | NULL                |                               |
| unique_id | varchar(256) | NO   |     | NULL                |                               |
| ts        | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+-----------+--------------+------+-----+---------------------+-------------------------------+
6 rows in set (0.017 sec)

MariaDB [aix_registry_dev]> show indexes from entries;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| entries |          0 | PRIMARY                 |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          0 | ENTRIES_UNIQUE_COMPOUND |            1 | node_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          0 | ENTRIES_UNIQUE_COMPOUND |            2 | attrib_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          0 | ENTRIES_UNIQUE_COMPOUND |            3 | value       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          0 | ENTRIES_UNIQUE_COMPOUND |            4 | unique_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          1 | id                      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          1 | HAS_A_ATTRIBUTE         |            1 | attrib_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| entries |          1 | HAS_A_NODE              |            1 | node_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.000 sec)

version

MariaDB [aix_registry_dev]> SELECT @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.4.18-MariaDB |
+-----------------+
1 row in set (0.000 sec)

i am stuck, any ideas/hints?

[rob101]

It could be nothing, it could be everything, but I don't see a semicolon terminator at the end of the query string, as there is with with the SQL statement on line 1, above the table.

[wardancer84]

It could be nothing, it could be everything, but I don't see a semicolon terminator at the end of the query string, as there is with with the SQL statement on line 1, above the table.

seems the semicolon whas eaten during copy/paste..added it back

[wardancer84]

seems mariadb simply does not support this "AS" construct....mysql 8+ only feature

[Larz60+]

in line 6 shouldn't there be something (data type) after % unique_id = new.unique_id" % \ ?