Drizzle Team•15mo ago

Mysql Syntax error executing query

Hi i got this issue: Error: 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 '(select coalesce(json_arrayagg(json_array(stores_categories.id_store, `store' at line 1 when i try to run this query:

const result = await db.query.stores.findMany({
      with: {
        categories: true,
        pictures: true
      },
    });

const result = await db.query.stores.findMany({
      with: {
        categories: true,
        pictures: true
      },
    });

and in images you can see my db connection, and schemas

17 Replies

Angelelz•14mo ago

This might be an issue. Can you show the query that got generated? Can you create a reproduction repo?

HugoOP•14mo ago

i can give you the git repo, how can i see the query dat got generated ?

Angelelz•14mo ago

You can pass logger: true to the drizzle object, in the same place where you pass the schema

HugoOP•14mo ago

this is what i got:

Query: select `stores`.`id_store`, `stores`.`cuid_store`, `stores`.`id_currency`, `stores`.`name`, `stores`.`baseline`, `stores`.`realtime_position`, `stores`.`last_position_updated`, `stores`.`profile_picture`, `stores`.`featured_label`, `stores`.`siret`, `stores`.`ape`, `stores`.`wrapping_enabled`, `stores`.`wrapping_price`, `stores`.`cutleries_enabled`, `stores`.`cutleries_price`, `stores`.`date_add`, `stores`.`date_update`, `stores_categories`.`data` as `categories`, `stores_pictures`.`data` as `pictures` from `stores` left join lateral (select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `stores_categories`.`id_category`)), json_array()) as `data` from `stores_categories` where `stores_categories`.`id_store` = `stores`.`id_store`) `stores_categories` on true left join lateral (select coalesce(json_arrayagg(json_array(`stores_pictures`.`id_picture`, `stores_pictures`.`id_store`, `stores_pictures`.`img_url`)), json_array()) as `data` from `stores_pictures` where `stores_pictures`.`id_store` = `stores`.`id_store`) `stores_pictures` on true
Query: select `id_customer`, `id_gender`, `id_lang`, `id_group`, `firstname`, `lastname`, `id_clerk`, `phone_number`, `email`, `password`, `last_password_gen`, `birthday`, `profile_picture`, `date_add`, `date_update` from `customers` where `customers`.`id_clerk` = ? -- params: ["user_2UTDrNFrpFYccEasywI5ZpKDQj0"]

Query: select `stores`.`id_store`, `stores`.`cuid_store`, `stores`.`id_currency`, `stores`.`name`, `stores`.`baseline`, `stores`.`realtime_position`, `stores`.`last_position_updated`, `stores`.`profile_picture`, `stores`.`featured_label`, `stores`.`siret`, `stores`.`ape`, `stores`.`wrapping_enabled`, `stores`.`wrapping_price`, `stores`.`cutleries_enabled`, `stores`.`cutleries_price`, `stores`.`date_add`, `stores`.`date_update`, `stores_categories`.`data` as `categories`, `stores_pictures`.`data` as `pictures` from `stores` left join lateral (select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `stores_categories`.`id_category`)), json_array()) as `data` from `stores_categories` where `stores_categories`.`id_store` = `stores`.`id_store`) `stores_categories` on true left join lateral (select coalesce(json_arrayagg(json_array(`stores_pictures`.`id_picture`, `stores_pictures`.`id_store`, `stores_pictures`.`img_url`)), json_array()) as `data` from `stores_pictures` where `stores_pictures`.`id_store` = `stores`.`id_store`) `stores_pictures` on true
Query: select `id_customer`, `id_gender`, `id_lang`, `id_group`, `firstname`, `lastname`, `id_clerk`, `phone_number`, `email`, `password`, `last_password_gen`, `birthday`, `profile_picture`, `date_add`, `date_update` from `customers` where `customers`.`id_clerk` = ? -- params: ["user_2UTDrNFrpFYccEasywI5ZpKDQj0"]

- error node_modules/mysql2/promise.js (94:21) @ PromiseConnection.query
- error Error: 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 '(select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `store' at line 1
null
- error node_modules/mysql2/promise.js (94:21) @ PromiseConnection.query
- error Error: 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 '(select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `store' at line 1
digest: "3833260066"
null

- error node_modules/mysql2/promise.js (94:21) @ PromiseConnection.query
- error Error: 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 '(select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `store' at line 1
null
- error node_modules/mysql2/promise.js (94:21) @ PromiseConnection.query
- error Error: 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 '(select coalesce(json_arrayagg(json_array(`stores_categories`.`id_store`, `store' at line 1
digest: "3833260066"
null

If you give me your github tag i will add you to my repo

Angelelz•14mo ago

This looks like it's an issue, a repro repo is better so others can reproduce it independently as well.

HugoOP•14mo ago

Hi ! i need your github tag to add you to the repo

Angelelz•14mo ago

Angelelz

HugoOP•14mo ago

i just added you. The query is here: https://github.com/Hrobitaillie/streeteats/blob/main/admin/hooks/getStore.ts

Angelelz•14mo ago

This is not a repro repo, this is an app I don't have clerk, and I don't know what database you have set up Please create a reproduction repo The reason I ask, is not to annoy you with additional steps, it's because sometimes, those are not really issues, but some error in the code that you might have, so building a repro repo helps you go over your own steps and possibly figuring it out

HugoOP•14mo ago

i do'nt know what is a repo repo i will check on googl By the way if i try to execute this query on phpmyadmin i get the same issue so i think it's a mysql issue depending on the version, but the probleme is i can't change the generated query from drizzle

Angelelz•14mo ago

Does this repo work for you? where are you importing your db from? You also don't have any seeding data for the stores to test your query

Angelelz•14mo ago

https://github.com/Angelelz/repro-streetarts

GitHub

GitHub - Angelelz/repro-streetarts: reproduction repository

reproduction repository. Contribute to Angelelz/repro-streetarts development by creating an account on GitHub.

Angelelz•14mo ago

This is a reproduction repository, it's a repository that contains the minimal code/libraries to reproduce the error that you are seeing it serves several purposes, it helps the library maintainers identify very easily the code that has issues, it helps you verify that the issue you're having is reproducible, it helps you go back through your steps and make sure you didn't miss anything Anyway, I could not reproduce your issue, if you clone/fork that repository, you follow the steps in the readme, you'll see that the query does not throw any errors

HugoOP•14mo ago

Well, Thank you for all your time, i really appreciate. I understand the purpose of a reproduction repo. Because of i am to much a beginner with full stacks and mysql, i mooved to prisma that si more easy to use at level

Yeraldi29#2900•11mo ago

SomeOne could resolve this? I have the same problem and this is the only thing that I found 😦

Angelelz•11mo ago

Are you using planetscale?

Baterka•10mo ago

I have a same issue too on MariaDB

Gaming

Programming

Mysql Syntax error executing query