Support Forum

Simple:Press 6 General Topics

Invalid database query error and can't create forum

Page: 1 2

Invalid database query error and can't create forum

Yellow Swordfish

Glinton, England

SP Master

Offline

Jul 10, 2018 - 3:30 pm

It is, actually, very simple using phpmyadmin. If you run it - select the correct database so you have a listing of the tables in the main window and then select the 'SQL' menu option along the top.

Then in the input window you get, simply paste the SQL command and click on the 'Go' button beneath.

There are two tables missing so the following two commands should create them depending on the reason they were not created in the first place. Hopefully this may become apparent!

So - for sfforums table:

CREATE TABLE `wp_sfforums` (
`forum_id` bigint(20) NOT NULL AUTO_INCREMENT,
`forum_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
`group_id` bigint(20) NOT NULL,
`forum_seq` int(4) DEFAULT NULL,
`forum_desc` mediumtext COLLATE utf8mb4_unicode_ci,
`forum_status` int(4) NOT NULL DEFAULT '0',
`forum_slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
`forum_rss` mediumtext COLLATE utf8mb4_unicode_ci,
`forum_icon` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`post_id` bigint(20) DEFAULT NULL,
`topic_count` mediumint(8) DEFAULT '0',
`forum_rss_private` smallint(1) NOT NULL DEFAULT '0',
`post_count` mediumint(8) DEFAULT '0',
`parent` bigint(20) NOT NULL DEFAULT '0',
`children` mediumtext COLLATE utf8mb4_unicode_ci,
`forum_message` mediumtext COLLATE utf8mb4_unicode_ci,
`post_id_held` bigint(20) DEFAULT NULL,
`post_count_held` mediumint(8) DEFAULT '0',
`forum_icon_new` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`topic_icon` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`topic_icon_new` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`keywords` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`forum_disabled` smallint(1) NOT NULL DEFAULT '0',
`topic_icon_locked` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`forum_icon_locked` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`topic_icon_pinned` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`feature_image` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_topic_id` bigint(20) NOT NULL DEFAULT '0',
`topic_icon_pinned_new` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`forum_id`),
KEY `group_id_idx` (`group_id`),
KEY `forum_slug_idx` (`forum_slug`),
KEY `post_id_idx` (`post_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and for sftopics;

CREATE TABLE `wp_sftopics` (
`topic_id` bigint(20) NOT NULL AUTO_INCREMENT,
`topic_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
`topic_date` datetime NOT NULL,
`topic_status` int(4) NOT NULL DEFAULT '0',
`forum_id` bigint(20) NOT NULL,
`user_id` bigint(20) unsigned DEFAULT NULL,
`topic_pinned` smallint(1) NOT NULL DEFAULT '0',
`topic_opened` bigint(20) NOT NULL DEFAULT '0',
`topic_slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
`post_id` bigint(20) DEFAULT NULL,
`post_count` mediumint(8) DEFAULT '0',
`post_id_held` bigint(20) DEFAULT NULL,
`post_count_held` mediumint(8) DEFAULT '0',
PRIMARY KEY (`topic_id`),
KEY `forum_id_idx` (`forum_id`),
KEY `topic_slug_idx` (`topic_slug`),
KEY `user_id_idx` (`user_id`),
KEY `post_id_idx` (`post_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Just click the raw code button - select and copy from each of the above one by one, - and paste into th SQL window and click on Go...

YELLOW

SWORDFISH

dogsday

Member

Free Members

Offline

Jul 11, 2018 - 2:31 am

Thanks for the code.

Ran the first and got an sql error...
#1709 - Index column size too large. The maximum column size is 767 bytes.

Not tried the second

Best wishes
DD

Yellow Swordfish

Glinton, England

SP Master

Offline

Jul 11, 2018 - 3:38 am

Ah - curiouser and curiouser... I am learning something new but am still surprised that this is a unique occurrence. Can you tell me what engine type you are using? By default - MySQL is usually installed using the MyISAM engine. But I suspect you may have switched to the InnoDB engine. Might be useful information...

Anyway - can you try this version for the sfforums table. You will note that all I have changed here is removing the collation data. This is probably best as it will then inherit from your database.

CREATE TABLE `wp_sfforums` (
`forum_id` bigint(20) NOT NULL AUTO_INCREMENT,
`forum_name` varchar(200) NOT NULL,
`group_id` bigint(20) NOT NULL,
`forum_seq` int(4) DEFAULT NULL,
`forum_desc` mediumtext,
`forum_status` int(4) NOT NULL DEFAULT '0',
`forum_slug` varchar(200) NOT NULL,
`forum_rss` mediumtext,
`forum_icon` varchar(50) DEFAULT NULL,
`post_id` bigint(20) DEFAULT NULL,
`topic_count` mediumint(8) DEFAULT '0',
`forum_rss_private` smallint(1) NOT NULL DEFAULT '0',
`post_count` mediumint(8) DEFAULT '0',
`parent` bigint(20) NOT NULL DEFAULT '0',
`children` mediumtext,
`forum_message` mediumtext,
`post_id_held` bigint(20) DEFAULT NULL,
`post_count_held` mediumint(8) DEFAULT '0',
`forum_icon_new` varchar(50) DEFAULT NULL,
`topic_icon` varchar(50) DEFAULT NULL,
`topic_icon_new` varchar(50) DEFAULT NULL,
`keywords` varchar(256) DEFAULT NULL,
`forum_disabled` smallint(1) NOT NULL DEFAULT '0',
`topic_icon_locked` varchar(50) DEFAULT NULL,
`forum_icon_locked` varchar(50) DEFAULT NULL,
`topic_icon_pinned` varchar(50) DEFAULT NULL,
`feature_image` varchar(50) DEFAULT NULL,
`last_topic_id` bigint(20) NOT NULL DEFAULT '0',
`topic_icon_pinned_new` varchar(50) DEFAULT NULL,
PRIMARY KEY (`forum_id`),
KEY `group_id_idx` (`group_id`),
KEY `forum_slug_idx` (`forum_slug`),
KEY `post_id_idx` (`post_id`)
);

Fingers crossed!

YELLOW

SWORDFISH

dogsday

Member

Free Members

Offline

Jul 11, 2018 - 5:59 am

Thank you.

that seems to have worked fine. It returned a comment that the table was empty. Looking at the schema, there is now a table called WP-sfforums.

Please could you send updated for the other table too?

I can't find the information that you request regarding the engine. We are hosting with LiquidWeb, previously Rackspace Cloud, and they went through a database update last year to debian(?) from memory. Happy to dig around a bit more.

Thanks for your continued help
DD

dogsday

Member

Free Members

Offline

Jul 11, 2018 - 6:03 am

Found the engine... Hope this helps.

Yellow Swordfish

Glinton, England

SP Master

Offline

Jul 11, 2018 - 6:08 am

Well that is good news and gives me something specific to look for. Thanks.

Looks like InnoDB it is so I need to run some test installs by the look of it!

Here is the other table create SQL:

CREATE TABLE `wp_sftopics` (
`topic_id` bigint(20) NOT NULL AUTO_INCREMENT,
`topic_name` varchar(200) NOT NULL,
`topic_date` datetime NOT NULL,
`topic_status` int(4) NOT NULL DEFAULT '0',
`forum_id` bigint(20) NOT NULL,
`user_id` bigint(20) unsigned DEFAULT NULL,
`topic_pinned` smallint(1) NOT NULL DEFAULT '0',
`topic_opened` bigint(20) NOT NULL DEFAULT '0',
`topic_slug` varchar(200) NOT NULL,
`post_id` bigint(20) DEFAULT NULL,
`post_count` mediumint(8) DEFAULT '0',
`post_id_held` bigint(20) DEFAULT NULL,
`post_count_held` mediumint(8) DEFAULT '0',
PRIMARY KEY (`topic_id`),
KEY `forum_id_idx` (`forum_id`),
KEY `topic_slug_idx` (`topic_slug`),
KEY `user_id_idx` (`user_id`),
KEY `post_id_idx` (`post_id`)
);

hopefully will not cause any problem either...

YELLOW

SWORDFISH

dogsday

Member

Free Members

Offline

Jul 11, 2018 - 6:12 am

Great! That worked too.

I'll go and have a play with the configuration now and see if I can create any fora.

I have a few test domains all running WP with the same configuration on that host. Happy to help with testing if needed.

Cheers
DD

dogsday

Member

Free Members

Offline

Jul 11, 2018 - 6:22 am

Just to close off the other point about the front end database error. That is also now resolved after manually creating those two tables.

The rest of the configuration options look straightforward enough for someone used to managing a forum 😉

Thanks for your help to get to this point. It's much appreciated.

Kind regards
DD

Yellow Swordfish

Glinton, England

SP Master

Offline

Jul 11, 2018 - 9:01 am

And I have opened a bug report on the install. It is clearly an InnoDB Engine issue...
Hope it all goes well from now on.

YELLOW

SWORDFISH

Page: 1 2

All RSS

Forum Timezone: Europe/Stockholm

Most Users Ever Online: 1170

Currently Online:

Guest(s) 1

Currently Browsing this Page:
1 Guest(s)