database error on join

Mysql error: Unknown column 'status' in 'field list'

Here is a link to the contents of the email I am getting about the error.

error report

We are not able to get new members or create another profile ourselves.
Please help!

Check your fields builder.

It appears you have a incomplete field setup after the You are field.

Check your join form for an item that was not completed; just guessing here.

Anyway, the error is a blank field label:

`` = '',
`LangID` = 1
Mysql error: Unknown column '' in 'field list'

Maybe the mySql people can shed some light on this.  I will go do a search in the meantime.

Oh, Dean answered before my post. LOL.

I also see that I can't access many files even as logged in as admin. I checked my status and it shows unconfirmed. I changed it to active and saved, but it reverts to unconfirmed. I'm at a loss.

I assumed the same Deano, I moved the fields below the you_are to inactive items and still have the same error.

Yes, this has happened to all members. None will activate...

It seems you have defined a field in either the profile table or sys_profile_fields that does not exist in one of these tables, causing an update to a not existing field. Check for differences between the two.

Ps. Please change your passwords, as they are in the error log

could it be that you dont have a field called 'status' but instead you have a field called 'Status'? Capitalization can be a bummer and throw things for a big loop.

EDIT:

or vice versa

 I do appreciate your help.
This confuses me to no end.

I see that in Sys_profile_fields, Status is capitalized, but in profiles it is not.

If that could cause this, how would I fix it and how did this happen?

did you by chance try to add a new profile field called status? I just checked my fresh install and there is no field called status in the sys_profile_fields table. The problem could be that you added a field using the profile builder called status and itr is acting up with the existing Status. Just my guess

also check what GeekGirl was saying. You are calling for a field with no name in your sql query - which of course will not work at all.

 Well that was a mistake...
I removed the new block in profile fields builder named status and now the entire site is off limits.
I created a simple Html block and names it status and the site is back online with the same restrictions again.
Back where I started at least.

I should slow down...

I did look for unnamed fields and found none.

Moving the field to inactive will not be enough. Even tho they do not show on the join form, they are still in the database. One of them is damaged and must be deleted.

And if the problem occurred because you were trying to create a field named status, then that would cause a problem. Status is a reserved field name that dolphin already uses. You can't create a field with that name.

 Thank you Deano, I will look into that now

Okay, so still where we started except with the additional problem of not having the ability to activate members. The database status in profiles shows active, but on edit profile through the admin panel it reverts to unconfirmed and members are restricted accordingly.
 We still can not join as a new member either.

I need to see a export of the current structure of these two database tables.

Profiles
sys_profile_fields

You can obtain a database export with phpMyAdmin, but only the structure is needed. Not the data.

Attach the structure data here along with the current database error if any as that may also be different than what you originally posted.

Actually. The full export on sys_profile_fields.

So structure only on Profiles and structure and data on sys_profile_fields

Got that problem tackled, all existing members can access everything now.

Still have an error on submitting join.
Mysql error: Column 'status' specified twice

I only see one instance in the database. There is a new block in the profile fields builder names status, but if I delete that we can't access the site at all.

Either PhpMyAdmin is confused or I am, I'm betting it's me. Ideas from here?

Deano, if you still need the database info I will get it to you pronto, just let me know.

 Yes. Still need them. You have to get rid of the duplicate, but the fields builder may be haveing problems dealing with it. There is suppose to be one on the profiles table with a capitol S as in Status. Not status.

the table sys_profile_fields is also suppose to only have one. The one with the capitol S at the front of the table. You may have to remove the extra by hand rather than use the fields builder and then clear the cache by hand.

@deanos:

i checked my fresh install and i dont have a status field in the sys_profile_fields table. is it just me? if it isnt supposed to be there, could he just use phpmyadmin and delete that field?

Correct. I am aware of that. There is suppose to be a status field. a Capitol S status field. In other words. Status should be there but not status

Anyhow, thats what i already told him. The lower case version has to be removed. The proper case version is the only one that is suppose to be there. In both the Profiles and sys_profile_fields tables.

Boy this has turned into an ordeal. Thank you for putting up with my inexperience with databases.
We have managed to get the join form to work properly now, but when we did that we can not activate members again.
I renamed the Status in the profiles table and made it capitalized.
Below are links to the tables you requested.

sys_profile_fields.sql

Profiles.sql

@deanos:

i have no field called status at all. with cap or lowercase  s in my sys_profile_fields table is what i meant. so should he remove from that table using phpmyadmin. i checked 3 different installs.

here is mine.

CREATE TABLE `sys_profile_fields` (
  `ID` smallint(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `Type` enum('text','html_area','area','pass','date','select_one','select_set','num','range','bool','system','block') NOT NULL DEFAULT 'text',
  `Control` enum('select','checkbox','radio') DEFAULT NULL COMMENT 'input element for selectors',
  `Extra` text NOT NULL,
  `Min` float DEFAULT NULL,
  `Max` float DEFAULT NULL,
  `Values` text NOT NULL,
  `UseLKey` enum('LKey','LKey2','LKey3') NOT NULL DEFAULT 'LKey',
  `Check` text NOT NULL,
  `Unique` tinyint(1) NOT NULL DEFAULT '0',
  `Default` text NOT NULL,
  `Mandatory` tinyint(1) NOT NULL DEFAULT '0',
  `Deletable` tinyint(1) NOT NULL DEFAULT '1',
  `JoinPage` int(10) unsigned NOT NULL DEFAULT '0',
  `JoinBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `JoinOrder` float DEFAULT NULL,
  `EditOwnBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `EditOwnOrder` float DEFAULT NULL,
  `EditAdmBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `EditAdmOrder` float DEFAULT NULL,
  `EditModBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `EditModOrder` float DEFAULT NULL,
  `ViewMembBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `ViewMembOrder` float DEFAULT NULL,
  `ViewAdmBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `ViewAdmOrder` float DEFAULT NULL,
  `ViewModBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `ViewModOrder` float DEFAULT NULL,
  `ViewVisBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `ViewVisOrder` float DEFAULT NULL,
  `SearchParams` text NOT NULL,
  `SearchSimpleBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `SearchSimpleOrder` float DEFAULT NULL,
  `SearchQuickBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `SearchQuickOrder` float DEFAULT NULL,
  `SearchAdvBlock` int(10) unsigned NOT NULL DEFAULT '0',
  `SearchAdvOrder` float DEFAULT NULL,
  `MatchField` int(10) unsigned NOT NULL DEFAULT '0',
  `MatchPercent` tinyint(7) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Not sure. It's in my install. So i cannot confirm if it should be removed. I am only concerned with the invalid lowercase version.

Well, that's really odd. The lower case was there again, I changed it back to upper case and all is well as of now.
No idea what would have done all this, just hope it doesn't happen again...
Again, thank you for all your incredible patience and help walking me through it today.

I just took the biggest deep breath...

A little side note on this.

NEVER play with your live site.

ALWAYS use a development site to tinker with things to avoid these kind of heart attacks in the future.

I never install a module, code changes, or CSS changes on my live site 1st.  I always do it on my development site 1st.

I have learned that lesson.

I changed the lower case field to upper case and all is well.

Good advice Steve...