snomed-database-loader
snomed-database-loader copied to clipboard
[MySQL] ERROR 1064 - while running `snomed_load_mysql` in mysql-loader-with-optimized-views
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.
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)
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.
They are similar to the instructions in the readme. The only configuration required is running ./bash/snomed_config_mysql
and subsequently restarting MySQL which is what I have done.
Do you think I missed something?
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 !
@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.
@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.
Thx for the update!