Search code examples
mysqlmysql-workbenchmysql-error-1064

MySQL giving an error on a query that seemed to run fine on workbench

Query that retrives strigified json data:

select count(*) from ABC 
where NOT(StoredFileLinks = '[]' or StoredFileLinks LIKE %xtractedImages\": []%') 
and NOT(Response LIKE 'Unable to detect%' or Response LIKE 'Your device is not%');

Can't seem to understand where the error is. It says:

pandas.io.sql.DatabaseError: Execution failed on sql 'select count(*) from ABC where NOT(StoredFileLinks = '[]' or StoredFileLinks LIKE %xtractedImages": []%') and NOT(Response LIKE 'Unable to detect%' or Response LIKE 'Your device is not%');': (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 \'%xtractedImages": []%\') and NOT(Response LIKE \'Unable to detect%\' or Response LI\' at line 1')

The same query runs on MySQL workbench with V8.04. This query is being used in ubuntu server whose mysql version is 14.14.

Where does the error lie?

Solution

  • your first like condition is not constructed properly.

    select count(*) from ABC 
where not(StoredFileLinks = '[]' or StoredFileLinks like '%xtractedImages": []%') 
and not(Response LIKE 'Unable to detect%' or Response like 'Your device is not%');