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

Sat Jun 19 16:57:17 UTC 2010

Hi,

3600 was an arbitrary limit Frank used. It's not an upper bound. He
used it because he thought 0 means "use default", not "infinite".

I don't know how the old dll dan work with the new Migrations, does
the old dll then also contain a MySqlScript class?
If not, why aren't there loading/linking errors with the old dll?

Melanie

Diva Canto wrote:
> 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
>>
>>    
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev
> 
> 