search

thespacedoctor / sherlock

The QUB Transient Classifier
GNU General Public License v3.0
11 stars 5 forks source link

"rank" is a reserved keyword in MySQL 8.0 #166

Closed genghisken closed 7 months ago

genghisken commented 7 months ago

Sherlock is currently incompatible with MySQL 8.0. They introduced rank as a reserved word in 8.0.2. The fix is to backquote the word.

Although I tried an in-situ fix, it looks like some other downstream code (e.g. in fundamentals) is working against me and removing the backquotes once more. Hence the query fails.

Traceback (most recent call last):
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/bin/sherlock", line 8, in <module>
    sys.exit(main())
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/sherlock/cl_utils.py", line 211, in main
    classifier.classify()
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/sherlock/transient_classifier.py", line 476, in classify
    colMaps=colMaps
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/sherlock/transient_classifier.py", line 792, in _update_transient_database
    "transients"]
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/fundamentals/mysql/insert_list_of_dictionaries_into_database_tables.py", line 173, in insert_list_of_dictionaries_into_database_tables
    dbSettings=dbSettings, dateModified=dateModified)
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/fundamentals/fmultiprocess.py", line 92, in fmultiprocess
    resultArray = resultArray.get(timeout=timeout)
  File "/usr/local/swtools/python/pstc/anaconda3/envs/sherlock_panstarrs37/lib/python3.7/site-packages/multiprocess/pool.py", line 657, in get
    raise self._value
pymysql.err.ProgrammingError: (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 'rank, _iErr, J, catalogue_object_subtype, G, rankScore, major_axis_arcsec, VErr,' at line 5")
genghisken commented 7 months ago

It gets more problematic. It looks like pymysql is what is reformatting the query string. There's a repr() call which preserves the formatting, but I don't know how to use it.

thespacedoctor commented 7 months ago

This was a 'fundamentals' issue. I fixed the 'insert_list_of_dictionaries_into_database_tables' class for MySQL 8.