SQL Database Errors: How to fix missing tables!

mydatery posted 27th of July 2009 in Community Voice. 6 comments.

Okay, we are always getting asked how to fix an SQL error and it is very rare that we ever have the answer, we see it like this in the forums:

Help!!! My site has fallen and it' can't get up!!!

Okay, not that exact line, but that is all we get in the subject line of the thread followed if we're lucky with this:

Database query error

Query:
SELECT * FROM `ProfilesSettings` WHERE `IDMember` = '7'

Mysql error:
Table 'tbcsi1_yoursite.com.ProfilesSettings' doesn't exist

Found error in file /home/dirname/public_html/templates/base/scripts/

BxBaseProfileView.php
at line 172. Called db_arr function with erroneous argument #0

Debug backtrace:

Array
(
    [1] => Array
        (
            [file] => /home/dirname/public_html/inc/db.inc.php
            [line] => 104
            [function] => db_res
            [args] => Array
                (
                    [0] => SELECT * FROM `ProfilesSettings` WHERE

 `IDMember` = '7'
                    [1] => 1
                )

        )

    [2] => Array
        (
            [file] => /home/dirname/public_html/templates/base/scripts/

BxBaseProfileView.php
            [line] => 172
            [function] => db_arr
            [args] => Array
                (
                    [0] => SELECT * FROM `ProfilesSettings` WHERE

`IDMember` = '7'
                )

        )

    [3] => Array
        (
            [file] => /home/dirname/public_html/profile.php
            [line] => 81
            [function] => genProfileCSS

Please keep in mind, I didn't post the whole thing, it goes on literally for over 3,000 lines of useless data. Everything we need is within that error section already.

What is it? How do I fix this? Where did this come from? Well, it's actually pretty simple if you do the following:

1. Take a deep breath.

2. Do not assume you will have to re-install your site, though many will automatically jump to this conclusion, it's not necessary to re-install a site for one error, even if the error is 3,000 lines long.

3. Say to yourself, I need to relax. I can figure this out, everything I need to know is right here in front of me.

4. If you are still anxious, get up and step away from your site for a moment, go do something else, come back to it when you've gotten used to the idea your site is down and the world is still continuing to exist.

5. Okay, are you calm? Good, then let's fix this type of error.

Take a look at it again, it says what we need to know, we just have to know how to decipher it:

Database query error

Query:
SELECT * FROM `ProfilesSettings` WHERE `IDMember` = '7'

Mysql error:
Table 'tbcsi1_bffdomaincom.ProfilesSettings' doesn't exist

That is the part that matters, the rest is just your server getting even with you for pissing it off and making it attempt to do things without the full instructions, that is all it's doing. Just telling you something is missing.

So, what is missing? Well, let's break it down further,

It says:

Mysql error:

Table 'tbcsi1_bffdomain.com.ProfileSettings' doesn't exist

Hmmm, what happened to them, they were there before I told it to drop them. Yes, I did this on purpose folks, it was to help make a point and to learn something for myself.

So, how do we get them in there? Where do they come from? Oh my god, my site has 2 million mods on it, how do I know where they came from?

In this instance and in all others, it's from the SQL files you uploaded. So, let's get ready to fix it.

Make sure you have the following tools to fix this error:

1. A computer

2. An internet connection

3. The login to your cPanel

4. Notepad++

***Item #4 is the most important, without it 1-3 are useless, believe me on this. If you don't have Notepad++ please go to sourceforge and get it, it's free and easy to use.

5. Your original dolphin 6.1.x install package sitting on your computer and copies of all your mods, in this instance and in most, you will need your Original Dolphin Package with the install folder. (You can even keep this folder on your server, just rename it to something else and you'll always have access to these files)

Now, get ready for the fun. Here's what I want you to do:

Open your dolphin package and go to:

dolphin/install/sql/install61.sql

Do not execute it, just open it up with Notepad++

Now, in this instance our table is missing 'ProfileSettings'

So, using the search function built into Notepad++, let's do a search for ProfileSettings (leave the apostrophes out, they don't matter)

Since we know we don't have the table and we want to create it, we need to look at how many times we find it and where we find it:

If you hit the Count Button on the Notepad++ search box you will see we have it 4 times. Uh oh! 4 times, now what do we do? How do we know we have the right one? Well, we will get to that next.

Since it says the table is missing we know we need to create one, in this instance, SQL is very simple, we're going to tell it to Create a table, so let's look for a command that tells it to do that. After hitting search we find this:

-- Table structure for table `ProfilesSettings`
--

CREATE TABLE `ProfilesSettings` (
`IDMember` int(10) NOT NULL default '0',
`BackgroundFilename` varchar(40) default NULL,
`BackgroundColor` varchar(60) default NULL,
`FontColor` varchar(60) default NULL,
`FontSize` varchar(60) default NULL,
`FontFamily` varchar(60) default NULL,
`Status` varchar(20) default NULL,
UNIQUE KEY `profile_id` (`IDMember`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Wow! It says to create the table. Great job, your about to fix your own site.

So now, highlight the text in that section until you get to the next part. Make sure you take the entire section of code, not just one or 2 lines. Some items will be hundreds if not thousands of lines. I have actually seen SQL queries that have gone over 80K lines, that is literally, 80k different queries in one. So make sure you take it all.

Now you get to create the table for real, go ahead and open your cPanel and navigate to the phpMyAdmin Section.

Are you there? I'm happy to wait for you.

**twittling thumbs... checking twitter... updating my profile on facebook... grabbing a kiss from my girl... teaching my daughter how to ride her bike...

Okay, glad to see you finally go to the phpMyAdmin section. Let's continue.

Now, look at the tabs along the top:

See the 2nd one, it says "SQL" on it?

Great, you have just passed the vision part of our test and can not go ahead and click on that SQL tab.

A new page will load, it will have a large white box taking up most of it.

Remember that section of SQL code we had you copy? Now you get to paste it in, right here in the white box. That's it. See how nice it looks in there? Awesome.

Now, look at the bottom right corner of that box.

Can you see the Go button?

That's it, do what your mind wants to do: Click the "Go" Button.

Your screen will reload and you will see this:

Your SQL query has been executed successfully (Query took 0.2511 sec)

and in the box below it:

-- Table structure for table `ProfilesSettings`

CREATE TABLE `ProfilesSettings` (

`IDMember` int( 10 ) NOT NULL default '0',
`BackgroundFilename` varchar( 40 ) default NULL ,
`BackgroundColor` varchar( 60 ) default NULL ,
`FontColor` varchar( 60 ) default NULL ,
`FontSize` varchar( 60 ) default NULL ,
`FontFamily` varchar( 60 ) default NULL ,
`Status` varchar( 20 ) default NULL ,
UNIQUE KEY `profile_id` ( `IDMember` )

) ENGINE = MYISAM DEFAULT CHARSET = utf8;

That's it. You can now go check your site and see that all is well again.

Please keep in mind that all SQL errors are just your server telling you that you've had a brainfart in the process of coding your site and forgot something. Not an impossible task and one that can be fixed.

Later on I will do another post to help you out with arguments that your server gets into with your coding. But for now, this will help you guys fix some of those sites and save you tons of time re-installing/remodding your sites.

Enjoy and Good Luck!

Comments

Newest

·Oldest

·Top

Please login to post a comment.

Technoman•27th of July 2009

Thanks alot for your explanations on fixing the 1 of many major sql errors ...

Well hopefully with your fix, dolphin aka boonex can you please fix these common errors in the next release!

Permalink•Reply•Plus•0 pluses

DeeEmm•27th of July 2009

Why not simply restore your database backup?

It's a lot less mucking about and can be done via the admin panel.

Permalink•Reply•Plus•0 pluses

mydatery•27th of July 2009

I would recommend that for those who have regular DB backups, this is in the instance of someone who has done a lot of modding to the site recently and won't have what they need in the back up either.

While I had fun typing this out long, it's really only about 4 steps:

1. Get the error

2. Open your Dolphin base package and find the install/sql files

3. Locate the missing table

4. Upload the missing table via phpMyAdmin/SQL

Permalink•Reply•Plus•0 pluses

mrden•30th of July 2009

Thanks mydatery. That is a big help. Could you spell out how to get my Video working? My site is inmylittleworld.com I can't seem to get video chat to work. Also,when I upload a video I get the approve but then it says undefined.
I would really appreciate any help you may be able to give. I have other problems but just want to get them done one at a time.
Thank you for your time.

Permalink•Reply•Plus•0 pluses

BeWytched•23rd of September 2009

Hey mydatery! Okay so I got like half way down your page and had to send you a comment to say THANK YOU FOR FINALLY EXPLAINING IT TO ME IN ENGLISH AND NOT COMPUTERIZE. lol I'm going to finish reading it now but just had to say that. I like the way you wait for us to do things one unrushed step at a time. If you have any ideas on how to make my web cam chat and the community have one sign in I'd be eternally grateful. Been searching and can't understand the instructions most times. Had a few see more

Permalink•Reply•Plus•0 pluses

dustybin•1st of March 2011

.
.
.

mydatery, thanks very much for that. I had a corrupted database table that had shed all its data and structure and no backup but dropping the table and following your instructions has just saved the day! Big thanks.

I appreciate you taking the time to explain this daunting yet easily solved issue. I spent ages looking for solutions that gave me the confidence to get all masterful on my database. You win!
Andrew.

Permalink•Reply•Plus•0 pluses

SQL Database Errors: How to fix missing tables!

Blogs

Help

Product

Company