Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007873opensim[REGION] OpenSim Corepublic2016-04-04 16:082016-04-12 10:50
ReporterVerwijs 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformlinuxOSOpenSUSE TumbleweedOS Version2016.04.01
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0007873: Table "Presence" not found on first run... should be "presence" ..
Description





~/OpenSim/Data/MySQL/Resources/Presence.migrations


BEGIN;

CREATE TABLE IF NOT EXISTS `Presence` (
  `UserID` varchar(255) NOT NULL,
  `RegionID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `SessionID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `SecureSessionID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  `LastSeen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `SessionID` (`SessionID`),
  KEY `UserID` (`UserID`),
  KEY `RegionID` (`RegionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

COMMIT;



`Presence` should be `presence` (small caps) witch mysql creates by default...
opensim crashes with "table not found"



Steps To Reproduce- run opensim with empty db on linux (first-run)
- stop...and start opensim again.

Additional Informationmono 4.2.2
TagsNo tags attached.
Git Revision or version number2cfe848ceb06d42812385fb495f57f3c1a480cc5
Run ModeStandalone (1 Region)
Physics EngineBulletSim
Script Engine
EnvironmentMono / Linux64
Mono VersionOther
Viewer
Attached Filestxt file icon mysql-bug-linux-only.txt [^] (6,327 bytes) 2016-04-10 04:19 [Show Content]

- Relationships
related to 0007856closedmelanie create user throws an error about UserAccounts table not found 

-  Notes
(0030143)
danbanner (manager)
2016-04-04 16:25

looking at http://opensimulator.org/viewgit/?a=commitdiff&p=opensim&h=548b741130717bc82f659cb0a55459638e5394bc [^] seems to indicate this was an intentional change
(0030149)
melanie (administrator)
2016-04-05 05:52

There may actually be some mixing of case in the code as a lower case table name has been reported to cause a crash elsewhere. If going lower case fixes it, then lower it shall be.
(0030150)
aiaustin (developer)
2016-04-05 08:29
edited on: 2016-04-05 08:42

I mentioned before that there seems to be a case difference between different databases (SQLite, MySQL and PGSQL), though Melanie pointed out that somehow MySQL on Windows makes them lower case anyway.

All lower case is mostly used, but a few have crept in that have an initial capital letter.

See this comment and image attached there...
http://opensimulator.org/mantis/view.php?id=7856#c30104 [^]

More case changes in the table names have also being made recently, without consistency across all the database options... e.g. for friends and avatar too...

http://opensimulator.org/viewgit/?a=commit&p=opensim&h=2cfe848ceb06d42812385fb495f57f3c1a480cc5 [^]

(0030151)
Gavin Hird (reporter)
2016-04-09 09:09

Before anyone change case on database tables it MUST be verified it does not have implications for the execution of code. PostgreSQL has Case sensitive column and database table names.

It changes are to be made, tables must be migrated and just not recreated.

NOTE NOTE NOTE:

All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Column names that were created with double-quotes and thereby retained upper-case letters (and/or other syntax violations) have to be double-quoted for the rest of their life. So, yes, PostgreSQL column names are case-sensitive:
(0030153)
aiaustin (developer)
2016-04-09 10:19
edited on: 2016-04-09 10:21

This does sound like its in a bit of a mess... as definitely diffferent database systems that are supported have different case for the table names That may (must?) mean that code that assumes a specifc case will work on some DBs and not others? Is this an issue since PostgreSQL use was added and that system needs to be migrated to a common case for all tables? Or does the code just naturally support any case in any DB that is furrently supported?

(0030154)
Gavin Hird (reporter)
2016-04-09 15:18

The Postgres tables are mostly OK as they initially were established in a consistent manner, but the problem arise when people who don't realize that Postgres tables and column names can be case sensitive if created the wrong way.

This usually happens when someone who is familiar with MySQL make changes or additions to Postgres tables using MySQL syntax, and there are many example of this in the tables now. The result is errors and creation of case sensitive columns (mostly). It can also lead to unnecessary or inefficient casting. In many cases the MySQL syntax will simply fail to produce the desired result.

In the future changes to the Postgres tables should not be made just because they MySQL tables are such and such.

The Postgres table definition need to
a) take advantage of what is the most efficient way of storing a column
b) minimize casting in the queries or in the code
c) take into concideration bugs and limitations in NPGSQL (the version shipping with mono is outdated)
d) take into consideration that the Postgres query planner does not work the same way as the MySQL query planner, meaning a MySQL query could be very expensive compared to one that is optimized for the database

It is equally important that people don't submit commits with changes to the Postgres tables or column with a "Not tested" appended to it.

For code that touch the Postgres tables to even be accepted it must be tested before being committed.

Testing is quite simple. It is a matter of installing Postgres from an installer that exist for most operating system, and make separate ini files that have the Postgres database option enable rather than MySQL. Apart from that operation of OpenSim should be pretty identical as with MySQL.

For this particular Mantis, often the problem on first run is that the account does not have the necessary privileges or ownership of the database operated on. Unless the user account owns the database or table many operations will simply fail as the database or table will be in read only mode for that user. Postgres has a more granulated privilege system than MySQL.

If an admin user makes changes to a table, the operation may change the table ownership away from the less privileged "opensim" account, and this ownership must be restored as part of the operation or the table will be read only for the "opensim" account. This is different from MySQL.
(0030155)
aiaustin (developer)
2016-04-10 01:29
edited on: 2016-04-10 05:59

Thanks for that explanation Gavin... it is most helpful to explain the current situation. If the tables in different data bases that are supported have different case for their table names though won't that mean that the code that manipulates those tables needs to accommdate that and lead to ineffficient code?

It sounds like the few tables for SQLite and MySQL that have mixed case and that have different behaviours in Linux and Windows are the outliers and potentially problematic.

Melanie and Kevin Cozens, should all the changes for initial MySQL table creation name case put into 0.9.0 recently be removed so at least there are no further variances in MYSQL and SQLite table names and variations even amongst different grids on these data bases depending on when they were initially created?

Should an aim for 0.9.0 be to try to fix the case for MySQL and SQLite to be all lower case fo all tables names on all OSes? But it seems from what Gavin says that special handing for Postgres is going to be needed anyway?

(0030156)
Gavin Hird (reporter)
2016-04-10 01:40

> If the tables in different data bases that are supported have different case for their table names though won't that mean that the code that manipulates those tables needs to accommodate that and lead to ineffficient code?

Not necessarily because the communication with the database is handled in separate MySQL, PGSQL and SQLIte code (found in OpenSim/Data in the source tree, so it is isolated there (or should be).

The biggest problem with Postgres is that people seem to think it is MySQL syntactically, which it isn't, but rather very much closer to ANSI SQL.

If you observe the statement above "All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL." – If this was observed in the code, then there would not be a case issue if the tables and columns were created without double quoting as they would be lowercased

For MySQL the situation is "Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix."

My take on the above is that all database tables and column names should be created lowercase to work around differences between operating systems and database systems. Obviously cleaning it up will take a bit effort, but can be done.
(0030157)
Verwijs (reporter)
2016-04-10 04:26

on first run (linux only!) i also get

valueMySql.Data.MySqlClient.MySqlException: Field 'TMStamp' doesn't have a default value
(see attachment)

from "hg_traveling_data" table. on first run there's no "home" position
or "last (login) position" MS-Windows strange enough seems to have no problem with this....

..
(0030158)
melanie (administrator)
2016-04-10 09:50

This bug is not about postgres and it's not about other tables. Each should be a separate report as they will not be fixed all at he same time.
Cleaning up table names is not possible. We would break existing installations and that is unacceptable. There is a long standing set of rules governing our databases:
- Never rename or remove fields
- Add new fields at the end only
- Write all statements so that they can handle the database having extra fields

The same goes for tables, we don't rename them. Many users wouldn't know how to rename their actual tables and if we did it for them it would break their websites, etc.
That can of worms is too large to open.

That said, the code refers to "Presence" throughout, I can't find a place in the mysql code where "presence"is used. That means the current code is correct. I will text it myself, though, to make sure, before I close this bug.
(0030159)
Verwijs (reporter)
2016-04-10 09:55

thank you :)
(0030160)
Gavin Hird (reporter)
2016-04-10 10:40

As far as I can see from the last MySQL table migrations in this commit made by Diva, all table names are lowercase as they should be.

http://opensimulator.org/viewgit/?a=commit&p=opensim&h=134e94a7e91adb7b4eb153c2df9b70096c4edfb4 [^]
(0030161)
Gavin Hird (reporter)
2016-04-10 10:45

These two patches will migrate the Postgres tables to be in sync with the 0.9 dev MySQL tables, and fix the table handler for "presence" for Postgres as the 0.9 dev code had broken a previous fix.

http://opensimulator.org/mantis/view.php?id=7846 [^]

http://opensimulator.org/mantis/view_all_bug_page.php [^]
(0030162)
melanie (administrator)
2016-04-10 11:58

That is what caused the issue, your statement is wrong. There is no "should be" in that sense because they should be what they were before the pivot revision. Diva changed that and that is what causes the problems. SO we changed most of it back and there seems to be one last inconsistency. I will fix that and all will be well. We will NOT put all the grids out there through the pain of changing database or table names.
(0030163)
Gavin Hird (reporter)
2016-04-10 12:12

As from the discussion above my opinion is that both tables and column should be created lowercased to minimize the potential for issues across operating systems and database systems.

Having said that I did not like Diva's commit both because it removed migrations and because I was not convinced it was properly tested.

– Which is why the patch I supplied for the Postgres tables left all the migrations in place for backward compatibility and to move the tables forward without data loss.
(0030164)
melanie (administrator)
2016-04-10 13:05

0.9 is meant to be a pivot release. Migrations are meant to be removed. To upgrade from something earlier than 0.8.2, one must first upgrade to 0.8.2 and then to 0.9.0. This is intentional.
(0030165)
Gavin Hird (reporter)
2016-04-10 13:10

Sounds like the strategy Microsoft has used to upgrade Sharepoint. – At astronomical cost for their users, haha. ;-))
(0030166)
aiaustin (developer)
2016-04-10 14:02
edited on: 2016-04-10 14:32

This all sounds like its going to get messier... what about systems that mix Linux and Windows Robust servers, data base servers and region servers? And what about a SQL dump on one Database/OS to allow for it to be loaded onto a different one as server choices change for a grid?

It is good to have a tidy up as Melanie indicates will occur when 0.9.0 is made the release... on our way one day past alpha code status ;-)

But should that pivot release take the opportunity of also correcting the case everywhere on all supported OSes and data base systems now to prevent future more serious incompatibilties?

(0030167)
Diva (administrator)
2016-04-10 14:17

The migrations cleanup commit didn't touch anything on Postgres. It affected only MySQL and SQLite.

Given that the code uses upper case, I can make those 2 tables upper case again: Presence and UserAccounts. Any issues with that, Melanie?
(0030168)
melanie (administrator)
2016-04-10 18:56

I already changed them but it appears that there is a reference to lower case "presence" somewhere. It seems to break things on a clean mysql install and I haven't tested that part yet.
It was a mistake to just try to change table casing just because it's folded to lower care on Windows. The world is not all Windows and changing the table name casing has causes a huge ruckus. We need to accept that we can't change anything about either tables or columns at all. It's the consensus originally reached and we need to stick to it or vote anew.
All that is needed from this point on is to find that reference to "presence" and change it to "Presence", or better yet eliminate it because all of the MySQL presence driver should use m_realm and m_realm is already "Presence". Nothing outside that module should be concerned with table names and if you changed D2 to match these "new" lower case table names then please change it back. We're stuck with these table names and in fact much older (0.5 or so) opensim versions were all lowercase, then someone started adding uppercased table names. "users" became "UserAccounts", etc. So the CamelCase names are actually more recent although I don't recall a discussion about whether all names should be camel cased being had. This is all a big mess that does bear cleaning up, but the risk of service disruption for grids is one I don't want to take by myself.
If core as a whole backed cleanup and would be willing to support users who get stuck, I would be for it. And, yes, if a cleanup happens, lower case would be the best choice because it poses the least risk of errors.
(0030171)
Diva (administrator)
2016-04-11 08:02

ok, I'll try to look at this today or tomorrow. There seems to be nothing in the MySQL dll. Maybe it's in some config file somewhere.
(0030172)
aiaustin (developer)
2016-04-11 09:14

Just a note that as well as presence, Melanie and Kevin Cozens made some case changes to other MySQL table names... useraccounts, avatar and friends. Any others recently? Do all these need reverting too?
(0030180)
Diva (administrator)
2016-04-12 10:50

FYI I just tried dev code in both windows an linux, sims from scratch, and got no errors.

- Issue History
Date Modified Username Field Change
2016-04-04 16:08 Verwijs New Issue
2016-04-04 16:25 danbanner Note Added: 0030143
2016-04-05 05:52 melanie Note Added: 0030149
2016-04-05 08:29 aiaustin Note Added: 0030150
2016-04-05 08:30 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:30 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:32 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:32 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:32 aiaustin Relationship added related to 0007856
2016-04-05 08:36 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:37 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:42 aiaustin Note Edited: 0030150 View Revisions
2016-04-05 08:42 aiaustin Note Edited: 0030150 View Revisions
2016-04-09 09:09 Gavin Hird Note Added: 0030151
2016-04-09 10:19 aiaustin Note Added: 0030153
2016-04-09 10:21 aiaustin Note Edited: 0030153 View Revisions
2016-04-09 15:18 Gavin Hird Note Added: 0030154
2016-04-10 01:29 aiaustin Note Added: 0030155
2016-04-10 01:40 Gavin Hird Note Added: 0030156
2016-04-10 04:19 Verwijs File Added: mysql-bug-linux-only.txt
2016-04-10 04:26 Verwijs Note Added: 0030157
2016-04-10 05:59 aiaustin Note Edited: 0030155 View Revisions
2016-04-10 09:50 melanie Note Added: 0030158
2016-04-10 09:55 Verwijs Note Added: 0030159
2016-04-10 10:40 Gavin Hird Note Added: 0030160
2016-04-10 10:45 Gavin Hird Note Added: 0030161
2016-04-10 11:58 melanie Note Added: 0030162
2016-04-10 12:12 Gavin Hird Note Added: 0030163
2016-04-10 13:05 melanie Note Added: 0030164
2016-04-10 13:10 Gavin Hird Note Added: 0030165
2016-04-10 14:02 aiaustin Note Added: 0030166
2016-04-10 14:17 Diva Note Added: 0030167
2016-04-10 14:32 aiaustin Note Edited: 0030166 View Revisions
2016-04-10 18:56 melanie Note Added: 0030168
2016-04-11 08:02 Diva Note Added: 0030171
2016-04-11 09:14 aiaustin Note Added: 0030172
2016-04-12 10:50 Diva Note Added: 0030180


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker