Schema introspection on Oracle is failing when user is 'system'

mondrake commented 3 years ago

Bug Report

Q	A
BC Break	yes/no
Version	3.0.0

Summary

Schema introspection in Oracle failing

Current behaviour

When executing createSchema() on an Oracle database and the user is 'system', I get

Unknown database type aq$_subscribers requested, Doctrine\DBAL\Platforms\OraclePlatform may not support it.

How to reproduce

See above.

Expected behaviour

The method should not fail and an introspected schema object should be returned.

morozov commented 3 years ago

@mondrake you'll need to provide a more specific way to reproduce this. I haven't seen this issue before.

mondrake commented 3 years ago

OK it was my mistake I was trying to introspect the "system" schema, I was not logging in the relevant user/schema. In any case I think a better error message should be thrown (e.g. "Introspecting the 'system' schema is not allowed").

Thanks

morozov commented 3 years ago

@mondrake if you want this issue to be addressed, please provide the steps to reproduce.

mondrake commented 3 years ago

Steps to reproduce, just in case:

1) in a test environment on GitHub using the oracle service with image wnameless/oracle-xe-11g-r2

2) login to oracle with system user, $tmpConnection = DriverManager::getConnection(["url" => "oci8://system:oracle@0.0.0.0:1521/XE"]);

3) invoke the createSchema method

$schemaManager = $tmpConnection->getSchemaManager();
$schema = $schemaManager->createSchema();

morozov commented 3 years ago

Indeed, the data type of AQ$_QUEUES.SUBSCRIBERS is AQ$_SUBSCRIBERS:

SELECT dbms_metadata.get_ddl('TABLE', 'AQ$_QUEUES') FROM dual;

  CREATE TABLE "SYSTEM"."AQ$_QUEUES" 
   (    "OID" RAW(16), 
    "EVENTID" NUMBER NOT NULL ENABLE, 
    "NAME" VARCHAR2(30) NOT NULL ENABLE, 
    "TABLE_OBJNO" NUMBER NOT NULL ENABLE, 
    "USAGE" NUMBER NOT NULL ENABLE, 
    "ENABLE_FLAG" NUMBER NOT NULL ENABLE, 
    "MAX_RETRIES" NUMBER, 
    "RETRY_DELAY" NUMBER, 
    "PROPERTIES" NUMBER, 
    "RET_TIME" NUMBER, 
    "QUEUE_COMMENT" VARCHAR2(2000), 
    "SUBSCRIBERS" "SYS"."AQ$_SUBSCRIBERS" , 
    "MEMORY_THRESHOLD" NUMBER, 
    "SERVICE_NAME" VARCHAR2(64), 
    "NETWORK_NAME" VARCHAR2(256), 
     CONSTRAINT "AQ$_QUEUES_PRIMARY" PRIMARY KEY ("OID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
     CONSTRAINT "AQ$_QUEUES_CHECK" UNIQUE ("NAME", "TABLE_OBJNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

Not sure what this type is and how to handle it otherwise.

doctrine / dbal