1. Forum
  3. Joomla
  5. Issue Tracker
  7. DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER

DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER

10 years 5 months ago - 10 years 5 months ago #1 by om
DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER was created by om
Hi,
i tried to restore my database to have a testing environment for joomla 2.5. i used phpadmin. After a while i get an error:

CREATE ALGORITHM = UNDEFINED DEFINER = `here-is-the-name-of-my-db-user-orig`@`orig-host` SQL SECURITY DEFINER VIEW `xyz_it_project_view1` AS SELECT `b`.`id` AS `id` , `b`.`parent_id` AS `pid` , `c`.`level` AS `level` , `c`.`title` AS `title` , `c`.`access` AS `access`
FROM (
`xyz_it_projects` `b`
JOIN `xyz_it_projects` `c`
)
WHERE (
(
`b`.`lft`
BETWEEN `c`.`lft`
AND `c`.`rgt`
)
AND (
`c`.`level` 0
)
)
ORDER BY `b`.`lft` , `c`.`lft` ;

MySQL meldet: Dokumentation
#1146 - Table 'testdb.xyz_it_projects' doesn't exist

i looked in the db-backup-file and it seems the problem is the option:
ALGORITHM = UNDEFINED DEFINER = `here-is-the-name-of-my-db-user-orig`@`orig-host` SQL SECURITY DEFINER

So as i change the name to the 'local-db-root'@'localhost' the error is the same.

what can i do to import the data for the issue-component.
when it delete the _it_* entries. the backup-file worked well. but a new installation of issue-tracker will fail.

Edited by om - 05.11.2013 10:46

Edited by om - 05.11.2013 10:49

Edited by om - 05.11.2013 10:52

Please Log in or Create an account to join the conversation.

10 years 5 months ago #2 by geoffc
Replied by geoffc on topic Re: DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
The problem is caused by the database that you are restoring to not having the correct database permissions to restore VIEWS. Look at the permissions on the database for the user. They need to have 'CREATE VIEW', 'CREATE ROUTINE' and 'TRIGGERS' permission.

The database where you have taken your export, does have these permissions since the views are present in your backup.

This is not a specific problem with Issue Tracker but with the database permissions on the site you are restoring to.

You do not say what version of Issue Tracker you are using, but I suspect that this is version 1.3.4.

Depending upon the reason for the 'restore' process will determine the best course of action to follow next. If the destination is a live system and you cannot obtain/set the required database permissions, then I can let you know where to obtain a version of Issue Tracker that does not require these permissions. [You may need to modify the backup criteria to exclude views, triggers and procedures.] If the destination can have these permissions set/changed, then this is the best way to go.
Regards
Geoff

Please Log in or Create an account to join the conversation.

10 years 5 months ago #3 by om
Replied by om on topic Re: DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
hi,
ok, i unterstand. i changed the user to root because:

Benutzer Host Typ Rechte GRANT
root linux global ALL PRIVILEGES Ja
root localhost global ALL PRIVILEGES Ja

but the same result after import.
I use 1.3.4 under joomla 2.5. the same result i get under Joomla 3.x with 1.4

i agree with you that here is a problem with mysql and not with issue-tracker. i changed the line to:

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

but same failure after import. can i remove the complete "ALGO...to... SECURITY DEFINER"-Option of the CREATE... VIEW line.

Perhaps there a problem when the line:
CREATE TABLE `ndroy_it_projects` (...)
is after der CREATE VIEW line in the import-file?

Please Log in or Create an account to join the conversation.

10 years 5 months ago #4 by geoffc
Replied by geoffc on topic Re: DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
You might be correct. I always use Akeeba Backup myself and regularly refresh my test instance from the data in live. I must admit it has never given me any problems, but then again why would phpadmin generate views after table creation. That doesn't make much sense logically.

The views are only used by the 'finder' plugin so as a test you might try excluding them from the backup and testing to see if the restore works. If it does the views could always be created afterwards.

I would need to carry out some tests using phpadmin export to discover anything else.

Re the triggers and procedures, the triggers are used for a second line audit so could be safely ignored, and the procedures are used for the sample data adding/removal, somthing that is not all that important on the restore.

==========

If the same problem occurs with IT 1.4, then it will not be permissions, since 1.4 has some extra checking of permissions involved and handles the lack of permissions gracefully.

Edited by geoffc - 05.11.2013 13:20

=========
Been looking around and it seems this problem is quite common. The general opinion appears to be that the database user doesn't have sufficient privileges (permissions) to use the CREATE VIEW command. [Which was where we started.] Another alternative might be that the new host is running a version of MySQL that doesn't support the ALGORITHM clause. If it is the latter you would need to upgrade the MySQL server.
Having said that it is likely that the ALGORITHM clause is supported on most MYSQL 5.x versions so I think this is unlikely.

The suggestion to remove the view creation and create it later has been suggested, but another solution is to change the CREATE VIEW statment just removing the ALOGITHM clause completely, so that it just reads 'CREATE VIEW', although it wouldn't explain the table not being found. Since the Joomla database connection user will 'own' the view and is used for all Joomla access to the database this should work.

Edited by geoffc - 05.11.2013 14:51
Regards
Geoff

Please Log in or Create an account to join the conversation.

7 years 11 months ago - 7 years 11 months ago #5 by calernitano
Replied by calernitano on topic Re: DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
I use joomla 3.5 and I have the same problem when I import the database in phpmyadmin in MAMP :-(

CREATE ALGORITHM=UNDEFINED DEFINER=`pincopallo`@`%` SQL SECURITY DEFINER VIEW `sp8r4_it_project_view2` AS select `sp8r4_it_project_view1`.`id` AS `id`,group_concat(`sp8r4_it_project_view1`.`title` order by `sp8r4_it_project_view1`.`level` ASC separator ' - ') AS `title`,`sp8r4_it_project_view1`.`access` AS `access` from `sp8r4_it_project_view1` group by `sp8r4_it_project_view1`.`id`;

#1449 - The user specified as a definer ('pincopallo'@'%') does not exist

The problem is on the original remote database or on the MAMP localhost database?
Where and how can I fix it?
Thanks!

Please Log in or Create an account to join the conversation.

7 years 11 months ago #6 by geoffc
Replied by geoffc on topic Re: DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
The database you are restoring to (MAMP) is lacking the permission 'CREATE VIEWS' for the joomla user. i.e the user doing the import.

Either omit the export of the views on the source database or add the permissions to the target database. Either would work although the use of the views on the search plugin would of course not be present it they were not in the exported backup file.

This is a database permissions/configuration problem.
Regards
Geoff

Please Log in or Create an account to join the conversation.

  1. Forum
  3. Joomla
  5. Issue Tracker
  7. DB import failure at CREATE ALGORITHM = UNDEFINED DEFINER
Time to create page: 0.153 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries