Perplexing database upgrade: 3.0.4 to 3.2.1

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Sun Sep 17, 2017 1:59 pm

Hi
Attempting trial upgrade of 3.0.4 to 3.2.1 on an Ubuntu virtual machine, prior to upgrading 'for real' (I'm a cautious soul :lol: ). Deduced I definitely needed a full package upgrade and instructions followed as supplied here...

phpBB 3.0 - 3.2

... I tried this several times, always an error at 10% on database upgrade...

Image

... that's quite an error ! I'm stuck with it.

I then tried a staged upgrade from the older versions archive, 3.0.4 to 3.0.14 then on to 3.1, then 3.2. Bottomline is I can't even get as far as 3.0.14. The install in this case recognises the versions correctly but on database update hangs at displaying the word 'Error' and that's it !... no error description, though I suspect it's the same as before. Command line upgrade (as opposed to GUI) gives same error.

It's possibly looking like a corruption in the database ? This was originally a phpBB version 2 database, upgraded to 3.0.4 a long time ago and as such correctly contains 92 tables with both phpbb_ and phpbb3_ prefixes. As version 3.0.4 it all works just fine !

1. Is there any phpBB 'tool' that I can use to ascertain the integrity of the database ?
2. Any other pointers as to what might be wrong here would be genuinely appreciated.

thanks and best regards

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 47781
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by stevemaury » Sun Sep 17, 2017 2:06 pm

Is the table prefix in the config.php file correct for the set of tables you want to update (that is, the ones currently used by 3.0.4)?
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Sun Sep 17, 2017 2:33 pm

Hi stevemaury

Yes the table prefix in the config.php file is correct, it is phpBB3_ ,which would be those created during the long ago version 2 to 3.0.4 conversion. I have tried many options with config.php eg. my original config.php had $dbms = 'mysql', so I tried $dbms = 'mysqli'. All this appears to do is change the beginning of the error to read either 'mysql4' or 'mysqli' respectively.
Also, I delete the database and restore it from backup each time I make an attempt to upgrade to ensure I then don't attempt to upgrade something that a failed upgrade might have already started on. I say this because after a failed upgrade I do have 1 more table (ie. 93 instead of 92).

The newly added table prior to database upgrade failure is 'phpbb3_migrations'.

thanks

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Mon Sep 18, 2017 2:10 pm

I can't see anything that is particularly obvious as a trend in the 'mega error' I posted above but to those of you who have far more knowledge of phpBB code than I do, is there anything in it which shows any sort of trend, which might give me a clue as to where I should be looking ?

Thanks in anticipation.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 47781
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by stevemaury » Mon Sep 18, 2017 2:35 pm

The text is unreadable in the image you posted. How about pasting it into the code tags here?
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Mon Sep 18, 2017 3:37 pm

Thanks Steve, certainly could do that but would need to reproduce the error and capture the text again, all development machines shutdown at present.

Much easier way, which should be available within any browser, is to right click and then select 'view image' or similar. This will display the image fullsize (currently it is constrained by the phpBB container). This certainly works ok for current Firefox versions. hth.

rxu
Extensions Development Team
Posts: 2751
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by rxu » Tue Sep 19, 2017 3:48 am

What are the MySQL version and tables type (InnoDB/MyISAM)?
(For the reference: https://tracker.phpbb.com/browse/PHPBB3-8944).

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Tue Sep 19, 2017 9:21 am

rxu thanks, the database server is 5.5.5-10.1.18-MariaDB-1~xenial.
All tables are MyISAM with the only exception being the original, phpBB version 2 phpbb_sessions table, which is a memory table. In version 3, I think this table became MyISAM too.
The tables I'm working with have been migrated from a hosting provider who is currently running MySQL 5.5

Does this give any clues ?

rxu
Extensions Development Team
Posts: 2751
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by rxu » Tue Sep 19, 2017 9:49 am

Well, as per the MariaDB docs https://mariadb.com/kb/en/library/conve ... to-innodb/
The maximum length of an INDEX is different between the Engines. (This change is not likely to hit you, but watch out.) MyISAM allows 1000 bytes; InnoDB allows 767 bytes
post_username field is to be varchar(255), so having in mind utf8 encoding which takes 3 bytes per character, it can't be longer than 765 bytes. So, even 255 chars long username shouldn't hit the limits.
Can you check the field type and length in the old/new database?

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Tue Sep 19, 2017 11:36 am

Ok, I understand about the field length issue and that requiring a conversion to a varchar(255) field should not affect either MyISAM or InnoDB tables.

Currently within the unconverted phpbb3_posts table, the post_username field is a blob field. My understanding of the error is that this is to be converted to a varchar(255) but is failing, is my understanding correct ?

Also is this conversion attempting to convert all the tables to InnoDB tables ?, surely this would not be necessary (although it may be preferable). Even with my current MariaDB version I should not be hitting any limits really (btw. it's a very small database only 519 posts).

So, field length not really applicable in old database as it's a blob field in this case.
It never gets to executing the SQL ALTER statement and post_username remains a blob field at the point the database update fails.

The single table that does get created is the phpbb3_migrations table which shows the 3.0.5 migrations (completing?) with the same start and finish times and then the table truncating at the 3.0.6 rc1 migration. What bothers me about this table is that it is created as an InnoDB table and all others are MyISAM tables. Can they coexist safely within the same database ? Perhaps I should attempt conversion of all old MyISAM tables to InnoDB tables before attempting database update ?

I hope some of this helps, thanks.

rxu
Extensions Development Team
Posts: 2751
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by rxu » Tue Sep 19, 2017 12:45 pm

Peter_B wrote:
Tue Sep 19, 2017 11:36 am
Currently within the unconverted phpbb3_posts table, the post_username field is a blob field.
Well, afaik (and from the migration files) it should be varchar(255). It was even varchar(25) in phpBB2 :)
Peter_B wrote:
Tue Sep 19, 2017 11:36 am
Also is this conversion attempting to convert all the tables to InnoDB tables ?
No.
Peter_B wrote:
Tue Sep 19, 2017 11:36 am
What bothers me about this table is that it is created as an InnoDB table and all others are MyISAM tables.
This may happen if currently InnoDB is set as default engine for your DBMS (which could be MyISAM in the at some point in the past).

Also, phpBB should work with either tables type ( MyISAM/InnoDB regardless) and doesn't require any particular tables type.

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Tue Sep 19, 2017 1:38 pm

This is weird !
post_username field is definitely a blob field in this case, even more perversely it is empty.

I manually converted post_username to a varchar(255) and retried the database conversion for the hell of it. This time it failed at 6% with the error that an 'acm module was missing' (though I have supplied all files necessary according to the upgrade instructions)

This board works fine at version 3.0.4, I have no idea why the database conversion is behaving like this. Looks like it will have to remain at 3.0.4 but that's crazy... for what reason... driving me mad :evil:

All I'm doing is working with an untouched MySQL dump generated from within the ACP of the board. In fact I've also tried one generated by PhpMyAdmin as well. That's a bit different as it still contains the version 2 tables, not that it should make any difference.

I'm now standing at somewhere between 20 to 30 failed conversion attempts, having made various small configuration changes along the way. I bet in the end it will be something really simple (often is) but I can't see it, I've just followed the instructions as written.

Any other ideas ? thanks.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 50698
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Brf » Tue Sep 19, 2017 1:51 pm

Peter_B wrote:
Tue Sep 19, 2017 1:38 pm
it is empty.
post_username should be empty, unless the post was made by an unregistered guest user. Back in phpbb2 it was defined as a VARCHAR(25).

Peter_B
Registered User
Posts: 10
Joined: Sun Sep 17, 2017 1:20 pm

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by Peter_B » Tue Sep 19, 2017 2:47 pm

A varchar(25)... very generous :)
It is starting to seem like that when the update to 3.0.4 was done many years ago that, perhaps the upgrade was errr... 'inappropriately scripted'. Though if that was the case it must have shown itself on countless other conversions over time, so I just don't know really. Just out of interest here's a list of the fields in the phpbb3_posts table as it currently stands...

Code: Select all

  post_id mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  topic_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  forum_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  poster_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  icon_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  poster_ip varchar(40) binary CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  post_time int(11) UNSIGNED NOT NULL DEFAULT 0,
  post_approved tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  post_reported tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
  enable_bbcode tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  enable_smilies tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  enable_magic_url tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  enable_sig tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  post_username blob NOT NULL,
  post_subject blob NOT NULL,
  post_text mediumblob NOT NULL,
  post_checksum varchar(32) binary CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  post_attachment tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
  bbcode_bitfield varchar(255) binary CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  bbcode_uid varchar(8) binary CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  post_postcount tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  post_edit_time int(11) UNSIGNED NOT NULL DEFAULT 0,
  post_edit_reason blob NOT NULL,
  post_edit_user mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  post_edit_count smallint(4) UNSIGNED NOT NULL DEFAULT 0,
  post_edit_locked tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (post_id),
  INDEX forum_id (forum_id),
  INDEX post_approved (post_approved),
  INDEX post_username (post_username),
  INDEX poster_id (poster_id),
  INDEX poster_ip (poster_ip),
  INDEX tid_post_time (topic_id, post_time),
  INDEX topic_id (topic_id)
)
ENGINE = MYISAM
AUTO_INCREMENT = 745
AVG_ROW_LENGTH = 3128
CHARACTER SET latin1
COLLATE latin1_general_ci;
... there are other blob fields where you might expect varchar types but this is what the version 2.x to 3.0.4 did. It would still be great to be able to actually upgrade but it's looking less and less possible. Any other ideas would be genuinely appreciated. Thanks.

rxu
Extensions Development Team
Posts: 2751
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: Perplexing database upgrade: 3.0.4 to 3.2.1

Post by rxu » Tue Sep 19, 2017 5:42 pm

The table schema you provided seems to be not quite correct as per phpBB3 database schema, also latin1 charset should be rather utf8_bin. In theory, you could try using mysql_upgrader.php script to fix the things like described here https://www.phpbb.com/support/docs/en/3 ... ue-errors/ with the exception that the script for 3.0 is located here.

Post Reply

Return to “[3.2.x] Support Forum”

Who is online

Users browsing this forum: Helios7, sqnconductive and 14 guests

cron