Issue: pwiz not iterating columns for the correct database
Author: caelx
State: closed
Created: 2014-04-25 01:48:16

We have two databases with tables that have the same name, this can sometimes cause pwiz to fail when inspecting the columns because the database is not specified.

This is what causes the issue:

``` python
    def get_columns(self, table):
        pk_col = self.get_primary_key(table)

        # Get basic metadata about columns. Amazingly, this is the same query
        # as we use with postgresql.
        curs = self.conn.execute_sql("""
SELECT column_name, is_nullable, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name=%s""", (table,))
        columns = curs.fetchall()
```

To fix this issue, you'll need to pass the name the database in for the table_schema column.

``` python
    def get_columns(self, table):
        pk_col = self.get_primary_key(table)

        # Get basic metadata about columns. Amazingly, this is the same query
        # as we use with postgresql.
        curs = self.conn.execute_sql("""
SELECT column_name, is_nullable, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name=%s
AND table_schema = DATABASE()""", (table,))
        columns = curs.fetchall()
```

We're using mysql, but I imagine this can affect postgres as well.


I'm not very familiar with MySQL's handling of `table_schema`. What is the effect of saying `table_schema = DATABASE()`?


Also, fwiw, I just pushed a rewrite of pwiz (183cf77cb9446d428f12612c8a0e47a5279a0fcc), so any changes will be against that code.


the table_schema is the database that the table comes from.  I'm not sure with how postgres handles the connection, but when you query information schema it's not restricted to the database that you are currently using, which is why there is a need to add table_schema to the where clause.

Here's an example:

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> describe test1.test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> describe test2.test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id2   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT column_name, is_nullable, data_type, character_maximum_length
    ->             FROM information_schema.columns
    ->             WHERE table_name='test_table';
+-------------+-------------+-----------+--------------------------+
| column_name | is_nullable | data_type | character_maximum_length |
+-------------+-------------+-----------+--------------------------+
| id1         | NO          | int       |                     NULL |
| id2         | NO          | int       |                     NULL |
+-------------+-------------+-----------+--------------------------+
2 rows in set (0.00 sec)

mysql> use test1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT column_name, is_nullable, data_type, character_maximum_length
    ->             FROM information_schema.columns
    ->             WHERE table_name='test_table';
+-------------+-------------+-----------+--------------------------+
| column_name | is_nullable | data_type | character_maximum_length |
+-------------+-------------+-----------+--------------------------+
| id1         | NO          | int       |                     NULL |
| id2         | NO          | int       |                     NULL |
+-------------+-------------+-----------+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT column_name, is_nullable, data_type, character_maximum_length
    ->             FROM information_schema.columns
    ->             WHERE table_name='test_table'
    ->             AND table_schema=DATABASE();
+-------------+-------------+-----------+--------------------------+
| column_name | is_nullable | data_type | character_maximum_length |
+-------------+-------------+-----------+--------------------------+
| id1         | NO          | int       |                     NULL |
+-------------+-------------+-----------+--------------------------+
1 row in set (0.00 sec)

mysql>
```