Migrate with Views error

Yii 2.0 General Discussions
1

I’m using migrations and when I ran fresh command I had the following error:

SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table ‘bpm.etapa_percent_complete_periodo_avance’

The SQL being executed was: DROP TABLE etapa_percent_complete_periodo_avance

Where etapa_percent_complete_periodo_avance is a View instead of table. I don’t know if this is an error/bug or I’m using this tools incorrectly.

The migration code is:

[font="Courier New"]


  public function safeUp()

    {

        $this->execute(" 

            CREATE OR REPLACE 

            ALGORITHM = UNDEFINED 

            DEFINER = `root`@`localhost`   

            SQL SECURITY DEFINER                        

            VIEW `etapa_percent_complete_periodo_avance` AS

            SELECT 

                etapa_periodo_avance.detalle_pedido_id, 

                etapa_periodo_avance.etapa_id, 

                nombre, 

                fecha_inicio, 

                fecha_fin, 

                round(haber_pieza / debe_pieza * 100,2) as percent_complete

            FROM etapa_periodo_avance 

            JOIN etapa_pedido_pieza 

            ON etapa_periodo_avance.etapa_id = etapa_pedido_pieza.etapa_id

            AND etapa_periodo_avance.detalle_pedido_id = etapa_pedido_pieza.detalle_pedido_id

        ");        

    }

[/font]

and the complete error message and exception


[font="Courier New"]Exception 'yii\db\Exception' with message 'SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'bpm.etapa_percent_complete_periodo_avance'

The SQL being executed was: DROP TABLE `etapa_percent_complete_periodo_avance`'


in /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/db/Schema.php:595


Error Info:

Array

(

    [0] => 42S02

    [1] => 1051

    [2] => Unknown table 'bpm.etapa_percent_complete_periodo_avance'

)


Stack trace:

#0 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/db/Command.php(1004): yii\db\Schema->convertException(Object(PDOException), 'DROP TABLE `eta...')

#1 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/console/controllers/MigrateController.php(307): yii\db\Command->execute()

#2 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(465): yii\console\controllers\MigrateController->truncateDatabase()

#3 [internal function]: yii\console\controllers\BaseMigrateController->actionFresh()

#4 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

#5 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)

#6 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/console/Controller.php(135): yii\base\Controller->runAction('fresh', Array)

#7 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('fresh', Array)

#8 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/fresh', Array)

#9 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migrate/fresh', Array)

#10 /home/diego/proyectos/fundalum/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))

#11 /home/diego/proyectos/fundalum/yii(20): yii\base\Application->run()

#12 {main}[/font]

Thank you in advance, Diego

2

This seems to be a problem with the SQL itself rather than with the migration tool, since you are passing the raw SQL to the "execute" method.

Does the SQL work as expected with a command line or with some SQL tool?

3

softark, thank you for your response,

The SQL code rans ok using SQL tools and mysql cli. It even works with ./yii migrate command, the problem is when, I’m using ./yii migrate/fresh command or I’m trying to redo the migration.

Warm regards,

4

I see.

I think that "migrate/refresh" will call "down" or "safeDown" before calling "up" or "safeUp". Check your "down" or "safeDown".