Support Forum

Advanced Search
Forum Scope


Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
general-topic
Invalid database query error and can't create forum
Avatar
Yellow Swordfish
Glinton, England
SP Master
sp_UserOfflineSmall 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...

andy-signature.png
YELLOW
SWORDFISH
Avatar
dogsday
Member
Free Members
sp_UserOfflineSmall 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

Avatar
Yellow Swordfish
Glinton, England
SP Master
sp_UserOfflineSmall 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!

andy-signature.png
YELLOW
SWORDFISH
Avatar
dogsday
Member
Free Members
sp_UserOfflineSmall 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

Avatar
dogsday
Member
Free Members
sp_UserOfflineSmall Offline
Jul 11, 2018 - 6:03 am

Found the engine... Hope this helps.

Show-engine.pngImage Enlarger

Avatar
Yellow Swordfish
Glinton, England
SP Master
sp_UserOfflineSmall 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...

andy-signature.png
YELLOW
SWORDFISH
Avatar
dogsday
Member
Free Members
sp_UserOfflineSmall 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

Avatar
dogsday
Member
Free Members
sp_UserOfflineSmall 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

Avatar
Yellow Swordfish
Glinton, England
SP Master
sp_UserOfflineSmall 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.

andy-signature.png
YELLOW
SWORDFISH
Forum Timezone: Europe/Stockholm
Most Users Ever Online: 1170
Currently Online:
Guest(s) 1
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Mr Papa: 19448
Ike: 2086
Brandon: 864
kvr28: 804
jim: 639
FidoSysop: 577
Conrad_Farlow: 531
fiddlerman: 358
Stefano Prete: 325
Member Stats:
Guest Posters: 616
Members: 17342
Moderators: 0
Admins: 4
Forum Stats:
Groups: 7
Forums: 17
Topics: 10116
Posts: 79581