Why are documentation of mariadb KB (added before 6 yeaars 9 months revision) and enterprise different? greenman
Through code you can find checks if lower_case_table_names != 2 or similar which is used to follow the logic.

anel,

Why are documentation of mariadb KB (added before 6 yeaars 9 months revision) and enterprise different?

The system variable reference in the Enterprise Documentation was automatically extracted from the usage message in the server that this Jira issue is referring to.

The usage message in the server says this:

MariaDB [(none)]> SELECT VARIABLE_COMMENT FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='lower_case_table_names';

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| VARIABLE_COMMENT                                                                                                                                                      |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| If set to 1 table names are stored in lowercase on disk and table names will be case-insensitive.  Should be set to 2 if you are using a case insensitive file system |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.002 sec)

And the Enterprise Documentation says this:

If set to 1 table names are stored in lowercase on disk and table names will be case-insensitive. Should be set to 2 if you are using a case insensitive file system

You can see that they are exactly the same.

In my opinion, this usage message should be improved. It is missing a lot of information.

The description from the KB can be used here.

greenman,

The description from the KB can be used here.

I don't think the description from the KB can be used as-is. As far as I can tell, that description has some inaccuracies and omissions, so it would probably have to be fixed.

For example, it says this about lower_case_table_names=2:

If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.

As far as I can tell, it is not true that this is the default value on Mac OS X. The source code only lists two default values:

#ifdef FN_NO_CASE_SENSE

    DEFAULT(1),

#else

    DEFAULT(0),

#endif

So if the OS is case sensitive, then the default value will be 0. If the OS is case insensitive, then the default value will be 1. There is no condition here that says that the default value would be 2 on Mac OS X. Am I missing something, or is the KB incorrect about this?

And for another example, the description from the KB also does not point out that lower_case_table_names=2 is totally invalid on Linux if the file system is case sensitive. If you try to set that value, then you will get a warning in the error log like the following, and your choice will be overridden:

Feb 20 00:00:53 ip-172-30-0-123 mysqld: 2020-02-20  0:00:53 0 [Warning] lower_case_table_names was set to 2, even though your the file system '/var/lib/mysql/' is case sensitive.  Now setting lower_case_table_names to 0 to avoid future problems.

For this reference to Mac OS X's default:

If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.

Is it referring to this block of code?:

  if (!lower_case_table_names && lower_case_file_system == 1)

  {

    if (lower_case_table_names_used)

    {

      sql_print_error("The server option 'lower_case_table_names' is "

                      "configured to use case sensitive table names but the "

                      "data directory resides on a case-insensitive file system. "

                      "Please use a case sensitive file system for your data "

                      "directory or switch to a case-insensitive table name "

                      "mode.");

      return 1;

    }

    else

    {

      if (global_system_variables.log_warnings)

	sql_print_warning("Setting lower_case_table_names=2 because file "

                  "system for %s is case insensitive", mysql_real_data_home);

      SYSVAR_AUTOSIZE(lower_case_table_names, 2);

    }

  }

https://github.com/MariaDB/server/blob/mariadb-10.4.12/sql/mysqld.cc#L4431

If so, then I think it would be more accurate to describe it like this:

If set to 2, names are stored as declared, but compared in lowercase. The server automatically sets the variable to this value and logs a warning when it detects that the file system is case insensitive, and the variable is not already set to 1 or 2. For example, this would happen on Mac OS X when using HFS+ or APFS, except when APFS is used in case-sensitive mode.