MySQL 8 syntax error INSERT ON DUPLICATE KEY-CodePudding

So, I have this query :

INSERT INTO availabilities (user_id, date, type, available, updated_at, created_at) 
VALUES (?, ?, ?, ?, ?, ?), 
        (?, ?, ?, ?, ?, ?), 
        (?, ?, ?, ?, ?, ?) AS new 
    ON DUPLICATE KEY UPDATE available = new.available

I don't see anything wrong here as I'm using the same syntax as this example in the official MySQL documentation :

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a new.b;

Unfortunately, I'm facing this error :

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS new ON DUPLICATE KEY UPDATE available = new.available' at line 1

What am I missing ?

MySQL version : 8.0.29 (official docker image)

EDIT :

I have been asked to share the code that generates the query so here it is :

/**
 * @param array<Availability> $entities
 */
private function addOrUpdate(array $entities): void
{
    $connection = $this->_em->getConnection();
    $preparedQuery = 'INSERT INTO availabilities (user_id, date, type, available, updated_at, created_at) VALUES ';

    $numberOfEntities = count($entities);
    for ($i = 0; $i < $numberOfEntities; $i  ) {
        if ($i > 0) {
            $preparedQuery .= ', ';
        }

        $preparedQuery .= '(?, ?, ?, ?, ?, ?)';
    }

    $preparedQuery .= ' AS new ON DUPLICATE KEY UPDATE available = new.available';

    $stmt = $connection->prepare($preparedQuery);

    $creationDate = CarbonImmutable::create()->toDateTimeString();
    for ($i = 0; $i < $numberOfEntities; $i  ) {
        $entity = $entities[$i];
        $offset = $i * 6;

        $stmt->bindValue($offset   1, $entity->getUser()->getId());
        $stmt->bindValue($offset   2, $entity->getDate()->format('Y-m-d'));
        $stmt->bindValue($offset  3, $entity->getType(), ParameterType::INTEGER);
        $stmt->bindValue($offset   4, $entity->getAvailable(), ParameterType::BOOLEAN);
        $stmt->bindValue($offset   5, $creationDate);
        $stmt->bindValue($offset   6, $creationDate);
    }

    $stmt->executeStatement();
    $this->_em->flush();
}

This is a method from a Doctrine repository in a Symfony (PHP) project.

Here is the table structure :

CREATE TABLE `availabilities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `date` date NOT NULL COMMENT '(DC2Type:date_immutable)',
  `type` smallint NOT NULL,
  `available` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_AVAILABILITY_TYPE_DATE` (`user_id`,`type`,`date`),
  KEY `IDX_D7FC41EFA76ED395` (`user_id`),
  CONSTRAINT `FK_D7FC41EFA76ED395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EDIT 2 :

So, like everyone has suggested, this is the SQL query generated :

INSERT INTO availabilities (user_id, date, type, available, updated_at, created_at) 
VALUES  AS new 
ON DUPLICATE KEY UPDATE available = new.available

Here is the problem : the values are gone ! Maybe I'm doing something wrong with doctrine.

CodePudding user response：

The example query and the query you provided(without ?) worked fine on my MySQL 8.0.25 version;

mysql> select version();
 ----------- 
| version() |
 ----------- 
| 8.0.25    |
 ----------- 
1 row in set (0.00 sec)


mysql> INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a new.b;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

Your query replacing ? with values.

mysql> show create table availabilities;
 CREATE TABLE `availabilities` (
  `user_id` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type` int DEFAULT NULL,
  `available` varchar(5) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
1 row in set (0.00 sec)


mysql> INSERT INTO availabilities (user_id,  date, type, available, updated_at, created_at)
    -> VALUES (1,'2022-05-10', 1,'Yes','2022-05-10 10:10:10', '2022-05-10 10:10:10'), (2,'2022-05-10', 2,'Yes','2022-05-10 10:10:10', '2022-05-10 10:10:10'), (1,'2022-05-10', 1,'Yes','2022-05-10 10:10:10', '2022-05-10 10:10:10') AS new  ON DUPLICATE KEY UPDATE available = new.available ;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

You have to print the query with values to check the problem

CodePudding user response：

It was all my fault, I was calling my method with an empty array and was looking at my query syntax when it was my code which was not ok.

Thanks everyone for your help !