Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
mhw opened this issue Aug 18, 2020 · 2 comments · May be fixed by #206
Open

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

mhw opened this issue Aug 18, 2020 · 2 comments · May be fixed by #206

Comments

@mhw
Copy link

mhw commented Aug 18, 2020

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
Copy link

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 'lower_case_%';
+------------------------+-------+
| 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)`

mhw added a commit to mhw/mysql_fdw that referenced this issue Sep 9, 2020
@mhw
Make IMPORT FOREIGN SCHEMA behave consistently
32c7ee8 
The case of the table names returned by information_schema queries
depends on MySQL's `lower_case_table_names` setting.
https://dev.mysql.com/doc/refman/5.7/en/charset-collation-information-schema.html
suggests forcing a suitable collation to work around the issue.

Fixes EnterpriseDB#202.
@mhw mhw linked a pull request Sep 9, 2020 that will close this issue
Open
mhw added a commit to mhw/mysql_fdw that referenced this issue Oct 14, 2020
@mhw
Make IMPORT FOREIGN SCHEMA behave consistently
c55ec1e 
The case of the table names returned by information_schema queries
depends on MySQL's `lower_case_table_names` setting.
https://dev.mysql.com/doc/refman/5.7/en/charset-collation-information-schema.html
suggests forcing a suitable collation to work around the issue.

Fixes EnterpriseDB#202.
@surajkharage19
Copy link

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.

@surajkharage19 surajkharage19 mentioned this issue Feb 26, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Make IMPORT FOREIGN SCHEMA behave consistently with mixed-case table names mhw/mysql_fdw
2 participants
@mhw @surajkharage19