MantisBT - opensim
View Issue Details
0004380opensim[REGION] OpenSim Corepublic2009-11-16 16:082015-01-08 16:22
kcozens 
melanie 
normalminoralways
patch feedbackopen 
master (dev code) 
 
99977902119d0bb981f507a5a6a96c1148766e77
Standalone (1 Region)
BasicPhysics
Mono / Linux32
2.4.2
0004380: Several MySQL tables have no index keys
While 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
The 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.
No tags attached.
related to 0007360confirmed  14 tables without primary key PGSQL 
? 009_UserStore.sql (190) 2009-11-16 16:08
http://opensimulator.org/mantis/file_download.php?file_id=2166&type=bug
? 032_RegionStore.sql (303) 2009-11-16 16:09
http://opensimulator.org/mantis/file_download.php?file_id=2167&type=bug
patch 0001-Patch-to-add-missing-indexes-to-MySQL-database-table.patch (1,295) 2009-11-17 11:40
http://opensimulator.org/mantis/file_download.php?file_id=2168&type=bug
Issue History
2009-11-16 16:08kcozensNew Issue
2009-11-16 16:08kcozensFile Added: 009_UserStore.sql
2009-11-16 16:08kcozensGit Revision => 99977902119d0bb981f507a5a6a96c1148766e77
2009-11-16 16:08kcozensSVN Revision => 0
2009-11-16 16:08kcozensRun Mode => Standalone (1 Region)
2009-11-16 16:08kcozensPhysics Engine => BasicPhysics
2009-11-16 16:08kcozensEnvironment => Mono / Linux32
2009-11-16 16:08kcozensMono Version => 2.4.2
2009-11-16 16:09kcozensFile Added: 032_RegionStore.sql
2009-11-16 17:36melanieNote Added: 0014180
2009-11-16 17:37melanieStatusnew => resolved
2009-11-16 17:37melanieResolutionopen => won't fix
2009-11-16 17:37melanieAssigned To => melanie
2009-11-16 17:39melanieNote Added: 0014181
2009-11-16 18:09melanieStatusresolved => acknowledged
2009-11-16 18:09melanieResolutionwon't fix => suspended
2009-11-16 18:10melanieStatusacknowledged => patch feedback
2009-11-16 18:10melanieResolutionsuspended => open
2009-11-16 19:52kcozensNote Added: 0014183
2009-11-17 11:40kcozensFile Added: 0001-Patch-to-add-missing-indexes-to-MySQL-database-table.patch
2011-08-14 19:39makopoppoNote Added: 0019563
2014-10-29 20:08kcozensRelationship addedrelated to 0007360

Notes
(0014180)
melanie   
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   
2009-11-16 17:39   
As an additional remark, we DO need keys on those tables. But not UNIQUE or PRIMARY ones.
(0014183)
kcozens   
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   
2011-08-14 19:39   
It needs to revise the patch concurrent with current codebase and vote again.