[GeoNetwork-users] Migration to Mysql failure

GeoNetwork GeoNetwork User
1

Ubuntu linux 8.10 32bit using Sun java runtime.
Using GAST I tried to setup a mysql database instead of the built-in.
Configuration went fine

When I went to Database Setup I get:
Cyclic reference found:
[list of modules]
Running it a second time the list got smaller but still lists
[Metadata, MetadataCateg, OperationAllowed, MetadataRating]
-Install notes, says this doesn't matter

Then I click ok and get
"Specified key was too long; max key length is 1000 bytes"

Looking at the cli output I see it happens right after
[gast] - Creating database schema

Under this config, Geonetwork will start, but various features are
missing, the overview map, login doesn't work, etc.

Any idea what went wrong in setting up the Mysql database, did I miss a
permission or something?

Thanks,
Alex

2

Hi Alex,
You can search the nabble archives for an earlier thread on this. My solution was to change the endcoding of the database to LATIN1. The underlying problem is that the key length of the metadata table ends up exceeding the maximum key length if the database encoding is set to UTF8. The underlying problem however is that the fields in the database are instantiated with too many characters. If you need to use UTF8 encoding, then you may be able to modify the schema manually. I did not try this however.

I am including my response on an earlier thread below, and would suggest that the schema of the metadata table to be changed (developers comments here???).

You should be able to ignore the cyclic reference errors.

Regards,
Jason Pickering

----
Glad to see that LATIN1 worked. However, if you need to use UTF8
encoding, then I suspect that the database needs to be modified.

