Phpmyadmin export VIEW without DATABASE_NAME or AL

Question

When exporting a sql dump with phpmyadmin it creates the VIEW table like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `database`.`table` etc..

Each time I have to manually edit the sql dump to remove the root user and database name.

Answer 1

Maybe I'm not understanding... but this has always worked for me. There are no references to the database name and all definers get wiped out so it's super-easy to restore from the file it generates:

mysqldump -uUSERNAME -pPASSWORD database |  sed -e 's/DEFINER=[^*]*\*/\*/' > backup.sql

To restore:

mysql -uUSERNAME -pPASSWORD database < backup.sql

As long as the database you're restoring to exists (empty or not), works like a charm.

Answer 2

In linux it's very simple to remove unwanted informations in a text file. Given that CREATE VIEW creates something like:

CREATE ALGORITHM=UNDEFINED DEFINER=USER@HOST SQL SECURITY DEFINER VIEW VIEWNAME ...etc.

You can use the following sed:

sed -e 's/ALGORITHM.*DEFINER VIEW/VIEW/' backup-mysqldump.sql > backup.sql

You can also using with mysqldump in a single command, with a pipe:

mysqldump -uUSERNAME -pPASSWORD database |sed -e 's/ALGORITHM.*DEFINER VIEW/VIEW/' > backup.sql

Answer 3

It creates a VIEW with DEFINER = { user | CURRENT_USER } and with fully qualified table name because during export VIEW PHPMyADMIN executes SHOW CREATE VIEW database_name.view_name; which returns all these values as it is part of SHOW CREATE VIEW result. Refer this.

Answer 4

When exporting a sql dump with phpmyadmin it creates the VIEW table like this:

I've checked with mysqldump, which does precisely the same. Both mysqldump as PHPMyAdmin seem to execute SHOW CREATE, which results in having a security definer in the create statement. I don't see any way to "turn it off".

Each time I have to manually edit the sql dump to remove the root user and database name.

This is where I can help though. Manually editing an SQL file is hell and it's too error-prone. I've had these issues before where the exporting user didn't exist on the platform I wanted to import. The only thing I needed to edit was everything between CREATE and VIEW.

Now, since you've tagged your question with php, I imagine you know how to use it from commandline. Here's a script that will replace all of the non-necessary values with an empty string:

<?php

$string = 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
    VIEW `database`.`table` AS';

$string = preg_replace(
    '/CREATE .*(TABLE|VIEW) `[^`]+`\.(`[^`]+`)/s',
    'CREATE $1 $2',
     $string 
);

var_dump( $string );

Maybe not a direct answer to your question, but it should prevent you from having to manually edit the dumps.

Answer 5

I use this simple script for removing after exporting with mysqldump

<?php
$file = file_get_contents($argv[1]);
$position = strpos($file, 'Final view structure for view');
if (false !== $position) {
    echo substr($file, 0, $position);
}
else {
    die('not found');
}

https://gist.github.com/kissarat/bbf33d10f9d7a3bc1adb

Answer 6

To remove database names from the result, you should set default database name, e.g. -

USE db_name;
SHOW CREATE VIEW view_name;

You will get something like this (DDL without database names) -

CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view1`
AS SELECT `table1`.`column1` AS `column1` FROM `table1`

The definer is a view property, I have not seen any options that could remove this property from the SHOW CREATE VIEW result. So, you will need to parse the text and modify it manually.

---

Also, you can try 'Generate Schema Script' or 'Backup' tool with 'Exclude DEFINER and SQL SECURITY clauses' option in dbForge Studio for MySQL. It will help you to generate the script you want in a few steps:

• select view in the Database Explorer
• open popup-menu and click on 'Generate Schema Script...' command
• find option 'Exclude DEFINER and SQL SECURITY clauses' and check it
• press 'Generate' button to build and open SQL text.