DiamondLightSource/ispyb-database

syntax error in routines.sql

dsclassen opened this issue · 3 comments

dsclassen commented

I'm getting the following error when trying to initialize a new instance of an ISPyB schema.

Enter password: 
ERROR 1064 (42000) at line 4402: 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 'JSON
     )
    MODIFIES SQL DATA
    COMMENT 'Inserts or updates info about dat' at line 20
dsclassen commented

I believe the problem is being caused by line 4421

p_scanParameters JSON

That JSON is not being recognized.

KarlLevik commented

Hi Scott,

That seems like a problem with one of the stored procedures or functions - I'm guessing maybe it doesn't like the JSON data type here:
https://github.com/DiamondLightSource/ispyb-database/blob/master/schema/routines.sql#L4421

What version of MariaDB are you running? The JSON data type was introduced in MariaDB 10.2. However, 'JSON' in MariaDB is really just an alias for 'longtext', so you can try changing it to 'longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin' and see if that works.

dsclassen commented

I figured it was something like that. We currently have 10.1.37
I changed it from JSON to longtext and that fixed the import problem.