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 - https://github.com/EnterpriseDB/mysql_fdw/issues/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 https://github.com/EnterpriseDB/mysql_fdw/issues/294#issuecomment-1968219714, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQBHMQ56EMY6BW56Z5CTG2TYV2Y2VAVCNFSM6AAAAABDYT5A7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNRYGIYTSNZRGQ. You are receiving this because you were mentioned.