EPrints Technical Mailing List Archive

Message: #05864


< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] Primary key on [dataset]__index tables


Hi John,

 

Thanks for sharing this!  We are also running 3.3.12 (upgraded from 3.2.4), and we have this:

 

*************************** 1. row ***************************

       Table: eprint__index

Create Table: CREATE TABLE `eprint__index` (

  `fieldword` varchar(255) DEFAULT NULL,

  `pos` int(11) DEFAULT NULL,

  `ids` text,

  KEY `eprint__index_pos` (`pos`),

  KEY `eprint__index_fieldword_pos` (`fieldword`,`pos`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

 

No primary key, and no UTF-8 on the CHARSET.   That is not so good – how do we fix this?

Could it also explain why our search is having difficulties with some French characters?

 

Best wishes,

 

Tomasz

 

 

 

From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of John Salter
Sent: August-08-16 9:47 AM
To: 'eprints-tech@ecs.soton.ac.uk' <eprints-tech@ecs.soton.ac.uk>
Subject: [EP-tech] Primary key on [dataset]__index tables

 

Hi,

I’ve been investigating some slowness issues with our database and discovered that some of the tables don’t have primary keys – e.g. eprint__index.

On our live system running 3.3.10 (migrated from 3.1.1, and 2.[something] before then), this is the table definition:

mysql> SHOW CREATE TABLE eprint__index \G

*************************** 1. row ***************************

       Table: eprint__index

Create Table: CREATE TABLE `eprint__index` (

  `fieldword` varchar(255) DEFAULT NULL,

  `pos` int(11) DEFAULT NULL,

  `ids` text,

  KEY `pos` (`pos`),

  KEY `fieldword` (`fieldword`,`pos`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

There are two keys, but no primary key.

 

On a dev install of EPrints 3.3.12, this is the table definition:

mysql> SHOW CREATE TABLE eprint__index \G

*************************** 1. row ***************************

       Table: eprint__index

Create Table: CREATE TABLE `eprint__index` (

  `fieldword` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `pos` int(11) NOT NULL,

  `ids` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`fieldword`,`pos`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

A primary key exists.

There are differences in the version of MySQL, but it looks like there’s a possibility that during an upgrade either the __index tables were missed out of some update, or excluded for some reason.

There is also the difference in character set – which should be caught by ~/bin/epadmin:upgrade_3_1_2_to_3_2_0

 

Inspecting the code, this block: https://github.com/eprints/eprints/blob/3.3/perl_lib/EPrints/Database.pm#L599-L605 will not add the primary key to existing tables.

 

Can anyone shed any light on this?

Do your __index tables have a primary key?

 

Cheers,

John