apache
[Bug] Primary key issues with MySQL
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!
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
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
@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
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
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.
