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

Sat Jun 19 11:18:56 UTC 2010

A few points to clarify the situation:

- The rollback I'm talking about is purely on the 0.7-post-fixes branch, 
and for the purposes of the 0.7 release. I'm fine with continuing to 
push on the new driver in the master branch.

- "Rolling back" is as simple as replacing the new MySql.Data.dll with 
the old one. No changes in the migration code seem to be necessary.

Now for the master branch's debugging of the new MySql driver:

- The point above is something to be noted in the puzzle surrounding the 
new MySql driver.

- The connection string variable Default Command Timeout does not work 
here; several people tried it, including myself.

- MySqlScript seems to be the culprit. I think it's being used by the 
new migrations code that you wrote, Alex. I can see here
http://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlScript_members.html
that is has a programatic interface for setting the timeout. So maybe we 
need to do that. I haven't been able to find references for the limit 
3600 secs that Frank mentioned. This needs to be completely clarified. 
Thanks.

On 6/18/2010 3:16 PM, Melanie wrote:
> 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
>>
>>
>>      
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev
>
>    