#2451 (ok 2.10.3) Copy Database View Problem
Hi,
We have databases in which we have tables and views... When we go into operation/copy database to make a backup, tables get copied, but the new database is missing all the views...
An error page is generated with an error I will paste below:
The error seems to be very easy to fix... Just change
CREATE ALGORITHM = UNDEFINED DEFINER = `mysqladmin`@ `64.18.66.%` SQL SECURITY INVOKER VIEW
for
CREATE ALGORITHM = UNDEFINED DEFINER = 'mysqladmin'@ '64.18.66.%' SQL SECURITY INVOKER VIEW
(I Just replaced the backquote by quote around the definer username and ip)
SQL query: DocumentationEdit
CREATE ALGORITHM = UNDEFINED DEFINER = `mysqladmin`@ `64.18.66.%` SQL SECURITY INVOKER VIEW `dicair_backup_2007-06-10`.`vActivesessionlist` AS SELECT `dicair_backup_2007-06-10`.`tActivesession`.`pkiActivesessionID` AS `pkiActivesessionID` , `dicair_backup_2007-06-10`.`tActivesession`.`fkiUserID` AS `fkiUserID` , `dicair_backup_2007-06-10`.`tActivesession`.`fkiComputerID` AS `fkiComputerID` , `dicair_backup_2007-06-10`.`tActivesession`.`dtActivesessionFirsthit` AS `dtActivesessionFirsthit` , `dicair_backup_2007-06-10`.`tActivesession`.`dtActivesessionLasthit` AS `dtActivesessionLasthit` , `dicair_backup_2007-06-10`.`tActivesession`.`sActivesessionIP` AS `sActivesessionIP` , `dicair_backup_2007-06-10`.`tActivesession`.`bActivesessionIssuperadmin` AS `bActivesessionIssuperadmin` , `dicair_backup_2007-06-10`.`tUser`.`sUserLoginname` AS `sUserLoginname` , `dicair_backup_2007-06-10`.`tComputer`.`sComputerDescription` AS `sComputerDescription` , concat( `dicair_backup_2007-06-10`.`tCompany`.`sCompanyName` , _utf8 ': ', `dicair_backup_2007-06-10`.`tDepartment`.`sDepartmentName` ) AS `CompanyDepartment` , `dicair_backup_2007-06-10`.`tCompany`.`fkiLanguageID` AS `fkiLanguageIDCompany` , `dicair_backup_2007-06-10`.`tDepartment`.`fkiLanguageID` AS `fkiLanguageIDDepartment` , `dicair_backup_2007-06-10`.`tComputer`.`pkiComputerID` AS `pkiComputerID`
FROM (
(
(
(
`dicair_backup_2007-06-10`.`tActivesession`
JOIN `dicair_backup_2007-06-10`.`tUser`
)
JOIN `dicair_backup_2007-06-10`.`tComputer`
)
JOIN `dicair_backup_2007-06-10`.`tCompany`
)
JOIN `dicair_backup_2007-06-10`.`tDepartment`
)
WHERE (
(
`dicair_backup_2007-06-10`.`tUser`.`pkiUserID` = `dicair_backup_2007-06-10`.`tActivesession`.`fkiUserID`
)
AND (
`dicair_backup_2007-06-10`.`tComputer`.`pkiComputerID` = `dicair_backup_2007-06-10`.`tActivesession`.`fkiComputerID`
)
AND (
`dicair_backup_2007-06-10`.`tCompany`.`pkiCompanyID` = `dicair_backup_2007-06-10`.`tActivesession`.`fkiCompanyID`
)
AND (
`dicair_backup_2007-06-10`.`tDepartment`.`pkiDepartmentID` = `dicair_backup_2007-06-10`.`tActivesession`.`fkiDepartmentID`
)
)
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '64.18.66.%` SQL SECURITY INVOKER VIEW `dicair_backup_2007-06-10`.`vActivesessio' at line 1
Logged In: YES
user_id=210714
Originator: NO
The backquotes are not the problem (those are generated by MySQL itself and they work). The problem is the extra blank generated by phpMyAdmin after the @.
Logged In: YES
user_id=1636250
Originator: YES
Oups sorry about the backquote fix :)
I probably removed the space also that is why I though it was the problem.
Anyway, an easy one to fix :)
Logged In: YES
user_id=210714
Originator: NO
I hope it's easy but I don't think it will. Playing with our parser can get tricky.
Logged In: YES
user_id=1383652
Originator: NO
wrong spaces (too many or missing ones) are a big problem in the parser.
I remember many issues in the last months.
Logged In: YES
user_id=210714
Originator: NO
Here is the patch merged for 2.10.3:
--- branches/QA_2_10/phpMyAdmin/libraries/sqlparser.lib.php 2007/04/03 10:23:49 10247
+++ branches/QA_2_10/phpMyAdmin/libraries/sqlparser.lib.php 2007/06/11 17:00:56 10432
@@ -2315,10 +2315,13 @@
}
break;
case 'quote_backtick':
- if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable') {
+ // here we check for punct_user to handle correctly
+ // DEFINER = `username`@`%`
+ // where @ is the punct_user and `%` is the quote_backtick
+ if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
$after .= ' ';
}
- if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable') {
+ if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
$before .= ' ';
}
break;