Regression in 2.0.11 - haveInDatabase() no longer works with tables that don't have a primary key

[n8whnp]

After upgrading from 2.0.10.1 we noticed a number of failing tests because haveInDatabase is now throwing an exception when trying to insert data into a table without a primary key. It is now throwing the error:

Exception: Table exampletable is not valid or doesn't have no primary key

This error appears to be introduced in PR: #1727.

[seggen-ibuildings]

Also, inserting data into a table with a database prefix doesn't work either. For example: $I->haveInDatabase('testdb.exampletable', array(...));

$this->driver->getPrimaryColumn($table) will fail with:
PDOException: SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected

[verschoof]

We have the same issue with a table that doesn't have a primary key.

[akireikin]

I have this issue with a regular table (without prefix, with pk). It seems that:

SHOW KEYS FROM category WHERE Key_name = "PRIMARY"

called from Codeception\Lib\Driver\Db::getPrimaryColumn() does not work for postgres. As a temp workaround, we can override it in Codeception\Lib\Driver\PostgreSqllike it is done for Sqlite.

Relates to #1846

[DavertMik]

fixed with #1846

[n8whnp]

@DavertMik does this fix the issue with MySQL database tables that simply do not have a primary key? That was the initial report here. I don't see anything in the commit that resolves that issue.

[josh7weaver]

I am using a MySql table without a primary key and am seeing the same error mentioned above.

[vgilevich]

Same for me - still is not working for table with primary key for 2 fields.

[n8whnp]

Just so we are clear since it looks like lots of different issues getting mixed together, the basic problem for us remains that we have tables w/o primary keys we need to insert data into, that is no longer possible due to the reported regression.

[Naktibalda]

Why would you have a table without a primary key?
I consider it being a database design issue.

I will implement support for multi-column primary keys,
it could be re-used to use include all data columns for tables without primary key.

PostgreSQL way: https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
Sqlite way: http://www.sqlite.org/pragma.html#pragma_table_info

[n8whnp]

@Naktibalda I work with legacy software, we are working to improve the schema, but part of that is ensuring good test coverage first ;)

[Naktibalda]

Oracle: http://stackoverflow.com/a/9016712/366348
MSSQL:http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query

[Naktibalda]

I solved all primary key issues.