SingleStore DB how to set the table name case sensitivity to OFF

Within SingleStore DB 7.6.12 (non-managed), I can’t seem to figure out how to set table_name_case_sensitivity=OFF properly.

I’ve tried running this in SS studio “SET GLOBAL table_name_case_sensitivity=OFF” which gives me error “Variable ‘lower_case_table_names’ is a read only and only settable at startup variable”.

I then tried using the command line:
sdb-admin update-config --all --set-global --key “lower_case_table_names” --value “OFF”

but get this error “stdout: Error 1238: Variable ‘lower_case_table_names’ is a read only and only settable at startup variable”

Then I tried editing the config file directly:
/var/lib/memsql/…/memsql.cnf and adding “lower_case_table_names=OFF”

then I get this error:
00000080 2022-03-07 15:12:38.664 ERROR: Thread -1: memsqld_main: Global variable @@table_name_case_sensitivity is not modifiable at startup
00000153 2022-03-07 15:12:38.664 ERROR: ./memsqld: exiting

How do you properly set this variable and have it remain between restarts?

You can only change the setting of the variable table_name_case_sensitivity when the cluster is empty. That is, the cluster must contain no user databases.

Did you see that notice at the bottom of this?
https://docs.singlestore.com/db/v7.6/en/reference/sql-reference/database-object-case-sensitivity.html?action=version-change

1 Like

@hanson I did see that note and I only have the information_schema db in the cluster.

I think I need to understand how to set a startup variable correctly.

@hanson I figured it out - I ended up having to SSH into the master aggregator and run this command:

sudo memsqlctl update-config --set-global --key "table_name_case_sensitivity" --value "OFF"
1 Like