Database Error Number 1146

[rcwild]

It appears I am not the only one having this issue. I installed all of the files from the upload folder (except install/install.php). I changed config.php.new to config.php and edited the values. The database name and table prefix('sd_') are correct. When I open ../install/upgrade.php I get the Database Error message quoted below. I'm referring to a message on the webpage – ../install/upgrade.php. I'm receiving the error message by email, too (hundreds of times).

I use a content management system called SubDreamer. I turned it off before attempting to install the vBulletin upgrade.

I re-uploaded all of the files a second and third time and still get the same error message.

When I attempt to access my admincp, I get an error page: "Database error. The database has encountered a problem."

Before posting this I reviewed several existing threads and found most of the advice related to incorrect table prefix, database name, plugins or not installing all of the files. I don't believe any of these apply to my situation. What else could it be?

Database error in vBulletin :

Invalid SQL:

SELECT *
FROM sd_datastore
WHERE title IN ('','options','bitfields','attachmentcache','forum cache','usergroupcache','stylecache','languagecach e','products','pluginlist','cron','profilefield');

MySQL Error : Table 'content.sd_datastore' doesn't exist
Error Number : 1146
Date : Tuesday, July 3rd 2007 @ 05:44:59 AM
Script : http://www.xxx.com/forums/install/upgrade.php
Referrer :
IP Address : xx.xxx.xxx.xxx
Username :
Classname : vb_database

[Colin F]

Where you running vBulletin before already - is this an upgrade?

[rcwild]

Yes. This is an upgrade.

[rcwild]

Anyone have an idea how to solve this problem?

[Wayne Luke]

What version are you upgrading from? That table is a pretty important table and has been in the system since 3.0.

[rcwild]

Upgrading from 3.6.3. I looked in the database. It's not there.

[Wayne Luke]

You had a table prefix previously in 3.6.3 correct? If not, remove the table prefix from your 3.6.7 config.php file. Is so, then you will have to recreate the table as vBulletin won't work without it.

CREATE TABLE sd_datastore (
title CHAR(50) NOT NULL DEFAULT '',
data MEDIUMTEXT,
unserialize SMALLINT NOT NULL DEFAULT '0',
PRIMARY KEY (title)
)

[rcwild]

Yes. The "sd_" prefix has been in place for a long time. In fact, I copied the old config.php into my text editor so I had all the info handy to re-enter into the new file.

[rcwild]

First, an admission of stupidity. I copied settings from old config.php file, but it now appears I opened the wrong file. Tables with sd_ prefix pertain to our SubDreamer content management system. Tables pertaining to vBulletin have vb3_ prefix. I think I would have discovered the problem earlier, but after posting here I also discovered that our website was moved to another server during the day. My access to our database was intermitent throughout the day.

I uploaded all of the upgrade files (which turned out to be to the old server). When trying to run ../install/upgrade.php, I never made it past the database error message. Left for a few hours. Came back and discovered the error message was gone. Miracle? Proceeded with ../install/upgrade.php (which turned out to be on the new server). No glitches ... until I got to the admincp and noticed we were still running 3.6.3. Checked the files on the server. All 3.6.3. Contacted our server admin who told me about the server move.

From this point forward, I assumed my problems were related to the server move. Tunnel vision. Install attempts using sd_ prefix continued to fail. I asked server admin if move could have deleted the sd_datastore table. To be sure, she restored the site from a backup that was run before the server move. As soon as I regained access to the database, I opened phpMyAdmin to look for sd_datastore. Found vb3_datastore. Doh!

Reinstalled all upgrade files. Changed config.php.new to config.php and edited values, including table prefix vb3_. Ran ../install/upgrade.php successfully. Forums back on line. Yippee!

But ... I continue to receive hundreds of database error emails like the one quoted below. ??

Database error in vBulletin :

Invalid SQL:

SELECT *
FROM sd_datastore
WHERE title IN ('','options','bitfields','attachmentcache','forum cache','usergroupcache','stylecache','languagecach e','products','pluginlist','cron','profilefield',' smiliecache','bbcodecache','mailqueue');

MySQL Error : Table 'content.sd_datastore' doesn't exist
Error Number : 1146
Date : Wednesday, July 4th 2007 @ 05:56:43 AM
Script : ../forums/showthread.php?p=2841
Referrer :
IP Address : xx
Username :
Classname : vb_database

[Wayne Luke]

The notices are probably backlog on your email server. You could ask your hosting provider if they can clear that queue. That email would have been generated every time someone hit your page while the wrong prefix was enabled.

[rcwild]

Unfortunately, I fear the problem is a bit more serious. While our forum is up and running, we're getting error messages in web pages from some functions. For example, when I try to view "New Posts", I get:

Database error in vBulletin 3.6.3:

Invalid SQL:
SELECT searchid FROM vb3_search AS search WHERE userid = 3 AND searchhash = '5c3f0f51186248594504f962a9f31c9f' AND orderedids = '867,1672,700,1670,1668' AND announceids = '' AND completed = 1;

MySQL Error : Unknown column 'completed' in 'where clause'
Error Number : 1054
Date : Thursday, July 5th 2007 @ 10:06:50 AM
Script : ../forums/search.php?do=getdaily
Referrer : ../forums/search.php?do=getnew
IP Address : xxx
Username : xxx
Classname : vb_database

