MySQL Bugs: #15403: Can't recover view

Bug #15403	Can't recover view
Submitted:	1 Dec 2005 19:04	Modified:	28 Aug 2006 11:11
Reporter:	Pedro Grullo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.1.5	OS:	Windows (Win. XP Prof.)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Backup

Description:
Trying to restore a schema from "SQL Files", restores it all but the only view in the schema.

Warning: Do not know how to handle this statement at line 743:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cuentasdesarrollo`.`movimientos_pendientes` AS select sql_no_cache `m`.`IdPeriodico` AS `IdPeriodico`,`m`.`Descripcion` AS `Descripcion`,`m`.`Comentario` AS `Comentario`,if((`m`.`TipoMovimientoIG` = _latin1'I'),`m`.`Importe`,-(`m`.`Importe`)) AS `Importe`,concat(`g`.`Categoria`,_latin1': ',`g`.`Descripcion`) AS `Grupo`,`c`.`Nombre` AS `Cuenta`,`m`.`F_UltimoApunte` AS `F_UltimoApunte`,`m`.`N_MesesPeriodo` AS `N_MesesPeriodo`,`m`.`N_DiaDelMes` AS `N_DiaDelMes`,`m`.`IdGrupo` AS `IdGrupo`,`m`.`IdCuenta` AS `IdCuenta` from ((`cuentasdesarrollo`.`movimientos_periodicos` `m` left join `cuentasdesarrollo`.`grupos` `g` on((`m`.`IdGrupo` = `g`.`IdGrupo`))) left join `cuentasdesarrollo`.`cuentas` `c` on((`m`.`IdCuenta` = `c`.`IdCuenta`))) where ((`m`.`F_UltimoApunte` + interval `m`.`N_MesesPeriodo` month) <= now());
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

I copied the query from the error message and executed it in the query browser without any problem.

The view recovers Ok if there is no data in the tables to recover.

How to repeat:
The problem seems to be related to the data restored. Though data is Ok (no key or index error). If you want, I could send a copy of the backup SQL file.

Suggested fix:
Correct the code or set the tables and views creation before the data restoration in SQL files.

Thank you for a problem report. Looks like a duplicate of bugs:

http://bugs.mysql.com/bug.php?id=14842
http://bugs.mysql.com/bug.php?id=14256

What version of MySQL server your are using?

I use version 5.0.11

I thought it would be a MA bug as I could run the query (copied from the message error) in the query browser.

My apologies for not having found those two similar bugs.

Your situation may be different from those bug reports (your view definer name is fully qualified, and, according to you, the statement works OK if it is executed separately). But, please, to be sure server is not a problem here, check with MySQL server 5.0.16 and inform about the results. Your 5.0.11 is really old.

I have installed version 5.0.16 then dropped the schema and tried to restore it with the same result:

Warning: Do not know how to handle this statement at line 743:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cuentasdesarrollo`.`movimientos_pendientes` AS select sql_no_cache `m`.`IdPeriodico` AS `IdPeriodico`,`m`.`Descripcion` AS `Descripcion`,`m`.`Comentario` AS `Comentario`,if((`m`.`TipoMovimientoIG` = _latin1'I'),`m`.`Importe`,-(`m`.`Importe`)) AS `Importe`,concat(`g`.`Categoria`,_latin1': ',`g`.`Descripcion`) AS `Grupo`,`c`.`Nombre` AS `Cuenta`,`m`.`F_UltimoApunte` AS `F_UltimoApunte`,`m`.`N_MesesPeriodo` AS `N_MesesPeriodo`,`m`.`N_DiaDelMes` AS `N_DiaDelMes`,`m`.`IdGrupo` AS `IdGrupo`,`m`.`IdCuenta` AS `IdCuenta` from ((`cuentasdesarrollo`.`movimientos_periodicos` `m` left join `cuentasdesarrollo`.`grupos` `g` on((`m`.`IdGrupo` = `g`.`IdGrupo`))) left join `cuentasdesarrollo`.`cuentas` `c` on((`m`.`IdCuenta` = `c`.`IdCuenta`))) where ((`m`.`F_UltimoApunte` + interval `m`.`N_MesesPeriodo` month) <= now());
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Of course, the view had not been restored. Then I removed the "warning..." and the "Ignoring..." from the error message and run it from the query browser. Then I got no error message and, naturally, the view was restored.

Can you, please, compress and upload the smallest becup that shows this problem? You may upload it a private file using the File tab.

Sample backup

Attachment: New Project 20051207 1231.zip (application/zip, text), 1.40 KiB.

Here you are. The "Sample backup" raises the error and I hope it is small enough.

Thank you for the backup. Verified just as described on MySQL Administrator 1.1.5 and backup provided (http://bugs.mysql.com/file.php?id=2503), with MySQL 5.0.15.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html