[Opensim-dev] Opensim 0.7 tidying up the MySQL DB connection string

Fri Jun 18 22:16:23 UTC 2010

The long run times can't be avoided. MySQL alter table is
excruciatingly slow with the huge BLOBs opensim uses.

We have to live with it.

I think there may be middle road between rollback and error. I'll
look at it.

Melanie

Alex Ra wrote:
> DC> Since you were the one who wrote the newly introduced
> DC> MySqlMigrations class, which uses the MySqlScript class, can you
> DC> please explain how we can get around the issue that Frank is
> DC> talking about?
> 
> Hello Diva,
> 
> I haven't looked how the timeouts are implemented in the driver,
> the code I added/modified doesn't do anything about them.
> 
> MySqlScript class is ONLY needed if a proc needs to be defined.
> Otherwise, the common Migration class can be used for mySQL just as
> for all other DBs.
> 
> I'm not sure the regular MySqlCommand would work any
> better with huge timeouts than MySqlScript.  If the timeout can be
> extended beyond the "default default" of 30 sec but then hits a
> limit, it sounds to me more like some server-side limitation
> then something in MySqlScript.
> 
> I can look at the driver sources, but not today.
> 
> Re rolling back to the old driver:  the code changes that accompanied
> the driver update can be found in r/12794, r/12795.  The converter
> function in DBGuids.cs is supposed to convert between UUIDs and the
> DB-specific representation more or less regardless of what the driver
> returns (string or Guid). So it should be possible to rollback the
> driver (or use "OldGuids" var) without actually touching the code.
> MySqlMigration would have to be removed, though.
> 
> I wonder what could make the migration run so slow, may be it would
> make sense to look into the migration scripts and try to optimize them
> somehow (add/remove some indexes or whatever).  Most of the migration stuff is
> just about adding/changing columns and for all I know a server should be
> able to process quite a lot of data in 1 hour (like hundreds of
> thousands rows).
> 
> Can Frank provide some details on what parts of the migration are
> especially slow?
> 
> When people start migrating their DBs to 0.7, it would be nice to make
> sure the scripts are reasonably efficient.
> 
> 
> DC> Since you were the one who wrote the newly introduced
> DC> MySqlMigrations class, which uses the MySqlScript class, can you
> DC> please explain how we can get around the issue that Frank is
> DC> talking about? It looks like the MySqlScript class has some
> DC> hardcoded timeouts. Frank says that we might be able to specify a
> DC> timeout between 0 and 1 hour, but not more. Some DB migrations,
> DC> especially assets, might take more than 1 hour.
> 
> DC> I'm really considering rolling back to using the old driver
> DC> because this one is causing too much grief and confusion at this
> DC> critical time when we want things to be stable.
> DC>  
> DC> Any light on this new driver, especially its limitations/bugs,
> DC> and how we can overcome them, would be very much appreciated so
> DC> that we can try to continue to use it in dev code.
> 
> DC> Thanks.
> DC> Crista
> 
> 
> DC> On 6/18/2010 10:57 AM, Frank Nichols wrote: 
> DC> The use of the MysqlScript class is at the heart of most of the
> DC> problems I have seen with the migration of mysql databases from
> DC> 0.6.9 to 0.7.0. The problem seems to be that the default command
> DC> timeout value for processing a sql statement with this class is 30
> DC> seconds, which is far to short to alter a table inserting a column
> DC> when there are hundreds of thousands of rows in that table.  
> 
> DC> In researching this it appears that the devart version of the new
> DC> MySql.data.dll has a version of the MysqlScript which allows you
> DC> to set the command timeout programmatically, however the mysql
> DC> version of this library does not have this property available. As
> DC> far as I could see there is currently no way to set the command
> DC> timeout. So, it would appear that automatic migrations are doomed
> DC> to fail in any case where they take more than 30 seconds.
> 
> DC> Frank
> 
> DC> On Fri, Jun 18, 2010 at 7:13 AM, Michael Cerquoni <nebadon2025 at gmail.com> wrote:
> DC> I have to say so far this new MySQL connector has been absolutely
> DC> nothing but trouble for Frank Nichols and myself testing Migration
> DC> from 0.6.9 to 0.7,  All throughout the testing we have had nothing
> DC> but failure after failure from this new MySQL connector that has
> DC> been introduced.  It has been nearly impossible to move forward on
> DC> to 0.7 because of this,  I have no doubt that this will be a major
> DC> problem for everyone to migrate over if you have even what seems
> DC> like the most minimal amounts of data.   At this time i really
> DC> have to give this new connector a -1.   Can someone please explain
> DC> to me why the Original MySQL connector that seemed to work
> DC> perfectly for years has now been replaced with one that constantly
> DC> times out during migrations and gives nothing but trouble with
> DC> DNS, requires us to use additional connection string settings, and
> DC> basiclly makes moving on to 0.7 impossible for myself and others??
> DC> I must be missing something here! 
> 
> 
> DC> On Fri, Jun 18, 2010 at 6:06 AM, AlexRa <info at adeptsql.com> wrote:
> 
> AA>> But I note that I has "pooling=false;" in my own string though its
> AA>> not been in the example ini files for a while.  Anyone know what this
> AA>> does and if its wise to keep it in the string if I had a working
> AA>> 0.6.9 system with it there before?
> 
> DC> Hello Ai,
> 
> DC> Can't tell you why the "pooling=false;" was there in the 1st place, but
> DC> I've noticed that it did cause connection failures on a Windows box during
> DC> performance testing (thousands mySQL requests in a loop, each with
> DC> connect/disconnect). The error didn't happen without the "pooling=false;"
> DC> option. I don't know if that affects mySQL on Linux as well.
> 
> DC> Best regards,
> 
> DC> Alex
> 
> 
> DC> _______________________________________________
> DC> Opensim-dev mailing list
> DC> Opensim-dev at lists.berlios.de
> DC> https://lists.berlios.de/mailman/listinfo/opensim-dev
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Best regards,
> 
> Alex
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev
> 
> 