surajkharage19
commented
3 years ago Hi,
I am not able to reproduce this issue on latest HEAD (8912bbb4e2ca53e8fe72c2a4b4b76dc8ef778f09), tried below steps:
Environment details:
OS: CentOS 7 PG version: 11.8 mysql version: 5.6.49
MySQL side:
-- Created partner table so that we can create foreign key.
create table partner(id int primary key);
-- Inserted values 1 to 10 like below in partner table
insert into partner values(1);
mysql> select count(*) from partner;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
-- Created document table like the same way.
create table document (
id int auto_increment primary key,
partner int null,
title varchar(255) null,
text longtext null,
constraint FK_D8698A76312B3E16 foreign key (partner) references partner (id))
collate=utf8_unicode_ci;
-- Created index
create index IDX_D8698A76312B3E16 on document (partner);
-- Inserted some random number of rows in document table like below:
insert into document(partner, title, text) values(1,'abc', 'abc');mysql> select count(*) from document;
+----------+
| count(*) |
+----------+
| 13 |
+----------+
1 row in set (0.02 sec)
Postgres side:
-- Created the foreign table as provided by you.
postgres@101109=#CREATE FOREIGN TABLE mysql_document(id integer not null, partner integer, title varchar(255), text text) server mysql_svr options (dbname 'suraj', table_name 'document');
CREATE FOREIGN TABLE
postgres@101109=#alter foreign table mysql_document owner to postgres;
ALTER FOREIGN TABLE
postgres@101109=#select count(*) from mysql_document ;
count
-------
13
(1 row)
postgres@101109=#select * from mysql_document ;
id | partner | title | text
----+---------+-------+------
1 | 1 | abc | abc
2 | 1 | abc | abc
3 | 2 | abc | abc
4 | 10 | abc | abc
6 | 8 | abc | abc
7 | 6 | abc | abc
8 | 4 | abc | abc
9 | 4 | abc | abc
10 | 4 | abc | abc
11 | 4 | abc | abc
12 | 7 | abc | abc
13 | 9 | abc | abc
14 | 5 | abc | abc
(13 rows)
postgres@101109=# \l+ postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
----------+--------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
postgres | centos | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7821 kB | pg_default | default administrative connection database
(1 row)
Can you please share below additional information to troubleshoot this further:
1: Exact OS version 2: What was mysql_fdw HEAD commit on which you tested this scenario? 3: If possible, share some problematic row sample data from your end. 4: In the last step, I can see you alter the foreign table owner to postgres, what was the earlier owner for that table? Whether postgres is a superuser? 5: It might be a collation issue, can you please share the collation details from both the databases?
mrAndersen
NordenF
Hello there, i have very strange problem with mysql_fdw PostgreSQL extension. Some data from postgres side is missing.
Mysql side table:
Generated with mysql_fdw table on postgres side:
After doing select * from old.document, i am getting only 5 records, however original table on mysql side consists of at least 60 records.
Mysql version is 5.7.31, Postges version is 11.8
What am i doing wrong?