Upgrade: function install_checkdb, what good does it do

[kmf]

This step in the upgradeprocess: function install_checkdb is taking forever on my forum with 3 million posts. What is it good for?
It checks each and every field in a table and count through them. This effectively prolongs the needed update-time with a factor 10. Can't you find another way to implement this?

[bfarber]

It pulls all your table schematics and checks them against the ones in the install .php flatfile definition. The size of your posts table shouldn't affect how long this particular functionality takes to run (it's effectively the same as going to your SQL Toolbox).

[kmf]

It pulls all your table schematics and checks them against the ones in the install .php flatfile definition. The size of your posts table shouldn't affect how long this particular functionality takes to run (it's effectively the same as going to your SQL Toolbox).

Unfortunately it does. I ran a profiler on the DB and it was doing a select count(<field>) for every freaking field. Because of the size of my site and DB, i'm using innoDB, a select count will takes 200 seconds to complete. For the posts-table taht would be at least 15*200 seconds. (innodb doesn't have rowcount-statistics)

But since it's only to check the schematics, a select count should not be neccesary, just checking the fields should be enough.

Also, to smooth things up, shouldn't the most important steps be done first? Like updating rebuilding the cache. When I got timed out during that table-check, I had to comment out it, but then something went wrong and the settings did not got recached or something, and thus not allowing me to enter the admin panel with the usual username/password. Instead it forces me to first use a email/password combo to get in, rebuild the settings AND then it's possible to login normally..

The same with skin rebduilding.

I'd think that such critical steps should be done way before checking the table schematics.

Also... even chosing for manual SQL insertions, during the upgrade from one version to another, there are still SQL-queries being run which could take awhile. I also had to comment these out and insert them manually....

[Digi]

They count should be removed, agreed.

As for "what's important", the database information is the most important process here and sometimes requires changes be made before all the other stuff, like caches, are updated.

[bfarber]

I would report that to the bug tracker in that case - a select count is not necessary.

However, this would LARGELY be avoided if you used myisam. I'm not sure your reasons for using innodb (row-level locking) but in any event InnoDB (in practice) is MUCH MUCH slower than MyISAM, and removes your ability to use fulltext searching.

DB integrity is of the utmost importance - I'm not sure why you would say it isn't. :unsure:

Also, any other "large" queries that are being run, please report those to the bug tracker as well. Some queries must be run through the script (as we need to loop on the data and perform certain operations that require php-script execution to determine how to update the data) but none of the ones in the upgrader that always run should be too big. In other words, setting to manual SQL should allow you to run the upgrade script without editing anything - if it doesn't, I'd like to hear why so we can fix it. :)

[kmf]

I would report that to the bug tracker in that case - a select count is not necessary.

However, this would LARGELY be avoided if you used myisam. I'm not sure your reasons for using innodb (row-level locking) but in any event InnoDB (in practice) is MUCH MUCH slower than MyISAM, and removes your ability to use fulltext searching.

row-level locking, data integrity, and all the plusses that innodb has over myisam. And search is no good reason too, because mysql is not meant for searches anyway, so I'm using a indexer.
The other reason would be the counts, but the plusses are far more important.

DB integrity is of the utmost importance - I'm not sure why you would say it isn't. :unsure:

I'd say it doesn't supercedes on site access. The check is merely "just to be sure" and could be done at the end too, when everything is setupped and login is possible. Especially when there is a chance that it could timeout and kill the upgrade process.

Also, any other "large" queries that are being run, please report those to the bug tracker as well. Some queries must be run through the script (as we need to loop on the data and perform certain operations that require php-script execution to determine how to update the data) but none of the ones in the upgrader that always run should be too big. In other words, setting to manual SQL should allow you to run the upgrade script without editing anything - if it doesn't, I'd like to hear why so we can fix it. :)

I don't really remember what queries they were, I think they would be the queries which afects the membertable and also counts. Will have to do a compare to see.

[Digi]

I'd say it doesn't supercedes on site access. The check is merely "just to be sure" and could be done at the end too, when everything is setupped and login is possible. Especially when there is a chance that it could timeout and kill the upgrade process.

The check is to make sure you are up to date BEFORE the update process is started. If it wasn't done first, IPB could possibly cause all sorts of errors from missing columns and such. So no, in this case, it needs to be done first.

[kmf]

The check is to make sure you are up to date BEFORE the update process is started. If it wasn't done first, IPB could possibly cause all sorts of errors from missing columns and such. So no, in this case, it needs to be done first.

But when the check fails due to timeout then we're even further from the final goal. Anyway, this could just be avoided by using something like mysql-list-fields as concluded so I won't venture on it.

[bfarber]

The check SHOULDN'T fail from a timeout - that in itself is the problem that needs to be addressed.

and all the plusses that innodb has over myisam.

I've yet to see any real plusses for innodb over myisam.

data integrity

Innodb has no repair functionality, so if a table corrupts - it's pretty much gone (compared to REPAIR TABLE blah using MyISAM). In all honesty, I've seen nothing but problems from users using innodb, at least on the important tables.

But hey, it's an available option, and your call. :)

[Digi]

Anyway, this could just be avoided by using something like mysql-list-fields as concluded so I won't venture on it.

Actually, that is database specific. IPS supports multiple databases. I'm not saying there isn't room for improvement here, just that "putting out there" ideas that do not work for the entire IPB community doesn't get us closer to those improvements.

[bfarber]

We could, however, make a list-fields function (if there isn't one now) in the driver, and then I can just let wizzy worry about how he will handle it :whistle:

[kmf]

Actually, that is database specific. IPS supports multiple databases. I'm not saying there isn't room for improvement here, just that "putting out there" ideas that do not work for the entire IPB community doesn't get us closer to those improvements.

We could, however, make a list-fields function (if there isn't one now) in the driver, and then I can just let wizzy worry about how he will handle it :whistle:

Just what I would say.
However, if the goal is to just get a list of the columnnames, you could also suffice with selecting one row of a table and loop through arraykeynames.
I'm sure you could think something up.

As for the extra queries, I've just linked them all together in the right order into two files (one for the posts table, the other for the rest) and ran it on two pages and it worked out fine. I've found some redundant stuff though... Of course, I did not documented it... maybe i'll redo the steps on a testmachine again.

[Mat Barrie]

Just what I would say.

However, if the goal is to just get a list of the columnnames, you could also suffice with selecting one row of a table and loop through arraykeynames.

I'm sure you could think something up.

As for the extra queries, I've just linked them all together in the right order into two files (one for the posts table, the other for the rest) and ran it on two pages and it worked out fine. I've found some redundant stuff though... Of course, I did not documented it... maybe i'll redo the steps on a testmachine again.

Just be aware that any changes you think of would have to be compatible with MS SQL Server and Oracle for inclusion into the core product - that's generally why there is often bizarre things you think are wrong, when in fact they are the only cross platform way to do it (although I agree that running a query which requires collecting every row in the database and computing a result is rather pointless)

[W1lz0r]

We could, however, make a list-fields function (if there isn't one now) in the driver, and then I can just let wizzy worry about how he will handle it :whistle:

NP here :)

[Jaggi]

your upgrade does need some work, something which was ignored and closed by some anal retentive person... dont' worry weren't none of you lot :P. Is that the upgrade from 1.3 to 2.0 standard ignores mime-types so causes errors on attachments while not telling people why :P.

[bfarber]

I fixed the field_exists routine - it's much more efficient now. Thanks for the suggestion. :)