Database error with (I think) custom data

[tvaughan]

Having tried installing CiviCRM on NearlyFreeSpeech, I eventually gave up and have now successfully installed it on a VPS with Bytemark (very good, btw).

All seemed to be working until I tried to view a contact record and got this error:

Database Error Code: Table 'databasename.civicrm_value_1_General_extra_info' doesn't exist, 1146

Error details:

Code: [Select]

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] =>
SELECT id
FROM   civicrm_value_1_General_extra_info
WHERE  entity_id = 139
 [nativecode=1146 ** Table 'databasename.civicrm_value_1_General_extra_info' doesn't exist]
    [type] => DB_Error
    [user_info] =>
SELECT id
FROM   civicrm_value_1_General_extra_info
WHERE  entity_id = 139
 [nativecode=1146 ** Table 'databasename.civicrm_value_1_General_extra_info' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT id
FROM   civicrm_value_1_General_extra_info
WHERE  entity_id = 139
 [nativecode=1146 ** Table 'databasename.civicrm_value_1_General_extra_info' doesn't exist]"]
)

I'm guessing this is to do with custom data, although the same database is working ok on XAMPP, oddly.  Any suggestions?[/code]

[Dave Greenberg]

Did you copy / move this DB from one server to another? Are you using a supported version of MySQL (5.x)? The referenced (missing) table is one that would have been created when a custom data group ("General extra info") was created - so not sure why it's missing now??

[tvaughan]

Thanks for the reply - I was just checking that and the table is definitely there, although it's named in all lower case characters.  Is this a case-sensitive thing (i.e. General_extra_info vs. general_extra_info?  I can see that custom data entry in the CiviCRM admin pages and can add fields to it fine.

I have moved the DB from MySQL on XAMPP to MySQL on LAMP (Ubuntu Gutsy) - character encoding problem?

[tvaughan]

Just renamed the table to General_extra_info and it seems to work ok now?!  How can I stop this problem with case happening?

(Edit: still happening with other mixed-case tables - I can rename them all but I'd rather solve the problem rather than the symptoms)

[Dave Greenberg]

It sounds like this might be a PHP version issue. The code which creates the custom field tables takes the custom group title and does this:

strtolower( CRM_Utils_String::munge( $group->title, '_', 32 ) );

... so all custom field value tables should be lower case.

What version of PHP are you running on the XAMPP server (which I assume is where these tables where initially created)?

... aside from that, I would try creating a new custom data group on your "production" Ubuntu site - and check if that's lower-casing things properly. If so, you can probably just lower case the other tablenames and move on.

[tvaughan]

The database is actually one that has gone from 1.7 all the way through to 2.0 from:

1.7 on LAMP (PHP 5.2.5, MySQL 4.1.22)
2.0 on XAMPP (PHP 5.2.5, MySQL 5.0.51a)
2.0 on LAMP (PHP 5.2.3-1, MySQL 5.0.45) <-- current version with problem

The problem is that the table names are all in lower-case, as intended.  CiviCRM seems to be looking for case-sensitive names so I actually end up having to upper-case the relevant bits of the table names to fix the problem.

[Chris Burgess]

Warning: contains speculation!

If your XAMMP install is OSX (I don't know if they package XAPMP for anything else?) then this may be an issue with OSX+MySQL and the (case insensitive) filesystem HFS+. I've seen MyISAM tables on HFS+ be accessible via crAzyCaSE names without any error, which sure seems handy enough at the time (ie, when working with People Who Love To Capitalise) but also means you can't do certain things (like change the table name to lowercase: error, table already exists) and also makes stuff break when you upload to the production system.

It was a few years back I observed this, but might be the same issue for you - HFS+ would explain why it DOESN'T give an error on XAMPP. I don't know if this affects current MySQL or MySQL with InnoDB.

It might also be that a Windows filesystem is case insensitive and produces the same symptoms, if you have XAMPP on Windows.

[tvaughan]

This was actually XAMPP on Windows which I believe is case-insensitive by default, and it was on Windows that I did the DB upgrade.  I'm wondering whether the upgrade script on Windows creates case-sensitive table names for custom data?

[Donald Lobo]

i just checked the upgrade script, we do not lower case the table names when creating a new custom table in the upgrade script. However we do store a valid case sensitive table name. We will fix this anomaly in the upgrade script in the next release

However, things should still work if the copy etc respect the case of the names being copied. So not sure what part of the process / which OS caused the table names to be lower cased. You might want to trace that and fix that

lobo

 

[tvaughan]

Thanks, I'll check.  Most likely it's a problem with XAMPP on Windows.  If I had done the upgrade on the production LAMP server I'm sure this problem wouldn't have occurred.

Thanks for the help!

[Deepak Srivastava]

Filed an issue here - http://issues.civicrm.org/jira/browse/CRM-3018