Phpmyadmin exports VIEW without DATABASE_NAME or ALGORITHM

Question

When exporting an sql dump with phpmyadmin, it creates a VIEW table as follows:

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

Every time I have to manually edit the sql dump to remove the root and database root .

Answer 1

Maybe I don’t understand ... but it always worked for me. There are no references to the database name, and all qualifiers will be destroyed, so they will be easily restored from the created file:

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

Recovery:

 mysql -uUSERNAME -pPASSWORD database < backup.sql 

As long as the database you are restoring exists (empty or not), it works like a charm.

Answer 2

To remove database names from the result, you must set the default database name, for example. -

 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 determinant is a view property, I did not see any parameters that could remove this property from the SHOW CREATE VIEW result. So, you will need to parse the text and change it manually.

---

Alternatively, you can try “Generate Script Schema” or “Backup” with the options “Exclude the DEFINER and SQL SECURITY option” in dbForge Studio for MySQL . This will help you generate the script you want in a few steps:

• select view in database explorer
• open the pop-up menu and click "Generate Script Schema ...".
• find the option "Exclude DEFINER and SQL SECURITY clauses" and check it
• Click Create to create and open SQL text.

Answer 3

When exporting an sql dump with phpmyadmin, it creates a VIEW table as follows:

I checked with mysqldump , which does the same thing. Both mysqldumps like PHPMyAdmin seem to perform SHOW CREATE, which leads to the presence of a security qualifier in the create statement. I see no way to "disable it."

Every time I have to manually edit the sql dump to remove the root username and database.

Here is where I can help. Manually editing the SQL file is hell and it is too error prone. I had these problems before the export user did not exist on the platform I wanted to import. The only thing I needed to edit was everything between CREATE and VIEW.

Now, since you tagged your question with php , I think you know how to use it from the command line. Here is a script that will replace all unnecessary 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 ); 

It may not be a direct answer to your question, but it should prevent you from manually editing dumps.

Answer 4

Creates a VIEW with DEFINER = {user | CURRENT_USER} and with the full table name, because during export of VIEW PHPMyADMIN executes SHOW CREATE VIEW database_name . view_name ; which returns all of these values ​​as it is part of the result of SHOW CREATE VIEW. See this .

Answer 5

I use this simple script to delete after export using 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

In linux, it is very simple to delete unnecessary information in a text file. Given that CREATE VIEW creates something like:

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

You can use the following sed:

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

You can also use mysqldump in the same pipe command:

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