error with mysql syntax locally but not on production

Question

I am seeing this error locally when I hit a page:

Mysql2::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 'and reference_number > )  ORDER BY reference_number asc LIMIT 1' at line 1:

What does this mean? This query does not fail on production but it does locally. On digitalOcean I see this:

ssh <username>@107.170.112.8
Welcome to Ubuntu 14.04 LTS (GNU/Linux 3.13.0-24-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

  System information as of Wed Oct 17 22:25:21 EDT 2018

  System load:  1.26                Processes:           108
  Usage of /:   80.1% of 157.36GB   Users logged in:     0
  Memory usage: 59%                 IP address for eth0: 107.170.112.8
  Swap usage:   2%                  IP address for eth1: 10.128.133.134

  Graph this data and manage this system at:
    https://landscape.canonical.com/

414 packages can be updated.
294 updates are security updates.

Last login: Wed Oct 17 22:25:22 2018 from 65.200.165.210
<username>:~$ mysql --version
mysql  Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.3

Is there a mismatch between my my msql versions?

The query (activeRecord in Rails 4.15) in question is this:

 other_catalog_item = @current_checkout_location.checked_in_current_catalog_items
    .where("catalog_section_id = #{selected_catalog_item.catalog_section_id} and reference_number > #{selected_catalog_item.reference_number}")
    .order('reference_number asc')
    .limit(1).first

The thing is... if I change it locally, I run the risk that production breaks. What should I do? What is the root of this problem?

and reference_number > ) ... the error message seems to be saying that the second parameter is not getting bound from your Rails code. You might want to include the code which is making this MySQL call. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 18, 2018 at 3:40
So you dont see that error in production when you hit that page? — Pavan
– Pavan, Commented Oct 18, 2018 at 4:21
(1) selected_catalog_item.reference_number is nil, you should update your test suite to cover this case. (2) Why are you using string interpolation for SQL? You should be using placeholders. — mu is too short
– mu is too short, Commented Oct 18, 2018 at 4:50
Is it likely the case that I my local data is terrible and incomplete? — Jwan622
– Jwan622, Commented Oct 18, 2018 at 13:43

Jame · Accepted Answer · 2018-10-18 07:57:51Z

1

Look into this

Mysql2::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 'and reference_number > )  ORDER BY reference_number asc LIMIT 1' at line 1:

and reference_number > ) is wrong mysql adapters. It's up to your data in local and production are different, selected_catalog_item has no reference_number, so it causes the issue

reference_number > #{selected_catalog_item.reference_number}"

answered Oct 18, 2018 at 7:57

Jame

342 bronze badges

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

Collectives™ on Stack Overflow

error with mysql syntax locally but not on production

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related