max_questions exceeded SQL error on board conversion phpBB2 to phpBB3

tryma · Post by **tryma** » Thu Dec 13, 2018 1:51 pm

General Error:
SQL ERROR [ mysqli ]

User '(redacted)' has exceeded the 'max_questions' resource (current value: 360000) [1226]

Hi,

I am currently attempting an upgrade from phpBB2 to phpBB3.2.4 following my ISP upgrading to PHP 7.2 which rendered our previous phpBB2 installation defunct.

I have setup a new phpBB3 forum and started the conversion which fails after a while because it has exhausted the max queries on the mysql database. The problem however is this is already at 360000, ie 100 per second and the ISP won't change this but rather demand the upgrade scripts to be modified to throttle the updates, ie slow down with wait or sleep.

So my question becomes is this possible to modify in the install/convert/convert.php script?

I've tried batch sizes of 2000, 1000 and 300 and num rows as 30, 20 and 10 but to no avail.

As extra info both my phpBB installations are on the same database in this shared hosting. I don't have extra databases available to setup the new phpBB3 as separate database so I'm guessing this might happen because conversion both reads from the phpBB2 and writes to the phpBB3 instance.

david63 · Post by **david63** » Thu Dec 13, 2018 2:37 pm

You could always do the conversion on your local server and then upload the converted database to your production server

warmweer · Post by **warmweer** » Thu Dec 13, 2018 4:56 pm

david63 wrote: ↑Thu Dec 13, 2018 2:37 pm You could always do the conversion on your local server and then upload the converted database to your production server

Should be standard procedure, no matter how experienced the board owner is.

tryma · Post by **tryma** » Thu Dec 13, 2018 7:24 pm

Well, I now managed to get past these issues by getting another database created on my account plan with this shared host - turned out I had room for one more.
After doing so the conversion from phpBB2 to phpBB3 on separate databases ran fine within the restrictions on max_questions in mySQL.

As for software requiring that you do a complete database backup to your local environment in order to perform what appears to be an automated migration tool, well, that should _not_ be standard procedure.

I read all the disclaimers and all the guide on how to perform the upgrade and the subsequent conversion and it was only after reading these support forums I started seeing hints to do the offline conversion. Before that, it seemed like an operation / tool you could run even in a production environment. And as I assumed, it was an issue to do with both my installations being in the same database and hence probably too many reads + updates between the two.

Anyhow - my new phpBB3 is up and running with all my historic data from phpBB2, I'm happy

david63 · Post by **david63** » Thu Dec 13, 2018 7:34 pm

tryma wrote: ↑Thu Dec 13, 2018 7:24 pm As for software requiring that you do a complete database backup to your local environment in order to perform what appears to be an automated migration tool, well, that should _not_ be standard procedure.

It does not necessarily have to be to your local environment - it can be to any other test/development environment but that is considered to be the standard way of updating/upgrading/converting of any system.

warmweer · Post by **warmweer** » Thu Dec 13, 2018 7:48 pm

tryma wrote: ↑Thu Dec 13, 2018 7:24 pm As for software requiring that you do a complete database backup to your local environment in order to perform what appears to be an automated migration tool, well, that should _not_ be standard procedure.

I didn't say that the software should require (as in to force) me (or you) to do anything like that.
What is said is that what david63 ponted out, should be standard procedure as a failsafe. Something the person should consider a standard action when making changes.I NEVER update or upgrade online before testing on localhost and taking a backup - OK I have sinned in the past concerning backups but since I test on localhost, the online board is untouched until all tests are passed and then the next step is normally a backup, and only after that, the update or modification.