Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0004380opensim[REGION] OpenSim Corepublic2009-11-16 16:082015-01-08 16:22
Reporterkcozens 
Assigned Tomelanie 
PrioritynormalSeverityminorReproducibilityalways
Statuspatch feedbackResolutionopen 
PlatformOSOS Version
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0004380: Several MySQL tables have no index keys
DescriptionWhile looking at the database structure using phpMyAdmin I noticed a message on some of the tables that they had no index. There are five tables with no defined index. They are avatarattachments, landaccesslist, regionban, terrain, and migrations.

The lack of an index on migrations is unlikely to be an issue but the first four should have an index. I have attached two migration files which add the missing indexes. The files belong in OpenSim/Data/MySQL/Resources
Additional InformationThe attached files include alter commands to add unique keys to certain columns of the tables mentioned above. I based this on looking at the MS SQL table creation code. I'm still getting familiar with the overall database structure so some of the unique keys may or may not be needed.
TagsNo tags attached.
Git Revision or version number99977902119d0bb981f507a5a6a96c1148766e77
Run ModeStandalone (1 Region)
Physics EngineBasicPhysics
Script Engine
EnvironmentMono / Linux32
Mono Version2.4.2
Viewer
Attached Files? file icon 009_UserStore.sql [^] (190 bytes) 2009-11-16 16:08
? file icon 032_RegionStore.sql [^] (303 bytes) 2009-11-16 16:09
patch file icon 0001-Patch-to-add-missing-indexes-to-MySQL-database-table.patch [^] (1,295 bytes) 2009-11-17 11:40 [Show Content]

- Relationships
related to 0007360confirmed 14 tables without primary key PGSQL 

-  Notes
(0014180)
melanie (administrator)
2009-11-16 17:36

Most of those keys would BADLY break things.

A primary key is unique by design, but the avatarattachments table has multiple rows for the same avatar.

Also, a region can have more than one entry in the ban list, etc.

Of all the proposed keyz, only one would not break things.

hard -1
(0014181)
melanie (administrator)
2009-11-16 17:39

As an additional remark, we DO need keys on those tables. But not UNIQUE or PRIMARY ones.
(0014183)
kcozens (administrator)
2009-11-16 19:52

I don't use MS SQL and misinterpreted the meaning of uniqueidentifier and PRIMARY in the database schema. I was going to attach a patch file which just adds an INDEX for the four tables but the patch created by git puts the resource files in the current top-level directory instead of in the proper sub-directory.
(0019563)
makopoppo (manager)
2011-08-14 19:39

It needs to revise the patch concurrent with current codebase and vote again.

- Issue History
Date Modified Username Field Change
2009-11-16 16:08 kcozens New Issue
2009-11-16 16:08 kcozens File Added: 009_UserStore.sql
2009-11-16 16:08 kcozens Git Revision => 99977902119d0bb981f507a5a6a96c1148766e77
2009-11-16 16:08 kcozens SVN Revision => 0
2009-11-16 16:08 kcozens Run Mode => Standalone (1 Region)
2009-11-16 16:08 kcozens Physics Engine => BasicPhysics
2009-11-16 16:08 kcozens Environment => Mono / Linux32
2009-11-16 16:08 kcozens Mono Version => 2.4.2
2009-11-16 16:09 kcozens File Added: 032_RegionStore.sql
2009-11-16 17:36 melanie Note Added: 0014180
2009-11-16 17:37 melanie Status new => resolved
2009-11-16 17:37 melanie Resolution open => won't fix
2009-11-16 17:37 melanie Assigned To => melanie
2009-11-16 17:39 melanie Note Added: 0014181
2009-11-16 18:09 melanie Status resolved => acknowledged
2009-11-16 18:09 melanie Resolution won't fix => suspended
2009-11-16 18:10 melanie Status acknowledged => patch feedback
2009-11-16 18:10 melanie Resolution suspended => open
2009-11-16 19:52 kcozens Note Added: 0014183
2009-11-17 11:40 kcozens File Added: 0001-Patch-to-add-missing-indexes-to-MySQL-database-table.patch
2011-08-14 19:39 makopoppo Note Added: 0019563
2014-10-29 20:08 kcozens Relationship added related to 0007360


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker