[Bug] Primary key issues with MySQL

CodyPin commented 5 months ago

Search before asking

[X] I searched in the issues and found nothing similar.

Paimon version

0.7.0

Compute Engine

Flink 1.17.2

Minimal reproduce step

Not sure. The problem is inconsistent, on some MySQL server it won't happen, on some it will filter out most of the tables

What doesn't meet your expectations?

To load all the tables mentioned in the argument 'including_tables'

Anything else?

Removed by @CodyPin

Are you willing to submit a PR?

[ ] I'm willing to submit a PR!

CodyPin commented 5 months ago

I have also tried paimon-flink-action-0.8, specifically paimon-flink-action-0.8-20240507.002037-81.jar from https://repository.apache.org/content/groups/snapshots/org/apache/paimon/paimon-flink-action/0.8-SNAPSHOT/paimon-flink-action-0.8-20240507.002037-81.jar but it just have the same effects. Maybe its something to do with table's metadata? Just guessing at this point

MOBIN-F commented 5 months ago

Does the ignored table have a schema change? You can set --ignore-incompatible false to check whether the table has schema changes, or check the log for "This table will be ignored". paimon Cdc Ingestion supports a limited number of schema changes @CodyPin

CodyPin commented 5 months ago

@MOBIN-F Thanks for the reply, this is my first try loading this database, so I guess it wouldn't matter if any of the tables has a schema change? I am not sure, in any case those table's schema haven't been touched since they were created. But I set the --ignore-incompatible for testing just to see if there would be any difference, but no, removing --ignore-incompatible or setting it to true doesn't have any difference.

And just to give more background, initially I was doing this with Flink and sinking into Iceberg, which would be able to get all the tables required, thus leading me to think it might be a Paimon issue either than Flink

MOBIN-F commented 4 months ago

I reproduced the problem, and the reason is as stated in the official documentation: If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

CREATE TABLE test_implicit_pk ( id bigint(20) NOT null, name varchar(255) NOT NULL, school varchar(50) DEFAULT NULL, UNIQUE key uk_sta_id (id,name), UNIQUE KEY uk_sta (school) ) ENGINE=InnoDB

desc test_implicit_pk

Field |Type |Null|Key|Default|Extra|
------+------------+----+---+-------+-----+
id |bigint(20) |NO |PRI| | |
name |varchar(255)|NO |PRI| | |
school|varchar(50) |YES |UNI| | |

actual primary key information exists

show create table test_implicit_pk

CREATE TABLE `test_implicit_pk` (
   `id` bigint(20) NOT NULL,
   `name` varchar(255) NOT NULL,
   `school` varchar(50) DEFAULT NULL,
   UNIQUE KEY `uk_sta_id` (`id`,`name`),
   UNIQUE KEY `uk_sta` (`school`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

No primary key information is displayed

In this case, the primary key information cannot be obtained using metaData.getPrimaryKeys. Instead, we need to use metaData.getIndexInfo and metaData.getColumns to determine the primary key what do you think? @JingsongLi @yuzelin

yuzelin commented 4 months ago

In this case, the primary key information cannot be obtained using metaData.getPrimaryKeys. Instead, we need to use metaData.getIndexInfo and metaData.getColumns to determine the primary key

Thanks for your suggestion @MOBIN-F , I will check it later.

MOBIN-F commented 1 month ago

flink-cdc paimon pipeline connector does not have this problem

apache / paimon