Domain Update database problem.

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Ideas Centre
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Domain Update database problem.

Post by Thunder_one » Wed Sep 09, 2015 4:53 am

Hi

sorry for my english. I am German and have a big problem and hope you can help me.

I have a new domain and a new server.

I have loaded all the files on the new server.

Database Backup I have done easily with mysqldumper.

And now the problem.

I now wanted to upload the database backup to my new domain.

Even with mysqldumper

Now he breaks me from getting at the table topics.

I always get this error.

Code: Select all

MySQL meldet:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=9764' at line 95

Error in the request:
CREATE TABLE `phpbb_topics` ( `topic_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_reported` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topic_poster` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_time_limit` int(11) unsigned NOT NULL DEFAULT '0', `topic_views` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_status` tinyint(3) NOT NULL DEFAULT '0', `topic_type` tinyint(3) NOT NULL DEFAULT '0', `topic_first_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_first_poster_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topic_first_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_last_poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_last_poster_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_subject` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_last_view_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_moved_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_bumped` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_bumper` mediumint(8) unsigned NOT NULL DEFAULT '0', `poll_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `poll_start` int(11) unsigned NOT NULL DEFAULT '0', `poll_length` int(11) unsigned NOT NULL DEFAULT '0', `poll_max_options` tinyint(4) NOT NULL DEFAULT '1', `poll_last_vote` int(11) unsigned NOT NULL DEFAULT '0', `poll_vote_change` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_visibility` tinyint(3) NOT NULL DEFAULT '0', `topic_delete_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_delete_reason` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_delete_user` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_approved` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_unapproved` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_softdeleted` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`topic_id`), KEY `forum_id` (`forum_id`), KEY `forum_id_type` (`forum_id`,`topic_type`), KEY `last_post_time` (`topic_last_post_time`), KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`), KEY `topic_visibility` (`topic_visibility`), KEY `forum_vis_last` (`forum_id`,`topic_visibility`,`topic_last_post_id`), FULLTEXT KEY `topic_title` (`topic_title`) ) ENGINE=InnoDB AUTO_INCREMENT=9764 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Fatal error: Couldn't create table or view `phpbb_topics´
I hope you can help me.

Thank you very much in advance.

User avatar
warex
Registered User
Posts: 48
Joined: Fri Jul 08, 2011 7:22 pm

Re: Domain Update database problem.

Post by warex » Wed Sep 09, 2015 1:25 pm

In these cases you should ask the host they restore your database ... make a copy of the database upload it to the root of your forum through FTP and restore them ... contact support and ask this favor, greetings.

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21148
Joined: Fri Aug 29, 2008 9:49 am
Location: Watching cricket - definitely

Re: Domain Update database problem.

Post by Mick » Wed Sep 09, 2015 1:43 pm

"The more connected we get the more alone we become" - Kyle Broflovski

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Wed Sep 09, 2015 5:34 pm

Hi,

I have done so but unfortunately get back an error message.

Code: Select all

Error at the line 253416: ) ENGINE=InnoDB AUTO_INCREMENT=9764 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Query: CREATE TABLE `phpbb_topics` (
`topic_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_reported` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`topic_poster` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_time` int(11) unsigned NOT NULL DEFAULT '0',
`topic_time_limit` int(11) unsigned NOT NULL DEFAULT '0',
`topic_views` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_status` tinyint(3) NOT NULL DEFAULT '0',
`topic_type` tinyint(3) NOT NULL DEFAULT '0',
`topic_first_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_first_poster_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`topic_first_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
`topic_last_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_last_poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_last_poster_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`topic_last_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
`topic_last_post_subject` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`topic_last_post_time` int(11) unsigned NOT NULL DEFAULT '0',
`topic_last_view_time` int(11) unsigned NOT NULL DEFAULT '0',
`topic_moved_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_bumped` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_bumper` mediumint(8) unsigned NOT NULL DEFAULT '0',
`poll_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`poll_start` int(11) unsigned NOT NULL DEFAULT '0',
`poll_length` int(11) unsigned NOT NULL DEFAULT '0',
`poll_max_options` tinyint(4) NOT NULL DEFAULT '1',
`poll_last_vote` int(11) unsigned NOT NULL DEFAULT '0',
`poll_vote_change` tinyint(1) unsigned NOT NULL DEFAULT '0',
`topic_visibility` tinyint(3) NOT NULL DEFAULT '0',
`topic_delete_time` int(11) unsigned NOT NULL DEFAULT '0',
`topic_delete_reason` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`topic_delete_user` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_posts_approved` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_posts_unapproved` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topic_posts_softdeleted` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`topic_id`),
KEY `forum_id` (`forum_id`),
KEY `forum_id_type` (`forum_id`,`topic_type`),
KEY `last_post_time` (`topic_last_post_time`),
KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`),
KEY `topic_visibility` (`topic_visibility`),
KEY `forum_vis_last` (`forum_id`,`topic_visibility`,`topic_last_post_id`),
FULLTEXT KEY `topic_title` (`topic_title`)
) ENGINE=InnoDB AUTO_INCREMENT=9764 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

MySQL: The used table type doesn't support FULLTEXT indexes

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

Re: Domain Update database problem.

