[
https://issues.apache.org/jira/browse/QPID-8122?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16395085#comment-16395085
]
Keith Wall commented on QPID-8122:
----------------------------------
MariaDB does support {{BINARY}} and {{VARBINARY}}
[https://mariadb.com/kb/en/library/data-types/]
The problematic statement the PRIMARY KEY of the following DDL:
{code:java}
stmt.execute("CREATE TABLE " + getConfiguredObjectHierarchyTableName()
+ " ( child_id VARCHAR(36) not null, parent_type varchar(255), parent_id
VARCHAR(36), PRIMARY KEY (child_id, parent_type))");{code}
{{utf8mb4}} uses a maximum of 4 bytes to store each Unicode character (it
allows Unicode supplementary characters), so I think MariaDB is computing the
index key storage requirement to be:
{code:java}
(36 * 4) + (255 * 4) = 1164{code}
This exceeds the 767 byte restriction.
We could change the {{child_id}} so that it is stored as a {{VARBINARY(16)}}
saving 20 bytes. This leaves 751 bytes for the storage of the {{parent_type}}.
We could opt to store the type as a {{VARCHAR(187)}} or a {{VARBINARY(751)}}
and, as Rob suggests, encode the UTF-8 ourselves.
We don't normally expect users to be inspecting the database underneath
Broker-J but if they needs to query these tables, perhaps for diagnostic
purposes, the MariaDB built ins {{UUID_TO_BIN()}} and {{BIN_TO_UUID()}} would
be convenient. Keeping the database knowing that the {{parent_type}} is text
would also help adhoc querying.
> [Broker-J] Create JDBC backed virtualhost node fails on MariaDB when database
> charset is utf8mb4
> ------------------------------------------------------------------------------------------------
>
> Key: QPID-8122
> URL: https://issues.apache.org/jira/browse/QPID-8122
> Project: Qpid
> Issue Type: Bug
> Components: Broker-J
> Environment: 10.0.34-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
> Reporter: Keith Wall
> Priority: Major
>
> With MariaDB 10.0.34 on Ubuntu 16.04 defaults its database character set type
> to {{utf8mb4}}.
> {noformat}
> MariaDB [qpid]> show variables like 'character_set_database';
> +------------------------+--------+
> | Variable_name | Value |
> +------------------------+--------+
> | character_set_database | utf8mb4 |
> +------------------------+--------+
> 1 row in set (0.00 sec)
> {noformat}
> If I try and create a JDBC backed VHN, it fails with the following exception:
> {noformat}
> 2018-03-09 13:22:58,067 ERROR [Broker-Config]
> (o.a.q.s.u.ServerScopedRuntimeException) - Unable to open configuration tables
> java.sql.SQLSyntaxErrorException: (conn=173) Specified key was too long; max
> key length is 767 bytes
> at
> org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:177)
> at
> org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
> at
> org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
> at
> org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
> at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
> at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:300)
> at
> org.apache.qpid.server.store.jdbc.AbstractJDBCConfigurationStore.createConfiguredObjectHierarchyTable(AbstractJDBCConfigurationStore.java:497)
> at
> org.apache.qpid.server.store.jdbc.AbstractJDBCConfigurationStore.createOrOpenConfigurationStoreDatabase(AbstractJDBCConfigurationStore.java:448)
> {noformat}
> The primary key length of the QPID_CONFIGURED_OBJECT_HIERARCHY is too long in
> this character set mode.
> It seems other MariaDB packages on other distributions default to {{latin1}}
> which does not face this problem. Switching to {{latin1}} like so is a
> workaround:
> {noformat}
> ALTER DATABASE qpid CHARACTER SET latin1{noformat}
> However, this would mean that a characters appearing in configuration items
> outwith latin1 would be lost.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
