Ticket #59 (closed defect: fixed)

Opened 8 years ago

Last modified 7 years ago

Current length of index names breaks mySQL

Reported by: smith@… Owned by: smartp@…
Priority: major Milestone: v1.2.0
Component: Raptor MUA Version: v0.2.0
Keywords: Cc:

Description

Need to shorten the index names from the gigantically long names they currently have.


mysql> create index !EzproxyAuthenticationEventAuthenticationEventorganisationName_indx on EzproxyAuthenticationEvent (organisationName);
ERROR 1059 (42000): Identifier name '!EzproxyAuthenticationEventAuthenticationEventorganisationName_indx' is too long


Reported by @fooflington.

Change History

comment:1 Changed 8 years ago by smith@…

  • Owner changed from smith@… to smartp@…
  • Status changed from new to assigned

comment:2 Changed 8 years ago by https://sid.kent.ac.uk/shibboleth!https://iam.cf.ac.uk/sp/shibboleth!zvh7tlm4lp9+gcgbtxeqnb6ldtw=

Appears to have some level of effect in Postgres too, but doesn't error:

$ psql <mua-schema.sql 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authenticationevent_pkey" for table "authenticationevent"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ezproxyauthenticationevent_pkey" for table "ezproxyauthenticationevent"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "shibbolethidpauthenticationevent_pkey" for table "shibbolethidpauthenticationevent"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "event_pkey" for table "event"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "release_information_pkey" for table "release_information"
CREATE TABLE
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "resource_metadata_pkey" for table "resource_metadata"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
NOTICE:  identifier "ezproxyauthenticationeventauthenticationeventorganisationname_indx" will be truncated to "ezproxyauthenticationeventauthenticationeventorganisationname_i"
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
NOTICE:  identifier "shibbolethidpauthenticationeventauthenticationeventrequesthost_indx" will be truncated to "shibbolethidpauthenticationeventauthenticationeventrequesthost_"
CREATE INDEX
CREATE INDEX
NOTICE:  identifier "shibbolethidpauthenticationeventauthenticationeventserviceid_indx" will be truncated to "shibbolethidpauthenticationeventauthenticationeventserviceid_in"
CREATE INDEX
NOTICE:  identifier "shibbolethidpauthenticationeventauthenticationeventorganisationname_indx" will be truncated to "shibbolethidpauthenticationeventauthenticationeventorganisation"
CREATE INDEX
NOTICE:  identifier "shibbolethidpauthenticationeventauthenticationeventservicename_indx" will be truncated to "shibbolethidpauthenticationeventauthenticationeventservicename_"
CREATE INDEX
NOTICE:  identifier "shibbolethidpauthenticationeventauthenticationevententityid_index" will be truncated to "shibbolethidpauthenticationeventauthenticationevententityid_ind"
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  relation "hashcode_indx" already exists
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE SEQUENCE
CREATE SEQUENCE
CREATE TABLE
INSERT 0 1

comment:3 Changed 8 years ago by smith@…

  • Milestone changed from v0.3.0 to v0.2.1

comment:4 Changed 8 years ago by smartp@…

  • Status changed from assigned to accepted

comment:5 Changed 8 years ago by smartp@…

  • Status changed from accepted to closed
  • Resolution set to fixed

truncated the index names within Hibernate.

comment:6 Changed 8 years ago by smith@…

Fixed in v0.2.1

comment:7 Changed 8 years ago by https://sid.kent.ac.uk/shibboleth!https://iam.cf.ac.uk/sp/shibboleth!zvh7tlm4lp9+gcgbtxeqnb6ldtw=

  • Status changed from closed to reopened
  • Resolution fixed deleted

I beleive this is still a problem...

Fresh install of raptor from yum:

# yum list installed raptor*
Loaded plugins: product-id, rhnplugin, security, subscription-manager
Updating certificate-based repositories.
Installed Packages
raptor-mua.noarch                                              1.0.1-1.1                                           @home_rhyssmith_raptor
raptor-server.noarch                                           1.0.0-6.1                                           @home_rhyssmith_raptor
raptor-web.noarch                                              1.0.1-1.1                                           @home_rhyssmith_raptor

into empty mysql instance (using InnoDB):

<prop key="hibernate.dialect"> org.hibernate.dialect.MySQLInnoDBDialect</prop>

Tables create fine but only with PRIMARY KEY indexes:

mysql> SELECT DISTINCT
    ->     TABLE_NAME,
    ->     INDEX_NAME
    -> FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'mua';
+----------------------------------+--------------------+
| TABLE_NAME                       | INDEX_NAME         |
+----------------------------------+--------------------+
| AuthenticationEvent              | PRIMARY            |
| EzproxyAuthenticationEvent       | PRIMARY            |
| ShibbolethIdpAuthenticationEvent | PRIMARY            |
| event                            | PRIMARY            |
| latestEqualEntries               | FK283FC3E3AAC8B6DE |
| release_information              | PRIMARY            |
| resource_metadata                | PRIMARY            |
+----------------------------------+--------------------+
7 rows in set (0.00 sec)

Taking the schema from http://iam.cf.ac.uk/trac/RAPTOR/wiki/Software/Productionalisation/MuaSchema, grepping down to the create index lines and applying regexes:

%s/EzproxyAuthenticationEventAuthenticationEvent/EzpAuthEvnt/g
%s/ShibbolethIdpAuthenticationEventAuthenticationEvent/ShibAuthEvt/g

... causes (most of the) indexes to be applied. One remains uncreated:

# mysql -u root -p mua </root/mua-index.sql 
Enter password: 
ERROR 1170 (42000) at line 3: BLOB/TEXT column 'serviceHost' used in key specification without a key length

That was:

create index AuthenticationEventrequesthost_indx on AuthenticationEvent (serviceHost);

Which applies to:

mysql> show create table AuthenticationEvent\G
*************************** 1. row ***************************
       Table: AuthenticationEvent
Create Table: CREATE TABLE `AuthenticationEvent` (
  `persistantId` bigint(20) NOT NULL,
  `eventtime` datetime DEFAULT NULL,
  `serviceHost` text,
  `resourceHost` varchar(255) DEFAULT NULL,
  `serviceId` varchar(255) DEFAULT NULL,
  `resourceId` varchar(255) DEFAULT NULL,
  `eventId` int(11) NOT NULL,
  `eventType` varchar(255) DEFAULT NULL,
  `resourceIdCategory` int(11) DEFAULT NULL,
  `entityId` varchar(255) DEFAULT NULL,
  `serviceName` varchar(255) DEFAULT NULL,
  `organisationName` varchar(255) DEFAULT NULL,
  `authenticationType` varchar(255) DEFAULT NULL,
  `principalName` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `affiliation` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`persistantId`),
  KEY `AuthenticationEventdt_index` (`eventtime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

comment:8 Changed 7 years ago by smith@…

  • Status changed from reopened to assigned
  • Milestone changed from v0.2.1 to v1.2.0

comment:9 Changed 7 years ago by smartp@…

Fixing this in hibernate is proving a problem, most allow 128 characters which I think now they all are, but some limit to SQL-92 30 characters, trying to get them to that size is more difficult through hibernate - will keep trying.

comment:10 Changed 7 years ago by smartp@…

  • Status changed from assigned to closed
  • Resolution set to fixed

Have added support for this with a new hibernate mapping file, denoted; event-name-limit.hbm.xml. However this would break current function if deployed to an existing system that has been using the old file - again notes need to be provided on the wiki.

Note: See TracTickets for help on using tickets.