I also don't understand how the email error messages could refer to sd_datastore when I was never successful with the installation until I used vb3_ as the table prefix.

[Wayne Luke]

When you upgraded to the newer version did you run the complete upgrade script? Run the following query:

ALTER TABLE `vb3_search` ADD `completed` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT 1;

Emails refer to sd_datastore because that is on of the first queries run on every page load. The sd_ comes from the incorrect prefix in the config.php file. The script is telling you the table doesn't exist, which would have been the correct error. You can get the same email by uploading all the vBulletin files and never running an install script.

[rcwild]

When you upgraded to the newer version did you run the complete upgrade script?

Yes.

Run the following query:

ALTER TABLE `vb3_search` ADD `completed` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT 1;

Don't know how to run queries.

Emails refer to sd_datastore because that is on of the first queries run on every page load. The sd_ comes from the incorrect prefix in the config.php file. The script is telling you the table doesn't exist, which would have been the correct error. You can get the same email by uploading all the vBulletin files and never running an install script.

The table prefix in the config.php file is correct: vb3_

If my FAILED installation attempt using sd_ in the config.php set something in motion to always search for sd_datastore, how do I shut that off? Is there a line of code in a script somewhere that I can change? Something in a table that I can change? Re-run the upgrade?

[Wayne Luke]

Once you update your config.php to the proper prefix it will look for the proper table. There is no other place to change it at unless you modified the code.

You can run queries within the vBulletin Admin Control Panel by going to Maintenance -> Execute SQL Queries.

To run Queries in the Admin Control Panel you need to edit your config.php file and add your user ID to the Can Run Queries section. It looks like:

// ****** USERS WITH QUERY RUNNING PERMISSIONS ******
// The users specified here will be allowed to run queries from the control panel.
// See the above entries for more information on the format.
// Please note that the ability to run queries is quite powerful. You may wish
// to remove all user IDs from this list for security reasons.
$config['SpecialUsers']['canrunqueries'] = '';

Place your user id between the single quotes.

[rcwild]

Ran the query and received this response:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1060
error desc: Duplicate column name 'completed'

[Wayne Luke]

Then you shouldn't have gotten the error above. Please open a support ticket so someone can take a closer look at your database.

www.vbulletin.com/go/techsupport/

[rcwild]

Getting this database error message again. We moved to a new server. Imported data from database on old server. We have someone who can run a query to create the table, but he says he needs to know how to structure the table. I assume he is asking for something similar to the response I received in message #7 of this thread. Our vBulletin tables use vb3_ prefix.

Thanks in advance for your help.


Database error in vBulletin :

Invalid SQL:

SELECT *
FROM vB3_datastore
WHERE title IN ('','options','bitfields','attachmentcache','forum cache','usergroupcache','stylecache','languagecach e','products','pluginlist','cron','profilefield',' userstats','birthdaycache','maxloggedin','iconcach e','eventcache','mailqueue');

MySQL Error : Table '#####.vB3_datastore' doesn't exist
Error Number : 1146
Date : Saturday, November 17th 2007 @ 02:20:19 PM
Script : ########
Referrer :
IP Address : ########
Username :
Classname : vb_database

[Wayne Luke]

This is a pretty important table to have missing. When restoring your backup are you sure it was a complete backup? Also make sure that the prefixes on your tables match the "vB3_" specified in your config.php file. If they don't then run tableprefix.php in your install folder.

CREATE TABLE vB3_datastore (
title CHAR(50) NOT NULL DEFAULT '',
data MEDIUMTEXT,
unserialize SMALLINT NOT NULL DEFAULT '0',
PRIMARY KEY (title)
);

[rcwild]

Ran that query. Now getting an error message that includes this:

content.vB3_setting' doesn't exist

A bit more history. Our site was hacked – bad. Bad enough that it crashed our server. Moved to a new one, planning to install everything from scratch. I installed the latest version of vBulletin, then had someone import the data. He encountered a series of errors stating: "table already exists". Lines in the .sql file we exported from the old database include: "create table".

I dropped all the tables assuming the import will create all tables. Looking at phpMyAdmin I can see there are quite a few tables. Is there a list of tables that should be there so we can create any missing tables all at once?

[Steve Machol]

Unfortunately your database may be trashed. Fill out a support ticket at:

http://members.vbulletin.com/membersupport_contactform.php

Please include a complete description of the problem and be sure to include the login info to your Admin CP, phpMyAdmin and FTP in the 'Sensitive Data' field.

[rcwild]

Case issue. I went back into phpMyAdmin and noticed two _datastore tables. One vb3_ and one vB3_

Now I'm getting this at the top of the page:

Warning: array_keys(): The first argument should be an array in /includes/functions.php on line 3863

Warning: Invalid argument supplied for foreach() in /includes/functions.php on line 3863

... and this when I log in:

Unable to add cookies, header already sent.
File: #####/forums/includes/class_core.php
Line: 3075

[Kerry-Anne]

Please submit a support ticket as requested then we can investigate this issue.

[rcwild]

Thank you, Kerry-Anne, for resolving this so quickly. Love it when solutions are simple –– or at least when support staff makes it simple.