MySQL: SHOW TABLES LIKE database.tablename doesn't work
Morbus Iff - July 25, 2008 - 02:43
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | duplicate |
Description
Consider a shared multisite like so:
<?php
$db_prefix = array(
'default' => 'cf_primary.primary_',
'users' => 'cf_shared.shared_',
'sessions' => 'cf_shared.shared_',
'role' => 'cf_shared.shared_',
'authmap' => 'cf_shared.shared_',
'sequences' => 'cf_shared.shared_',
);
?>And then, consider the code db_table_exists('cache_filter'). When run, it'll run the SQL SHOW TABLES LIKE 'cf_primary.primary_cache_filter' which is invalid for MySQL. What DOES work, on the other hand is SHOW TABLES FROM cf_primary LIKE 'cache_filter'. I propose that db_table_exists() becomes aware of a database name prefix (the stuff before a .). Note that the similar db_column_exists() DOES work already (SHOW COLUMNS FROM drupal_6.watchdog LIKE 'wid'; does what it's supposed to).
Comments from database experts?
#1
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
#2
Isn't the database-in-table-name thing a MySQL-specific feature?
#3
@Crell: Schemas in PostgreSQL work in a very similar manner.
See also: #294301: db_table_exists for multiple schemas database
#4
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).
#5
+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:
#6
SQL schemas are part of the SQL standard - seems like it would be useful to support this fully.
#7
What about something like this? I haven't tested on PostgreSQL, but findTables() uses a similar approach.
#8
What version is this patch for? Have you tested against 5 and 6 or is this 7 specific?
#9
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.
#10
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.
#11
The last submitted patch failed testing.
#12
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.
#13
The last submitted patch failed testing.
#14
You need to reroll your patch againt CVS/HEAD (drupal 7)
#15
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.
#16
Changing status.
#17
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.
#18
#19
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.
#20
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.
#21
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.
#22
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?