You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by GitBox <gi...@apache.org> on 2022/05/11 17:36:49 UTC
[GitHub] [flink] dusukang opened a new pull request, #19703: Modify JDBC Catalog
dusukang opened a new pull request, #19703:
URL: https://github.com/apache/flink/pull/19703
The primary key obtained by MysqlCatalog is inconsistent with the primary key of the database table
Here are my case:
I want to use MysqlCatalog to obtain the information of the database table user. The database table creation statement is as follows
```
CREATE TABLE flinksql_test.`user` (
`uid` bigint(20) NOT NULL,
`uname` varchar(36) DEFAULT NULL,
`others` varchar(128) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
This is my test code:
```
import org.apache.flink.connector.jdbc.catalog.MySqlCatalog;
import org.apache.flink.table.api.Schema;
import org.apache.flink.table.catalog.CatalogBaseTable;
import org.apache.flink.table.catalog.ObjectPath;
import org.apache.flink.table.catalog.exceptions.TableNotExistException;
import java.util.Optional;
public class Demo02 {
public static void main(String[] args) throws TableNotExistException {
MySqlCatalog mySqlCatalog = new MySqlCatalog("mysql-catalog",
"flinksql_test",
"root",
"123456789",
String.format("jdbc:mysql://127.0.0.1:3306"));
CatalogBaseTable table = mySqlCatalog.getTable(new ObjectPath("flinksql_test", "user"));
Optional<Schema.UnresolvedPrimaryKey> primaryKey = table
.getUnresolvedSchema()
.getPrimaryKey();
System.out.println(primaryKey);
}
}
```
The obtained primary key is (Host,User), The primary key from Database is uid
<img width="1007" alt="截屏2022-05-12 上午1 10 54" src="https://user-images.githubusercontent.com/68139929/167908239-c6f3f0ad-af06-436f-87e2-85c60428b400.png">
In my opinion, when obtaining the primary key, the value of the incoming catalog and schema is null, and the SQL splicing of the database to obtain the primary key does not add " TABLE_SCHEMA LIKE ? AND"
<img width="491" alt="截屏2022-05-12 上午1 25 31" src="https://user-images.githubusercontent.com/68139929/167910188-6df6f3ec-cb33-49cc-91d5-61dcb1167c98.png">
<img width="985" alt="截屏2022-05-12 上午1 09 55" src="https://user-images.githubusercontent.com/68139929/167908960-cf873c66-a227-41fa-99ea-5cff8a181f29.png">
<img width="974" alt="截屏2022-05-12 上午1 08 57" src="https://user-images.githubusercontent.com/68139929/167909024-22b15192-0755-416e-8421-dab0fdfc0d15.png">
Later, it was found that there was also a user table in the self-contained MySQL database with the following structure:
```
CREATE TABLE mysql.`user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
`Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
`Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`User_attributes` json DEFAULT NULL,
PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges';
```
Therefore, is it necessary to obtain the primary key by passing schema and catalog information.In the later test, I added the transmission of schema and catalog information, and the corresponding primary key can be found here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@flink.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [flink] flinkbot commented on pull request #19703: Modify JDBC Catalog
Posted by GitBox <gi...@apache.org>.
flinkbot commented on PR #19703:
URL: https://github.com/apache/flink/pull/19703#issuecomment-1124067035
<!--
Meta data
{
"version" : 1,
"metaDataEntries" : [ {
"hash" : "82f1819d73fb2ff5a151eb2c2a79d968fbe89274",
"status" : "UNKNOWN",
"url" : "TBD",
"triggerID" : "82f1819d73fb2ff5a151eb2c2a79d968fbe89274",
"triggerType" : "PUSH"
} ]
}-->
## CI report:
* 82f1819d73fb2ff5a151eb2c2a79d968fbe89274 UNKNOWN
<details>
<summary>Bot commands</summary>
The @flinkbot bot supports the following commands:
- `@flinkbot run azure` re-run the last Azure build
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@flink.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [flink] dusukang closed pull request #19703: Modify JdbcCatalog to get primary key In scenarios with the same table name
Posted by GitBox <gi...@apache.org>.
dusukang closed pull request #19703: Modify JdbcCatalog to get primary key In scenarios with the same table name
URL: https://github.com/apache/flink/pull/19703
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@flink.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org