Converting old phpBB to v3 - BLOBs in DB

[joth]

Hi.

I have an (very) old phpBB that needs to be converted to v3. I ran the v3 installation and converted the old phpBB with the converter, all passed OK. But after the conversion I cannot login or view the admin panel anymore. It seems that several fields in the DB now are BLOBs instead of VARCHAR or TEXT.

I tried to edit all fields that I found to the correct type (according to the v3 install files) but without any success. I also found this topic viewtopic.php?f=65&t=2152650 that I tried to follow. My problem with that solution is that I neither can figure ouit what character is used on the old phpBB (ie UTF-8, Latin etc). And also, if I try to alter the phpBB_users table I get this error message:

Code: Select all

SQL query:

ALTER TABLE phpbb_users CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci

MySQL said: Documentation

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci' at l 

Any suggestions where to start? How can I verify the current character set?
I use PHPMyAdmin to admin the DB.

Cheers
Johan

[Oyabun1]

My problem with that solution is that I neither can figure ouit what character is used on the old phpBB (ie UTF-8, Latin etc)

Run the SQL query

Code: Select all

SHOW CREATE TABLE phpbb_users

Change phpbb_ if that is not your table prefix.

[joth]

Ok, done that, this is from the original phpBB:

Code: Select all

CREATE TABLE `teknik_users` (
 `user_id` mediumint(8) NOT NULL default '0',
 `user_active` tinyint(1) default '1',
 `username` varchar(25) NOT NULL default '',
 `user_password` varchar(32) NOT NULL default '',
 `user_session_time` int(11) NOT NULL default '0',
 `user_session_page` smallint(5) NOT NULL default '0',
 `user_lastvisit` int(11) NOT NULL default '0',
 `user_regdate` int(11) NOT NULL default '0',
 `user_level` tinyint(4) default '0',
 `user_posts` mediumint(8) unsigned NOT NULL default '0',
 `user_timezone` decimal(5,2) NOT NULL default '0.00',
 `user_style` tinyint(4) default NULL,
 `user_lang` varchar(255) default NULL,
 `user_dateformat` varchar(14) NOT NULL default 'd M Y H:i',
 `user_new_privmsg` smallint(5) unsigned NOT NULL default '0',
 `user_unread_privmsg` smallint(5) unsigned NOT NULL default '0',
 `user_last_privmsg` int(11) NOT NULL default '0',
 `user_emailtime` int(11) default NULL,
 `user_viewemail` tinyint(1) default NULL,
 `user_attachsig` tinyint(1) default NULL,
 `user_allowhtml` tinyint(1) default '1',
 `user_allowbbcode` tinyint(1) default '1',
 `user_allowsmile` tinyint(1) default '1',
 `user_allowavatar` tinyint(1) NOT NULL default '1',
 `user_allow_pm` tinyint(1) NOT NULL default '1',
 `user_allow_viewonline` tinyint(1) NOT NULL default '1',
 `user_notify` tinyint(1) NOT NULL default '1',
 `user_notify_pm` tinyint(1) NOT NULL default '0',
 `user_popup_pm` tinyint(1) NOT NULL default '0',
 `user_rank` int(11) default '0',
 `user_avatar` varchar(100) default NULL,
 `user_avatar_type` tinyint(4) NOT NULL default '0',
 `user_email` varchar(255) default NULL,
 `user_icq` varchar(15) default NULL,
 `user_website` varchar(100) default NULL,
 `user_from` varchar(100) default NULL,
 `user_sig` text,
 `user_sig_bbcode_uid` varchar(10) default NULL,
 `user_aim` varchar(255) default NULL,
 `user_yim` varchar(255) default NULL,
 `user_msnm` varchar(255) default NULL,
 `user_occ` varchar(100) default NULL,
 `user_interests` varchar(255) default NULL,
 `user_actkey` varchar(32) default NULL,
 `user_newpasswd` varchar(32) default NULL,
 PRIMARY KEY  (`user_id`),
 KEY `user_session_time` (`user_session_time`)
) TYPE=MyISAM

This is from the new phpBB:

Code: Select all

