By geddon on

Having a problem with multiple sites. When I duplicate one of my databases I get the following errors:

    * user warning: Table 'cache_filter' already exists query: CREATE TABLE geddon_buildagod.cache_filter ( cid varchar(255) NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/geddon/public_html/includes/database.mysql.inc on line 172.
    * user warning: Table 'cache_menu' already exists query: CREATE TABLE geddon_buildagod.cache_menu ( cid varchar(255) NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/geddon/public_html/includes/database.mysql.inc on line 172.
    * user warning: Table 'cache_page' already exists query: CREATE TABLE geddon_buildagod.cache_page ( cid varchar(255) BINARY NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/geddon/public_html/includes/database.mysql.inc on line 172.

This occurs on all the sites when I attempt to update.php and when viewing the module list in the admin pane. This does not happen on the original site.

I've read elsewhere that deleting these tables might be the solution, however, Drupal simply replaces the tables and gives the previous error.

⋅ Categories: Drupal 5.x

Comments

apodo’s picture

apodo commented

I had the same problem so investigated the code and found the problem was the $db_prefix variable in settings.php file for my sites. As you are also running multiple sites I am guessing that you have a similar setup to mine.

This is an example of how the settings.php files looked for my sites:

$db_url = 'mysql://username:password@localhost/sitedb';
$db_prefix = array(
        'default'        => 'sitedb.',       
        'users'          => 'drupal.',    
        'sequences'      => 'drupal.',
        'profile_fields' => 'drupal.',
        'profile_values' => 'drupal.',
);

"sitedb" is the site's own database while "drupal" is a shared database containing only the tables listed above so that all sites would use the same user information.

The problem was that the code used by the update.php page to check whether the cache tables exists does not work with a database name before the table name. It called the MySQL server to look for a table with the literal name "sitedb.cache_filter" and so this check failed.

Unfortunately a prefix is required so that each site saves it owns unique values in the sequences table. The only alternative to specifying the database name in this manner is to use a prefix to the table names that are unique to each database, but this is not ideal for my purposes.

It is the "cache_filter" table whose existence is checked, and as caching does not require the sequences table, the solution is simply to remove the prefix from this one table:

$db_url = 'mysql://username:password@localhost/sitedb';
$db_prefix = array(
        'default'        => 'sitedb.',       
        'users'          => 'drupal.',    
        'sequences'      => 'drupal.',
        'profile_fields' => 'drupal.',
        'profile_values' => 'drupal.',
        'cache_filter' => '',
);

It is annoying to have to do that, and the error is harmless anyway, but at least there is a workaround.

Michael.

theorichel’s picture

theorichel commented

Having the same problem and inspired by the above I simply pasted the text-file from the Drupal database dir with the MYSQL-field definitions in the SQL box of PHPMyadmin and clicked start and voila.

What this error says is that it is looking for a db-definition without prefixes.