IMPORT FOREIGN SCHEMA behaves differently depending on mysql server's lower_case_table_names setting

[mhw]

I have a database in MySQL on Linux (for production) and macOS (for development). In that database some table names use mixed-case (e.g. articleGalleryImage). I was trying to write a database migration that is portable from development to production.

On macOS in development (mysql_fdw version 2.5.3, mysql server version 5.7.29 Homebrew, PostgreSQL server 12.3 also from Homebrew):

import foreign schema app_development limit to ("articleGalleryImage")
  from server mysql_server into mysql

completes successfully, but creates no foreign table, while

import foreign schema app_development limit to (articlegalleryimage)
  from server mysql_server into mysql

works, and creates a foreign table called articlegalleryimage.

On Linux (mysql_fdw version 2.5.3, mysql server version 5.7.31-0ubuntu0.18.04.1-log, PostgreSQL server 12.3 (Ubuntu 12.3-1.pgdg18.04+1)) the reverse is true and the foreign table is called articleGalleryImage. This makes it tricky to write a single statement that will work on both platforms. It also means that resulting schemas objects have different names.

I think the cause could be mysql's lower_case_table_names setting. On macOS the default for this is 2, while on Linux it is 0. I've pared down the query that is run to get the table details from MySQL's information schema to the following:

select t.table_name
from information_schema.tables as t
where t.table_name in ('articleGalleryImage');

Running this on macOS I get this:

+---------------------+
| table_name          |
+---------------------+
| articlegalleryimage |
+---------------------+

Note that the table_name value has been lower-cased: the actual table name is mixed case in the database schema, but this seems to be a quirk of MySQL. On Linux I get this:

+---------------------+
| table_name          |
+---------------------+
| articleGalleryImage |
+---------------------+

Reading https://dev.mysql.com/doc/refman/5.7/en/charset-collation-information-schema.html suggests a possible solution might be to force the collation - again on macOS:

select t.table_name from information_schema.tables as t where t.table_name collate utf8_general_ci in ('articleGalleryImage');
+---------------------+
| table_name          |
+---------------------+
| articleGalleryImage |
+---------------------+

I'll see if I can make that modification and report back.

[surajkharage19]

Thanks @mhw for reporting this issue. Your analysis seems correct to me. I too observed that mysql 5.7 has lower_case_table_names set to 2 by default and got below results. Will see how we can fix this.

MacOS mysql 5.7:

mysql> show variables like 'lowercase%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | ON | | lower_case_table_names | 2 | +------------------------+-------+ 2 rows in set (0.01 sec)

mysql> select t.table_name -> from information_schema.tables as t -> where t.table_name in ('articleGalleryImage'); +---------------------+ | table_name | +---------------------+ | articlegalleryimage | +---------------------+ 1 row in set (0.00 sec)

mysql> select t.table_name from information_schema.tables as t where t.table_name collate utf8_general_ci in ('articleGalleryImage'); +---------------------+ | table_name | +---------------------+ | articleGalleryImage | +---------------------+ 1 row in set (0.00 sec)`

[surajkharage19]

HI @mhw,

We have analysed this issue further and found that if we add collate utf8_general_ci clause with the table name then Unix platform behaviour is changing which is not correct. Please see the below example performed on MySQL 8 on the CentOS platform.

mysql> create table foobar(id int);
Query OK, 0 rows affected (0.05 sec)

mysql> create table FOOBAR(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table FooBar(id int);
Query OK, 0 rows affected (0.01 sec)

-- With collate clause.
mysql> select t.table_name from information_schema.tables as t where t.table_name collate utf8_general_ci in ('foobar');
+------------+
| TABLE_NAME |
+------------+
| foobar     |
| FOOBAR     |
| FooBar     |
+------------+
3 rows in set (0.01 sec)

-- Without collate clause.
mysql> select t.table_name from information_schema.tables as t where t.table_name in ('foobar');
+------------+
| TABLE_NAME |
+------------+
| foobar     |
+------------+
1 row in set (0.00 sec)

mysql> select t.table_name from information_schema.tables as t where t.table_name in ('FOOBAR');
+------------+
| TABLE_NAME |
+------------+
| FOOBAR     |
+------------+
1 row in set (0.00 sec)

mysql> select t.table_name from information_schema.tables as t where t.table_name in ('FooBar');
+------------+
| TABLE_NAME |
+------------+
| FooBar     |
+------------+
1 row in set (0.00 sec)

Moreover, Windows and macOS are the non-supported platforms for mysql_fdw. Please check below supported platform and we recommend using any of them. https://www.enterprisedb.com/docs/mysql_data_adapter/latest/02_requirements_overview/

Hope this helps.