I believe this is causing the problem.
CREATE TABLE `Metadata` (
`id` int(11) NOT NULL default '0',
`uuid` varchar(250) collate latin1_bin NOT NULL,
`schemaId` varchar(32) collate latin1_bin NOT NULL,
`isTemplate` char(1) collate latin1_bin NOT NULL default 'n',
`isHarvested` char(1) collate latin1_bin NOT NULL default 'n',
`createDate` varchar(24) collate latin1_bin NOT NULL,
`changeDate` varchar(24) collate latin1_bin NOT NULL,
`data` text collate latin1_bin NOT NULL,
`source` varchar(250) collate latin1_bin NOT NULL,
`title` varchar(255) collate latin1_bin default NULL,
`root` varchar(255) collate latin1_bin default NULL,
`harvestUuid` varchar(250) collate latin1_bin default NULL,
`owner` int(11) NOT NULL,
`groupOwner` int(11) default NULL,
`harvestUri` varchar(255) collate latin1_bin default NULL,
`rating` int(11) NOT NULL default '0',
`popularity` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`,`source`,`harvestUuid`),
KEY `owner` (`owner`),
KEY `groupOwner` (`groupOwner`),
KEY `MetadataNDX1` (`uuid`),
KEY `MetadataNDX2` (`source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

This is my metadata table as dumped from MySQL. The unique key length is
too long . However, the composite fields, namely uuid and harvestUuid
are listed at varchar (255). This field length is too long, and I assume
that this data could be stored in a much shorter field length. I have
not delved into the code either at this point, but see no reason why a
UUID needs to be stored in a varchar(255) column. This would have the
effect of shortening the key length certainly improving performance. If
you need multibyte encodings such as UTF8, shorten the field length to
32 and see what happens.

Maybe the developers can comment here?

-----Original Message-----
From: Alex Mandel [mailto:tech_dev@anonymised.com]
Sent: Thu 2009-04-16 00:16
To: geonetwork-users@lists.sourceforge.net
Subject: [GeoNetwork-users] Migration to Mysql failure

Ubuntu linux 8.10 32bit using Sun java runtime.
Using GAST I tried to setup a mysql database instead of the built-in.
Configuration went fine

When I went to Database Setup I get:
Cyclic reference found:
[list of modules]
Running it a second time the list got smaller but still lists
[Metadata, MetadataCateg, OperationAllowed, MetadataRating]
-Install notes, says this doesn't matter

Then I click ok and get
"Specified key was too long; max key length is 1000 bytes"

Looking at the cli output I see it happens right after
[gast] - Creating database schema

Under this config, Geonetwork will start, but various features are
missing, the overview map, login doesn't work, etc.

Any idea what went wrong in setting up the Mysql database, did I miss a
permission or something?

Thanks,
Alex

------------------------------------------------------------------------------
Stay on top of everything new and different, both inside and
around Java (TM) technology - register by April 22, and save
$200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco.
300 plus technical and hands-on sessions. Register today.
Use priority code J9JMT32. http://p.sf.net/sfu/p
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

3

So indeed there is a problem with the GAST create-mysql sql script. When
I created my database I was using MySQL Admininstrator and it didn't
prompt me for encoding. Also after some discussion it seems utf-8 should
be the default as many text fields might have international characters.

2 possible solutions exist and I wanted to get feedback on which one the
developers prefer.

1. Simply shortening the varchar length of the fields included in Create
TABLE Metadata(...unique(uuid,source,harvestUuid),...) worked.

2. Specifying specific fields to be latin1 while maintaining an overall
utf8 encoding ala something like:

CREATE TABLE IF NOT EXISTS `Metadata` (
  `id` int(11) NOT NULL default '0',
  `uuid` varchar(250) character set latin1 collate latin1_bin NOT NULL,
  `schemaId` varchar(32) character set latin1 collate latin1_bin NOT NULL,
  `isTemplate` char(1) NOT NULL default 'n',
  `isHarvested` char(1) NOT NULL default 'n',
  `createDate` varchar(24) NOT NULL,
  `changeDate` varchar(24) NOT NULL,
  `data` longtext NOT NULL,
  `source` varchar(250) character set latin1 collate latin1_bin NOT NULL,
  `title` varchar(255) default NULL,
  `root` varchar(255) default NULL,
  `harvestUuid` varchar(250) character set latin1 collate latin1_bin
default NULL,
  `owner` int(11) NOT NULL,
  `groupOwner` int(11) default NULL,
  `harvestUri` varchar(255) character set latin1 collate latin1_bin
default NULL,
  `rating` int(11) NOT NULL default '0',
  `popularity` int(11) NOT NULL default '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`,`source`,`harvestUuid`),
  KEY `owner` (`owner`),
  KEY `groupOwner` (`groupOwner`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I'm not sure of the ramifications either way since I don't know the
internals yet.

Thanks,
Alex

Pickering, Jason wrote:

Hi Alex,
You can search the nabble archives for an earlier thread on this. My solution was to change the endcoding of the database to LATIN1. The underlying problem is that the key length of the metadata table ends up exceeding the maximum key length if the database encoding is set to UTF8. The underlying problem however is that the fields in the database are instantiated with too many characters. If you need to use UTF8 encoding, then you may be able to modify the schema manually. I did not try this however.

I am including my response on an earlier thread below, and would suggest that the schema of the metadata table to be changed (developers comments here???).

You should be able to ignore the cyclic reference errors.

Regards,
Jason Pickering

----
Glad to see that LATIN1 worked. However, if you need to use UTF8
encoding, then I suspect that the database needs to be modified.

I believe this is causing the problem.
CREATE TABLE `Metadata` (
`id` int(11) NOT NULL default '0',
`uuid` varchar(250) collate latin1_bin NOT NULL,
`schemaId` varchar(32) collate latin1_bin NOT NULL,
`isTemplate` char(1) collate latin1_bin NOT NULL default 'n',
`isHarvested` char(1) collate latin1_bin NOT NULL default 'n',
`createDate` varchar(24) collate latin1_bin NOT NULL,
`changeDate` varchar(24) collate latin1_bin NOT NULL,
`data` text collate latin1_bin NOT NULL,
`source` varchar(250) collate latin1_bin NOT NULL,
`title` varchar(255) collate latin1_bin default NULL,
`root` varchar(255) collate latin1_bin default NULL,
`harvestUuid` varchar(250) collate latin1_bin default NULL,
`owner` int(11) NOT NULL,
`groupOwner` int(11) default NULL,
`harvestUri` varchar(255) collate latin1_bin default NULL,
`rating` int(11) NOT NULL default '0',
`popularity` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`,`source`,`harvestUuid`),
KEY `owner` (`owner`),
KEY `groupOwner` (`groupOwner`),
KEY `MetadataNDX1` (`uuid`),
KEY `MetadataNDX2` (`source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

This is my metadata table as dumped from MySQL. The unique key length is
too long . However, the composite fields, namely uuid and harvestUuid
are listed at varchar (255). This field length is too long, and I assume
that this data could be stored in a much shorter field length. I have
not delved into the code either at this point, but see no reason why a
UUID needs to be stored in a varchar(255) column. This would have the
effect of shortening the key length certainly improving performance. If
you need multibyte encodings such as UTF8, shorten the field length to
32 and see what happens.

Maybe the developers can comment here?

-----Original Message-----
From: Alex Mandel [mailto:tech_dev@anonymised.com]
Sent: Thu 2009-04-16 00:16
To: geonetwork-users@lists.sourceforge.net
Subject: [GeoNetwork-users] Migration to Mysql failure

Ubuntu linux 8.10 32bit using Sun java runtime.
Using GAST I tried to setup a mysql database instead of the built-in.
Configuration went fine

When I went to Database Setup I get:
Cyclic reference found:
[list of modules]
Running it a second time the list got smaller but still lists
[Metadata, MetadataCateg, OperationAllowed, MetadataRating]
-Install notes, says this doesn't matter

Then I click ok and get
"Specified key was too long; max key length is 1000 bytes"

Looking at the cli output I see it happens right after
[gast] - Creating database schema

Under this config, Geonetwork will start, but various features are
missing, the overview map, login doesn't work, etc.

Any idea what went wrong in setting up the Mysql database, did I miss a
permission or something?

Thanks,
Alex

------------------------------------------------------------------------------
Stay on top of everything new and different, both inside and
around Java (TM) technology - register by April 22, and save
$200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco.
300 plus technical and hands-on sessions. Register today.
Use priority code J9JMT32. http://p.sf.net/sfu/p
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

4

Hi Alex,
Good to hear it worked, and I think your solution is certainly better that setting the entire DB to Latin1.

I would suggest that you (and the devs) might want to further shorten the uuid (and the harvestuuid) field to 36 characters. They should always be 36 characters, so I am not sure why they have been implemented as varchar(250). This should speed up searches and make the DB a bit smaller.

Regards,
Jason

Alex Mandel wrote:

So indeed there is a problem with the GAST create-mysql sql script. When
I created my database I was using MySQL Admininstrator and it didn't
prompt me for encoding. Also after some discussion it seems utf-8 should
be the default as many text fields might have international characters.

2 possible solutions exist and I wanted to get feedback on which one the
developers prefer.

1. Simply shortening the varchar length of the fields included in Create
TABLE Metadata(...unique(uuid,source,harvestUuid),...) worked.

2. Specifying specific fields to be latin1 while maintaining an overall
utf8 encoding ala something like:

CREATE TABLE IF NOT EXISTS `Metadata` (
  `id` int(11) NOT NULL default '0',
  `uuid` varchar(250) character set latin1 collate latin1_bin NOT NULL,
  `schemaId` varchar(32) character set latin1 collate latin1_bin NOT NULL,
  `isTemplate` char(1) NOT NULL default 'n',
  `isHarvested` char(1) NOT NULL default 'n',
  `createDate` varchar(24) NOT NULL,
  `changeDate` varchar(24) NOT NULL,
  `data` longtext NOT NULL,
  `source` varchar(250) character set latin1 collate latin1_bin NOT NULL,
  `title` varchar(255) default NULL,
  `root` varchar(255) default NULL,
  `harvestUuid` varchar(250) character set latin1 collate latin1_bin
default NULL,
  `owner` int(11) NOT NULL,
  `groupOwner` int(11) default NULL,
  `harvestUri` varchar(255) character set latin1 collate latin1_bin
default NULL,
  `rating` int(11) NOT NULL default '0',
  `popularity` int(11) NOT NULL default '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`,`source`,`harvestUuid`),
  KEY `owner` (`owner`),
  KEY `groupOwner` (`groupOwner`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I'm not sure of the ramifications either way since I don't know the
internals yet.

Thanks,
Alex

Pickering, Jason wrote:
  

Hi Alex,
You can search the nabble archives for an earlier thread on this. My solution was to change the endcoding of the database to LATIN1. The underlying problem is that the key length of the metadata table ends up exceeding the maximum key length if the database encoding is set to UTF8. The underlying problem however is that the fields in the database are instantiated with too many characters. If you need to use UTF8 encoding, then you may be able to modify the schema manually. I did not try this however.

I am including my response on an earlier thread below, and would suggest that the schema of the metadata table to be changed (developers comments here???).

You should be able to ignore the cyclic reference errors.

Regards,
Jason Pickering

----
Glad to see that LATIN1 worked. However, if you need to use UTF8
encoding, then I suspect that the database needs to be modified.

I believe this is causing the problem.
CREATE TABLE `Metadata` (
`id` int(11) NOT NULL default '0',
`uuid` varchar(250) collate latin1_bin NOT NULL,
`schemaId` varchar(32) collate latin1_bin NOT NULL,
`isTemplate` char(1) collate latin1_bin NOT NULL default 'n',
`isHarvested` char(1) collate latin1_bin NOT NULL default 'n',
`createDate` varchar(24) collate latin1_bin NOT NULL,
`changeDate` varchar(24) collate latin1_bin NOT NULL,
`data` text collate latin1_bin NOT NULL,
`source` varchar(250) collate latin1_bin NOT NULL,
`title` varchar(255) collate latin1_bin default NULL,
`root` varchar(255) collate latin1_bin default NULL,
`harvestUuid` varchar(250) collate latin1_bin default NULL,
`owner` int(11) NOT NULL,
`groupOwner` int(11) default NULL,
`harvestUri` varchar(255) collate latin1_bin default NULL,
`rating` int(11) NOT NULL default '0',
`popularity` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`,`source`,`harvestUuid`),
KEY `owner` (`owner`),
KEY `groupOwner` (`groupOwner`),
KEY `MetadataNDX1` (`uuid`),
KEY `MetadataNDX2` (`source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

This is my metadata table as dumped from MySQL. The unique key length is
too long . However, the composite fields, namely uuid and harvestUuid
are listed at varchar (255). This field length is too long, and I assume
that this data could be stored in a much shorter field length. I have
not delved into the code either at this point, but see no reason why a
UUID needs to be stored in a varchar(255) column. This would have the
effect of shortening the key length certainly improving performance. If
you need multibyte encodings such as UTF8, shorten the field length to
32 and see what happens.

Maybe the developers can comment here?

-----Original Message-----
From: Alex Mandel [mailto:tech_dev@anonymised.com]
Sent: Thu 2009-04-16 00:16
To: geonetwork-users@lists.sourceforge.net
Subject: [GeoNetwork-users] Migration to Mysql failure
Ubuntu linux 8.10 32bit using Sun java runtime.
Using GAST I tried to setup a mysql database instead of the built-in.
Configuration went fine

When I went to Database Setup I get:
Cyclic reference found:
[list of modules]
Running it a second time the list got smaller but still lists
[Metadata, MetadataCateg, OperationAllowed, MetadataRating]
-Install notes, says this doesn't matter

Then I click ok and get
"Specified key was too long; max key length is 1000 bytes"

Looking at the cli output I see it happens right after
[gast] - Creating database schema

Under this config, Geonetwork will start, but various features are
missing, the overview map, login doesn't work, etc.

Any idea what went wrong in setting up the Mysql database, did I miss a
permission or something?

Thanks,
Alex

------------------------------------------------------------------------------
Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save
$200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco.
300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

------------------------------------------------------------------------------
Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save
$200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco.
300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork