select - Can't create table throught a view with function inside mysql -

i created 2 tables

create table `prova` ( `id`  int not null auto_increment , `text`  varchar(255) not null , primary key (`id`) ) ;  create table `prova2` ( `id2`  int not null auto_increment , `text2`  varchar(255) not null , primary key (`id2`) ) ;  insert prova (text) values ('ffffff'); 

a function select on table 1 , inserts row in table 2 if value of variable @test set 0:

create function `get_prova`()  returns int(11) begin  declare id_prova int ; declare test int ;       set @test = 1;     set @id_prova = (select id prova limit 1);      if (@test = 0)         insert prova2 (text2) values ('dddd');     end if;  return @id_prova;  end; 

then, create view calls function:

create view temp_prova  select  id,             text,              get_prova() prova prova 

i want create table 3 contains result of view:

create table zzz_prova  select * temp_prova; 

but when try create table zzz_prova error:

[sql]create table zzz_prova select * temp_prova; [err] 1746 - can't update table 'prova2' while 'zzz_prova' being created.

why error show up? thank you

what version of mysql running?

changes in mysql 5.6.2 (2011-04-11)

• incompatible change; replication: no longer possible issue create table ... select statement changes tables other table being created. such statement not executed , instead fails error.

  one consequence of change update may no longer used @ select portion of create table ... select.

  this means that, prior upgrading previous release, should rewrite create table ... select statements cause changes in other tables statements no longer so.

  this change has implications statement-based replication between mysql 5.6 (or later slave) , master running previous version of mysql. in such case, if create table ... select statement on master causes changes in other tables succeeds on master, statement nonetheless fails on slave, causing replication stop. keep happening, should either use row-based replication, or rewrite offending statement before running on master. (bug #11749792, bug #11745361, bug #39804, bug #55876)

  references: see bug #47899.

update

mysql 5.5:

mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.47    | +-----------+ 1 row in set (0.00 sec)  mysql> drop function if exists `f`; query ok, 0 rows affected (0.00 sec)  mysql> drop table if exists `t1`; query ok, 0 rows affected (0.00 sec)  mysql> drop table if exists `t2`; query ok, 0 rows affected (0.00 sec)  mysql> delimiter |  mysql> create function `f`()     -> returns int     -> begin     ->     insert `t2` values (1);     ->     return 1;     -> end| query ok, 0 rows affected (0.00 sec)  mysql> delimiter ;  mysql> create table `t2`(`c1` int); query ok, 0 rows affected (0.00 sec)  mysql> create table `t1` select `f`() `c1`; query ok, 1 row affected (0.00 sec) records: 1  duplicates: 0  warnings: 0  mysql> select `c1` `t1`; +------+ | c1   | +------+ |    1 | +------+ 1 row in set (0.00 sec)  mysql> select `c1` `t2`; +------+ | c1   | +------+ |    1 | +------+ 1 row in set (0.00 sec) 

mysql 5.6:

mysql> select version(); +-----------------+ | version()       | +-----------------+ | 5.6.25          | +-----------------+ 1 row in set (0.00 sec)  mysql> drop function if exists `f`; query ok, 0 rows affected, 1 warning (0.00 sec)  mysql> drop table if exists `t1`; query ok, 0 rows affected, 1 warning (0.00 sec)  mysql> drop table if exists `t2`; query ok, 0 rows affected, 1 warning (0.00 sec)  mysql> delimiter |  mysql> create function `f`()     -> returns int     -> begin     ->     insert `t2` values (1);     ->     return 1;     -> end| query ok, 0 rows affected (0.00 sec)  mysql> delimiter ;  mysql> create table `t2`(`c1` int); query ok, 0 rows affected (0.00 sec)  mysql> create table `t1` select `f`() `c1`; error 1746 (hy000): can't update table 't2' while 't1' being created.