Post by stevemaury » Wed Sep 09, 2015 8:14 pm

Run this query on the database you are trying to import and report the output:

Code: Select all

SELECT config_value FROM phpbb_config WHERE config_name = 'search_type'
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Wed Sep 09, 2015 8:29 pm

stevemaury wrote:Run this query on the database you are trying to import and report the output:

Code: Select all

SELECT config_value FROM phpbb_config WHERE config_name = 'search_type'
Hi,

Where should I enter?

Sorry I understand not what I need right now.

do you mean this ?

Image

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

Re: Domain Update database problem.

Post by stevemaury » Wed Sep 09, 2015 10:44 pm

Yep, that's it. Full text_native IS compatible with InnoDB tables. But try this. Before importing the database, run this query:

Code: Select all

UPDATE phpbb_config SET config_value = 'fulltext_mysql' WHERE config_name = 'search_type'
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Wed Sep 09, 2015 11:18 pm

OK. The database is empty. Now when I

Code: Select all

UPDATE phpbb_config SET config_value = 'fulltext_mysql' WHERE config_name = 'search_type'
up out, I get an error message

# 1146 - Table 'phpbb.phpbb_config' does not exist

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Wed Sep 09, 2015 11:30 pm

I have run the command in phpmyadmin.

Then I have the database back to the dumper recorded but still get the error

Code: Select all

MySQL-ERROR
MySQL meldet:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=9772' at line 95

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

Re: Domain Update database problem.

Post by stevemaury » Thu Sep 10, 2015 12:40 am

Thunder_one wrote:OK. The database is empty. Now when I

Code: Select all

UPDATE phpbb_config SET config_value = 'fulltext_mysql' WHERE config_name = 'search_type'
up out, I get an error message

# 1146 - Table 'phpbb.phpbb_config' does not exist
Of course it doesn't exist if the database is empty. Why did you empty it?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Domain Update database problem.

Post by Oyabun1 » Thu Sep 10, 2015 4:21 am

I think the issue is that MariaDB doesn't natively support InnoDB.

If your host has MySQL available try using that rather than MariaDB.

Otherwise, you could try altering the backup file, by making a copy of it and doing a find and replace of TYPE=InnoDB with TYPE=XtraDB.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Thu Sep 10, 2015 9:51 am

Hi

I have my Hoster written that he should install me a MySQL database when the MariaDB problems makes.

I have the date with the Bank opened editor and TYPE = InnoDB searched but find nothing there.

Replace wanted TYPE = InnoDB with TYPE = XtraDB.

Thank you for your help.

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Domain Update database problem.

Post by Oyabun1 » Thu Sep 10, 2015 11:11 am

Sorry, Iwas going by the error message. It should be ENGINE=InnoDB replace with ENGINE=XtraDB.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Thu Sep 10, 2015 11:31 am

I despair yet :(

I have made

error

Code: Select all

MySQL meldet:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=XtraDB AUTO_INCREMENT=9772' at line 95

Fehler bei der Anfrage:
CREATE TABLE `phpbb_topics` ( `topic_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_reported` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topic_poster` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_time_limit` int(11) unsigned NOT NULL DEFAULT '0', `topic_views` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_status` tinyint(3) NOT NULL DEFAULT '0', `topic_type` tinyint(3) NOT NULL DEFAULT '0', `topic_first_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_first_poster_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topic_first_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_last_poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_last_poster_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_poster_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_subject` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_last_post_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_last_view_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_moved_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_bumped` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_bumper` mediumint(8) unsigned NOT NULL DEFAULT '0', `poll_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `poll_start` int(11) unsigned NOT NULL DEFAULT '0', `poll_length` int(11) unsigned NOT NULL DEFAULT '0', `poll_max_options` tinyint(4) NOT NULL DEFAULT '1', `poll_last_vote` int(11) unsigned NOT NULL DEFAULT '0', `poll_vote_change` tinyint(1) unsigned NOT NULL DEFAULT '0', `topic_visibility` tinyint(3) NOT NULL DEFAULT '0', `topic_delete_time` int(11) unsigned NOT NULL DEFAULT '0', `topic_delete_reason` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `topic_delete_user` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_approved` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_unapproved` mediumint(8) unsigned NOT NULL DEFAULT '0', `topic_posts_softdeleted` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`topic_id`), KEY `forum_id` (`forum_id`), KEY `forum_id_type` (`forum_id`,`topic_type`), KEY `last_post_time` (`topic_last_post_time`), KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`), KEY `topic_visibility` (`topic_visibility`), KEY `forum_vis_last` (`forum_id`,`topic_visibility`,`topic_last_post_id`), FULLTEXT KEY `topic_title` (`topic_title`) ) ENGINE=XtraDB AUTO_INCREMENT=9772 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Fatal error: Couldn't create table or view `phpbb_topics´

Thunder_one
Registered User
Posts: 119
Joined: Sat May 09, 2015 11:00 pm

Re: Domain Update database problem.

Post by Thunder_one » Thu Sep 10, 2015 2:24 pm

Found error.

The standing here with me

Image

Now it is correct.

The other problem was that had my MariaDB an old version.

My hoster has this now will update to the latest version and now the error is gone.

The error must come first. :)

Locked

Return to “[3.1.x] Support Forum”