luozihen opened a new issue, #12415: URL: https://github.com/apache/seatunnel/issues/12415
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22bug%22) and found no similar issues. ### What happened What happened: A MySQL JDBC sink that auto-creates its target table fails when one of the configured primary key columns is nullable in the source metadata. Setup: `generate_sink_sql = true`, `schema_save_mode = "CREATE_SCHEMA_WHEN_NOT_EXIST"` and `create_index = true` (the default), with the key columns configured through the top-level `primary_keys` option or through `multi_table_config.primary_keys`. If the target table does not exist yet, the sink generates the DDL and MySQL rejects it: java.sql.SQLSyntaxErrorException: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead wrapped by SeaTunnel as: CatalogException: ErrorCode:[API-03], ErrorDescription:[Catalog initialize failed] - Failed creating table sink.table1 SeaTunnelRuntimeException: ErrorCode:[API-09], ErrorDescription:[Handle save mode failed] Root cause: `MysqlCreateTableSqlBuilder#buildColumnIdentifySql` emits an explicit `NULL` for every column whose metadata says nullable, without checking whether that column is part of the primary key, while `buildPrimaryKeySql` is appended when `create_index` is true. The generated DDL therefore looks like: CREATE TABLE `table1` ( `c_int` int NULL COMMENT '', `c_integer` int NULL COMMENT '', ... PRIMARY KEY (`c_int`, `c_integer`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = ''; MySQL accepts a key column that is nullable only when the column is not declared `NULL` explicitly: omitting the keyword makes MySQL convert the column to `NOT NULL` implicitly, while an explicit `NULL` raises `ER_PRIMARY_CANT_HAVE_NULL` (1171). `OceanBaseMysqlCreateTableSqlBuilder` contains the same code. Workaround: declare the key columns as `NOT NULL` in the source table, or pre-create the target table so the sink does not generate the DDL. Proposed fix: in `MysqlCreateTableSqlBuilder#buildColumnIdentifySql` (and in the OceanBase MySQL-mode builder) emit `NOT NULL` when the column is part of the table's primary key, in addition to the existing `!column.isNullable()` case, and cover it with a unit test in `MysqlCreateTableSqlBuilderTest` that asserts a nullable key column is written as `NOT NULL`. Found while adding an E2E for `multi_table_config.primary_keys` (apache/seatunnel#11878, PR apache/seatunnel#12015). It is intentionally not fixed in that PR to keep it scoped; the E2E there uses dedicated source tables whose key columns are `NOT NULL`. ### SeaTunnel Version 3.0.0-SNAPSHOT (dev). Reproduced on commit c15f9fdc5 of PR https://github.com/apache/seatunnel/pull/12015. ### SeaTunnel Config ```conf env { parallelism = 1 job.mode = "BATCH" } source { jdbc { url = "jdbc:mysql://localhost:3306/seatunnel" driver = "com.mysql.cj.jdbc.Driver" username = "root" password = "******" table_list = [ { table_path = "source.table1" } ] } } sink { jdbc { url = "jdbc:mysql://localhost:3306/seatunnel" driver = "com.mysql.cj.jdbc.Driver" username = "root" password = "******" database = "sink" table = "${table_name}" generate_sink_sql = true schema_save_mode = "CREATE_SCHEMA_WHEN_NOT_EXIST" create_index = true primary_keys = ["c_int"] } } With the source table created as follows (the key column is nullable) and the target table `sink.table1` not existing: CREATE TABLE source.table1 ( `c_int` int(11) DEFAULT NULL, `c_varchar` varchar(64) DEFAULT NULL ); ``` ### Running Command ```shell ./bin/seatunnel.sh --config ./config/jdbc_mysql_nullable_pk.conf -e local ``` ### Error Exception ```log ... INFO org.apache.seatunnel.api.sink.DefaultSaveModeHandler - Creating table sink.table1 with action CREATE TABLE `table1` ( `c_int` int NULL COMMENT '', `c_varchar` varchar(64) NULL COMMENT '', PRIMARY KEY (`c_int`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = ''; ... INFO org.apache.seatunnel.connectors.seatunnel.jdbc.catalog.AbstractJdbcCatalog - Execute sql : CREATE TABLE `table1` ( ... ); Caused by: java.sql.SQLSyntaxErrorException: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead Caused by: org.apache.seatunnel.api.table.catalog.exception.CatalogException: ErrorCode:[API-03], ErrorDescription:[Catalog initialize failed] - Failed creating table sink.table1 Caused by: org.apache.seatunnel.common.exception.SeaTunnelRuntimeException: ErrorCode:[API-09], ErrorDescription:[Handle save mode failed] --- ``` ### Zeta or Flink or Spark Version Reproduced on the local Zeta engine and on the Zeta, Flink (1.13 / 1.15 / 1.18 / 1.20) and Spark (2.4 / 3.3) E2E containers. ### Java or Scala Version Java 8 and Java 11 (CI matrix). ### Screenshots _No response_ ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
