IMPORT FOREIGN SCHEMA not importing mixed case tables

[DavidBuch1]

Hi
I am connecting postgres 15 to mariadb, but the IMPORT FOREIGN SCHEMA seems to skip all the mixed case tables.
It imports all the lower case ones just fine though.

There are a LOT of tables, so doing it manually would be very cumbersome.

Can you advise please.

thanks

[surajkharage19]

Hi @DavidBuch1,

You are talking about this issue - #202?

[DavidBuch1]

hi. Yes.. looks similar to my issue, but I cant see how to get past it.
note, we are running on ubuntu 22.04, so this is not a windows or mac issue.
I have tried to force mariadb to store lowercase, but it doesnt take the setting, and seems to alwasy remain at 0

[surajkharage19]

Okay. Can you please just share a simple reproducible test case from your end? How do you create a mixed case table on MySQL, value of lower_case_table_names, and IMPORT FOREIGN SCHEMA command tried at your end?

[DavidBuch1]

Hi
Sure:
SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names'; value 0
setting in conf files
[mysqld]
lower_case_table_names=2

Mysql/Mariadb tables
CREATE TABLE debugLog (
id int(11) NOT NULL AUTO_INCREMENT,
routine varchar(255) DEFAULT NULL,
line int(11) DEFAULT NULL,
debugMsg varchar(1000) DEFAULT NULL,
isError int(11) DEFAULT NULL,
createdDate datetime DEFAULT current_timestamp(),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4265468 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

and from postgres
IMPORT FOREIGN SCHEMA xld
FROM SERVER xld_bridge
INTO imports;

all the lowercase tables import, but examples like the above dont.

its not practical to make them all lowercase, as there so many dependencies

[surajkharage19]

Thanks for sharing the test case.

I tried the same at my end and it is working fine. I am using MySQL 8 at my end.

mysql> CREATE TABLE debugLog (
    -> id int(11) NOT NULL AUTO_INCREMENT,
    -> routine varchar(255) DEFAULT NULL,
    -> line int(11) DEFAULT NULL,
    -> debugMsg varchar(1000) DEFAULT NULL,
    -> isError int(11) DEFAULT NULL,
    -> createdDate datetime DEFAULT current_timestamp(),
    -> PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4265468 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql> 
mysql> 
mysql> SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

Postgres:

edb@44896=#IMPORT FOREIGN SCHEMA s2 FROM SERVER mysql_server into s2;
IMPORT FOREIGN SCHEMA
edb@44896=#
edb@44896=#\d s2.*
                               Foreign table "s2.debugLog"
   Column    |            Type             | Collation | Nullable | Default | FDW options 
-------------+-----------------------------+-----------+----------+---------+-------------
 id          | integer                     |           | not null |         | 
 routine     | character varying(255)      |           |          |         | 
 line        | integer                     |           |          |         | 
 debugMsg    | character varying(1000)     |           |          |         | 
 isError     | integer                     |           |          |         | 
 createdDate | timestamp without time zone |           |          |         | 
Server: mysql_server
FDW options: (dbname 's2', table_name 'debugLog')

Can you please verify the same at your end once? From your update, I can see that you are using mariadb, if possible test the same on MySQL just to rule out the possibility of mariadb specific issue.

[DavidBuch1]

Hi It’s not possible to test on mysql as we have migrated from mysql 5 to mariadb, because of mysql 5 -> mysql 8 inconsistencies. This is really an interim measure anyway as we are moving to Postgres completely ands scrapping anything mysql/mariadb, but thats still a way away. Kind regards David Buch CEO & Founder Appzoola +61 451 753 960 +1 218-203-5343 Meeting request: https://calendly.com/appzoola  On 28 Feb 2024, at 14:44, surajkharage19 ***@***.***> wrote: Thanks for sharing the test case. I tried the same at my end and it is working fine. I am using MySQL 8 at my end. mysql> CREATE TABLE debugLog ( -> id int(11) NOT NULL AUTO_INCREMENT, -> routine varchar(255) DEFAULT NULL, -> line int(11) DEFAULT NULL, -> debugMsg varchar(1000) DEFAULT NULL, -> isError int(11) DEFAULT NULL, -> createdDate datetime DEFAULT current_timestamp(), -> PRIMARY KEY (id) -> ) ENGINE=InnoDB AUTO_INCREMENT=4265468 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; Query OK, 0 rows affected, 3 warnings (0.01 sec) mysql> mysql> mysql> SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 0 | +------------------------+-------+ 1 row in set (0.00 sec) Postgres: ***@***.***=#IMPORT FOREIGN SCHEMA s2 FROM SERVER mysql_server into s2; IMPORT FOREIGN SCHEMA ***@***.***=# ***@***.***=#\d s2.* Foreign table "s2.debugLog" Column | Type | Collation | Nullable | Default | FDW options -------------+-----------------------------+-----------+----------+---------+------------- id | integer | | not null | | routine | character varying(255) | | | | line | integer | | | | debugMsg | character varying(1000) | | | | isError | integer | | | | createdDate | timestamp without time zone | | | | Server: mysql_server FDW options: (dbname 's2', table_name 'debugLog') Can you please verify the same at your end once? From your update, I can see that you are using mariadb, if possible test the same on MySQL just to rule out the possibility of mariadb specific issue. — Reply to this email directly, view it on GitHub <#294 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQBHMQ56EMY6BW56Z5CTG2TYV2Y2VAVCNFSM6AAAAABDYT5A7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNRYGIYTSNZRGQ>. You are receiving this because you were mentioned.