[MySQL] ERROR 1064 - while running `snomed_load_mysql` in mysql-loader-with-optimized-views

[axelv]

Hi,

First of all thanks for the scripts in this repo!

I have some problems running the scripts for the MySQL with optimized views.
After successfully running sudo ./bash/snomed_config_mysql the load command fails:
Running

sudo ./bash/snomed_load_mysql

Gives me the following error

mysql --defaults-extra-file="/Users/axelvanraes/dev/snomed-database-loader/mysql-loader-with-optimized-views/cnf/my_snomedimport_client.cnf"  --protocol=tcp --host=localhost --port=3306 --default-character-set=utf8mb4 --user root --password  < "/Users/axelvanraes/dev/snomed-database-loader/mysql-loader-with-optimized-views/mysql_load/sct_mysql_temp.sql"
Enter password:
CHECKING MySQL CONFIGURATION SETTINGS
CHECKING MySQL CONFIGURATION SETTINGS
ERROR 1064 (42000) at line 166: 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 '= (@`req_len`)),'MySQL Fulltext minimum word length OK',concat('WARNING! Fulltex' at line 1

I'm running mysql 8.0.29 on a Mac M1 with Big Sur 11.3.

[axelv]

After some research I was able to pin down the statement that triggers the error.

I think there is a problem with this statement:
https://github.com/IHTSDO/snomed-database-loader/blob/master/mysql-loader-with-optimized-views/mysql_load/sct_mysql_load_create_InternationalRF2.sql#L138

Running that line with the enclausing SELECT * FROM throws the exact same error:

mysql> SELECT * FROM (SELECT IF(@@GLOBAL.ft_min_word_len=@req_len,'MySQL Fulltext minimum word length OK',CONCAT('WARNING! Fulltext minimum word length (ft_min_word_len) must be ',@req_len,' but has value: ', @@GLOBAL.ft_min_word_len)) 'Message') data WHERE Message="";
ERROR 1064 (42000): 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 '= (@`req_len`)),'MySQL Fulltext minimum word length OK',concat('WARNING! Fulltex' at line 1

I was able to reduce this statement further down to the following line:

mysql> SELECT * FROM (SELECT IF(@@GLOBAL.ft_min_word_len=@req_len,'MySQL Fulltext minimum word length OK',CONCAT('WARNING! Fulltext minimum word length (ft_min_word_len) must be ',@req_len,' but has value: ', @@GLOBAL.ft_min_word_len)) 'Message') data WHERE Message="";
ERROR 1064 (42000): 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 '= 2),'IF clause is true','IF clause is false')' at line 1

I also checked that the global variable does exist.

mysql> SELECT @@GLOBAL.ft_min_word_len;
+--------------------------+
| @@GLOBAL.ft_min_word_len |
+--------------------------+
|                        4 |
+--------------------------+
1 row in set (0,00 sec)

[JonZammit]

Hi @axelv did you follow the instructions here - https://confluence.ihtsdotools.org/display/DOCSQLPG/Appendix+A%3A+Building+the+SNOMED+CT+Example+Database ?

There is a bit about MySQL configuration.

[axelv]

They are similar to the instructions in the readme. The only configuration required is running ./bash/snomed_config_mysqland subsequently restarting MySQL which is what I have done.

Do you think I missed something?

[axelv]

I think it has to do with how I installed the MySQL service.

I've installed the mysql service with homebrew: brew install mysql
As a result, I had to update the paths in the bash scripts.

A temporary workaround for me was commenting on the configuration check on this line.

And then doing the check of global variables manually:

SELECT @@GLOBAL.ft_stopword_file  /* should be empty */
SELECT @@GLOBAL.ft_min_word_len /* should be 2 */
SELECT  @@GLOBAL.local_infile /* should be 1*/

sudo ./bash/snomed_load_mysql did run without errors !

[JonZammit]

@axelv thank you for the update. We have successfully tested several versions of MySQL server on macos without homebrew. The configuration file (/usr/local/mysql/support-files/my_snomedserver.cnf) is required. We will see if we can replicate your setup.

[JonZammit]

@axelv It looks like this issue was introduced recently with the release of MySQL 8.0.29. I was able to reproduce your error today with 8.0.29, but confirmed that 8.0.28 works properly. We will let you know if/when we can release an improved version of the scripts but at least we now have a couple of workarounds. Thanks again.

[axelv]

Thx for the update!