Description:
This problem is described in detail here: https://forums.oracle.com/forums/thread.jspa?messageID=9823046� . In short, the driver sends the following commands to the mysql server when attempting to create a materialized view:
159531 Query SET NAMES latin1
159531 Query SET character_set_results = NULL
159531 Query SET SQL_AUTO_IS_NULL = 0
159531 Query set autocommit=0
159531 Query select database()
159531 Query COMMIT
159531 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
159531 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'lims' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) AND TABLE_NAME LIKE 'test_link'
159531 Query SHOW COLUMNS FROM `lims`.`test_link`
159531 Query SELECT `test_id`,`data` FROM `lims`.`test_link` LIMIT 0
159531 Query set @@sql_select_limit=1
159531 Query SELECT A1.`test_id`,A1.`data` FROM `test_link` A1
159531 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'lims' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) AND TABLE_NAME LIKE 'test'
159531 Query SHOW COLUMNS FROM `lims`.`test`
159531 Query SELECT `test_id` FROM `lims`.`test` LIMIT 0
159531 Query SELECT A1.`test_id` FROM `test` A1
159531 Query COMMIT
when creating just a simple view, the command sequence looks like this:
159636 Query SET NAMES latin1
159636 Query SET character_set_results = NULL
159636 Query SET SQL_AUTO_IS_NULL = 0
159636 Query set autocommit=0
159636 Query select database()
159636 Query COMMIT
159636 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
159636 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'lims' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) AND TABLE_NAME LIKE 'test_link'
159636 Query SHOW COLUMNS FROM `lims`.`test_link`
159636 Query SELECT `test_id`,`data` FROM `lims`.`test_link` LIMIT 0
159636 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'lims' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) AND TABLE_NAME LIKE 'test'
159636 Query SHOW COLUMNS FROM `lims`.`test`
159636 Query SELECT `test_id`,`num`,`string`,`fl`,`dbl`,`blb` FROM `lims`.`test` LIMIT 0
159636 Query COMMIT
How to repeat:
On mysql side:
create table test(test_id int primary key auto_increment, string varchar(255));
insert into test values(null, "first"), (null, "second");
create table test_link (test_id int, data varchar(255));
insert into test_link select test_id, 'same old, same old' from test;
on Oracle side with database link set-up as 'mysql':
create materialized view test as select "test_id", "string" as name, "data" from "test"@mysql inner join "test_link"@mysql using ("test_id");
