Node js JSON string insert to DB but cant update same JSON string

Question

I encountered a strange mistake. I add a data that I send with Vue axios post to the database, a column of this data that is converted to JSON object with JSON.stringfy. When I try to update this line later, can I share the idea that I am having the following error? I shared SQL string below.

{
  id: null,
  table_id: 1425,
  user_id: 15,
  order_time: 1586975000253,
  order_status: 1,
  order: [
    {
      product: 8,
      amount: 1,
      portion: [Object],
      order_time: 1586974979254,
      status: 0,
      desc: ''
    },
    {
      product: 4,
      amount: 1,
      portion: [Object],
      order_time: 1586974979707,
      status: 0,
      desc: ''
    },
    {
      product: 8,
      amount: 1,
      portion: [Object],
      order_time: 1586974980271,
      status: 0,
      desc: ''
    }
  ],
  corp: 'sssxx'
}

ER_PARSE_ERROR: 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 'order = '[{\"product\":8,\"amount\":1,\"portion\":{\"id\":1,\"title\":\"Tam Pors' at line 1

UPDATE orders SET order = '[{\\"product\\":8,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974979254,\\"status\\":0,\\"desc\\":\\"\\"},{\\"product\\":4,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974979707,\\"status\\":0,\\"desc\\":\\"\\"},{\\"product\\":8,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974980271,\\"status\\":0,\\"desc\\":\\"\\"}]', order_status = 1 WHERE id = NULL AND table_id = 1425 and corp = 'sssxx'

I've never used MariaDB but some easy Googling finds that it's a NoSQL layer on top of MySQL. MySQL has a character length limit on VARCHAR, and since your JSON is very long you might be hitting that limit. This is especially possible given that your error message is truncating the update value (note the value ends with "Tam Pors"). I'd start by checking the character limit of the underlying VARCHAR in your MariaDB. — Ertai87
– Ertai87, Commented Apr 15, 2020 at 18:44
Hey bro. order column lengt limit 5000. But i said also same json string inserted but cant updated. — Mahir Altınkaya
– Mahir Altınkaya, Commented Apr 15, 2020 at 18:51

Bill Karwin · Accepted Answer · 2020-04-15 18:53:38Z

1

The problem with your code has nothing to do with using JSON.

The word order is a reserved keyword. See https://mariadb.com/kb/en/reserved-words/ You can't use it as a column name unless you delimit it with back-ticks:

UPDATE orders SET `order` = ...whatever...

The clue in the error message is that it complained about the word order, not about anything in your JSON.

...for the right syntax to use near 'order = ...

Syntax errors show you exactly the point in your SQL syntax where the parser got confused.

answered Apr 15, 2020 at 18:53

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mahir Altınkaya Over a year ago

Oh dude. You are amazing :o actualy I tried but i cant see this. maybe another fail. Sorry. Thanks again.

Collectives™ on Stack Overflow

Node js JSON string insert to DB but cant update same JSON string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related