CREATE TABLE `phpbb_users` (
 `user_id` mediumint(8) unsigned NOT NULL auto_increment,
 `user_type` tinyint(2) NOT NULL default '0',
 `group_id` mediumint(8) unsigned NOT NULL default '3',
 `user_permissions` mediumblob NOT NULL,
 `user_perm_from` mediumint(8) unsigned NOT NULL default '0',
 `user_ip` varchar(40) binary NOT NULL default '',
 `user_regdate` int(11) unsigned NOT NULL default '0',
 `username` blob NOT NULL,
 `username_clean` blob NOT NULL,
 `user_password` varchar(120) binary NOT NULL default '',
 `user_passchg` int(11) unsigned NOT NULL default '0',
 `user_pass_convert` tinyint(1) unsigned NOT NULL default '0',
 `user_email` blob NOT NULL,
 `user_email_hash` bigint(20) NOT NULL default '0',
 `user_birthday` varchar(10) binary NOT NULL default '',
 `user_lastvisit` int(11) unsigned NOT NULL default '0',
 `user_lastmark` int(11) unsigned NOT NULL default '0',
 `user_lastpost_time` int(11) unsigned NOT NULL default '0',
 `user_lastpage` blob NOT NULL,
 `user_last_confirm_key` varchar(10) binary NOT NULL default '',
 `user_last_search` int(11) unsigned NOT NULL default '0',
 `user_warnings` tinyint(4) NOT NULL default '0',
 `user_last_warning` int(11) unsigned NOT NULL default '0',
 `user_login_attempts` tinyint(4) NOT NULL default '0',
 `user_inactive_reason` tinyint(2) NOT NULL default '0',
 `user_inactive_time` int(11) unsigned NOT NULL default '0',
 `user_posts` mediumint(8) unsigned NOT NULL default '0',
 `user_lang` varchar(30) binary NOT NULL default '',
 `user_timezone` decimal(5,2) NOT NULL default '0.00',
 `user_dst` tinyint(1) unsigned NOT NULL default '0',
 `user_dateformat` varchar(90) binary NOT NULL default 'd M Y H:i',
 `user_style` mediumint(8) unsigned NOT NULL default '0',
 `user_rank` mediumint(8) unsigned NOT NULL default '0',
 `user_colour` varchar(6) binary NOT NULL default '',
 `user_new_privmsg` int(4) NOT NULL default '0',
 `user_unread_privmsg` int(4) NOT NULL default '0',
 `user_last_privmsg` int(11) unsigned NOT NULL default '0',
 `user_message_rules` tinyint(1) unsigned NOT NULL default '0',
 `user_full_folder` int(11) NOT NULL default '-3',
 `user_emailtime` int(11) unsigned NOT NULL default '0',
 `user_topic_show_days` smallint(4) unsigned NOT NULL default '0',
 `user_topic_sortby_type` char(1) binary NOT NULL default 't',
 `user_topic_sortby_dir` char(1) binary NOT NULL default 'd',
 `user_post_show_days` smallint(4) unsigned NOT NULL default '0',
 `user_post_sortby_type` char(1) binary NOT NULL default 't',
 `user_post_sortby_dir` char(1) binary NOT NULL default 'a',
 `user_notify` tinyint(1) unsigned NOT NULL default '0',
 `user_notify_pm` tinyint(1) unsigned NOT NULL default '1',
 `user_notify_type` tinyint(4) NOT NULL default '0',
 `user_allow_pm` tinyint(1) unsigned NOT NULL default '1',
 `user_allow_viewonline` tinyint(1) unsigned NOT NULL default '1',
 `user_allow_viewemail` tinyint(1) unsigned NOT NULL default '1',
 `user_allow_massemail` tinyint(1) unsigned NOT NULL default '1',
 `user_options` int(11) unsigned NOT NULL default '230271',
 `user_avatar` varchar(255) binary NOT NULL default '',
 `user_avatar_type` tinyint(2) NOT NULL default '0',
 `user_avatar_width` smallint(4) unsigned NOT NULL default '0',
 `user_avatar_height` smallint(4) unsigned NOT NULL default '0',
 `user_sig` mediumblob NOT NULL,
 `user_sig_bbcode_uid` varchar(8) binary NOT NULL default '',
 `user_sig_bbcode_bitfield` varchar(255) binary NOT NULL default '',
 `user_from` blob NOT NULL,
 `user_icq` varchar(15) binary NOT NULL default '',
 `user_aim` blob NOT NULL,
 `user_yim` blob NOT NULL,
 `user_msnm` blob NOT NULL,
 `user_jabber` blob NOT NULL,
 `user_website` blob NOT NULL,
 `user_occ` blob NOT NULL,
 `user_interests` blob NOT NULL,
 `user_actkey` varchar(32) binary NOT NULL default '',
 `user_newpasswd` varchar(120) binary NOT NULL default '',
 `user_form_salt` varchar(96) binary NOT NULL default '',
 `user_new` tinyint(1) unsigned NOT NULL default '1',
 `user_reminded` tinyint(4) NOT NULL default '0',
 `user_reminded_time` int(11) unsigned NOT NULL default '0',
 PRIMARY KEY  (`user_id`),
 UNIQUE KEY `username_clean` (`username_clean`(255)),
 KEY `user_birthday` (`user_birthday`),
 KEY `user_email_hash` (`user_email_hash`),
 KEY `user_type` (`user_type`)
) TYPE=MyISAM

[joth]

Nevermind, this seems to be a big issue so I'll simply start from scratch. Thanks anyway!