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]

Reply via email to