MySQL: SHOW TABLES LIKE database.tablename doesn't work

That's a problem.

There are/were other issues mentioning this:
#122336: Issue with db_table_exists function with shared user configurations and MySQL
#134308: installer.php incorrectly allows '.' in a DB table prefix

Isn't the database-in-table-name thing a MySQL-specific feature?

@Crell: Schemas in PostgreSQL work in a very similar manner.

See also: #294301: db_table_exists for multiple schemas database

OK, but is that coincidence or an actual SQL standard? If a standard, we should support it where possible. If a limited extension, we should probably try to avoid relying on it unless we are able to abstract it for use on other database engines (the way the query builders abstract multi-insert and merge, for instance).

+1 This is an issue for us as well. In Drupal 5.x when running update.php in a multi-site configuration with prefixing. We get the following errors:

• user warning: Table 'myprefix_cache_filter' already exists query: CREATE TABLE kenny_myprefix.myprefix_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/kenny/public_html/myprefix/includes/database.mysqli.inc on line 156.
• user warning: Table 'myprefix_cache_menu' already exists query: CREATE TABLE kenny_myprefix.myprefix_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/kenny/public_html/myprefix/includes/database.mysqli.inc on line 156.
• user warning: Table 'myprefix_cache_page' already exists query: CREATE TABLE kenny_myprefix.myprefix_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/kenny/public_html/myprefix/includes/database.mysqli.inc on line 156.
• user warning: Table 'myprefix_cache_content' already exists query: CREATE TABLE kenny_myprefix.myprefix_cache_content ( 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/kenny/public_html/myprefix/includes/database.mysqli.inc on line 156.

SQL schemas are part of the SQL standard - seems like it would be useful to support this fully.

What about something like this? I haven't tested on PostgreSQL, but findTables() uses a similar approach.

What version is this patch for? Have you tested against 5 and 6 or is this 7 specific?

We utilize multi-site with prefixed database names quite a bit for our clients. We have further discovered that not only is this an issue with update.php and install profiles but anywhere in code where db_table_exists() is called. For example, even adding fields to a CCK type throws this ugly warning as does any install or uninstall hook where the function is referenced. For this reason, we have created two patches for the purposes of multi-site installation which cover mysql and mysqli connections.

Essentially, we are checking for the existence of periods in a prefix (indicating a call to another database) and we are using the native mysql functions to perform the correct query for MySQL which is "SHOW TABLES FROM [dbname] LIKE [table]" as the original issue creator points out.

We feel that the widespread use of Drupal 5 calls for this patch and encourage developers who work in Drupal 6 and 7 to address this accordingly to version.

These patches address the files: includes/database.mysqli.inc and includes/database.mysql.inc

The development of this patch was sponsored by CommonPlaces E-Solutions LLC.

What version is this patch for? Have you tested against 5 and 6 or is this 7 specific?

My guess is 7.x, because development takes place there and if needed things are back ported.

Sorry for that. Our apologies. I created the patch in the wrong structure. I have re-attached the patches here and they have been created using the Submitting Patches guidelines in the handbook.

You need to reroll your patch againt CVS/HEAD (drupal 7)

The issue is that the patch attached is for Drupal 5. That was my error as I thought I had been clear in the issue comments. I have created an issue for 5, #335450: Using database prefix names fails in MySQL database. Multi-site configurations., and have requested that Drupal 7 developers look at this patch and roll it for the proper version so it may be back-ported. Thank you.

I have attached the files for your review below suffixed with D5 so the auto-tester doesn't pick them up.

Changing status.

You would be the D7 developer, unless someone else has the itch to scratch this will never be tested in D7, and thus never back ported to 6 and 5.

As far as I understand this issue, we are still in CNR, for the patch in #7, that suggested a clever way to solve that issue for both MySQL and PostgreSQL.

About #7:
- we don't use aliases: please use count() instead of sizeof()
- replace the COUNT(*) queries, and replace it with by a db_query_range() on table

Otherwise, looks very good.

This patch addresses the points raised in #20 and adds some tests.

I didn't touch the Sqlite code. According to this, SQLite supports information schema almost but not completely.

We have come up with a more generic solution in #342503: Schema function findTables fails on postgres - wrong SQL. Could you help us test and validate it?

How about we port the d5 version to d6. In my opinion, it really makes sense to have two separate issues for D5 and D6 vs D7 since the generic solution for D7 does not apply at all with all of the new database stuff in D7. However the D5 and D6 patches are practically identical.

Until someone can tell me where to put these that is better, I am putting them here for reference.

This issue manifested itself as #615566: warning: Invalid argument supplied for foreach() in content.module on line 1022. on a Drupal 6 site with MySQL 5.1.41, and we tracked it down to db_table_exists failing when using a database name prefix . I'm re-opening this issue as we still need to fix this in Drupal 6.

We tested the MySQL version of the patch in #24 and it resolved our problems (per above referenced issue) on a site with shared tables.

Confirming this is an issue with D6.

db_table_exists failing when using a database name prefix renders CCK (and I'm guessing other modules) useless.

Subscribing

The patch by jen_t_man for MySQL in #24 is working. But this problem seems to arise only for the CCK Content module. All other's work fine. Also, even after applying this patch, creating a new content type and then fields in it (the point at which content_type_xxx table is created) is not smooth. The new table gets created, but fields in it are not.

@avadhutp, it doesn't only affect CCK. A lot of stuff can fail, most notably system_update_* functions. See the list of functions that use db_table_exists here:

http://api.drupal.org/api/function/db_table_exists/6

Regardless, the function db_table_exists returns an incorrect value for perfectly valid installs of Drupal so it's definitely a bug.

This patch fixes the bug identified in this issue for me. If you are still having problems with CCK after successfully applying this patch, can I ask the value of your $db_prefix? I've noticed that this patch only works if the prefix contains the characters a-z, 0-9 and underscore. This means it will fail if it contains uppercase letters or characters other than those mentioned. This might be a limitation of the patch because MySQL identifiers are case-sensitive on *nix systems.

@dazweeja: Hmm. Tried updating and you are correct. My shared tables prefix is, predictably, "shared." Why do user, sessions, roles, users_roles, etc. tables work properly when in shared using the current 6.19 version of db_table_exists()? Any clues as to whether this happens only with table names that start with certain strings, for example, in the case of CCK, content_***? Also, I am using MySQL. Is the behavior different in PostGreSQL?

@avadhutp, most of the problems with CCK relating to this bug are due to the schema for content not being returned correctly. This is because halfway through 'content_schema' in 'content.install' it hits these lines and returns prematurely:

if (!db_table_exists('content_node_field') || !db_table_exists('content_node_field_instance')) {
    return $schema;
}

This problem should be solved by applying the patch in this thread. If it isn't, it might require more drastic measures like deleting and re-creating existing content types (after applying this patch). The Export feature of CCK should help with this. There's other things to check here (ignore my last comment in that thread though if you've applied the patch): #615566: warning: Invalid argument supplied for foreach() in content.module on line 1022.

Probably best to discuss these issues there and leave this thread for this specific problem, which I think the patch in #24 solves. As I said, my issue with the patch is that it doesn't cover all valid Drupal installations (such as uppercase letters and possibly other legal characters in db prefixes). I'm not sure if it's worth spending any time on though if it has no chance of getting into core. The patch as it is should fix this problem in almost all cases.

The patch in #24 is working. Applied Patch, Cleared Cache and it works! Thanks!

I concur. Tried the patch #24 with 6.19, Ubuntu 10.04, & MySQL 5.3.2. Works fine. Should this be applied to CCK core before the next release?

Have the same problem.
Tried from #24 and #31 - didn't work.
Drupal 6.20. settings.php:

$db_url = 'mysql://ts_user:xxx@localhost/ts_main';
$db_prefix = array(
'default' => 'ts_main.', 
'users' => 'ts_common.',
'sessions' => 'ts_common.',
'authmap' => 'ts_common.',
'users_roles' => 'ts_common.',
'profile_fields' => 'ts_common.',
'profile_values' => 'ts_common.',
'locales_source' => 'ts_common.',
'locales_target' => 'ts_common.',

Is there other things I can try?

Bump and my take on solving the issue in Drupal 6.x. This patch doesn't attempt to validate mysql identifiers like the regex solution, only split database prefix from table name by looking at the period position.

#35 is much better.

Same problem here. Patch #35 worked for me, but I guess it would fail when there are more than 1 dot in the prefix (eg: `table name` . `myprefix.table`). A dot in the table name is perfectly legal in MySQL.

The fix for tables that have dots is really simple. I haven't tested this, just grabbed #35 and added the 2 to explode. A period is not allowed in a database name (in MYSQL at least) so this shouldn't be a problem.

Let's put it under need review.

@j_ten_man, just for reference, I believe periods have been allowed in database names since 2006:

"Before MySQL 5.1.6, there are some limitations on the characters that can be used in identifiers for database objects that correspond to file system objects. For example, path name separator characters are not permitted, and “.” is not permitted because it begins the extension for table files.

As of MySQL 5.1.6, any character is legal in database or table identifiers except ASCII NUL (0x00)"

http://dev.mysql.com/doc/refman/5.1/en/identifier-mapping.html

Difficult then to determine when the dot is used as a part of the [db|table] name or as a separator. If we take "one.two.three_" as an example prefix, what about the following behavior:

- look for a table named "one.two.three_[table_name]" in the current DB
- if that fails, gather the list of DBs the user has access to (like with SHOW DATABASES) and try to match one of these with the prefix. Here, for example, a DB named "one" or "one.two" would match.
- fail is there is no table or if there is more than one DB that matches.

Just woke up. I think we can actually still do this but another approach. We have the table name so we can figure out what the prefix for the table was that was added. Anyways, I'll spend a little time on this later and throw a patch up if someone else doesn't beat me to it.

Now that I'm awake a little more and thought about this and read #41 more carefully, this is quite ugly. Adds a bunch of overhead just to handle this ugly case.

Should we handle '.' in database names to fix this? The Drupal 7 fix doesn't handle this: http://drupal.org/node/342503. Once again I'll test this later today but it doesn't look like it would just looking at the patch.

So apparently a dot in a table is not allowed currently. db_escape_table() removes any '.' in a table name. Unless that is changed, I think that we should go with #35 as it duplicates the functionality of what is in D7. #35 works for me. For documentation purposes, here are the tests I ran against #35 and results (note that all tables actually exist):

$db_prefix = array(
  'test.table' => 'test.database.',
  'test_table' => 'test.database.',
  'test.table2' => 'test_database.',
  'test_table2' => 'test_database.',
);

DB: test.database, TABLE: test.table, FUNCTION CALL: db_table_exists('test.table');, Result: Does not exist
DB: test.database, TABLE: test_table, FUNCTION CALL: db_table_exists('test_table');, Result: Does not exist
DB: test_database, TABLE: test.table2, FUNCTION CALL: db_table_exists('test.table2');, Result: Does not exist
DB: test_database, TABLE: test_table, FUNCTION CALL: db_table_exists('test_table2');, Result: Exists

Right, if db_escape_table() does not allow using databases names with a dot then #35 will work. It could use some code comments though.

If we know that table names can't have dots, does that mean we can still handle the 'dots in database names' case by modifying #35:

$query = "SHOW TABLES IN ".$table[0]." LIKE '".$table[1]."'";

becomes:

$table_name = array_pop($table);
$db_name = implode('.', $table);
$query = "SHOW TABLES IN ".$db_name." LIKE '".$table_name."'";

Maybe not necessary but it's hard to anticipate how someone might name their database.

Marked #134308: installer.php incorrectly allows '.' in a DB table prefix as a duplicate of this issue.

I ran into this issue just recently and it was happening intermittently (ie: anonymous users could post and successfully save CCK fields while logged in users were not able to save fields) and it was a bit weird.

- Drupal 6.24
- CiviCRM 3.4.8
- Typical sharing of data with Views 2.x

Applied the patch in #38 and the issue has been fixed.