Broken Database

I have an old website from 2012, so it was probably originally using CB 1.9.1 when it was built. The client came out of hibernation and finally wants to start using their website again.

I've maintained the site over the years and made all the Joomla and extension updates. But I ran into a problem upgrading sites from 1.9.1 to the 2.x versions of CB. I documented the process for fixing it - all the sites changed the fax field (ID 44) to terms fields - and manually fixed this in PHPMyAdmin for all the sites.

But I've come across something entirely different with the site I'm working on today. There were "duplicate" fields for the address fields - one would be "address" and the other would be "cb_address"... "city" and "cb_city", and so on. The problem is that the fields appear as columns in the comprofiler table, but NOT in the comprofiler_fields table.

Whenever I try to edit any of these address fields, I get an error:

Duplicate column name 'cb_state'

So I cannot edit any of these fields (the address, company, city, state, zipcode, phone, or website) without getting an error like the one above.

There is no data in any of the "cb_..." fields - only the fields without the "cb_" prefix are populated with user data.

I tried using all the Tools to clean everything up, but it didn't change anything - all green results.

How do I clean this mess up so I can edit the fields and get the full use out of this website again?

Thank you,
Ernie

The old sample data used to create the fields without the cb_ prefix which caused them to act like system fields when they weren't. This had been fixed in CB 2.x, but it shouldn't be adding those sample data fields on install as you'd of had to of run the sample data tool for them to be created again.

As for your issue you should be able to run the various database tools within CB > Tools to fix any missing table columns or rows. You can also just delete the cb_ prefixed fields then adjust the _comprofiler_fields and _comprofiler tables to prefix the non-prefixed fields to ensure they're acting as the newer sample data fields.

It's possible (but highly unlikely) that back in 2012 I ran the Sample Data tool to create the redundant fields.

But the problem now is that I've run the database tools - including the "Strict" user fields tool - to try to fix this and it's not fixed.

Are you suggesting that I rename the fields in the _comprofiler_fields table to the cb_ versions of the same names? I can't do that because I already have non-cb_ prefixed versions of those fields - those are the fields containing data in the _comprofiler table.

Is there another table that stores the cb_ versions of fields other than _comprofiler_fields? Or are those cb_ fields hard-coded somewhere?

I just don't understand how the error is being produced when those fields beginning with cb_ are not even present in the _comprofiler_fields table - they're only present as columns (all null) in the _comprofiler table.

... and the database Tools are not reconciling this.

Thank you,
Ernie

Does anyone have a solution for this?

Is there a graceful way to uninstall and reinstall so that the databases are fixed?

I really don't know enough about the dependencies to start hacking at the site.

Thank you,
Ernie

As Kyle suggested, (backup first of course), then delete the "duplicate" and empty column in the _comprofiler table.

You can then check inside the _comprofiler_fields table in the "columns" column that it's the corresponding one. It doesn't matter for cb if the database names are prefixed with cb_ or not. It matters that they match and are unique between _users and _comprofiler.

I didn't catch the part about deleting the columns in _comprofiler in Kyle's response. I'm sorry.

So it's safe to do that - I've never deleted a column from a table in PHPMySQL - I assume I'll have to write a query to delete the columns, right?

I had already deleted the cb_ fields from _comprofiler_fields, so that's clean. I just assumed that in the database tools, it would remove those fields from _comprofiler.

I will proceed with caution.

Thank you,
Ernie