COLDMANYU opened a new issue, #11096: URL: https://github.com/apache/paimon/issues/11096
### Search before asking - [x] I searched in the [issues](https://github.com/apache/paimon/issues) and found nothing similar. ### Paimon version 2.0.0, and master `274779c878`. The table was added in 1.4.0 by #7423, so 1.4.x is affected too. ### Compute Engine Java API (catalog initialization, so any engine). Found through Apache Gravitino, whose Paimon catalog creates a Paimon `JdbcCatalog`. ### Minimal reproduce step 1. Start MySQL 8.0 with its default character set `utf8mb4` (tested on 8.0.46). 2. Create a JDBC catalog on it. For example, in Spark: ``` spark.sql.catalog.paimon=org.apache.paimon.spark.SparkCatalog spark.sql.catalog.paimon.metastore=jdbc spark.sql.catalog.paimon.uri=jdbc:mysql://<host>:3306/paimon_meta spark.sql.catalog.paimon.jdbc.user=... spark.sql.catalog.paimon.jdbc.password=... spark.sql.catalog.paimon.warehouse=... ``` The catalog fails while it creates its tables. The trace below is from Paimon 2.0.0, created through Gravitino: ``` java.lang.RuntimeException: Cannot initialize JDBC catalog at org.apache.paimon.jdbc.JdbcCatalog.<init>(JdbcCatalog.java:134) Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes at org.apache.paimon.jdbc.JdbcCatalog.initializeCatalogTablesIfNeed(JdbcCatalog.java:187) ``` The statement that fails is `JdbcUtils.CREATE_TABLE_PROPERTIES_TABLE`. Running it directly in MySQL gives the same error: ```sql CREATE TABLE paimon_table_properties( catalog_key VARCHAR(255) NOT NULL, database_name VARCHAR(255) NOT NULL, table_name VARCHAR(255) NOT NULL, property_key VARCHAR(255) NOT NULL, property_value VARCHAR(1000), PRIMARY KEY (catalog_key, database_name, table_name, property_key)); -- ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes ``` ### What doesn't meet your expectations? A JDBC catalog on MySQL with `utf8mb4` should start. Before 1.4.0 this table did not exist, and the other catalog tables fit. The primary key has four `VARCHAR(255)` columns. With `utf8mb4` each character takes up to 4 bytes, so the key is 4 x 255 x 4 = 4080 bytes, above InnoDB's 3072-byte limit. The other catalog tables (`paimon_tables`, `paimon_database_properties`, `paimon_views`) have three-column keys (3060 bytes) and are created without problems. What I checked on MySQL 8.0.46: | Case | Result | |---|---| | Statement above, `utf8mb4` database | ERROR 1071 | | Statement above, `utf8mb3` database | Table created (key is 3060 bytes) | | `paimon_tables`, `paimon_database_properties`, `paimon_views`, `utf8mb4` database | Tables created | Details: - The table is created on every start, whether `sync-all-properties` is true or false (`initializeCatalogTablesIfNeed` does not check the option), so turning the option off does not help. - `utf8mb4` is the default character set since MySQL 8.0, so a new MySQL 8 database hits this unless its character set is changed. - The existing JDBC catalog tests run on SQLite and PostgreSQL, which do not check the declared key length when the table is created, so CI does not catch it. ### Anything else? A database that already has `paimon_table_properties` is not affected, because the catalog only creates the table when it is missing. Creating the table by hand before the first start is a workaround. Possible fixes I can think of (I have not picked one, and would like to hear which is preferred): 1. Make some key columns shorter, e.g. `catalog_key` and `property_key`. This adds new length limits. 2. Use a surrogate primary key and a non-unique index on `(catalog_key, database_name, table_name)`. Every statement on this table filters by these three columns or by the first two of them, and properties are always deleted and re-inserted together, so the existing statements would not change. The DDL would differ per database (auto-increment syntax), and the database would no longer reject duplicate rows for the same property. 3. Keep the current DDL and document the workaround for MySQL with `utf8mb4`. ### Are you willing to submit a PR? - [x] I'm willing to submit a PR! -- 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]
