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 <[email protected]> > 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 <[email protected]> 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> [email protected] > DC> https://lists.berlios.de/mailman/listinfo/opensim-dev > > > > > > > > > > > Best regards, > > Alex > > _______________________________________________ > Opensim-dev mailing list > [email protected] > https://lists.berlios.de/mailman/listinfo/opensim-dev > > _______________________________________________ Opensim-dev mailing list [email protected] https://lists.berlios.de/mailman/listinfo/opensim-dev
