MySQL create table for regions fails on new data base setup?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

MySQL create table for regions fails on new data base setup?

aiaustin
hello.. I have a working OpenSim database on MySQL 5.6.21 Community
Edition and have done a mysqldump on that.  I set up a new server
with MySQL 5.7.10 Community Edition and created the opensim database
as usual... I then wanted to load in the dumped mysql from the other server.

I got an error ERROR 1031 (HY000) at line 3016: Table storage engine
for 'regions' doesn't have this option

I can't edit the very large SQL file to check what that line has on
it, but after looking round on the web a bit and using a suggestion
there I did get the create table statement for "regions" from the
previous working system and try to load that on the new MYSQL setup
and that give the same error... as noted in the trace below. So I
assume its the table create for the "regions" table that is the
problem.  But what is wrong? I can dump the SQL from the original
data base again if some parameter is needed there.

Can anyone cast a light on this or suggest a fix?

C:\Users\****> mysql --verbose -u ****** -p opensim < create-regions.sql
Enter password: *********
--------------
CREATE TABLE `regions` (
   `uuid` varchar(36) NOT NULL,
   `regionHandle` bigint(20) unsigned NOT NULL,
   `regionName` varchar(128) DEFAULT NULL,
   `regionRecvKey` varchar(128) DEFAULT NULL,
   `regionSendKey` varchar(128) DEFAULT NULL,
   `regionSecret` varchar(128) DEFAULT NULL,
   `regionDataURI` varchar(255) DEFAULT NULL,
   `serverIP` varchar(64) DEFAULT NULL,
   `serverPort` int(10) unsigned DEFAULT NULL,
   `serverURI` varchar(255) DEFAULT NULL,
   `locX` int(10) unsigned DEFAULT NULL,
   `locY` int(10) unsigned DEFAULT NULL,
   `locZ` int(10) unsigned DEFAULT NULL,
   `eastOverrideHandle` bigint(20) unsigned DEFAULT NULL,
   `westOverrideHandle` bigint(20) unsigned DEFAULT NULL,
   `southOverrideHandle` bigint(20) unsigned DEFAULT NULL,
   `northOverrideHandle` bigint(20) unsigned DEFAULT NULL,
   `regionAssetURI` varchar(255) DEFAULT NULL,
   `regionAssetRecvKey` varchar(128) DEFAULT NULL,
   `regionAssetSendKey` varchar(128) DEFAULT NULL,
   `regionUserURI` varchar(255) DEFAULT NULL,
   `regionUserRecvKey` varchar(128) DEFAULT NULL,
   `regionUserSendKey` varchar(128) DEFAULT NULL,
   `regionMapTexture` varchar(36) DEFAULT NULL,
   `serverHttpPort` int(10) DEFAULT NULL,
   `serverRemotingPort` int(10) DEFAULT NULL,
   `owner_uuid` varchar(36) NOT NULL DEFAULT
'00000000-0000-0000-0000-000000000000',
   `originUUID` varchar(36) DEFAULT NULL,
   `access` int(10) unsigned DEFAULT '1',
   `ScopeID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
   `sizeX` int(11) NOT NULL DEFAULT '0',
   `sizeY` int(11) NOT NULL DEFAULT '0',
   `flags` int(11) NOT NULL DEFAULT '0',
   `last_seen` int(11) NOT NULL DEFAULT '0',
   `PrincipalID` char(36) NOT NULL DEFAULT
'00000000-0000-0000-0000-000000000000',
   `Token` varchar(255) NOT NULL,
   `parcelMapTexture` varchar(36) DEFAULT NULL,
   PRIMARY KEY (`uuid`),
   KEY `regionName` (`regionName`),
   KEY `regionHandle` (`regionHandle`),
   KEY `overrideHandles`
(`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`),
   KEY `ScopeID` (`ScopeID`),
   KEY `flags` (`flags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'
--------------

ERROR 1031 (HY000) at line 1: Table storage engine for 'regions'
doesn't have this option

_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL create table for regions fails on new data base setup?

aiaustin
As noted before, I am getting an error on loading a MySQL 5.6 dump of
my OpenSim content and trying to load it on a new MySQL 5.7
server.  I have tracked this down to the SQL load complaining about a
ROW_FORMAT=FIXED on the "regions" table That seems to be the only
table row that has that option.

Is this fixed setting needed, and is it correct for recent MySQL data
base versions?  Can it safely be changed to "Don't Use" like all the
other opensim data base tables?

_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL create table for regions fails on new data base setup?

Tom Frost
Hi,

On Fri, Jan 08, 2016 at 12:05:46PM +0000, Ai Austin wrote:
> As noted before, I am getting an error on loading a MySQL 5.6 dump
> of my OpenSim content and trying to load it on a new MySQL 5.7
> server.  I have tracked this down to the SQL load complaining about
> a ROW_FORMAT=FIXED on the "regions" table That seems to be the only
> table row that has that option.
>
> Is this fixed setting needed, and is it correct for recent MySQL
> data base versions?  Can it safely be changed to "Don't Use" like
> all the other opensim data base tables?

https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-specification.html does not specify FIXED as a valid valie for ROW_FORMAT in 5.6, so I assume it is a deprecated setting that was ignored in 5.6 and completely removed in 5.7.

In any case, the ROW_FORMAT option determines the way mysql stored rows in binary format in the underlying database files. The worst that can happen is that performance is impacted if this setting is 'incorrect'. Given that this is in the region table, which normally doesn't have many rows anyway, it's rather a moot point what you set it to.

Best,

Tom
_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL create table for regions fails on new data base setup?

GarminKawaguichi
In reply to this post by aiaustin
May be that could help:

http://bugs.mysql.com/bug.php?id=23404

GCI

Le 08/01/2016 12:11, Ai Austin a écrit :
hello.. I have a working OpenSim database on MySQL 5.6.21 Community Edition and have done a mysqldump on that.  I set up a new server with MySQL 5.7.10 Community Edition and created the opensim database as usual... I then wanted to load in the dumped mysql from the other server.

I got an error ERROR 1031 (HY000) at line 3016: Table storage engine for 'regions' doesn't have this option

I can't edit the very large SQL file to check what that line has on it, but after looking round on the web a bit and using a suggestion there I did get the create table statement for "regions" from the previous working system and try to load that on the new MYSQL setup and that give the same error... as noted in the trace below. So I assume its the table create for the "regions" table that is the problem.  But what is wrong? I can dump the SQL from the original data base again if some parameter is needed there.

Can anyone cast a light on this or suggest a fix?

C:\Users\****> mysql --verbose -u ****** -p opensim < create-regions.sql
Enter password: *********
--------------
CREATE TABLE `regions` (
  `uuid` varchar(36) NOT NULL,
  `regionHandle` bigint(20) unsigned NOT NULL,
  `regionName` varchar(128) DEFAULT NULL,
  `regionRecvKey` varchar(128) DEFAULT NULL,
  `regionSendKey` varchar(128) DEFAULT NULL,
  `regionSecret` varchar(128) DEFAULT NULL,
  `regionDataURI` varchar(255) DEFAULT NULL,
  `serverIP` varchar(64) DEFAULT NULL,
  `serverPort` int(10) unsigned DEFAULT NULL,
  `serverURI` varchar(255) DEFAULT NULL,
  `locX` int(10) unsigned DEFAULT NULL,
  `locY` int(10) unsigned DEFAULT NULL,
  `locZ` int(10) unsigned DEFAULT NULL,
  `eastOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `westOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `southOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `northOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `regionAssetURI` varchar(255) DEFAULT NULL,
  `regionAssetRecvKey` varchar(128) DEFAULT NULL,
  `regionAssetSendKey` varchar(128) DEFAULT NULL,
  `regionUserURI` varchar(255) DEFAULT NULL,
  `regionUserRecvKey` varchar(128) DEFAULT NULL,
  `regionUserSendKey` varchar(128) DEFAULT NULL,
  `regionMapTexture` varchar(36) DEFAULT NULL,
  `serverHttpPort` int(10) DEFAULT NULL,
  `serverRemotingPort` int(10) DEFAULT NULL,
  `owner_uuid` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `originUUID` varchar(36) DEFAULT NULL,
  `access` int(10) unsigned DEFAULT '1',
  `ScopeID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `sizeX` int(11) NOT NULL DEFAULT '0',
  `sizeY` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `last_seen` int(11) NOT NULL DEFAULT '0',
  `PrincipalID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `Token` varchar(255) NOT NULL,
  `parcelMapTexture` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `regionName` (`regionName`),
  KEY `regionHandle` (`regionHandle`),
  KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`),
  KEY `ScopeID` (`ScopeID`),
  KEY `flags` (`flags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'
--------------

ERROR 1031 (HY000) at line 1: Table storage engine for 'regions' doesn't have this option

_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users


_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL create table for regions fails on new data base setup?

Shy Robbiani
I stumbled over this a wile ago and submitted a patch. I'm not sure it ever got implemented.

According to the MySQL 5.6 Reference manual in relation to InnoDB tables: "ROW_FORMAT=FIXED is not supported. If ROW_FORMAT=FIXED is specified while innodb_strict_mode is disabled, InnoDB issues a warning and assumes ROW_FORMAT=COMPACT. If ROW_FORMAT=FIXED is specified while innodb_strict_mode is enabled, InnoDB returns an error".

And a few lines later: "For MyISAM tables, the option value can be FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED".

Maybe the default in 5.7 changed to strict or the behaviour has been changed. I don't know.

On Fri, Jan 8, 2016 at 4:02 PM, GarminKawaguichi <[hidden email]> wrote:
May be that could help:

http://bugs.mysql.com/bug.php?id=23404

GCI


Le 08/01/2016 12:11, Ai Austin a écrit :
hello.. I have a working OpenSim database on MySQL 5.6.21 Community Edition and have done a mysqldump on that.  I set up a new server with MySQL 5.7.10 Community Edition and created the opensim database as usual... I then wanted to load in the dumped mysql from the other server.

I got an error ERROR 1031 (HY000) at line 3016: Table storage engine for 'regions' doesn't have this option

I can't edit the very large SQL file to check what that line has on it, but after looking round on the web a bit and using a suggestion there I did get the create table statement for "regions" from the previous working system and try to load that on the new MYSQL setup and that give the same error... as noted in the trace below. So I assume its the table create for the "regions" table that is the problem.  But what is wrong? I can dump the SQL from the original data base again if some parameter is needed there.

Can anyone cast a light on this or suggest a fix?

C:\Users\****> mysql --verbose -u ****** -p opensim < create-regions.sql
Enter password: *********
--------------
CREATE TABLE `regions` (
  `uuid` varchar(36) NOT NULL,
  `regionHandle` bigint(20) unsigned NOT NULL,
  `regionName` varchar(128) DEFAULT NULL,
  `regionRecvKey` varchar(128) DEFAULT NULL,
  `regionSendKey` varchar(128) DEFAULT NULL,
  `regionSecret` varchar(128) DEFAULT NULL,
  `regionDataURI` varchar(255) DEFAULT NULL,
  `serverIP` varchar(64) DEFAULT NULL,
  `serverPort` int(10) unsigned DEFAULT NULL,
  `serverURI` varchar(255) DEFAULT NULL,
  `locX` int(10) unsigned DEFAULT NULL,
  `locY` int(10) unsigned DEFAULT NULL,
  `locZ` int(10) unsigned DEFAULT NULL,
  `eastOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `westOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `southOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `northOverrideHandle` bigint(20) unsigned DEFAULT NULL,
  `regionAssetURI` varchar(255) DEFAULT NULL,
  `regionAssetRecvKey` varchar(128) DEFAULT NULL,
  `regionAssetSendKey` varchar(128) DEFAULT NULL,
  `regionUserURI` varchar(255) DEFAULT NULL,
  `regionUserRecvKey` varchar(128) DEFAULT NULL,
  `regionUserSendKey` varchar(128) DEFAULT NULL,
  `regionMapTexture` varchar(36) DEFAULT NULL,
  `serverHttpPort` int(10) DEFAULT NULL,
  `serverRemotingPort` int(10) DEFAULT NULL,
  `owner_uuid` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `originUUID` varchar(36) DEFAULT NULL,
  `access` int(10) unsigned DEFAULT '1',
  `ScopeID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `sizeX` int(11) NOT NULL DEFAULT '0',
  `sizeY` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `last_seen` int(11) NOT NULL DEFAULT '0',
  `PrincipalID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `Token` varchar(255) NOT NULL,
  `parcelMapTexture` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `regionName` (`regionName`),
  KEY `regionHandle` (`regionHandle`),
  KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`),
  KEY `ScopeID` (`ScopeID`),
  KEY `flags` (`flags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'
--------------

ERROR 1031 (HY000) at line 1: Table storage engine for 'regions' doesn't have this option

_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users


_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users



_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: MySQL create table for regions fails on new data base setup?

aiaustin
In reply to this post by aiaustin
Thanks for those notes... as noted on the Mantis at
http://opensimulator.org/mantis/view.php?id=7799 I think the fix is
simply to drp the ROW_FORMAT=FIXED from the current scheme spec for "regions".

I have tested Openvue and AiLand grids with the regions table options
with ROW_FORMAT=FIXED changed to unspecified.. i.e. "Don't Use" in
the MySQL GUI. That works fine as far as I can tell.

I then tried a MySQL dump from a MySQL 5.6 setup and was able to
successfully mount that on a fresh sever on version 5.7.10 and I am
now running Openvue grid on that.

So I think the fix is very simple... the regions table schema needs
to have the ROW_FORMAT=FIXED removed..

I am not sure how to set up migrations like this, so can a dev
experienced in migrations do this for MySQL and consider if its
needed for all other supported data bases?

_______________________________________________
Opensim-users mailing list
[hidden email]
http://opensimulator.org/cgi-bin/mailman/listinfo/opensim-users
Loading...